sqlQuery-对答案

sterne 发布于

实验1

实验2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
--2.列出年龄超过平均值的所有学生名单，按年龄降序显示
select *
from student
where sage>=(select avg(sage) from student)
order by sage desc
-- 注意这个嵌套查询

--3.显示在1985年以后出生的学生的基本信息；
select *
from student
where( year(getdate())-Sage)>1985
-- 使用year(getdate())-Sage（我直接写的2021，会不泛用）

--10.按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
SELECT Sno AS '学号',Sname AS '姓名',Ssex AS '性别',year(getdate()-Sage) AS '出生年份',Sdept AS '院系'
FROM Student
ORDER BY '出生年份' ASC;
-- 下面order by可以直接使用改名之后的

--16.列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
我的答案属于是直接不对
SELECT Sno,AVG(Grade) AS '平均成绩'
FROM SC
WHERE Grade<60 -- 会导致avg算出的是低于60分课程的平均分
GROUP BY Sno
HAVING COUNT(*)>=2;

再写一遍：
SELECT Sno, AVG(grade) AS '平均成绩'
FROM SC X
WHERE EXIST(
	SELECT * FROM SC Y
	WHERE X.Sno=Y.Sno AND grade<60
	GROUP BY Sno
    HAVING COUNT(*)>=2
	)-- 或者这里用sno in什么什么，EXIST习惯了也得记得相关子查询的关联
GROUP BY Sno

标答另一种写法：
select sno,avg(grade) 平均成绩
from sc
group by sno
having count(case when grade <60 then 1 end)>=2

--19. 查询各个专业男生、女生的人数
SELECT Sdept,Ssex,COUNT(*) AS '人数'
FROM Student
GROUP BY Ssex,Sdept;-- 注意是专业-性别的聚合，先聚合性别后专业

--20. 查询各种姓氏的人数（假设没有复姓）；
select left(Sname,1)姓氏,count(*)人数
from student
group by left(Sname,1)

--21查询选修课程数最多的学号及选修课程数最少的学号
select sno, count (*) 选课门数
from sc
group by sno
having  count(*)>=all(select count(*) from sc group by sno) or count(*)<=all(select count(*) from sc group by sno)

--5．求使用零件数量在200-400之间的工程号
select JNO
from SPJ
group by JNO
having sum(QTY)between 200 and 400
-- 工程不止有一个条目所以是sum

--6．	查询每种零件的零件号，以及使用该零件的工程数。
SELECT P.PNO,COUNT(DISTINCT JNO) AS '工程数'
from p left join spj on spj.PNO=p.PNO
group by p.PNO
-- 1我没用distinct，2没用左外连接，会忽略掉没被任何工程使用的零件

实验3

好久没看了，快忘了

记忆修复中……

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
--1.	查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的详细情况；
SELECT *
FROM Course
WHERE Cname LIKE 'DB_%s__';
-- 错了 下划线是任意字符 需要转码
LIKE 'DB\_%s__' ESCAPE '\'';

--6.	查询所选课程的平均成绩大于张毅的平均成绩的学生学号、姓名及平均成绩；
SELECT Student.Sno,Sname,AVG(Grade) AS '平均成绩'
FROM Student JOIN SC ON(Student.Sno=SC.Sno)
GROUP BY Student.Sno,Sname
HAVING AVG(Grade)>(
	SELECT AVG(Grade) 
	FROM Student JOIN SC ON(Student.Sno=SC.Sno)
	WHERE Sname='张力');
-- 要用avg是聚合函数所以需要groupby having里面也可以嵌套查询

感觉是不是EXISTS的多看看
--10.	查询至少选修“数据库”和“数据结构”课程的学生的基本信息；
我称之为“两个不存在问题”
select *
from student
where not exists (select *
	              from course 
			      where cname in ('数据库','数据结构')
				   and not exists(select *
								   from sc
								   where student.sno = sc.sno and sc.cno =course.cno))
								   
