1.假设有一个教学管理数据库有以下3个关系：
学生表：Students(学号sno,姓名sname,年龄age,性别sex,籍贯jg)、
课程表：Courses(课程号cno,课程名cname,学分xf)、
选课表：Enrolls(学号sno,课程号cno,分数score)：
1.查询哪些课程只有女生选读；
2.查各课程的平均成绩，按课程号分组，只选择学生超过3人的课程成绩；
3.找出不选“数据库”课程的学生学号、姓名；
4.找出选修了全部课程的学生姓名；
5.求平均成绩90分以上，选课男生人数3人以上的课程名。
-- 2.1
select ee.* from enrolls ee
where ee.cno not in
(select b.cno
from
(select e.* from enrolls e
where e.sno in (select s.sno from students s where s.sex='女')) a
right join enrolls b
on a.sno=b.sno
where a.sno is null)
-- 2.2
select avg(score) from Enrolls e group by e.cno having count(1)>3;
-- 2.3
select s.sno,s.sname from Students s
where s.sno in (select e.sno from Enrolls e where e.cno !=(select c.cno from Courses c where c.cname='数据库'))
-- 2.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))
-- 2.5
select c.cname from Courses c
where c.cno in
(select e.cno from Enrolls e where e.sno in (select s.sno from Students s where s.sex='男')
group by e.cno having count(1)>3 and avg(score)>90)
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 sal s on e.empid = s.empid
where s.date like '201905__' and s.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%';