SQL Sailorssid integer sname string rating integer age

SQL

• Sailors(sid: integer, sname: string, rating: integer, age: real) • Boats(bid: integer, bname: string, color: string) • Reserves(sid: integer, bid: integer, day: dates)

Tum denizcilerin isimlerini ve yaslarini bulunuz SELECT sname, age FROM Sailors

18 yasindan kucuk denizcilerin tum bilgilerini listeleyin SELECT * FROM Sailors WHERE age < 18

103 numarali botu reserve eden denizcilerin isimlerini bulunuz SELECT S. sname FROM sailors S, Reserves R WHERE S. sid = R. sid AND R. bid = 103

Kirmizi bot reserve eden denizcilerin sid’lerini bulunuz SELECT R. sid FROM Reserves R, Boats B WHERE R. bid = B. bid AND B. color = ‘red’

Kirmizi bot reserve eden denizcilerin isimlerini bulunuz SELECT S. sname FROM Sailors S, Reserves R, Boats B WHERE S. sid = R. sid AND R. bid = B. bid and B. color = ‘kirmizi’

Bob isimli denizci tarafindan reserve edilen botlarin renklerini bulunuz SELECT B. color FROM Boats B, Reserves R, Sailors S WHERE B. bid = R. bid AND R. sid = S. sid AND S. sname = ‘Bob’

Ayni gun icinde iki farkli bot reserve eden denizcilerin isimlerini bulunuz SELECT S. sname FROM Sailors S, Reserves R 1, Reserves R 2 WHERE S. sid = R 1. sid AND R 1. sid = R 2. sid AND R 1. day = R 2. day AND R 1. bid <> R 2. bid

Kirmizi veya yesil bir bot reserve eden denizcilerin isimlerini bulunuz SELECT S. sname FROM Sailors S, Reserves R, Boats B WHERE S. sid = R. sid AND R. bid = B. bid AND (B. color = ‘kirmizi’ OR B. color = ‘yesil’)

Hem kirmizi hem de yesil bot reserve eden denizcilerin isimlerini bulunuz SELECT S. sname FROM Sailors S, Reserves R 1, Reserves R 2, Boats B 1, Boats B 2 WHERE S. sid = R 1. sid AND S. sid = R 2. sid AND R 1. bid = B 1. bid AND R 2. bid = B 2. bid AND B 1. color = ‘kirmizi’ AND B 2. color = ‘yesil’

UNION, INTERECT, EXCEPT, IN op ANY, op ALL EXIST KUME ISLEMLERI

UNION – Kume Bilesimi Kirmizi ya da yesil bot reserve etmis denizcilerin isimlerini bulunuz SELECT S. sname FROM Reserves R, Boats B, Sailors S WHERE R. bid= B. bid AND B. color = ‘kirmizi’ and R. sid = S. sid UNION SELECT S. sname FROM Reserves R, Boats B, Sailors S WHERE R. bid= B. bid AND B. color = ‘yesil’ and R. sid = S. sid

INTERSECT– Kume Kesisimi Kirmizi ve yesil bot reserve etmis denizcilerin isimlerini bulunuz SELECT S. sname FROM Reserves R, Boats B, Sailors S WHERE R. bid= B. bid AND B. color = ‘kirmizi’ and R. sid = S. sid INTERSECT SELECT S. sname FROM Reserves R, Boats B, Sailors S WHERE R. bid= B. bid AND B. color = ‘yesil’ and R. sid = S. sid

EXCEPT: Kume farki Kirmizi bot reserve edip yesil bot reserve etmemis olan denizcilerin isimlerini bulunuz SELECT S. sname FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid AND R. bid = B. bid AND B. color = ‘kirmizi’ EXCEPT SELECT S. sname FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid AND R. bid = B. bid AND B. color = ‘yesil’

IN: Kumenin elemani 103 numarali botu reserve eden denizcilerin isimlerini bulunuz SELECT S. sname FROM Sailors S WHERE S. sid IN (SELECT R. sid FROM Reserves R WHERE R. bid = 103) IC i. CE girmis dongu

Hic kirmizi bot reserve etmemis denizcilerin isimlerini bulunuz SELECT S. sname FROM Sailors S WHERE S. sid NOT IN (SELECT R. sid FROM Reserves R WHERE R. bid IN (SELECT B. bid FROM Boats B WHERE B. color = ‘kirmizi’))

op ANY: op herhangi birinden Derecesi Ali’nin derecesinden daha yuksek olan denizcilerin isimlerini bulunuz SELECT S. sname FROM Sailors S WHERE S. rating > ANY(SELECT S 2. rating FROM Sailors S 2 WHERE S 2. sname= ‘Ali’)

Derecesi en yuksek olan denizcinin ismini bulunuz SELECT S. sname FROM Sailors S WHERE S. rating >= ALL (SELECT S 2. rating FROM Sailors S 2)