数据库系统概论第三章部分习题

/**
 * @file 1.sql
 * @author (None)
 * @brief
 * @version 0.1
 * @date 2022-04-10
 *
 * @copyright Copyright (c) 2022
 *
 */

-- UTF-8

/*
第三章第3题
有两个关系 S(A,B,C,D) 和 T(C,D,E,F) ,写出与下列查询等价的SQL表达式
*/

-- (1) σ(A=10) (S)
SELECT *
FROM S
WHERE A = 10


-- (2) π(A,B) (S)
SELECT DISTINCT A, B
FROM S


-- (3) S ∞ T
SELECT A, B, S.C, S.D, E, F
FROM S, T
WHERE S.C = T.C
    AND S.D = T.D


-- (4) S ∞(S.C=T.C) T
SELECT A, B, S.C, S.D, T.C, T.D, E, F
FROM S, T
WHERE S.C = T.C


-- (5) S ∞(A<E) T
SELECT A, B, S.C, S.D, T.C, T.D, E, F
FROM S, T
WHERE A < E


-- (6) π(C,D) (S) × T
SELECT Z.C, Z.D, T.C, T.D, E, F
FROM T,(SELECT C, D FROM S) Z



/*
第三章第4题
用SQL语句建立第2章习题6中的4个表;
针对建立的4个表用SQL完成第2章习题6中的查询
*/

CREATE DATABASE SPJ;

-- S(SNO,SNAME,STATUS,CITY);
-- P(PNO,PNAME,COLOR,WEIGHT);
-- J(JNO,JNAME,CITY);
-- SPJ(SNO,PNO,JNO,QTY);

USE SPJ;

-- 定义并插入S表
DROP TABLE IF EXISTS S;
CREATE TABLE S (
    SNO CHAR(2) PRIMARY KEY,
    SNAME NCHAR(3),
    STATUS INT,
    CITY NCHAR(2)
);
INSERT INTO S
VALUES ('S1', '精益', 20, '天津'),
    ('S2', '盛锡', 10, '北京'),
    ('S3', '东方红', 30, '北京'),
    ('S4', '丰泰盛', 20, '天津'),
    ('S5', '为民', 30, '上海');


-- 定义并插入P表
DROP TABLE IF EXISTS P;
CREATE TABLE P (
    PNO CHAR(2),
    PNAME NCHAR(3),
    COLOR NCHAR(1),
    WEIGHT INT
);
INSERT INTO P
VALUES ('P1', '螺母', '红', 12),
    ('P2', '螺栓', '绿', 17),
    ('P3', '螺丝刀', '蓝', 14),
    ('P4', '螺丝刀', '红', 14),
    ('P5', '凸轮', '蓝', 40),
    ('P6', '齿轮', '红', 30);


-- 定义并插入J表
DROP TABLE IF EXISTS J;
CREATE TABLE J (
    JNO CHAR(2),
    JNAME NVARCHAR(4),
    CITY NCHAR(2)
);
INSERT INTO J
VALUES ('J1', '三建', '北京'),
    ('J2', '一汽', '长春'),
    ('J3', '弹簧厂', '天津'),
    ('J4', '造船厂', '天津'),
    ('J5', '机车厂', '唐山'),
    ('J6', '无线电厂', '常州'),
    ('J7', '半导体厂', '南京');


-- 定义并插入SPJ表
DROP TABLE IF EXISTS SPJ;
CREATE TABLE SPJ (
    SNO CHAR(2),
    PNO CHAR(2),
    JNO CHAR(2),
    QTY INT
);
INSERT INTO SPJ
VALUES ('S1', 'P1', 'J1', '200'),
    ('S1', 'P1', 'J3', '100'),
    ('S1', 'P1', 'J4', '700'),
    ('S1', 'P2', 'J2', '100'),
    ('S2', 'P3', 'J1', '400'),
    ('S2', 'P3', 'J2', '200'),
    ('S2', 'P3', 'J4', '500'),
    ('S2', 'P3', 'J5', '400'),
    ('S2', 'P5', 'J1', '400'),
    ('S2', 'P5', 'J2', '100'),
    ('S3', 'P1', 'J1', '200'),
    ('S3', 'P3', 'J1', '200'),
    ('S4', 'P5', 'J1', '100'),
    ('S4', 'P6', 'J3', '300'),
    ('S4', 'P6', 'J4', '200'),
    ('S5', 'P2', 'J4', '100'),
    ('S5', 'P3', 'J1', '200'),
    ('S5', 'P6', 'J2', '200'),
    ('S5', 'P6', 'J4', '500');


-- (1) 求供应工程J1零件的供应商号码SNO
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO = 'J1';


-- (2) 求供应工程J1零件P1的供应商号码SNO
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO = 'J1'
    AND PNO = 'P1';


