SQL语句习题--汇总

1、数据库试题集汇总设某商业集团关于商店销售商品的数据库中有三个基本表：商店SHOP（S#，SNAME，AREA，MGR_NAME）其属性是商店编号，商店名称，区域名，经理姓名。销售SALE（S#，G#， QUANTITY）其属性是商店编号，商品编号，销售数量。商品GOODS（G#，GNAME，PRICE）其属性是商品编号，商品名称，单价。36试写出下列查询的关系代数表达式、元组表达式和关系逻辑规则：检索销售“冰箱”的商店的编号和商店名称。37试写出上面第36题的SELECT语句表达形式。并写出该查询的图示形式。38试写出下列操作的SQL语句：从SALE表中，把“开开商店”中销售单价高于1000元

2、的商品的销售元组全部删除。39写一个断言，要求区域名为“EAST”的商店里销售商品的单价不能低于100元。40试写出下列操作的SQL语句：统计区域名为“EAST”的所有商店销售的每一种商品的总数量和总价值。要求显示（G#，GNAME，SUM_QUANTITY，SUM_VALUE），其属性为商品编号、商品名称、销售数量、销售价值。37解：SELECT语句如下：SELECT A.S#，SNAMEFROM SHOP A，SALE B，GOODS CWHERE A.S#=B.S# AND B.G#=C.G# AND GNAME='冰箱'； 38解：DELETE FROM SALEWHE

3、RE S# IN（SELECT S# FROM SHOP WHERE SNAME='开开商店'） AND G# IN（SELECT G#FROM GOODS WHERE PRICE>1000）；39解：CREATE ASSERTION ASSE8 CHECK（100<=ALL（SELECT PRICE FROM SHOP A，SALE B，GOODS C WHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'）；或 CREATE ASSERTION ASSE8 CHECK（NOT EXISTS（SELECT * F

4、ROM SHOP A，SALE B，GOODS C WHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST' AND PRICE<100）；40解：SELECT C.G#，GNAME，SUM（QUANTITY）AS SUM_QUANTITY，PRICE*SUM（QUANTITY）AS SUM_VALUEFROM SHOP A，SALE B，GOODS CWHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'GROUP BY C.G#，GNAME；（注：SELECT子句中的属性C.G#，G

5、NAME应在分组子句中出现）36设数据库中有两个基本表：职工表 EMP（E#，ENAME，AGE，SALARY，D#），其属性分别表示职工工号、姓名、年龄、工资和工作部门的编号。部门表 DEPT（D#，DNAME，MGR#）,其属性分别表示部门编号、部门名称和部门经理的职工工号。试指出每个表的主键和外键。并写出每个表创建语句中的外键子句。37 在第36题的两个基本表中，写出下列查询的关系代数表达式和SQL语句：检索每个部门经理的工资，要求显示其部门编号、部门名称、经理工号、经理姓名和经理工资。36答：EMP表的主键为E#，外键为D#。DEPT表的主键为D#，外键为MGR#在EMP表的创建语句中

6、,可写一个外键子句：FOREIGN KEY D# REFERENCES DEPT（D#）；在DEPT表的创建语句中,可写一个外键子句：FOREIGN KEY MGR# REFERENCES EMP（E#）；MGR#=E#37答：关系表达式为：DEPT.D#，DNAME，MGR#，ENAME，SALARY（DEPT EMP）SELECT语句为：SELECT DEPT.D#，DNAME，MGR#，ENAME，SALARYFROM DEPT，EMPWHERE MGR#=E#；31设某商业集团为仓库存储商品设计了三个基本表：仓库STORE（S#，SNAME，SADDR），其属性是仓库编号、仓库名称和地

7、址。存储SG（S#，G#，QUANTITY），其属性是仓库编号、商品编号和数量。商品GOODS（G#，GNAME，PRICE），其属性是商品编号、商品名称和单价现检索仓库名称为“莘庄”的仓库里存储的商品的编号和名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和SELECT语句。32在第31题的基本表中，检索存储全部种类商品的仓库的编号及名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和SELECT语句。33在第31题的基本表中，检索每个仓库存储商品的总价值。试写出相应的SELECT语句。要求显示（S#，SUM_VALUE），其属性为仓库编号及该库存储商品的总价值。34在第3

