mysql__习题

1.部门与员工

部门表dept

员工表staff

– 部门表
CREATE TABLE dept(
id INT PRIMARY KEY,
dept_name VARCHAR(10),
dept_desc VARCHAR(10),
dept_date DATE
)
– 员工表
CREATE TABLE staff(
staff_id INT PRIMARY KEY,
staff_name VARCHAR(5),
staff_gender VARCHAR(1),
staff_age INT,
staff_money INT,
staff_deptid INT
)

– 1.查询所有员工信息，信息内容包括(id,员工姓名,性别,年龄,工资,部门名称)

ALTER TABLE staff ADD CONSTRAINT staff_deptid_fk FOREIGN KEY(staff_deptid) REFERENCES dept(id)
SELECT staff_name,staff_gender,staff_age,staff_money,dept_name FROM staff INNER JOIN dept ON staff.`staff_deptid`=dept.`id`

– 2.查询出工资最高的员工，并显示员工信息

SELECT staff_name,staff_gender,staff_age,staff_money,dept_name FROM staff INNER JOIN dept ON staff.`staff_deptid`=dept.`id`
WHERE staff_money=(SELECT MAX(staff_money) FROM staff)

– 3.查询出每个部门工资最高的员工信息

SELECT staff_name,staff_gender,staff_age,staff_money,dept_name FROM staff s INNER JOIN dept d ON s.staff_deptid=d.id 
                                   WHERE staff_money IN(SELECT MAX(staff_money) FROM staff GROUP BY staff_deptid)

– 4.查询所有男性员工工资总和,平均工资,最高工资,最低工资

SELECT SUM(staff_money) FROM staff WHERE staff_gender="男"
SELECT AVG(staff_money) FROM staff WHERE staff_gender="男"
SELECT MAX(staff_money) FROM staff WHERE staff_gender="男"
SELECT MIN(staff_money) FROM staff WHERE staff_gender="男"

– 5.查询工资大于平均工资的员工信息

SELECT staff_name,staff_gender,staff_age,dept_name  FROM staff  INNER JOIN dept ON staff.`staff_deptid`=dept.`id` WHERE staff_money>(SELECT AVG(staff_money) FROM staff)

– 6.查询所有姓王员工的姓名和性别

SELECT staff_name,staff_gender FROM staff WHERE staff_name LIKE "王%"

– 7.查询年龄最大的前3个员工的姓名和年龄

SELECT staff_name,staff_age FROM staff ORDER BY staff_age DESC LIMIT 0,3

– 8.统计每个部门的工资总和,显示信息：部门名称，工资总和

SELECT dept_name 部门名称,SUM(staff_money) 工资总和 FROM staff INNER JOIN dept ON dept.`id`=staff.`staff_deptid` GROUP BY staff_deptid

– 9.统计每个部门的总人数,显示信息：部门名称，部门人数

SELECT dept_name 部门名称,COUNT(staff_deptid) 部门人数 FROM staff INNER JOIN dept ON dept.`id`=staff.`staff_deptid` GROUP BY staff_deptid