数据库作业十-第三章课后习题

1.有两个关系S(A,B,C,D)和T(C,D,E,F)，写出下列等价的SQL表达式
(1) ${\sigma }_{A=10}(S)$

在S中选择A=10的

SELECT *
FROM S
WHETE A=10

(2) ${\Pi }_{A,B}(S)$

求S上A,B两个属性上的投影

SELECT A,B
FROM S

(3) $S\bowtie 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\underset{S.C=T.C}{\bowtie }T$

等值连接

SELECT *
FROM S,T
WHERE S.C=T.C

(5)$S\underset{A<E}{\bowtie }T$

一般连接

SELECT *
FROM S,T
WHERE S.A<T.E

(6)${\Pi }_{C,D}(S)\times T$

投影出S中的C，D，再求与T的笛卡尔积,当然可以先做笛卡尔积，然后再投影

SELECT S.C,S.D,T.*
FROM S,T

2.用SQL语句建立2.6中的四个表，针对建立的表，完成查询

先建立表，并插入数据

CREATE TABLE S
(SNO VARCHAR(5) PRIMARY KEY,
SNAME VARCHAR(10) UNIQUE,
STATUS INT NOT NULL,
CITY VARCHAR(10) NOT NULL
)

CREATE TABLE P
(PNO VARCHAR(5) PRIMARY KEY,
PNAME VARCHAR(10) NOT NULL,
COLOR VARCHAR(5) NOT NULL,
WEIGHT INT NOT NULL
)

CREATE TABLE J
(JNO VARCHAR(5) PRIMARY KEY,
JNAME VARCHAR(10) UNIQUE,
CITY VARCHAR(10) NOT NULL
)

CREATE TABLE SPJ
(SNO VARCHAR(5) NOT NULL,
PNO VARCHAR(5) NOT NULL,
JNO VARCHAR(5) NOT NULL,
QTY INT NOT NULL
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO)
)

INSERT INTO S VALUES('S1','精益',20,'天津')
INSERT INTO S VALUES('S2','盛锡',10,'北京')
INSERT INTO S VALUES('S3','东方红',30,'北京')
INSERT INTO S VALUES('S4','丰泰盛',20,'天津')
INSERT INTO S VALUES('S5','为民',30,'上海')

INSERT INTO P VALUES('P1','螺母','红',12)
INSERT INTO P VALUES('P2','螺栓','绿',17)
INSERT INTO P VALUES('P3','螺丝刀','蓝',14)
INSERT INTO P VALUES('P4','螺丝刀','红',14)
INSERT INTO P VALUES('P5','凸轮','蓝',40)
INSERT INTO P VALUES('P6','齿轮','红',30)

INSERT INTO J VALUES('J1','三建','北京')
INSERT INTO J VALUES('J2','一汽','长春')
INSERT INTO J VALUES('J3','弹簧厂','天津')
INSERT INTO J VALUES('J4','造船厂','天津')
INSERT INTO J VALUES('J5','机车厂','唐山')
INSERT INTO J VALUES('J6','无线电厂','常州')
INSERT INTO J VALUES('J7','半导体厂','南京')

INSERT INTO SPJ VALUES('S1','P1','J1',200)
INSERT INTO SPJ VALUES('S1','P1','J3',100)
INSERT INTO SPJ VALUES('S1','P1','J4',700)
INSERT INTO SPJ VALUES('S1','P2','J2',100)
INSERT INTO SPJ VALUES('S2','P3','J1',400)
INSERT INTO SPJ VALUES('S2','P3','J2',200)
INSERT INTO SPJ VALUES('S2','P3','J4',500)
INSERT INTO SPJ VALUES('S2','P3','J5',400)
INSERT INTO SPJ VALUES('S2','P5','J1',400)
INSERT INTO SPJ VALUES('S2','P5','J2',100)
INSERT INTO SPJ VALUES('S3','P1','J1',200)
INSERT INTO SPJ VALUES('S3','P3','J1',200)
INSERT INTO SPJ VALUES('S4','P5','J1',100)
INSERT INTO SPJ VALUES('S4','P6','J3',300)
INSERT INTO SPJ VALUES('S4','P6','J4',200)
INSERT INTO SPJ VALUES('S5','P2','J4',100)
INSERT INTO SPJ VALUES('S5','P3','J1',200)
INSERT INTO SPJ VALUES('S5','P6','J2',200)
INSERT INTO SPJ VALUES('S5','P6','J4',500)

