数据库查询和视图练习实操

原创于 2025-09-21 14:45:49 发布 · 688 阅读

29 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Oracle 11g笔记专栏收录该内容

8 篇文章

订阅专栏

1、Views 表：

请查询出所有浏览过自己文章的作者。

结果按照作者的 id 升序排列。

查询结果的格式如下所示：

示例 1：

输入：
Views 表：
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date |
+------------+-----------+-----------+------------+
| 1 | 3 | 5 | 2019-08-01 |
| 1 | 3 | 6 | 2019-08-02 |
| 2 | 7 | 7 | 2019-08-01 |
| 2 | 7 | 6 | 2019-08-02 |
| 4 | 7 | 1 | 2019-07-22 |
| 3 | 4 | 4 | 2019-07-21 |
| 3 | 4 | 4 | 2019-07-21 |
+------------+-----------+-----------+------------+

输出：
+------+
| id |
+------+
| 4 |
| 7 |
+------+

作业答案代码：

CREATE TABLE Views (
    article_id INT,
    author_id INT,
    viewer_id INT,
    view_date DATE
);
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (1, 3, 5, TO_DATE('2019-08-01', 'YYYY-MM-DD'));
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (1, 3, 6, TO_DATE('2019-08-02', 'YYYY-MM-DD'));
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (2, 7, 7, TO_DATE('2019-08-01', 'YYYY-MM-DD'));
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (2, 7, 6, TO_DATE('2019-08-02', 'YYYY-MM-DD'));
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (4, 7, 1, TO_DATE('2019-07-22', 'YYYY-MM-DD'));
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (3, 4, 4, TO_DATE('2019-07-21', 'YYYY-MM-DD'));
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (3, 4, 4, TO_DATE('2019-07-21', 'YYYY-MM-DD'));
SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;

作业代码答案：

-- 创建 Tweets 表
CREATE TABLE Tweets (
    tweet_id INT PRIMARY KEY,
    content VARCHAR2(100)
);
-- 插入示例数据
INSERT INTO Tweets (tweet_id, content) VALUES (1, 'Vote for Biden');
INSERT INTO Tweets (tweet_id, content) VALUES (2, 'Let us make America great again!');
-- 执行查询语句
SELECT tweet_id
FROM Tweets
WHERE LENGTH(content) > 15;

运行结果：

3、表：Visits

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id 是该表中具有唯一值的列。
该表包含有关光临过购物中心的顾客的信息。

表：Transactions

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
+----------------+---------+
transaction_id 是该表中具有唯一值的列。
此表包含 visit_id 期间进行的交易的信息。

有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。

返回以任何顺序排序的结果表。

返回结果格式如下例所示。

示例 1：

输入:
Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
| 1        | 23          |
| 2        | 9           |
| 4        | 30          |
| 5        | 54          |
| 6        | 96          |
| 7        | 54          |
| 8        | 54          |
+----------+-------------+
Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
| 2              | 5        | 310    |
| 3              | 5        | 300    |
| 9              | 5        | 200    |
| 12             | 1        | 910    |
| 13             | 2        | 970    |
+----------------+----------+--------+
输出:
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54          | 2              |
| 30          | 1              |
| 96          | 1              |
+-------------+----------------+
解释:
ID = 23 的顾客曾经逛过一次购物中心，并在 ID = 12 的访问期间进行了一笔交易。
ID = 9 的顾客曾经逛过一次购物中心，并在 ID = 13 的访问期间进行了一笔交易。
ID = 30 的顾客曾经去过购物中心，并且没有进行任何交易。
ID = 54 的顾客三度造访了购物中心。在 2 次访问中，他们没有进行任何交易，在 1 次访问中，他们进行了 3 次交易。
ID = 96 的顾客曾经去过购物中心，并且没有进行任何交易。
如我们所见，ID 为 30 和 96 的顾客一次没有进行任何交易就去了购物中心。顾客 54 也两次访问了购物中心并且没有进行任何交易。

作业代码：

-- 创建 Visits 表
CREATE TABLE Visits (
    visit_id INT PRIMARY KEY,
    customer_id INT
);

-- 创建 Transactions 表
CREATE TABLE Transactions (
    transaction_id INT PRIMARY KEY,
    visit_id INT,
    amount INT
);
-- 插入 Visits 表数据
INSERT INTO Visits (visit_id, customer_id) VALUES (1, 23);
INSERT INTO Visits (visit_id, customer_id) VALUES (2, 9);
INSERT INTO Visits (visit_id, customer_id) VALUES (4, 30);
INSERT INTO Visits (visit_id, customer_id) VALUES (5, 54);
INSERT INTO Visits (visit_id, customer_id) VALUES (6, 96);
INSERT INTO Visits (visit_id, customer_id) VALUES (7, 54);
INSERT INTO Visits (visit_id, customer_id) VALUES (8, 54);

-- 插入 Transactions 表数据
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (2, 5, 310);
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (3, 5, 300);
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (9, 5, 200);
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (12, 1, 910);
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (13, 2, 970);
SELECT 
    v.customer_id,
    COUNT(v.visit_id) AS count_no_trans
FROM 
    Visits v
LEFT JOIN 
    Transactions t 
ON 
    v.visit_id = t.visit_id
WHERE 
    t.visit_id IS NULL
GROUP BY 
    v.customer_id;

运行结果：