数据库期末复习资料

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 GR70ORDER BY GR DESC3、在SC表中按学号分组汇总学生的平均分，并按平均分的降序排列。SELECT S#,平均分=AVG(GR)FROM SCGROUP BY S#ORDER BY 平均分 DESC或SELECT S#,AVG(GR)AS 平均分FROM SCGROUP BY S#ORDER BY 平均分 DESC4、在SC表中查询选修了3门以上课程的学生学号。SELECT S#FROM SCGROUP BY S#HAVING COUNT(*)35、按学号对不及格的成绩记录进行明细汇总。SELECT S#,C#,GRFROM SCWHERE GR60ORDER BY S#COMPUTE COUNT(GR)BY S#6、分别用子查询和连接查询，求107号课程不及格的学生信息。子查询：SELECT 学号,姓名,联系电话FROM XSQKWHERE 学号 IN(SELECT 学号FROM XS_KCWHERE 课程号=107 AND 成绩60)连接查询：SELECT XSQK.学号,姓名,联系电话FROM XSQKJOIN XS_KC ON XSQK.学号=XS_KC.学号WHERE 课程号=107AND 成绩607、用连接查询在XSQK表中查询住在同一寝室的学生，即其联系电话相同SELECT 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_KC)11、查询与杨颖同一个系的同学姓名。SELECT 姓名FROM XSQKWHERE 所在系=(SELECT 所在系FROM XSQKWHERE 姓名=杨颖)12、查询选修了课程的学生的姓名、课程名与成绩。SELECT 姓名,课程名,成绩FROM XS_KC,XSQK,KCWHERE (XS_KC.学号=XSQK.学号) AND (XS_KC.课程号=KC.课程号) AND XSQK.学号=ANY(SELECT 学号FROM XS_KC)13、统计每门课程的选课人数和最高分。SELECT 课程名,COUNT(*)AS 选此课人数,MAX(成绩)AS 最高分FROM XS_KC,KCWHERE XS_KC.课程号=KC.课程号GROUP BY 课程名14、统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。错误程序：SELECT XSQK.学号,姓名,COUNT(*)AS 选课门数,SUM(成绩)AS 总成绩FROM XS_CK,XSQKWHERE XS_CK.学号=XSQK.学号GROUP BY 姓名ORDER BY 选课门数 DESC正确程序：SELECT 学号,姓名,COUNT(*)AS 选课门数,SUM(成绩)AS 总成绩FROM XS_CK,XSQKWHERE XS_CK.学号=XSQK.学号GROUP BY 姓名ORDER BY 选课门数 DESC15、 WHERE子句与HAVING子句有何不同？答：二者的根本区别在于作用对象不同，WHERE自居作用于基本表或视图，从中选择满足条件的元祖；HAVING子句作用于组，选择面租条件的组，必须用在GROUP BY子句之后，但GROUP BY子句可没有HAVING子句。建立和使用视图：1、 创建一个简单视图，查询“计算机系”学生的信息。CREATE VIEW 计算机系学生信息AS SELECT*FROM XSQKWHERE 所在系=计算机2、创建一个简单视图，统计每门课程的选课人数和最高分。CREATE 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 选课的同学姓名课程名及成绩AS SELECT 姓名,课程名,成绩FROM XS_KC,XSQK,KCWHERE (XS_KC.学号=XSQK.学号) AND (XS_KC.课程号=KC.课程号) AND XSQK.学号=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）创建计算机系学生的视图，该视图的属性列由学号、姓名、课程号和任课教师号组成。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#,CNFROM T,CWHERE(T.C#=C.C#)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(SAL+COMM=1000)（7）检索没有选修C1课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列。SELECT SN,AVG(GR) AS AVGGRFROM S,SCWHERE NOT EXISTS(SELECT*FROM SCWHERE S#=S.S# AND C#=C1)GROUP BY SC.S#,S.SNHAVING(COUNT(*)=2)ORDER BY 2 DESC（8）检索选修和张三同学所选课程中任意一门相同的学生姓名，课程名。SELECT SN,CNFROM S,SC,CWHERE S.S#=SC.S# AND C.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)（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 日期,书名,存书.数量,销售.数量*金额 AS 合计金额FROM 存书,销售WHERE 存书.书号=销售.书号GROUP BY 书名,日期,存书.数量,销售.数量*金额（5）SELECT 书名,销售.数量 FROM 存书,销售WHERE 存书.书号= 销售.书号 AND 销售.数量100 AND 日期+30(SELECT MAX(日期) FROM 销售)