Sql练习

环境搭建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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);

查询“01”课程比“02”课程成绩高的所有学生的学号；

1
2
3
4
5
6
7
8
9
select t1.sid,t1.score1,t2.score2 from 
(select 
sid,score as score1
from SC where cid='01') t1

join 
(select 
sid,score as score2
from SC where cid = '02') t2  on t1.sid = t2.sid and t1.score1 > t2.score2

查询平均成绩大于60分的同学的学号和平均成绩

select
sid,avg(score)
from SC group by sid having avg(score) > 60

查询所有同学的学号、姓名、选课数、总成绩

select s.sid,s.sname,ifnull(count(cid),0),ifnull(sum(score),0) from Student s left join SC sc on s.sid = sc.sid group by sid

查询姓“李”的老师的个数

select count(sid) from Student where sname like ‘李%’;

查询没学过“张三”老师课的同学的学号、姓名

select
s.sid,s.sname
from Student s where s.sid not in(
select
sc.sid
from SC sc left join Course c on sc.cid = c.cid left join Teacher t on t.tid = c.tid where t.tname=’张三’);

查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名

select

from
(

select
sc1.sid
from SC sc1 where sc1.cid=’01’
join
SC sc2 where sc2.cid=’02’) b;