DB_hw2_hudson

							hw2.txt Database Hw2 B91902083 張席維 1 ======== 因為之前比較常用 SQL 所以在這邊有點直覺就使用比較類似 SQL 的方法去弄了... n-join: nature join p: rename column rename: rename table select: project where: select x: cross a. select person-name( where company-name = First Bank ( works)) b. select person-name, city( select person-name( where company-name = First Bank ( works)) n-join employee) c. select person-name, street, city( select person-name( where company-name = First Bank and salary > 10000( works) ) n-join employee) d. select person-name( employee n-join works n-join company) e. select person-name( p( c( 1->manager-name), employee) n-join manages n-join employee) f. select person-name( where company-name != First Bank( works)) g. select person-name( where salary_compare < salary( p( c( 1-> salary_compare), select salary( where company-name = Small Bank ( works)select person-name, company-name, salary( where salary_compare > salary( p( c( 1-> salary_compare), select salary( where company-name = Small Bank ( works) ) x works ) ) ) ) x where conpany-name != Small Bank ( works) ) ) )

第 1 頁

h. select company-name( select city( where company-name = Small Bank ( company)) n-j oin company) i. rename( temp, select person-name, company-name( works)) select p1( where p1= p2 and p2= p3 and c1!= c2 and c2!= c3 and c3!= c1( p( c( 1-> p1, 2-> c1, 3-> p2, 4-> c2, 5-> p3, 6-> c3), temp x temp x temp ) ) ) 2 ======== a.

PDF created with pdfFactory Pro trial version www.pdffactory.com

hw2.txt SQL

第 2 頁

SELECT timetable.[Planet's Name] FROM timetable WHERE (((timetable.[Character's Name])='princess leia') AND ((timetable.Movie)=3)); Result Planet's Name Tatooine Endor b. SQL SELECT Count( [Character's Name]) FROM timetable WHERE (((timetable.Movie)=3) and [planet's name]='dagobah'); Result Expr1000 3 c. SQL select distinct [character's name] from timetable, characters where timetable.movie = 2 and timetable.[planet's name]=characters.homeworld and timetable.[character's na me]=characters.name; Result character's name d. SQL select [character's name] from (select [character's name], count([planet's name]) as [planet number] from (select distinct timetable.[character's name], timetable.[plan et's name] from timetable, planets where timetable.[planet's name]=planets.name and planets.affiliation='rebels') group by [character's name]) as temp1, (select count(n ame) as [total number] from planets where affiliation='rebels') as temp2 where [plan et number]=[total number]; Result character's name e. SQL select distinct timetable.[planet's name] from characters, timetable where timetable .[character's name]=characters.name and characters.race='droid'; Result planet's name Bespin Dagobah Endor Hoth

PDF created with pdfFactory Pro trial version www.pdffactory.com

hw2.txt Tatooine f. SQL

第 3 頁

select timetable.[character's name], timetable.[planet's name], sum( timetable.[time of departure]- timetable.[time of arrival]) as [total time] from timetable, planets where timetable.[planet's name]=planets.name and planets.affiliation='neutral' grou p by timetable.[character's name], timetable.[planet's name]; Result character's name planet's name C-3 PO Bespin 4 C-3 PO Tatooine 4 Chewbacca Bespin 4 Chewbacca Endor 5 Chewbacca Tatooine 4 Darth Vader Bespin 5 Han Solo Bespin 4 Han Solo Endor 5 Han Solo Tatooine 4 Jabba the Hutt Tatooine 22 Lando Calrissian Bespin 9 Lando Calrissian Endor 1 Lando Calrissian Tatooine 2 Luke Skywalker Bespin 2 Luke Skywalker Dagobah 5 Luke Skywalker Endor 3 Luke Skywalker Tatooine 3 Obi-Wan Kanobi Tatooine 2 Owen Lars Tatooine 1 Princess Leia Bespin 4 Princess Leia Endor 5 Princess Leia Tatooine 2 R2-D2 Bespin 2 R2-D2 Dagobah 5 R2-D2 Endor 3 Rancor Tatooine 23 Yoda Dagobah 25 g. SQL select [character's name], movie from timetable, planets, characters where [characte r's name]=characters.name and [planet's name]=planets.name and (characters.race='hum an' and planets.type='desert' or characters.race='droid' and planets.type='swamp') o rder by [character's name], movie; Result character's name Han Solo 1 Han Solo 3 Lando Calrissian Luke Skywalker 1 Luke Skywalker 3 Obi-Wan Kanobi 1 Owen Lars 1 Princess Leia 3 R2-D2 2 R2-D2 3 h. SQL movie 3 total time

PDF created with pdfFactory Pro trial version www.pdffactory.com

hw2.txt

第 4 頁

select [character's name], t1.movie from (select [character's name], movie, count([p lanet's name]) as [planet number] from timetable group by [character's name], movie) as t1, (select movie, max([planet number]) as [max planet] from (select [character' s name], movie, count([planet's name]) as [planet number] from timetable group by [c haracter's name], movie) group by movie) as t2 where [planet number]=[max planet] an d t1.movie=t2.movie; Result character's name Luke Skywalker 1 Luke Skywalker 2 Luke Skywalker 3 R2-D2 2 i. SQL select [planet's name], t1.movie from (select [planet's name], movie, count([charact er's name]) as [char number] from timetable group by [planet's name], movie) as t1, (select movie, max([char number]) as [max char] from (select [planet's name], movie, count([character's name]) as [char number] from timetable group by [planet's name], movie) group by movie) as t2 where [char number]=[max char] and t1.movie=t2.movie; Result planet's name Bespin 2 Tatooine 1 Tatooine 3 j. SQL select t2.movie, t2.race, t2.name from ( select movie, race, max(slot) as maxslot fr om ( select timetable.movie, characters.race, characters.name, count( [time of arriv al] ) as slot from timetable, characters where timetable.[character's name]=characte rs.name group by timetable.movie, characters.race, characters.name ) group by movie, race ) as t1, ( select timetable.movie, characters.race, characters.name, count( [t ime of departure] - [time of arrival] ) as slot from timetable, characters where tim etable.[character's name]=characters.name group by timetable.movie, characters.race, characters.name ) as t2 where t1.movie=t2.movie and t1.race= t2.race and t1.maxslot = t2.slot; Result movie race name 1 Droid C-3 PO 1 Human Luke Skywalker 1 Hutt Jabba the Hutt 1 Rancor Rancor 1 Unknown Yoda 1 Wookie Chewbacca 2 Droid R2-D2 2 Human Luke Skywalker 2 Hutt Jabba the Hutt 2 Rancor Rancor 2 Unknown Yoda 2 Wookie Chewbacca 3 Droid R2-D2 3 Human Luke Skywalker 3 Hutt Jabba the Hutt 3 Rancor Rancor 3 Unknown Yoda 3 Wookie Chewbacca movie movie

PDF created with pdfFactory Pro trial version www.pdffactory.com

hw2.txt k. SQL

第 5 頁

select planets.[name] from planets where planets.name not in ( select timetable.[pla net’ s name] as name from timetable where timetable.movie=1 or timetable.movie=2 ); character's name Luke Skywalker 1 Luke Skywalker 2 Luke Skywalker 3 R2-D2 2 movie

PDF created with pdfFactory Pro trial version www.pdffactory.com