或者：
SELECT *
FROM Student X
WHERE EXISTS(
	SELECT Student.Sno
	FROM Student JOIN SC ON(Student.Sno=SC.Sno) JOIN Course ON(SC.Cno=Course.Cno)
	WHERE Cname='数据库' AND Student.Sno=X.Sno
	INTERSECT
	SELECT Student.Sno
	FROM Student JOIN SC ON(Student.Sno=SC.Sno) JOIN Course ON(SC.Cno=Course.Cno)
	WHERE Cname='数据结构' AND Student.Sno=X.Sno);

--11.	查询没有选修张毅所选修的全部课程的学生的姓名；
--（就是查找一个学生，存在张毅选修的一个课程，他没有选修）
select Sname
from student S1
where exists(-- 存在一门课
    select * 
    from sc SC1
    where Sno=(
        select Sno
        from student
        where Sname='张毅'
    ) and not exists (-- 没有选修
        select *
        from sc SC2
        where S1.Sno=SC2.Sno and SC1.Cno=SC2.Cno
    )
);

--15.	列出同时选修“1”号课程和“2”号课程的所有学生的姓名；（使用三种方法实现）
--(1)exists
select Sname
from student S
where exists(
    select *
    from sc
    where S.Sno=sc.Sno and Cno='1'
) and exists(
    select *
    from sc
    where S.Sno=sc.Sno and Cno='2'
);
--(2)求交集
select Sname
from student
where Sno in
    ((select Sno
    from sc
    where Cno='1')
    intersect 
    (select Sno
    from sc
    where Cno='2'));
--(3)
select Sname
from student
where Sno in
    (select Sno
    from sc
    where Cno='1'and 
	sno in(select Sno
    from sc
    where Cno='2'))
    
--17.	使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；
某个（任意）学生用any，所有用all
SELECT Sname,Sage,Sdept
FROM Student
WHERE Sdept!='CS' AND Sage<ANY(
	SELECT Sage
	FROM Student
	WHERE Sdept='CS');

实验4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
--4.创建每门课程的平均分和选课人数的视图（课程号，课程名，平均分，人数）。
CREATE VIEW course_data
AS
SELECT kc.课程号,课程名,AVG(成绩) AS '平均分',COUNT(学号) AS '人数'
FROM kc LEFT JOIN cj ON(kc.课程号=cj.课程号)
GROUP BY kc.课程号,课程名;
注意左连接

--10.显示选修课程数最多的学号及选修课程数最少的学号，姓名（使用派生表实现）。
SELECT X.学号,姓名
FROM (SELECT TOP 1 学号 FROM cj GROUP BY 学号 ORDER BY COUNT(*) DESC) AS maxsno,
	 (SELECT TOP 1 学号 FROM cj GROUP BY 学号 ORDER BY COUNT(*) ASC) AS minsno,xs X
WHERE X.学号=maxsno.学号 or X.学号=minsno.学号;

--11.查询每个学生成绩高于自己的平均成绩的学号，姓名，课程号和成绩（使用派生表实现）。
SELECT xs.学号,姓名,课程号,成绩
FROM (SELECT 学号,AVG(成绩) AS score FROM cj GROUP BY 学号) AS avgscore,xs JOIN cj ON(xs.学号=cj.学号)
WHERE xs.学号=avgscore.学号 AND cj.成绩>avgscore.score;
派生表？好怪哦

--12.自己验证with check option的作用。
CREATE VIEW CS_stu
AS
SELECT *
FROM xs
WHERE 专业='计算机'
WITH CHECK OPTION;
GO
INSERT
INTO CS_stu
VALUES('100','123','女','1987-05-20','信息管理',10,NULL,NULL);
报错：
--试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION
--而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。
with check option更新视图要满足where条件

--17.	查询没有选修数据结构、操作系统和英语三门课的学生的学号，姓名，课程号，课程名和成绩。
写一个练练手
SELECT * -- 懒得写了
FROM XS JOIN KC ON(..) JOIN CJ ON(..)
WHERE NOT EXISTS(
	SELECT *
	FROM KC Y
	WHERE Y.课程名='数据结构' OR Y.课程名='操作系统' OR Y.课程名='英语');

