常见面试SQL问题

常见面试SQL题

Student (sbo,sname,sage,ssex) 学生表
sno:学号;sname:学生姓名;sage:学生年龄;ssex:学生性别
Course(cno,cname,tno)课程表
cno:课程编号;canem:课程名字;tno:教师编号
Scores(sno,cno,score)成绩表
sno:学号;cno:课程编号;score:成绩
Teacher(tno,tname)教师表
tno:教师编号;tname:教师名字

1 学生表

//创建学生表并添加数据
Create table Student(
	sno int ,				--学号
	sname nvarchar(200),	--姓名
	sage int,				--年龄
	ssex nvarchar(20)		--性别
)
//插入学生表10条数据
insert into Student(sno,sname,sage,ssex) values(1,'张三',24,'male')
insert into Student(sno,sname,sage,ssex) values(2,'李四',26,'male')
insert into Student(sno,sname,sage,ssex) values(3,'王五',28,'male')
insert into Student(sno,sname,sage,ssex) values(4,'赵六',30,'male')
insert into Student(sno,sname,sage,ssex) values(5,'夜华',28,'male')
insert into Student(sno,sname,sage,ssex) values(6,'白浅',90,'female')
insert into Student(sno,sname,sage,ssex) values(7,'扶摇',18,'female')
insert into Student(sno,sname,sage,ssex) values(8,'无极',30,'male')
insert into Student(sno,sname,sage,ssex) values(9,'王菊',30,'female')
insert into Student(sno,sname,sage,ssex) values(10,'杨超越',20,'female')

2 课程表

//创建课程表并添加数据
Create table Course(
	cno int ,				--课程编号
	cname nvarchar(200),	--课程名字
	tno nvarchar(200)		--教师编号
)
//drop table Course 
//插入课程表9条数据
insert into Course(cno,cname,tno) values(1001,'english','TS01')
insert into Course(cno,cname,tno) values(1002,'math','TS09')
insert into Course(cno,cname,tno) values(1003,'art','TS07')
insert into Course(cno,cname,tno) values(1004,'dance','TS08')
insert into Course(cno,cname,tno) values(1005,'physic','TS03')
insert into Course(cno,cname,tno) values(1006,'chemistry','TS02')
insert into Course(cno,cname,tno) values(1007,'paint','TS06')
insert into Course(cno,cname,tno) values(1008,'panio','TS05')
insert into Course(cno,cname,tno) values(1009,'couputer','TS04')

3 成绩表

//创建成绩表并添加数据
Create table Scores(
	sno int,				--学号
	cno int,				--课程编号
	score int				--成绩
)
//插入成绩表27条数据
insert into Scores(sno,cno,score) values(1,1001,90)
insert into Scores(sno,cno,score) values(1,1002,94)
insert into Scores(sno,cno,score) values(1,1003,96)
insert into Scores(sno,cno,score) values(1,1004,98)
insert into Scores(sno,cno,score) values(1,1005,91)
insert into Scores(sno,cno,score) values(1,1006,95)
insert into Scores(sno,cno,score) values(1,1007,97)
insert into Scores(sno,cno,score) values(1,1008,92)
insert into Scores(sno,cno,score) values(1,1009,100)
insert into Scores(sno,cno,score) values(2,1001,91)
insert into Scores(sno,cno,score) values(2,1002,92)
insert into Scores(sno,cno,score) values(2,1003,97)
insert into Scores(sno,cno,score) values(2,1004,99)
insert into Scores(sno,cno,score) values(2,1005,92)
insert into Scores(sno,cno,score) values(2,1006,96)
insert into Scores(sno,cno,score) values(2,1007,98)
insert into Scores(sno,cno,score) values(2,1008,93)
insert into Scores(sno,cno,score) values(2,1009,99)
insert into Scores(sno,cno,score) values(3,1001,89)
insert into Scores(sno,cno,score) values(3,1002,93)
insert into Scores(sno,cno,score) values(3,1003,95)
insert into Scores(sno,cno,score) values(3,1004,97)
insert into Scores(sno,cno,score) values(3,1005,90)
insert into Scores(sno,cno,score) values(3,1006,94)
insert into Scores(sno,cno,score) values(3,1007,96)
insert into Scores(sno,cno,score) values(3,1008,91)
insert into Scores(sno,cno,score) values(3,1009,97)

