SQL Server经典练习题

m0_65192155

于 2023-10-12 23:19:48 发布

阅读量311

点赞数 2

文章标签：数据库

本文链接：https://blog.csdn.net/m0_65192155/article/details/133781196

版权

创建题目所需数据表以及添加数据

--学生表 Student
create table Student
(
    SId varchar(10),
    Sname varchar(10),
    Sage datetime,
    Ssex varchar(10)
);

--教师表 Teacher
create table Teacher
(
    TId varchar(10),
    Tname varchar(10)
);

--科目表 Course
create table Course
(
    CId varchar(10),
    Cname nvarchar(10),
    TId varchar(10)
);

--成绩表 Sc
create table SC
(
    SId varchar(10),
    CId varchar(10),
    score decimal(18,1)
);

--学生表 Student 添加数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

--教师表 Teacher 添加数据
insert into Teacher values('01' , '张老师');
insert into Teacher values('02' , '李老师');
insert into Teacher values('03' , '王老师');

--科目表 Course  添加数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--成绩表 Sc      添加数据
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

练习题目

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

--方法一
select stu.*,sc1.CId,sc1.score,sc2.CId,sc2.score from Student stu
inner join SC sc1 on sc1.SId = stu.SId and sc1.CId = '01'
inner join SC sc2 on sc2.SId = stu.SId and sc2.CId = '02'
where sc1.score > sc2.score

--方法二
SELECT st.*,sc.score FROM Student st INNER JOIN (
SELECT sc1.sid,sc1.score FROM(
SELECT * FROM SC WHERE CId = '01') sc1 INNER join
(SELECT * FROM SC WHERE CId = '02') sc2 ON sc1.SId = sc2.SId 
WHERE sc1.score > sc2.score) sc
ON st.SId = sc.SId

2.查询同时存在" 01 "课程和" 02 "课程的学生数据

----方法一
select stu.*,sc1.CId,sc2.CId from Student stu
inner join SC sc1 on sc1.SId = stu.SId and sc1.CId = '01'
inner join SC sc2 on sc2.SId = stu.SId and sc2.CId = '02'

----方法二
SELECT * FROM Student WHERE sid IN(
SELECT sid FROM SC WHERE CId = '02' AND sid IN(SELECT sid FROM SC WHERE CId = '01'))

3.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

SELECT * FROM (SELECT * FROM SC  WHERE CId = '01') sc1 
LEFT JOIN(SELECT * FROM SC  WHERE CId = '02') sc2 
ON sc1.SId = sc2.SId

4.查询不存在" 01 "课程但存在" 02 "课程的情况

select * from Student st
where st.SId not in(select scs1.SId from SC scs1 where scs1.CId = '01')
and st.SId in(select scs2.Sid from SC scs2 where scs2.CId = '02')

5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select st.SId,st.Sname,scavg.平均成绩 from Student st 
inner join (select scs.SId ,avg(scs.score) '平均成绩' from SC scs 
group by scs.SId having avg(scs.score) >= 60) scavg on st.SId = scavg.SId

6.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

----方式一(子查询)
select scs.SId,st.Sname,scs.courseSum,scs.scoreSum from Student st
inner join(
select sc.SId ,COUNT(sc.CID) 'courseSum',sum(sc.score) 'scoreSum' from SC sc
group by sc.SId) scs on st.SId = scs.SId

----方式二(联合查询)
SELECT st.SId,st.Sname,COUNT(sc.CId) 'courseSum',SUM(sc.score) 'scoreSum' 
FROM Student st 
INNER JOIN SC  sc ON st.SId= sc.SId GROUP BY st.SId,st.Sname

7.查询学过「张」老师授课的同学的信息

select st.* from Student st
inner join sc sc on st.SId = sc.SId
where sc.CId = (select c.CID from Course c where c.Tid = 
(select Tid from Teacher t where t.Tname like '张老师' ))

8. 查询没有学全所有课程的同学的信息

select * from Student st 
inner join
(select scs.SId,COUNT(scs.CId) as ccount from SC scs group by scs.SId )sc
on st.SId = sc.SId 
WHERE sc.ccount <(select count(Course.CID) from Course)

9.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

select * from Student st where st.SId in(
select distinct scc.SId from  SC scc where scc.CId in(
select CId from SC sc where sc.SId = '01')) AND st.SId <> '01'

