转自 http://blog.csdn.net/yangyi22/article/details/5379986
题目一:
有两张表:部门表department :
部门编号dept_id,
部门名称dept_name
员工表employee:
员工编号emp_id,
员工姓名emp_name,
部门编号dept_id,
工资emp_wage
CREATE TABLE department(
dept_id INT NOT NULL AUTO_INCREMENT,
dept_name CHAR(50) NOT NULL,
PRIMARY KEY(dept_id));
CREATE TABLE employee(
emp_id INT NOT NULL AUTO_INCREMENT,
emp_name CHAR(50) NOT NULL,
dept_id INT NOT NULL,
emp_wage INT,
PRIMARY KEY(emp_id),
FOREIGN KEY(dept_id) REFERENCES department(dept_id));
INSERT INTO department(dept_name) VALUES("app");
INSERT INTO department(dept_name) VALUES("algo");
INSERT INTO department(dept_name) VALUES("human affairs");
INSERT INTO department(dept_name) VALUES("administration");
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("1.wang", 1, 10000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("2.jin", 1, 10000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("3.yang", 1, 10000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("4.xie", 2, 15000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("5.liu", 2, 15000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("6.yang", 3, 30000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("7.pan", 3, 30000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("8.cheng", 3, 15000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("9.sun", 4, 8000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("10.juan", 4, 10000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("11.jiang", 1, 9000);
INSERT INTO employee(emp_name,dept_id, emp_wage) VALUES("12.lv", 1, 15000);
根据下列题目写出sql:
1、列出工资大于5000的员工所属的部门名、员工id和员工工资;
2、列出员工表中的部门id对应的名称和员工id(左连接)
3、列出员工大于等于2人的部门名称
4、列出工资最高的员工姓名
5、求各部门的平均工资
6、求各部门的员工工资总额
7、求每个部门中的最大工资值和最小工资值,并且它的最小值小于5000,最大值大于10000
8、假如现在在库中有一个和员工表结构相同的空表employee2,请用一条sql语句将employee表中的所以记录插入到employee2表中。
answer:
1:
列出工资大于5000的员工所属的部门名、员工id和员工工资;
select emp_id,emp_wage,dept_name
from employee as e inner join department as d
on e.dept_id=d.dept_id
where e.emp_wage>5000
group by e.emp_id;
2:
列出员工表中的部门id对应的名称和员工id(左连接)
select dept_name,emp_id from department d left join employee e on e.dept_id=d.dept_id group by e.emp_id;
+------------+--------+
| dept_name | emp_id |
+------------+--------+
| 咨询部 | NULL |
| 软件开发部 | 1001 |
| 市场策划部 | 1002 |
| 销售部 | 1003 |
| HR | 1004 |
| HR | 1005 |
| HR | 1006 |
| 软件开发部 | 1007 |
+------------+--------+
| dept_name | emp_id |
+------------+--------+
| 咨询部 | NULL |
| 软件开发部 | 1001 |
| 市场策划部 | 1002 |
| 销售部 | 1003 |
| HR | 1004 |
| HR | 1005 |
| HR | 1006 |
| 软件开发部 | 1007 |
+------------+--------+
3:
列出员工大于等于2人的部门编号
名称
select dept_name from department d [inner] join employee e on d.dept_id=e.dept_id group by dept_name
having count(e.dept_id) >=2;
4:
列出工资最高的员工姓名
select * from employee
where emp_wage =(select max(emp_wage) from employee);
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1007 | ad | 1 | 12000 |
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1007 | ad | 1 | 12000 |
+--------+----------+---------+----------+
5:
求各部门的平均工资
select dept_name,AVG(emp_wage) as '平均工资' from employee e join department d on e
.dept_id=d.dept_id Group by dept_name;
+------------+------------+
| dept_name | 平均工资 |
+------------+------------+
| HR | 7500.0000 |
| 市场策划部 | 2500.0000 |
| 软件开发部 | 10000.0000 |
| 销售部 | 3200.0000 |
+------------+------------+
| dept_name | 平均工资 |
+------------+------------+
| HR | 7500.0000 |
| 市场策划部 | 2500.0000 |
| 软件开发部 | 10000.0000 |
| 销售部 | 3200.0000 |
+------------+------------+
如果用右连接:
select dept_name,AVG(emp_wage) as '平均工资' from employee e right
join department d on e.dept_id=d.dept_id Group by dept_name;
+------------+------------+
| dept_name | 平均工资 |
+------------+------------+
| HR | 7500.0000 |
| 咨询部 | NULL |
| 市场策划部 | 2500.0000 |
| 软件开发部 | 10000.0000 |
| 销售部 | 3200.0000 |
+------------+------------+
| dept_name | 平均工资 |
+------------+------------+
| HR | 7500.0000 |
| 咨询部 | NULL |
| 市场策划部 | 2500.0000 |
| 软件开发部 | 10000.0000 |
| 销售部 | 3200.0000 |
+------------+------------+
6:求各部门的员工工资总额
select dept_name,SUM(emp_wage) as '部门工资总额' from employee e join department d on e.dept_id=d.dept_id
Group by dept_name;
7: 求每个部门中的最大工资值和最小工资值,并且它的最小值小于5000,最大值大于10000
select dept_name ,max(emp_wage)from employee e inner join department d on e.dept_id=d.dept_id
where emp_wage>=10000
group by dept_name
union all
select dept_name ,
min(emp_wage) from employee e inner join department d on e.dept_id=d.dept_id
where emp_wage<=5000
group by dept_name;
如果是求每个部门中的最大工资值和最小工资值-->
select dept_name ,max(emp_wage),min(emp_wage)
from employee e
inner join department d
on e.dept_id=d.dept_id
group by dept_name;
8:
假如现在在库中有一个和员工表结构相同的空表employee2,请用一条sql语句将employee表中的所以记录插入到employee2表中。
insert into employee2 select * from employee;