1) Consider the table products given below

TABLE: products

pid customername productname brand Price

P1001 Vishwa Goodday Britannia 25

P1002 Santhosh Darkfantasy Sunfeast 35

P1001 Vishwa Bourbon Britannia 20

P1003 Ashwin Oreo Cadbury 30

P1002 Santhosh MarieGold Britannia 10

P1003 Ashwin Nice Sunfeast 30

How many rows will be obtained as output after successfully executing the given query?

SELECT pid, customername FROM products WHERE price > 10 GROUP BY pid,
customername HAVING COUNT (DISTINCT brand) > 1;

a) 2
b) 1
c) 4
d) 3

Answer: b
Explanation: There is only one row with a distinct brand whose price is > 1.

2) Consider the table products given above. Choose the suitable query to display product
names ending with the alphabet ‘y’

TABLE: Products

pid Customername productname Brand price

P1001 Vishwa Goodday Britannia 25

P1002 Santhosh Darkfantasy Sunfeast 35

P1001 Vishwa Bourbon Britannia 20

 P1003 Ashwin Oreo Cadbury 30

P1002 Santhosh MarieGold Britannia 10

P1003 Ashwin Nice Sunfeast 30

a) select productname from products where productname like '%y';

b) select productname from products where productname like 'y%';
c) select productname from products where productname like '_y';
d) select productname from products where productname like 'y_';

Answer: a
Explanation: %y gives all the selected column values that end in y.

3) Consider the table Products and Seller given below

TABLE: Products

Productid Productname Price

P1001 TV 35000

P1002 Laptop 55000

P1003 Smartphone 20000

 P1004 Speaker 10000

P1005 Headphones 19500

TABLE: Seller

Sellerid Productid Status

S1001 P1001 Delivered

S1002 P1002 NotDelivered

S1003 P1001 Delivered

S1004 P1002 NotDelivered

S1005 P1004 Delivered

How many rows will be obtained as output after successfully executing the given query?

SELECT Productid FROM Seller WHERE Status='Delivered' AND Productid IN (SELECT

Productid FROM Products WHERE Price>=20000);

a) 1
b) 2
c) 3
d) 4

Answer: b
Explanation: The table seller with status =' Delivered' have 3 rows with Productid P1001 and
P1004. Among those Productid, the Price of >=20000 is present for only P1001 (Price = 35000).
So as a result, only 2 rows will be fetched.

4) Choose the Incorrect statement in accordance with LIKE operator in SQL

I. LIKE operator in SQL is case sensitive.

II. LIKE ‘%ono%’ outputs all strings that contain ono
III. LIKE ‘%xyz’ outputs all strings that begin with xyz
IV. LIKE ‘_lee_’ outputs all strings which have 5 characters in total, where the 2nd,3rd and
4th characters are l,e,e respectively
 a) I & III
b) I & II
c) II & III
d) All the above

Answer: a
Explanation: Like operator in SQL is case insensitive and to search for strings that begins with
‘xyz’ the Like operator ‘xyz%’ is used. So, the first and the third statement are incorrect.

5) For the given Products table, Choose the correct statement to retrieve the values that
contain ‘_ea_’ in the ‘Productname’ column?

Products table columns:

Productid VARCHAR
Productname TEXT
Price integer
a) SELECT Productid, Productname, Price FROM Products WHERE Productname = '%ea
%';
b) SELECT Productid, Productname, Price FROM Products WHERE Productname LIKE
'ea_%';
c) SELECT Productid, Productname, Price FROM Products WHERE Productname LIKE
'%ea%';
d) None of these

Answer: c
Explanation: To output a string that contains ‘_ea_’, the correct syntax is LIKE ‘%ea%’ where
the ‘%’ denotes zero or more characters

6) Consider the table Intern_Details given below

Internid Internname Stipend Bonus Assigned_Project

I1 Arun 27000 400 Banking

I2 Manoj 20000 300 Networking

I3 Arun 40000 600 Testing

I4 Mani 50000 700 Testing

I5 Ravi 25000 null Networking

I6 Ravi 20000 300 Networking

 I7 Prem 30000 null Testing

I8 Balu 27000 null Banking

Query:
SELECT Assigned_Project as Project, ROUND(AVG(Stipend)) as Average_Salary,
SUM(Bonus) as Total_Bonus FROM Intern_Details
GROUP BY Assigned_Project HAVING AVG(Stipend) > 20000 AND SUM(Bonus) > 500;

What will the output be for the given query?

