# 等值连接
# 1 查询员工名对应的部门名
SELECTemp.last_name,dep.department_name
FROMemployees emp,departments dep
WHEREemp.department_id = dep.department_id;# 2 查询员工名、工种名、工种号
SELECTemp.last_name,job.job_title,job.job_id
FROMjobs job,employees emp
WHEREjob.job_id = emp.job_id # 3 查询有奖金的员工名和部门名SELECTemp.last_name,dep.department_nameFROMemployees emp,departments depWHEREemp.department_id = dep.department_idAND emp.commission_pct IS NOT NULL;# 4 查询城市名中第二个字符是‘O’的部门
SELECTdep.department_name,loc.city
FROMlocations loc,departments dep
WHEREloc.location_id = dep.location_id
AND loc.city LIKE '_o%';# 5 查询每个城市的部门个数
SELECTCOUNT(1),loc.city
FROMlocations loc,departments dep
WHEREloc.location_id = dep.location_id
GROUP BYloc.city;# 6 查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECTdep.department_name,dep.manager_id,MIN(emp.salary)
FROMemployees emp,departments dep
WHEREemp.department_id = dep.department_id
AND emp.commission_pct IS NOT NULL
GROUP BYemp.department_id;# 7 查询每个工种的工种名和员工个数,并且按员工个数降序
SELECTjob.job_title,COUNT(*)
FROMjobs job,employees emp
WHEREjob.job_id = emp.job_id
GROUP BYemp.job_id
ORDER BYCOUNT(1) DESC;# 8 查询员工名、部门名和所在的城市
SELECTemp.last_name,dep.department_name,loc.city
FROMemployees emp,departments dep,locations loc
WHEREemp.department_id = dep.department_id
AND dep.location_id = loc.location_id;
2 非等值连接
# 非等值连接
# 1 查询员工的工资和工资级别
SELECTemp.last_name,gra.grade_level,emp.salary
FROMemployees emp,job_grades gra
WHEREemp.salary BETWEEN gra.lowest_sal
AND gra.highest_sal;
3 自连接
# 自连接
# 1 查询员工名和上级领导名称
SELECTemp.last_name emp,man.last_name man
FROMemployees emp,employees man
WHEREemp.manager_id = man.employee_id;