SQL Server 初学综合练习题

最新推荐文章于 2024-04-04 13:13:07 发布

打劫棒棒糖

最新推荐文章于 2024-04-04 13:13:07 发布

阅读量717

点赞数 1

分类专栏：数据库文章标签：数据库 sql sqlserver

本文链接：https://blog.csdn.net/qq_37784387/article/details/129257777

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SQL Server 实践练习

说明

说明

sql语句综合练习题目,包含简单查询，聚合，嵌套，多表等。对于问题的解答可能有多种sql语句写法，这里只是提供一种思路方案，不考虑实际效率。如有错误，欢迎指正！

一、准备数据表

新建数据库，命名随意，然后添加下列数据表：

/*学生信息表*/
create table Student(
StuNo varchar(10) not null primary key,
StuName nvarchar(20) not null,
age int not null,
sex varchar(2) not null
)
/*教师表*/
create table Teacher(
TeacherNo varchar(10) NOT NULL primary key,
TeacherName Nvarchar(20) NOT NULL
)
/*课程表 （含课程老师） */
create table Course(
CouNo varchar(10) NOT NULL primary key,
CouName nvarchar(20) NOT NULL,
TeacherNo varchar(10) NOT NULL 
)
/* 分数表 / 学生选课分数表 */
create table Score(
StuNo varchar(10) NOT NULL,
CouNo varchar(10) NOT NULL,
Credit float NOT NULL,
constraint PK_Score primary key (StuNo,CouNo)
)

简单的外键关系，加不加无所谓

alter table course
add constraint FK_Course_Teacher foreign key (TeacherNo)
references Teacher(TeacherNo)
alter table Score
add constraint FK_Score_Student foreign key (StuNo)
references Student(StuNo)
alter table Score
add constraint FK_Score_Course foreign key (CouNo)
references Course(CouNo)

二、数据表记录

然后添加一些数据记录，方便做练习

学生表

/*** 初始化学生表 ***/
insert into Student values ('s001','张三',23,'男');
insert into Student values ('s002','李四',22,'男');
insert into Student values ('s003','吴鹏',22,'男');
insert into Student values ('s004','琴沁',20,'女');
insert into Student values ('s005','王丽',20,'女');
insert into Student values ('s006','李波',21,'男');
insert into Student values ('s007','刘玉',21,'男');
insert into Student values ('s008','萧蓉',21,'女');
insert into Student values ('s009','陈萧晓',23,'女');
insert into Student values ('s010','陈美',22,'女');

教师表

/*** 初始化教师表 ***/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '陈燕');
insert into teacher values ('t003', '胡明星');
insert into teacher values ('t004', '张顺');
insert into teacher values ('t005', '林超');

课程表

/*** 初始化课程表 **/
insert into course values ('c001','Phyton','t002');
insert into course values ('c002','Java','t002');
insert into course values ('c003','GO','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t004');
insert into course values ('c008','DIV+CSS','t004');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','.net core','t003');
insert into course values ('c011','Vue','t005');
insert into course values ('c012','Rust','t005');
insert into course values ('c013','c/c++','t003');

成绩表

/***  分数表 / 学生选课分数表 ***/
insert into Score values ('s001','c001',78.9);
insert into Score values ('s001','c002',82);
insert into Score values ('s001','c003',59);
insert into Score values ('s002','c001',72);
insert into Score values ('s002','c002',80.9);
insert into Score values ('s003','c001',88.9);
insert into Score values ('s003','c002',81.9);
insert into Score values ('s004','c001',60.9);
insert into Score values ('s005','c005',78.9);
insert into Score values ('s005','c006',80);
insert into Score values ('s005','c013',97);
insert into Score values ('s006','c004',60);
insert into Score values ('s006','c007',88.9);
insert into Score values ('s006','c008',72);
insert into Score values ('s006','c011',54);
insert into Score values ('s007','c002',72.9);
insert into Score values ('s007','c003',59);

三、练习

查询男生、女生人数。打印：性别，人数

select Sex as '性别', count(*) as '人数' from student group by Sex

查询姓“张”的学生名单。

select  '学生姓名'=StuName from Student where StuName like '张%'

统计各课程的学生数量：课程名，人数

select c.CouName as '课程','人数'=count(s.StuNo) 
from Course c
left join Score s
on c.CouNo=s.CouNo
group by s.CouNo,c.CouName,c.CouNo
order by c.CouNo asc