a)
Project Average_Salary Total_Bonus

Networking 21667 600

Testing 40000 1300

b)
Project Average_Salary Total_Bonus

Networking 21667 600

Banking 40000 600

c)
Project Average_Salary Total_Bonus

Networking 21667 600

Banking 40000 600

Testing 50000 700

d)
Project Average_Salary Total_Bonus

Testing 40000 1300

Banking 40000 600

Answer: a
Explanation: By the use of Group By statement, the given table is reduced to 3 rows and from
the three rows, only 2 rows meet the given constraints such as (Average of Stipend > 20000
and Sum of Bonus > 500). Further, in the two rows, the ROUND and SUM functions are applied
to get the given output.

7) Consider the table Interns given below

Internid Internname Location Stipend Assigned_Project

I1 Arun Hyderabad 140000 Banking

I2 Manoj Chennai 225000 Networking

I3 Ashwin Mumbai 79000 Testing

I4 Mani Mumbai 152000 Testing

I5 Ravi Chennai 73000 Networking

A and B want to retrieve the Intern details and sort those details according to a location in
alphabetical order and in decreasing order of the stipend in each of those locations.
A’s Query:
SELECT Internid, Internname, Location, Stipend, Assigned_Project FROM Interns ORDER BY
Location ASC, Stipend DESC;
B’s Query:
SELECT Internid,Internname,Location,Stipend,Assigned_Project FROM Interns ORDER BY 4
DESC, 3 ASC;
Which Query will produce the required output?

a) Both A & B
b) Neither A nor B
c) A
d) B

Answer: c
Explanation: A’s Query selects the required information from the given table and for performing
sorting, the Order By keyword is used, where the Location is sorted in the Ascending order first
and for the rows which have the same location, the stipend is sorted in the descending order.
8) Consider the table Employee given below
Location name Salary

Norway Alex 81000

Kuwait Martin 77000

Kuwait Ashraf 37000

 Egypt Darren 12000

Norway Naveen 57000

Serbia Mitchell 25000

Egypt Nithin 15000

Write the perfect query to get the given output
Location name Salary

Kuwait Martin 77000

Egypt Darren 12000

Egypt Nithin 15000

a) SELECT*FROM Employee WHERE (LOWER(Location) LIKE '%t' AND (UPPER(name)

LIKE '%N' OR UPPER(name) LIKE 'N%')) ;
b) SELECT*FROM Employee WHERE (LOWER(Location) LIKE '%th' OR (UPPER(name)
LIKE '%N' AND UPPER(name) LIKE 'N%')) ;
c) SELECT*FROM Employee WHERE (LOWER(Location) LIKE '%t' AND (UPPER(name)
LIKE '%A' OR UPPER(name) LIKE 'N%')) ;

d) SELECT*FROM Employee WHERE (LOWER(Location) LIKE '%th' OR (UPPER(name)

LIKE '%D' AND UPPER(name) LIKE 'N%')) ;

Answer: a
Explanation: The LOWER and UPPER function converts the location and name into lowercase
and uppercase respectively and the Like operator which uses ‘%t’ for location and ‘%n’ for name
matches the pattern with the table and prints the required output.

9) Consider the table Intern_Details given below

Internid Internname Stipend Bonus Assigned_Project

I1 Arun 27000 400 Banking

I2 Manoj 20000 300 Networking

I3 Arun 40000 600 Testing

 I4 Mani 50000 700 Testing

I5 Ravi 25000 null Networking

I6 Ravi 20000 300 Networking

I7 Prem 30000 null Testing

I8 Balu 27000 null Banking

How many rows will be obtained as output after successfully executing the given query?
SELECT * FROM Intern_Details WHERE Stipend > 25000 AND Assigned_project LIKE 'T%'
AND Bonus IS NOT NULL;

a) 0
b) 2
c) 1
d) 3

Answer: b
Explanation: Initially the table contains 8 rows. Applying the first condition ( Stipend>25000), it is
reduced to 5 rows and after applying the second condition (LIKE ‘T%’ ) the table is reduced to 3
rows. Then the final condition of the (Bonus - Not Null) is applied and the 3 rows are reduced to
2 rows as one row contains a NULL value. So 2 rows are obtained as a result

10) Consider the table products given below

pid productname brand Price

P1001 Goodday Britannia 25

P1002 Darkfantasy Sunfeast 35

P1001 Bourbon Britannia 20

P1003 Oreo Cadbury 30

P1002 MarieGold Britannia 10

