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

数据库原理作业（一）

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	/*不包括没选课的同学*/

2、查询选修了‘VB’课程并且年龄不等于20岁的男同学的学号，姓名。

select Sname ,Sno From student 
	where Sno in(select sno from course where cname = 'VB') 	/*先选出“选修VB”的Sno，再用这个Sno去筛选男同学*/
		AND Ssex = '男' AND Sage != 20

3、查询选修了‘C01’课程，并且年龄在18-20岁之间的计算机系的同学的学号，姓名。

SELECT Sno, Sname  FROM Student 
	where Sno in(SELECT Sno FROM Course where Cno = 'c01')
		AND Sage between 18 and 20	/*同上一题*/

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

SELECT Sno,Sname From Student
	where rtrim(Sname) like '[张王李]%'	/*trim是去除数据中的空格，这里rtrim代表去除右边的空格，意味着只要开头词是张王李之一，就满足条件。*/
	AND Sdept = '计算机系'

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

SELECT Sname 姓名 , COUNT(*) 选课门数, COUNT(Grade) 已考试门数
	from sc join student on sc.sno = student.sno
	GROUP BY student.Sname	/*分组，指的是名字相同的人就count。*/

6、查询选修了3门以上课程的同学的学号和选课门数。

SELECT Sno, count(*) 选课门数 FROM SC
	GROUP BY Sno
	HAVING COUNT(*) > 3 	/*语法having count，只有大于3的才会被记录*/

7、查询计算机系，没有选课同学的学号和姓名（要求用外连接实现）。

SELECT student.Sno,Sname 
	FROM student left join sc on student.sno = sc.sno /*student左接sc*/
	Where Sdept = '计算机系' AND sc.sno is NULL

8、查询与刘晨在同一系学习的学生的姓名和所在的系。（要求用自链接实现）

SELECT S2.Sname , S2.Sdept FROM Student S1 join Student S2
	on S1.Sdept = S2.Sdept
	WHERE S1.Sname = '刘晨'
	AND S2.Sname != '刘晨'

9、查询没有选'c01'课程的同学的学号和姓名。（要求用not in实现）

SELECT Student.Sno,Sname FROM Student
	join SC on Student.Sno = SC.Sno
	WHERE Cno not in ('c01')	/*没选c01的*/
	GROUP BY Student.Sno, Sname

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

SELECT TOP 3 Sno,Grade From SC
	join Course on Course.Cno = SC.Cno
	WHERE Cname = 'VB' ORDER BY Grade DESC  	/*order by desc降序排列*/

11、查询计算机系男同学的学号，性别和年龄，按年龄从大到小排序。

SELECT Sno,Ssex,Sage FROM Student
	WHERE Ssex = '男' and Sdept = '计算机系'
	ORDER BY Sage DESC		/*同上*/

12、把‘vb’课程不及格的同学分数改为60分。（用多表连接实现）

UPDATE SC SET Grade = 60	/*注意这里是UPDATE更新*/
	WHERE Cno in (SELECT Cno FROM Course WHERE Cname = 'VB')	/*先选出vb的cno，再用这个cno去查sc表。、*/
	and Grade < 60

13、删除‘vb’课程的选课记录。（用多表连接实现）

DELETE FROM SC 		/*DELETE删除数据*/
	WHERE Cno in (SELECT Cno FROM Course WHERE Cname = 'vb')	/*先选出vb的cno，再用这个cno去查sc表*/

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

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

15、统计指定学号同学的不及格课程的门数，统计结果用输出参数返回。

CREATE PROCEDURE Failed
@dept VARCHAR(20), @rs INT OUTPUT	/*设定变量dept（指定的学号），rs（输出值）*/
AS 
	SELECT @rs = COUNT(Grade) From SC
	WHERE Sno = @dept
	AND Grade < 60

16、用后触发型触发器实现。不能将不及格的同学的成绩改为及格。违反约束，给出提示“不能将不及格成绩改为及格”。

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

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

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

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

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
)