4 教师表

//创建教师表并添加数据
Create table Teacher(
	tno nvarchar(200),		--教师编号
	tname nvarchar(200)		--教师名字
)
//插入教师表9条数据
insert into Teacher(tno,tname) values('TS01','何炅')
insert into Teacher(tno,tname) values('TS02','墨洲')
insert into Teacher(tno,tname) values('TS03','汪涵')
insert into Teacher(tno,tname) values('TS04','悟空')
insert into Teacher(tno,tname) values('TS05','唐僧')
insert into Teacher(tno,tname) values('TS06','陶渊明')
insert into Teacher(tno,tname) values('TS07','翟天临')
insert into Teacher(tno,tname) values('TS08','孙俪')
insert into Teacher(tno,tname) values('TS09','刘奕君')

问题:

1、查询"1001"课程比"1002"课程成绩高的所有学生的学号

//1、查询"1001"课程比"1002"课程成绩高的所有学生的学号
select c.sno 'sno',a.score '1001',b.score '1002',c.sname from 
(select sno,score from Scores  where cno=1001) a
inner join (select sno,score from Scores  where cno=1002) b on a.sno=b.sno
inner join Student c on c.sno=a.sno
where a.score>b.score
//查询单个课程学生编号,成绩
select sno,score from Scores  where cno=1001
select sno,score from Scores  where cno=1002

2、查询平均成绩大约60分的同学的学号和平均成绩

//2、查询平均成绩大约60分的同学的学号和平均成绩
select sno,AVG(score) from Scores 
group by sno having avg(score)>60 

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

//3、查询所有同学的学号、姓名、选课数、总成绩;
select a.sno,a.sname,COUNT(b.cno),sum(b.score) from Student a
left join Scores b on b.sno=a.sno
group by a.sno,a.sname
//方法2
select b.sno,b.sname,COUNT(a.cno),sum(case when  a.score is null then 0 else a.score end) from Scores a
right join  Student b on b.sno=a.sno
group by b.sno,b.sname
//方法3
select a.sno,a.sname,COUNT(b.cno),sum(case when  b.score is null then 0 else b.score end) from Student a
left join  Scores b on b.sno=a.sno
group by a.sno,a.sname

4、查询姓"悟"的老师的个数

//4、查询姓"悟"的老师的个数;
select count(a.tname ) from Teacher a where a.tname like '%悟%'

拓展:查个数并含有名字

//查个数并含有名字
select a.tname ,count(1) from Teacher a
group by a.tname having a.tname like '%悟%'

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

//5、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select b.sname,c.cname,a.score from Scores a
left join Student b on a.sno=b.sno
left join Course c on a.cno=c.cno
where a.score>70

6、检索"1004"课程分数小于60分,按分数降序

//6、检索"1004"课程分数小于60分,按分数降序
//desc 降序  esc 升序  
//select  * from 表名 a where a.字段1<60 and a.字段2='1004' order by a.字段1    //默认升序排列
select  * from Scores a where a.score<60 and a.cno='1004' order by a.score desc

7、删除"2"同学的"1007"课程成绩

//7、删除"2"同学的"1007"课程成绩
//先查后删
//select * from Scores a where a.sno=2 and a.cno=1007
delete a from Scores a where a.sno=2 and a.cno=1007

8、删除学习"叶平"老师课的Scores表记录

//8、删除学习"叶平"老师课的Scores表记录
//先查后删
//select *from Scores a  where a.cno =(select cno from Course where tno=(select tno from Teacher where tname='叶平') )
delete a from Scores a  where a.cno =(select cno from Course where tno=(select tno from Teacher where tname='叶平') )
//select tno from Teacher a where tname='悟空'

update date 2020-10-26

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页