1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名
INSERT INTO student (name,course,score)
VALUES
('张小明','语文','65'),
('张小明','数学','67'),
('李颖','语文','72'),
('李颖','数学','96'),
('黄一山','语文','85'),
('黄一山','数学','99'),
('黄一山','英语','93');
select name from student GROUP BY name HAVING MIN(score)>80;
select distinct name from student where name not in (select distinct name from student where score<=80);
2. 删除冗余，删除所有信息都相同的学生记录
delete student where id not in (SELECT min(id) from student GROUP BY name,course,score);
3. 一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合.
SELECT a.name, b.name from team a, team b where a.name < b.name;
4. 面试题：怎么把这样一个aaa表
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
select year, (select amount from aaa m where month=1 and m.year=aaa.year) as m1,(select amount from aaa m where month=2 and m.year=aaa.year) as m2,(select amount from aaa m where month=3 and m.year=aaa.year) as m3,(select amount from aaa m where month=4 and m.year=aaa.year) as m4from aaa group by year;
5. sql面试题
有表A，结构如下：
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为：
p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。
结果：
select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id
,sum(case when s_id=2 then p_num else 0 end) as s2_id
,sum(case when s_id=3 then p_num else 0 end) as s3_id
from A group by p_id;
6. 每个部门工资最高的员工（如果有两个薪资相同的会显示出来）
select a.`Name`,a.Salary,b.Name from employee a,department b WHERE a.DepartmentId=b.Id And
a. Salary =(select MAX(Salary) from employee where DepartmentId = a.DepartmentId);
7. 删除表中多余的重复记录，重复记录是根据单个字段（name）来判断，只留有id最小的记录
DELETE FROM employee
WHERE name in (SELECT a.name from (SELECT name from employee GROUP BY name HAVING COUNT(name) > 1) AS a)
AND id not in (SELECT b.id from (SELECT min(id) id from employee GROUP BY name HAVING COUNT(name)>1) AS b);
8. 计算每个人的总成绩并排名（要求显示字段：姓名，总成绩）
SELECT name,sum(score) s from student GROUP BY name ORDER BY s DESC;
9. 列出各门课程成绩最好的学生（要求显示字段：学号，姓名，科目，成绩）
SELECT s.name,s.subject,s.score,s.stuid from student s,
(SELECT subject,max(score) ms from student GROUP BY subject) as m
WHERE s.subject=m.subject AND s.score=m.ms;
10. 列出各个课程的平均成绩（要求显示字段；课程，平均成绩）
SELECT subject, avg(score) from student GROUP BY subject;
如果读完觉得有收获的话，欢迎点【在看】，关注【程序员溪言】，及时获得精彩推送！！！回复口令”java”，获得大量学习视频资料！
扫描二维码
获取更多精彩
关注程序员溪言
点这里在看
