数据库期末复习资料汇总

1、1、查询全体学生姓名，学号，性别SELECT学号 ,姓名 ,性别FROM XSQK2、查询学生全部信息SELECT *FROM XSQK3、查询选修了课程的学生的学号SELECT学号FROM XS_KCGROUP BY学号4、查询全体学生的姓名(将姓名改为NAME), 学号 ,性别SELECT DISTINCT姓名NAME, 学号 ,性别FROM XSQK1二、条件查询1、查询选修课程为101 的学生的学号和成绩SELECT学号 ,成绩FROM XS_CKWHERE课程号 =101用课本作业做：2、查询分数在 70 分以上的学生选课信息，并按成绩降序排列SELECT *FROM SCWHERE

2、 GR>70ORDER BY GR DESC3、在 SC表中按学号分组汇总学生的平均分， 并按平均分的降序排列。SELECT S#,平均分 =AVG(GR)FROM SCGROUP BY S#ORDER BY平均分 DESC或SELECT S#,AVG(GR)AS平均分1 / 7FROM SCGROUP BY S#ORDER BY平均分DESC4、在 SC表中查询选修了3 门以上课程的学生学号。SELECT S#FROM SCGROUP BY S#HAVING COUNT(*)>35、按学号对不及格的成绩记录进行明细汇总。SELECT S#,C#,GRFROMSCWHEREGR&l

3、t;60ORDER BY S#COMPUTECOUNT(GR)BYS#6、 分别用子查询和连接查询，求107 号课程不及格的学生信息。子查询：SELECT学号 ,姓名 ,联系电话FROMXSQKWHERE学号IN(SELECT学号FROM XS_KCWHERE课程号 ='107' AND成绩 <60)连接查询：SELECT XSQK学.号 , 姓名 , 联系电话FROM XSQKJOIN XS_KC ON XSQK学.号 =XS_KC学.号WHERE课程号 ='107'AND 成绩 <607、用连接查询在 XSQK表中查询住在同一寝室的学生，即其联系

4、电话相同2 / 7SELECT A学.号 ,A. 姓名 ,A. 联系电话FROM XSQK AJOIN XSQK B ON A.联系电话 =B.联系电话WHERE A学.号 <>B.学号8、查询 XSQK表中所有系名SELECT DISTINCT所在系FROM XSQK9、查询有多少同学选修了课程。SELECT COUNT(*)AS选修了课程的人数FROM XSQKWHERE学号 IN(SELECT学号FROM XS_KC)10、查询有多少同学没有选课。SELECT COUNT(*)AS没有选修课程的人数FROM XSQKWHERE学号 NOT IN(SELECT学号FROM XS

5、_KC)11、查询与杨颖同一个系的同学姓名。SELECT姓名FROM XSQKWHERE所在系 =(SELECT所在系FROM XSQKWHERE姓名 =' 杨颖 ')12、查询选修了课程的学生的姓名、课程名与成绩。SELECT姓名 , 课程名 , 成绩FROM XS_KC,XSQK,KCWHERE (XS_KC学.号 =XSQK学.号 ) AND (XS_KC. 课程号 =KC.课程号 ) ANDXSQK.学号 =ANY(SELECT学号FROM XS_KC)13、统计每门课程的选课人数和最高分。SELECT课程名 ,COUNT(*)AS 选此课人数 ,MAX(成绩 )AS

6、最高分FROM XS_KC,KCWHERE XS_KC课.程号 =KC.课程号GROUP BY课程名14、统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。错误程序：SELECTXSQK学.号 , 姓名 ,COUNT(*)AS 选课门数 ,SUM(成绩 )AS 总成绩FROM XS_CK,XSQKWHERE XS_CK学.号 =XSQK学.号3 / 7GROUP BY姓名ORDER BY选课门数 DESC正确程序：SELECT学号 , 姓名 ,COUNT(*)AS 选课门数 ,SUM(成绩 )AS 总成绩 FROM XS_CK,XSQKWHERE XS_CK学.号 =XSQK学.号G

7、ROUP BY姓名ORDER BY选课门数 DESC15、WHERE子句与 HAVING子句有何不同？答：二者的根本区别在于作用对象不同， WHERE自居作用于基本表或视图，从中选择满足条件的元祖； HAVING子句作用于组，选择面租条件的组，必须用在 GROUP BY子句之后，但 GROUP BY子句可没有 HAVING子句。建立和使用视图：1、创建一个简单视图，查询“计算机系”学生的信息。CREATE VIEW计算机系学生信息AS SELECT*FROM XSQKWHERE所在系 =' 计算机 '4 / 72、创建一个简单视图，统计每门课程的选课人数和最高分。CREATE