以前写的作弊版（遇事不决建view）
CREATE VIEW DS_OP_EN
AS
SELECT X.学号
FROM xs X JOIN cj Y ON(X.学号=Y.学号) JOIN kc Z ON(Y.课程号=Z.课程号)
WHERE Z.课程名='数据结构' OR Z.课程名='操作系统' OR Z.课程名='英语';
GO 
SELECT X.学号,姓名,Y.课程号,课程名,成绩
FROM xs X JOIN cj Y ON(X.学号=Y.学号) JOIN kc Z ON(Y.课程号=Z.课程号)
WHERE NOT EXISTS(
	SELECT *
	FROM DS_OP_EN A
	WHERE A.学号=X.学号);

--18.	将没有选课的学生的总学分设置为0。
UPDATE xs
SET 总学分=0
WHERE NOT EXISTS(
	SELECT *
	FROM cj
	WHERE xs.学号=cj.学号);


实验5

安全性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
--1．创建一windows用户（名字自定），用sql语句建立windows验证模式的登录名。默认数据库为student
CREATE LOGIN [DESKTOP-8QRH2EV\liu] FROM WINDOWS--注意：DESKTOP-8QRH2EV是计算机名，liu是Windows用户名
WITH DEFAULT_DATABASE = Student;
--2．用sql语句和系统存储过程建立登录名sqluser,密码为1234
CREATE LOGIN sqluser WITH PASSWORD = '1234'
exec sp_addlogin sqluser, '1234'
--3．为student数据库新建用户u1，其登录名为sqluser。
USE student
GO
CREATE USER u1 FOR LOGIN sqluser
exec sp_adduser sqluser,u1
--4．新建登录usersf，并将其加入到sysadmin固定服务器角色中。
exec sp_addlogin usersf, '1234'
exec sp_addsrvrolemember 'usersf','sysadmin'--由于sysadmin是系统管理员角色，拥有所有权限。
--5．将student用户u1（登录名为sqluser）加入到db_owner角色中，使其全权负责该数据库,并验证其权限。
USE student
GO 
exec sp_addrolemember 'db_owner',u1
 -- 验证:以sqluser登录,在新建查询界面输入以下内容验证
    USE student
    GO
    SELECT * FROM SC-- 成功，db_owner拥有查询权限
    UPDATE SC
    SET Grade = 75
    WHERE Sno = '200515001' AND cno = '4' -- 成功，db_owner拥有修改表项的权限
	insert into course values('12','大数据','1',3)--成功，db_owner拥有插入的权限
    ALTER TABLE SC
        ADD temp tinyint -- 成功，db_owner拥有更改表结构的权限

--6．为SPJ数据库新建用户u2，u3，其登录名分别为u2，u3。
CREATE LOGIN u2 WITH PASSWORD = '1234', DEFAULT_DATABASE = SPJ
CREATE LOGIN u3 WITH PASSWORD = '1234', DEFAULT_DATABASE = SPJ
USE SPJ
GO
CREATE USER u2 FOR LOGIN u2
CREATE USER u3 FOR LOGIN u3

--（1）授予用户u2对S表有SELECT 权，对P表颜色（COLOR）具有更新权限；
    GRANT SELECT ON S TO u2 WITH GRANT OPTION;
    GRANT UPDATE(COLOR) ON P TO u2
    WITH GRANT OPTION;
--（2）u2将其拥有的权限授予u3；
    -- 本小题在u2登录下执行.验证u2获得权限，并给u3授权
USE SPJ
GO
 SELECT * FROM S; -- 成功
 SELECT * FROM P; -- 拒绝
 UPDATE P SET COLOR='红'--成功
 update P set color='绿' where pno='p1'--拒绝，因为u2没有对P的查询权限
 GRANT SELECT ON S TO u3;
 GRANT UPDATE(COLOR) ON P TO u3
