SQL 2008/12/09

NeoYang

第一題:找出在Classes表中 沒有選課紀錄的學生名字

1. Select name From Students
   
2. Where Not Exists(
   
3.         Select * From Classes
   
4.         Where Classes.sid = Students.sid
   
5. )

複製代碼

第二題:找出'李鴻章'開設所有課之學生名字

1. Select name From Students
   
2. Where Not Exists(
   
3.         (Select c_no From Instructors,Classes
   
4.         Where name = '李鴻章' And Instructors.eid = Classes.eid
   
5.         )Except(
   
6.         Select c_no From Classes
   
7.         Where Students.sid = Classes.sid)
   
8. )

複製代碼

第三題:更新Classes資料表 講師'李鴻章'上課教室'500-K'改為'300-K'

1. Update Classes Set Classes.room = '300-K'
   
2. From Classes Inner Join Instructors On Classes.eid = Instructors.eid
   
3. Where Instructors.name = '李鴻章' And Classes.room = '500-K'

複製代碼