環(huán)境是mysql
練習(xí)數(shù)據(jù)見SQL：練習(xí)的前期準(zhǔn)備
sql 練習(xí)(一)
11、查詢‘3-105’號(hào)課程的平均分
SELECT cno,AVG(degree)
FROM score
WHERE cno='3-105'
12、查詢Score表中至少有5名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)偏友。
SELECT AVG(degree),cno
FROM score
WHERE cno LIKE'3%'
GROUP BY cno
HAVING COUNT(*)>=5
SELECT AVG(degree),cno
FROM score
GROUP BY cno
HAVING (COUNT(1)>=5 AND cno LIKE '3%')
13、查詢最低分大于70忘衍，最高分小于90的Sno列。
SELECT sno
FROM score
GROUP BY sno
HAVING MIN(degree)>=70 AND MAX(degree)<=90
14卿城、查詢所有學(xué)生的Sname淑履、Cno和Degree列。
-- 集合思維（優(yōu)選）
SELECT a.sname,b.cno,b.degree
FROM student a
JOIN score b
ON a.sno=b.sno
-- 過程思維
SELECT (SELECT sname FROM student WHERE sno = sc.sno) sname ,cno ,degree
FROM score sc
15藻雪、查詢所有學(xué)生的Sno、Cname和Degree列狸吞。
-- 集合思維（優(yōu)選）
SELECT b.sno,a.cname,b.degree
FROM course a
JOIN score b
ON a.cno=b.cno
-- 過程思維
SELECT sno,(SELECT cname FROM course WHERE course.cno=score.cno),degree
FROM score
16勉耀、查詢所有學(xué)生的Sname指煎、Cname和Degree列。
-- 集合思維（優(yōu)選）
SELECT b.sname,a.degree,c.cname
FROM score a
JOIN student b ON a.sno=b.sno
JOIN course c ON a.cno=c.cno
-- 過程思維
SELECT (SELECT Sname FROM Student WHERE student.Sno=Score.Sno) sname
,(SELECT Cname FROM course WHERE course.Cno=score.Cno) cname
,Degree
FROM score
17便斥、 查詢“95033”班學(xué)生的平均分至壤。
-- PS :課程號(hào)是95033，并不是班級(jí)
-- 集合思維（優(yōu)選）
SELECT AVG(b.degree),b.cno
FROM student a
JOIN score b
ON a.sno=b.sno
WHERE a.class='95033'
GROUP BY cno
-- 過程思維
SELECT AVG(degree),Cno
FROM score
JOIN student
ON student.sno=score.sno
WHERE cno IN (SELECT cno FROM score WHERE class="95033")
GROUP BY cno
--18枢纠、假設(shè)使用如下命令建立了一個(gè)grade表：
SELECT *FROM grade
CREATE TABLE grade(low INT(3),upp INT(3),rank CHAR(1))
INSERT INTO grade VALUES(90,100,'A')
INSERT INTO grade VALUES(80,89,'B')
INSERT INTO grade VALUES(70,79,'C')
INSERT INTO grade VALUES(60,69,'D')
INSERT INTO grade VALUES(0,59,'E')
現(xiàn)查詢所有同學(xué)的Sno像街、Cno和rank列。
-- 集合思維（優(yōu)選）
SELECT a.Sno,a.Cno,b.rank
FROM score a
JOIN grade b
ON a.degree>=b.low AND a.degree<=b.upp
-- 過程思維
SELECT s.sno,s.cno
,(SELECT rank FROM grade AS g WHERE s.degree BETWEEN g.low AND g.upp)AS rank
FROM SCORE AS s
19晋渺、查詢選修“3-105”課程的成績高于“109”號(hào)同學(xué)成績的所有同學(xué)的記錄镰绎。
SELECT *
FROM score
WHERE cno = '3-105'
AND degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105')
20、查詢score中選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄木西。score(sno,cno,degree)
-- EXISTS與IN的使用效率的問題畴栖，通常情況下采用exists要比in效率高，
因?yàn)镮N不走索引八千，但要看實(shí)際情況具體使用：
-- IN適合于外表大而內(nèi)表小的情況吗讶；EXISTS適合于外表小而內(nèi)表大的情況
--集合思維（優(yōu)選）
SELECT a.sno,a.degree,a.cno
FROM score a
LEFT JOIN (SELECT MAX(degree) degree,cno
FROM score
GROUP BY cno)b
ON a.cno = b.cno
WHERE a.degree != b.degree
AND a.sno IN(SELECT sno
FROM score
GROUP BY sno
HAVING COUNT(sno)>1)
-- lcy 過程思維
SELECT sno,degree,cno
FROM score
WHERE sno IN(SELECT sno
FROM score
GROUP BY sno
HAVING COUNT(sno)>1)
AND CONCAT(degree,sno) NOT IN(SELECT CONCAT(MAX(degree),sno)
FROM score
GROUP BY sno)
<錯(cuò)誤寫法>：原因在于當(dāng)有個(gè)最高分的A同學(xué)的分?jǐn)?shù)88，是B同學(xué)的最低分88分恋捆，88分會(huì)被過濾掉照皆。
解決方法有2個(gè)（其一是拼接在一起，其二是用集合的方式：join沸停、except）
SELECT sno,cno,degree,CONCAT(degree,sno)
FROM score
WHERE degree NOT IN (SELECT MAX(degree)
FROM score
GROUP BY sno)
AND sno IN (SELECT sno FROM score GROUP BY sno HAVING COUNT(cno)>1)
歡迎留言進(jìn)行討論 : )
