1.假设有一个教学管理数据库有以下3个关系：
学生表：Students(学号sno,姓名sname,年龄age,性别sex,籍贯jg)、
课程表：Courses(课程号cno,课程名cname,学分xf)、
选课表：Enrolls(学号sno,课程号cno,分数score)：
1.1 查询哪些课程只有女生选读；
1.2 查各课程的平均成绩，按课程号分组，只选择学生超过3人的课程成绩；
1.3 找出不选“数据库”课程的学生学号、姓名；
1.4 找出选修了全部课程的学生姓名；
1.5 求平均成绩90分以上，选课男生人数3人以上的课程名。
-- 1.1
-- 关联（外连接）
select t1.cno
(select s.sno,e.cno from students s,enrolls e
where s.sno=e.sno and s.sex='女') t1
left join
(select s.sno,e.cno from students s,enrolls e
where s.sno=e.sno and s.sex='男') t2
on t1.cno=t2.cno
where t2.cno is null;
-- 子查询
select e.cno from students s,enrolls e
where s.sno=e.sno and s.sex='女'
and s.cno not in(select e.cno from students s,enrolls e
where s.sno=e.sno and s.sex='男')
-- 1.2
select avg(score) from Enrolls e group by e.cno having count(1)>3;
-- 1.3
select s.sno,s.sname from Students s
where s.sno not in (select e.sno from Enrolls e where e.cno =(select c.cno from Courses c where c.cname='数据库'))
-- 1.4
select s.sname from Students s
where s.sno in (select e.sno from Enrolls e group by e.sno having count(1)=(select count(1) from Courses c))
-- 1.5
select c.cname from Enrolls e,Courses c where e.cno=c.cno
group by e.cno having avg(e.score)>90
and e.cno in(select e.cno from Enrolls e,Students s
where e.sno=s.sno and s.sex='男' group by e.cno having count(1)>3);
2.Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Mark | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Mark 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Mark | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
答案：
select d.name as department, e.employee, c.salary
from employee e, department d
(select departmentid, max(salary) salary from employee group by deptmentid) c
where e.departmentid=d.departmentid and d.departmentid=c.departmentid
and e.salary = c.salary;
3.编写一个SQL查询，删除Info表中所有重复的电子邮箱，重复的邮箱只保留Id最小的记录。
+--------+-------------------+
| Id | Email |
+--------+-------------------+
| 1 | john@example.com |
| 2 | boss@example.com |
| 3 | john@example.com |
+--------+-------------------+
答：
delete from info a
where a.email in (select b.email from info b group by b.email having count(b.email ) >= 2)
and a.id not in (select min(c.id) from info c group by c.email having count(c.email ) >= 2);
4、根据题目要求，写出对应的sql
雇员表：employee
+-------+-------+-----+-----+
| EmpId | Name | sex | age |
+-------+-------+-----------+
| 1 | 张鹏 | 男 | 25 |
| 2 | 张晓 | 女 | 20 |
| 3 | 王明 | 男 | 22 |
| 4 | 王刚 | 男 | 24 |
| 6 | 彭飞 | 男 | 18 |
+-------+-------+-----+-----+
销售表：sal
+-------+--------------+-----+-----+---------+
| EmpId |product |price|count| date |
+-------+--------------+-----------+---------+
| 1 | 遥控飞机A 款 |1000 | 1 |20190520 |
| 2 | 遥控汽车 |600 | 1 |20190601 |
| 2 | 遥控飞机B 款 |800 | 1 |20190530 |
| 3 | 玩具枪 |100 | 2 |20190510 |
| 4 | 儿童自行车 |300 | 1 |20190520 |
+-------+--------------+-----+-----+---------+
问题1：查年龄大于 20 以上的员工信息
select * from employee e where e.age>20;
问题2：统计每个人的销售额
select s.empid, sum(s.price*s.count) from sal s group by s.empid;
问题3：统计5月份销售额为0的员工信息
select e.* from employee e left join (select * from sal s where s.date like '201905__') ss
on e.empid = ss.empid where ss.empid is null;
-- 外连接：左连接（左边为主表，主表每一行都显示）
select * from dept d left join emp e on d.deptno=e.deptno
where e.empno is null;
5、某班学生表和考试成绩表分别如下：
表student：
+-------+--------------+
| ID | Name |
+-------+--------------+
| 1 | Jack |
| 2 | Marry |
| 3 | Rose |
| 4 | Bob |
| 5 | john |
| 6 | Betty |
| 7 | Robert |
+-------+--------------+
表：Achievement
+-------+----------+
| ID | score |
+-------+----------+
| 1 | 90 |
| 2 | 96 |
| 3 | 88 |
| 4 | 86 |
| 5 | 83 |
| 6 | 83 |
+-------+----------+
其中 ID为学生的编号，Name 为姓名，Mark 为成绩，请针对以下问题，写出相应的 sql语句：
1、请查询成绩>85分的学生的姓名；
select b.name
from student b
where b.id in(select a.id from acheviment a where a.mark >85);
2、请查询成绩>=90分的人数：
select count(*) from acheviment a where a.mark>90;
3、Robert 此次考试考了80分，但是成绩没能录入表中，请将其成绩添加进去；
insert into acheviment values(7,80);
insert into acheviment (select s.id,80 from student s where s.name='Robert');
4、请将Rose 的成绩修改为 87；
update acheviment a set a.mark=87
where a.id=(select s.id from student s where s.name='Rose');
5、请删除 Betty 的记录；
delete from acheviment a
where a.id=(select s.id from student s where s.name='Bettty');
delete from student s where s.name='Bettty';
6. 查询出分数为 83 且姓名首字母为B开头的学生;
select * from acheviment a, student s
where a.id=s.id and a.mark=83 and s.name like 'B%';
6、把这样的图1学生表xs用一条SQL语句查询得到图2的效果。
图1: 学生表xs
图2：
解题思路：利用decode临时增加列名，并且对decode取别名，同时利用子查询。decode用于当前行的条件判断。
--创建xs学生表
create table xs(
xno varchar2(3) ,
name varchar2(10),
course varchar2(10),
score number(5,2)
)
--插入数据
insert into xs values ('01','张三','数学','80');
insert into xs values('01','张三','语文','81');
insert into xs values('01','张三','英语','88');
insert into xs values('02','王五','数学','70');
insert into xs values('02','王五','语文','90');
insert into xs values('02','王五','英语','77');
commit;
select x.* from xs x;
答案如下：
select x1.xno,x1.name,
sum(x1.shuxue) shuxue,
sum(x1.yuwen) yuwen,
sum(x1.yingyu) yingyu
from (select x.xno,x.name,
decode(x.course,'数学',x.score,null) shuxue,
decode(x.course,'语文',x.score,null) yuwen,
decode(x.course,'英语',x.score,null) yingyu
from xs x) x1
group by x1.xno,x1.name;
7.
Table A
+------+----------+---------+
|stuID | classID | stuName |
+------+----------+---------+
| 1 | A | chen |
| 2 | A | wang |
| 3 | B | li |
| 4 | B | yang |
+------+----------+---------+
Table B
+--------+----------+
|classID | className|
+--------+----------+
| A | 一班 |
| B | 二班 |
+--------+----------+
Table C
+------+--------+-------+
|stuID | course | score |
+------+--------+-------+
| 1 | 数学 | 80 |
| 1 | 英语 | 85 |
| 1 | 语文 | 90 |
| 2 | 数学 | 85 |
| 2 | 英语 | 85 |
| 2 | 语文 | 95 |
| 3 | 语文 | 88 |
| 4 | 英语 | 85 |
| 3 | 数学 | 95 |
| 3 | 英语 | 88 |
| 4 | 语文 | 85 |
| 4 | 数学 | 99 |
+------+-------+-------+
1.查找wang的数学分数；
select A.stuName, C.cource, C.score
from A,C where A.stuID=C.stuID and A.stuName='wang' and C.cource='数学';
2.查找一班各学科分数最高的学生；
select xx.stuName,yy.course,yy.max
from (select * from A,C where A.stuID=C.stuID) xx,
(select C.course,max(score) max from A,B,C
where A.classID=B.classID and A.stuID=C.stuID and B.className='一班'
group by C.course) yy
where xx.course=yy.course and xx.score=yy.max;
浙公网安备 33010602011771号