1 / 16

Tutorial 4 : Basic SQL

Tutorial 4 : Basic SQL. Answer: Consider the following schemas. CUST( cust-id , name ), and WITHDRAW( cust-id , acc-id , date , amount )

julie-avery
Download Presentation

Tutorial 4 : Basic SQL

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. Tutorial 4: Basic SQL

  2. Answer: Consider the following schemas.CUST(cust-id, name), andWITHDRAW(cust-id, acc-id, date, amount) • Write an SQL query to retrieve all the names of the customers who have withdrawn more than 1k dollars in a single withdrawal. If a customer made several such withdrawals, her/his name should be reported only once. • SELECT DISTINCT nameFROM CUST C, WITHDRAW WWHERE C.cust-id = W.cust-id and W.amount > 1k

  3. Answer: Consider the following schemas.CUST(cust-id, name), andWITHDRAW(cust-id, acc-id, date, amount) • Retrieve the acc-id of accounts except those with smallest withdraw amount. • SELECT DISTINCT T1.acc-id FROM WITHDRAW T1, WITHDRAW T2WHERE T1.amount > T2.amount • Note: this query will also return the account with smallest withdraw amount if the same account has been withdrawn for a larger amount. We will fix this query later on.

  4. Answer: Consider the following schemas.CUST(cust-id, name), andWITHDRAW(cust-id, acc-id, date, amount) • Sometimes there may be a “shared” account, namely, an account with multiple owners. • Write an SQL query to return the acc-id of all the shared accounts.You may assume that all the owners of a shared account have made withdrawals from the account. • SELECT T1.acc-idFROM WITHDRAW T1, WITHDRAW T2WHERE T1.cust-id <> T2.cust-id and T1.acc-id = T2.acc-id

  5. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) Q1: Find the names of sailors who havereserved boat number 103. SELECT sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103

  6. Q2: Find the sid’s of sailors who’ve reserved at least one boat Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) SELECTS.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid SELECT sid FROM Reserves OR

  7. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) Q3:Find the names of sailors who’ve reserved at least one boat SELECT sname FROM Sailors S, Reserves R WHERE S.sid=R.sid

  8. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) Q4:Find the sid’s of sailors who’ve reserved a red boat. SELECT sid FROM Reserves R, Boats B WHERE R.bid=B.bid AND B.color=‘red’

  9. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) Q5:Find the names of sailors who’ve reserved a red boat. SELECT sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid and R.bid=B.bid and B.color=‘red’

  10. Q6:Find the sid’s of sailors who’ve reserved at least two different boats on the same day. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) SELECTR1.sid FROM Reserves R1, Reserves R2 WHERE R1.sid=R2.sid and R1.day=R2.day and R1.bid<>R2.bid

  11. Q7:Find the names of sailors who’ve reserved at least two different boats on the same day. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) SELECT sname FROM Sailors S, Reserves R1, Reserves R2 WHERE S.sid = R1.sid and R1.sid=R2.sid and R1.day=R2.day and R1.bid<>R2.bid

  12. Q8:Find sid’s of sailors who’ve reserved a red or a green boat Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) SELECT sid FROM Boats B, Reserves R WHERE R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’) (SELECT sid FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’) UNION (SELECT sid FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘green’) OR

  13. Q9:Find sid’s of sailors who’ve reserved a red and a green boat Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) SELECTR1.sid FROM Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE (R1.bid=B1.bid AND B1.color=‘red’) AND (R2.bid=B2.bid ANDB2.color=‘green’) AND R1.sid=R2.sid (SELECT sid FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’) INTERSECT (SELECT sid FROM Boats B, Reserves R WHERE R.bid=B.bidANDB.color=‘green’) OR

  14. Q10:Find sid’s of sailors with age over 20 who have not reserved a red boat Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) (SELECT sid FROM Sailors WHERE age > 20) EXCEPT (SELECT sid FROM Boats B, Reserves R WHERE R.bid=B.bidAND B.color=‘red’)

  15. bid bname color 101 Interlake blue 102 Interlake red 103 Clipper green 104 Marine red Reserves R1 Reserves R2 Sailors Boats

  16. bid bid bname bname color color 101 101 Interlake Interlake blue blue 102 102 Interlake Interlake red red 103 103 Clipper Clipper green green 104 104 Marine Marine red red Reserves R1 Boats B1 Reserves R2 Boats B2

More Related