CREATE TABLE Student (
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY( s_id )
);
--课程表
CREATE TABLE Course (
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY( c_id )
);
--教师表
CREATE TABLE Teacher (
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY( t_id )
);
--成绩表
CREATE TABLE Score (
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT ,
PRIMARY KEY( s_id , c_id )
);
--插入学生表测试数据
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' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
select * from[dbo].[Course]
select * from[dbo].[Score]
select * from[dbo].[Student]
select * from[dbo].[Teacher]
--11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
select distinct a.s_id, a.s_name
from Student a join Score b on a.s_id= b.s_id
where c_id in
(select c_id from Score where s_id = '01')
and a.s_id<>'01';
--※10、查询没有学全所有课的学生的学号、姓名
--select[s_id], [s_name]from [dbo].[Student] where not in(select [s_id] from [dbo].[Score] where c_id)
--写法一：
select Student.s_id, Student.s_name
from Student join Score on Score.s_id = Student.s_id
group by Student.s_id, s_name
having count(Score.c_id) < (select count(c_id) from Course);
--写法二：
select s_id, s_name
from Student
where s_id IN (SELECT s_id FROM Score group by s_id
having count(c_id)<(select count(c_id) from Course))
--小于还可以是不等于
--9、查询所有课程成绩小于60分的学生的学号、姓名
select s1.s_id,s1.s_name from [dbo].[Student] s1 join [dbo].[Score] s2
on s1.s_id=s2.s_id where s_score<60--mistake
select[s_id], [s_name]from [dbo].[Student] where [s_id]
not in(select [s_id]from [dbo].[Score] where [s_score]>=60)--√
select s_id, s_name
from Student
where s_id NOT IN (SELECT s_id FROM Score where s_score >=60)--answer
--8、查询课程编号为“02”的总成绩
select sum(s_score) from [dbo].[Score] where [c_id]='02'
select sum(s_score)
from Score
where c_id = '02';--answer
--※7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select s1.s_id,s1.s_name from [dbo].[Student] s1 join [dbo].[Score] s2
on s1.s_id=s2.s_id where s2.c_id in('01','02')--mistake
--写法一：
select s_id, s_name from Student
where s_id in
(select a.s_id from
(select s_id from Score where c_id = '01') as a
join (select s_id from Score where c_id ='02') as b
on a.s_id= b.s_id);
--写法二：
select s_id,s_name from Student
where s_id in
(select s_id from Score where c_id = '01')
AND s_id in
(select s_id from Score where c_id = '02')
--写法三：
select a.s_id,a.s_name
from Student a JOIN Score b ON a.s_id=b.s_id
JOIN Score c ON a.s_id=c.s_id
where b.c_id='01' and c.c_id='02'
and b.s_id=c.s_id ;
--6、查询学过“张三”老师所教的所有课的同学的学号、姓名
select s1.s_id,s1.s_name from [dbo].[Student] s1 join [dbo].[Score] s2 on s1.s_id=s2.s_id join [dbo].[Course] c
on s2.c_id=c.c_id join [dbo].[Teacher] t on c.t_id=t.t_id where t.t_name='张三' --√
select s.s_id,s_name from [dbo].[Student] s where s.s_id in(select s.s_id from
[dbo].[Course] c join [dbo].[Teacher] t on c.t_id=t.t_id where t.t_name='张三')--mistake
--写法一：
select s_id, s_name
from Student
where s_id in
(select s_id from Score join Course on Score.c_id = Course.c_id
join Teacher on Course.t_id = Teacher.t_id
where t_name = '张三');
--写法二：
select Student.s_id, s_name
from Student JOIN Score on Student.s_id = Score.s_id
JOIN Course on Score.c_id = Course.c_id
JOIN Teacher on Teacher.t_id = Course.t_id
where t_name='张三';---连接4个表√
--※5.查询没学过“张三”老师课的学生的学号、姓名(重点)
select s1.s_id,s1.s_name from [dbo].[Student] s1 join [dbo].[Score] s2 on s1.s_id=s2.s_id join [dbo].[Course] c
on s2.c_id=c.c_id join [dbo].[Teacher] t on c.t_id=t.t_id where t.t_name!='张三'--mistake
select s.s_id,s_name from [dbo].[Student] s,[dbo].[Course] c ,[dbo].[Teacher] t where s.s_id=c.c_id and
c.t_id=t.t_id and t.t_name !='张三'--mistake
select s.s_id,s_name from [dbo].[Student] s join [dbo].[Course] c on s.s_id=c.c_id join [dbo].[Teacher] t on
c.t_id=t.t_id where t.t_name!='张三'--mistake
select s_id, s_name
from Student
where s_id not in
(select s_id from Score join Course on Score.c_id = Course.c_id
join Teacher on Course.t_id = Teacher.t_id
where t_name = '张三');--answer
--4、查询姓“张”的老师的个数
select count(*) from [dbo].[Teacher] where t_name like '张%'
select count(t_id)
from Teacher
where t_name like '张%';---answer
--3、查询所有学生的学号、姓名、选课数、总成绩
select s1.[s_id] ,[s_name],count([c_id]),sum([s_score]) from [dbo].[Score] s1
join [dbo].[Student] s2 on s1.s_id=s2.s_id group by s1.[s_id],[s_name]
select Score.s_id AS '学号', s_name AS '姓名',count(c_id)AS '选课数',sum(s_score)AS '总成绩'
from Score join Student on Score.s_id = Student.s_id
group by Score.s_id,s_name;--answer
--2、查询平均成绩大于60分的学生的学号和平均成绩
select [s_id],AVG([s_score]) from [dbo].[Score] where [s_score]>60 group by [s_id]
select s_id, avg(s_score)
from Score
group by s_id
having avg(s_score)>60;--标准答案
--※1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。
--select * from [dbo].[Course] c,[dbo].[Score] s1,[dbo].[Student] s2 where c.c_id=s1.c_id and s1.s_id=s2.s_id and
-- 写法一：
select a.s_id from [dbo].[Score] a join [dbo].[Score] b on a.s_id=b.s_id and a.s_score>b.s_score where
a.c_id='01' and b.c_id='02'
select a.s_id
from Score a join Score b on a.s_id = b.s_id and a.s_score >b.s_score
where a.c_id = '01' and b.c_id= '02';
-- 写法二：
select a.s_id
from (select * from Score where c_id = '01') as a
join (select * from Score where c_id='02') as b
on a.s_id = b.s_id
where a.s_score > b.s_score;
