Link Query

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

 

转载于:https://www.cnblogs.com/FYSUIT/archive/2010/11/27/1890014.html