P1003 Nice Sunfeast 30

Choose the appropriate query to print the below output
Output:
brand productname price
 Britannia MarieGold 10

Britannia Goodday 25

Britannia Bourbon 20

Cadbury Oreo 30

Sunfeast Nice 30

a) SELECT Brand,productname,price FROM products WHERE price <=30 ORDER BY

brand ASC, productname ASC;
b) SELECT Brand,productname,price FROM products WHERE price <=30 ORDER BY
brand ASC, productname DESC;
c) SELECT Brand,productname,price FROM products WHERE price <=30 GROUP BY
brand ASC, productname ASC;
d) SELECT Brand,productname,price FROM products WHERE price <=30 GROUP BY
brand ASC, productname DESC;

Answer: b
Explanation: Initially the table contains 6 rows. Applying the first condition (price <=30), the
table is reduced to 5 rows. The next condition is to perform sorting by using the Order By
keyword. So, the brand is sorted in the Ascending order first and for the rows which have the
same brand, the product name is sorted in the descending order

11) Consider the given tables below

TABLE: Company_Projects
pid Name

I1 Siemens

I2 CITI

I3 JP Morgan

I4 Lufthansa
TABLE: Employee_allocation
eid name Pid

E1 Aditi I2
 E2 Elvin I2

E3 Jaydev I3

E4 Axar NULL

E5 Vijai NULL
SELECT C.pid,C.name,E.eid FROM Company_Projects C FULL JOIN Employee_allocation E
ON C.pid=E.pid AND E.pid IS NOT NULL;
How many rows will be obtained as output after successfully executing the given query?

a) 6
b) 3
c) 7
d) 5

Answer: b
Explanation: The FULL JOIN keyword returns all the matching data from both tables whether
the other table matches or not. So, the output data will be completely dependent on the given
query "C.pid=E.pid AND E.pid IS NOT NULL". The column E.pid has only 3 rows without the
NULL value where the rows E4 and E5 have NULL values. So, only 3 rows will be fetched as a
result

12) Consider the table Electronics given below

Id Name Price Discount Stock

E1 Laptop 30000 8 10

E2 Printer 5000 5 15

E3 Camera 7000 20 18

E4 Power Bank 1500 15 15

E5 Smartphones 9000 6 10

E6 TV 25000 30 30

SELECT e1.Id, e1.Name FROM Electronics e1 JOIN Electronics e2

ON e1.stock = e2.stock AND e1.Discount <> e2.Discount
WHERE e1.Discount>7
How many rows will be obtained as output after successfully executing the given query?

a) 6
b) 0
c) 2
d) 12

Answer: c
Explanation: Initially the table contains 6 rows. After applying SQL Inner Join, the table is
increased to 10 rows as the rows E3 AND E6 have unique Stock values and other rows don't.
After applying the next condition (e1.Discount <> e2.Discount) the table is reduced to 4 rows.
Then the final condition of (e1.Discount>7) is applied and the table is reduced to 2 rows as only
two out of four rows contain a discount value greater than 7. So, only two rows are fetched as
the result.

13) Consider the table Electronics given below

Id Name Price Discount Stock

E1 Laptop 30000 8 10

E2 Printer 5000 5 15

E3 Camera 7000 20 18

E4 Power Bank 1500 15 15

E5 Smartphones 9000 6 10

E6 TV 25000 30 30

Select DISTINCT(E1.Price) from Electronics AS E1 WHERE 3=( SELECT COUNT (DISTINCT

Price) FROM Electronics AS E2 where E1.Price <= E2.Price);

What will the output be for the given query?

a) 7000
b) 30000
c) 25000
d) 9000

Answer: d
Explanation: The query prints the third costliest product in the given table. The use of (where
2=) and comparison of the table with two aliases results in printing 9000

14) Consider the Table given below

TABLE: products

pid customername productname brand price

P1001 Vishwa Goodday Britannia 25

P1002 Santhosh Darkfantasy Sunfeast 35

P1001 Vishwa Bourbon Britannia 20

P1003 Ashwin Oreo Cadbury 30

P1002 Santhosh MarieGold Britannia 10

P1003 Ashwin Nice Sunfeast 30

SELECT count(p.customername) from products AS p join products AS p1 on p.pid=p1.pid

where p1.brand =p.brand;

What will the output be for the given query?

a) 10
b) 8
c) 9
d) 7