--（3）用sql语句逐一验证u3所获得的权限。
    -- 以u3身份登录:
 SELECT * FROM S; -- 成功
 SELECT * FROM P; -- 拒绝
 UPDATE P SET COLOR='红'--成功
 update P set color='绿' where pno='p1'--拒绝，因为u2没有对P的查询权限
  --（4）撤销用户u3所获得的权限，并验证。
    -- 以u2身份登录并操作:
    REVOKE SELECT ON S FROM u3;
    REVOKE UPDATE(COLOR) ON P FROM u3;
    -- 以u3身份登录并操作:
    SELECT * FROM S -- 拒绝
    UPDATE P SET COLOR='红' --拒绝
--7.在student数据库中建立角色operate,该角色具有对student和course表的查询权限；具有对表sc的插入和修改权限。
USE student
GO
CREATE ROLE operate
GRANT SELECT ON student TO operate
GRANT SELECT ON course TO operate
GRANT update,INSERT ON SC TO operate
--8.拒绝用户u1对sc表的修改权限。
DENY update ON SC TO u1
--9.使用存储过程将角色operate赋给用户u1,并用sql语句验证其权限。（特别验证u1对sc表的修改权限）
exec sp_addrolemember operate,u1
-- 验证:这里用登录名sqluser登录
USE student
GO
SELECT * FROM Student-- 成功
SELECT * FROM course-- 成功
insert into sc values ('200515003','3',88,NULL)--成功
update sc set temp=0
--消息 229，级别 14，状态 5，第 6 行
--拒绝了对对象 'sc' (数据库 'student'，架构 'dbo')的 UPDATE 权限。
--这是因为用户u1通过operate角色获得的对SC的update权限，由于被DENY了，因此该语句的执行被拒绝了.
--10.创建数据库架构jg_test,其所有者为student用户u1。
use student
go
create schema jg_test
authorization u1
grant alter
on schema::dbo
to u1
--（1）授予用户u1建表权限；
grant create table
to u1
--（2）授予用户（自己建立wei）对架构jg_test进行查询、插入的权限
exec sp_addlogin wei
create user wei
for login wei
grant select,insert
on schema::jg_test
to wei
--（3）以u1身份登录sql srver,新建一个表，观察表的架构名称。验证用户wei对该表的查询和插入权限，并验证其是否可对该表进行删除。
create table jg_test.s
(s1 char(10),s2 int)
--命令成功完成
--以wei身份登录sql srver
use student
go
select * from jg_test.s--命令成功完成
insert into jg_test.s values('t1234',10)--命令成功完成
delete from jg_test.s
--消息 229，级别 14，状态 5，第 5 行
--拒绝了对对象 's' (数据库 'student'，架构 'jg_test')的 DELETE 权限。
补充：
--11. 在student数据库中创建架构（schema）teacher指定给用户teacher（也就是要先创建一个teacher用户）
use student
go
CREATE LOGIN tt WITH PASSWORD = '1234'
create user teacher for login tt
create schema teacher AUTHORIZATION teacher
--12.	在已创建的teacher架构中创建“tea”表，表结构为（tno(编号), tname(姓名), tsd（专业）,tphone, te_mail）(数据类型和长度自己定义)，通过teacher架构为teacher1用户设置查询权限，验证teacher1用户对表tea是否具有select权限和delete权限，为什么？
create table teacher.tea
(tno char(10),
 tname varchar(20),
 tsd varchar(10),
 tphone char(11),
 te_mail varchar(30)
 )
 insert into teacher.tea values('1234567890','王伟','计算机','13963112345','123.com')
 --验证过程
 /*在“对象资源管理器”中展开数据库“student”→“安全性”→“架构”，选择新创建的架构teacher，
 右键单击鼠标，在弹出的快捷菜单中选择“属性”菜单项，打开“架构属性”窗口，
 在该架构属性的“权限”选项页中，单击“搜索”按钮，选择用户u1（假设已经创建），为用户授予“选择”权限。单击“确定”*/
 --以sqluser登录
 select * from teacher.tea--成功
delete from teacher.tea--拒绝了对对象 'tea' (数据库 'student'，架构 'teacher')的 DELETE 权限

全复制了qwq

实验6