10.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

----方法一
select * from Student where sid 
IN(select sid from sc where cid in(select cid from sc where sid=01)
GROUP by sid 
having COUNT(CId)=(select count(cid) from sc where sid=01)) AND SId <> '01'

--方法二
select * from Student where sid in (select SId from (select distinct Sid,cid=
  stuff((
	select ','+CId from SC where sc.SId=tt.SId for xml path('')),1,1,'')
   from SC as tt  group by Sid,cid) a where a.cid=(select b.cid from (select distinct Sid,cid=
  stuff((
	select ','+CId from SC where sc.SId=tt.SId for xml path('')),1,1,'')
   from SC as tt where SId='01' group by Sid,cid) b)) and Student.SId!='01'

11. 查询没学过"张"老师讲授的任一门课程的学生姓名

SELECT * FROM Student st WHERE st.SId NOT IN(
SELECT sid FROM SC where cid  
IN (select c.CID from Course c where 
c.Tid = (select Tid from Teacher t where t.Tname  like '张老师' )))

12.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select st.SId,st.Sname,sc.avg from Student st 
inner join(
select scs.SId,avg(scs.score) 'avg' from SC scs 
where scs.score < 60 group by scs.SId having count(scs.CId) >= 2) 
sc on sc.SId = st.SId

13.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

----方法一
select * from Student st inner join(
select scc.SId,scc.score,scc.CId from SC scc 
where scc.CId = '01' and scc.score < 60 ) 
sc on sc.SId = st.SId
order by sc.score DESC

----方法二
SELECT * FROM Student st INNER JOIN SC sc 
ON st.SId = sc.SId WHERE sc.CId = '01' and sc.score < 60  
ORDER BY sc.score DESC

14. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

----方法一
SELECT * FROM
(SELECT sc.SId,
max(CASE WHEN sc.CId = '01' THEN sc.score ELSE NULL end) '01', 
max(CASE WHEN sc.CId = '02' THEN sc.score ELSE NULL end) '02',
max(CASE WHEN sc.CId = '03' THEN sc.score ELSE NULL end) '03',
avg(sc.score) 'avgScore'
FROM SC sc GROUP BY sc.SId ) a ORDER BY a.avgScore desc

----方法二
SELECT st.SId,
(SELECT sc1.score FROM SC sc1 WHERE CId = '01' AND sc1.SId = st.SId) '01',
(SELECT sc2.score FROM SC sc2 WHERE CId = '02' AND sc2.SId = st.SId) '02',
(SELECT sc3.score FROM SC sc3 WHERE CId = '03' AND sc3.SId = st.SId) '03',
AVG(sc.score) 'avgScore'
FROM  Student  st
INNER JOIN SC sc ON st.SId = sc.SId
GROUP BY st.SId
ORDER BY  avgScore desc

15.查询各科成绩最高分、最低分和平均分，选修人数，及格率，中等率，优良率，优秀率

----方式一
select cc.CId,cc.Cname, 
max(scc.score) '最高分',
min(scc.score) '最低分',
avg(scc.score) '平均分',
count(scc.SId) '选修人数',
CONCAT(SUM(CASE WHEN scc.score>=60 THEN 1 ELSE 0 end)*100 / COUNT(scc.SId),'%')'及格率',
CONCAT(SUM(CASE WHEN scc.score>=70 AND scc.score < 80 THEN 1 ELSE 0 end)*100 / COUNT(scc.SId),'%')'中等率',
CONCAT(SUM(CASE WHEN scc.score>=80 AND scc.score < 85 THEN 1 ELSE 0 end)*100 / COUNT(scc.SId),'%')'优良率',
CONCAT(SUM(CASE WHEN scc.score>=85 AND scc.score <=100 THEN 1 ELSE 0 end)*100 / COUNT(scc.SId),'%')'优秀率'
from Course cc inner join
SC scc on cc.CID = scc.CId group by cc.Cname ,cc.CId