Answer: b
Explanation: Initially the table contains 6 rows. Applying the Join condition results in rows
increasing to 12 in the table. The next condition (where p1.brand =p.brand) is applied and the
table is reduced to 8 rows where the duplicate values of P1002 and P1003 are removed. Finally,
the Count function returns the number of rows present in the table. So, 8 is fetched as the
result.

15) From the following options for the given Query, which should be placed in the Query's
empty portion to choose the "Department_name" that contains Engineering as its ending string?

SELECT Student_name FROM College WHERE Department_name LIKE ' _____ Engineering';
 a) ?
b) &
c) %
d) ==

Answer: c
Explanation: The Like operator uses the ‘%’ wildcard in conjunction with it. The ‘%’ represents
zero or multiple characters. Here, the Syntax for printing the required output is LIKE
‘%Engineering’.

16) Consider the table Interns given below

Internid Internname Location Stipend Assigned_Project

I1 Arun Hyderabad 140000 Banking

I2 Manoj Chennai 225000 Networking

I3 Ashwin Mumbai 79000 Testing

I4 Mani Mumbai 152000 Testing

I5 Ravi Chennai 73000 Networking

SELECT _______ FROM Interns WHERE Assigned_Project= 'Networking';

Choose the correct function to use in the blank portion to find the mean of the Intern's Stipend

a) AVG(Stipend)
b) SUM(Stipend)
c) COUNT(Stipend)
d) MEAN(Stipend)

Answer: a
Explanation: The AVG function returns the mean value or average value of any numeric column.
17) Which of the given query should be used to find all the batsmen who participated in ODI
World Cup 2015 but not in the 2016 T20 World Cup?

a) SELECT DISTINCT(Batsman_name) FROM Tournament WHERE name NOT IN

('ODIWC’, 'T20WC');

b) SELECT DISTINCT Batsman_name FROM Cricket WHERE Tournament = 'ODIWC’

AND YEAR= 2015 AND Batsman_name NOT IN (SELECT Batsman_name FROM
Cricket WHERE Tournament = 'T20WC' AND YEAR= 2016);

c) SELECT COUNT (DISTINCT Name) FROM Tournament WHERE (Batsman_name,

Tournament, YEAR) IN (SELECT Batsman_name, Tournament, YEAR FROM Cricket
WHERE Cricket.Name= World Cup);

d) (SELECT Batsman_name FROM Cricket WHERE Tournament = 'T20WC' AND YEAR=

2016)

Answer: b
Explanation: In the Inner query, the NOT IN Operator excludes all the batsmen who participated
in the T20 World Cup in 2016. Then the result is fed to the outer query and the required output
where the batsmen who participated only in the 2015 ODI World Cup is displayed.

18) Consider the Table given below

TABLE: products

pid customername productname brand price

P1001 Vishwa Goodday Britannia 25

P1002 Santhosh Darkfantasy Sunfeast 35

P1001 Vishwa Bourbon Britannia 20

P1003 Ashwin Oreo Cadbury 30

P1002 Santhosh MarieGold Britannia 10

P1003 Ashwin Nice Sunfeast 30

Choose the appropriate query to print the below output

Output:

Total_Count

a) SELECT count(distinct(pid)) as Total_Count FROM products where brand='Britannia' or

(customername = 'Ashwin' or customername='Santhosh');

b) SELECT sum(distinct(pid)) as Total_Count FROM products where brand='Britannia' and

(customername = 'Ashwin' or customername='Santhosh');

c) SELECT count(distinct(pid)) as Total_Count FROM products where brand='Britannia'

and customername <> 'Ashwin';

d) SELECT sum(distinct(pid)) as Total_Count FROM products where brand='Britannia' and

customername <> 'Ashwin';

Answer: c
Explanation: Initially the table contains 6 rows for pid. The first condition (brand='Britannia')
results in reducing the table to 3 rows as there are only three pid rows that contain Britannia as
their brand. The next condition is executed and the same three rows which contain the pid such
as P1001, P1001 and P1002 remain. The COUNT(DISTINCT(pid)) prints only distinct values
out of the three remaining rows. So, 2 is fetched as the result.

19) Consider the tables given below

TABLE: Account_Holder

Acct_id Name Location

 A1 Aditi Chennai

A2 Elvin Mumbai

A3 Jaydev Nagpur

A4 Axar Delhi

TABLE: Bank_Details

IFSC Bank_Name Bank_Location

B1 HDFC Chennai

B2 ICICI Kanpur

B3 PNB Mumbai

B4 Axis Kolkata

TABLE: Account_Info

Acct_id IFSC Acct_no Acct_Type Balance