8、1题的基本表中，写一个断言，规定每个仓库存储商品的单价为1万元以上的商品种类最多为20种。31解：关系代数表达式为：G#，GNAME（SNAME= '莘庄'（STORESGGOODS）元组表达式为： t | ($u) ($v) ($w) (GOODS(u)SG(v)STORE（w）u1=v2v1=w1w2= '莘庄't1=u1t2=u2)关系逻辑规则如下：W（x，y）¬GOODS（x，y，a）SG（b，x，c）STORE（b，'莘庄'，d）SELECT语句为：SELECT A.G#，GNAMEFROM GOODS AS A，SG AS

9、B，STORE AS CWHERE A.G#=B.G# AND B.S#=C.S# AND SNAME= '莘庄'；32解：关系代数表达式为：S#，SNAME（STORE（S#，G#（SG）÷G#（GOODS）元组表达式为： t | ($u) ("v) ($w) (STORE(u) GOODS（v） SG(w) w1=u1 w2=v1 t1=u1 t2=u2)关系逻辑规则如下：W（x，y） STORE（x，y，a）GOODS（b，c，d）SG（x，b，e）SELECT语句为：SELECT S#，SNAMEFROM STOREWHERE NOT EXISTS（

10、SELECT *FROM GOODSWHERE NOT EXISTS（SELECT *FROM SGWHERE SG.S#=STORE.S#AND SG.G#=GOODS.G#）；33解：SELECT语句为：SELECT S#，SUM（QUANTITY *PRICE）AS SUM_VALUEFROM SG，GOODSWHERE SG.G#=GOODS.G#GROUP BY S#；34解：CREATE ASSERTION ASSE6 CHECK（20>=ALL（SELECT COUNT（SG.G#）FROM SG，GOODSWHERE SG.G#=GOODS.G# AND PRICE>

11、;10000GROUP BY S#）；现有关系数据库如下： 部门（部门编号，部门名称，电话）员工(员工编号，姓名，性别，部门编号，职务) 项目(项目编号，项目名称，预算) 施工(员工编号，项目编号，工时)用关系代数表达式实现下列12小题：1检索“王小毛”的员工的部门编号和职务；2检索工时超过100小时的项目名称和员工的姓名； 用SQL语言实现下列37小题：3查询每个员工的姓名，并按职称降序排列；4查询总工时在480小时以上（含480小时）的员工编号和总工时；5检索职称为“高级工程师”，同时至少参加了一预算在100万以上的项目的员工的姓名和部门名称；6定义一个工作量视图，包含的属性有员工编号、姓

12、名、项目名称和工时。7对项目名称为“高教新区项目”的施工工时增加50小时；1.部门编号，职务 (姓名=王小毛(员工) 2. 项目名称，姓名(工时)100(施工)项目员工)3.SELECT 姓名 FROM 员工 ORDER BY 职称 DESC4SELECT 员工编号，SUM（工时） FROM 施工 GROUP BY 员工编号 HAVING SUM（工时）>=480 5. SELECT 姓名，部门名称 FROM 员工，部门，项目，施工WHERE 部门.部门编号=员工.部门编号 AND 员工.员工编号=施工.员工编号 AND 施工.项目编号=项目.项目编号 AND 职称=高级工程师 AND

13、预算>10000006. CREATE VIEW 工作量( 员工编号，姓名，项目名称，工时)AS SELECT员工.员工编号，姓名，项目名称，工时FROM 员工，项目，施工WHERE员工.员工编号=施工.员工编号AND 施工.项目编号=项目.项目编号 7.UPDATE 施工SET 工时=工时+50 WHERE 项目编号 IN (SELECT 项目编号FROM 项目WHERE 项目名称=高教新区项目)37. 图书出版管理数据库中有两个基本表： 图书 (书号，书名，作者编号，出版社，出版日期) 作者 (作者编号，作者名，年龄，地址) 试用SQL语句写出下列查询：检索年龄低于作者平均年龄的所有

