Microsoft Word - Sailor-SQL-Exercise.doc

Size: px

Start display at page:

Download "Microsoft Word - Sailor-SQL-Exercise.doc"

崩诚骆
6 years ago
Views:

1 Tables: 水手 Sailors (sid, sname, rating, age) 預訂 Reserves (sid, bid, day) 船隻 Boats (bid, bname, color) Sailors 水手 Reserves 預訂 SID SNAME RATING AGE SID BID DAY 10 Dustin Brutus Art Bob Frodo Dustin Horatio Lubber Andy Horatio Zorba Rusty 水手船隻預訂日期 Boats 船隻 BID BNAME COLOR 101 Interlake blue 102 Interlake red 103 Clipper green 104 Marine red 109 HKSAR pink // drop tables if exist. drop table Sailors; drop table Reserves; drop table Boats; // create tables create table Sailors( sid int not null primary key, sname varchar(20), rating int, age decimal(4,1) create table Boats( bid int not null primary key, bname varchar(20), color varchar(20) 預訂 create table Reserves( sid int, bid int, day date, primary key (sid,bid,day), for Q.39 foreign key (sid) references Sailors(sid) ON DELETE CASCADE, foreign key (bid) references Boats(bid) ON DELETE CASCADE ~ 1 ~

2 insert into Sailors values (22,'Dustin',7,45.0), (29,'Brutus',1,33.0), (31,'Lubber',8,55.5), (32,'Andy',8,25.5), (58,'Rusty',10,35.0), (64,'Horatio',7,35.0), (71,'Zorba',10,16.0), (74,'Horatio',9,35.0), (85,'Art',3,25.5), (95,'Bob',3,63.5 Exercises: insert into Boats values (101,'Interlake','blue'), (102,'Interlake','red'), (103,'Clipper','green'), (104,'Marine','red'), (109,'HKSAR','pink' insert into Reserves values (22,101,' '), (22,102,' '), (22,103,' '), (22,104,' '), (31,102,' '), (31,103,' '), (31,104,' '), (64,101,' '), (64,102,' '), (74,103,' ' 1) "Find all sailors with a rating above 7" 所有水手 (rating 高於 7) 2) "Find sailor name, boat id and date for each reservation" 船隻預訂紀錄 3) "Find the names and ages of all sailors" 所有水手姓名年齡 4) Find the names of sailors that are 30 or older 所有水手姓名 ( 年齡 30 或以上 ) 5) Find the names of boats that have been reserved on 01/10/2010 在某天有哪些預訂船隻 ( 名稱 ) 6) "Find the sid of sailors who have reserved a red boat" 曾預訂紅色船隻 7) sailors whose name begins and ends with B and has at least 3 characters 所有水手姓名 ( 至少 3 英文字母, 以 B 開始及結束 ) 8) "Find the average age of all sailors" 水手平均年齡 9) "Find the average age of sailors with a rating of 10" 10) Find duplicate sailor names 找出姓名相同的水手 11) Find for each boat, the total no of reservations 計算每艘船的預訂總數 (a) show boat id (b) show boat name 12) Find for each boat name, the no of different sailors that reserved it 計算每艘船曾被多少個不同的水手預訂 13) Find for each boat, the no of different sailors that reserved it on 01/10/2010 ( 同上, 在某天 ) 14) "Find the sid & names of sailors who have reserved boat number 103" // 那些水手曾預訂 boat#103: 22,31,74 ~ 2 ~

3 15) "Find the names of sailors who have never reserved boat number 103" // 那些水手從未預訂 boat#103: 29,32,58,64,71,85,95 16) "Find the names of sailors who have reserved a red boat" // 那些水手曾預訂 red boat: Dustin, Lubber, Horatio 17) "Find the colors of boats reserved by Lubber" 他曾預訂過船隻的顏色 // red, green 18) "Find the names of sailors who have reserved at least one boat" // 那些水手曾預訂過船隻 : Dustin, Lubber, Horatio 19) "Find the names of sailors who have reserved a red or a green boat" // 那些水手曾預訂過船隻 (red or green): Dustin, Lubber, Horatio 20) "Find the names of sailors who have reserved both red and green boat" // 那些水手曾預訂過船隻 (red and green): Dustin, Lubber 21) "sid of sailors with age over 20 who have not reserved a red boat" // 年齡 >20, 從未預訂過 red boat: Brutus, Andy, Rusty, Horatio(74), Art, Bob 22) "names of sailors who have reserved at least two different boats" // 至少預訂過 2 隻不同船隻 : Dustin, Lubber, Horatio 23) "Find sid of all sailors who have reserved green but not red boats" 只預訂過 green boat, 但未預訂過 red boat: 顯示 sid 24) "Find the names of sailors who have reserved green but not red boat" // 只預訂過 green boat, 但未預訂過 red boat: 顯示水手姓名 Horatio 25) "Find the sid and names of sailors who have not reserved a red boat" 從未預訂過 red boat: 26) "Find sailors whose rating is greater than sailor called Horatio" // rating 大於 (a) 任何一個 (b) 所有名叫 Horatio 的水手 (a) some/any 任何一個 (>min) (b) every/all 所有 (>max) 27) "Find the sailors with the highest rating" 最高 rating 的水手 28) "Find the name and age of the oldest sailor" 最年長的水手 29) "Count the number of sailors" 水手的總人數 30) "Count the number of different sailor names" 水手姓名的總數 ( 不可重複 ) 31) Find boats NOT reserved by the sailor with sid=31. 水手 #31 未租過的船隻 ~ 3 ~