A1 B4 3445674 Savings 20000

A1 B2 6471611 Savings 10000

A3 B2 6965147 Credit 40000

A4 B1 7415024 Recurring 15000

Deposits

A3 B2 4405123 Savings 30000

A2 B3 5681662 Credit 45700

Query:

select COUNT(Name) from Account_Holder as A INNER JOIN Account_Info AS AI

ON A.acct_id=AI.acct_id WHERE acct_type IN (SELECT acct_type FROM Account_Info
GROUP BY acct_type HAVING COUNT(acct_type) <>
(SELECT MAX(acct_type) FROM (SELECT COUNT(acct_type) FROM Account_Info GROUP
 BY acct_type))) AND LENGTH(name)<6;

How many number of rows will be obtained as output after successfully executing the given
query?

a) 1
b) 4
c) 3
d) 2

Answer: b
Explanation:

20) Consider the tables Account_Holder, Bank_Details and Account_Info given below

TABLE: Account_Holder

Acct_id Name Location

A1 Aditi Chennai

A2 Elvin Mumbai

A3 Jaydev Nagpur

A4 Axar Delhi

TABLE: Bank_Details

IFSC Bank_Name Bank_Location

B1 HDFC Chennai

B2 ICICI Kanpur

B3 PNB Mumbai

B4 Axis Kolkata
TABLE: Account_Info

Acct_id IFSC Acct_no Acct_Type Balance

A1 B4 3445674 Savings 20000

A1 B2 6471611 Savings 10000

A3 B2 6965147 Credit 40000

A4 B1 7415024 Recurring 15000

Deposits

A3 B2 4405123 Savings 30000

A2 B3 5681662 Credit 45700

Query:

select Name,bank_name from ((Account_Info as AI INNER JOIN Account_Holder AS A ON

AI.Acct_id=A.Acct_id) INNER JOIN Bank_Details AS B ON AI.IFSC=B.ifsc) where acct_type
NOT in ('Savings','Recurring Deposits') GROUP BY name,bank_name ORDER BY name
DESC;

What will the output be for the given query?

a)
Name Bank_Name

Elvin PNB

Jaydev ICICI

b)
Name Bank_Name
 Jaydev ICICI

Axar HDFC

Aditi Axis

Aditi ICICI

c)
Name Bank_Name

Jaydev ICICI

Elvin PNB

d)
Name Bank_Name

Aditi Axis

Aditi ICICI

Axar HDFC

Jaydev ICICI

Answer: c
Explanation: The Inner join performed on the three tables using the common columns returns 6
rows as output. Then the next condition where the account type is not part of either Savings or
Recurring deposits is executed which results in only 2 rows ( Acct_Id - A2, A3). Then the Order
By Keyword sorts the Name in descending order and displays the required output.

21) Consider the tables given below

TABLE: Account_Holder

Acct_id Name Location

A1 Eshwin Chennai
 A2 Elvin Mumbai

A3 Elangovan Nagpur

A4 Axar Delhi

TABLE: Account_Info

Acct_id IFSC Acct_no Acct_Type Balance

A1 B4 3445674 Savings 20000

A1 B2 6471611 Savings 10000

A3 B2 6965147 Credit 40000

A4 B1 7415024 Recurring 15000

Deposits

A3 B2 4405123 Savings 30000

A2 B3 5681662 Credit 45700

Query:

SELECT Name from Account_Holder as A INNER JOIN Account_Info AS I ON

A.acct_id=I.acct_id AND name LIKE '%i%' where balance>20000;

What will the output be for the given query?

a)
Name

Elvin
 Eshwin

b)
Name

Elangovan
Elangovan
Elvin

c)
Name

Elvin

d) No output

Answer: c
Explanation: The Inner join is performed on the given two tables using the common column
‘Acct_id’ which returns 6 rows as output. Then the next condition (Like ‘%i%’) reduces the table
to 3 rows as there are only three values that contain the character ‘i’. Then the final condition
( balance > 20000) returns only one row as the final output as the other 2 rows contain a
balance less than or equal to 20000. So, only one row with the name ‘Elvin’ will be fetched as
the output.

22) Consider the three tables given below

TABLE: Items

Item_id Item_name Category Price Discount Stock

I1 Dream Catcher Showpiece 500 10 63

I2 Cinnamon Candles Home Decor 550 5 35

I3 Watch Box Utilities 2000 20 18

I4 Music Plant Lamp Home Decor 1500 15 5

