4.15

Rewrite the query

SELECT * 
FROM section NATURAL JOIN classroom 

without using a natural join but instead using an inner join with a using condition.

---

SELECT c.building,c.room_number,course_id,sec_id,semester,year,time_slot_id,capacity
FROM section INNER JOIN classroom c USING (building, room_number); 

Bonus:

To check if the above queries give same results we can use the following query:-

WITH q1 AS (
    -- q1 stands for query 1
    SELECT * 
    FROM section NATURAL JOIN classroom 
), q2 AS (
    -- q2 stands for query 2 
    SELECT c.building,c.room_number,course_id,sec_id,semester,year,time_slot_id,capacity
    FROM section INNER JOIN classroom c USING (building, room_number)
) 
-- we need to check that result of q1 is a subset of q2 and 
-- result of q2 is a subset of q1
SELECT 
    NOT EXISTS (
        (SELECT * FROM q1)
        EXCEPT
        (SELECT * FROM q2)
    ) 
    
    AND 
    
    NOT EXISTS (
        (SELECT * FROM q2) 
        EXCEPT 
        (SELECT * FROM q1)
    )

On my instance of the database in postgres the above query gives ‘t’ meaning true.