?12? SQL????????

1
?12? SQL????????

• 12-1 SQL???????
• 12-2 ??????
• 12-3 ????EquiJoins????
• 12-4 ??????
• 12-5 ??????
• 12-6 ???????
• 12-7 SQL???
• 12-8 ???????

2
12-1 SQL???????-??

• SQL????(Join)????????????,????????????????????????
  ?,???????????,???????????????,??,?????????????????
  ?????
• ???Classes?????????sid?????eid,??????,???????????
  ???????

3
12-1 SQL???????-??

• SQL?????????????????????,????????,???????,????????
  ?,????
• ???????
• ????
• EquiJoins?????
• ???????
• ???????

4
12-1 SQL???????-?????
5
12-1 SQL???????-??????
6
12-2 ??????-??

• SQL?CROSS JOIN???????????????????????(Cartesian
  Product),?????????????????????

7
12-2 ??????-??1

• SQL????Ch12-2-1.sql
• ???Students?????sid?name???Classes??????????c_no??
  ???eid??????????,????
• SELECT Students.sid, Students.name, Classes.c_no,
  Classes.eid
• FROM Students CROSS JOIN Classes

8
12-2 ??????-??2

• SQL????Ch12-2-2.sql
• ??????????WHERE????Students?Classes????????????,??
  ????????sid??,????
• SELECT Students.sid, Students.name, Classes.c_no,
  Classes.eid
• FROM Students
• CROSS JOIN Classes
• WHERE Students.sid Classes.sid

9
12-3 ????EquiJoins????

• 12-3-1 ?????
• 12-3-2 EquiJoins????

10
12-3-1 ?????-??

• ???(Theta Join)???SQL???????????gt???lt???gt???lt??
  ?ltgt?????????2?????

11
12-3-1 ?????-??

• SQL????Ch12-3-1.sql
• ?Classes???????????????????,???????????,????
• SELECT c1.c_no, c1.eid, c1.sid AS C1_SID,
• c1.score AS C1_SCORE, c2.sid AS C2_SID,
• c2.score AS C2_SCORE
• FROM Classes c1, Classes c2
• WHERE c1.c_no c2. c_no
• AND c1.eid c2.eid
• AND c1.score gt c2.score

12
12-3-2 EquiJoins????-??

• EquiJoins??????????????????????????(Relationship)?
  ????,????????????????,?????????
• SELECT table1.column1, table2.column2 ,
  table3.column3
• FROM table1, table2., table3
• WHERE table1.column tabel2.column
• and table1.column table3.column
• EquiJoins????WHERE???????????????????????

13
12-3-2 EquiJoins????-??1

• SQL????Ch12-3-2-1.sql
• ?Students?Classes????????????,????????????,????
• SELECT FROM Students, Classes
• WHERE Students.sid Classes.sid

14
12-3-2 EquiJoins????-??2

• SQL????Ch12-3-2-2.sql
• ?Courses?Classes????????????????????????,????
• SELECT Courses., Classes.room
• FROM Courses, Classes
• WHERE Courses.c_no Classes.c_no

15
12-4 ??????

• 12-4-1 ??????
• 12-4-2 INNER JOIN??

16
12-4 ??????

• ??????????(Natural Join)????SQL???INNER
  JOIN??????,?????

17
12-4-1 ??????-??

• ?????????????SQL??????????????INNER
  JOIN,??,????????EquiJoins?????SQL??,??????????????
  ??

18
12-4-1 ??????-??

• SQL????Ch12-4-1.sql
• ?Students?Classes?????????????????,????
• SELECT Students., Classes.eid,
  Classes.c_no,Classes.time,Classes.room
• FROM Students, Classes
• WHERE Students.sid Classes.sid

19
12-4-2 INNER JOIN??-??

• SQL???INNER JOIN????????????????,?????????????????
  ???

20
12-4-2 INNER JOIN??-??1

