分享几道sql查询练习题

department(dno,dname,officeroom,homepage)

student(sno,sname,sex,age,dno)

course(cno,cname,cpno,credit,dno)

sc(sno,cno,score,recorddate)

query

一、单表

(1)查询所有年龄大于等于20岁的学生学号、姓名;

select sno,sname

from student

where age>=20

(2)查询所有姓钱的男生学号、姓名、出生年份;

select sno,sname,2018-age

from student

where sname like '钱%' and sex='男';

(3)查询所有学分大于3的课程名称;

select cname

from course

where credit>3

(4)查询所有没有被分配到任何学院的学生姓名;

select sname

from student

where dno is null

(5)查询所有尚未设置主页的学院名称。

select dname

from department

where homepage is null

二、聚集

(1)查询各个学院的平均年龄;

select d.dname,abc.avgage

from department d,(select d.dno,avg(age)

from student s right outer join department d

on s.dno=d.dno

group by d.dno) as abc(dno, avgage)

where d.dno=abc.dno;

(2)查询每个学生选修课程的平均分;

select student.sname,avg(score)

from student left outer join sc on student.sno = sc.sno

group by student.sno

(3)查询各课程的平均分;

select cname,avg(score)

from course left outer join sc on course.cno = sc.cno

group by course.cno

(4)查询各学院开设的课程门数;

select dname,count(distinct cno)

from department,course

where department.dno = course.dno

group by department.dname

(5)查询各门课程选修人数。

select cname,count(sno)

from course,sc

where course.cno = sc.cno

group by cname

三、多表

(1)查询“信息学院”所有学生学号与姓名;

select sno,sname

from department,student

where student.dno = department.dno and dname = '信息学院'

(2)查询“软件学院”开设的所有课程号与课程名称;

select cno,cname

from department,course

where department.dno = course.dno and dname = '软件学院';

(3)查询与“陈丽”在同一个系的所有学生学号与姓名;

select sno,sname

from student

where dno = ( select dno

from student

where sname = '陈丽' )

(4)查询与“张三”同岁的所有学生学号与姓名;

select sno,sname

from student

where age = ( select age

from student

where sname = '张三')

(5)查询与“张三”同岁且不与“张三”在同一个系的学生学号与姓名;

select sno,sname

from student

where age = (select age

from student

where sname = '张三')

and dno != (select dno

from student

where sname = '张三')

(6)查询学分大于“离散数学”的所有课程名称;

select cname

from course

where credit > (select credit

from course

where cname = '离散数学')

(7)查询选修了课程名为“组合数学”的学生人数;

select count(distinct sno)

from sc,course

where sc.cno = (select cno

from course

where cname = '组合数学')

(8)查询没有选修“离散数学”的学生姓名;

select sname

from student

where sno not in (select sno

from sc

where cno = (select cno

from course

where cname = '离散数学') )

(9)查询与“算法设计与分析”、“移动计算”学分不同的所有课程名称;

select cname

from course

where credit not in (select credit

from course

where cname = '算法设计与分析' or cname = '移动计算')

(10)查询平均分大于等于90分的所有课程名称;

select cname

from course,sc

where course.cno = sc.cno

group by course.cno

having avg(sc.score)>= 90

(11)查询选修了“离散数学”课程的所有学生姓名与成绩;

select sname,score

from student,sc

where student.sno = sc.sno

and cno in (select cno

from course

where cname = '离散数学')

(12)查询“王兵”所选修的所有课程名称及成绩;

select cname,score

from course,sc

where course.cno = sc.cno and sc.sno in (select sno

from student

where sname = '王兵')

(13)查询所有具有不及格课程的学生姓名、课程名与成绩;

select sname,cname,score

from student,course,sc

where student.sno = sc.sno and course.cno = sc.cno and score<60

(14)查询选修了“文学院”开设课程的所有学生姓名;

select sname

from student

where sno in (select sno

from sc,course

where sc.cno = course.cno and course.cno in (select cno

from course,department

where course.dno = department.dno and dname = '文学院'))

(15)查询“信息学院”所有学生姓名及其所选的“信息学院”开设的课程名称。

select sname,cname

from student,course

where sno in (select sno

from student

where dno = (select dno

from department

where dname = '信息学院') )

and cno in (select cno

from course,department

where course.dno = department.dno and dname = '信息学院')

四、综合

(1)查询所有学生及其选课信息(包括没有选课的学生);

select student.sname,

(select course.cname

from course

where course.cno=sc.cno)

from student left join sc on student.sno=sc.sno;