----方式二
SELECT s.CId,
(SELECT c.Cname FROM Course c WHERE c.CId = s.CId) 'CName',
max(s.score) '最高分',
min(s.score) '最低分',
avg(s.score) '平均分',
count(s.SId) '选修人数',
CONCAT((SELECT COUNT(sid) FROM SC  WHERE CId = s.CId and score>=60 ) * 100 / COUNT(sid),'%') '及格率',
CONCAT((SELECT COUNT(sid) FROM SC  WHERE CId = s.CId and score>=70 AND score < 80 ) * 100 / COUNT(sid),'%') '中等率',
CONCAT((SELECT COUNT(sid) FROM SC  WHERE CId = s.CId and score>=80 AND score < 85 ) * 100 / COUNT(sid),'%') '优良率',
CONCAT((SELECT COUNT(sid) FROM SC  WHERE CId = s.CId and score>=85 ) * 100 / COUNT(sid),'%') '优秀率'
 FROM SC s
 GROUP BY s.CId

16.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select c.Cname,sc.score,
rank() over(partition by c.cname order by sc.score desc) '排名' 
from SC sc
inner join Course c on sc.CId = c.CId

17.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

select st.SId,st.Sname,scSum.sumScore,
DENSE_RANK() over(order by scSum.sumScore desc) '排名' 
from Student st
inner join 
(select sc.Sid,SUM(sc.score) 'sumScore' from SC sc group by sc.SId) scSum
on st.SId = scSum.SId

18.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

----方法一
select c.CId,c.Cname,
sum(case when sc.score>=85 and sc.score<=100 then 1 ELSE 0 end) '[100-85]',
sum(case when sc.score>=70 and sc.score<85 then 1 ELSE 0 end) '[85-70]',
sum(case when sc.score>=60 and sc.score<70 then 1 ELSE 0 end) '[70-60]',
sum(case when sc.score>=0 and sc.score<60 then 1 ELSE 0 end) '[60-0]',
CONCAT(sum(case when sc.score>=85 and sc.score<=100 then 1 ELSE 0 end) * 100 / COUNT(sc.SId),'%') '[100-85_Percentage]',
concat(sum(case when sc.score>=70 and sc.score<85 then 1 ELSE 0 end)* 100 / count(sc.sid),'%') '[85-70_Percentage]',
concat(sum(case when sc.score>=60 and sc.score<70 then 1 ELSE 0 end)* 100 / count(sc.sid),'%') '[70-60_Percentage]',
concat(sum(case when sc.score>=0 and sc.score<60 then 1 ELSE 0 end) * 100 / count(sc.sid),'%')'[60-0_Percentage]'
from SC  sc
inner join Course c 
on sc.CId = c.CId
group by c.CId,c.Cname

----方法二
SELECT sc.CId,
(SELECT c.Cname FROM Course c WHERE c.CId = sc.CId) AS Cname,
(SELECT COUNT(a.sid) FROM SC a WHERE a.cid = sc.CId and (score >= 85 and score <= 100)) AS '[100-85]',
(SELECT COUNT(b.sid) FROM SC b WHERE b.cid = sc.CId and (score >= 70 and score < 85)) AS '[85-70]',
(SELECT COUNT(c.sid) FROM SC c WHERE c.cid = sc.CId and (score >= 60 and score < 70)) AS '[70-60]',
(SELECT COUNT(d.sid) FROM SC d WHERE d.cid = sc.CId and (score >= 0 and score < 60)) AS '[60-0]',
CONCAT((SELECT COUNT(a.sid) FROM SC a WHERE a.cid = sc.CId and (score >= 85 and score <= 100)) * 100 /COUNT(sc.SId),'%') AS '[100-85_Percentage]',
CONCAT((SELECT COUNT(b.sid) FROM SC b WHERE b.cid = sc.CId and (score >= 70 and score < 85)) * 100/COUNT(sc.SId),'%') AS '[85-70_Percentage]',
CONCAT((SELECT COUNT(c.sid) FROM SC c WHERE c.cid = sc.CId and (score >= 60 and score < 70)) * 100/COUNT(sc.SId),'%') AS '[70-60_Percentage]',
CONCAT((SELECT COUNT(d.sid) FROM SC d WHERE d.cid = sc.CId and (score >= 0 and score < 60)) * 100/COUNT(sc.SId),'%') AS '[60-0_Percentage]'
FROM SC sc 
GROUP BY sc.CId

19.查询各科成绩前三名的记录

SELECT * FROM (select sc.SId,c.Cname,sc.score,
DENSE_RANK() over(partition by c.cid order by sc.score desc) row_no 
from SC sc
inner join Course c on sc.CId = c.CId ) a WHERE a.row_no <=3

20.查询出只选修两门课程的学生学号和姓名

