|
網(wǎng)上流傳較廣的50道SQL訓(xùn)練，奮斗了不知道多久終于寫(xiě)完了,。前18道題的難度依次遞增,，從19題開(kāi)始的后半部分算是循環(huán)練習(xí)和額外function的附加練習(xí),，難度恢復(fù)到普通狀態(tài),。
第9題非常難,，我反正沒(méi)有寫(xiě)出來(lái),，如果有寫(xiě)出來(lái)了的朋友還請(qǐng)賜教,。
這50道里面自認(rèn)為應(yīng)該沒(méi)有太多錯(cuò)誤，而且盡可能使用了最簡(jiǎn)單或是最直接的查詢,，有多種不相上下解法的題目我也都列出了,，但也歡迎一起學(xué)習(xí)的朋友進(jìn)行討論和解法優(yōu)化啊~
數(shù)據(jù)表介紹
--1.學(xué)生表
Student(SId,Sname,Sage,Ssex)
--SId 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別
--2.課程表
Course(CId,Cname,TId)
--CId 課程編號(hào),Cname 課程名稱,TId 教師編號(hào)
--3.教師表
Teacher(TId,Tname)
--TId 教師編號(hào),Tname 教師姓名
--4.成績(jī)表
SC(SId,CId,score)
--SId 學(xué)生編號(hào),CId 課程編號(hào),score 分?jǐn)?shù)
學(xué)生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢(qián)電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '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' , '女');
科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '語(yǔ)文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語(yǔ)' , '03');
教師表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
成績(jī)表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
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);
練習(xí)題目
- 查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
1.1 查詢同時(shí)存在" 01 "課程和" 02 "課程的情況
1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為 null )
1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況
查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī) 查詢?cè)?SC 表存在成績(jī)的學(xué)生信息 查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名,、選課總數(shù),、所有課程的總成績(jī)(沒(méi)成績(jī)的顯示為 null )
4.1 查有成績(jī)的學(xué)生信息
查詢「李」姓老師的數(shù)量 查詢學(xué)過(guò)「張三」老師授課的同學(xué)的信息 查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息 查詢至少有一門(mén)課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息 查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程 完全相同的其他同學(xué)的信息 查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名 查詢兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī) 檢索" 01 "課程分?jǐn)?shù)小于 60,，按分?jǐn)?shù)降序排列的學(xué)生信息 按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī) 查詢各科成績(jī)最高分,、最低分和平均分：
以如下形式顯示：課程 ID，課程 name,，最高分,，最低分，平均分,，及格率,，中等率，優(yōu)良率,，優(yōu)秀率
及格為>=60,，中等為：70-80，優(yōu)良為：80-90,，優(yōu)秀為：>=90
要求輸出課程號(hào)和選修人數(shù),，查詢結(jié)果按人數(shù)降序排列,，若人數(shù)相同，按課程號(hào)升序排列
- 按各科成績(jī)進(jìn)行排序,，并顯示排名,， Score 重復(fù)時(shí)保留名次空缺
15.1 按各科成績(jī)進(jìn)行排序，并顯示排名,， Score 重復(fù)時(shí)合并名次
- 查詢學(xué)生的總成績(jī),，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺
16.1 查詢學(xué)生的總成績(jī),，并進(jìn)行排名,，總分重復(fù)時(shí)不保留名次空缺
統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱,，[100-85],，[85-70]，[70-60],，[60-0] 及所占百分比 查詢各科成績(jī)前三名的記錄 查詢每門(mén)課程被選修的學(xué)生數(shù) 查詢出只選修兩門(mén)課程的學(xué)生學(xué)號(hào)和姓名 查詢男生,、女生人數(shù) 查詢名字中含有「風(fēng)」字的學(xué)生信息 查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù) 查詢 1990 年出生的學(xué)生名單 查詢每門(mén)課程的平均成績(jī),，結(jié)果按平均成績(jī)降序排列,，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列 查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào),、姓名和平均成績(jī) 查詢課程名稱為「數(shù)學(xué)」,，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù) 查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒(méi)成績(jī)，沒(méi)選課的情況） 查詢?nèi)魏我婚T(mén)課程成績(jī)?cè)?70 分以上的姓名,、課程名稱和分?jǐn)?shù) 查詢不及格的課程 查詢課程編號(hào)為 01 且課程成績(jī)?cè)?80 分以上的學(xué)生的學(xué)號(hào)和姓名 求每門(mén)課程的學(xué)生人數(shù) 成績(jī)不重復(fù),，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī) 成績(jī)有重復(fù)的情況下,，查詢選修「張三」老師所授課程的學(xué)生中,，成績(jī)最高的學(xué)生信息及其成績(jī) 查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào),、學(xué)生成績(jī) 查詢每門(mén)功成績(jī)最好的前兩名 統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）,。 檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào) 查詢選修了全部課程的學(xué)生信息 查詢各學(xué)生的年齡，只按年份來(lái)算 按照出生日期來(lái)算,，當(dāng)前月日 < 出生年月的月日則,，年齡減一 查詢本周過(guò)生日的學(xué)生 查詢下周過(guò)生日的學(xué)生 查詢本月過(guò)生日的學(xué)生 查詢下月過(guò)生日的學(xué)生
答案
1.查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
因?yàn)樾枰康膶W(xué)生信息，則需要在sc表中得到符合條件的SId后與student表進(jìn)行join,，可以left join 也可以 right join
select * from Student RIGHT JOIN (
select t1.SId, class1, class2 from
(select SId, score as class1 from sc where sc.CId = '01')as t1,
(select SId, score as class2 from sc where sc.CId = '02')as t2
where t1.SId = t2.SId AND t1.class1 > t2.class2
)r
on Student.SId = r.SId;
select * from (
select t1.SId, class1, class2
from
(SELECT SId, score as class1 FROM sc WHERE sc.CId = '01') AS t1,
(SELECT SId, score as class2 FROM sc WHERE sc.CId = '02') AS t2
where t1.SId = t2.SId and t1.class1 > t2.class2
) r
LEFT JOIN Student
ON Student.SId = r.SId;
1.1 查詢同時(shí)存在" 01 "課程和" 02 "課程的情況
select * from
(select * from sc where sc.CId = '01') as t1,
(select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;
1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為 null )
這一道就是明顯需要使用join的情況了,，02可能不存在，即為left join的右側(cè)或right join 的左側(cè)即可.
select * from
(select * from sc where sc.CId = '01') as t1
left join
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;
select * from
(select * from sc where sc.CId = '02') as t2
right join
(select * from sc where sc.CId = '01') as t1
on t1.SId = t2.SId;
1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況
select * from sc
where sc.SId not in (
select SId from sc
where sc.CId = '01'
)
AND sc.CId= '02';
- 查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
這里只用根據(jù)學(xué)生ID把成績(jī)分組，對(duì)分組中的score求平均值,，最后在選取結(jié)果中AVG大于60的即可. 注意,，這里必須要給計(jì)算得到的AVG結(jié)果一個(gè)alias.（AS ss）
得到學(xué)生信息的時(shí)候既可以用join也可以用一般的聯(lián)合搜索
select student.SId,sname,ss from student,(
select SId, AVG(score) as ss from sc
GROUP BY SId
HAVING AVG(score)> 60
)r
where student.sid = r.sid;
select Student.SId, Student.Sname, r.ss from Student right join(
select SId, AVG(score) AS ss from sc
GROUP BY SId
HAVING AVG(score)> 60
)r on Student.SId = r.SId;
select s.SId,ss,Sname from(
select SId, AVG(score) as ss from sc
GROUP BY SId
HAVING AVG(score)> 60
)r left join
(select Student.SId, Student.Sname from
Student)s on s.SId = r.SId;
- 查詢?cè)?SC 表存在成績(jī)的學(xué)生信息
select DISTINCT student.*
from student,sc
where student.SId=sc.SId
4.查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名,、選課總數(shù),、所有課程的成績(jī)總和
聯(lián)合查詢不會(huì)顯示沒(méi)選課的學(xué)生：
select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc
group by sc.sid)r
where student.sid = r.sid;
如要顯示沒(méi)選課的學(xué)生(顯示為NULL)，需要使用join:
select s.sid, s.sname,r.coursenumber,r.scoresum
from (
(select student.sid,student.sname
from student
)s
left join
(select
sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber
from sc
group by sc.sid
)r
on s.sid = r.sid
);
4.1 查有成績(jī)的學(xué)生信息
這一題涉及到in和exists的用法,，在這種小表中,，兩種方法的效率都差不多,，但是請(qǐng)參考SQL查詢中in和exists的區(qū)別分析
當(dāng)表2的記錄數(shù)量非常大的時(shí)候,，選用exists比in要高效很多.
EXISTS用于檢查子查詢是否至少會(huì)返回一行數(shù)據(jù)，該子查詢實(shí)際上并不返回任何數(shù)據(jù),，而是返回值True或False.
結(jié)論：IN()適合B表比A表數(shù)據(jù)小的情況
結(jié)論：EXISTS()適合B表比A表數(shù)據(jù)大的情況
select * from student
where exists (select sc.sid from sc where student.sid = sc.sid);
select * from student
where student.sid in (select sc.sid from sc);
- 查詢「李」姓老師的數(shù)量
select count(*)
from teacher
where tname like '李%';
- 查詢學(xué)過(guò)「張三」老師授課的同學(xué)的信息
多表聯(lián)合查詢
select student.* from student,teacher,course,sc
where
student.sid = sc.sid
and course.cid=sc.cid
and course.tid = teacher.tid
and tname = '張三';
- 查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息
因?yàn)橛袑W(xué)生什么課都沒(méi)有選,，反向思考，先查詢選了所有課的學(xué)生,，再選擇這些人之外的學(xué)生.
select * from student
where student.sid not in (
select sc.sid from sc
group by sc.sid
having count(sc.cid)= (select count(cid) from course)
);
- 查詢至少有一門(mén)課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息
這個(gè)用聯(lián)合查詢也可以,，但是邏輯不清楚，我覺(jué)得較為清楚的邏輯是這樣的：從sc表查詢01同學(xué)的所有選課cid--從sc表查詢所有同學(xué)的sid如果其cid在前面的結(jié)果中--從student表查詢所有學(xué)生信息如果sid在前面的結(jié)果中
select * from student
where student.sid in (
select sc.sid from sc
where sc.cid in(
select sc.cid from sc
where sc.sid = '01'
)
);
9.查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
不會(huì)做,。
10.查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名
仍然還是嵌套,，三層嵌套， 或者多表聯(lián)合查詢
select * from student
where student.sid not in(
select sc.sid from sc where sc.cid in(
select course.cid from course where course.tid in(
select teacher.tid from teacher where tname = "張三"
)
)
);
select * from student
where student.sid not in(
select sc.sid from sc,course,teacher
where
sc.cid = course.cid
and course.tid = teacher.tid
and teacher.tname= "張三"
);
11.查詢兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào),，姓名及其平均成績(jī)
從SC表中選取score小于60的,，并group by sid，having count 大于1
(更新采用評(píng)論1中的解法)
select student.SId, student.Sname,b.avg
from student RIGHT JOIN
(select sid, AVG(score) as avg from sc
where sid in (
select sid from sc
where score<60
GROUP BY sid
HAVING count(score)>1)
GROUP BY sid) b on student.sid=b.sid;
- 檢索" 01 "課程分?jǐn)?shù)小于 60,，按分?jǐn)?shù)降序排列的學(xué)生信息
雙表聯(lián)合查詢,，在查詢最后可以設(shè)置排序方式，語(yǔ)法為ORDER BY ***** DESC\ASC;
select student.*, sc.score from student, sc
where student.sid = sc.sid
and sc.score < 60
and cid = "01"
ORDER BY sc.score DESC;
- 按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
select * from sc
left join (
select sid,avg(score) as avscore from sc
group by sid
)r
on sc.sid = r.sid
order by avscore desc;
- 查詢各科成績(jī)最高分,、最低分和平均分：
以如下形式顯示：課程 ID,，課程 name，最高分,，最低分,，平均分，及格率,，中等率,，優(yōu)良率，優(yōu)秀率
及格為>=60,，中等為：70-80,，優(yōu)良為：80-90，優(yōu)秀為：>=90
要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列,，若人數(shù)相同,，按課程號(hào)升序排列
select
sc.CId ,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 選修人數(shù),
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 優(yōu)良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 優(yōu)秀率
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC
- 按各科成績(jī)進(jìn)行排序，并顯示排名,， Score 重復(fù)時(shí)保留名次空缺
這一道題有點(diǎn)tricky,，可以用變量，但也有更為簡(jiǎn)單的方法,，即自交（左交）
用sc中的score和自己進(jìn)行對(duì)比,，來(lái)計(jì)算“比當(dāng)前分?jǐn)?shù)高的分?jǐn)?shù)有幾個(gè)”。
select a.cid, a.sid, a.score, count(b.score)+1 as rank
from sc as a
left join sc as b
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;
- 查詢學(xué)生的總成績(jī),，并進(jìn)行排名,，總分重復(fù)時(shí)不保留名次空缺
這里主要學(xué)習(xí)一下使用變量。在SQL里面變量用@來(lái)標(biāo)識(shí),。
set @crank=0;
select q.sid, total, @crank := @crank +1 as rank from(
select sc.sid, sum(sc.score) as total from sc
group by sc.sid
order by total desc)q;
- 統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),，課程名稱，[100-85],，[85-70],，[70-60]，[60-0] 及所占百分比
有時(shí)候覺(jué)得自己真是死腦筋,。group by以后的查詢結(jié)果無(wú)法使用別名,，所以不要想著先單表group by計(jì)算出結(jié)果再?gòu)牡诙埍砝锾砩险n程信息，而應(yīng)該先將兩張表join在一起得到所有想要的屬性再對(duì)這張總表進(jìn)行統(tǒng)計(jì)計(jì)算,。這里就不算百分比了,，道理相同。
注意一下,，用case when 返回1 以后的統(tǒng)計(jì)不是用count而是sum
select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid;
- 查詢各科成績(jī)前三名的記錄
大坑比,。mysql不能group by 了以后取limit，所以不要想著討巧了,，我快被這一題氣死了,。思路有兩種，第一種比較暴力,，計(jì)算比自己分?jǐn)?shù)大的記錄有幾條,，如果小于3 就select，因?yàn)閷?duì)前三名來(lái)說(shuō)不會(huì)有3個(gè)及以上的分?jǐn)?shù)比自己大了,，最后再對(duì)所有select到的結(jié)果按照分?jǐn)?shù)和課程編號(hào)排名即可,。
select * from sc
where (
select count(*) from sc as a
where sc.cid = a.cid and sc.score<a.score
)< 3
order by cid asc, sc.score desc;
第二種比較靈巧一些，用自身左交,，但是有點(diǎn)難以理解,。
先用自己交自己,，條件為a.cid = b.cid and a.score<b.score，其實(shí)就是列出同一門(mén)課內(nèi)所有分?jǐn)?shù)比較的情況,。
想要查看完整的表可以
select * from sc a
left join sc b on a.cid = b.cid and a.score<b.score
order by a.cid,a.score;
查看,，發(fā)現(xiàn)結(jié)果是47行的一個(gè)表，列出了類似 01號(hào)課里“30分小于50,，也小于70,，也小于80，也小于90”“50分小于70,，小于80,，小于90”.....
所以理論上，對(duì)任何一門(mén)課來(lái)說(shuō),，分?jǐn)?shù)最高的那三個(gè)記錄,，在這張大表里，通過(guò)a.sid和a.cid可以聯(lián)合確定這個(gè)同學(xué)的這門(mén)課的這個(gè)分?jǐn)?shù)究竟比多少個(gè)其他記錄高/低,，
如果這個(gè)特定的a.sid和a.cid組合出現(xiàn)在這張表里的次數(shù)少于3個(gè),，那就意味著這個(gè)組合（學(xué)號(hào)+課號(hào)+分?jǐn)?shù)）是這門(mén)課里排名前三的,。
所以下面這個(gè)計(jì)算中having count 部分其實(shí)count()或者任意其他列都可以,，這里制定了一個(gè)列只是因?yàn)楸萩ount()運(yùn)行速度上更快。
select a.sid,a.cid,a.score from sc a
left join sc b on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<3
order by a.cid;
- 查詢每門(mén)課程被選修的學(xué)生數(shù)
select cid, count(sid) from sc
group by cid;
- 查詢出只選修兩門(mén)課程的學(xué)生學(xué)號(hào)和姓名
嵌套查詢
select student.sid, student.sname from student
where student.sid in
(select sc.sid from sc
group by sc.sid
having count(sc.cid)=2
);
聯(lián)合查詢
select student.SId,student.Sname
from sc,student
where student.SId=sc.SId
GROUP BY sc.SId
HAVING count(*)=2,；
21.查詢男生,、女生人數(shù)
select ssex, count(*) from student
group by ssex;
- 查詢名字中含有「風(fēng)」字的學(xué)生信息
select *
from student
where student.Sname like '%風(fēng)%'
23.查詢同名學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
找到同名的名字并統(tǒng)計(jì)個(gè)數(shù)
select sname, count(*) from student
group by sname
having count(*)>1;
嵌套查詢列出同名的全部學(xué)生的信息
select * from student
where sname in (
select sname from student
group by sname
having count(*)>1
);
24.查詢 1990 年出生的學(xué)生名單
select *
from student
where YEAR(student.Sage)=1990;
25.查詢每門(mén)課程的平均成績(jī),，結(jié)果按平均成績(jī)降序排列,，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列
select sc.cid, course.cname, AVG(SC.SCORE) as average from sc, course
where sc.cid = course.cid
group by sc.cid
order by average desc,cid asc;
26.查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào),、姓名和平均成績(jī)
having也可以用來(lái)截取結(jié)果表,，在這里就先得到平均成績(jī)總表，再截取AVG大于85的即可.
select student.sid, student.sname, AVG(sc.score) as aver from student, sc
where student.sid = sc.sid
group by sc.sid
having aver > 85;
- 查詢課程名稱為「數(shù)學(xué)」,，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)
select student.sname, sc.score from student, sc, course
where student.sid = sc.sid
and course.cid = sc.cid
and course.cname = "數(shù)學(xué)"
and sc.score < 60;
- 查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒(méi)成績(jī),，沒(méi)選課的情況）
select student.sname, cid, score from student
left join sc
on student.sid = sc.sid;
- 查詢?nèi)魏我婚T(mén)課程成績(jī)?cè)?70 分以上的姓名、課程名稱和分?jǐn)?shù)
select student.sname, course.cname,sc.score from student,course,sc
where sc.score>70
and student.sid = sc.sid
and sc.cid = course.cid;
30.查詢存在不及格的課程
可以用group by 來(lái)取唯一,，也可以用distinct
select cid from sc
where score< 60
group by cid;
select DISTINCT sc.CId
from sc
where sc.score <60;
31.查詢課程編號(hào)為 01 且課程成績(jī)?cè)?80 分及以上的學(xué)生的學(xué)號(hào)和姓名
select student.sid,student.sname
from student,sc
where cid="01"
and score>=80
and student.sid = sc.sid;
- 求每門(mén)課程的學(xué)生人數(shù)
select sc.CId,count(*) as 學(xué)生人數(shù)
from sc
GROUP BY sc.CId;
- 成績(jī)不重復(fù),，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
用having max()理論上也是對(duì)的,，但是下面那種按分?jǐn)?shù)排序然后取limit 1的更直觀可靠
select student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "張三"
having max(sc.score);
select student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "張三"
order by score desc
limit 1;
- 成績(jī)有重復(fù)的情況下,，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
為了驗(yàn)證這一題,，先修改原始數(shù)據(jù)
UPDATE sc SET score=90
where sid = "07"
and cid ="02";
這樣張三老師教的02號(hào)課就有兩個(gè)學(xué)生同時(shí)獲得90的最高分了,。
這道題的思路繼續(xù)上一題,，我們已經(jīng)查詢到了符合限定條件的最高分了，這個(gè)時(shí)候只用比較這張表,，找到全部score等于這個(gè)最高分的記錄就可,，看起來(lái)有點(diǎn)繁復(fù)。
select student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "張三"
and sc.score = (
select Max(sc.score)
from sc,student, teacher, course
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "張三"
);
- 查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào),、課程編號(hào),、學(xué)生成績(jī)
同上，在這里用了inner join后會(huì)有概念是重復(fù)的記錄：“01 課與 03課”=“03 課與 01 課”,，所以這里取唯一可以直接用group by
select a.cid, a.sid, a.score from sc as a
inner join
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
group by cid, sid;
36.查詢每門(mén)功成績(jī)最好的前兩名
同上19題
select a.sid,a.cid,a.score from sc as a
left join sc as b
on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<2
order by a.cid;
37.統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）
select sc.cid, count(sid) as cc from sc
group by cid
having cc >5;
38.檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)
select sid, count(cid) as cc from sc
group by sid
having cc>=2;
- 查詢選修了全部課程的學(xué)生信息
select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
HAVING count(*) = (select DISTINCT count(*) from course )
40.查詢各學(xué)生的年齡,，只按年份來(lái)算
不想做，一般都用41題的方法精確到天
- 按照出生日期來(lái)算,，當(dāng)前月日 < 出生年月的月日則,，年齡減一
select student.SId as 學(xué)生編號(hào),student.Sname as 學(xué)生姓名,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 學(xué)生年齡
from student
42.查詢本周過(guò)生日的學(xué)生
select *
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());
- 查詢下周過(guò)生日的學(xué)生
select *
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;
44.查詢本月過(guò)生日的學(xué)生
select *
from student
where MONTH(student.Sage)=MONTH(CURDATE());
45.查詢下月過(guò)生日的學(xué)生
select *
from student
where MONTH(student.Sage)=MONTH(CURDATE())+1;
|
