Joining Tables
MCQ - 1
MCQ - 2
Ans : a
MCQ - 3
Ans : a,d
MCQ - 5
SQL Query - 1
SELECT e.empid,e.empfname,e.emplname,p.projectid,p.projectname
FROM Employee AS e
INNER JOIN
Project AS p
ON e.empid = p.empid;
SQL Query - 2
SELECT e.empid, e.emailid, e.phoneno, c.clientfname, c.clientlname
FROM Employee AS e
INNER JOIN
Client_d AS c
ON e.empid = c.empid
WHERE e.city = 'Delhi' OR c.city='Kolkata';
SQL Query - 3
SELECT p.projectname, c.clientemailid FROM Project AS p
INNER JOIN
Client_d AS c
ON p.clientid = c.clientid
WHERE p.projectstartdate > '2021-04-30'
ORDER BY c.age DESC;
SQL Query - 4
SELECT s.*, sp.*, p.* FROM Supplier AS s
INNER JOIN SP
INNER JOIN product AS p
ON
s.sno= SP.sno AND p.pno = SP.pno;
SQL Query - 5
SELECT p.pname,p.colour, sp.qty FROM Supplier AS s
INNER JOIN SP
INNER JOIN product AS p
ON p.pno = sp.pno AND s.sno = sp.sno
WHERE s.sname = 'sahil';
SQL Query - 6
SELECT p.colour, count(p.pno) AS nop FROM product AS p
INNER JOIN sp
ON p.pno = sp.pno
GROUP BY p.colour;
SQL Query - 7
SELECT s.status, MAX(sp.qty) AS m FROM Supplier AS s
INNER JOIN SP
ON s.sno = sp.sno
GROUP BY s.status;
SQL Query - 8
SELECT s.sname, p.pname , s.status FROM supplier AS s
INNER JOIN product AS p INNER JOIN SP
ON p.pno = sp.pno AND s.sno = sp.sno
ORDER BY sp.qty;
SQL Query - 9
SELECT e.empfname, e.emplname, p.projectid, p.projectname FROM employee e
LEFT JOIN project p ON p.empid = e.empid;
SQL Query - 10
SELECT b.bname, b.bcity, p.pid, p.pdate, p.amount FROM buyer b
LEFT JOIN product p ON b.bid = p.bid
ORDER BY p.pdate;
SQL Query - 11
SELECT b.bname, b.bcity, b.budget, s.sname, s.scity FROM Seller s
LEFT JOIN Buyer b
ON b.sid= s.sid
WHERE b.budget < 3000
ORDER BY b.bid;
SQL Query - 12
SELECT b.bname, b.bcity, p.pid, p.pdate, p.amount, s.sname, s.profit FROM Seller s
JOIN product p ON s.sid=p.sid
JOIN buyer b
ON b.bid=p.bid;
SQL Query - 13
SELECT p.projectid, p.projectname, e.empfname, e.emplname, e.emailid FROM employee e
RIGHT JOIN project p
ON p.empid = e.empid;
SQL Query - 14
SELECT c.clientid, c.clientfname, c.clientlname, c.clientemailid, p.projectid, p.projectname
FROM client_d c RIGHT JOIN project p
ON p.clientid = c.clientid
WHERE c.age BETWEEN 25 AND 35
ORDER BY c.age AND p.projectid;
SQL Query - 15
SELECT b.bname, b.bcity, b.budget, s.sname, s.scity FROM buyer b
RIGHT JOIN seller s
ON b.sid=s.sid
ORDER BY s.sid;
SQL Query - 16
SELECT b.bname, b.bcity, b.budget, s.sname, p.pid, p.pdate, p.amount
FROM buyer b join seller s ON b.sid=s.sid
RIGHT JOIN product p ON b.bid = p.bid;
SQL Query - 17
SELECT e.empfname, e.emplname, p.projectid FROM employee e
CROSS JOIN project p;
SQL Query - 18
SELECT b.bname, b.bcity, p.pid, p.pdate, p.amount FROM buyer b
LEFT JOIN Product p ON b.bid = p.bid
WHERE b.budget>0;
SQL Query - 19
SELECT p.projectid, p.projectname, e.empfname, e.emplname, e.emailid FROM project p
LEFT JOIN employee e
ON p.empid = e.empid
UNION
SELECT p.projectid, p.projectname, e.empfname, e.emplname, e.emailid FROM project p
RIGHT JOIN employee e
ON p.empid = e.empid;
