本文共 4462 字,大约阅读时间需要 14 分钟。
为管理学员培训信息,建立3个表: S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄 C (C#,CN ) C#,CN 分别代表课程编号、课程名称 SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名 --实现代码: SELECT SN,SD FROM S WHERE [S#] IN( SELECT [S#] FROM C,SC WHERE C.[C#]=SC.[C#] AND CN=N'税收基础') 2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位 --实现代码: SELECT S.SN,S.SD FROM S,SC WHERE S.[S#]=SC.[S#] AND SC.[C#]='C2' 3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位 --实现代码: SELECT SN,SD FROM S WHERE [S#] NOT IN( SELECT [S#] FROM SC WHERE [C#]='C5') 4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位 --实现代码: SELECT SN,SD FROM S WHERE [S#] IN( SELECT [S#] FROM SC RIGHT JOIN C ON SC.[C#]=C.[C#] GROUP BY [S#] HAVING COUNT(*)=(SELECT COUNT(*) FROM C) ); 5. 查询选修了课程的学员人数 --实现代码: SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC 6. 查询选修课程超过5门的学员学号和所属单位 --实现代码: SELECT SN,SD FROM S WHERE [S#] IN( SELECT [S#] FROM SC GROUP BY [S#] HAVING COUNT(DISTINCT [C#])>5) 题目2 问题描述: 已知关系模式: S (SNO,SNAME) 学生关系。SNO 为学号,SNAME 为姓名 C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师 SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩 1. 找出没有选修过“李明”老师讲授课程的所有学生姓名 --实现代码: SELECT SNAME FROM S WHERE NOT EXISTS( SELECT * FROM SC,C WHERE SC.CNO=C.CNO AND CNAME='李明' AND SC.SNO=S.SNO) 2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩 --实现代码: SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE) FROM S,SC,( SELECT SNO FROM SC WHERE SCGRADE<60 GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2 )A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME 3. 列出既学过“1”号课程,又学过“2”号课程的所有学生姓名 --实现代码: SELECT S.SNO,S.SNAME FROM S,( SELECT SC.SNO FROM SC,C WHERE SC.CNO=C.CNO AND C.CNAME IN('1','2') GROUP BY SNO HAVING COUNT(DISTINCT CNO)=2 )SC WHERE S.SNO=SC.SNO 4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号 --实现代码: SELECT S.SNO,S.SNAME FROM S,( SELECT SC1.SNO FROM SC SC1,C C1,SC SC2,C C2 WHERE SC1.CNO=C1.CNO AND C1.NAME='1' AND SC2.CNO=C2.CNO AND C2.NAME='2' AND SC1.SCGRADE>SC2.SCGRADE )SC WHERE S.SNO=SC.SNO 5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩 --实现代码: SELECT S.SNO,S.SNAME,SC.[1号课成绩],SC.[2号课成绩] FROM S,( SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE FROM SC SC1,C C1,SC SC2,C C2 WHERE SC1.CNO=C1.CNO AND C1.NAME='1' AND SC2.CNO=C2.CNO AND C2.NAME='2' AND SC1.SCGRADE>SC2.SCGRADE )SC WHERE S.SNO=SC.SNO 题目3: INNER JOIN返回的结果集是两个表中所有相匹配的数据。 inner Join 内连接,也就是一般的相等连接 用途: 当你要从两个或者以上的表中选取结果集时,你就会用到INNER JOIN。 例: “Employees”表中的数据如下, OID Name 01 Hansen, Ola 02 Svendson, Tove 03 Svendson, Stephen 04 Pettersen, Kari “Orders”表中的数据如下: ID Product 01 Printer 03 Table 03 Chair 用Employees的ID和Orders的ID相关联选取数据: SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE Employees.ID = Orders.ID 返回结果: Name Product Hansen, Ola Printer Svendson, Stephen Table Svendson, Stephen Chair 或者你也可以用JOIN关键字来完成上面的操作: SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.ID = Orders.ID INNER JOIN的语法: SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield 题目4: 外联接。外联接可以是左向外联接、右向外联接或完整外部联接。 在 FROM 子句中指定外联接时,可以由下列几组关键字中的一组指定: LEFT JOIN 或 LEFT OUTER JOIN。 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。 RIGHT JOIN 或 RIGHT OUTER JOIN。 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。 FULL JOIN 或 FULL OUTER JOIN。 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。 仅当至少有一个同属于两表的行符合联接条件时,内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行,只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行,以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。 1. left outer join:左外关联 select e.last_name, e.department_id, d.department_name from employees e left outer join departments d on (e.department_id = d.department_id); 结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录。 2. right outer join:右外关联 select e.last_name, e.department_id, d.department_name from employees e right outer join departments d on (e.department_id = d.department_id); 结果为:所有员工及对应部门的记录,包括没有任何员工的部门记录。 3. full outer join:全外关联 select e.last_name, e.department_id, d.department_name from employees e full outer join departments d on (e.department_id = d.department_id); 结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。 select * from tf_purview where purviewcode in (select purviewcode from tf_purview group by purviewcode having count(*) > 1) order by purviewcode转载地址:http://iblmi.baihongyu.com/