MySQL期末练习（含答案）

数据库期末考试复习所用，有疑惑的同学，欢迎评论区讨论。

题目：

使用的employee数据库表结构如下
职工表：Emp（Eno，Ename，Eage，Esex，Ecity）
工作表：Works（Eno，Cno，Salary）
公司表：Comp（Cno，Cname，Ccity）

一、写出创建works表的语句，要求将Eno和Cno定义为主键，Eno和Cno分别定义成works表的外键，salary值默认为0。
二、写出以下的SQL语句
1、假设每个职工只能在一个公司工作，检索工资超过1000元男性职工工号和姓名。
2、假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工工号和姓名。
3、检索在“联华公司”工作、工资超过1000元的女性职工的工号和姓名。
4、假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示（工号，数目，工资总数）。
5、工号为E6的职工在多个公司工作，检索至少在E6职工兼职的所有公司工作的职工工号。
6、在每一公司中为50岁以上职工加薪100元（若职工为多个公司工作，可重复加）
7、在数据库中删除年龄大于60岁的职工有关元组
8、假如你是DBA，现在系统要求增加一个用户zhangsan，密码123456，并且zhangsan用户可以查询“联华公司”女职工的职工号、姓名、年龄和所在城市，请写出你完成该操作应该执行的sql语句。
9、创建一个视图，通过该视图可以查看每个职工的工号(Eno)、姓名(Ename)、年龄(Eage)、公司名(Cname)、公司所在城市(Ccity)和工资(Salary)
三、写出上题前3个题目的关系代数表达式。

答案如下：

一、写出创建works表的语句，要求将Eno和Cno定义为主键，Eno和Cno分别定义成works表的外键，salary值默认为0。
create table works(
Eno char(4),
Cno char(4),
Salary float default(0),
primary key(Eno,Cno),
foreign key (Eno) references Emp(Eno),
foreign key (Cno) references Comp(Cno)
)
二、写出以下的SQL语句
1、假设每个职工只能在一个公司工作，检索工资超过1000元男性职工工号和姓名。
select Emp.Eno,Ename
from Emp,works
where Emp.Eno=works.Eno and salary>=1000 and Esex =‘男’;

2、假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工工号和姓名。
select Emp.Eno,Ename
from Emp,works
where Emp.Eno=works.Eno and Cno=‘C4’
and Emp.Eno in (select Eno from works where Cno=‘C8’);

3、检索在“联华公司”工作、工资超过1000元的女性职工的工号和姓名。
select Emp.Eno,Ename
from Emp,works,Comp
where Emp.Eno=works.Eno and works.Cno=Comp.Cno
and Cname=‘联华公司’ and salary>=1000 and Esex=‘女’;

4、假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示（工号，数目，工资总数）。
select Eno 工号,count(Cno) 数目,sum(salary) 工资总数
from works
group by Eno;

5、工号为E6的职工在多个公司工作，检索至少在E6职工兼职的所有公司工作的职工工号。
select Eno
from Emp y
where not exists(select * from works x where Eno=‘E6’
and not exists(select * from works where Eno=y.Eno and Cno=x.Cno) );

Select distinct Eno
From works where cno >= all(
Select cno
From works
Where Eno=‘E6’
)

6、在每一公司中为50岁以上职工加薪100元（若职工为多个公司工作，可重复加）
update works
set salary=salary+100
where Eno in (select Eno from Emp where Eage>=50);

7、在数据库中删除年龄大于60岁的职工有关元组
delete from works
where Eno in (select Eno from Emp where Eage>60);
delete from Emp
where Eage>60;

8、假如你是DBA，现在系统要求增加一个用户zhangsan，密码123456，并且zhangsan用户可以查询“联华公司”女职工的职工号、姓名、年龄和所在城市，请写出你完成该操作应该执行的sql语句。
(1)Create user ‘zhangsan’ @‘%’ identity by password=’123456’;
(2)Use 职工数据库
(3)Create view f_lianhua
As select eno,ename,eage,ecity
from emp
where eno in(select eno from works where cno in (select cno from comp where cname=’ 联华公司’))
and esex=’女’;
(4) grant select on f_lianhua to zhangsan;

9、创建一个视图，通过该视图可以查看每个职工的工号(Eno)、姓名(Ename)、年龄(Eage)、公司名(Cname)、公司所在城市(Ccity)和工资(Salary)
create view v_emp
as
select Eno, Ename, Eage, Cname, Ccity, Salary
from Emp,Works, Comp
where Emp.Eno=Works.Eno and Works.Cno=Comp.Cno;

三、写出上题前3个题目的关系代数表达式。