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
AS (
), q2 -- 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.