SQL练习

使用MySQL进行的一些sql练习，题目内容来自 http://topic.csdn.net/u/20081020/15/1abf54d0-f401-42ab-a75e-df90027ceba0.html

use test;

-- 查询工资最高的员工信息
select name,salary,id,dept_id
from t_employee
where salary =
	(select max(salary) from t_employee);

-- 查询每个部门最高工资的员工信息
select name,salary,dept_id
from t_employee
inner join
(
	select max(e.salary) as maxsalary, d.id as tmp_dept_id
	from t_employee as e
	inner join t_department as d
	on e.dept_id = d.id
	group by d.id
) as tmp
where salary = maxsalary and dept_id = tmp_dept_id
order by dept_id;

-- 查询 工资小于carter 而大于jones 的员工
select id,name,salary
from t_employee
where salary <
	(select salary from t_employee where name='carter')
and salary >
	(select salary from t_employee where name='jones')
order by id;

-- 查询每个部门 工作为clerk的 最高工资和最低工资
select tmp.dept_id,min(tmp.salary),max(tmp.salary)
from 
(select * from t_employee where job = 'clerk') as tmp
group by tmp.dept_id;

select min(salary) as lowest, max(salary) as highest, dept_id
from t_employee
where job = 'clerk'
group by dept_id;

-- 列出管理者和及其下属
select m.dept_id, m.name as manager_name, e.name as employee_name
from t_employee as e
inner join t_employee as m
on e.manager = m.id
order by m.dept_id;

-- 查询所有部门中 job为clerk 的员工名字，工作，部门名 
select d.id, d.name ,e.name, e.job
from t_department as d, t_employee as e
where d.id = e.dept_id and e.job = 'clerk'
order by d.id;

-- 查询工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
-- explain
select a.name,a.salary,a.dept_id
from t_employee as a
where salary >
(
	select avg(salary)
	from t_employee as b
	where a.dept_id = b.dept_id
)
order by a.dept_id;

-- 使用join完成与上面语句一样的查询，有可能影响性能（？）
--explain
select e.name,e.salary,e.dept_id, tmp.avgsalary
from t_employee as e
inner join
(select avg(salary) as avgsalary,dept_id as d_id from t_employee group by dept_id) as tmp
on e.dept_id = tmp.d_id
where e.salary > tmp.avgsalary;

-- 查询列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序
select count(a.salary) as how_many, a.dept_id
from t_employee as a
where salary >
(
	select avg(salary)
	from t_employee as b
	where a.dept_id = b.dept_id
)
group by a.dept_id
order by a.dept_id;

select tmp.how_many,tmp.d_id
from 
(
	select count(a.salary) as how_many, a.dept_id as d_id
	from t_employee as a
	where salary >
	(
	select avg(salary)
	from t_employee as b
	where a.dept_id = b.dept_id
	)
	group by a.dept_id
) as tmp
where tmp.how_many > 1
order by tmp.d_id;