(1)求供应工程J1零件的供应商号码SNO

SELECT SNO
FROM SPJ
WHERE JNO='J1'

(2)求供应工程J1零件为红色的供应商号码SNO

SELECT SNO
FROM (SELECT PNO,JNO,SNO FROM SPJ)as SPJ,(SELECT PNO,COLOR FROM P)as P
WHERE SPJ.PNO=P.PNO AND P.COLOR=N'红' AND SPJ.JNO='J1'

(3)求供应工程J1零件为红色的供应商号码SNO。

SELECT SNO
FROM SPJ
INNER JOIN P ON SPJ.PNO=P.PNO
WHERE P.COLOR=N'红' AND SPJ.JNO='J1'

(4）求没有使用天津供应商生产的红色零件的工程号JNO。

SELECT DISTINCT JNO
FROM SPJ
WHERE JNO NOT IN
    (SELECT JNO
    FROM SPJ,S,P
    WHERE  S.SNO=SPJ.SNO 
    AND P.PNO=SPJ.PNO
    AND CITY=N'天津'  
    AND COLOR=N'红')

（5）求至少用了供应商S1所供应的全部零件的工程号JNO。

SELECT DISTINCT JNO
FROM SPJ SPJ1
WHERE NOT EXISTS
    (SELECT *
    FROM SPJ SPJ2
    WHERE SPJ2.SNO='S1'
          AND NOT EXISTS
          (SELECT *
          FROM SPJ SPJ3
          WHERE JNO = SPJ1.JNO
             AND PNO = SPJ2.PNO)
          )

这个题出的有问题。先放过，不在这浪费时间。

针对4题中的表做如下操作
（1）找出所有供应商的姓名和所在城市

SELECT SNAME,CITY
FROM S

（2）找出所有零件的名称，颜色，重量

SELECT PNAME,COLOR,WEIGHT
FROM P

（3）找出所有供应商S1所供应零件的工程号码JNO

SELECT JNO
FROM SPJ
WHERE SNO='S1'

（4）找出所有工程项目J2使用的各种零件的名称和数量

SELECT PNAME,QTY
FROM P,SPJ
WHERE JNO='J2' AND P.PNO=SPJ.PNO

（5）找出上海厂商供应的所有零件号码。

SELECT PNO
FROM S,SPJ
WHERE S.SNO=SPJ.SNO AND CITY=N'上海'

（6）找出使用上海产的零件的工程名称

SELECT JNAME
FROM J
WHERE JNO IN
      (SELECT JNO
       FROM S,SPJ
      WHERE S.SNO=SPJ.SNO AND CITY=N'上海')

（7）找出没有使用天津产的零件的工程号码

SELECT JNO
FROM SPJ
WHERE JNO NOT IN
	  (SELECT DISTINCT JNO
      FROM S,SPJ
      WHERE S.CITY=N'天津' AND S.SNO=SPJ.SNO)

（8）把全部红色零件的颜色改为蓝色

UPDATE P
SET COLOR=N'蓝'
WHERE COLOR=N'红'

（9）由S5供给J4的零件P6改为由S3供应。

UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6'

（10）从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。

DELETE
FROM SPJ
WHERE SNO='S2'

DELETE 
FROM S
WHERE SNO='S2'

（11）请将（S2,J6,P4,200）插入供应情况关系中。

INSERT INTO SPJ
VALUES('S2','J6','P4',200)

为三建工程项目建立一个供应情况的视图，包括SNO,PNO,QTY.

CREATE VIEW SAN_JIAN
AS 
  SELECT SNO,PNO,QTY
  FROM SPJ,J
  WHERE SPJ.JNO=J.JNO AND JNAME=N'三建'

（1）找出三建项目使用的各种零件代码PNO和其数量。

SELECT DISTINCT PNO,QTY
FROM SAN_JIAN;

(2)找出供应商S1的供应情况。

SELECT *
FROM SAN_JIAN
WHERE SNO='S1'