<概论>经典SQL习题

关系模式 S_T

学生-课程关系模式 S-T

学生表:Student(Sno,Sname,Ssex,Sage,Sdept)
课程表：Course(Cno，Cname，Cpno，Ccredit)
学生选课表：SC(Sno,Cno,Grade)

单表查询

查询指定列

Q1：查询全体学生的学号与姓名

1 2	select Sno，Sname from Student

Q2:查询全体学生的详细记录

1	select * from Student

等价于

1 2	select Sno，Sname，Ssex，Sage，Sdept from Student

Q3:查询全体同学的姓名，出生年份和所在系，要求用小写字母表示系名。

1 2	select Sname,'Year of birth',DATEPART("year",now())-Sage,LOWER(Sdept) from Student

选择表中的若干元组

Q4 ：查询选修了课程的学生学号

1 2	select distinct Sno from Student

Q5: 查询有不及格的学生学号

1
2
3

select distinct Sno
from SC
where Grade<60;

Q6:查询年龄在20-30岁（包括20和30岁）之间的学生姓名，系别，和年龄

1
2
3

select Sname,Sdept,Sage
from Student
where Sage between 20 and 30

Q7:查询信息系（IS），数学系（MA），和计算机系（CS）学生的姓名和性别

1
2
3

select Sname,Ssex
from Student
where Sdept in('IS','MA','CS')

Q8:查询学号为200215121的学生的详细情况

1 2	select * from Student where Sno like '200215121'

等价于

1 2	select * from Student where Sno = '200215121'

Q9: 查询名字中第二个字为‘阳’字的学生的姓名和学号

1
2
3

select Sname ,Sno
from Student
where Sname like '_阳%'

Q10:查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况。

1 2	select * from Course where Cname like 'DB\_%i__' escape '\'

Q11:查询所有有成绩的学生学号和课程号

1
2
3

select Sno,Cno
from SC
where grade is not NULL;

Q12:查询计算机系年龄在岁20岁以下的学生姓名

1 2	select Sname from Student where Sdept ='CS' and Sage<20;

Q13: 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

1 2	select * from Student order by Sdept ,Sage desc;

Q14：查询学生总人数

1	select count(*) from Student

Q15：查询选修了课程的学生人数

1	select count(distinct *) from SC

Q16：计算1号课程的学生平均成绩

1 2	select avg(Grade) from SC where Cno = 1;

Q17：查询选修1号课程的学生最高分数

1 2	select max(Grade) from SC where Cno = 1

Q18：查询学生200215122选修课程的总学分数

1 2	select sum(Ccredit) from SC，Course where SC.Cno = Course.Cno and Sno = "200215122"

Q19:查询每门课的选课人数

1 2	select Cno,count(*) from SC group by Cno

Q20：查询每个系男女的平均年龄

1 2	select Sdept,Ssex,avg(Sage) from Student group by Sdept , Ssex

Q21：查询选修了4门以上（包括四门）的学生学号

1
2
3

select Sno from SC
group by Sno
having count(*)>3

Q22：查询有2门以上课程是90分的学生学号及（90分以上）的课程数

select Sno,count(*) from SC
where Grade>=90
group by Sno
having count(*) > 2

连接查询

等值连接与非等值连接

Q23：查询每个学生及其选课情况

1 2	select Student.Sno ,Cno,Grade from Stundet ,SC where Student.Sno=SC.Sno

自身连接

Q24：查询每门课程的间接先修课

1 2	select X.Cno,Y.Cpno from Course X,Course Y where X.Cpno=Y.Cno

外连接

Q25：查询每个学生及其选课情况

1 2	select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student left out join SC on (Student.Sno=SC.Sno)

复合条件连接

Q26：查询每个学生的学号，姓名，选修的课程名及成绩

1 2	select Student.Sno ,Sname,Cname,Grade from Student,Course ,SC where Student.Sno = SC.Sno and SC.Cno = Course.Cno

嵌套查询

Q27:查询与刘晨在同一个系的学生

select * from Student
where Sno in
(select Sno from Student
where Sname like '刘晨')

Q28：查询选修了课程名为“信息系统”的学生学号和姓名

select Sno,Sname from Student
where Sno in
(select Sno from SC
where Cno in
(select Cno from Course
where Cname like '信息系统'))

Q29：查询“200215121”所选课程的课程名，课程号，和学分。

select Cname,Cno ,Ccredit from Course
where Cno in
(select Cno from SC
where Sno = '200215121')

Q30：找出每个学生超过他选修课程平均成绩的课程号

select Sname,Sage from Student
where Sdept not like 'IS' and
Sage < any (select Sage from Student
		   where Sdept = 'IS')

Q31：查询其他系中比信息系某一学生年龄小的学生姓名及年龄

select Sname,Sage from Student
where Sdept not like 'IS' and
Sage < any (select Sage from Student
		   where Sdept = 'IS')

Q32：找出年龄最小的学生

1 2	select * from Student where Sage = (select min(Sage) from Student )

Q33：找出具有最高平均成绩的学号及平均成绩

select Sno,avg(Grade) from SC
group by Sno
having avg(Grade) >= all(select avg(Grade) from SC
					   group by Sno)

Q34：列出得过100分的学生的学号，姓名

select Sno,Sname from Student
where exists (
select * from SC
where SC.Sno=Student.Sno and Grade = 100)

