1 ---033查询每个学生及其选修课程的情况--------------------------------------------------------
2 USE student_course;
3 SELECT Student.*,SC.*
4 FROM Student,SC
5 WHERE Student.Sno=SC.Sno;
6
7 ---034对上题用自然链接完成-----------------------------------------------------------------
8 SELECT Student.Sno/*在等值基础上去掉重复列*/,Sname,Ssex,Sage,Sdept,Cno,Grade
9 FROM Student,SC
10 WHERE Student.Sno=SC.Sno;
11
12 ---035查询每一门课的间接先修课（先修课的先修课）------------------------------------------------
13 SELECT * FROM Course
14 SELECT First.Cno,Second.Cpno
15 FROM Course FIRST , Course SECOND
16 WHERE First.Cpno=Second.Cno;
17
18 ---036改写查询每个学生及其选修课程的情况-----------------------------------------------------
19 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
20 FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);
21 SELECT * FROM Student;
22 SELECT * FROM SC;
23
24 ---037查询选修号课程且成绩在分及以上的所有学生------------------------------------------------
25 SELECT Student.Sno,Sname,Grade
26 FROM Student,SC
27 WHERE Student.Sno=SC.Sno AND Cno='2' AND Grade>='80';
28 SELECT * FROM Student;
29 SELECT * FROM SC;
30
31 ---038查询每个学生的学号、姓名选修的课程名称及成绩----------------------------------------------
32 SELECT Student.Sno,Sname,Cname,Grade
33 FROM Student,Course,SC
34 WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno;
35 SELECT * FROM SC;
36
37 ---【题1】在样例数据库pubs 中查询在同一城市的出版社和作者的信息(内连接)----------------------------
38 USE pubs
39 SELECT p.pub_name, p.city, a.au_fname, a.au_lname
40 FROM publishers AS p INNER JOIN authors AS a ON p.city = a.city
41 ORDER BY pub_name;
42
43 ---【题2】在样例数据库pubs 的authors 表中查询有相同邮政编码的作者。(内连接)-------------------------
44 USE pubs
45 SELECT DISTINCT a1.au_fname, a1.au_lname,a1.zip
46 FROM authors AS a1 INNER JOIN authors AS a2 ON a1.zip = a2.zip
47 WHERE a1.au_id<>a2.au_id
48 ORDER BY a1.zip;
49
50 ---【题3】在样例数据库pubs 中，对表publishers 和authors 以city 列值相等为条件做左外连接查询-------
51 USE pubs
52 SELECT p.pub_name, p.city, a.au_fname, a.au_lname
53 FROM publishers AS p LEFT OUTER JOIN authors AS a ON p.city = a.city
54 ORDER BY pub_name;
55
56 ---【题4】在样例数据库pubs 中，对表publishers 和authors 以city 列值相等为条件做右外连接查询---------
57 USE pubs
58 SELECT p.pub_name, p.city, a.au_fname, a.au_lname
59 FROM publishers AS p RIGHT OUTER JOIN authors AS a ON p.city = a.city
60 ORDER BY pub_name;
61
62 ---【题5】在样例数据库pubs 中，对表publishers 和authors 以city 列值相等为条件做全连接查询-----------
63 USE pubs
64 SELECT p.pub_name, p.city, a.au_fname, a.au_lname
65 FROM publishers AS p FULL OUTER JOIN authors AS a ON p.city = a.city
66 ORDER BY pub_name;
67
68
