常見面試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