MySQL期末练习（含答案）

浅浅的花香味﹌ 2023-09-28 10:27 131阅读 0赞

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

#### **题目：** ####

使用的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个题目的关系代数表达式。  
![在这里插入图片描述][4bd658545bce43c3970d65a09fc92e1f.png]

[4bd658545bce43c3970d65a09fc92e1f.png]: https://img-blog.csdnimg.cn/4bd658545bce43c3970d65a09fc92e1f.png