-- (3) 求供应工程J1零件为红色的供应商号码SNO
SELECT DISTINCT SNO
FROM SPJ,P
WHERE JNO = 'J1'
    AND SPJ.PNO = P.PNO
    AND COLOR = '红';


-- (4) 求没有使用天津供应商生产的红色零件的工程号JNO
SELECT DISTINCT JNO
FROM J
WHERE NOT EXISTS(
        SELECT *
        FROM SPJ
        WHERE SPJ.JNO = J.JNO
            AND SNO IN (
                SELECT SNO
                FROM S
                WHERE CITY = '天津'
            )
            AND PNO IN (
                SELECT PNO
                FROM P
                WHERE COLOR = '红'
            )
    );


-- (5) 求至少用了供应商S1所供应的全部零件的工程号JNO
-- 题目的相反意思:求供应商S1所供应的零件的没有工程使用的工程号
SELECT DISTINCT JNO
FROM SPJ A
WHERE NOT EXISTS(           -- 如果以下情况不存在
        SELECT *
        FROM SPJ B
        WHERE SNO = 'S1'                -- 查找S1供应的全部零件
            AND NOT EXISTS( -- 其他工程都没有选用S1供应的零件
                SELECT *
                FROM SPJ C
                WHERE C.PNO = B.PNO     -- 找出其他工程使用过的S1供应的零件
                    AND C.JNO = A.JNO   -- 返回工程的编号，用A接收
            )
    );



/*
第三章第五题
针对习题4中的4个表试用SQL完成以下操作:
*/

-- (1) 找出所有供应商的姓名和所在城市
SELECT SNAME 供应商姓名, CITY 所在城市
FROM S;


-- (2) 找出所有零件的名称、颜色、重量
SELECT PNAME 零件名称, COLOR 颜色, WEIGHT 重量
FROM P;


-- (3) 找出使用供应商S1所供应零件的工程号码
SELECT JNO 工程号码
FROM SPJ
WHERE SNO = 'S1';


-- (4) 找出工程项目J2使用的各种零件的名称及数量
SELECT PNAME 零件名称, QTY 零件数量
FROM SPJ, P
WHERE SPJ.PNO = P.PNO
    AND JNO = 'J2';


-- (5) 找出上海厂商供应的所有零件号码
SELECT DISTINCT P.PNO 零件号码
FROM P, S, SPJ
WHERE CITY = '上海'
    AND SPJ.SNO = S.SNO
    AND SPJ.PNO = P.PNO;


-- (6) 找出使用上海产的零件的工程名称
SELECT DISTINCT JNAME 工程名称
FROM SPJ, S, J
WHERE S.CITY = '上海'
    AND SPJ.SNO = S.SNO
    AND SPJ.JNO = J.JNO;


-- (7) 找出没有使用天津产的零件的工程号码
SELECT JNO 工程号码
FROM J
WHERE NOT EXISTS(
        SELECT *
        FROM SPJ
        WHERE SPJ.JNO = J.JNO
            AND SNO IN(
                SELECT SNO
                FROM S
                WHERE CITY = '天津'
            )
    );


-- (8) 把全部红色零件的颜色改成蓝色
UPDATE P
SET COLOR = '蓝'
WHERE COLOR = '红';
SELECT * FROM P;


-- (9) 由S5供给J4的零件P6改为由S3供应,请做必要的修改
UPDATE SPJ
SET SNO = 'S3'
WHERE SNO = 'S5'
    AND JNO = 'J4'
    AND PNO = 'P6';
SELECT * FROM SPJ;


-- (10) 从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录
DELETE
FROM SPJ
WHERE SNO = 'S2';
SELECT * FROM SPJ;

DELETE
FROM S
WHERE SNO = 'S2';
SELECT * FROM S;


-- (11) 请将(S2,J6,P4,200)插入供应情况关系
INSERT INTO SPJ
VALUES('S2','P6','J4',200);
SELECT * FROM SPJ;



/*
第三章第九题
请为三建工程项目建立一个供应情况的视图,
包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY).
针对该视图完成下列查询:
(1) 找出三建工程项目使用的各种零件代码及其数量.
(2) 找出供应商S1的供应情况.
*/

-- 创建视图
GO

CREATE VIEW VSPJ AS
SELECT SNO 供应商代码,
    PNO 零件代码,
    QTY 供应数量
FROM SPJ, J
WHERE JNAME = '三建'
    AND SPJ.JNO = J.JNO;

GO


-- (1) 找出三建工程项目使用的各种零件代码及其数量.
SELECT 零件代码, 供应数量
FROM VSPJ;


-- (2) 找出供应商S1的供应情况.
SELECT 零件代码, 供应数量
FROM VSPJ
WHERE 供应商代码 = 'S1';