-- Student(S#,Sname,Sage,Ssex)  
-- Course(C#,Cname,T#)  
-- SC(S#,C#,score)  
-- Teacher(T#,Tname)  

-- 1.查询“1”课程比“2”课程成绩高的所有学生的学号； 
select a.student_id
from sc as a
where a.course_id = 2
and a.score <
(
	select score
	from sc as b
	where a.student_id = b.student_id
	and b.course_id = 1
);

-- 2.查询平均成绩大于70分的同学的学号和平均成绩
select student_id,avg(score) as avg_score
from sc
group by student_id
having avg_score > 70;
 
-- 3.查询所有同学的学号、姓名、选课数、总成绩

select s.id,s.name,tmp.how_many_courses,tmp.total_score
from student as s,
(
	select student_id,count(*) as how_many_courses,sum(score) as total_score
	from sc
	group by student_id
) as tmp 
where tmp.student_id = s.id;

-- 4.查询没学过 TOM 老师课的同学的学号、姓名
select id,name
from student
where id not in
(
	select distinct(sc.student_id)
	from sc
	where sc.course_id in  
	(
		select c.id as c_id
		from teacher as t
		inner join course as c
		on t.id = c.teacher_id
		where t.name = 'TOM'
	)
);

-- 5. 查询学过 1 并且也学过 2 课程的同学的学号、姓名；
select id,name
from student
where id in
(
	select student_id
	from sc
	where course_id = 1
	and student_id in
	(
		select student_id
		from sc
		where course_id = 2
	)
);

-- 6.查询学过 TOM 老师所教的所有课的同学的学号、姓名

--select S#,Sname 
--from Student 
--where S# in 
--(select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' 
--group by S# having count(SC.C#)=
--(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平')); 

select id,name
from student
where id in
(
	select distinct(student_id)
	from sc
	where sc.course_id in
	(
		select c.id
		from course as c,
			 teacher as t
		where c.teacher_id = t.id
		and t.name = 'TOM'
	)
);

-- 7.查询课程编号 2 的成绩比课程编号 1 课程低的所有同学的学号、姓名
--  Select S#,Sname from 
-- (
--  Student.S#,Student.Sname,score ,
--  (select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 
-- 	from Student,SC where Student.S#=SC.S# and C#='001'
-- ) S_2 where score2 <score; 

select id,name
from student
where id in
(
	select sc1.student_id
	from sc as sc1
	inner join 
	(
		select score,student_id
		from sc
		where course_id = 1
	) as tmp
	on tmp.student_id = sc1.student_id
	where sc1.course_id = 2
	and sc1.score < tmp.score
);

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

select id,name
from student
where id in
(
	select distinct(student_id)
	from sc
	where score < 60
);

-- 9.查询没有学全所有课的同学的学号、姓名
select id,name
from student,
(
	select student_id as s_id,count(*) as how_many_my_courses
	from sc
	group by student_id
	having how_many_my_courses <
	(select count(*) from course)
) as tmp
where id = tmp.s_id;

select student.id,student.name
from student,sc
where student.id = sc.student_id
group by student.id
having count(course_id) < (select count(*) from course);

-- 10.查询至少有一门课与学号为 5 的同学所学相同的同学的学号和姓名
select id,name
from student
where id !=5
and id in
(
	select distinct(student_id)
	from sc 
	where sc.course_id in
	(
		select course_id
		from sc
		where student_id = 5
	)
);

-- 11.把 SC 表中 TOM 老师教的课的成绩都更改为此课程的平均成绩
update sc,
(	
	select avg(score) as average,course_id
	from sc
	where course_id in
	(
		select course.id
		from course,teacher
		where teacher.name = 'TOM'
		and course.teacher_id = teacher.id
	)
	group by course_id
) as tmp
set sc.score = tmp.average
where sc.course_id = tmp.course_id;

-- 12.查询和 2 号的同学学习的课程完全相同的其他同学学号和姓名
-- tips：先筛选，再计算筛选后每个学生的选课记录，等于2号学生的选课记录数的即为目标学生
select id,name
from student
where id in
(
	select student_id
	from sc
	where student_id !=2
	and course_id in 
	(
		select course_id
		from sc 
		where student_id = 2
	)
	group by student_id
	having count(course_id) = 
	(select count(*) from sc where student_id = 2)
);

-- 13.向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号 3号课的同学学号；3号课的平均成绩

insert into sc
(
	select distinct(student_id),3 as course,(select avg(score) from sc where course_id =3 ) as score
	from sc
	where student_id not in
	(
		select student_id
		from sc
		where course_id =3
	)
);

-- 14.按平均成绩从高到低显示所有学生的 english french chinese三门的课程成绩，
-- 按如下形式显示： 学生ID,English,French,Chinese,有效课程数,有效平均分

-- COALESCE(null,default value for null);
-- Returns the first non-NULLvalue in the list, or NULL if there are no non-NULL values

select s.student_id as 学生ID
,COALESCE((select score from sc where sc.student_id = s.student_id  and sc.course_id = (select id from course where name='english') ),0) as English
,COALESCE((select score from sc where sc.student_id = s.student_id and sc.course_id =  (select id from course where name='french')),0) as French
,COALESCE((select score from sc where sc.student_id = s.student_id and sc.course_id =  (select id from course where name='chinese')),0) as Chinese
,count(*) as 课程数,avg(s.score) as 平均分
from sc as s
group by s.student_id
order by avg(s.score) desc;

-- 15.按各科平均成绩从低到高和及格率的百分数从高到低顺序 显示

select course_id as 'Course ID',avg(score) as Average,sum(score>=60)/count(*) as 'Pass Rate'
from sc
group by course_id
order by sum(score>=60)/count(*) desc;


-- 16. 查询出只选修了一门课程的全部学生的学号和姓名
select id,name 
from student 
where id in
(
	select student_id
	from sc
	group by student_id
	having count(student_id) =1
);

-- 17. 查询各科成绩前三名的记录
-- 以下查询会报错：
-- ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
-- select sc1.student_id as '学生ID',sc1.course_id as '课程ID',sc1.score as '分数'
-- from sc as sc1
-- where sc1.score in
-- (
-- 	select score
-- 	from sc
-- 	where course_id = sc1.course_id
-- 	order by score desc
-- 	limit 3
-- )
-- order by sc1.course_id;

-- 18.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select s.id, s.name, tmp.Average
from student as s,
(
	select avg(score) as Average,student_id as sid
	from sc
	group by student_id
	having avg(score) > 85
) as tmp
where s.id = tmp.sid;


-- 19.查询课程名称为 chinese ，且分数低于60的学生姓名和分数 
select s.id,s.name, tmp.score
from student as s,
(	
	select student_id, score 
	from sc
	where score < 60
	and course_id =
	(
		select id
		from course
		where name= 'chinese'
	) 
)	as tmp
where s.id = tmp.student_id;

-- 20. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s.id,s.name,c.name,sc.score
from student as s,course as c,sc
where sc.score > 70
and s.id = sc.student_id
and c.id = sc.course_id
order by s.id;

-- 21.查询选修 TOM 老师所授课程的学生中，成绩最高的学生姓名、课程及其成绩
select student.id as '学生ID',student.name as '学生姓名',course.name as '课程名',tmp.highest as '最高分'
from student,course,
(
	select student_id as sid,score as highest,course_id as cid
	from sc
	where score = 
	(
		select max(score) from sc
		where course_id in 
		(
			select course.id
			from teacher,course
			where course.teacher_id = teacher.id
			and teacher.name = 'TOM'
		)
	) 
	and course_id in 
	(
		select course.id
		from teacher,course
		where course.teacher_id = teacher.id
		and teacher.name = 'TOM'
	)
) as tmp
where student.id = tmp.sid
and course.id = tmp.cid;

-- 22.查询全部学生都选修的课程的课程号和课程名
select id,name
from course
where id in
(
	select course_id
	from sc
	group by course_id
	having count(student_id) = (select count(id) from student)
);

-- 23.查询两门以上不及格课程的同学的学号及其平均成绩 
select s.id as '学生ID',
(select avg(score) from sc where student_id = s.id) as '平均分'
from student as s
where s.id in 
(
	select student_id
	from sc
	group by student_id
	having sum(score<60)  >=2
);

-- 24.统计各科成绩:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select c.id as '课程ID',c.name as '课程名'
,(select count(*) from sc where sc.course_id = c.id and (score <= 100 and score >=85)) as '[100-85]'
,(select count(*) from sc where sc.course_id = c.id and (score < 85 and score >=70)) as '[85-70]'
,(select count(*) from sc where sc.course_id = c.id and (score < 70 and score >=60)) as '[70-60]'
,(select count(*) from sc where sc.course_id = c.id and (score <60)) as '[<60]'
from course as c;