Database Management System: Questions (1 - 20) Are Based On The Following 3 Tables

Database Management System
Questions (1 – 20) are based on the following 3 tables:

Table Name: Cust_Info
CustNum City OrderNum

1001 Kanpur 2001
1002 Vizag 2002
1003 Guntur 2003
1004 Agra 2004
1005 Guntur 2005
1006 Pune 2006
1007 Guntur 2007
1008 Pune 2008
1009 Delhi 2009
1010 Imphal 2010
Table Name: Order_Info
OrderNum OrderDate OrderedItem Quantity

2001 02-FEB-05 Pen 5
2001 02-FEB-05 Pencil 3
2002 13-JAN-05 Pen 3
2002 13-JAN-05 Pencil 8
2003 11-JAN-05 Table 1
2004 11-JAN-04 Chair 4
2005 11-JAN-04 Table 1
2006 17-APR-01 Pen 10
2007 21-JUL-04 Pencil 20
2008 15-JAN-05 Table 2
2009 10-OCT-02 Table 1
2009 10-OCT-02 Pen 1
2009 10-OCT-02 Pencil 1
2010 18-OCT-03 Table 1
Table Name: Item_Info
ItemName UnitPrice (in Rs)

Pen 10
Pencil 2
Table 101
Chair 26
1
1. Which of the following could be a primary key of the Order_Info table?
(a) OrderNum (b) OrderDate

(c) OrderedItem (d) None of these
2. Which of the following could be a secondary key of the order_Info table?
(a) OrderNum (b) Order Date

(c) OrderedItem (d) None of these
3. If someone talks about the order header information, probably he/ she is referring to the table:
(a) Cust_Info (b) Order_Info

(c) Item_Info (d) None of these
4. The SQL statement:
SELECT SUM (B.Quantity*C.UnitPrice)

FROM Cust_Info A, Order_Info B, Item_Info C
WHERE A.CustItem=1009 AND A.OrderNum=B.OrderNum
AND B.OrderItem=C.ItemName;
Prints:
(a) 101 (b) An unpredictable value
(c) 1139 (d) None of these
5. To answer the Question “Whose bill is the highest” we need to write SQL query that joins the
table:
(a) Cust_Info, Order_Info, Item_Info (b) Order_Info and Item_Info

(c) Order_Info and Item_Info (d) None of these
6. How many rows does the following SQL query print?
SELECT DISTINCT A.OrderNum, B.OrderNum

FROM Cust_Info A, Order_Info B
GROUP BY (A.OrderNum, B.OrderNum);
2
(a) 10 (b) 24
(c) 100 (d) 240
7. Fill in the blanks in the following query that finds the number of orders that ordered for either
pencil or pen.
SELECT ___________ (DISTINCT __________ ) FROM Order_Info B

WHERE B.OrderedItem= ‘Pen’ ___________ B.OrderedItem= ‘___________’
(a) COUNT, OrderNum, OR, ‘Pencil’ (b) COUNT, OrderNum, ‘OR’, ‘Pencil’
(c) UNIQUE, OrderNum, OR, ‘Pencil' (d) None of these
8. Fill in the blanks in the following query that finds the number of customers who ordered for
either pen or pencil:
SELECT COUNT ( __________ A.CustNum)

FROM Cust_Info A, Order_Info B
WHERE A.OrderNum= B.OrderNum
_________ B.OrderedItem __________ (‘Pen’, ‘Pencil’)
(a) ALL, AND, NOT IN (b) ALL, OR, IN

(c) DISTINCT, AND, IN (d) DISTINCT, OR, IN
9. Which of the following CustNum will not be displayed by the following SQL query?
SELECT A.CustNum FROM Cust_Info A, Order_Info B

WHERE A.OrderNum = B.OrderNum AND B.OrderedItem=’Pen’
UNION
WHERE A.OrderNum=B.OrderNum AND B.OrderedItem=’Pencil’;
(a) 1006 (b) 1007

(c) 1008 (d) 1009
10. How many rows will be displayed by the following SQL query?

WHERE A.OrderNum= B.OrderNum AND B.OrderedItem= ‘Pen’
3
UNION ALL
SELECT A. CustNum FROM Cust_Info A, Order_Info B
WHERE A.OrderNum= B.OrderNum AND B.OrderedItem=’Pencil’;
(a) 7 (b) 8
(c) 9 (d) 10
11. Which CustNum will not be displayed by the following SQL query?

WHERE A.OrderNum= B.OrderNum AND B.OrderedItem= ’Pen’
INTERSECT
WHERE A.OrderNum=B.OrderNum AND B.OrderedItem=’Pencil’;
(a) 1001 (b) 1002

(c) 1006 (d) 1009
12. How many CustNum will be displayed be the following SQL query?
SELECT DISTINCT A.CustNum FROM Cust_Info A, Order_Info B,

WHERE A.OrderNum=B.OrderNum AND B.OrderedItem
NOT IN (SELECT A.OrderedItem FROM Order_Info A
WHERE A.OrderNum IN (2001, 2002));
(a) 4 (b) 5
(c) 6 (d) 7
13. How many CustNum will be displayed by the following SQL query?

WHERE A.OrderNum= B.OrderNum AND B.OrderedItem IN (‘Pen’, ‘Pencil’)
INTERSECT
WHERE A.OrderNum= B.OrderNum AND B.OrderedItem NOT IN (‘Pen’, ‘Pencil’)
(a) 0 (b) 1
(c) 4 (d) 5
4
14. How many CustNum will be displayed in the following SQL query?
SELECT DISTINCT A.CustNum FROM Cust_Info A, Order_Info B

