sql的价值？（20K）

sql练习干货（二）

---

建议：先自己写，再看答案

---

大佬们___先活动活动颈椎

1、假设表team 结构如下：
ID(number) Name(varchar2)
1 a
2 b
3 b
4 a
5 c
6 C
请写出sql语句执行一个删除操作，当Name 列上有相同时，只保留ID这列上值小的记录。
例如：删除后的结果如下：
ID(number) Name(varchar2)
1 a
2 b
3 c

2、有一张表，里面有3个字段；语文，数学，英语。 其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这
三条记录并按以下条件显示出来
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格

3、拷贝表（拷贝数据，源表名：a 目标表名：b）

4、有两个表tab12和tab13,均有key和value两个字段，如果tab13的key在tab12中也有，就把tab13的value换为tab12中对应的value

5、已知数据库表a和b，两个表共享id，写出a和b关联查询的sql

6、存在employee结构如下
Emp_id number(4) not null --员工编号
Emp_name varchar2(20)not null --员工姓名
Dept_id number(2) --部门编号
Job_id varchar2(30) --岗位编号
Salary number(8,2) --薪水
Emp_id Emp_name Dept_id Job_id salary
0021 张三 001 001 12000
0022 李四 001 002 11000
0023 李磊 002 001 1200
0021 张三 001 001 12000
0024 全峰 003 021 8000
根据此表结构请写出sql完成如下要求：
1）请写出sql语句查询出工资高于9100的员工记录
2）列出每个部门的最高工资、最低工资、平均工资
3）列出超出每个部门平均工资的人的人员编号、姓名、工资和所在部门的平均工资
4）按照上述表结构请写出sql完成如下要求：上表中由于没有设置主键，导致存在重复记录（比如0021），请写出sql完成如下功能：删除
人员编号重复的记录

7、有3个表 S, C, SC
S (SNO, SNAME) 代表 （学号，姓名）
C(CNO, CNAME, CTEACHER) 代表（课号，课名，教师）
SC(SNO, CNO, SCGRADE) 代表（学号，课号，成绩）
问题：
1）找出没选过“黎明”老师的所有学生的姓名
2）列出2门以上（含2门）不及格学生的姓名及平均成绩。
3）既学过1号课程又学过2号课所有学生的姓名

8、学生课程成绩表
序号 学生id 课程id 课程成绩
id sid cid Score
1 1 1 50.5
2 1 1 60.5
3 1 2 89.5
4 1 3 70
5 2 1 70
6 2 3 73
7 3 2 72
1个人有多门课程，由于有补考，有人1门课程又多次成绩；
计算：1）每个人有多少门课程，倒序；
2）计算每个学生的所有课程的平均成绩，倒序（补考的按最后成绩算）

9、表User
id Name
1 zhangsan
2 lisi
3 lisi
4 wangwu
5 wangwu
…… ……
10000条数据，每10条数据必有重复name；写一句sql去重复数据，id正序，重复数据留第一条；展示id，name;
如上表：去掉后剩下数据id为1,2,4……;

10、现在有下列三张表，请根据条件写sql语句.
学生信息表：student (student_id, student_name, sex, age);
课程信息表：course(course_id, course_name);
成绩信息表：score(student_id, course_id, score);
1）查询年龄大于14岁的前10名学生信息
2）查出学生id等于“001”的学生所有课程得分
3）查出所有课程得分都大于80分的女生姓名列表；

哇！好可爱啊！！！好养眼啊！！哇！还跟我动作一样哎。

答案：

一：

答案：1）delete from team where id not in (select min(id) from team group by name)

二：

 答案：1）select case when 语文>=80 then '优秀' when 语文>=60 then '及格' else '不及格' end 语文 ,
    case when 数学>=80 then '优秀' when 数学>=60 then '及格' else '不及格' end 数学 ,
    case when 英语>=80 then '优秀' when 英语>=60 then '及格' else '不及格' end 英语 from student

三：

    答案：
1）insert into b(a, b, c) select d,e,f from a;

四：

 答案：1）update tab13 set value=(select value from tab12 where tab12.key=tab13.key) 

五：

答案：1）select * from a left join b on a.id = b.id 或者 select * from a a1, b b1 where a1.id = b1.id

六：

 答案：
    1）select * from employee where salary > 9100
    2) select max(salary), min(salary), avg(salary) from employee group by Dept_id
    3) select e.Emp_id, e.Emp_name, e.salary, newe.sal from employee e left join (select e1.Dept_id,avg(salary) sal from employee e1 group by
    e1.Dept_id) newe on e.Dept_id = newe.Dept_id where e.salary > (select avg(salary) from employee group by Dept_id)
    4)
    

七：

 答案：
    1）select SnamefromS where Sno not in ( select Sno fromSC where Cno in (selectcno fromC where Cteacher='黎明') )
    2) SELECT S.SNO, S.SNAME, AVG(SC.SCGRADE)
    FROM S, SC
    WHERE S.SNO = SC.SNO AND S.SNO IN
    (
    SELECT SC.SNO FROM SC
    WHERE SC.SCGRADE< 60
    GROUP BY SC.SNO
    HAVING COUNT(SC.CNO) >= 2
    )
    GROUP BY S.SNO, S.SNAME
    3) SELECT S.SNO,S.SNAME
    FROM S,(
    SELECT SC.SNO
    FROM SC,C
    WHERESC.CNO=C.CNO
    ANDC.CNAMEIN('1','2')
    GROUP BYSNO
    HAVINGCOUNT(DISTINCT CNO)=2
    )SCWHERES.SNO=SC.SNO

八：

答案：1）select sid, count( distinct cid) from score group by sid ORDER BY COUNT(CID)
2)select sid, avg(score) from score where id in (select max(id) from score group by cid,sid) group by sid order by avg(score)

九：

 答案：1）select id,name from t_user where id in (select min(id) from t_user group by name)

十：

答案：1）select * from sudent where age > 14 and rownum < 11 order by student_id
2) select c.course_name, s.score from course c left join score s on c.course_id = s.course_id where s.student_id = '001'
3) select stu.student_name from student stu, 
(select student_id,min(score) from score group by student_id having min(score) > 80) e 
where stu.student_id = e.student_id

参考文献：

【sql的价值？（20K）---- 第一章】