练习题

SELECT CONCAT(last_name,',',first_name) '姓名', e.department_id '部门号', d.`department_name` '部门名'

FROM employees e, departments d

WHERE e.department_id= d.`department_id`


# 查询90号部门的员工job_id 和90号部门的locaton_id
SELECT  e.job_id ,e.department_id '部门号' , lo.location_id
FROM employees e, departments d, `locations` lo
WHERE e.department_id=d.department_id
AND d.location_id = lo.location_id

#选择所有有奖金的员工
SELECT *FROM  employees
WHERE  commission_pct IS NOT NULL

#选择城市在Toronto 工作的员工
SELECT e.employee_id, lo.city '城市',  CONCAT(e.`first_name`,'_',e.`last_name`)'姓名'  
FROM employees e, departments d, locations lo
WHERE e.department_id=d.department_id
AND d.location_id = lo.location_id
AND lo.city ='Toronto'

#查询每个工种，每个部门的部门名，工种名和最低工资
SELECT e.job_id, d.department_name,j.job_title,d.`department_id`,MIN(e.`salary`)
FROM employees e ,departments d, jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY d.`department_name`, j.job_title

#查询每个国家下面的部门数大于2的国家编号
SELECT  COUNT(*),l.country_id
FROM locations l,departments d
WHERE d.location_id=l.location_id
GROUP BY l.country_id
HAVING COUNT(*)>2


#查询指定员工信命，员工号，已经管理者的姓名和员工号

SELECT  CONCAT(e.`first_name`,'_',e.`last_name`) '姓名',e.`employee_id` '员工号', CONCAT(e2.`first_name`,'_',e.`last_name`) '管理者姓名',e2.`employee_id` '管理者员工号'
FROM employees e , employees e2
WHERE  e.`employee_id`=101
AND e.`manager_id`=e2.employee_id