• SQL????Ch12-4-2-1.sql
• ???Students?????sid?name???Classes???????????c_no?
  ????eid????????????????????,???????sid,????
• SELECT Students.sid, Students.name, Classes.c_no,
  Classes.eid
• FROM Students
• INNER JOIN Classes
• ON Students.sid Classes.sid

21
12-4-2 INNER JOIN??-??2

• SQL????Ch12-4-2-2.sql
• Transact-SQL??????Ch12-4-2-1.sql???????,??INNER
  JOIN????Courses???,??????????,????
• SELECT Students.sid, Students.name,
• Courses., Classes.eid
• FROM Courses INNER JOIN
• (Students INNER JOIN Classes
• ON Students.sid Classes.sid)
• ON Classes.c_no Courses.c_no

22
12-4-2 INNER JOIN??-??2
23
12-4-2 INNER JOIN??-??3

• SQL????Ch12-4-2-3.sql
• Transact-SQL???????Ch12-4-2-2.sql???????,??INNER
  JOIN????Instructors???,??????????,????
• SELECT Students.sid, Students.name,
• Courses., Instructors.
• FROM Instructors INNER JOIN
• (Courses INNER JOIN
• (Students INNER JOIN Classes
• ON Students.sid
  Classes.sid)
• ON Classes.c_no Courses.c_no)
• ON Classes.eid Instructors.eid

24
12-4-2 INNER JOIN??-??3
25
12-5 ??????

• 12-5-1 LEFT JOIN???????
• 12-5-2 RIGHT JOIN???????
• 12-5-3 FULL JOIN????????

26
12-5 ??????

• SQL???OUTER JOIN??????????????????????,??INNER
  JOIN???????????????????????????????,??????OUTER
  JOIN??,????
• ?????(LEFT JOIN)
• ?????(RIGHT JOIN)
• ??????(FULL JOIN)

27
12-5-1 LEFT JOIN???????-??

• ????????????????,?????????????,??????????????????

28
12-5-1 LEFT JOIN???????-??

• SQL????Ch12-5-1.sql
• Students??????Classes????????????,???????sid,????S
  tudents????????,????
• SELECT Students.sid, Students.name, Classes.c_no,
  Classes.score
• FROM Students LEFT JOIN Classes
• ON Students.sid Classes.sid

29
12-5-1 LEFT JOIN???????-??
30
12-5-2 RIGHT JOIN???????-??

• ????????????????????,??????????????????

31
12-5-2 RIGHT JOIN???????-??1

• SQL????Ch12-5-2-1.sql
• Classes??????Students??????????????,???????sid,???
  ?Students????????,????
• SELECT Classes.c_no, Classes.score, Students.sid,
  Students.name
• FROM Classes RIGHT JOIN Students
• ON Classes.sid Students.sid

32
12-5-2 RIGHT JOIN???????-??1
33
12-5-2 RIGHT JOIN???????-??2

• SQL????Ch12-5-2-2.sql
• ????JOIN????Students?Courses?Classes???,????
• SELECT Students.sid, Students.name, Courses.,
  Classes.eid
• FROM Courses RIGHT JOIN
• (Students INNER JOIN Classes ON Students.sid
  Classes.sid)
• ON Classes.c_no Courses.c_no

34
12-5-2 RIGHT JOIN???????-??2
35
12-5-3 FULL JOIN????????-??

• ???LEFT JOIN????????RIGHT JOIN???????,FULL
  JOIN????????????????????????

36
12-5-3 FULL JOIN????????-??

• SQL????Ch12-5-3.sql
• Students??????Classes?????????????,???????sid,????
  
• SELECT Students.sid, Students.name, Classes.c_no,
  Classes.score
• FROM Students FULL JOIN Classes
• ON Students.sid Classes.sid

37
12-5-3 FULL JOIN????????-??
38
12-6 ???????

• 12-6-1 ?????????
• 12-6-2 UNION????
• 12-6-3 INTERSECT????
• 12-6-4 EXCEPT????

