SQL试题练习

一、查询员工的全名，email和电话
SELECT concat(first_name,',',last_name),email,phone_int FROM employees;
//concat(str1, str2,...)功能：将多个字符串连接成一个字符串
例1:select concat (id, name, score) as info from employee;
例2:select concat (id, ',', name, ',', score) as info from employee;
//concat_ws(str1, str2,...)功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）
例3:select concat_ws (',',id, name, score) as info from employee;
//group_concat()功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
例4:使用group_concat()和group by显示相同名字的人的id号：
    select name, group_concat ( id ) from employee group by name;
例5:将上面的id号从大到小排序，且用'_'作为分隔符
    select name, group_concat ( id order by id desc separator '_' ) from employee group by name; 
例6：上面的查询中显示了以name分组的每组中所有的id。接下来我们要查询以name分组的所有组的id和score：
    select name, group_concat ( concat_ws ('_',id, score) order by id) from employee group by name; 

二、查询所有员工的全名，月薪和年薪（月薪*12）
SELECT first_name,last_name,salary,salary*12 FROM employees;

三、查询所有员工的全名，月薪和年终奖(年薪*commission_pct)
SELECT first_name,last_name,salary,salary*12*ifnull(commission_pct,0) FROM employees;

四、查询哪些部门和职位有员工
SELECT DISTINCT department_id,job_id from employees WHERE department_id is not null;

五、查询1999年之后入职的员工信息
SELECT first_name,last_name,hire_date FROM employees WHERE year(hire_date) >= '1999'

六、查询公司的老板信息
SELECT first_name,last_name FROM employees WHERE manager_id IS NULL

七、查询所有员工信息，按照部门和年薪降序排序；
SELECT first_name,last_name,salary*12 as total FROM employees ORDER BY department_id,total DESC

八、给用户名加密，保留员工全名前3位，中间4位使用*代替，如果姓名还有多余的字符，保留；
SELECT concat(RPAD(substring(concat(first_name,last_name),1,3),7,'*'),SUBSTRING(concat(first_name,last_name),8)) FROM employees

九、计算员工姓名和全薪；
SELECT first_name,last_name,salary,salary*12*(1+ifnull(commission_pct,0)) FROM employees

十、查询员工所属的部门id，如果没有部门，打印“未分配部门”
SELECT first_name,last_name,ifnull(department_id,'未分配部门') FROM employees

十一、查询出每一个部门的平均工资
SELECT department_id,AVG(salary) FROM employees WHERE department_id is not null GROUP BY department_id 

十二、查询平均工资高于8000的部门和其平均工资
SELECT department_id,AVG(salary) FROM employees WHERE department_id is not null GROUP BY department_id HAVING AVG(salary)>=8000

十三、查询换过工作员工换工作的次数
SELECT employee_id,COUNT(employee_id) FROM job_history GROUP BY employee_id

十四、查询在95，96，97，98年各进公司多少人
SELECT year(hire_date),count(employee_id)
FROM employees
WHERE YEAR(hire_date) in ('1995','1996','1997','1998')
GROUP BY YEAR(hire_date)

十五、输出员工名称，员工id，员工所属部门
SELECT first_name,last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id

十六、输出员工信息，包括employee_id,  first_name,    department_id,  department_name   location_id     city
SELECT e.employee_id,e.first_name,d.department_id,d.department_name,l.location_id,l.city
FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id

十七、在 EMPLOYEES 表中所有薪水位于JOB_GRADES表最低薪水和最高薪水之间雇员的薪水级别
SELECT e.first_name,e.last_name,e.salary
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal

SELECT e.first_name,e.last_name,e.salary
FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal

十八、查询所有有奖金的员工的姓名，部门，地址，城市
SELECT e.employee_id,e.first_name,d.department_id,d.department_name,l.location_id,l.city
FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL

十九、查询last_name为'Ki' 的员工的经理信息
SELECT first_name,last_name FROM employees WHERE employee_id IN (SELECT manager_id FROM employees WHERE last_name LIKE 'Ki%')

二十、查询公司工资最低的员工信息
SELECT first_name,last_name,salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees)

二十一、查询1999年来公司的所有员工的最高工资的员工
SELECT first_name,last_name,hire_date,salary
FROM employees WHERE salary=
(SELECT MAX(salary) FROM employees WHERE year(hire_date)='1999')
AND year(hire_date) = '1999'

二十二、查询曾经做过ST_CLERK的员工信息
SELECT e.first_name,e.last_name,j.job_id FROM employees e JOIN job_history j ON e.employee_id = j.employee_id WHERE j.job_id = 'ST_CLERK'