《数据库系统概论》例程及习题（二）

简介

这次内容有书上第三章的几乎所有例程运行结果和三道习题的实验。为了纪念被老师惨无人道剥削的学习时光，再辛苦一点儿，全都放上来，说不定就能拯救日后同样痛苦的小老弟小老妹。

建表

CREATE TABLE Student
	(Sno	CHAR(9) PRIMARY KEY,
	Sname	CHAR(20) UNIQUE,
	Ssex	CHAR(2),
	Sage	SMALLINT,
	Sdept	CHAR(20)
	);

CREATE TABLE Course
	(Cno	CHAR(4) PRIMARY KEY,
	Cname	CHAR(40),
	Cpno	CHAR(4),
	Ccredit	SMALLINT,
	FOREIGN	KEY (Cpno) REFERENCES Course(Cno)
	);

CREATE TABLE SC
	(Sno	CHAR(9),
	Cno	    CHAR(4),
	Grade	SMALLINT,
	PRIMARY KEY (Sno,Cno),
	FOREIGN KEY (Sno) REFERENCES Student(Sno),
	FOREIGN KEY (Cno) REFERENCES Course(Cno)
	);

插入数据

insert into Student (Sno,Sname,Ssex,Sage,Sdept)
values  ('201215121','李勇','男',20,'CS'),
        ('201215122','刘晨','女',19,'CS'),
        ('201215123','王敏','女',18,'MA'),
        ('201215125','张立','男',19,'IS');

insert into Course (Cno,Cname,Cpno,Ccredit)
values  ('6','数据处理',null,2),
        ('2','数学',null,2),
        ('4','操作系统','6',3),
        ('7','PASCAL语言','6',4),
        ('5','数据结构','7',4),
        ('1','数据库','5',4),
        ('3','信息系统','1',4);
	
insert into SC (Sno,Cno,Grade)
values  ('201215121','1',92),
        ('201215121','2',85),
        ('201215121','3',88),
        ('201215122','2',90),
        ('201215122','3',80);

数据查询

单表查询(例3.16-3.48)

-- 16
SELECT Sno,Sname
FROM Student;

-- 17
SELECT Sname,Sno,Sdept
FROM Student;

-- 18
SELECT * FROM Student;

-- 19
SELECT Sname,2014-Sage
FROM Student;

-- 20
SELECT Sname,'Year of Birth:',2020-Sage,LOWER(Sdept)
FROM Student;

-- 21
SELECT DISTINCT Sno FROM SC;

-- 22
SELECT Sname
FROM Student
WHERE Sdept='CS';

-- 23
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;

-- 24
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;

-- 25
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;

-- 26
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;

-- 27
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS');

-- 28
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('IS','MA','CS');

-- 29
SELECT *
FROM Student
WHERE Sno LIKE '201215121';

-- 30
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';

-- 31
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__';

-- 32
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '__阳%';

-- 33
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';

-- 34
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\ ' ;

-- 35
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i__' ESCAPE '\ ' ;

-- 36
SELECT Sno, Cno
FROM SC
WHERE Grade IS NULL;

-- 37
SELECT Sno, Cno
FROM SC
WHERE Grade IS NOT NULL;

-- 38
SELECT Sname
FROM Student
WHERE Sage<20;

-- 39
SELECT Sno, Grade
FROM SC
WHERE Cno= '3'
ORDER BY Grade DESC;

-- 40
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;

-- 41
SELECT COUNT(*)
FROM Student;

-- 42
SELECT COUNT(DISTINCT Sno)
FROM SC;

-- 43
SELECT AVG(Grade)
FROM SC
WHERE Cno='1';

-- 44
SELECT MAX(Grade)
FROM SC
WHERE Cno='1';

-- 45
SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='201215012' AND SC.Cno=Course.Cno;

-- 46
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;

-- 47
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;

-- 48
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

连接查询(例3.49-3.54)

-- 49
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;

-- 50
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno;

-- 51
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND
SC.Cno='2' AND SC.Grade>90;

-- 52
SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

-- 53
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT JOIN SC ON
(Student.Sno=SC.Sno);

