等值连接

1查找城市名中第二个字符为o的部门名和城市名

SELECT
    city 城市名,
    department_name
FROM
    locations,
    departments d
WHERE
    d.location_id = locations.location_id
AND locations.city LIKE '_o%'

2查询每个城市部门个数

SELECT COUNT(*) 个数,city FROM
    locations l,
    departments d
WHERE
    d.location_id = l.location_id GROUP BY city

3查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name,d.manager_id,MIN(e.salary) FROM

employees e,departments d
WHERE e.department_id =d.department_id
and e.commission_pct is not null
GROUP BY department_name,d.manager_id;

4查询每个工种的工种名和员工的个数,并按员工个数降序

SELECT
    job_title,
    count(*)
FROM
    employees e,
    jobs j
WHERE
    e.job_id = j.job_id
GROUP BY
    job_title
ORDER BY
    count(*) DESC

非等值连接

5查询员工的工资和工资级别

SELECT
    salary,
    grade_level
FROM
    employees e,
    job_grades jj
WHERE
    e.salary BETWEEN jj.lowest_sal
AND highest_sal

自连接

6查询员工名和上级的名称

SELECT
    e.employee_id,
    e.first_name,
    e.manager_id,
    m.first_name,
    m.manager_id
FROM
    employees e,
    employees m
WHERE
    e.manager_id = m.employee_id

自连接

自连接可以一张表拆分成多张表

7查询每个国家下的部门数大于2的个数
group by分组后 使用having

SELECT
    country_id,
    count(*) 部门个数
FROM
    locations l,
    departments d
WHERE
    l.location_id = d.location_id
GROUP BY
    country_id
HAVING
    count(*) > 2

非等值连接

8查询工资级别的个数>2的个数，并按工资级别降序

SELECT
    salary AS 工资,
    grade_level AS 级别,
    count(*)
FROM
    employees e
INNER JOIN job_grades jj ON e.salary BETWEEN jj.lowest_sal
AND highest_sal
GROUP BY
    grade_level DESC
HAVING
    count(*) > 2