I5 Crystal Platter Utilities 2999 7 10

I6 Crystal Chariot Showpiece 2000 15 32

I7 Wood Coaster Set Utilities 1300 30 30

I8 Golden Foil Rose Showpiece 500 30 30

I9 Photo Frames Home Decor 500 30 30

TABLE: Buyer

Buyer_id Buyer_name Location

B1 Jack Delhi

B2 John Bangalore

B3 Sam Mumbai

B4 Andrew Bangalore

B5 Anne Delhi

B6 Maria Mumbai

B7 Jeny Bangalore

TABLE: Item_order

Order_id Buyer_id Item_id Quantity Shipping_Destination

1001 B2 I4 2 Delhi

1002 B3 I2 5 Bangalore

1003 B5 I1 3 Bangalore

1004 B2 I4 1 Bangalore
 1005 B1 I3 9 Mysore

1006 B2 I1 8 Mumbai

1007 B5 I6 4 Chennai

1008 B5 I7 4 Chennai

1009 B5 I8 5 Mumbai

1010 B6 I5 6 Mysore

Query:

SELECT DISTINCT io.Order_id, io.Buyer_id, io.Item_id

FROM Item_order AS io, Items AS i, Item_order AS io1
WHERE i.Item_id = io.Item_id AND io.Buyer_id = io1.Buyer_id AND i.Category = 'Home
Decor'
GROUP BY io.Order_id, io.Buyer_id, io.Item_id
HAVING count(io.Buyer_id) > 1

What will the output be for the given query?

a)

Order_id Buyer_id Item_id

1001 B2 I4

1004 B2 I4

b)

Order_id Buyer_id Item_id

 1001 B2 I4

1002 B3 I2

1004 B2 I4

c)

Order_id Buyer_id Item_id

1002 B3 I2

d)
No output

Answer: a
Explanation: The tables Items and Item_order are joined using the common column ‘item_id’
and after applying the condition (Category = 'Home Decor') the table fetches 3 rows with
order_id as 1001,1002,1004 as output. The next condition is applied and the final output
contains 2 rows as they are the rows that satisfy the condition (count(io.Buyer_id) > 1).

23) Consider the three tables given below

TABLE: Items

Item_id Item_name Category Price Discount Stock

I1 Dream Catcher Showpiece 500 10 63

I2 Cinnamon Candles Home Decor 550 5 35

I3 Watch Box Utilities 2000 20 18

I4 Music Plant Lamp Home Decor 1500 15 5

I5 Crystal Platter Utilities 2999 7 10

I6 Crystal Chariot Showpiece 2000 15 32

I7 Wood Coaster Set Utilities 1300 30 30

I8 Golden Foil Rose Showpiece 500 30 30

I9 Photo Frames Home Decor 500 30 30

TABLE: Buyer

Buyer_id Buyer_name Location

B1 Jack Delhi

B2 John Bangalore

B3 Sam Mumbai

B4 Andrew Bangalore

B5 Anne Delhi

B6 Maria Mumbai

B7 Jeny Bangalore

TABLE: Item_order

Order_id Buyer_id Item_id Quantity Shipping_Destination

1001 B2 I4 2 Delhi

1002 B3 I2 5 Bangalore

1003 B5 I1 3 Bangalore

1004 B2 I4 1 Bangalore
 1005 B1 I3 9 Mysore

1006 B2 I1 8 Mumbai

1007 B5 I6 4 Chennai

1008 B5 I7 4 Chennai

1009 B5 I8 5 Mumbai

1010 B6 I5 6 Mysore

Query:

SELECT io.Order_id, io.Item_id, io.Buyer_id FROM Item_order AS io

WHERE io.Buyer_id =
(SELECT b.Buyer_id FROM Buyer AS b WHERE Location = 'Delhi' AND b.Buyer_id
=io.Buyer_id )
AND io.Item_id=
(SELECT i.Item_id FROM Items AS i WHERE Category != 'Utilities' AND i.Item_id =
io.Item_id)
AND io.Order_id =
(SELECT io1.Order_id FROM Item_order AS io1 WHERE Shipping_Destination != 'Chennai'
AND io1.Order_id = io.Order_id)

What will the output be for the given query?

a)

Order_id Item_id Buyer_id

1007 I6 B5

b)

Order_id Item_id Buyer_id

1003 I1 B5
 1009 I8 B5

c)

Order_id Item_id Buyer_id

1005 I3 B1

d)

Order_id Item_id Buyer_id

1001 I4 B2