14、作者的作者名、书名和出版社。 39. 学校有多名学生，财务处每年要收一次学费。为财务处收学费工作设计一个数据库，包括两个关系： 学生 (学号，姓名，专业，入学日期) 收费 (学年，学号，学费，书费，总金额) 假设规定属性的类型：学费、书费、总金额为数值型数据；学号、姓名、学年、专业为字符型数据；入学日期为日期型数据。列的宽度自定义。试用SQL语句定义上述表的结构。(定义中应包括主键子句和外键子句) 37. SELECT 作者名，书名，出版社 FROM 图书，作者 WHERE 图书 . 作者编号 = 作者 . 作者编号 AND 年龄 < = (SELECT AVG (年龄) FROM 作者

15、)； 39. CREATE TABLE 学生 (学号 CHAR (8)， 姓名 CHAR (8)， 专业 CHAR (13)， 入学日期 DATE， PRIMARY KEY (学号)； CREATE TABLE 收费 (学年 CHAR (10)， 学号 CHAR (8)， 学费 NUMERIC (4)， 书费 NUMERIC (5，2)， 总金额 NUMERIC (7，2)， PRIMARY KEY (学年，学号)， FOREIGN KEY (学号) REFERENCES 学生 (学号)； 现有关系数据库如下：学生(学号，姓名，性别，专业，奖学金)课程(课程号，名称，学分)学习(学号，课程号，

16、分数)5.检索不学课程号为“C135”课程的学生信息，包括学号，姓名和专业；6.检索至少学过课程号为“C135”和“C219”的学生信息，包括学号、姓名和专业；7.从学生表中删除成绩出现过0分的所有学生信息；8.定义“英语”专业学生所学课程的信息视图AAA，包括学号、姓名、课程号和分数。5.SELECT 学号，姓名，专业FROM 学生WHERE 学号 NOT IN(SELECT 学号FROM 学习WHERE 课程号='C135')6. SELECT 学号，姓名，专业FROM 学生WHERE 学号 IN(SELECT X. 学号FROM 学习  X， 学习 YWHERE

17、 X.学号=Y.学号 AND X.课程号='C135' AND Y.课程号=C219)7.DELETE FROM 学生WHERE 学号 IN(SELECT 学号FROM 学习WHERE 分数=0)8.CREATE VIEW AAA(学号，姓名，课程号，分数)AS SELECT 学号，姓名，课程号，分数FROM 学生，学习WHERE 学生.学号 =学习.学号 AND 专业=英语1 用SQL的有关语句定义1） 学生关系Student，包括学号Sno、姓名SN、年龄SA；2） 课程关系Course，包括课程号Cno、课程名CN、任课教师CT；3） 学生选课关系，包括Sno、Cno和成

18、绩G。注意：说明主键码和外键码（如果有的话），并在键码属性上建立索引。（12分）2 按题1的学生关系模式，用SQL的有关语句1） 授予赵华对学生关系Student的插入和修改权限；2） 插入任意3个学生的完整信息；3） 修改所有学生的年龄（加1）。（12分）3 结合题1定义的学生选课数据库，用SQL语句查询计算机系学生数据库课的成绩，输出学生姓名和成绩，按成绩排序（降序），若成绩相同，再按学号排序（升序）。（6分）11）CREATE TABLE Student(Sno INT PRIMARY KEY,SN CHAR(30),SA INT);2)CREATE TABEL Course( Cno

19、INT PRIMARY KEY, CN CHAR(30), CT CHAR(30);3)CREATE TABEL SC( Sno INT, Cno INT, G INT, PRIMAYR KEY(Sno,Cno), FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno);21）GRANT INSERT, UPDATE, ON Student TO 赵华； 2） INSERT INTO Student(TABLE (01001,赵华,19),(01002,李芳,18),(01003,刘红,

20、20),);3）UPDATE Student SET SA=SA+1;3SELECT SN,G FROM Student,Course,SCWHERE Student.Sno=SC.Sno AND Course.Cno=SC.CnoAND Course.CN=数据库ORDER BY G DESC,Sno;2000011李刚男22计算机课程表CourseCnoCnameCpnoCcredit1024数据库原理113641136离散数学112841137管理学 41156英语 61128高等数学61111体育3学生选课表SCSnoCnoGrade2000012115680200

21、0113115689200025611569320000141156882000256113777200027811378920000121024802000014113690200001211367820000121137702000014102488成绩类别表typeLowest_gradeHighest_grade优秀85100良7584及格6074不及格059第一章课件：编写基本的SQL语句。1 查询所有学生情况。2 Select * from student;3 查询所有学生的姓名，性别以及年龄。4 Select sname,ssex,sage from student;5 查询所有

22、学生10年后的年龄。6 Select sage+10 10年后 from student;7 查询所有课程（列名用中文显示）。8 Select cname 课程名 from course;9 查看竟有那些学生选课（重复学号显示一次）。10 Select distinct sno from course where cno is not null;11 显示课程表的边结构。12 Desc course;第二章课件：约束和排序数据。01查询计算机系的所有学生的姓名和年龄。Select sname,sage from student where sdept =计算机;02查询体育课的学分。Selec

23、t ccredit from course where cname=体育;03查询年龄小于18的学生。Select * from student where sage < 18;04查询年龄大于20的学生。Select * from student where sage >20;05查询年龄介于18和20之间的学生（包括18和20）。Select * from student where sage between 18 and 20;06查询年龄不在18和20之间的学生。Select * from student where sage not between 18 and 2007

24、查询年龄为18，20，22的学生。Select * from student where sage in (18,20,22);08查询年龄不是18，20，22的学生。Select * from student where sage not in (18,20,22);09查询所有姓张的学生。Select * from student where sname like 张%10查询所有没有先行课的课程。Select cname from course where cpno is null;11查询有先行课的课程。Select cname from course where cpno is no

25、t null;12在计算机系中找，姓张的男生。Select * from student where sname like 张% and cdept=计算机13在计算机系中找，姓张的或者姓李的男生并且按照年龄从大到小排序。Select sname,ssex,sage,sdept from student where sname like '张%' or sname like '李%' order by sage desc ;14查询所有学生信息，显示结果先按系从大到小排序，再按年龄排序。Select * from stident order by sdept d

26、esc ,sage desc;第三章课件：多表查询1查询每个学生（学号）选了哪门课（课程）得了多少分？Select sc.sno,ame,sc.grade from sc,course c where o = o;2查询每个学生（姓名）选了哪门课（课程号）得了多少分？select s.aname,o,sc.grade from sc,course c,student s where o = o and s.sno=sc.sno;3查询每个学生（姓名）选了哪门课（课程名）得了多少分？select s.aname,ame,sc.grade from sc,course c,student s wh

27、ere o = o and s.sno=sc.sno;4查询一下王林选可哪门课得了多少分。select s.aname,ame,sc.grade from sc,course c,student s where o = o and s.sno=sc.sno where s.sname = 王林;5查询每个学生的成绩类别（优、良还是及格）。Select sc.sno,type.type from sc,type where sc.grade between Lowest_grade and Highest_grade;6查询哪个学生没有选课（用外查询）。select s.sno from stu

28、dent s where s.sno not in (select sno from sc where grade !=0);7查询哪门课没有人选（用外查询）。select o from course c where o not in (select cno from sc where grade !=0);第四章课件：组函数1查询一下所有课程的平均分，最高分，最低分和总分数。Select avg(grade),max(grade),min(grade),sum(grade) from sc;2查询一下有多少个学生参加选课。Select count(grade) from (select di

29、stinct sno from sc);3查询一下计算机系有多少人过20岁。Select count(sname) from (Select sname from student where sage >20 and sdept=计算机);4统计一下计算机系的男生多少人。Select count(sname) from (Select sname from student where ssex=男 and sdept=计算机);5查询一下每个学生考试的最高分和最低分。Select sno,max(grade),min(grade) from sc group by sno;6查询每门课（

30、课程号）的最高分和最底分。Select cno,max(grade),min(grade) from sc group by cno;7查询每门课（课程名）的最高分和最底分。Select cname,max(hgrade),min(lgrade) from course c,( Select cno,max(grade) hgrade,min(grade) lgrade from sc group by cno) a where o=o group by cname;8查询计算机系中男生多少人，女生多少人。Select (select count(ssex) from student wher

31、e ssex='男' and sdept='计算机') 男,(select count(ssex) from student where ssex='女' and sdept='计算机') 女 from dual;9，查询人数在三百人以上的系。Select sdept ,count(sno) from student group by sdept having count(sno)>300; 10查询选修人数在三人（包括三人）的课程（课程名）。Select cname from course where cno in (Se

32、lect cno from sc group by cno having count(sno)>2);11查询各科考试成绩最低的同学。Select distinct sno from sc where cno in (select cno from (Select cno,min(grade) from sc group by cno);12查询考试成绩小于所选课程平均分的人。（有能力的同学选做）Select sname from student where sno in (Select sno from sc where grade <(select avg(grade) fro

33、m sc);第五章课件：子查询1查询所有比王林大的同学信息。Select *from student where sage>(select sage from student where sname=王林)2查询和王林同在一个系的所有学生信息。Select *from student where sdept=(select sdept from student where sname=王林)3查询一下谁的成绩（所有成绩）最低。select sno,min(grade) from sc where sno=(select sno from sc where grade=(select min(grade) from sc) group by sno;4查询一下每门课成绩最底的同学（要姓名，和成绩）。select s.sname,a.lowgrade from student s,(select sno,min(grade) lowgrade from sc group by sno) a