select st.SId,st.Sname from Student st where st.SId IN
(select sc.SId nums from SC sc GROUP by sc.SId having count(sc.CId) = 2)

21. 查询同名学生名单，并统计同名人数

SELECT st.Sname,COUNT(st.Sname) '同名人数' FROM Student st 
GROUP BY st.Sname 
HAVING COUNT(st.Sname) > 1

22. 查询 1990 年出生的学生名单

select * from Student   where year(Sage) = '1990'

23.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT sc.CId,AVG(sc.score) 'avgScore' FROM SC  sc 
GROUP BY sc.CId 
ORDER BY AVG(sc.score) DESC,sc.CId asc

24.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

----方法一
select st.SId,st.Sname,scf.stu_avg from Student st
inner join(
select sc.SId,avg(sc.score) 'stu_avg' from SC sc group by sc.SId)  scf
on  st.SId = scf.SId
where scf.stu_avg >= 85

----方法二
select st.SId,st.Sname,avg(sc.score) 'avg' from Student st
inner join SC sc
on sc.SId = st.SId
group by st.SId,st.Sname
having avg(sc.score) >=85

25.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select st.Sname,sc.score from Student st 
inner join SC sc on sc.SId = st.SId
inner join Course c on c.CId = sc.CId
where c.Cname = '数学'  and sc.score < 60

26.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select * from Student st
left join SC sc on sc.SId = st.SId
left join Course c on c.CId = sc.CId

27.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

select st.Sname,c.Cname,sc.score from Student st
inner join SC sc on st.SId = sc.SId
inner join Course c on c.CId = sc.CId
where sc.score > 70

28. 查询存在不及格的课程

select c.Cname,scc.score from Course c inner join(
select sc.CId,sc.score from SC sc where sc.score < 60) scc
on c.CId = scc.CId

29.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名

select st.SId,st.Sname from Student st 
where st.SId in(                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
select sc.SId from SC sc 
where sc.CId = '01' and sc.score >=80)

30.成绩有重复的情况下，查询选修「张」老师所授课程的学生中，成绩最高的学生信息及其成绩

select stf.*,sc.score from Student stf inner join 
SC sc on sc.SId = stf.SId 
inner join
(
select scs.CId,MAX(scs.score) zhangMax from SC scs
inner join Course c on scs.CId = c.CId
where c.TId = (select t.TId from Teacher t where t.Tname like '张%')   
group by scs.CId
) maxs on maxs.CId = sc.CId  and sc.score = maxs.zhangMax

31.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select * from SC sc1
inner join SC sc2 ON sc1.SId = sc2.SId 
where  sc1.CId != sc2.CId and sc1.score = sc2.score

32.查询每门功成绩最好的前两名

SELECT * FROM(select scs.CId,scs.SId,scs.score,cs.Cname,
 DENSE_RANK() over(partition by scs.cid order by scs.score desc) row_no from SC scs 
 inner join Course cs on scs.CId = cs.CId ) a WHERE a.row_no <=2

33. 统计每门课程的学生选修人数（超过 5 人的课程才统计)

select c.Cname ,count(sc.CId) '选修人数' from Course c
inner join SC sc on c.CId = sc.CId
group by c.Cname having count(sc.CId) >5

34.检索至少选修两门课程的学生学号

select sc.SId,COUNT(sc.CId) from SC sc
group by sc.SId having COUNT(sc.CId)>=2

--35.查询选修了全部课程的学生信息

select * from Student st 
where st.SId in(
select sc.sid from SC sc group by sc.SId having count(sc.CId) =(
select count(cs.CId) 'cNum' from Course cs ))

36. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

----实现过程
SELECT st.SId,st.Sname,
DATEDIFF(yy,st.Sage,GETDATE()) '年份差值',
st.Sage '出生日期',
GETDATE() '当前日期',
CONVERT(VARCHAR(10),st.Sage,21)'出生日期2',
SUBSTRING(CONVERT(VARCHAR(10),st.Sage,20),6,2) '出生月份',
RIGHT(CONVERT(VARCHAR(10),st.Sage,20),2) '出生日期',
CONCAT(SUBSTRING(CONVERT(VARCHAR(10),st.Sage,20),6,2),RIGHT(CONVERT(VARCHAR(10),st.Sage,20),2)) '出生月日',
CONCAT(SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),20),6,2),RIGHT(CONVERT(VARCHAR(10),GETDATE(),20),2)) '当前月日',
(CASE WHEN CONCAT(SUBSTRING(CONVERT(VARCHAR(10),st.Sage,20),6,2),RIGHT(CONVERT(VARCHAR(10),st.Sage,20),2))
<= CONCAT(SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),20),6,2),RIGHT(CONVERT(VARCHAR(10),GETDATE(),20),2))  
THEN DATEDIFF(yy,st.Sage,GETDATE()) ELSE (DATEDIFF(yy,st.Sage,GETDATE()) - 1) END) '年龄'
FROM Student st

