1 / 17

Advanced SQL Techniques: Retrieve Sailors Who Reserved Boat #103

This advanced SQL query explores multiple ways to retrieve the names of sailors who have reserved boat #103. It includes using different join types such as INNER JOIN, LEFT OUTER JOIN, and NATURAL JOIN. The query showcases how to interact with Sailors and Reserves tables efficiently by leveraging SQL's powerful join capabilities. Additionally, it demonstrates techniques to filter results based on specific conditions, allowing you to recognize and utilize complex SQL structures effectively for data retrieval tasks in relational databases.

schuyler
Télécharger la présentation

Advanced SQL Techniques: Retrieve Sailors Who Reserved Boat #103

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SELECT Statement Chapter (7): Advanced SQL

  2. Find names of sailors who’ve reserved boat #103 Sailors Reserves SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103

  3. Find names of sailors who’ve reserved boat #103 Sailors Reserves SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND bid=103

  4. CROSS JOIN Sailors Reserves SELECT * FROM Sailors S, Reserves R SELECT * FROM Sailors S CROSS JOINReserves R

  5. INNER-JOIN Sailors Reserves SELECT * FROM Sailors S, Reserves R WHERE S.sid=R.sid SELECT * FROM Sailors S INNER JOINReserves R ON S.sid=R.sid SELECT * FROM Sailors S INNER JOINReserves R USING sid

  6. EQUI-JOIN Reserves Sailors SELECT * FROM Sailors S, Reserves R WHERE S.sid=R.sid SELECT * FROM Sailors S INNER JOINReserves R ON S.sid=R.sid SELECT * FROM Sailors S INNER JOINReserves R USING sid

  7. NATURAL-JOIN SELECT * FROM Sailors S INNER JOINReserves R USING sid SELECT * FROM Sailors S NATURAL JOINReserves R USING sid

  8. LEFTOUTER-JOIN Report all the sailors with their reservations SELECT * FROM Sailors S, Reserves R WHERE S.sid=R.sid SELECT * FROM Sailors S LEFT OUTER JOINReserves R WHERE S.sid=R.sid

  9. LEFTOUTER-JOIN Report all the sailors with their reservations SELECT * FROM Sailors S, Reserves R WHERE S.sid=R.sid SELECT * FROM Sailors S NATURAL LEFT OUTER JOINReserves R WHERE S.sid=R.sid

  10. RIGHT OUTER-JOIN Report all the boats with their reservations Reserves Boats SELECT * FROM Reserves R RIGHT OUTER-JOINBoats B WHERE R.bid=B.bid

  11. Find sailors’ names who’ve reserved at least one boat Reserves Sailors SELECTDISTINCT S.sname FROM Sailors S, Reserves R WHERES.sid=R.sid

  12. Find names of sailors who’ve reserved a redor a green boat SELECTS.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid ANDR.bid=B.bid AND(B.color=‘red’ ORB.color=‘green’) SELECTS.sname FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid ANDB.color=‘red’ UNION SELECTS.sname FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid ANDR.bid=B.bidANDB.color=‘green’

  13. Find names of sailors who’ve reserved a redanda green boat SELECTS.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid ANDR.bid=B.bid AND(B.color=‘red’ AND B.color=‘green’) SELECTS.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid AND R.bid=B.bid ANDB.color=‘red’ INTERSECT SELECTS.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid ANDR.bid=B.bidANDB.color=‘green’

  14. Find names of sailors who’ve reserved a redanda green boat SELECTS.sid FROM Sailors S, Boats B, Reserves R WHERES.sid=R.sidANDR.bid=B.bidANDB.color=‘red’ ANDS.sidIN (SELECT S2.sid FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=‘green’) SELECTS.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid AND R.bid=B.bid ANDB.color=‘red’ INTERSECT SELECTS.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid ANDR.bid=B.bidANDB.color=‘green’

  15. Find names of sailors who’ve reserved a redand nota green boat SELECTS.sid FROM Sailors S, Boats B, Reserves R WHERES.sid=R.sidANDR.bid=B.bidANDB.color=‘red’ ANDS.sidNOT IN(SELECT S2.sid FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=‘green’) SELECTS.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid AND R.bid=B.bid ANDB.color=‘red’ EXCEPT SELECTS.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid ANDR.bid=B.bidANDB.color=‘green’

  16. SELF-JOIN

  17. VIEW CREATE VIEW Emp_Manager AS SELECT* FROMEmp_Manager SELECTMANAGER FROMEmp_Manager SELECTMANAGER FROMEmp_Manager WHERE EMPLOYEEID = 123 Security …

More Related