1002 I2 B3

1004 I4 B2

1006 I1 B2

Answer: b
Explanation: Selecting the three required columns from the table ‘Item_order’ for the first inner
subquery fetches five rows as output as there are two rows with id (B1, B5) which has the
location of Delhi which then joined with Item_order table fetches 5 rows as output. Then the next
subquery is executed where item_id (I1, I2, I4, I6, I8, I9) which doesn’t have the ‘utilities’
category as one of their values are fetched which then further executed with the previous
subquery with AND operator reduces it to three rows. The final subquery is executed next,
where the rows excluding the values which contain the Shipping_Destination as ‘Chennai’ is
fetched. So, the final table is reduced to 2 rows and they are displayed.

24) Consider the three tables given below

TABLE: Items

Item_id Item_name Category Price Discount Stock

I1 Dream Catcher Showpiece 500 10 63

I2 Cinnamon Candles Home Decor 550 5 35

I3 Watch Box Utilities 2000 20 18

I4 Music Plant Lamp Home Decor 1500 15 5

I5 Crystal Platter Utilities 2999 7 10

I6 Crystal Chariot Showpiece 2000 15 32

I7 Wood Coaster Set Utilities 1300 30 30

I8 Golden Foil Rose Showpiece 500 30 30

I9 Photo Frames Home Decor 500 30 30

TABLE: Buyer

Buyer_id Buyer_name Location

B1 Jack Delhi

B2 John Bangalore

B3 Sam Mumbai

B4 Andrew Bangalore

B5 Anne Delhi

B6 Maria Mumbai

B7 Jeny Bangalore

TABLE: Item_order

Order_id Buyer_id Item_id Quantity Shipping_Destination

1001 B2 I4 2 Delhi

1002 B3 I2 5 Bangalore

1003 B5 I1 3 Bangalore
 1004 B2 I4 1 Bangalore

1005 B1 I3 9 Mysore

1006 B2 I1 8 Mumbai

1007 B5 I6 4 Chennai

1008 B5 I7 4 Chennai

1009 B5 I8 5 Mumbai

1010 B6 I5 6 Mysore

Query:

SELECT i.Item_id, i.Item_name, i.Stock, count (io.Item_id) AS No_of_Orders FROM Items AS

i, Item_order AS io WHERE i.Item_id = io.Item_id AND i.Stock > 40 GROUP BY i.Item_id,
i.Item_name, i.Stock HAVING count (io.Item_id) > 1

The Item_names which will be displayed as a result of this query is?

a) Dream Catcher
b) Dream Catcher, Cinnamon Candles, Crystal Chariot
c) Cinnamon Candles, Crystal Chariot
d) None of the above

Answer: a

25) Consider the three tables given below

TABLE: Items

Item_id Item_name Category Price Discount Stock

I1 Dream Catcher Showpiece 500 10 63

I2 Cinnamon Candles Home Decor 550 5 35

I3 Watch Box Utilities 2000 20 18

I4 Music Plant Lamp Home Decor 1500 15 5

I5 Crystal Platter Utilities 2999 7 10

I6 Crystal Chariot Showpiece 2000 15 32

I7 Wood Coaster Set Utilities 1300 30 30

I8 Golden Foil Rose Showpiece 500 30 30

I9 Photo Frames Home Decor 500 30 30

TABLE: Buyer

Buyer_id Buyer_name Location

B1 Jack Delhi

B2 John Bangalore

B3 Sam Mumbai

B4 Andrew Bangalore

B5 Anne Delhi

B6 Maria Mumbai

B7 Jeny Bangalore

TABLE: Item_order

Order_id Buyer_id Item_id Quantity Shipping_Destination

1001 B2 I4 2 Delhi

1002 B3 I2 5 Bangalore

1003 B5 I1 3 Bangalore
 1004 B2 I4 1 Bangalore

1005 B1 I3 9 Mysore

1006 B2 I1 8 Mumbai

1007 B5 I6 4 Chennai

1008 B5 I7 4 Chennai

1009 B5 I8 5 Mumbai

1010 B6 I5 6 Mysore

Query:

select Count(o.Buyer_id) from Items i ,Item_order o WHERE i.Item_id = o.Item_id AND

i.Category NOT IN ('Home Decor','Utilities')

What will the output be for the given query?

a) 7
b) 6
c) 5
d) 4

Answer: d

26) Consider the three tables given below

TABLE: Items

Item_id Item_name Category Price Discount Stock

I1 Dream Catcher Showpiece 500 10 63