数据库完整性约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
--1.	将stu数据库中student表的sno定义为主键；
USE STU
go
ALTER TABLE STUDENT 
ALTER COLUMN SNO CHAR(9) NOT NULL/*将某属性设置为主键之前，必须先将该属性设置为NOT NULL*/？？还有这种事
ALTER TABLE STUDENT
ADD CONSTRAINT P1 PRIMARY KEY(SNO)

--3.	为表course中的字段cname添加唯一值约束；
ALTER TABLE COURSE
ADD CONSTRAINT U1 UNIQUE(CNAME)

--4.	将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为sc_pk;
ALTER TABLE SC
ALTER COLUMN SNO CHAR(9) NOT NULL
ALTER TABLE SC
ALTER COLUMN CNO CHAR(4) NOT NULL
ALTER TABLE SC
ADD CONSTRAINT SC_PK PRIMARY KEY(SNO,CNO)

--5)	向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；
ALTER TABLE SC
ADD CONSTRAINT F4 FOREIGN KEY(SNO)  REFERENCES STUDENT(SNO)
就是添加外码的意思

--6.	定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；
必须是数字字符！
ALTER TABLE STUDENT 
ADD CONSTRAINT C1 CHECK(SNO LIKE '[1-9]00[0-9][0-9][0-9][0-9][0-9][0-9]')
错误：
ALTER TABLE Student
ADD CONSTRAINT stu_sno_check CHECK(Sno LIKE '[1-9]00______');

--13.	设计触发器实现如果一个学生转专业了，那么输出一条信息显示该学生各门课程的平均分。
USE STUDENT
GO
CREATE TRIGGER A1
ON STUDENT
FOR
UPDATE
AS 
IF UPDATE(SDEPT)
BEGIN
DECLARE @AVG NUMERIC(4,1),@SNO CHAR(9)
SELECT @SNO=(SELECT SNO FROM DELETED)
SELECT @AVG=(SELECT AVG(GRADE) FROM SC WHERE SNO=@SNO) 
PRINT @SNO+'各门课的平均成绩'+cast(@AVG as CHAR(5))
END
我的：
CREATE TRIGGER t13 ON Student AFTER UPDATE AS
    IF(UPDATE(Sdept))
        BEGIN
            DECLARE @sno CHAR(9)
            SELECT @sno=Sno FROM inserted
            SELECT AVG(Grade) '平均分' FROM SC WHERE SC.Sno=@sno
        end;
GO
--测试
UPDATE STUDENT SET SDEPT='CS' WHERE SNO='200515008'/*只有修改sdept时，触发器才触发执行*/
UPDATE STUDENT SET SAGE=26 WHERE SNO='200515008'/*该语句只修改了学生的年龄，A1触发器并没有执行*/
if(update(sdept))总感觉在哪见过，记一下，要忘了

15.在课程表里添加一列：选课人数，设置触发器每门课如果有人选修了那么课程表里的选课人数相应修改，考虑批量插入选课记录的情况。
create trigger cnt_add
on sc 
for insert
as
begin
if @@ROWCOUNT=1
update course set cnt=cnt+1
where course .cno in (select cno  from inserted)
else 
update course set cnt=cnt+(select count(sno) from inserted where course .cno =inserted.cno ) 
where course.cno in (select cno from inserted)
end
看看没用游标版（用update...in）
建在sc上

--16.设计一触发器，约束每门课程的课容量为120。
use student
go
create trigger ex16
on sc
for insert
as 
	declare @count int,@kecheng varchar(20)
	select @count=count(*) from sc where cno=(select cno from inserted)
	select @kecheng=cname from course where cno=(select cno from inserted)
	if(@count>120)
		begin
			print '课程'+@kecheng+'人数已经达到课容量'
			rollback
		end
	else
		begin
			print '选课'+@kecheng+'成功'
		end
看@count是如何得到的

--20.	使用第19题的两个表设计触发器实现如果员工的工资发生变化则向工资变化表插入一条记录，包含教工编号，原工资，新工资。
CREATE TRIGGER t20 ON Teacher AFTER UPDATE AS
    IF(UPDATE(TSALARY))-- 不应该是更新，工资发生变化有一个和原值比较的步骤
        BEGIN
            DECLARE @osalary MONEY,@nsalary MONEY,@tno CHAR(9)
            SELECT @osalary=TSALARY FROM deleted
            SELECT @nsalary=TSALARY,@tno=TNO FROM inserted

            INSERT INTO Salary(TNO, T_old_salary, T_new_salary)
            VALUES(@tno,@osalary,@nsalary)
        end;
    GO
    
