哈工大数据库实验一sql查询

1.实验目的

掌握MySQL关系数据库管理系统的基本命令，并熟练使用SQL语言管理MySQL数据库。掌握SQL语言的使用方法，学会使用SQL语言进行关系数据库查询，特别是聚集查询、连接查询和嵌套查询。

2.实验环境

Windows XP或Windows 7操作系统、MySQL5.1.41或MySQL5.6版本，Code Blocks编程环境。

3.实验内容

3.1实验任务

创建关系数据库COMPANY，使用SQL语言完成如下查询：

1：参加了项目名为“SQLProject”的员工名字；

select ename

from EMPLOYEE , PROJECT,works_on

where employee.ESSN=works_on.ESSN and

works_on.pno=project.PNO and

PROJECT. PNAME ='SQL Project';

2：在“ResearchDepartment”工作且工资低于3000元的员工名字和地址；

select ename,ADDRESS

from EMPLOYEE ,DEPARTMENT

whereemployee.DNO=department.DNO and SALARY<3000 and department.DNAME='ResearchDepartment'

3：没有参加项目编号为P1的项目的员工姓名；

SELECT eNAME

from employee

where ename not in (

select ename

from EMPLOYEE , works_on

where employee.ESSN=works_on.ESSN andworks_on.PNO='P1')

4：由张红领导的工作人员的姓名和所在部门的名字；

selectEMPLOYEE.ename ,department.DNAME

from EMPLOYEE ,department

whereemployee.DNO=department.DNO and employee.SUPERSSN in (

 

SELECT ESSN

from employee

WHERE ename="张红"

)

 

5：至少参加了项目编号为P1和P2的项目的员工号；

select EMPLOYEE.ESSN

from EMPLOYEE , works_on

where employee.ESSN=works_on.ESSN andworks_on.PNO='P2' and employee.ESSN in (

select EMPLOYEE.ESSN

from EMPLOYEE , works_on

where employee.ESSN=works_on.ESSN andworks_on.PNO='P1'

)6：参加了全部项目的员工号码和姓名；

 

select   ENAME,ESSN

from EMPLOYEE NATURAL join  works_on

GROUP BY ENAME

HAVING COUNT(PNO)=(SELECT count(*) from project)

 

7：员工平均工资低于3000元的部门名称；

select dname

from EMPLOYEE NATURAL join  department

GROUP BY DNAME

HAVING avg(SALARY)<3000

8：至少参与了3个项目且工作总时间不超过8小时的员工名字；

select ename

from EMPLOYEE NATURAL join  works_on

GROUP BY eNAME

HAVING sum(HOURS)<=18 and count(PNO)>=3

9：每个部门的员工小时平均工资；

select dno,sum(SALARY)/sum(HOURS)

from EMPLOYEENATURAL join  works_on

GROUP BY DNO

注意：

（1）需要在课前创建关系数据库COMPANY并准备数据，详见3.2，3.3；

（2）每人抽查三个查询，根据抽查情况给分；

（3）本次实验不需要提交实验报告，成绩以课上检查为准；

（4）实验二会用到COMPANY数据库。

3.2关系数据库COMPANY介绍

创建关系数据库COMPANY，其模式如下（下划线表示关系的主键）：

 

关系EMPLOYEE(ENAME, ESSN, ADDRESS, SALARY, SUPERSSN, DNO)

ENAME: 工作人员名字，

ESSN: 工作人员身份证号，

ADDRESS: 工作人员住址，

SALARY: 工作人员工资，

SUPERSSN: 工作人员直接领导的身份证号，

DNO: 所属部门号

 

关系DEPARTMENT(DNAME, DNO, MGRSSN, MGRSTARTDATE)

DNAME: 部门名，

DNEMBER: 部门号，

MGRSSN: 部门领导身份证号，

MGRSTARTDATE: 部门领导开始领导工作的日期

 

关系PROJECT(PNAME, PNO, PLOCATION, DNO)

PNAME: 工程项目名，

PNO: 工程项目号，

PLOCATION: 工程项目所在地，

DNO: 工程项目所属部门号

 

关系WORKS_ON(ESSN, PNO, HOURS)

ESSN: 工作人员身份证号，

PNO: 工程项目号，

HOURS: 工作小时数

3.3数据准备

向创建的数据库COMPANY中添加数据，以备后续查询使用。

要求数据库中至少包含50个员工，5个部门，10项工程，

并且必须包含“研发部”、编号为P1和P2的项目、名叫张红的员工。