MySQL，刷题之对多表查询，题+代码！！

考察：多表查询

1.查询每个学生的学号，姓名和所学的课程编号及成绩。
2.查询每个学生的姓名、所学的课程名称及相应成绩。
3.查询数据库成绩不及格的学生学号和姓名。
4.查询名称为“数据库”的课程的平均得分。
5.查询与张三家庭住址相同的女同学的姓名（用自身连接实现）。
6.查询学号为“2015002”的学生所学的课程中成绩最高的课程名称。
7.查找所有学生的选课及成绩情况，包括未选课的学生（外连接）。
8.查询与张三或李四专业相同，并且家庭住址在上海的学生姓名。
9.列出未学“计算机网络”课程的学生信息. （使用IN或NOT IN 子查询）
10.列出比所有北京的学生年龄都小的其它地方的学生姓名和生日。
11.列出平均成绩最高的课程的课程编号和平均成绩。
12.查询学号“2015002”的学生所学课程的最高分、最低分、总分、平均分。
13.查询与张三家庭住址相同的女同学的姓名（用嵌套子查询实现）。
 14.找出张三所学的所有课程的课程号和课程名称（用嵌套子查询实现）。
15.列出学了“数据库”的学生姓名.
 16 找出没有学数据库的学生的姓名（选做）；
   a) 用嵌套子查询实现（NOT IN）
   b) 用嵌套子查询实现 (NOT EXISTS) 
17. 找出所有课程都及格的学生的学号和姓名（至少两种方法实现）（选做）。
18. 找出学了所有“选修”课的学生姓名（选做）。

#1
select students.sno,students.sname,score.cno,score.grade 
from students 
inner join score on students.sno=score.sno;
#2
select students.sname,courses.Cname,score.grade 
from students 
join score on students.sno = score.sno 
join courses on courses.cno = score.cno;
#3
select score.grade,students.sno,students.Sname
from students
inner join score on students.sno = score.sno
where score.grade<60;
#4
select avg(score.grade)
from score
inner join courses on courses.cname = '数据库'&&score.cno=courses.cno;
#5
select s1.sname 
from students as s1,students as s2
where s1.sex='女' AND s1.saddress=s2.saddress AND s2.sname='张三' AND s1.sname!='张三' ;
#6
select courses.cname,score.grade
from courses
INNER JOIN score on courses.cno = score.cno
INNER JOIN students on students.sno = score.sno
where students.sno = '2015002' LIMIT 1;
#7
select students.sno,courses.cname,score.grade
from courses left join score on courses.cno = score.cno
left join students on students.sno = score.sno;
#8
select students.sname from students
where (students.sdept = (select students.sdept from students
where students.sname = '张三')or (select students.sdept from students
where  students.sname = '李四') ) && students.saddress = '上海';
#9
select * from students
where sdept not in (select courses.cname from courses
LEFT JOIN score on courses.cno = score.cno
LEFT JOIN students on students.sno = score.sno
where courses.cname = '计算机网络');
#10
select students.sbirthday from students where students.saddress = '北京';
select students.sname,students.sbirthday from students
where students.saddress != '北京' && students.sbirthday<(select students.sbirthday from students where students.saddress = '北京');
#11
select courses.cno,AVG(score.grade)
from score INNER JOIN courses on courses.cno = score.cno GROUP BY courses.cno;
#12
select MAX(score.grade),MIN(score.grade),SUM(score.grade),AVG(score.grade)
from score
WHERE score.sno = '2015002';
#13
select students.sname from students
where students.sex = '女' && students.saddress = (select students.saddress from students where sname = '张三'); 
#14
select courses.cname,courses.cno from courses 
LEFT JOIN score on score.cno = courses.cno 
LEFT JOIN students on students.sno = score.sno
where students.sname = (select sname from students where sname = '张三');
#15
select students.sname from students
LEFT JOIN score on score.sno = students.sno
LEFT JOIN courses on courses.cno = score.cno
where courses.cname = '数据库';
#16 
#a
select DISTINCT students.sname from students
LEFT JOIN score on score.sno = students.sno
LEFT JOIN courses on courses.cno = score.cno
where  courses.cname not in (select cname from courses WHERE cname = '数据库');
#b
select DISTINCT students.sname from students
LEFT JOIN score on score.sno = students.sno
LEFT JOIN courses on courses.cno = score.cno
where  not exists (select cname from courses WHERE cname = '数据库');
#17
#1)
select DISTINCT students.sno,students.sname from students
LEFT JOIN score on score.sno = students.sno
LEFT JOIN courses on courses.cno = score.cno
where score.grade>=60;
#2）
select DISTINCT students.sno,students.sname from students
RIGHT JOIN score on score.sno = students.sno
RIGHT JOIN courses on courses.cno = score.cno
where score.grade>=60;
#18		
SELECT sname
FROM students
WHERE not EXISTS
(
	select * from courses where ctype = '选修'
	and not EXISTS(
	select * from score where score.sno = students.sno and
	score.cno = courses.cno
	)
);