39
12-6-1 ?????????-1

• ??UNION??????????????????,???????,???????,???????
  ??
• SELECT column1, column2 FROM table1
• UNION
• SELECT column1, column2 FROM table2
• ??INTERSECT???????????????,?????????
• SELECT column1, column2 FROM table1
• INTERSECT
• SELECT column1, column2 FROM table2

40
12-6-1 ?????????-2

• ??EXCEPT????????SELECT?????,????????SELECT?????,?
  ????????
• SELECT column1, column2 FROM table1
• EXCEPT
• SELECT column1, column2 FROM table2

41
12-6-2 UNION????

• SQL????Ch12-6-2.sql
• ?Students?Instructors??????name???????????????????
  ,????
• SELECT name
• FROM Students
• UNION
• SELECT name
• FROM Instructors

42
12-6-3 INTERSECT????

• SQL????Ch12-6-3.sql
• ?Students?Instructors??????name???????????????????
  ??????,????
• SELECT name
• FROM Students
• INTERSECT
• SELECT name
• FROM Instructors

43
12-6-4 EXCEPT????

• SQL????Ch13-6-4.sql
• ?Students?Instructors??????name????????????Student
  s???,????Instructors????????,????
• SELECT name
• FROM Students
• EXCEPT
• SELECT name
• FROM Instructors

44
12-7 SQL????

• 12-7-1 ??????
• 12-7-2 ?????????
• 12-7-3 ?????????
• 12-7-4 ?????????

45
12-7 SQL????

• SQL???(Subquery)?????????????,??????SQL???????????
  ???,???????(Nested Query)?
• ????????????SELECT????,????,??????????????,???????
  ?????

46
12-7-1 ??????-??

• ???????SQL????,????????SELECT???WHERE??,??????????
  ??????,?????????SELECT???
• ??SQL?????????,?????????,???????????????,???????

47
12-7-1 ??????-??

• ????????SELECT???WHERE???GOUP BY???HAVING??,??????
  ?????????????,????????,????
• SELECT column1, column2, , columnN
• FROM table1
• WHERE column (SELECT column
• FROM table2
• WHERE conditions)

48
12-7-1 ??????-????

• ??????SQL????????
• ??????SELECT???????????,????????????????
• ??????,???????ORDER BY??,????????ORDER
  BY??,??????GROUP BY??????
• ???????????,????????IN??????
• BETWEEN/AND?????????????,???????????

49
12-7-2 ?????????-??1

• SQL????Ch12-7-2-1.sql
• ?Students???????name??????sid,????Classes?????????
  ???????,????
• SELECT COUNT() FROM Classes
• WHERE sid
• (SELECT sid FROM Students WHERE name'???')

50
12-7-2 ?????????-??2

• SQL????Ch12-7-2-2.sql
• ?Students?????????GPA??????????????,????
• SELECT sid, name, tel, GPA FROM Students
• WHERE GPA gt
• (SELECT AVG(GPA) FROM Students)

51
12-7-3 ?????????-??

• SQL??????EXISTS?IN?????????,EXISTS????????????????
  ??,??????????????
• IN???????????????????????,??????????????

52
12-7-3 ?????????-EXISTS??(??1)

• SQL????Ch12-7-3-1.sql
• ?Students?????Classes??????CS222?????????,??
• SELECT sid, name, tel, GPA FROM Students
• WHERE EXISTS
• (SELECT FROM Classes
• WHERE c_no CS222 and Students.sid
  Classes.sid)

53
12-7-3 ?????????-EXISTS??(??2)

• SQL????Ch12-7-3-2.sql
• ?Classes?Courese????????221-S?100-M?????????,????
  
• SELECT FROM Courses
• WHERE EXISTS
• (SELECT FROM Classes
• WHERE (room221-S or room100-M) and
  Courses.c_noClasses.c_no)

54
12-7-3 ?????????-EXISTS??(??3)