-- 54
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;

嵌套查询(例3.55-3.63)

-- 55
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
		(SELECT Sdept
		FROM Student
		WHERE Sname= '刘晨');

-- 56
SELECT Sno,Sname
FROM Student
WHERE Sno IN
		(SELECT Sno
		FROM SC
		WHERE Cno IN
				(SELECT Cno
				FROM Course
				WHERE Cname= '信息系统'));

-- 57
SELECT Sno, Cno
FROM SC x
WHERE Grade >= (SELECT AVG(Grade)
				FROM SC y
				WHERE y.Sno=x.Sno);

-- 58
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
				FROM Student
				WHERE Sdept= 'CS')
		AND Sdept <> 'CS' ;

-- 59
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL (SELECT Sage
				FROM Student
				WHERE Sdept= ' CS ')
		AND Sdept <> ' CS ';

-- 60
SELECT Sname
FROM Student
WHERE EXISTS
		(SELECT *
		FROM SC
		WHERE Sno=Student.Sno AND Cno= '1');

-- 61
SELECT Sname
FROM Student
WHERE NOT EXISTS
		(SELECT *
		FROM SC
		WHERE Sno=Student.Sno AND Cno= '1');

-- 62
SELECT Sname
FROM Student
WHERE NOT EXISTS
		(SELECT *
		FROM Course
		WHERE NOT EXISTS
				(SELECT *
				FROM SC
				WHERE Sno= Student.Sno
				AND Cno= Course.Cno));

-- 63
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
		(SELECT *
		FROM SC SCY
		WHERE SCY.Sno = '201215122' AND
				NOT EXISTS
				(SELECT *
				FROM SC SCZ
				WHERE SCZ.Sno=SCX.Sno AND
				SCZ.Cno=SCY.Cno));

集合查询(例3.64-3.68)

-- 64
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;

-- 65
SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno= '2';

-- 66
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage<=19;

-- 67
SELECT Sno
FROM SC
WHERE Cno='1' AND Sno IN
				(SELECT Sno
				FROM SC
				WHERE Cno= '2');

-- 68
SELECT * 
FROM Student
WHERE Sdept='CS' AND Sage>19;

数据更新

插入(例3.69-3.72)

-- 69
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('201215128','陈冬','男','IS',18);

-- 70
INSERT INTO Student
VALUES ('201215126','张成民','男',18,'CS');

-- 71
INSERT INTO SC(Sno,Cno)
VALUES ('201215128 ','1');

-- 72
CREATE TABLE Dept_age
	(Sdept	CHAR(15),
	Avg_age	SMALLINT
	);
INSERT INTO Dept_age(Sdept,Avg_age)
	SELECT Sdept,AVG(Sage)
	FROM Student
	GROUP BY Sdept;

修改(例3.73-3.75)

-- 73
UPDATE Student
SET Sage=22
WHERE Sno='201215121';

-- 74
UPDATE Student
SET Sage= Sage+1;

-- 75
UPDATE SC
SET Grade=0
WHERE Sno IN
		(SELECT Sno
		FROM Student
		WHERE Sdept= 'CS' );

删除(例3.76-3.78)

-- 76
DELETE FROM SC
WHERE Sno= '201215128';
DELETE FROM Student
WHERE Sno= '201215128';

-- 77
DELETE FROM SC;

-- 78
DELETE FROM SC
WHERE Sno IN
		(SELECT Sno
		FROM Student
		WHERE Sdept= 'CS');

空值的处理(例3.79-3.83)

-- 79
INSERT INTO SC(Sno,Cno)
VALUES ('201215126','1');

-- 80
UPDATE Student
SET Sdept = NULL
WHERE Sno='201215200';

-- 81
SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;

-- 82
SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno='1';

-- 83
SELECT Sno
FROM SC
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);

视图

定义视图(例3.84-3.91)

-- 84
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS';

-- 85
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION;

-- 86
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept= 'IS' AND
		Student.Sno=SC.Sno AND
		SC.Cno= '1';

-- 87
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;

