Sql 练习50道（掌握可解大部分的sql问题）（初始化 and 1-10）

数据库表初始化：

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);

题目（详解 1-10）：

1.查询“某1”课程比“某2”课程成绩高的所有学生的学号；
SELECT a.sid FROM 
(SELECT sid,score FROM sc WHERE cid=01) a,
(SELECT sid,score FROM sc WHERE cid=02) b 
WHERE a.sid = b.sid 
AND a.score > b.score;

2.查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid,AVG(score) 
FROM sc 
GROUP BY sid 
HAVING AVG(score) > 60;

3.查询所有同学的学号、姓名、选课数、总成绩
SELECT s.sid,s.sname,COUNT(sc.cid),SUM(sc.score) 
FROM student s 
LEFT JOIN sc 
ON s.sid = sc.sid 
GROUP BY sid,sname;

4.查询姓“李”的老师的个数；
SELECT COUNT(tid) 
FROM teacher 
WHERE tname LIKE '李%';

5.查询没学过“张三”老师课的同学的学号、姓名；
SELECT s.sid,s.sname 
FROM student s 
WHERE s.sid NOT IN
(SELECT sc.sid 
FROM course c,sc,teacher t 
WHERE c.tid=t.tid 
AND c.cid=sc.cid 
AND t.tname='张三');

6.查询学过“数学”并且也学过“语文”课程的同学的学号、姓名；
SELECT t1.sid,t1.sname FROM
(SELECT s.sid,s.sname FROM student s,sc,course c WHERE c.cname='数学' AND c.cid=sc.cid AND sc.sid=s.sid) t1,
(SELECT s.sid,s.sname FROM student s,sc,course c WHERE c.cname='语文' AND c.cid=sc.cid AND sc.sid=s.sid) t2
WHERE t1.sid=t2.sid;

7.查询学过“王五”老师所教的所有课的同学的学号、姓名；
SELECT t.* FROM 
(SELECT s.sid,s.sname 
FROM student s,sc,teacher t,course c 
WHERE t.tname='王五' AND t.tid=c.tid AND c.cid=sc.cid AND sc.sid=s.sid) t;

8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
SELECT t1.sid,t1.sname FROM
(SELECT s.sid,s.sname,score FROM student s,sc WHERE s.sid=sc.sid AND sc.cid=01) t1,
(SELECT s.sid,s.sname,score FROM student s,sc WHERE s.sid=sc.sid AND sc.cid=02) t2
WHERE t1.sid=t2.sid AND t1.score<t2.score;

9.查询所有课程成绩小于60分的同学的学号、姓名；
SELECT s.sid,s.sname,sc.cid 
FROM student s,sc 
WHERE s.sid=sc.sid AND sc.score<60;

10.查询没有学全所有课的同学的学号、姓名；
SELECT s.sid,s.sname 
FROM student s LEFT JOIN sc 
ON s.sid=sc.sid 
GROUP BY s.sid 
HAVING COUNT(sc.cid)<(SELECT COUNT(cid) FROM course);