----最终结果代码
SELECT st.SId,st.Sname,st.Sage,
(CASE WHEN CONCAT(SUBSTRING(CONVERT(VARCHAR(10),st.Sage,20),6,2),RIGHT(CONVERT(VARCHAR(10),st.Sage,20),2))<=CONCAT(SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),20),6,2),RIGHT(CONVERT(VARCHAR(10),GETDATE(),20),2))  
THEN DATEDIFF(yy,st.Sage,GETDATE()) ELSE (DATEDIFF(yy,st.Sage,GETDATE()) - 1) END) '年龄'
FROM Student st

37.查询本周过生日的学生

----实现过程
SELECT st.SId,st.Sname,
CONVERT(VARCHAR(10),st.Sage,21) '出生日期',
CONVERT(VARCHAR(10),GETDATE(),21) '当前日期',
RIGHT(CONVERT(VARCHAR(10),st.Sage,21),6)'获取过生日年月',
CONCAT('2023',RIGHT(CONVERT(VARCHAR(10),st.Sage,21),6))'查询今年过生日的日期',
(CASE WHEN DATEPART(WEEK,CONCAT('2023',RIGHT(CONVERT(VARCHAR(10),st.Sage,21),6))) = DATEPART(week,getdate()) THEN '是'ELSE '否' end)'是否生日'
FROM Student st

----最终代码
SELECT st.SId,st.Sname,
CONVERT(VARCHAR(10),st.Sage,21) '出生日期',
CONVERT(VARCHAR(10),GETDATE(),21) '当前日期',
(CASE WHEN DATEPART(WEEK,CONCAT('2023',RIGHT(CONVERT(VARCHAR(10),st.Sage,21),6))) = DATEPART(week,getdate()) THEN '是'ELSE '否' end)'是否生日'
FROM Student st

38.查询下周过生日的学生

SELECT st.SId,st.Sname,
CONVERT(VARCHAR(10),st.Sage,21) '出生日期',
CONVERT(VARCHAR(10),GETDATE(),21) '当前日期',
(CASE WHEN DATEPART(WEEK,CONCAT('2023',RIGHT(CONVERT(VARCHAR(10),st.Sage,21),6))) = (DATEPART(week,getdate()) + 1) THEN '是'ELSE '否' end)'是否生日'
FROM Student st

40. 查询下月过生日的学生

select * from Student st where month(st.Sage) =MONTH(GETDATE()) + 1

41.赵雷同学因为某些原因转学，要求用事务方式删除掉赵雷同学相关的信息

--开始事务
BEGIN tran tran_delete

DECLARE @setError int
SET @setError = 0
delete from SC where SC.SId = (
select  stf.SId from Student  stf  where stf.Sname = '赵雷')
SET	@setError = @setError + @@error
delete from Student  where  Sname = '赵雷'
SET	@setError = @setError + @@error

if (@setError = 0)
begin
	commit tran tran_delete
end
else
begin
	rollback tran tran_delete
end

42.新入职周老师是计算机专业毕业的,请用触发器实现对周老师相关信息的添加

--after触发器(方法一)
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'tri_insertTeachar')
	DROP TRIGGER tri_insertTeachar
	go
create trigger tri_insertTeachar on Teacher  
for  insert
as
BEGIN
	DECLARE @tid VARCHAR(10)
	SELECT @tid =TId FROM Inserted
	INSERT INTO Course VALUES('04','计算机',@tid)
END
go
INSERT INTO Teacher VALUES('04','周老师')

--instead of触发器(方法二)
IF EXISTS(SELECT * FROM sysobjects where name = 'tri_insertT')
	DROP TRIGGER tri_insertT
	go
