[TUST]数据库原理作业（二）

数据库原理作业（二）

1、建表。包含知识点：，主键，外键，非空，Check约束，缺省值。

题目：创建Student表（使用CHECK约束、缺省值姓名“李明”），Course表，SC表。

Student表：
CREATE TABLE Student(
		Sno char(7) PRIMARY KEY,
		Sname char(10) NOT NULL,
		Ssex char(2)  DEFAULT '男' ,	/*缺省值，即默认值，这里默认男。*/
		Sage tinyint,
		Sdept varchar(20)
		CHECK(Ssex IN ('男','女')	/*CHECK约束，性别只能为男或者女。*/
	)

		

Course表：
	CREATE TABLE Course (
		Cno char(6) NOT NULL,
		Cname varchar(20) NOT NULL,
		Credit tinyint,
		Semester tinyint,
		PRIMARY KEY(Cno)
	)
SC表：
	CREATE TABLE SC(
		Sno char(7) NOT NULL,
		Cno char(6) NOT NULL,
		Grade tinyint,
		PRIMARY KEY (Sno,Cno),
		FOREIGN KEY (Sno) REFERENCES Student(Sno),
		FOREIGN KEY (Cno) REFERENCES Course(Cno)
	)

2 多表链接。

题目：1、查询每个同学的学号，姓名，课程号，课程名，分数。（不包括没选课的同学）（利用多表链接）

select Sname ,Student.Sno,Course.Cno,Cname,Grade From student 
	join sc on student.Sno = sc.Sno
	join course on course.Cno = sc. Cno	/*简单多表链接*/
	where Grade is not null

3 分组查询。

题目：查询计算机系每个同学所选的课程门数和已经考试的课程门数。

SELECT Sname 姓名 , COUNT(*) 选课门数, COUNT(Grade) 已考试门数
	from sc join student on sc.sno = student.sno
	GROUP BY student.Sname	/*GROUP BY 分组*/

4、通配符 % _ []，escape

题目：

查询计算机系，所有名字中含有‘张’‘王’‘李’的同学的学号和姓名。

SELECT Sno,Sname From Student
	where rtrim(Sname) like '_[张王李]%' /*注意是名字中含有。*/
	AND Sdept = '计算机系'

查找field1字段中包涵下划线‘_’的记录：

SELECT Sname From NEW
	where field1  like '%!_%' ESCAPE '!'/用escape来确认，当想要查找的东西是通配符时。/

5、排序，TOP

题目：查询VB课程考试成绩前3名的同学学号和VB成绩。（不包含并列）

SELECT TOP 3 Sno,Grade From SC
	join Course on Course.Cno = SC.Cno
	WHERE Cname = 'VB' ORDER BY Grade DESC	/*TOP是前几个的意思。*/

6 减法，交集

减法（差运算）题目：查询被李勇选了但是没有被刘晨选择的课程，列出课程名和学分

SELECT Cname，Credit
	From Student S JOIN SC ON S.Sno = SC.Sno
	JOIN Course C on C.Cno = SC.Cno		/*EXCEPT 减法，在表1里减去表2的东西*/
	WHERE Sname = '李勇'
EXCEPT
SELECT Cname，Credit
	From Student S JOIN SC ON S.Sno = SC.Sno
	JOIN Course C on C.Cno = SC.Cno
	WHERE Sname = '刘晨'

交集题目：查询李勇和刘晨所选择的相同的课程（即查询同时被李勇和刘晨选择的课程），列出课程名和学分。

SELECT Cname，Credit
	From Student S JOIN SC ON S.Sno = SC.Sno
	JOIN Course C on C.Cno = SC.Cno
	WHERE Sname = '李勇'
INTERSECT					/*INTERSECT是取交集*/
SELECT Cname，Credit
	From Student S JOIN SC ON S.Sno = SC.Sno
	JOIN Course C on C.Cno = SC.Cno
	WHERE Sname = '刘晨'

7、将查询结果保存到新表

题目：将计算机系的学生信息保存到永久表ComputerStudent中

SELECT *
	INTO ComputerStudent FROM Student		/*这里用到INTO。*/
	WHERE Sdept = '计算机系'			

8、查询结果的并运算

题目：查询计算机系和信息系学生的学号、姓名、年龄和所在系

SELECT Sno, Sname, Sage, Sdept FROM Student
	WHERE Sdept = '计算机系'
UNION 						/*并运算，相当于两次查找结果join*/
SELECT Sno, Sname, Sage, Sdept FROM Student
	WHERE Sdept = '信息系'

9 插入，删除，更新数据

插入题目：将新生记录（9521104、陈冬、男、18岁、信息系）插入到Student表中。

INSERT INTO Student VALUES('9521104','陈冬','男',18,'信息系'）			/*注意约束要求*/

修改题目：把‘Java’课程不及格的同学分数改为60分。

UPDATE SC SET Grade = 60
	WHERE Cno in (SELECT Cno FROM Course WHERE Cname = 'Java')	/*先选出课程名为java的cno，再用这个cno去SC表里找*/
	and Grade < 60

删除题目：删除‘Java’课程的选课记录。

DELETE FROM SC 
	WHERE Cno in (SELECT Cno FROM Course WHERE Cname = 'Java')	/*先选出课程名为java的cno，再用这个cno去SC表里找*/

10、创建视图

题目：创建视图，查询每个学生的选课门数，平均成绩。

CREATE view IS_STUDENT 
AS
	SELECT Sno,COUNT(Sno) 选课门数, AVG(Grade)平均成绩		/*以这个查询结果创建视图！！！！！*/
	FROM SC 
	GROUP BY Sno

11、存储过程，包含输入输出参数，默认值

输入参数题目：查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名与考试成绩。

CREATE PROCEDURE p_grade2
	@dept char(20) = '信息系'		/*定义新的变量，默认值为信息系。*/
AS
	SELECT Sname,Sdept,Cname,Grade
		From Student s INNER JOIN SC
		ON s.sno = sc.sno INNER JOIN Course c
		On c.cno = sc.cno
		WHERE Sdept = @dept

输出参数题目：计算两个数的乘积，将计算结果用输出参数返回给调用者。

CREATE PROCEDURE p_SUM
	@var1 int, @var2 int, @var3, int output
AS 
	Set @var3 = @var1 * @var2		/*简单赋值语句。*/

12、 触发器，包含inserted,deleted表

题目：对SC表中，限制不能将不及格学生的成绩改为及格，如果违反约束，则给出信息：“不能将不及格成绩改为及格。”

CREATE TRIGGER Tri_Grade
	ON SC AFTER UPDATE		/*后触发型trigger*/
AS
IF EXISTS(
	SELECT *FROM inserted a join deleted b
	ON a.Sno =b.Sno and a.Cno=b.Cno
	WHERE
	b.Grade<60 AND a.Grade>=60		/*如果更改前和更改后，有小于60分的成绩被改成大于60分*/
)
BEGIN
	ROLLBACK
	PRINT '不能将不及格成绩改为及格'
END

13、标量函数

题目：用标量函数实现。查询指定学号同学的不及格课程的门数。

CREATE FUNCTION dbo.F_count(@Sno CHAR(7))	/*设定函数，变量为sno。*/
RETURNS INT AS
BEGIN
	return (
		SELECT COUNT(*) FROM SC 	/*查询这个学号的内容。*/
		WHERE Sno=@Sno AND Grade<60
	)
END

14、内联表值函数

题目：用内联表值函数实现。查询指定学号同学的不及格课程的门数。

CREATE FUNCTION dbo.F_snoavg(@dept CHAR(20))
RETURNS TABLE AS				/*内联表*/

RETURN(
	SELECT Student.Sno,COUNT(*) 不及格门数
	FROM
	Student join SC on Student.Sno=Sc.Sno/	*多表链接*/
	WHERE
	Student.Sno=@dept AND Grade<60
	GROUP BY student.sno
)

15、多语句表值函数

题目：定义查询指定系的学生姓名、性别和年龄类型的多语句表值函数，其中年龄类型列的值为：如果该学生的年龄超过该系学生的平均年龄2岁，则为“偏大年龄”；如果该学生的年龄在平均年龄的-1到+2范围内，则为“正常年龄”；如果该学生的年龄小于平均年龄-1，则为“偏小年龄”。

CREATE FUNCTION f_SType(@dept carchar(20))
	RETURNS @retSType table(		/*新建变量，类型为表。*/
		Sname char(10),		/*新表中含有这些东西！*/
		Ssex char(2),
		SType char(8)		/*年龄类型type*/
		)
AS
BEGIN
	DECLARE @AvgAge int		/*新建整形变量“平均年龄”*/
	SET @AvgAge = (SELECT AVG(Sage) FROM Student WHERE Sdept = @dept)
	INSERT INTO @retSType		/*写入新表中*/
		SELECT Sname, Ssex,
			CASE		/*选择结构*/
				WHEN Sage > @AvgAge+2 THEN '偏大年龄'
				WHEN Sage BETWEEN @AvgAge-1 AND @AvgAge+2 THEN '正常年龄'
				ELSE '偏小年龄'
			END
		FROM Student WHERE Sdept = @dept
	RETURN
END