WHERE A.OrderNum=B.OrderNum AND A.City
IN (‘Kanpur’, ‘Guntur’) AND B.OrderedItem IN (‘Table’, ‘Chair’);
(a) 0 (b) 1
(c) 2 (d) 3
15. How many CustNum will be displayed by the following SQL query?
SELECT DISTINCT (A.CustNum) FROM Cust_Info A, Order_Info B

WHERE A.Order.Num= B.OrderNum AND B.OrderedItem
IN (‘Table’, ‘Chair’, ‘Pen’, ‘Pencil’)
MINUS
WHERE A.OrderNum= B.OrderNum AND A.City
IN (‘Kanpur’, ‘Vizag’, ‘Guntur’, ‘Agra’, ‘Pune’, ‘Delhi’, ‘Imphal’);
(a) 0 (b) 1
(c) 2 (d) 3
16. Which of the following remarks about the following query are True?

WHERE A.OrderNum=B.OrderNum AND B.OrderedItem
= (SELECT C.ItemName FROM Item_Info C
WHERE C.UnitPrice= (SELECT MAX (C.UnitPrice) FROM Item_Info C));
(a) It returns 5 rows

(b) One of the CustNum returned is 1008
(c) It returns all CustNum that ordered a table
(d) All of the above
17. Which of the listed options can fill the blank if the following query displayed exactly 6 rows?
SELECT DISTINCT (A. CustNum) FROM Cust_Info A, Order_Info B

WHERE A.OrderNum= B.OrderNum AND B.OrderedItem
IN (SELECT C.ItemName FROM Item_Info C WHERE C.UnitPrice > ______ );
5
(a) 2 (b) 8
(c) 20 (d) 30
18. What is the CustNum that will be displayed by the following query?

AND B.OrderDetails= (Select MAX (C.OrderDate) FROM Order_Info C);
(a) 1001 (b) 1002

(c) 1003 (d) 1004
19. How many rows are returned by the following query?

WHERE A.OrderedItem= B.OrderedItem
AND B.OrderDate BETWEEN ’11-JUN-04’ AND ’02-FEB-05’;
(a) 4 (b) 5
(c) 6 (d) 7
20. The query:
SELECT SYSDATE FROM DUAL;

DISPLAYS
02-FEB-05
How many rows will be displayed by the following query?

WHERE A.OrderNum=B.OrderNum AND SUBSTR (TO_CHAR (B.OrderDate, 8)
= (SELECT SUBSTR (TO_CHAR (SYSDATE), 8) FROM DUAL;
(a) 1 (b) 2
(c) 3 (d) 4

Database Management System: Questions (1 - 20) Are Based On The Following 3 Tables

Uploaded by

Copyright:

Available Formats

Database Management System: Questions (1 - 20) Are Based On The Following 3 Tables

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Management System: Questions (1 - 20) Are Based On The Following 3 Tables

Uploaded by

Copyright:

Available Formats

Database Management System

Questions (1 – 20) are based on the following 3 tables:

CustNum City OrderNum

Table Name: Order_Info

OrderNum OrderDate OrderedItem Quantity

Table Name: Item_Info

ItemName UnitPrice (in Rs)

(a) OrderNum (b) OrderDate

2. Which of the following could be a secondary key of the order_Info table?

(a) OrderNum (b) Order Date

(a) Cust_Info (b) Order_Info

4. The SQL statement:

SELECT SUM (B.Quantity*C.UnitPrice)

(a) Cust_Info, Order_Info, Item_Info (b) Order_Info and Item_Info

6. How many rows does the following SQL query print?

SELECT DISTINCT A.OrderNum, B.OrderNum

SELECT ___________ (DISTINCT __________ ) FROM Order_Info B

SELECT COUNT ( __________ A.CustNum)

(a) ALL, AND, NOT IN (b) ALL, OR, IN

SELECT A.CustNum FROM Cust_Info A, Order_Info B

(a) 1006 (b) 1007

SELECT A.CustNum FROM Cust_Info A, Order_Info B

SELECT A.CustNum FROM Cust_Info A, Order_Info B

(a) 1001 (b) 1002

SELECT DISTINCT A.CustNum FROM Cust_Info A, Order_Info B,

SELECT A.CustNum FROM Cust_Info A, Order_Info B

SELECT DISTINCT A.CustNum FROM Cust_Info A, Order_Info B

SELECT DISTINCT (A.CustNum) FROM Cust_Info A, Order_Info B

SELECT DISTINCT (A.CustNum) FROM Cust_Info A, Order_Info B

(a) It returns 5 rows

SELECT DISTINCT (A. CustNum) FROM Cust_Info A, Order_Info B

SELECT DISTINCT (A.CustNum) FROM Cust_Info A, Order_Info B

(a) 1001 (b) 1002

19. How many rows are returned by the following query?

SELECT DISTINCT (A.CustNum) FROM Cust_Info A, Order_Info B

20. The query:

SELECT SYSDATE FROM DUAL;

How many rows will be displayed by the following query?

SELECT DISTINCT (A.CustNum) FROM Cust_Info A, Order_Info B

You might also like

SELECT _ (DISTINCT ) FROM Order_Info B