-- 88
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2020-Sage
FROM Student;

-- 89
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

-- 90
CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
AS
SELECT *
FROM Student
WHERE Ssex='女';

-- 91
DROP VIEW BT_S;
DROP VIEW IS_S1 CASCADE;

查询视图(例3.92-3.94)

-- 92
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

-- 93
SELECT IS_Student.Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';

-- 94
SELECT *
FROM S_G
WHERE Gavg>=90;

更新视图(例3.95-3.97)

-- 95
UPDATE IS_Student
SET Sname= '刘辰'
WHERE Sno= '201215122';

-- 96
CREATE OR REPLACE VIEW IS_Student
AS
SELECT Sno,Sname,Sage,Sdept
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION;
INSERT INTO IS_Student
VALUES ('201215129','赵新',20,'IS');

-- 97
DELETE FROM IS_Student
WHERE Sno='201215129';

习题3.4

CREATE TABLE S
(SNO CHAR(3) PRIMARY KEY,
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10));

CREATE TABLE P
(PNO CHAR(3) PRIMARY KEY,
PNAME CHAR(10),
COLOR CHAR(4),
WEIGHT SMALLINT);
     
CREATE TABLE J
(JNO CHAR(3) PRIMARY KEY,
JNAME CHAR(10),
CITY CHAR(10));

CREATE TABLE SPJ
(SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY	SMALLINT,
FOREIGN KEY (SNO) REFERENCES S(Sno),
FOREIGN KEY (PNO) REFERENCES P(Pno),
FOREIGN KEY (JNO) REFERENCES J(Jno));

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

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

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

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)
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO='J1';

-- (2)
SELECT SNO
FROM SPJ
WHERE JNO='J1' AND PNO='P1';

-- (3)
SELECT SNO
FROM SPJ
WHERE JNO='J1' AND PNO IN
				(SELECT PNO
				FROM P
				WHERE COLOR='红');

-- (4)
SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT *
FROM SPJ,S,P
WHERE SPJ.JNO=J.JNO AND
		SPJ.SNO=S.SNO AND
		SPJ.PNO=P.PNO AND
		S.CITY='天津' AND
		P.COLOR='红');

-- (5)
SELECT JNO
FROM J JX
WHERE NOT EXISTS
/*如果不存在JX没用过的S1提供的任一零件，返回true*/
(SELECT *
FROM SPJ SPJX
WHERE SNO='S1' AND NOT EXISTS
			/*如果工程JX有没用过S1提供的零件，返回true*/
			(SELECT *
			FROM SPJ SPJY
			WHERE SPJY.PNO=SPJX.PNO AND SPJY.JNO=JX.JNO));

习题3.5

-- (1)
SELECT SNAME,CITY FROM S;

-- (2)
SELECT PNAME,COLOR,WEIGHT FROM P;

-- (3)
SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1';

-- (4)
SELECT P.PNAME,SPJ.QTY
FROM P,SPJ
WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2';

-- (5)
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO IN
		(SELECT SNO
		FROM S
		WHERE S.CITY='上海');

-- (6)
SELECT JNAME
FROM J,SPJ,S
WHERE J.JNO=SPJ.JNO AND
		SPJ.SNO=S.SNO AND
		S.CITY='上海';

-- (7)
SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT *
FROM SPJ,S
WHERE SPJ.JNO=J.JNO AND
		SPJ.SNO=S.SNO AND
		S.CITY='天津');

-- (8)
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红';

-- (9)
UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6';

-- (10)
DELETE FROM SPJ WHERE SNO='S2';
DELETE FROM S WHERE SNO='S2';

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

习题3.9

-- 创建视图
CREATE VIEW V_SPJ
AS
SELECT SPJ.SNO,SPJ.PNO,SPJ.QTY
FROM SPJ,J
WHERE SPJ.JNO=J.JNO AND J.JNAME='三建';

-- (1)
SELECT PNO,SUM(QTY)
FROM V_SPJ
GROUP BY PNO;

-- (2)
SELECT PNO,QTY
FROM V_SPJ
WHERE SNO='S1';