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 = ‘杨佳伟’)))