Set Operations

MCQ - 1

Ans : a,d

MCQ - 2

MCQ - 3

Ans : c

MCQ - 4

Ans : c

MCQ - 5

Ans : a

MCQ - 6

MCQ - 7

SQL Query - 1

SELECT * FROM Empdept1 UNION

SELECT * FROM Empdept2;

SQL query - 2

SELECT * FROM Empdept1 WHERE job = 'salesman'

UNION ALL

SELECT * FROM Empdept2

WHERE job = 'salesman';

SQL query - 3

Solution:

SELECT EMPFNAME, EMPLNAME, EMPCODE FROM EMPDEPT1

UNION ALL

SELECT EMPFNAME, EMPLNAME, EMPCODE FROM EMPDEPT2 ORDER BY EMPCODE;

SQL query - 4

SELECT item_name, item_type, price FROM shop_1

UNION ALL

SELECT item_name, item_type, price FROM shop_2

ORDER BY PRICE desc;

SQL query - 5

SELECT ITEM_NAME, PRICE FROM SHOP_1 WHERE PRICE>25

UNION ALL

SELECT ITEM_NAME, PRICE FROM SHOP_2 WHERE PRICE>25;

SQL query - 6

SELECT DISTINCT * FROM empdept1 INNER JOIN empdept2

USING (empcode, empfname, emplname, job);

SQL query - 7

SELECT DISTINCT item_name, item_type FROM shop_1 INNER JOIN shop_2

USING (item_name, item_type) WHERE item_type = 'stationery';

SQL query - 8

SELECT DISTINCT item_name, price FROM shop_1 INNER JOIN shop_2

USING(item_name, price) WHERE price >20;

SQL query - 9

SELECT * FROM empdept1 LEFT JOIN empdept2

USING (empcode, empfname, emplname, job)

WHERE empdept2.empcode IS NULL;

SQL query - 10

SELECT item_name, item_type FROM shop_1 LEFT JOIN shop_2

USING(item_name, item_type)

WHERE shop_2.item_id IS NULL

ORDER BY item_name;

SQL query - 11

SELECT item_name, price FROM shop_1 RIGHT JOIN shop_2

USING(item_name, price)

WHERE shop_2.price>50;