仲恺数据库原理 chp3课后答案
- 格式：docx
- 大小：12.70 KB
- 文档页数：5
3.5
CREATE TABLE S
(Sno char(2) PRIMARY KEY,
SNAME varchar(8) NOT NULL, STATUS smallint,
SCITY varchar(8))
CREATE TABLE P
(PNO char(2) PRIMARY KEY,
PNAME varchar(8) NOT NULL,
COLOR varchar(8),
WEIGHT smallint)
CREATE TABLE J
(JNO char(2) PRIMARY KEY,
JNAME varchar(8) NOT NULL,
JCITY varchar(8) NOT NULL)
CREATE TABLE SPJ
(SNO char(2) NOT NULL,
PNO char(2) NOT NULL,
JNO char(2) NOT NULL,
QTY smallint,
PRIMARY KEY(SNO,PNO,JNO), FOREIGN KEY(SNO) REFERENCES S(SNO), FOREIGN KEY(PNO) REFERENCES P(PNO), FOREIGN KEY(JNO) REFERENCES J(JNO))
3.6
CREATE TABLE S
(Sno char(2) PRIMARY KEY,
SNAME varchar(8) NOT NULL, STATUS smallint,
SCITY varchar(8))
CREATE TABLE P
(PNO char(2) PRIMARY KEY,
PNAME varchar(8) NOT NULL,
COLOR varchar(8),
WEIGHT smallint)
CREATE TABLE J
(JNO char(2) PRIMARY KEY,
JNAME varchar(8) NOT NULL,
JCITY varchar(8) NOT NULL)
CREATE TABLE SPJ
(SNO char(2) NOT NULL,
PNO char(2) NOT NULL,
JNO char(2) NOT NULL,
QTY smallint,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO))
SELECT SNAME,STATUS
FROM S
WHERE SCITY='天津'
SELECT PNAME,COLOR,WEIGHT
FROM P;
SELECT JNO
FROM SPJ
WHERE SNO='S1';
SELECT PNAME,QTY
FROM P,SPJ
WHERE JNO='J2';
SELECT PNO
FROM S,SPJ
WHERE SCITY='上海' AND S.SNO='SPJ.SNO';
SELECT JNAME
FROM SPJ,S,J
WHERE SCITY='上海'AND S.SNO=SPJ.SNO AND SPJ.JNO=J.JNO
SELECT JNO
FROM S,SPJ
WHERE SCITY!='天津' AND S.SNO='SPJ.SNO'
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红'
UPDATE SPJ
SET SNO='S3'
WHERE SPJ.SNO='S5' AND SPJ.JNO='J4' AND SPJ.PNO='P6'
DELETE
FROM S
WHERE S.SNO='S2'
DELETE
FROM SPJ
WHERE SPJ.SNO='S2'
INSERT
INTO SPJ (SNO,PNO,JNO,QTY)
VALUES('S2','P4','J6','500')
3.7
CREATE TABLE S
(S# char(8) PRIMARY KEY,
SNAME varchar(8) NOT NULL,
AGE smallint,
SEX char(2) NOT NULL DEFAULT('女'))
CREATE TABLE C
(C# char(4) PRIMARY KEY,
CNAME varchar(20),
TEACHAR varchar(20))
CREATE TABLE SC
(S# char(8),
C# char(4),
GRADE smallint,
PRIMARY KEY(S#,C#),
FOREIGN KEY(S# ) REFERENCES S(S# ),
FOREIGN KEY(C# ) REFERENCES C(C# ),)
SELECT CNAME ,C#
FROM C
WHERE TEACHAR ='刘某'
SELECT S#,SNAME
FROM S
WHERE AGE>'23' AND SEX='男'
SELECT C#,CNAME,TEACHAR
FROM C,S
WHERE S#='S3'
SELECT C#,CNAME
FROM S,C
WHERE SNAME='张小飞'NOT EXISTS
SELECT S#,SNAME
FROM S
WHERE C#>=3
SELECT C#,CNAME
FROM C
WHERE C# IN
(SELECT DISTINCT C#
GROUP BY C#
HAVING COUNT(*)=SELECT COUNT(*) FROM S)
DELETE
FROM SC
WHERE GRADE=NULL
UPDATE SC
SET GRADE='60'
WHERE C# IN
(SELECT C#
FROM C
WHERE CNAME='高等数学')
UPDATE SC
SET GRADE=GRADE*1.05
WHERE S# IN
(SELECT S#
FROM S
WHERE GRADE<AVG(GRADE) AND SEX='女')
INSERT
INTO C
VALUES ('C8','VC++','王昆')。
