Cycle 02

Lab Cycle 2 — SQL Operators, Aggregate Functions, Grouping & Joins

3 Questions
Question 03 of 03

Comprehensive Relational Joins (Inner, Natural, Outer)

Problem Statement

Write SQL queries to demonstrate all join types between Supplier, Part, and Supplies: 1. Perform Inner Join on Supplier and Supplies. 2. Perform Natural Join on tables. 3. Perform Left Outer Join on Supplier and Supplies. 4. Perform Right Outer Join on Supplies and Part. 5. Perform Full Outer Join on Supplier and Supplies.

C++ 
-- 1. Inner Join on Supplier and Supplies
SELECT s.SID, s.Sname, s.branch, sp.PID, sp.qty, sp.date_supplied
FROM Supplier s
INNER JOIN Supplies sp ON s.SID = sp.SID;

-- 2. Natural Join on Supplier and Supplies (matches on common column SID)
SELECT *
FROM Supplier
NATURAL JOIN Supplies;

-- 3. Left Outer Join (all suppliers, even those who have not supplied any parts)
SELECT s.SID, s.Sname, s.city, sp.PID, sp.qty, sp.date_supplied
FROM Supplier s
LEFT OUTER JOIN Supplies sp ON s.SID = sp.SID;

-- 4. Right Outer Join (all parts, even those that have never been supplied)
SELECT p.PID, p.Pname, p.price, sp.SID, sp.qty, sp.date_supplied
FROM Supplies sp
RIGHT OUTER JOIN Part p ON sp.PID = p.PID;

-- 5. Full Outer Join (all suppliers and all supplies combined with NULLs)
SELECT s.SID AS sup_sid, s.Sname, sp.PID AS sup_pid, sp.qty, sp.date_supplied
FROM Supplier s
FULL OUTER JOIN Supplies sp ON s.SID = sp.SID;
Question 3 of 3