Sql查询综合练习及答案

1. 表结构

1.1. student表

CREATE TABLE `student` (
  `stu_id` varchar(10) DEFAULT NULL,
  `stu_name` varchar(10) DEFAULT NULL,
  `stu_age` datetime DEFAULT NULL,
  `stu_sex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2. teacher表

CREATE TABLE `teacher` (
  `t_id` varchar(10) DEFAULT NULL,
  `t_name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.3. course(课程)表

CREATE TABLE `course` (
  `c_id` varchar(10) DEFAULT NULL,
  `c_name` varchar(10) DEFAULT NULL,
  `t_id` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.4. sc(学生成绩)表

CREATE TABLE `sc` (
  `s_id` varchar(10) DEFAULT NULL,
  `c_id` varchar(10) DEFAULT NULL,
  `score` double(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 测试数据

2.1. student表数据

INSERT INTO `student` VALUES ('01', '韦小宝', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '阿珂', '1990-12-21 00:00:00', '女');
INSERT INTO `student` VALUES ('03', '双儿', '1990-05-20 00:00:00', '女');
INSERT INTO `student` VALUES ('04', '方怡', '1990-08-06 00:00:00', '女');
INSERT INTO `student` VALUES ('05', '沐剑屏', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '曾柔', '1992-03-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '苏荃', '1989-07-01 00:00:00', '女');
INSERT INTO `student` VALUES ('08', '建宁公主', '1990-01-20 00:00:00', '女');

2.2. teacher表数据

INSERT INTO `teacher` VALUES ('01', '陈近南');
INSERT INTO `teacher` VALUES ('02', '康熙');
INSERT INTO `teacher` VALUES ('03', '观证禅师');

2.3. course表数据

INSERT INTO `course` VALUES ('01', '语文', '01');
INSERT INTO `course` VALUES ('02', '数学', '02');
INSERT INTO `course` VALUES ('03', '英语', '03');
INSERT INTO `course` VALUES ('04', 'Java', '02');

2.4. sc表数据

INSERT INTO `sc` VALUES ('01', '01', '80.0');
INSERT INTO `sc` VALUES ('01', '02', '90.0');
INSERT INTO `sc` VALUES ('01', '03', '99.0');
INSERT INTO `sc` VALUES ('02', '01', '70.0');
INSERT INTO `sc` VALUES ('02', '02', '60.0');
INSERT INTO `sc` VALUES ('02', '03', '80.0');
INSERT INTO `sc` VALUES ('03', '01', '80.0');
INSERT INTO `sc` VALUES ('03', '02', '80.0');
INSERT INTO `sc` VALUES ('03', '03', '80.0');
INSERT INTO `sc` VALUES ('04', '01', '50.0');
INSERT INTO `sc` VALUES ('04', '02', '30.0');
INSERT INTO `sc` VALUES ('04', '03', '20.0');
INSERT INTO `sc` VALUES ('05', '01', '76.0');
INSERT INTO `sc` VALUES ('05', '02', '87.0');
INSERT INTO `sc` VALUES ('06', '01', '31.0');
INSERT INTO `sc` VALUES ('06', '03', '34.0');
INSERT INTO `sc` VALUES ('07', '02', '89.0');
INSERT INTO `sc` VALUES ('07', '03', '98.0');

3. 面试题

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

select a.s_id from 
(select s_id,c_id,score from sc where c_id='01') a, 
(select s_id,c_id,score from sc where c_id='02') b 
where a.s_id=b.s_id and a.score>b.score

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

select s_id,avg(score)  from sc group by s_id having avg(score)>60

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

select sum(score) '总成绩',count(s_id) '选课数',s_id '学',stu.stu_name 
from sc left join student stu on sc.s_id=stu.stu_id   
group by s_id

3.4. 查询姓“陈”的老师的个数；

select count(DISTINCT(t_name)) teacherNum 
from teacher where t_name like '陈%';

3.5. 查询没学过“陈近南”老师课的同学的学号、姓名；

#查询没学过“陈近南”老师课的同学的学号、姓名
select stu_name,stu_id from student where stu_id
not in (
	select s_id from sc 
	left join course c on sc.c_id=c.c_id 
	left join teacher t on c.t_id=t.t_id 
	where t.t_name="陈近南"
)

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

#查询学过“01”并且也学过编号“02”课程的同学的学号、姓名； 
select stu.stu_name,stu.stu_id from sc s1 left join student stu on s1.s_id=stu.stu_id  
where s1.c_id='01' 
and 
EXISTS(select * from sc s2 where s2.c_id='02' and s2.s_id=s1.s_id )

3.7. 查询学过“陈近南”老师所教的所有课的同学的学号、姓名；

select stu_name,stu_id from student 
where stu_id in(
	select sc.s_id from sc 
	left join course c on sc.c_id = c.c_id 
	left join teacher t on c.t_id=t.t_id 
	where t.t_name="陈近南"  
	group by sc.s_id  having count(sc.s_id)=(
		 select count(c.c_id) from course c 
left join teacher t on c.t_id=t.t_id 
	 where t.t_name='陈近南' 
	)
)

3.8. 查询所有课程成绩小于60分的同学的学号、姓名；

SELECT * from student s where s.stu_id  in 
(
select s.s_id from sc s where s.score<60  
group by s.s_id having count(s.s_id)=(
	select count(sc.s_id) from sc  where s.s_id=sc.s_id  
)
);

3.9. 查询没有学全所有课的同学的学号、姓名；

#查询没有学全所有课的同学的学号、姓名
select s.stu_id,s.stu_name from sc left join student s on sc.s_id=s.stu_id 
group by s.stu_id 
having count(s.stu_id)=
(
select count(c_id) from course
);

3.10. 查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；

select DISTINCT(stu_id),stu_name from sc 
left join student s on sc.s_id= s.stu_id 
where sc.c_id in (
select c_id from sc where sc.s_id='01'
)

3.11. 查询至少学过学号为“01”同学所有一门课的其他同学学号和姓名；

select DISTINCT(stu_id),stu_name from sc 
left join student s on sc.s_id= s.stu_id 
where sc.c_id in (
select c_id from sc where sc.s_id='01'
)
and sc.s_id!='01'

3.12. 查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名

select stu.stu_name,count(s2.c_id) courseNum 
from sc s2 left join student stu on s2.s_id=stu.stu_id 
where s2.c_id in (select DISTINCT(c_id) from sc s1 where s1.s_id='02') 
group by s2.s_id 
having courseNum= (select count(DISTINCT(c_id)) from sc s1 where s1.s_id='02') AND count(c_id) = (SELECT count(c_id) FROM sc s1 WHERE s1.s_id = s0.s_id)

3.13. 删除学习“陈近南”老师课的SC表记录

delete from sc where sc.c_id in( 
select * from (
select c.c_id from sc s,course c,teacher t where s.c_id=c.c_id and c.t_id=t.t_id and t.t_name="陈近南") b);

执行下面语句进行数据恢复

INSERT INTO `sc` VALUES ('01', '02', '90.0');
INSERT INTO `sc` VALUES ('02', '02', '60.0');
INSERT INTO `sc` VALUES ('03', '02', '80.0');
INSERT INTO `sc` VALUES ('04', '02', '30.0');
INSERT INTO `sc` VALUES ('05', '02', '87.0');
INSERT INTO `sc` VALUES ('07', '02', '89.0');