SQL高级查询

一、实验目的

1、掌握多表连接、子查询和分组统计的SQL语法；

2、熟悉SQL内、外连接的语法、使用场景和用法；

3、了解in、exist、any、all等谓词的使用方法；

4、了解触发器的基本原理和编写方法。

二、实验内容

根据oracle 的sample db, 进行如下的SQL练习：

1、查询所有仓库的仓库名称、地址、邮政编码、城市、州名、国家名称和区域名称。

SELECT warehouse_name,
    address,
    postal_code city,
    state,
    country_name,
    region_name
FROM warehouses
    NATURAL JOIN locations
    NATURAL JOIN countries
    NATURAL JOIN regions;

2、查询所有客户联系人（contacts）和雇员的信息，按照以下格式显示：

姓名（first_name空格last_name）、email、人员类别（客户联系人显示“contact”，雇员显示“employee”），并按照姓名升序排序；

SELECT CONCAT(first_name,' ',last_name) AS full_name,
    email,
    "contact" AS type
FROM contacts
UNION
SELECT CONCAT(first_name,' ',last_name) AS full_name,
    email,
    "employee" AS type
FROM employees
ORDER BY full_name ASC;

查询所有客户联系人（contacts）和雇员的姓氏信息，如果有重复的话，如何去重。

SELECT DISTINCT last_name
FROM (
        SELECT DISTINCT last_name
        FROM contacts
        UNION
        SELECT DISTINCT last_name
        FROM employees
    ) CE;

3、查询有销售人员参与成交的订单客户信息，结果显示客户联系人姓名、客户公司名称和销售代表姓名。

SELECT DISTINCT CONCAT(c.first_name, ' ', c.last_name) C_name,
    m.name,
    CONCAT(e.first_name, ' ', e.last_name) E_name
FROM contacts c,
    customers m,
    orders o,
    employees e
WHERE c.customer_id = m.customer_id
    AND o.customer_id = m.customer_id
    AND o.salesman_id = e.employee_id;

4、查询2017年销售额高于100万的销售员，显示ID和姓名。

SELECT employee_id,
    CONCAT(first_name, ' ', last_name)
FROM employees
WHERE employee_id IN (
        SELECT salesman_id
        FROM order_items
            NATURAL JOIN orders
        WHERE YEAR(order_date) = 2017
            AND status <> 'Canceled'
        GROUP BY salesman_id
        HAVING SUM(quantity * unit_price) > 1000000
    );

5、查询没有任何人购买的商品名称和商品描述。

SELECT product_name,
    description
FROM products
WHERE product_id NOT IN (
        SELECT product_id
        FROM order_items
    );

6、查询45号客户46号客户都订购过的产品信息（产品ID和名称）(提示：可以采用集合运算或者outer join)。

SELECT product_id,
    product_name
FROM products
    NATURAL JOIN order_items
    NATURAL JOIN orders
WHERE customer_id = 45
INTERSECT
SELECT product_id,
    product_name
FROM products
    NATURAL JOIN order_items
    NATURAL JOIN orders
WHERE customer_id = 46;

7、查询所有客户订单状态为”Shipped”的如下统计信息：每一位客户的ID、姓名、订单数量、订单总金额和订单平均金额。

SELECT customer_id,
    name,
    COUNT(DISTINCT order_id),
    SUM(quantity * unit_price),
    SUM(quantity * unit_price) / COUNT(DISTINCT order_id)
FROM customers
    NATURAL JOIN orders
    NATURAL JOIN order_items
WHERE status = 'Shipped'
GROUP BY customer_id;

8、查询所有客户的每个年度的商品采购总金额，按照以下格式显示和排序：

客户ID、客户名称、年度、采购金额（降序）。

SELECT DISTINCT customer_id,
    name,
    YEAR(order_date) AS year,
    SUM(quantity * unit_price) AS money
FROM customers
    NATURAL JOIN orders
    NATURAL JOIN order_items
WHERE status <> 'Canceled'
GROUP BY customer_id,
    year
ORDER BY money DESC;

9、查询没有库存的商品id和商品名称。

SELECT product_id,
    product_name
FROM products
WHERE product_id NOT IN (
        SELECT product_id
        FROM inventories
    );

10、查询没有任何订单的销售人员名单(id, first name, last name)，要求分别使用IN 、EXISTS谓词和外连接。

IN：

SELECT employee_id,
    first_name,
    last_name
FROM employees
WHERE employee_id NOT IN (
        SELECT salesman_id
        FROM orders
        WHERE salesman_id IS NOT NULL
    )
    AND job_title = 'Sales Representative';

EXISTS：

SELECT employee_id,
    first_name,
    last_name
FROM employees
WHERE NOT EXISTS (
        SELECT *
        FROM orders
        WHERE salesman_id = employee_id
    )
    AND job_title = 'Sales Representative';

外连接：

SELECT DISTINCT employee_id,
    first_name,
    last_name
FROM employees e
    LEFT OUTER JOIN orders o ON e.employee_id = o.salesman_id
WHERE o.salesman_id IS NULL
    AND job_title = 'Sales Representative';

11、统计每个仓库按产品类型（Category）的库存商品数量，显示仓库名称、产品类型和库存数量，并按照仓库名称和产品类型对结果进行排序。

SELECT warehouse_name,
    category_name,
    SUM(quantity)
FROM warehouses w
    NATURAL JOIN inventories
    NATURAL JOIN products
    NATURAL JOIN product_categories
