好像今天圣誕節(jié)
剛刷完SQL練習(xí)【SQL經(jīng)典練習(xí)題】颁湖，本篇文章將對(duì)我不牢固的知識(shí)做簡(jiǎn)單匯總宣蠕。
沒對(duì)比就沒標(biāo)準(zhǔn)，當(dāng)練習(xí)超經(jīng)典SQL練習(xí)題甥捺，做完這些你的ＳＱＬ就過關(guān)了時(shí)才知道自己以前練習(xí)的SQL是最基礎(chǔ)的內(nèi)容抢蚀。至于文章內(nèi)容這里不做描述，感興趣可以自己練習(xí)镰禾。
#20.查詢scores中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績(jī)的記錄皿曲。##
select * from scores group by sno
having count(cno) >1
and
degree != max(degree) ;
#21.查詢成績(jī)高于學(xué)號(hào)為“109”、課程號(hào)為“3-105”的成績(jī)的所有記錄吴侦。
select * from scores
group by cno
having cno = 3105 and degree >
(select degree from scores where sno = 109 and cno = 3105);
#22.查詢和學(xué)號(hào)為108的同學(xué)同年出生的所有學(xué)生的Sno屋休、Sname和Sbirthday列。
select sno,sname,sbirthday from students
where sbirthday =
(select sbirthday from students where sno = 108);
#24.查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名备韧。為什么是模棱兩可的博投？為什么分組直接用cno不行，需要用a.cno或者b.cn
select * from
(select * from coursers) a
inner join
(select * from scores) b
on a.cno = b.cno
inner join
(select * from teachers) c
on a.tno = c.tno
group by a.cno
having count(sno) > 5;
24給我的教訓(xùn)在表連接的時(shí)候在做條件選擇和查詢的時(shí)候指定列名出自哪張表
#26.查詢存在有85分以上成績(jī)的課程Cno.
我的代碼：
select cno,degree from scores
group by cno
having max(degree) > 85;
題主代碼：
SELECT distinct Cno
FROM Scores
WHERE Degree>85;
26絕對(duì)是自己的邏輯問題盯蝴，為什么要按課程分組直接篩選大于85分成績(jī)的同學(xué)的課程號(hào)就可以啦毅哗。看見distinct可能是天真的我想用group by 選擇有幾個(gè)cno吧
邏輯問題捧挺！
#28.查詢“計(jì)算機(jī)系”與“電子工程系“不同職稱的教師的Tname和Prof虑绵。
（我沒有理解到題意……笨死……）
select tname,prof from teachers
where depart = '計(jì)算機(jī)系' and prof not in
(select distinct prof from teachers where depart = '電子工程系');
#29.查詢選修編號(hào)為“3-105“課程且成績(jī)至少高于任意選修編號(hào)為“3-245”的同學(xué)的成績(jī)的Cno、Sno和Degree,并按Degree從高到低次序排序闽烙。
我的代碼：
select cno,sno,degree from scores
where cno = 3105 and degree >
(select min(degree) from scores where cno = 3245)
order by degree desc;
題主代碼：
SELECT Cno,Sno,Degree
FROM Scores
WHERE Cno='3-105' AND Degree > ANY(SELECT Degree
FROM Scores
WHERE Cno='3-245')
ORDER BY Degree DESC;
29任意不能用最小值嗎不能用min嗎翅睛？第一次見any,方法自己還是會(huì)的，可能有些函數(shù)用的少
#31.查詢所有教師和同學(xué)的name黑竞、sex和birthday.
我的代碼：（感覺好可愛的自己）
select tname,tsex,tbirthday,sname,ssex,sbirthday from
(select * from teachers) a
inner join
(select * from coursers) b
on a.tno = b.tno
inner join
(select * from scores) c
on c.cno = b.cno
inner join
(select * from students) d
on d.sno = c.sno;
題主代碼：
select sname,ssex,sbirthday from students
union
select tname,tsex,tbirthday from teachers;
#32.查詢所有“女”教師和“女”同學(xué)的name捕发、sex和birthday.
select sname,ssex,sbirthday from students where ssex = '女'
union
select tname,tsex,tbirthday from teachers where tsex = '女';
31原諒我已經(jīng)把union已經(jīng)忘了，現(xiàn)在還不是很會(huì)用……
#33.查詢成績(jī)比該課程平均成績(jī)低的同學(xué)的成績(jī)表很魂。???不明白不懂
題主代碼：
SELECT s1.*
FROM Scores AS s1 INNER JOIN (
SELECT Cno,AVG(Degree) AS aDegree
FROM Scores
GROUP BY Cno) s2
ON(s1.Cno=s2.Cno AND s1.Degree<s2.aDegree);
33下來在看一下吧扎酷，明天上班路上復(fù)習(xí)。第一次見on可以連接多個(gè)條件遏匆，并且不是等號(hào)是小于符號(hào)
#34.查詢所有任課教師的Tname和Depart.?????
題主代碼：
select * from coursers;
SELECT Tname,Depart
FROM Teachers
WHERE Tno IN(
SELECT Tno
FROM Coursers);
#35.查詢所有未講課的教師的Tname和Depart.
SELECT Tname,Depart
FROM Teachers
WHERE Tno NOT IN(SELECT Tno FROM Coursers);
34絕對(duì)是沒明白意思法挨，任課老師谁榜，老師還有不上課的嗎？這里居然是讓我篩選哪些老師上課（PS：表中有些老師不上課……）
吐槽……簡(jiǎn)直顛覆本寶寶的常識(shí)凡纳，有不上課的老師嗎窃植？
#36.查詢至少有2名男生的班號(hào)。
我的代碼：
SELECT Class,COUNT(1) AS boyCount
FROM Students
WHERE Ssex='男'
GROUP BY Class
HAVING boyCount>=2;
題主代碼：
select class,ssex,count(1) as boycount from students where
ssex = '男' group by class
having count(ssex) >= 2;
36用count的位置不同吧荐糜，第一次見count(1),居然不對(duì)字段進(jìn)行操作的巷怜，是否有count（2），下來去操作下
#38.查詢Student表中每個(gè)學(xué)生的姓名和年齡暴氏。
我的代碼：
select curtime(); #現(xiàn)在的時(shí)間
select curdate(); #現(xiàn)在的日期
select sname,year(now()) - year(Sbirthday) as sage
from students;
題主代碼：
SELECT Sname,YEAR(NOW())-YEAR(Sbirthday) AS Sage
FROM Students;
38完全是時(shí)間的基礎(chǔ)知識(shí)掌握不牢固
#43.查詢和“李軍”同性別的所有同學(xué)的Sname.
我的代碼：
select sname from students
where ssex =
(select ssex from students where sname = '李軍');
題主代碼：
SELECT s1.Sname
FROM Students AS s1 INNER JOIN Students AS s2
ON(s1.Ssex=s2.Ssex)
WHERE s2.Sname='李軍';
#44.查詢和“李軍”同性別并同班的同學(xué)Sname.
我的代碼：
select * from students
where ssex =
(select ssex from students where sname = '李軍')
and class =
(select class from students where sname = '李軍');
題主代碼：
SELECT s1.Sname
FROM Students AS s1 INNER JOIN Students AS s2
ON(s1.Ssex=s2.Ssex AND s1.Class=s2.Class)
WHERE s2.Sname='李軍';
#45.查詢所有選修“計(jì)算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績(jī)表
我的代碼：
（可愛的我丛版，這么勤勞聯(lián)結(jié)這么多張表……不知疲勞）
select sname,degree,cname,ssex from
(select * from coursers) a
inner join
(select * from scores) b
on a.cno = b.cno
inner join
(select * from students) c
on b.sno = c.sno
where cname = '計(jì)算機(jī)導(dǎo)論' and ssex = '男';
題主代碼：
SELECT * FROM Scores
WHERE Sno IN
(SELECT Sno FROM Students WHERE Ssex='男')
AND Cno IN (SELECT Cno FROM Coursers WHERE Cname='計(jì)算機(jī)導(dǎo)論');
43、44偏序、45是每任何問題的，只是感覺題主的代碼和我的不一樣胖替。不知道是不是我的方法有問題研儒。
好啦，之前練習(xí)的SQL練習(xí)【SQL經(jīng)典練習(xí)題】就總結(jié)到這里了……
20181225學(xué)習(xí)時(shí)間
7:00——7:30
6:00——7:10
9:30——12:30
仰臥起坐100独令，1點(diǎn)睡覺端朵，6點(diǎn)20起床，6點(diǎn)50開始學(xué)習(xí)