查询所有同学的选课数及总成绩,学号、姓名、选课数、总成绩

select s1.StuNo,s1.StuName,'选课数'=ISNULL(s2.选课数,0),'总成绩'=ISNULL(s2.总成绩,0) 
from Student s1
left join (
	select StuNo, '选课数'=COUNT(CouNo),'总成绩'=sum(Credit) from Score 
	group by stuno
) s2
on s1.StuNo=s2.StuNo

查询“c001”课程比“c002”课程成绩高的所有学生打印姓名学号

 select s1.StuName as '姓名',t1.StuNo as '学号' 
 from Score t1
 left join Student s1
 on t1.StuNo=s1.StuNo
 where CouNo='c001' and Credit>(
 	select Credit from Score t2 where CouNo='c002' and t1.StuNo=t2.StuNo
 )

select s1.StuName as '姓名',t1.StuNo as '学号' 
from (
	select a1.StuNo,'c001_credit'=a1.Credit,'c002_credit'=a2.Credit 
	from Score a1
	left join (select * from Score )a2
	on a1.stuno=a2.stuno
	where a1.couNo='c001' and a2.couNo='c002'
) t1
left join Student s1
on t1.StuNo=s1.StuNo
where t1.c001_credit>t1.c002_credit

查询平均成绩大于60 分的同学的学号姓名和平均成绩

select '姓名'=s1.StuName, '学号'=sc.StuNo,'平均成绩'=sc.Credit from Student s1
right join (
	select StuNo,'Credit'=avg(Credit) from Score 
	group by StuNo
	having AVG(Credit)>60
)sc
on s1.StuNo=sc.StuNo

查询姓“刘”的老师的人数；

select count(*) as '姓“刘”的老师的人数' from Teacher where TeacherName like '刘%'

查询学过“陈燕”老师课的同学的学号、姓名

select t1.StuNo, s1.StuName 
from Student s1
right join (
	select distinct c2.StuNo 
	from Course c1 
	left join (
		select StuNo,CouNo from Score group by StuNo,CouNo
	) c2
	on c1.CouNo= c2.CouNo
	where TeacherNo=(select TeacherNo from Teacher where TeacherName='陈燕')
) t1
on s1.StuNo=t1.StuNo

select t1.StuNo, s1.StuName 
from Student s1
right join (
	select  distinct StuNo 
	from Score 
	where CouNo in (
		select CouNo from Course where TeacherNo=(select TeacherNo from Teacher where TeacherName='陈燕')
	)
)t1
on s1.StuNo=t1.StuNo

查询没学过“陈燕”老师课的所有同学的学号、姓名；

select s1.StuNo, s1.StuName 
from Student s1
left join (
	select distinct c2.StuNo from Course c1 
	left join (
		select StuNo,CouNo from Score group by StuNo,CouNo
		) c2
	on c1.CouNo= c2.CouNo
	where TeacherNo=(select TeacherNo from Teacher where TeacherName='陈燕')
) t1
on s1.StuNo=t1.StuNo
where t1.StuNo is null

select stuno,stuname 
from Student s
where StuNo not in
(
	select stuNo from Score sc,
	(
		select t.TeacherNo,c.CouNo from Teacher t
 		left join Course c 
 		on t.TeacherNo=c.TeacherNo 
		where t.TeacherName='陈燕'
	)as b
	where sc.CouNo=b.CouNo
)

每位老师课程占比 (课程表中每位老师的课所占比例)

select a.TeacherName ,'课程占比'=CONVERT(decimal(4,2),a.课程数)/CONVERT(decimal(4,2),b.总数) from (
select t1.TeacherName,t2.课程数 from Teacher t1
left join (
select TeacherNo,'课程数'=count(CouNo) from Course 
group by TeacherNo
)t2
on t1.TeacherNo=t2.TeacherNo

)as a,(select '总数'=count(CouNo) from Course ) as b

查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

select a.StuName,b.StuNo from Student as a
right join(
	select t1.StuNo from Score t1
	inner join (
			select StuNo from Score  where CouNo='c002'
			)t2
	on t1.StuNo=t2.StuNo
	where t1.CouNo='c001'
) as b
on a.StuNo=b.StuNo

查询所有课程成绩小于60 分的同学的学号、姓名

