多表查询之非等值查询

#非等值连接
#案例1：查询员工的工资和工资级别
SELECT
    salary,
    grade_level
FROM
    employees e,
    job_grades g
WHERE
    salary BETWEEN g.lowest_sal
AND g.highest_sal
AND g.grade_level = 'A';

#3.自连接
#案例：查询员工名和上级的名称
SELECT
    e.employee_id,
    e.last_name,
    m.employee_id,
    m.last_name
FROM
    employees e,
    employees m
WHERE
    e.manager_id = m.employee_id;

#作业
#1、显示员工表的最大工资，工资平均值
SELECT
    Max(salary),
    avg(salary)
FROM
    employees;

#查询员工表的 employee_id, job_id, last_name，按 department_id降序， salary升序
SELECT
    employee_id,
    job_id,
    last_name
FROM
    employees
ORDER BY
    department_id DESC,
    salary;

#三、查询员工表的bid中包含a和e的，并且a在e的前面
SELECT
    job_id
FROM
    employees
WHERE
    job_id LIKE '%a%e%';

/*四、已知表 student，里面有id（学号），name，gradeId（年级编号）
    已知表grade，里面有id（年级编号），name（年级名）
    已知表result，里面有id, score,studentNo（学号）
要求查询姓名、年级名、成绩 */
SELECT
    s. NAME,
    g. NAME,
    r.score
FROM
    student s,
    grade g,
    result r
WHERE
    s.id = r.studentNo
AND s.gradeId = g.id;

#五、显示当前日期，以及去前后空格，截取子字符串的函数
SELECT
    NOW() SELECT
        trim(字符 FROM '');

SELECT
    substr(str, startIndex);

SELECT
    sunstr (str, startIndex, length);

#1.显示所有员工的姓名，部门号和部门名称
USE myemployees;

SELECT
    last_name,
    d.department_id,
    d.department_name
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id;

#2.查询90号部门员工的job_id和90号部门的1ocation id
USE myemployees;

SELECT
    e.job_id,
    d.location_id
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id
AND e.department_id = 90;

#3选择所有有奖金的员工的last_name,department_name,location_id,city
SELECT
    last_name,
    department_name,
    l.location_id,
    city
FROM
    employees e,
    departments d,
    locations l
WHERE
    l.location_id = d.location_id
AND e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

#4.选择city在Toronto工作的员工的last_name,job_id,department_id,department_name
SELECT
    last_name,
    job_id,
    d.department_id,
    department_name
FROM
    employees e,
    departments d,
    locations l
WHERE
    e.department_id = d.department_id
AND l.location_id = d.location_id
AND city = 'Toronto';

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

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

#7.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
#employees Emp# manager Mar#
#kochhar    101   king   100
SELECT
    e.last_name employees,
    e.employee_id "Emp#",
    m.last_name manager,
    m.employee_id "Mgr#"
FROM
    employees e,
    employees m
WHERE
    e.manager_id = m.employee_id
AND e.last_name = 'kochhar';

CREATE TABLE job_grades (
    grade_level VARCHAR (3),
    lowest_sal INT,
    highest_sal INT
);

INSERT INTO job_grades
VALUES
    ('A', 1000, 2999);

INSERT INTO job_grades
VALUES
    ('B', 3000, 5999);

INSERT INTO job_grades
VALUES
    ('C', 6000, 9999);

INSERT INTO job_grades
VALUES
    ('D', 10000, 14999);

INSERT INTO job_grades
VALUES
    ('E', 15000, 24999);

INSERT INTO job_grades
VALUES
    ('F', 25000, 40000);