4 32) "Find the names of sailors who are older than the oldest sailor with a rating of 10" 那些水手比 (rating10 中最年長的水手 ) 更年長 33) "Find the age of the youngest sailor for each rating level" 每 rating 組別中, 最年輕的水手 34) "Find the age of the youngest sailor who is eligible to vote (age>=18) // for each rating level with at least 2 such sailors" 每 rating 組別中, 超過 18 而最年輕的水手, 每組別必須有至少 2 人 35) "Find the no of reservations for each red boat" 每艘 red boat 的預訂次數 36) "Find age of sailors for each rating level that has at least 2 sailors" 每 rating 組別中, 水手的 (a) 平均年齡 (b) 最高年齡, 每組別必須有至少 2 人 (a) average (b) maximum 37) For each boat, identify sid the of sailor who made the highest no of reservations. 每一艘船, 預訂次數最多的水手及次數 insert into Reserves values (64,101,' '), (64,102,' '), (31,103,' '), (31,104,' ' 38) Identify the boat with most reservations. 那艘船的預訂次數最多 39) For each boat, find the number of times it has been reserved; including those boats that have never been reserved (list boat id and name). 每一艘船 ( 包括沒有預訂的船隻 ) 的預訂次數 40) Identify sailors who have never reserved a red boat. 哪些水手從未預訂過 red boat. 41) Find the sailors who have at least 10 reservations for red boats. 哪些水手至少預訂過 10 次 red boat 42) Show All reservations made 所有預訂船隻資料 (sid,sname,bid,bname,color) Harder 51) Find sailors who have reserved all red boats 哪些水手已預訂過所有紅色船 22,31 52) List sailors who have reserved only green boats. 哪些水手只預訂過綠色船 74 53) "Find sailors who have reserved all boats" 哪些水手已預訂過所有船隻 Dustin 54) "Find the names of sailors who have reserved all boats called Interlake" 哪些水手已預訂過所有名為 Interlake 的船隻 Dustin, Horatio 55) For each sailor, list all boats which have never been reserved by that sailor 列出每個水手從未租用過的船隻 (sid, sname, bid, bname) ~ 4 ~

5 56) List the most recent reservation (bid, bname, sid, sname) for each boat. 每艘船最後 ( 最近 ) 一次出租紀錄 sid bname bid sname day 64 Interlake 101 Horatio Interlake 102 Horatio Clipper 103 Lubber Marine 104 Lubber ) Create a table LastReservation (sid, bname, bid, sname, day) that contains for every sailor the most recent date when the sailor has made a reservation and the boat he/she has reserved. 找出每個水手最後 ( 最近 ) 一次出租紀錄, 並建立新檔 LastReservation (sid, bname, bid, sname, day) sid bname bid sname day 64 Interlake 102 Horatio Clipper 103 Horatio Marine 104 Dustin Marine 104 Lubber ) For each sailor, who has at least ten reservations for red boats, increase the rating by 1. 凡租用紅色船達 10 次 ( 或以上 ) 的水手, 把 rating 增加 1 59) "Compute increments for the ratings of persons who have sailed two different boats on the same day" 凡在同一天內租用兩艘不同船隻的水手, 把 rating 增加 1 60) "Find the sailor ids with top 5 rating ranks." 找出 rating 最高的 5 名水手 ~ 5 ~

coverage2.ppt

coverage2.ppt Satellite Tool Kit STK/Coverage STK 82 0715 010-68745117 1 Coverage Definition Figure of Merit 2 STK Basic Grid Assets Interval Description 3 Grid Global Latitude Bounds Longitude Lines Custom Regions