Q35：查询选修了所有课程的学生的姓名

select Sname from Student
where not exists (select * from Course
			 where not exists (select * from SC
							  SC.Sno=Student.Sno and Course.Cno=SC.Cno) )

Q36：查询至少选修了200215122选修的全部课程的学生的学号

select distinct Sno from Student
where not exists (select * from SC X
				 where X.Sno='200215122' and not exists (select * from SC Y
													  where Y.Cno=X.Cno and Y.Sno=Student.Sno))

Q37:列出各系学生人数（人数多的排在前面）

1
2
3

select Sdept,count(*) num from Student
group by Sdept
order by num desc

Q38：找出各科成绩均在85分或以上的学生学号，姓名

/*错解：
select Sno,Sname from Student
where not esixts (select * from SC
				 where SC.Sno=Student.Sno
				 and Grade < 85)
错误原因在于从学生表入手，会把没有选课的学生放入结果集中
*/
//正解：
select Sno,Sname from Student
where Sno in (select Sno from SC X
			 where not exists (select * from SC Y
							  where X.Sno=Y.Sno
							  and Y.Grade<85))

Q39：找出有三门课程成绩在75分以下的学生的学号，姓名

select Sno,Sname from Student
where 3=(select count(*) from SC
		where Student.Sno=SC.Sno
		and Grade<75)

Q40：列出数据库课程成绩前五名的学生的学号，成绩，按成绩降序排列

select Sno,Grade from SC X
where Cno in (select Cno from Course
			 where Cname like '数据库')
and 5>= (select count(*) from SC Y
		where Y.Grade>=X.Grade
		and Cno in (select Cno from Course
				   where Cname like '数据库'))
order by Grade desc

关系模式SPJ

供应商：S(Sno,Sname,City)
零件：P(Pno,Pname,Color,Weight)
工程项目: J(Jno,Jname,City)
供应: SPJ(Sno,Jno,Pno,QTY)

Q1：供应工厂“钢铁厂”零件名为“螺钉”的供应单位编号及名称

select Sno,Sname from S
where Sno in(select Sno from SPJ
			where Jno in (select Jno from J
						 where Jname like '钢铁厂')
			and Pno in (select Pno from P
					   where Pname like '螺钉'))

Q2:供应工程“教学楼”零件颜色为灰白色的单位编号及名称

select Sno,Sname from S
where Sno in(select Sno from SPJ
			where Jno in (select Jno from J
						 where Jname like '教学楼')
			and Pno in (select Pno from P
					   where Color like '灰白'))

Q3：求供应工程J1零件的供应商号码Sno

1 2	select Sno from SPJ where Jno = 'J1'

Q4：求供应工程J1零件为红色的供应商号码Sno

1
2
3

select Sno from SPJ
where Jno = 'J1' and Pno in (select Pno from P
							where Color = '红')

Q5：求供应工程J1零件为P1的供应商号码Sno

1
2
3

select Sno from SPJ
where Jno = 'J1'
and Pno = 'P1'

Q6：求没有使用天津供应商生产的红色零件的工程号Jno

/*第一种*/
select Jno from J
where not exists (select * from SPJ
				 where SPJ.Jno = J.Jno
				 and Sno in (select Sno from S
							where City = '天津')
				 and Pno in (select Pno from P
							where Color = '红'))

/*第二种*/
select Jno from J
where Jno not in (select Jno from SPJ
				 where Sno in (select Sno from S
							  where City = '天津')
				 and Pno in (select Pno from P
							where Color = '红'))

Q7：求至少使用了供应商S1所供应的全部零件的工程号Jno

select Jno from J
where not exists (select * from SPJ X
				 where X.Sno = 'S1'
				 and not exists (select * from SPJ Y
								where Y.Pno=X.Pno
								and Y.Jno=J.Jno))

Q8：找出所有供应商的姓名和城市

1	select Sname,City from S

Q9：找出所有零件的名称颜色和重量

1	select Pname,Color,Weight from P

Q10：找出使用供应商S1所供应零件的工程号码

1 2	select Jno from SPJ where Sno = 'S1'

Q11：找出工程项目J2使用的各种零件的名称及其数量

1
2
3

select Pname,QTY from SPJ,P
where SPJ.Pno=J.Pno
and SPJ.Jno = 'J2'

Q12：找出上海厂商供应的所有零件号码

1
2
3

select Pno from SPJ
where Sno in(select Sno from S
			where City = '上海')

Q13：找出使用上海产的零件的工程名称

select Jname from J
where Jno in (select Jno from SPJ
			 where Sno in (select Sno from S
						  where City = '上海'))

Q14：找出没有使用天津产的零件的工程号码

select Jno from J
where Jno not in (select Jno from SPJ
				 where Sno in (select Sno from S
							  where City = '天津'))

Q15：把全部红色零件颜色改成蓝色

1
2
3

update P
set Color = '蓝'
where Color = '红'

Q16：由S5供给J4的零件P6改为由S3供应

update SPJ
set Sno = 'S3'
where Sno='S5'
and Jno='J4'
and Pno='p6'

Q17：从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录

// step1:
delete from SPJ
where Sno = 'S2'
// step2:
delete from S
where Sno = 'S2'

Q18：请将（S2，J6，P4，200）插入到供应情况关系中

1 2	insert into SPJ values ('S2','J6','P4',200)