CREATE TRIGGER tri_insertT ON Teacher INSTEAD OF INSERT
AS
	DECLARE @tid VARCHAR(10),@tname VARCHAR(20)
	SELECT @tid =TId,@tname =Tname FROM Inserted
	INSERT INTO Course VALUES('04','计算机',@tid)
	INSERT INTO Teacher VALUES(@tid,@tname)
go
INSERT INTO Teacher VALUES('04','周老师')

43.现张老师已到退休年龄，需要从系统中删除张老师的授课信息，要求用触发器实现

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'trigger_DeleteTeacher')
	DROP TRIGGER trigger_DeleteTeacher
	GO
CREATE TRIGGER trigger_DeleteTeacher 
ON Teacher 
INSTEAD OF DELETE
AS
	DECLARE @tid VARCHAR(20)	--删除老师的信息
	DECLARE @error int
	SELECT @tid = TId FROM Deleted
	DELETE FROM SC WHERE SC.CId =(SELECT CId FROM Course WHERE TId =@tid)
	SET @error += @@ERROR
	DELETE FROM Course WHERE TId = @tid
	SET @error += @@ERROR
	DELETE FROM teacher WHERE TId = @tid
	SET @error += @@ERROR
	IF (@error <> 0)
	BEGIN
	    PRINT '删除失败'
		ROLLBACK TRAN
	END
GO 

DELETE FROM teacher WHERE tname = '张老师'

44.判断英语成绩有没有及格,及格为60分,若没有及格则将分数加5分,直到及格,用游标方式实现

--声明游标
declare  cursor_addSocre CURSOR 
SCROLL FOR (SELECT SId,score FROM SC WHERE CId = (SELECT CId FROM Course WHERE Cname = '英语'))

--打开游标
OPEN cursor_addSocre

--遍历游标
DECLARE @englishScore INT,@SID VARCHAR(10) 
FETCH first FROM cursor_addSocre into @SID,@englishScore
WHILE (@@FETCH_STATUS = 0)
BEGIN
    WHILE(@englishScore < 60)
    BEGIN
        UPDATE SC SET score += 5 WHERE SId = @SID
		SELECT @englishScore = score FROM SC WHERE CId = (SELECT CId FROM Course WHERE Cname = '英语') AND SId = @SID
    END
	FETCH NEXT FROM cursor_addSocre INTO  @SID,@englishScore
END

--关闭游标
CLOSE cursor_addSocre
--删除游标
DEALLOCATE cursor_addSocre
GO

45.得到每个学生的各科的考试成绩，要求使用自定义函数

----方法一 内嵌表值函数
CREATE FUNCTION fun_getStuInfoScore()
RETURNS table
RETURN SELECT st.SId,st.Sname,
	   MAX(CASE WHEN c.Cname = '语文' THEN sc.score ELSE NULL end) '语文',
	   MAX(CASE WHEN c.Cname = '数学' THEN sc.score ELSE NULL end) '数学',
	   MAX(CASE WHEN c.Cname = '英语' THEN sc.score ELSE NULL end) '英语'
	   FROM Student st INNER JOIN SC sc ON st.SId = sc.SId INNER JOIN Course c ON sc.CId = c.CId  GROUP BY st.SId,st.Sname
GO 

SELECT * from dbo.fun_getStuInfoScore()
GO 

----方法二 标量函数 
CREATE FUNCTION fun_getScore(@sname VARCHAR(20),@cname VARCHAR(20))
RETURNS DECIMAL
AS
BEGIN
       DECLARE @score  DECIMAL
	   SELECT @score =sc.score
	   FROM Student st INNER JOIN SC sc ON st.SId = sc.SId INNER JOIN Course c ON sc.CId = c.CId WHERE st.Sname = @sname --GROUP BY st.SId,st.Sname 
	   RETURN @score
END
GO
	
SELECT dbo.fun_getScore('赵雷','语文') AS 成绩

m0_65192155

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
SQL Server经典练习题

---方法一内嵌表值函数MAX(CASE WHEN c.Cname = '语文' THEN sc.score ELSE NULL end) '语文',MAX(CASE WHEN c.Cname = '数学' THEN sc.score ELSE NULL end) '数学',MAX(CASE WHEN c.Cname = '英语' THEN sc.score ELSE NULL end) '英语'GOGO----方法二标量函数ASBEGINENDGO。
复制链接

扫一扫