GROUP BY warehouse_name,
    category_name
ORDER BY warehouse_name,
    category_name;

12、查询采购了至少与100号订单的产品相同的客户信息（ID与客户名称）。

SELECT customer_id,
    name
FROM customers
    NATURAL JOIN orders
    NATURAL JOIN order_items
    NATURAL JOIN products
WHERE product_name =(
        SELECT DISTINCT product_name
        FROM order_items
            NATURAL JOIN products
        WHERE order_id = 100
    )
    AND status = 'Shipped';

13、查询管理员工数量最多的经理信息。

SELECT *
FROM employees
WHERE employee_id =(
        SELECT manager_id AS cnt
        FROM employees
        GROUP BY manager_id
        ORDER BY count(*) DESC
        LIMIT 1
    );

14、使用SQL将COUNTRIES的数据复制到COUNTRY_A，然后将region=4的国家从COUNTRY_A中删除；使用SQL将COUNTRIES的数据复制到COUNTRY_B，然后将region=2的国家从COUNTRY_B中删除。

根据下图，基于上述修改后的COUNTRY数据，假设A=COUNTRY_A，B=COUNTRY_B，给出对应的SQL查询结果，并解释结果意义。

先对表格countries进行复制，并删除country_a中region=4的国家和country_b中region=2的国家：

CREATE TABLE country_a (
    SELECT *
    FROM countries
);
DELETE FROM country_a
WHERE region_id = 4;
CREATE TABLE country_b (
    SELECT *
    FROM countries
);
DELETE FROM country_b
WHERE region_id = 2;

LEFT JOIN

SELECT *
FROM country_a A
    LEFT JOIN country_b B ON A.country_id = B.country_id;

LEFT JOIN表示选择所有包含在A中的数据。

RIGHT JOIN

SELECT *
FROM country_a A
    RIGHT JOIN country_b B ON A.country_id = B.country_id;

RIGHT JOIN表示选择所有包含在B中的数据。

LEFT JOIN EXCLUDING INNER JOIN

SELECT *
FROM country_a A
    LEFT JOIN country_b B ON A.country_id = B.country_id
WHERE B.country_id IS NULL;

LEFT JOIN EXCLUDING INNER JOIN表示选择所有包含在A中但不包含在B中的数据。

FULL OUTER JOIN

SELECT *
FROM country_a A
    LEFT JOIN country_b B ON A.country_id = B.country_id
UNION
SELECT *
FROM country_a A
    RIGHT JOIN country_b B ON A.country_id = B.country_id;

FULL OUTER JOIN表示选择所有既包含在A中又包含在B中的数据。

RIGHT JOIN EXCLUDING INNER JOIN

SELECT *
FROM country_a A
    RIGHT JOIN country_b B ON A.country_id = B.country_id
WHERE A.country_id IS NULL;

RIGHT JOIN EXCLUDING INNER JOIN表示选择所有包含在B中但不包含在A中的数据。

INNER JOIN

SELECT *
FROM country_a A
    INNER JOIN country_b B ON A.country_id = B.country_id;

INNER JOIN表示选择所有既包含在A中又包含在B中的数据。

FULL OUTER JOIN EXCLUDING INNER JOIN

SELECT *
FROM country_a A
    LEFT JOIN country_b B ON A.country_id = B.country_id
WHERE B.country_id IS NULL
UNION
SELECT *
FROM country_a A
    RIGHT JOIN country_b B ON A.country_id = B.country_id
WHERE A.country_id IS NULL;

FULL OUTER JOIN EXCLUDING INNER JOIN表示选择所有要么包含在A中，要么包含在B中，但A和B不能同时包含的数据。

15、将orders表复制一份结构和数据到order_trg，在新建的order_trg表上创建一个after update触发器，当把STATUS为“Pending”值的记录修改为“Shipped”时，要求在触发器中将每一条订单记录的修改结果记录到一个日志表update_log，该表需要保存订单ID、修改前的STATUS值、修改后的STATUS值、当前的用户名和修改时间（精确到秒）。

首先先将order表复制：

CREATE TABLE order_trg (
    SELECT *
    FROM orders
);

然后创建一个update_log表，用于存放日志：

CREATE TABLE update_log(
    order_id int,
    status_pre VARCHAR(20),
    status_new VARCHAR(20),
    current_username VARCHAR(255),
    update_time DATETIME
);

接下来创建触发器order_trigger：

DELIMITER $$ 
CREATE TRIGGER order_trigger
AFTER
UPDATE ON order_trg FOR EACH ROW 
BEGIN 
	IF OLD.status = 'Pending'
    AND NEW.status = 'Shipped' THEN
	INSERT INTO update_log
	VALUES (
        NEW.order_id,
        OLD.status,
        NEW.status,
        CURRENT_USER(),
        CURRENT_TIME()
    );
	END IF;
END;
$$ 
DELIMITER ;

接下来以order_trg中第一条状态为“Pending”的数据为例（其order_id=1），将其状态修改为“Shipped”：

UPDATE order_trg
SET status = 'Shipped'
WHERE order_id = 1;

查询此时的update_log表，发现生成了一条日志。

posted on 2024-01-20 23:52 CyberFisher 阅读(165) 评论(0) 收藏举报

SQL高级查询

SQL高级查询

一、 实验目的

二、 实验内容

一、实验目的

二、实验内容