select
e.last_name,e.job_id,j.job_title
from
employees e,jobs j
where
e.job_id = j.job_id;
添加筛选条件
案例1:查询有奖金的员工名和部门名
select
e.last_name,d.department_name,commission_pct
from
employees e,departments d
where
e.department_id = d.department_id and
commission_pct isnotnull;# 条件之间使用and连接
案例2:查询城市名中第二个字符为o的部门名和城市名
select
d.department_name,l.city
from
departments d,locations l
where
d.location_id = l.location_id and
city like'_o%';
添加分组
案例1:查询每个城市的部门个数
selectcount(*) 个数,city
from
departments d,locations l
where
d.location_id = l.location_id
groupby
city;
案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select
d.department_name,d.manager_id,min(salary)from
departments d,employees e
where
d.department_id = e.department_id and
commission_pct isnotnull;groupby
department_name,d.manager_id;
添加排序
查询每个工种的工种名和员工的个数,按员工个数降序
select
job_title,count(*)from
jobs j,employees e
where
e.job_id = j