8、VIEW每门课人数和最高分AS SELECT课程名 ,COUNT(*)AS 选此课人数 ,MAX(成绩 )AS 最高分 FROM XS_KC,KCWHERE XS_KC课.程号 =KC.课程号GROUP BY课程名3、创建一个复杂视图，查询与“俞奇军”住在同一寝室的学生信息，即其联系电话相同。CREATE VIEW与俞奇军同学住同一寝室的学生信息AS SELECT*FROM XSQKWHERE联系电话 =(SELECT联系电话FROM XSQKWHERE姓名 =' 俞奇军 ')4、创建一个复杂视图，查询选修了课程的同学的姓名，课程名及成绩。CREATE VIEW选课的同学姓名课

9、程名及成绩AS SELECT姓名 , 课程名 , 成绩FROM XS_KC,XSQK,KCWHERE (XS_KC学.号 =XSQK学.号 ) AND (XS_KC. 课程号 =KC.课程号 ) ANDXSQK.学号 =ANY(SELECT学号FROM XS_KC)课本上第 135 页四、 5 答案（1）用 SQL 的 DDL 语言创建S 表， S#为主键， SN 不能为空。CREATE TABLE S(S# CHAR(10) CONSTRAINT S_PRIM PRIMARY KEY,SN CHAR(10) NULL,AGE INT,DEPT VARCHAR(20)（2）创建计算机系学生的视

10、图，该视图的属性列由学号、姓名、课程号和任课教师号组成。CREATE VIEW C_STUDENT(S#,SN,C#,T#)AS SELECT S.S#,SN,C.C#,T#FROM S,SC,C,TWHERE S.S#=SC.S# AND C.C#=SC.C# AND T.C#=SC.C#（3）检索计算机系年龄在20 岁以上的学生学号。SELECT S#FROM SWHERE(DEPT=' 计算机 ')AND(AGE>=20)(4) 检索姓王的教师所讲课程的课程号及课程名称。错误程序：SELECT C.C#,CN5 / 7FROM T,CWHERE(T.C#=C.C#)

11、AND(TN='王 %')正确程序：SELECT C.C#,CNFROM T,CWHERE(T.C#=C.C#)AND(TN LIKE王 % )错误分析 :此处王 %为模糊查询，而=为精确查询，故应该用LIKE 语句查询。（ 5）检索张三同学课程的成绩，列出SN,C#,GR 。SELECT SN,C#,GRFROM S,SCWHERE (S.S#=SC.S#)AND(SN=张三 )（ 6）检索选修总收入超过1000 元的教师所讲课程的学生姓名、课程号和成绩。SELECT SN,C#,GRFROM S,T,SCWHERE(S.S#=SC.S#)AND(T.C#=SC.C#)AND

12、(SAL+COMM>=1000)（ 7）检索没有选修 C1 课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列。SELECT SN,A VG(GR) AS A VGGRFROM S,SCWHERE NOT EXISTS(SELECT*FROM SCWHERE S#=S.S# AND C#='C1')GROUP BY SC.S#,S.SNHAVING(COUNT(*)=2)ORDER BY 2DESC（ 8）检索选修和张三同学所选课程中任意一门相同的学生姓名，课程名。SELECT SN,CNFROM S,SC,CWHERE S.S#=SC.S# AND C.

13、C#=SC.C# AND SC.C#=ANY(SELECT C#FROM S,SCWHERE S.S#=SC.S# AND SN=' 张三 ')（ 9） S1 同学选修了C3 ，将此信息插入SC 表中。INSERT INTO SC(S#,C#)VALUES('S1','C3')（ 10）删除 S 表中没有选修任何课程的学生记录。DELETEFROM SWHERE S.S# NOT IN(SELECT SC.S#FROM SC)6 / 7（ 1）建立存书表和销售表；CREATE TABLE存书(书号VARCHAR(10) PRIMARY KEY,书名VARCHAR(20),出版社CHAR(20),版次INT,出版日期DATETIME,作者CHAR(10),书价NUMERIC(4,1),进价NUMERIC(4,1),数量INT )CREATE TABLE销售(日期DATETIME,书号VARCHAR(10) PRIMARY KEY,数量INT,金额NUMERIC(4,1) )(2)SELECT书名 ,数量 ,进价 *数量AS 余额FROM存书(3)SELECT SUM( 数量 * 金额 )AS 总销售额FROM销售（ 4）SELECT