I2 Cinnamon Candles Home Decor 550 5 35

I3 Watch Box Utilities 2000 20 18

I4 Music Plant Lamp Home Decor 1500 15 5

I5 Crystal Platter Utilities 2999 7 10

I6 Crystal Chariot Showpiece 2000 15 32

I7 Wood Coaster Set Utilities 1300 30 30

I8 Golden Foil Rose Showpiece 500 30 30

I9 Photo Frames Home Decor 500 30 30

TABLE: Buyer

Buyer_id Buyer_name Location

B1 Jack Delhi

B2 John Bangalore

B3 Sam Mumbai

B4 Andrew Bangalore

B5 Anne Delhi

B6 Maria Mumbai

B7 Jeny Bangalore

TABLE: Item_order

Order_id Buyer_id Item_id Quantity Shipping_Destination

1001 B2 I4 2 Delhi

1002 B3 I2 5 Bangalore

1003 B5 I1 3 Bangalore
 1004 B2 I4 1 Bangalore

1005 B1 I3 9 Mysore

1006 B2 I1 8 Mumbai

1007 B5 I6 4 Chennai

1008 B5 I7 4 Chennai

1009 B5 I8 5 Mumbai

1010 B6 I5 6 Mysore

Query:

select o.shipping_destination,o.quantity from Items i ,Item_order o WHERE i.Item_id =

o.Item_id AND i.Category NOT LIKE '%ec%' GROUP by o.shipping_destination,o.quantity
ORDER by quantity desc;

What will the output be for the given query?

a)

Shipping_Destination Quantity

Mysore 9

Mysore 6

Chennai 4

b)

Shipping_Destination Quantity

Mumbai 8

Bangalore 5

Mumbai 5

Chennai 4
 Bangalore 3

Delhi 2

Bangalore 1

c)

Shipping_Destination Quantity

Chennai 4

Mysore 6

Mysore 9

d)

Shipping_Destination Quantity

Bangalore 1

Delhi 2

Bangalore 3

Chennai 4

Bangalore 5

Mumbai 5

Mumbai 8

Answer: a

27) Consider the tables given below

Table: Catalogue

T_no Parts_no Price

T1 P1 200
 T1 P2 100

T1 P3 150

T2 P4 250

T2 P5 300

T3 P1 300

T3 P2 200

T3 P3 350

T3 P4 300

Table: Traders

T_no T_name Location

T1 Global Traders Chennai

T2 Premium Traders Kanpur

T3 Vinayak Furnitures Mumbai

Table: Parts

Parts_no Parts_name P_type

P1 Door Wood

P2 Coffin Wood

P3 Door Steel

P4 Chair Steel

P5 Chair Wood

Query:
 SELECT T.T_no, T.T_name FROM Traders T, Catalogue C WHERE T.T_no = C.T_no AND
Price > (SELECT AVG (Price) FROM Catalogue WHERE Parts_no = 'P4' GROUP BY
Parts_no);

a) 5
b) 4
c) 0
d) 2

28) Consider the tables given below

Table: Runs

Player Team Runs_Scored

Virat Kohli RCB 6300

David Warner SRH 5400

AB de Villiers RCB 5200

Query:

SELECT Count(*) FROM ((SELECT Player, Team FROM Runs) AS S NATURAL JOIN
(SELECT Team, Runs_Scored FROM Runs) AS T);

a) 5
b) 3
c) 6
d) None of the above

29) Consider the tables given below

Table: Booking

T_id Class Bus_id

0 AC 1200

1 AC 1201

2 SC 1201

5 AC 1203

1 SC 1204

3 AC 1202

Table: Traveller

T_id T_name Age

0 Suresh 45

1 Ram 46

2 Axar 47

3 Rahul 59

Query:

SELECT T_id FROM Booking WHERE class ='AC' AND EXISTS (SELECT * FROM Traveller
WHERE Age > 45 AND Traveller.T_id = Booking.T_id);

a) 1,2
b) 1,0
c) 1,5
 d) 1,3

30) Consider the given schema

Faculties(Faculty_code, f_name, l_name, phone_no, date_of_birth, subject_handled);

Which of the following query would display the last name of all the faculties where the second
letter in the first name is ‘e’?

a) select l_name from Faculties where f_name like ‘_e%’;

b) select l_name from Faculties where f_name like ‘%e_’;
c) select l_name from Faculties where f_name like ‘%e%’;
d) select l_name from Faculties where f_name like ‘e_’;