• SQL????Ch12-7-3-3.sql
• ?????,?????????Ch13-6-3-2.sql?SQL??????????,????
• SELECT DISTINCT Courses. FROM Courses, Classes
• WHERE (Classes.room'221-S' or Classes.room'100-M
  ') and Courses.c_noClasses.c_no

55
12-7-3 ?????????-IN??(??1)

• SQL????Ch12-7-3-4.sql
• ?Courses?Classes???????S004???????,????
• SELECT FROM Courses
• WHERE c_no NOT IN
• (SELECT c_no FROM Classes WHERE sid'S004')

56
12-7-3 ?????????-IN??(??2)

• SQL????Ch12-7-3-5.sql
• ?Students?Classes?Instructors?????????????????????
  ,????
• SELECT FROM Instructors
• WHERE eid IN
• (SELECT eid FROM Classes
• WHERE sid(SELECT sid FROM Students
• WHERE name '???'))

57
12-7-3 ?????????-IN??(??3)

• SQL????Ch12-7-3-6.sql
• Ch13-6-3-5.sql???????????????????,????
• SELECT DISTINCT Instructors.
• FROM Instructors, Classes, Students
• WHERE Instructors.eid Classes.eid
• and Classes.sid Students.sid
• and Students.name '???'

58
12-7-4 ?????????-??

• ?SQL??????EXISTS?NOT???????,??????????????????,???
  ?????????
• ?????SQL??????EXISTS?NOT????????
• ????????????,??6?????????????,????
• RnS R (R-S) S-(S-R)

59
12-7-4 ?????????-????(??)

• SQL????Ch12-7-4-1.sql
• ?Students?Instructors??????name????????????Student
  s????????,???????Instructors???,????
• SELECT name FROM Students
• WHERE NOT EXISTS (SELECT name FROM Instructors
  WHERE Instructors.name Students.name )

60
12-7-4 ?????????-????(??)

• ???R?S??????????????,???????,????
• ?????R-S??????????,?Transact-SQL?MySQL
  MaxDB?ANSI-SQL???????
• ??????R-S???????????,??????R
  Temp????,?????R?S?????????

61
12-7-4 ?????????-????(???)

• SQL????Ch12-7-4-2.sql
• ?Students?Instructors??????name????R-S?????,??????
  ??Temp???,????
• SELECT name
• INTO Temp
• FROM Students
• WHERE NOT EXISTS (SELECT name FROM Instructors
• WHERE Instructors.name Students.name
  )

62
12-7-4 ?????????-????(???)

• SQL????Ch12-7-4-3.sql
• ???R (R-S)????,?????R Temp,??????????,????????
  ????????,????
• SELECT name FROM Students
• WHERE NOT EXISTS (SELECT name FROM Temp
• WHERE name Students.name )

63
12-8 ???????

• 12-8-1 UPDATE?JOIN?????
• 12-8-2 DELETE?JOIN?????

64
12-8-1 UPDATE?JOIN?????-??

• SQL????Ch12-8-1.sql
• ?Classes?????????????????300-K?????,?????900am,??
  ??
• UPDATE Classes SET Classes.time 900am
• FROM Classes INNER JOIN Instructors
• ON Classes.eid Instructors.eid
• WHERE Instructors.name ??? and Classes.room
  300-K

65
12-8-1 UPDATE?JOIN?????-??

• ??SQL????Classes???????????,????
• SELECT eid, sid, c_no, room, DATEPART(Hour, time)
  AS hour
• FROM Classes

66
12-8-2 DELETE?JOIN?????-??

• SQL????Ch12-8-2.sql
• ?Classes???????????????300-K?????,????
• DELETE Classes FROM Classes INNER JOIN
  Instructors
• ON Classes.eid Instructors.eid
• WHERE Instructors.name ??? and Classes.room
  300-K

67
12-8-2 DELETE?JOIN?????-??

• ??SQL????Classes???????????,????
• SELECT eid, sid, c_no, room FROM Classes