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

#查詢每個國家下面的部門數(shù)大于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


#查詢指定員工信命，員工號，已經(jīng)管理者的姓名和員工號

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