sql 练习题 （二）
创建表：
员工 employees
工资表 salary
部门 departments
create or replace table employees(
empID INT(10) NOT NULL UNIQUE PRIMARY KEY ,
empName VARCHAR(20) NOT NULL , #姓名
sex VARCHAR(4) , #性别
birth date, #出生日期
deptID INT(20) , #部门编码
jobs VARCHAR(20) , #工作岗位
firJob date, #开始工作的日期
hiredate date,#入职日期
politicalStatus VARCHAR(20) , # 政治面貌
leader INT(10) #领导编码
);
create or replace table salary(
sid INT(10) NOT NULL UNIQUE PRIMARY KEY ,
empID INT(10) not null,
salary INT(10) not null,#工资
lastedit date #上次调薪日期
);
create or replace table departments(
deptid INT(10) NOT NULL UNIQUE PRIMARY KEY ,
deptname VARCHAR(20) not null,#部门名称
faterdeptid INT(10) # 上级部门编码
);
单表查
1、显示所有职工的基本信息。
SELECT * FROM `employees`;
2、查询所有职工所属部门的部门号，不显示重复的部门号。
SELECT DISTINCT deptID FROM employees
3、求出所有职工的人数.
SELECT COUNT(*) FROM employees
4、列出最高工和最低工资。
SELECT MAX(salary)AS '最高工资' ,MIN(salary) AS '最低工资' FROM salary
5、列出职工的平均工资和总工资。
SELECT AVG(salary)AS '平均工资',SUM(salary) AS '总工资' FROM salary
6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
CREATE table work_date(
workID INT(10) NOT NULL UNIQUE PRIMARY KEY ,
empID INT(10) NOT NULL ,
employeesName VARCHAR(20) NOT NULL,
workDate date
);
7、显示所有女职工的年龄
SELECT employees.empID,employees.empName, TIMESTAMPDIFF(YEAR,birth,SYSDATE()) AS age FROM employees WHERE employees.sex = '女'
8、列出所有姓刘的职工的职工号、姓名和出生日期。
SELECT empID,empName, birth FROM employees WHERE empName LIKE '刘%'
9、列出1990年以前出生的职工的姓名、参加工作日期。
SELECT empName, firJob, birth FROM employees WHERE birth <'1990-00-00';
SELECT empName, firJob, birth FROM employees WHERE YEAR(birth) < 1990
10、列出总人数大于4的部门号和总人数。
SELECT deptID AS '部门号', COUNT(*) AS '总人数' FROM employees GROUP BY deptID HAVING COUNT(*)>= 4
11、列出所有陈姓和李姓的职工姓名。
SELECT empID,empName FROM employees WHERE empName LIKE '刘%' or empName LIKE '李%'
12、列出所有部门号为1002和1003的职工号、姓名。
SELECT empID,empName, deptID FROM employees WHERE deptID = '1002' or deptID = '1003';
SELECT empID,empName, deptID FROM employees WHERE deptID IN ('1002','1003')
13、将职工表worker中的职工按出生的先后顺序排序。
SELECT * FROM employees ORDER BY birth ASC
14、求出各部门党员的人数。
SELECT deptID,COUNT(*) FROM employees WHERE politicalStatus = 'dangyuan' GROUP BY deptID
多表查询：
1、列出每名职工的职工号、姓名和部门名。
SELECT employees.empID, employees.empName, departments.deptname FROM employees, departments WHERE employees.deptID = departments.deptid
2、列出市场部的所有女职工的姓名和政治面貌。
SELECT a.empName, a.politicalStatus FROM employees a, departments b WHERE b.deptname = '市场部' AND a.deptID = b.deptid
3、显示所有职工的姓名、部门名和工资数。
SELECT a.empID, a.empName, b.deptname, c.salary FROM employees a ,departments b, salary c WHERE a.deptID = b.deptid AND a.empID = c.empID
4、显示所有职工的职工号、姓名、部门名和工资，并按部门名顺序排列。
SELECT a.empID, a.empName, b.deptname, c.salary FROM employees a ,departments b, salary c WHERE a.deptID = b.deptid AND a.empID = c.empID
ORDER BY b.deptID
SELECT a.empID, a.empName, b.deptname, c.salary FROM employees a LEFT JOIN departments b ON a.deptID = b.deptid
LEFT JOIN salary c ON a.empID = c.empID ORDER BY b.deptID
5、显示各部门名和该部门的所有职工平均工资。
SELECT d.deptid, d.deptname, AVG(s.salary) AS '平均工资' FROM departments d LEFT JOIN employees a ON d.deptid = a.deptID LEFT JOIN
salary s ON a.empID = s.empID GROUP BY d.deptid
6、显示所有平均工资高于1200的部门名和对应的平均工资。
SELECT d.deptid, d.deptname, AVG(s.salary) AS '平均工资' FROM departments d LEFT JOIN employees a ON d.deptid = a.deptID LEFT JOIN
salary s ON a.empID = s.empID GROUP BY d.deptid HAVING AVG(s.salary)>1200
7、查询刘欣所在的部门。
SELECT a.empName, b.deptid, b.deptname FROM employees a ,departments b WHERE a.deptID = b.deptid AND a.empName = '刘欣'
8、查询部门名为财务处的职工的情况。
SELECT a.* FROM employees a ,departments b WHERE a.deptID = b.deptid AND b.deptname = '财务处'
9、列出工资在1000－2000之间的所有职工姓名。
SELECT a.empID, a.empName, b.salary FROM employees a ,salary b WHERE a.empID = b.empID AND b.salary>=1000 AND b.salary <2000
10、显示工资最高的前3名职工的职工号和姓名。
SELECT a.empID, a.empName, b.salary FROM employees a ,salary b WHERE a.empID = b.empID ORDER BY b.salary DESC LIMIT 3