(2)查询“形式语言与自动机”先修课的课程名称;

select cname

from course

where cno = (select cpno

from course

where cname = '形式语言与自动机')

(3)查询“形式语言与自动机”间接先修课课程名称;

select cname

from course

where cno = (select cpno

from course

where cno = (select cpno

from course

where cname = '形式语言与自动机'))

(4)查询先修课为编译原理数学的课程名称;

select cname

from course

where cpno = (select cno

from course

where cname = '编译原理数学')

(5)查询间接先修课为离散数学的课程名称;

select cname

from course

where cpno in (select cno

from course

where cpno in (select cno

from course

where cname = '离散数学')

)

(6)查询所有没有先修课的课程名称;

select cname

from course

where cpno is null

(7)查询所有没选修“形式语言与自动机”课程的学生姓名;

select sname

from student

where sno not in (select sno

from sc

where cno = (select cno

from course

where cname = '形式语言与自动机') )

(8)查询所有选修了“形式语言与自动机”但没选修其先修课的学生姓名;

select sname

from student

where sno in (select sno

from sc

where cno = (select cno

from course

where cname = '形式语言与自动机') )

and sno not in (select sno

from sc

where cno = (select cno

from course

where cno = (select cpno

from course

where cname = '形式语言与自动机') ))

(9)查询选修课程总学分大于等于28的学生姓名及其选修课程总学分;

select s.sno,s.sname,v.sumcredit

from student s, (select sno,sum(credit)

from sc, course c

where sc.cno=c.cno

group by sc.sno

having sum(credit)>=28) as v(sno,sumcredit)

where s.sno=v.sno;

(10)查询选修了3门以上课程且成绩都大于85分的学生学号与姓名;

select sname

from student

where student.sno in (select sno

from sc

where score>85

group by sc.sno

having count(cno)>3)

(11)查询恰好选修了3门课并且都及格的学生姓名;

select sname

from student

where student.sno in (select sno

from sc

where score>=60

group by sc.sno

having count(cno)=3)

(12)查询人数多于6的学院名称及其学生人数;

select dname,count(student.sno)

from department,student

where department.dno = student.dno

group by department.dno

having count(student.sno)>6

(13)查询平均成绩高于王兵的学生姓名;

select sname

from student s

where s.sno in (select sc.sno

from sc

group by sc.sno

having avg(score)> all(select avg(score)

from sc

where sc.sno = (select sno

from student

where sname = '王兵')))

(14)查询所有选修了离散数学并且选修了编译原理课程的学生姓名;

select sname

from student

where sno in (select distinct sno

from sc

where cno = (select cno

from course

where cname = '离散数学'))

and sno in (select sno

from sc

where sc.cno = (select cno

from course

where cname = '编译原理'))

(15)查询软件学院离散数学课程平均分;

select avg(score)

from sc

where cno = (select cno

from course

where cname = '离散数学')

(16)查询年龄与“软件学院”所有学生年龄都不相同学生姓名及其年龄和学院;

select s.sno,s.sname,s.age,d.dname

from student s left join department d on s.dno=d.dno

where age not in (select age

from student

where age is not null

and dno = (select dno

from department

where dname='软件学院'))

(17)查询各学院选修同一门课人数大于4的学院、课程及选课人数;

select d.dname,c.cname,v.countof

from course c,department d, (select dno,cno,count(s.sno)

from student s, sc

where s.sno=sc.sno

group by dno,cno

having count(sc.sno)>4) as v(dno,cno,countof)

where c.cno=v.cno and d.dno=v.dno;

(18)查询仅仅选修了“高等数学”一门课程的学生姓名;(学号、姓名及所在学院名称)

select student.sno,student.sname,department.dname

from student left outer join department on student.dno = department.dno

where student.sno not in (select sno

from sc

where cno != (select cno

from course

where cname = '高等数学'))

(19)查询平均学分积小于70分的学生姓名。

select sname

from student

where sno in(select sc.sno

from sc,course

where sc.cno=course.cno and score is not null and credit is not null

group by sno

having sum(score*credit)/sum(credit)<70)

(20)查询选修了“信息学院”开设全部课程的学生姓名。

select sname

from student

where sno not in (select sno

from sc

where cno not in (select cno

from course

where dno = (select dno

from department

where dname = '信息学院')))

(21)查询选修了“杨佳伟”同学所选修的全部课程的学生姓名。

select sname

from student s

where sno in (select sno

from sc

where cno in (select cno

from sc

where sno = (select sno

from student

where sname = '杨佳伟')))