Practical Assignment 5
-- 1(i) Cartesian product of Doctor and Patient
SELECT * FROM Doctor, Patient;
-- Output: This will produce a Cartesian product, displaying all combinations of rows from both
tables.
-- (ii) Cartesian product using JOIN syntax
SELECT * FROM Doctor JOIN Patient;
-- Output: This query is incomplete. It should specify the type of join (e.g., CROSS JOIN).
-- (iii) Cartesian product using CROSS JOIN
SELECT * FROM Doctor CROSS JOIN Patient;
-- Output: Same as (i) above, producing all combinations of rows from both tables.
-- (iv) Natural Join
SELECT * FROM Doctor NATURAL JOIN Patient;
-- Output: This query will produce a result where rows are matched based on columns with the
same name in both tables (e.g., DNo).
-- (v) Specific Natural Join with condition
SELECT Dname, Pname FROM Doctor NATURAL JOIN Patient WHERE Dname = 'Amitabh';
-- Output:
-- Dname | Pname
-- Amitabh | Noor
-- Amitabh | Hari
-- (vi) Equi Join using WHERE clause
SELECT * FROM Doctor D, Patient P WHERE D.DNo = P.DNo;
-- Output: Joins the tables on DNo.
-- (vii) Equi Join using JOIN ON
SELECT * FROM Doctor JOIN Patient ON Doctor.DNo = Patient.DNo;
-- Output: Same as (vi), joins on DNo.
-- (viii) Filtering based on Fees
SELECT PName, AdmDate, Fees FROM Patient P, Doctor D WHERE D.DNo = P.DNo AND
Fees > 1000;
-- Output:
-- PName | AdmDate | Fees
-- Noor | 2024-01-25 | 1500
-- Hari | 2024-05-10 | 1500
-- (ix) Count of patients grouped by doctor DNo
SELECT Patient.DNo, COUNT(*) FROM Doctor JOIN Patient ON Doctor.DNo = Patient.DNo
GROUP BY Patient.DNo;
-- Output:
-- DNo | COUNT(*)
-- D1 | 2
-- D2 | 1
-- (x) Count of patients grouped by doctor name
SELECT Dname, COUNT(*) FROM Doctor D, Patient P WHERE D.DNo = P.DNo GROUP BY
Dname;
-- Output:
-- Dname | COUNT(*)
-- Amitabh | 2
-- Aniket | 1
-- (xi) Count of patients per doctor with count > 1
SELECT Dname, COUNT(*) FROM Doctor D, Patient P WHERE D.DNo = P.DNo GROUP BY
Dname HAVING COUNT(*) > 1;
-- Output:
-- Dname | COUNT(*)
-- Amitabh | 2
-- 2(i) Cartesian product of Guardian and Student
SELECT * FROM Guardian, Student;
-- Output: This will produce a Cartesian product, showing all combinations of rows from both
tables.
-- (ii) Equi Join
SELECT * FROM Student S JOIN Guardian G ON S.guid = G.GUID;
-- Output: Shows students and their corresponding guardians based on the matching GUID.
-- (iii) Student details and guardian’s name and phone number
SELECT S.*, G.Gname, G.GPhone FROM Student S JOIN Guardian G ON S.guid = G.GUID;
-- Output: Shows student details along with their guardian’s name and phone number.
-- (iv) Students with guardians missing phone numbers
SELECT S.*, G.Gname, G.GAddress FROM Student S JOIN Guardian G ON S.guid = G.GUID
WHERE G.GPhone IS NULL;
-- Output:
-- rn | sname | sdateofbirth | guid | Gname | GAddress
-- 33 | John | 2002-08-18 | 33 | Danny | Punjabi Bagh, Delhi
-- (v) Students and guardians residing in 'Punjabi Bagh'
SELECT S.*, G.Gname, G.GAddress FROM Student S JOIN Guardian G ON S.guid = G.GUID
WHERE G.GAddress LIKE '%Punjabi Bagh%';
-- Output:
-- rn | sname | sdateofbirth | guid | Gname | GAddress
-- 4 | John | 2002-08-18 | 33 | Danny | Punjabi Bagh, Delhi
-- 46 | Manika| 2002-03-10 | 46 | Sujata| Punjabi Bagh, Delhi
-- (vi) Students and guardians with missing phone numbers
SELECT S.*, G.GAddress FROM Student S JOIN Guardian G ON S.guid = G.GUID WHERE
G.GPhone IS NULL;
-- Output:
-- rn | sname | sdateofbirth | guid | GAddress
-- 33 | John | 2002-08-18 | 33 | Punjabi Bagh, Delhi
-- (vii) Students and guardians not residing in Delhi
SELECT S.*, G.Gname, G.GPhone, G.GAddress FROM Student S JOIN Guardian G ON S.guid
= G.GUID WHERE G.GAddress NOT LIKE '%Delhi%';
-- Output:
-- rn | sname | sdateofbirth | guid | Gname | GPhone | GAddress
-- 55 | Baichung | 2002-02-28 | 11 | Baichung | 7110047139 | Mall, Shimla
-- (viii) Students with roll numbers 3 to 5 and their corresponding guardians' name and phone
SELECT S.rn, S.sname, G.Gname, G.GPhone FROM Student S JOIN Guardian G ON S.guid =
G.GUID WHERE S.rn BETWEEN 3 AND 5;
-- Output:
-- rn | sname | Gname | GPhone
-- 3 | Tasleem | NULL | NULL
-- 4 | John | Danny | NULL
-- 5 | Ali | Sujata | 780293674
-- (ix) Number of students residing in Delhi
SELECT COUNT(*) FROM Student S JOIN Guardian G ON S.guid = G.GUID WHERE
G.GAddress LIKE '%Delhi%';
-- Output: 3