select b.StuNo,s.StuName 
from Student s
right join(
	select sc.Stuno ,' s'=AVG(credit) from Score sc
group by sc.StuNo
having avg(Credit)<60
)as b
on s.StuNo=b.StuNo

查询没有学全课程表中所有课的同学的学号、姓名

select st.StuName,st.StuNo 
from Student st
where st.StuNo not in(
	select StuNo 
	from Score s
	group by StuNo
	having count(couno)=(select count(*) from Course) 
)

select st.StuNo,st.StuName,'课程数'=count(s.couno) from student st 
left join Score s
on st.StuNo=s.StuNo
group by st.StuNo,st.StuName
having count(s.couno)<(select '总课程数'=count(*) from Course )

查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名

select st.StuName,t1.StuNo from Student st 
right join (
	select distinct StuNo from Score  
	where StuNo<>'s001'
	and CouNo in(
		select CouNo from Score where StuNo='s001'
	)
) t1
on st.StuNo=t1.StuNo

select distinct b.StuNo,st.StuName from Score s
inner join(
	select Stuno,CouNo from Score where StuNo!='s001'
)as b
on s.CouNo=b.CouNo and s.StuNo='s001' 
left join Student st 
on b.StuNo=st.StuNo

查询课程表中各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select c.CouName,t1.最高分,t1.最低分 from Course  c
left join (
	select CouNo, '最高分'=MAX(Credit),'最低分'=min(Credit) from Score
	group by CouNo
) t1
on c.CouNo=t1.CouNo

Score表中按各科成绩及格率从高到低顺序(70 及格)

select s1.CouNo, '及格率'=CONVERT(float, ISNULL( s2.及格人数,0))/count(s1.Stuno)
from Score s1
left join (
	select CouNo, count(Stuno) as '及格人数'  from Score
	where Credit>=70
	group by CouNo
) s2
on s1.CouNo=s2.CouNo
group by s1.CouNo,s2.及格人数
order by 及格率 desc

select CouNo,convert(float,sum(case when Credit>=70 then 1 else 0 end))/count(Credit) as '及格率'
from Score 
group by CouNo
order by 及格率 desc

查看学生c001 c002 c003 课程成绩。打印：学生姓名 C001 C002 C003

select st.StuName as '学生姓名',sc.c001,sc.c002,sc.c003 from student st
left join (
	select StuNo,sum(case when CouNo='c001' then Credit else null end) as 'c001',
		sum(case when CouNo='c002' then Credit else null end ) as  'c002',
		sum(case when CouNo='c003' then Credit else null end) as 'c003'
		from Score
 	group by StuNo
 ) sc
on st.StuNo=sc.StuNo

统计列印各科成绩,各分数段人数。
打印：课程ID,课程名称,[100-85],(85-70],(70-60],[ <60]

select c.CouNo, c.CouName,t1.* from Course c
left join (
select CouNo, sum(case when Credit>=85 then 1 else 0 end) as '100-85',
	   sum(case when Credit>=70 and Credit<85 then 1 else 0 end) as '85-10',
	   sum(case when Credit>=60 and Credit<70 then 1 else 0 end) as '70-60',
	   sum(case when Credit<60  then 1 else 0 end) as '<60'
from Score
group by CouNo
)t1
on c.CouNo=t1.CouNo

查询不同老师所教不同课程平均分从高到低显示

select t.TeacherName ,t1.CouName,t1.平均分 from Teacher t
left join (
	select c.CouName,c.TeacherNo,s.平均分 from Course c
	left join (
		select CouNo,'平均分'=avg(credit) from Score
		group by CouNo
	) s
	on c.CouNo=s.CouNo
) t1
on t.TeacherNo=t1.TeacherNo
order by t1.平均分 desc

查询分数表中每门功课成绩最好的前两名

select * from (
	select *,rank()over(partition by couno order by credit desc) as num from Score
)as a 
where num<3

查询各科成绩前三名的记录:(不考虑成绩并列情况) 课程名称,学生,成绩,排名,

select t1.CouName,st.StuName,t2.Credit,t2.rn from Course t1
left join(
	select * from (
		select StuNo,CouNo,Credit,ROW_NUMBER() over(partition by CouNo order by credit desc) as 'rn' 
		from Score
	)sc
	where sc.rn<4
) t2 
on t1.CouNo=t2.CouNo
left join Student st
on t2.StuNo=st.StuNo
where t2.rn<4