数据库实验一

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

mysql> select ename from employee,works_on,project where employee.essn=works_on.
essn and works_on.pno=project.pno and pname='sql project';
+------------+
| ename      |
+------------+
| ZhangHong  |
| ZhangHong1 |
+------------+
2 rows in set (0.00 sec)


2：在“Research Department”工作且工资低于3000元的员工名字和地址；
mysql> select ename,address from employee,department where dname='research depar
tment' and salary<3000;
+------------+---------+
| ename      | address |
+------------+---------+
| ZhangHang1 | Harbin  |
| ZhangHang2 | Harbin  |
| ZhangHang3 | Harbin  |
| ZhangHang4 | Harbin  |
| ZhangHang5 | Harbin  |
| ZhangHang6 | Harbin  |
| ZhangHang7 | Harbin  |
| ZhangHang8 | Harbin  |
| ZhangHang9 | Harbin  |
| ZhangHang  | Harbin  |
| ZhangFei1  | Xian    |
| ZhangFei2  | Xian    |
| ZhangFei3  | Xian    |
| ZhangFei4  | Xian    |
| ZhangFei5  | Xian    |
| ZhangFei6  | Xian    |
| ZhangFei7  | Xian    |
| ZhangFei8  | Xian    |
| ZhangFei9  | Xian    |
| ZhangHong1 | Beijing |
| ZhangHong2 | Beijing |
| ZhangHong3 | Beijing |
| ZhangHong4 | Beijing |
| ZhangHong5 | Beijing |
| ZhangHong6 | Beijing |
| ZhangHong7 | Beijing |
| ZhangHong8 | Beijing |
| ZhangHong9 | Beijing |
+------------+---------+
28 rows in set (0.00 sec)


3：没有参加项目编号为P1的项目的员工姓名；
select ename from employee inner join works_on using(essn) where essn not in(select essn from employee inner join works_on using(essn) where pno =1) group by ename;
+------------+
| ename      |
+------------+
| Guanyu     |
| Guanyu1    |
| Liubei     |
| Liubei1    |
| ZhangFei   |
| ZhangFei1  |
| ZhangHang1 |
+------------+
7 rows in set (0.00 sec)


4：由张红领导的工作人员的姓名和所在部门的名字；
select ename,dname from employee,department where employee.dno = department.dno and superssn =(select essn from employee where ename = 'ZhangHong');
+------------+---------------------+
| ename      | dname               |
+------------+---------------------+
| ZhangHong  | Research Department |
| ZhangHong1 | Research Department |
| ZhangHong2 | Research Department |
| ZhangHong3 | Research Department |
| ZhangHong4 | Research Department |
| ZhangHong5 | Research Department |
| ZhangHong6 | Research Department |
| ZhangHong7 | Research Department |
| ZhangHong8 | Research Department |
| ZhangHong9 | Research Department |
+------------+---------------------+
10 rows in set (0.00 sec)


5：至少参加了项目编号为P1和P2的项目的员工号；
select essn from works_on where pno = 1 and essn  in  (select essn from works_on where pno =2);
+--------------------+
| essn               |
+--------------------+
| 610326199302111801 |
+--------------------+
1 row in set (0.00 sec)


6：参加了全部项目的员工号码和姓名；
select essn,ename from employee where essn in(select essn from(select essn,count(pname) as count from project,works_on  where project.pno = works_on.pno group by essn) as t where count in (select count(pname)as count from project));
+--------------------+------------+
| essn               | ename      |
+--------------------+------------+
| 610326199302111801 | ZhangHong1 |
+--------------------+------------+
1 row in set (0.00 sec)


7：员工平均工资低于3000元的部门名称；
select dname from(select dname,avg(salary) as avg from employee,department  where employee.dno = department.dno group by dname) as t where avg<3000;
+-----------------+
| dname           |
+-----------------+
| Kill Department |
+-----------------+
1 row in set (0.00 sec)


8：至少参与了3个项目且工作总时间不超过8小时的员工名字；
select ename from employee where essn in(select essn from(select essn,count(pname) as count,sum(hours) as sum from project,works_on  where project.pno =works_on.pno group by essn) as t where count>=3 and sum <=8);
+---------+
| ename   |
+---------+
| Guanyu2 |
+---------+
1 row in set (0.00 sec)


9：每个部门的员工小时平均工资；
select t1.dname,salary/hours/num from (select dname ,sum(hours) as hours from employee,department,works_on where employee.dno = department.dno and employee.essn= works_on.essn group by dname)as t2,(select dname,sum(salary) as salary,count(ename) as num from employee,department where employee.dno = department.dno group by dname) as t1 where t1.dname=t2.dname group by dname;
+---------------------+------------------+
| dname               | salary/hours/num |
+---------------------+------------------+
| Fight Department    |     211.11111111 |
| Kill Department     |     277.75000000 |
| Research Department |      80.85106383 |
| Sale Department     |     703.66666667 |
| Talk Department     |     372.52941176 |
+---------------------+------------------+

5 rows in set (0.00 sec)

附件：

数据库和实验要求

http://download.csdn.net/detail/u010772673/8680833