MySQL数据库原理习题---SQL复杂查询

SQL复杂查询

对各表中的数据进行不同条件的连接查询和嵌套查询

1）查询每个学生及其选课情况；

Select student.sno,sname,age,sex,sdept,course.cno,cname,cpno,credit,grade
From student,course,sc
Where student.sno=sc.sno and Course.cno=sc.cno;

2）查询每门课的间接先修课

Select c1.cno,c2.cpno
From course c1,course c2
Where c1.cpno=c2.cno;

3）将STUDENT,SC进行右连接

Select * 
From student,sc  
Where student.sno=sc.sno;

或者

SELECT* 
FROM Student RIGHT JOIN SC ON Student.Sno=SC.Sno;

4）查询有不及格的学生姓名和所在系

Select sname,sdept 
From student
Where sno in(select sno from sc where grade<60);

或者

SELECT Sname,Sdept 
FROM Student,SC  
WHERE Student.Sno=SC.Sno AND Grade<60;

5）查询所有成绩为优秀（大于90分）的学生姓名

Select distinct sname  From student
Where sno in(select sno from sc
Group by sno having min(grade)>=90);

6）查询既选修了2号课程又选修了3号课程的学生姓名、学号；

Select sno,sname 
From student 
Where sno in(
Select sc1.sno
From sc sc1,sc sc2
Where sc1.sno=sc2.sno and sc1.cno='002' and sc2.cno='003');

7）查询和刘晨同一年龄的学生

Select s2.sno,s2.sname  
From student s1,student s2
Where s1.age=s2.age and s1.sname='刘晨' and s2.sname<>'刘晨';

或者

SELECT*
FROM Student
WHERE Sage=(SELECT Sage FROM Student WHERE Sname='刘晨');

8）选修了课程名为“数据库”的学生姓名和年龄

Select sname,age  
From student,course,sc
Where student.sno=sc.sno and Course.cno=sc.cno and Cname='数据库';

或者

SELECT Sname,Sage
FROM Student 
WHERE Sno in(SELECT Sno FROM SC WHERE Cno=(SELECT Cno FROM Course WHERE Cname='数据库'));

9）查询其他系比IS系任一学生年龄小的学生名单

select *  
from student 
where sdept<>'IS' and age<(select max(age) from student where sdept='IS');

或者

SELECT*
FROM Student 
WHERE Sage<ANY(SELECT Sage FROM Student WHERE Sdept='IS') AND Sdept<>'IS';

10）查询其他系中比IS系所有学生年龄都小的学生名单

Select * 
From student
Where sdept<>'is' and age<(select min(age) from student Where sdept='IS');

或者

SELECT*
FROM Student 
WHERE Sage<ALL(SELECT Sage FROM Student WHERE Sdept='IS') AND Sdept<>'IS';

11）查询选修了全部课程的学生姓名

Select sname
From student
Where not exists( Select *  From course Where not exists(
Select *  From sc  Where sc.cno=course.cno and sc.sno=student.sno));

或者

SELECT Sname
FROM Student 
WHERE Sno IN(SELECT Sno FROM SC GROUP BY Cno HAVING COUNT(*)=(SELECT COUNT(*) FROM Course));

12）查询计算机系学生及其性别是男的学生

select * 
from student  
where sdept='cs' and sex='男';

13）查询选修课程1的学生集合和选修2号课程学生集合的差集

Select sno from sc Where cno='001' and sno not in(
Select sno from sc Where cno='002');

或者

SELECT*
FROM Student 
WHERE Sno in(SELECT Sno FROM SC WHERE Cno='001'AND Sno not IN(SELECT Sno FROM SC WHERE Cno='002'));

14）查询李丽同学不学的课程的课程号

Select * from course Where cno not in(
select cno from sc Where cno in(
Select cno from sc where sno=(select sno from student Where sname='李丽')));

或者

SELECT Cno
FROM Course 
WHERE Cno not in(SELECT Cno FROM SC WHERE Sno in(SELECT Sno FROM Student WHERE Sname='李丽'));

15）查询选修了3号课程的学生平均年龄

Select avg(age) 
From student
Where sno in(select sno from sc Where cno='003');

16）求每门课程学生的平均成绩

Select cno,avg(grade) 
from sc Group by cno;

17）统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列

Select cno,count(sno) rs 
from sc
Group by cno Having count(sno)>=3
Order by rs desc,cno;

18）查询学号比刘晨大，而年龄比他小的学生姓名

Select s2.sname    
From student s1,student s2
Where s1.sname='刘晨' and s2.sno>s1.sno and s2.age<s1.age;

或者

SELECT Sname 
FROM Student
WHERE Sno>(SELECT Sno FROM Student WHERE Sname='刘晨')AND Sage<(SELECT Sage FROM Student WHERE Sname='刘晨');

19）求年龄大于女同学平均年龄的男同学姓名和年龄

Select sname,age 
From student 
Where sex='男' and age>(
Select avg(age) from student Where sex='女');

20）求年龄大于所有女同学年龄的男同学姓名和年龄

Select sname,age 
From student
Where sex=' 男' and  age>(select max(age) from student
Where sex='女');

21）查询至少选修了95002选修的全部课程的学生号码

Select *  From sc sc1 Where not exists(
select *  From sc sc2 Where sc2.sno='95002' and Not exists (
select * from sc sc3 Where sc3.sno=sc1.sno and
Sc3.cno=sc2.cno));
select cno from sc where sno='95002' order by cno;

或者

SELECT DISTINCT Sno
FROM SC x
WHERE NOT EXISTS(SELECT* FROM SC y WHERE y.Sno='95002' and not exists(SELECT* FROM SC z WHERE Z.Cno=y.Cno and z.Cno=x.Cno));

22）查询08001和08009两个学生都选修的课程的信息

Select * from course Where cno in(
select cno from sc Where sno='08001' and Cno in(
select cno from sc Where sno='08009'));