经典SQL50题（1~10）

创建表SQL

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

表结构

学生表 Student
sid 学生编号 sname 学生姓名 sage 出生年月 ssex 学生性别
课程表 Course
cid 课程编号 cname 课程名称 tid 教师编号
教师表 Teacher
tid 教师编号 tname 教师姓名
成绩表 SC
sic 学生编号 cid 课程编号 score 分数

1~10题

-- 1、查询“01”课程比“02”课程成绩高的所有学生的学号：
SELECT s1.sid
FROM SC s1
INNER JOIN (
SELECT s2.sid,s2.score
FROM SC s2
WHERE s2.cid='02'
) s3 ON s1.sid=s3.sid
WHERE s1.cid='01'
AND
s1.score>s3.score

-- 2查询平均成绩大于60分的同学的学号和平均成绩；
SELECT s1.sid,AVG(s2.score)
FROM Student s1
LEFT JOIN SC s2 on s1.sid=s2.sid
GROUP BY s1.sid
HAVING AVG(s2.score)>60

-- 3、查询所有同学的学号、姓名、选课数、总成绩
SELECT s1.sid,s1.sname,s3.total,s3.total_score
FROM Student s1 LEFT JOIN (
SELECT s2.sid,COUNT(*) total,SUM(s2.score) total_score
FROM SC s2 
GROUP BY s2.sid
) s3 on s1.sid=s3.sid

-- 4、查询姓“李”的老师的个数；
SELECT COUNT(*)
FROM Teacher
WHERE 
tname like '李%'

-- 5、查询没学过“张三”老师课的同学的学号、姓名；
SELECT s1.sid,s1.sname
FROM Student s1
WHERE s1.sid
NOT in(
SELECT sid
FROM SC
WHERE cid in(
SELECT c1.cid
FROM Teacher t1 
INNER JOIN Course c1 on t1.tid=c1.tid
WHERE
t1.tname='张三'
))

-- 6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
SELECT sid,sname
FROM Student
WHERE sid in(
SELECT s1.sid
FROM SC s1
INNER JOIN (
SELECT s2.sid,s2.score
FROM SC s2
WHERE s2.cid='02'
) s3 ON s1.sid=s3.sid
WHERE s1.cid='01'
)

-- 7、查询学过“张三”老师所教的课的同学的学号、姓名；
SELECT s1.sid,s1.sname
FROM Student s1
WHERE s1.sid
in(
SELECT sid
FROM SC
WHERE cid in(
SELECT c1.cid
FROM Teacher t1 
INNER JOIN Course c1 on t1.tid=c1.tid
WHERE
t1.tname='张三'
))

-- 8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
SELECT sid,sname
FROM Student
WHERE sid in(
SELECT s1.sid
FROM SC s1
INNER JOIN (
SELECT s2.sid,s2.score
FROM SC s2
WHERE s2.cid='02'
) s3 ON s1.sid=s3.sid
WHERE s1.cid='01'
AND
s1.score<s3.score
)

-- 9、查询所有课程成绩小于60分的同学的学号、姓名；
SELECT s1.sid,s1.sname
FROM Student s1 
WHERE 
s1.sid in(
SELECT s2.sid
FROM SC s2
GROUP BY s2.sid
HAVING MAX(s2.score)<60
)

-- 10、查询没有学全所有课的同学的学号、姓名；
SELECT s1.sid,s1.sname
FROM Student s1
WHERE
s1.sid NOT in(
SELECT s2.sid
FROM SC s2
GROUP BY s2.sid
HAVING COUNT(*)=(SELECT COUNT(DISTINCT cid) FROM Course)
)