create trigger tri20 on teacher
after update
as
begin
declare @a char(9), @b int, @c int
select @a = tno, @b = salary from deleted
select @c = salary from inserted
if (@b<>@c) 
		insert into salary_change
		values(@a, @b, @c)
end

实验7

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
--4.	编写一个存储过程，统计某门课程的优秀（90-100）人数、良好（80-89）人数、中等（70-79）人数、及格（60-69）人数和及格率，
--其输入参数是课程号，输出的是各级别人数及及格率，及格率的形式是90.25%，执行存储过程，在消息区显示1号课程的统计信息。
create procedure p1
@cno char(4),
@great int output,
@good int output,
@medium int output,
@pass int output,
@passper varchar(10) output
as
	declare @sum float
	select @great=count(*)	from sc where cno=@cno and grade between 90 and 100
	select @good=count(*)	from sc where cno=@cno and grade between 80 and 89
	select @medium=count(*)	from sc where cno=@cno and grade between 70 and 79
	select @pass=count(*)	from sc where cno=@cno and grade between 60 and 69
	select @sum=count(*)	from sc where cno=@cno
	select @passper = str(cast((@great+@good+@medium+@pass)*100/@sum as numeric(5,2)))
	--select @passper=(str(cast((@great)*100.0/@sum*1.0 as ) ) +'%')
declare	@gr int,@go int,@me int,@pa int,@pap varchar(10)
exec p1 '1' ,
@gr output,
@go  output,
@me  output,
@pa output,
@pap output
print '优秀人数'+str(@gr) 
print '良好人数'+str(@go)  
print '中等人数'+str(@me) 
print '及格人数'+str(@pa) 
print '及格率'+@pap+'%'

我的：
CREATE PROCEDURE Course_percent @cno CHAR(4) AS
    DECLARE @S INT,@A INT,@B INT,@C INT,@per VARCHAR(10)
    SELECT  @S=COUNT(CASE WHEN Grade BETWEEN 90 AND 100 THEN 1 END),
            @A=COUNT(CASE WHEN Grade BETWEEN 80 AND 89 THEN 1 END),
            @B=COUNT(CASE WHEN Grade BETWEEN 70 AND 79 THEN 1 END),
            @C=COUNT(CASE WHEN Grade BETWEEN 60 AND 69 THEN 1 END),
            @per=CONCAT(CAST(CONVERT(FLOAT,COUNT(CASE WHEN Grade BETWEEN 60 AND 100 THEN 1 END))/CONVERT(FLOAT,COUNT(DISTINCT Sno))*100.0 AS NUMERIC(10,2)),'%')
    FROM SC JOIN Course C2 on C2.Cno = SC.Cno WHERE @cno=SC.Cno
    PRINT '优秀人数：'+CAST(@S AS VARCHAR(10))+' 良好人数：'+CAST(@A AS VARCHAR(10))+' 中等人数：'+CAST(@B AS VARCHAR(10))+' 及格人数：'+CAST(@C AS VARCHAR(10))+' 及格率：'+@per;
GO

--7. 请设计一个存储过程实现下列功能：
--判断某个专业某门课程成绩排名为n的学生的成绩是否低于该门课程的平均分，如果低于平均分
--，则将其成绩改为平均分，否则输出学号、姓名、课程号、课程名、成绩。（提示：可以在存储过程内部使用游标）。
create procedure ex7 @dept varchar(8),@cno char(4),@n int
as
declare @avggrade int
select @avggrade=(select avg(grade)	from sc	where cno=@cno)
declare c scroll cursor for
select student.sno,sname,sc.cno,cname,grade from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno and course.cno=@cno and student.sdept=@dept 
order by grade desc
open c
declare @sno char(9),@sname nchar(6),@cno1  char(4),@cname varchar(20),@grade int
fetch absolute @n from c into @sno,@sname,@cno1,@cname,@grade
if @grade>@avggrade
print @sno+'   '+@sname+'    '+@cno1+'    '+@cname+'    '+cast(@grade as char(4))
else
update sc
set grade=@avggrade
where current of c
close c
deallocate c
--测试
exec  ex7 'CS', '1',3

完蛋，某个专业我就没看见
CREATE PROCEDURE  Student_grade_update @cno CHAR(4),@rank INT AS
    DECLARE @avg INT,@sno CHAR(9),@grade SMALLINT
    SELECT @avg=AVG(Grade) FROM Student JOIN SC S on Student.Sno = S.Sno WHERE @cno=Cno
    DECLARE csr3 SCROLL CURSOR FOR
        SELECT Sno,Cno,Grade
        FROM SC
        WHERE @cno=Cno
        ORDER BY Grade DESC
    OPEN csr3
    FETCH ABSOLUTE @rank FROM csr3 INTO @sno,@cno,@grade
    IF @grade<@avg
        BEGIN
            PRINT '成绩低于这门课程平均分'
            UPDATE SC SET Grade=@avg WHERE @sno=Sno AND @cno=Cno
        end
    ELSE BEGIN
            SELECT S2.Sno,Sname,C.Cno,Cname,Grade
            FROM Student JOIN SC S2 on Student.Sno = S2.Sno JOIN Course C on C.Cno = S2.Cno
            WHERE @cno=C.Cno AND @sno=S2.Sno
    end
    CLOSE csr3
    DEALLOCATE csr3
GO


--10.	现有图书管理数据库， 其中包含如下几个表：
--读者表：reader(学号，姓名，性别，余额)
--借书表：lend（学号，书号，借书日期，应还日期，是否续借）
--欠款表：debt(学号，日期，欠款金额)
--还书表：return(学号，书号，还书日期) 
--请设计一个存储过程实现续借或还书操作，具体要求如下：
--只有没有超期的书才可以续借（借书和续借时间都为30天），并修改应还日期，否则只能还书；还书时删除借书表内的借阅记录，并向还书表中插入一条还书记录，注意还书日期为当前日期，并且对超期图书，按照超期的天数计算出罚款金额（每天每本书罚款0.1元），并将罚款信息插入到欠款表中，同时将罚款从读者表的余额里扣除。
use 图书管理--已建数据库
create table reader
(学号 char(9),姓名 varchar(10),性别 char(2),余额 money)
create table lend
(学号 char(9),书号 char(20),借书日期 date,应还日期 date,是否续借 varchar(6))

create table debt
(学号 char(9),日期 date,欠款金额 money)

create table [return]
(学号 char(9),书号 char(20),还书日期 date)
create procedure return_book @sno char(9),@bno char(20)
as
declare @rdate date,@xujie varchar(6),@day int
select @rdate=应还日期,@xujie=是否续借
from lend
where 学号=@sno and 书号=@bno
 if (@xujie='续借' and @rdate>=getdate() ) 
 update lend set 应还日期=dateadd(day,30,应还日期)
  else begin
       delete from lend
	   where 学号=@sno and 书号=@bno
	   insert into [return] values (@sno,@bno,getdate())
	   end
if(@rdate<getdate())
begin
set @day=datediff(day,@rdate,getdate())
insert into debt values(@sno,getdate(),@day*0.1)
update reader set 余额=余额-@day*0.1
where 学号=@sno 
end
--测试
exec return_book '201900801','12345678'
exec return_book '201900801','112345678'
exec return_book '201900802','12345678'
STERNE

STERNE

SDUer，大一入门，大二入土，大三老东西！

63
1
20
TOC
  1. 1. 实验1
  2. 2. 实验2
  3. 3. 实验3
  4. 4. 实验4
  5. 5. 实验5
  6. 6. 实验6
  7. 7. 实验7
NOTICE

这里还什么都没有呢喵~

CATEGORYS
TAGS
ACM Arc CCPC HPC bot c cpp deeplearning games hexo java maths pyqt python qt recommends review sql srtp test