一、基本信息
1.學(xué)生表
Student(S,Sname,Sage,Ssex)
--S 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別
2.課程表
Course(C,Cname,T)
C 課程編號(hào),Cname 課程名稱,T 教師編號(hào)
3.教師表
Teacher(T,Tname)
T 教師編號(hào),Tname 教師姓名
4.成績(jī)表
SC(S,C,score)
S 學(xué)生編號(hào),C 課程編號(hào),score 分?jǐn)?shù)
二、創(chuàng)建數(shù)據(jù)庫(kù)
1.學(xué)生表 Student
create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
2.科目表 Course
create table Course(C varchar(10),Cname nvarchar(10),T varchar(10));
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '03');
3.教師表 Teacher
create table Teacher(T varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
4.成績(jī)表 SC
create table SC(S varchar(10),C 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);
50道聯(lián)系題目及參考答案（若有錯(cuò)，請(qǐng)指出，謝謝！）
1.查詢"01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
SELECT c.*,d.c,d.score
FROM
(
SELECT student.s,sname,c,score
FROM student,sc
WHERE student.s=sc.s AND c='01'
)
c,
(
SELECT student.s,c,score
FROM student,sc
WHERE student.s=sc.s AND c='02'
)d
WHERE c.s=d.s AND c.score >d.score;
+------+--------+------+-------+------+-------+
| s | sname | c | score | c | score |
+------+--------+------+-------+------+-------+
| 02 |錢電 | 01 | 70.0 | 02 | 60.0 |
| 04 |李云 | 01 | 50.0 | 02 | 30.0 |
+------+--------+------+-------+------+-------+
1.1查詢同時(shí)存在"01 "課程和" 02 "課程的情況
SELECT A.S,A.C,B.C
FROM
(
SELECT *
FROM sc
WHERE sc.c='01'
)A
INNER JOIN
(
SELECT *
FROM sc
WHERE sc.c='02'
)B
ON A.s=B.s;
+------+------+------+
| S | C | C |
+------+------+------+
| 01 | 01 | 02 |
| 02 | 01 | 02 |
| 03 | 01 | 02 |
| 04 | 01 | 02 |
| 05 | 01 | 02 |
+------+------+------+
1.2查詢存在"01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為NULL )
SELECT A.c,A.c,A.score,B.c,B.score
FROM
(
SELECT *
FROM sc
WHERE sc.c='01'
)A
LEFT JOIN
(
SELECT *
FROM sc
WHERE sc.c='02'
)B
ON A.s=B.s
WHERE B.c is NULL;
+------+------+-------+------+-------+
| C | C | score | C | score |
+------+------+-------+------+-------+
| 01 | 01 | 31.0 | NULL | NULL |
+------+------+-------+------+-------+
1.3查詢不存在"01 "課程但存在" 02 "課程的情況
SELECT *
FROM
(
SELECT *
FROM sc
WHERE sc.c='01'
)A
RIGHT JOIN
(
SELECT *
FROM sc
WHERE sc.c='02'
)B
ON A.s=B.s
WHERE A.c is NULL;
+------+------+-------+------+------+-------+
| S | C | score | S | C | score |
+------+------+-------+------+------+-------+
| NULL | NULL | NULL | 07 | 02 | 89.0 |
+------+------+-------+------+------+-------+
2.查詢平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
SELECT st.s,st.sname,avg(score)
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY st.s
HAVING avg(score)>=60;
+------+--------+------------+
| s | sname | avg(score) |
+------+--------+------------+
| 01 |趙雷 | 89.66667 |
| 02 |錢電 | 70.00000 |
| 03 |孫風(fēng) | 80.00000 |
| 05 |周梅 | 81.50000 |
| 07 |鄭竹 | 93.50000 |
+------+--------+------------+
3.查詢?cè)赟C表存在成績(jī)的學(xué)生信息
SELECT st.s,st.ssex,st.sage,sc.c,sc.score
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY st.s;
+------+------+---------------------+------+-------+
| s | ssex | sage | c | score |
+------+------+---------------------+------+-------+
| 01 |男 | 1990-01-01 00:00:00 | 01 | 80.0 |
| 02 |男 | 1990-12-21 00:00:00 | 01 | 70.0 |
| 03 |男 | 1990-05-20 00:00:00 | 01 | 80.0 |
| 04 |男 | 1990-08-06 00:00:00 | 01 | 50.0|
| 05 |女 | 1991-12-01 00:00:00 | 01 | 76.0 |
| 06 |女 | 1992-03-01 00:00:00 | 01 | 31.0 |
| 07 |女 | 1989-07-01 00:00:00 | 02 | 89.0 |
+------+------+---------------------+------+-------+
4.查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)(沒成績(jī)的顯示為NULL )
SELECT A.s,sname,COUNT(c)選課總數(shù),score 總成績(jī)
FROM student A
LEFT JOIN sc B
ON A.s=B.s
GROUP BY A.s;
+------+--------+--------------+-----------+
| s | sname |選課總數(shù) |總成績(jī) |
+------+--------+--------------+-----------+
| 01 |趙雷 | 3 | 80.0 |
| 02 |錢電 | 3 | 70.0 |
| 03 |孫風(fēng) | 3 | 80.0 |
| 04 |李云 | 3 | 50.0 |
| 05 |周梅 | 2 | 76.0 |
| 06 |吳蘭 | 2 | 31.0 |
| 07 |鄭竹 | 2 | 89.0 |
| 08 |王菊 | 0 | NULL |
+------+--------+--------------+-----------+
4.1查有成績(jī)的學(xué)生信息
SELECT A.s,sname,COUNT(c)選課總數(shù),score 總成績(jī)
FROM student A
INNER JOIN sc B
ON A.s=B.s
GROUP BY A.s ;
+------+--------+--------------+-----------+
| s | sname |選課總數(shù) |總成績(jī) |
+------+--------+--------------+-----------+
| 01 |趙雷 | 3 | 80.0 |
| 02 |錢電 | 3 | 70.0 |
| 03 |孫風(fēng) | 3 | 80.0 |
| 04 |李云 | 3 | 50.0 |
| 05 |周梅 | 2 | 76.0 |
| 06 |吳蘭 | 2 | 31.0 |
| 07 |鄭竹 | 2 | 89.0 |
+------+--------+--------------+-----------+
5.查詢「李」姓老師的數(shù)量
SELEC Tteacher.tname,COUNT(Tname)
FROM teacher
WHERE tname
LIKE '李%'
GROUP BY tname;
+--------+--------------+
| tname | COUNT(Tname) |
+--------+--------------+
|李四 | 1 |
+--------+--------------+
6.查詢學(xué)過「張三」老師授課的同學(xué)的信息
SELECT student.*
FROM student
WHERE s
IN
(
SELECT s
FROM sc
WHERE c
IN
(
SELECT c
FROM course
WHERE t
IN
(
SELECT t
FROM teacher
WHERE tname ='張三'
)
)
);
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |趙雷 | 1990-01-01 00:00:00 |男 |
| 02 |錢電 | 1990-12-21 00:00:00 |男 |
| 03 |孫風(fēng) | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 |
| 07 |鄭竹 | 1989-07-01 00:00:00 |女 |
+------+--------+---------------------+------+
7.查詢沒有學(xué)全所有課程的同學(xué)的信息
SELECT st.*,sc.c,sc.score
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY st.s
HAVING COUNT(st.sname)< 3;
+------+--------+---------------------+------+------+-------+
| S | Sname | Sage | Ssex |c | score |
+------+--------+---------------------+------+------+-------+
| 05 |周梅 | 1991-12-01 00:00:00 |女 | 01 | 76.0 |
| 06 |吳蘭 | 1992-03-01 00:00:00 |女 | 01 | 31.0 |
| 07 |鄭竹 | 1989-07-01 00:00:00 |女 | 02 | 89.0 |
+------+--------+---------------------+------+------+-------+
8.查詢至少有一門課與學(xué)號(hào)為"01 "的同學(xué)所學(xué)相同的同學(xué)的信息
SELECT *
FROM
(
SELECT st.*
FROM student
AS st,sc
WHERE st.s=sc.s
AND sc.c= any
(
SELECT c
FROM sc
WHERE s='01'
)
) A
GROUP BY s;
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |趙雷 | 1990-01-01 00:00:00 |男 |
| 02 |錢電 | 1990-12-21 00:00:00 |男 |
| 03 |孫風(fēng) | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 |
| 06 |吳蘭 | 1992-03-01 00:00:00 |女 |
| 07 |鄭竹 | 1989-07-01 00:00:00 |女 |
+------+--------+---------------------+------+
9.查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
SELECT st.*
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY st.s
HAVING st.s<>'01'
AND sum(c)=
(
SELECT sum(c)
FROM sc
WHERE s='01'
GROUP BY s
);
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 02 |錢電 | 1990-12-21 00:00:00 |男 |
| 03 |孫風(fēng) | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
+------+--------+---------------------+------+
10.查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
SELECT st.*
FROM student AS st,sc
WHERE st.s=sc.s
AND sc.c in
(
SELECT c
FROM course
WHERE t = any
(
SELECT t
FROM teacher
WHERE tname='張三'
)
);
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |趙雷 | 1990-01-01 00:00:00 |男 |
| 02 |錢電 | 1990-12-21 00:00:00 |男 |
| 03 |孫風(fēng) | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 |
| 07 |鄭竹 | 1989-07-01 00:00:00 |女 |
+------+--------+---------------------+------+
11.查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)
SELECT A.*,B.平均成績(jī)
FROM student A ,
(
SELECT *,avg(score) 平均成績(jī)
FROM sc
WHERE score <60
GROUP BY s
HAVING COUNT(s)>=2
)B
WHERE A.s = B.s;
+------+--------+---------------------+------+--------------+
| S | Sname | Sage | Ssex | 平均成績(jī) |
+------+--------+---------------------+------+--------------+
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 33.33333 |
| 06 | 吳蘭 | 1992-03-01 00:00:00 | 女 | 32.50000 |
+------+--------+---------------------+------+--------------+
12.檢索"01 "課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
SELECT st.*,A.score
FROM student AS st,
(
SELECT *
FROM sc
WHERE c='01'
AND score<60
)A
WHERE st.s=A.s
ORDER BY score DESC;
+------+--------+---------------------+------+-------+
| S | Sname | Sage | Ssex |score |
+------+--------+---------------------+------+-------+
| 04 |李云 | 1990-08-06 00:00:00 |男 | 50.0 |
| 06 |吳蘭 | 1992-03-01 00:00:00 |女 | 31.0 |
+------+--------+---------------------+------+-------+
13.按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
SELECT sc.*,平均值
FROM sc LEFT JOIN
(
SELECT *,avg(score) as平均值
FROM sc GROUP BY s
)A
ON sc.s=A.s
ORDER BY平均值DESC;
+------+------+-------+-----------+
| S | C | score |平均值 |
+------+------+-------+-----------+
| 07 | 02 | 89.0 | 93.50000 |
| 07 | 03 | 98.0 | 93.50000 |
| 01 | 03 | 99.0 | 89.66667 |
| 01 | 01 | 80.0 | 89.66667 |
| 01 | 02 | 90.0 | 89.66667 |
| 05 | 01 | 76.0 | 81.50000 |
| 05 | 02 | 87.0 | 81.50000 |
| 03 | 01 | 80.0 | 80.00000 |
| 03 | 02 | 80.0 | 80.00000 |
| 03 | 03 | 80.0 | 80.00000 |
| 02 | 01 | 70.0 | 70.00000 |
| 02 | 02 | 60.0 | 70.00000 |
| 02 | 03 | 80.0 | 70.00000 |
| 04 | 02 | 30.0 | 33.33333 |
| 04 | 03 | 20.0 | 33.33333 |
| 04 | 01 | 50.0 | 33.33333 |
| 06 | 01 | 31.0 | 32.50000 |
| 06 | 03 | 34.0 | 32.50000 |
+------+------+-------+-----------+
14.查詢各科成績(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 A.c,cname,最高分,最低分,平均分,及格率,中等率,優(yōu)良率,優(yōu)秀率
FROM course A
LEFT JOIN
(
SELECT c,max(score)最高分,min(score)最低分,avg(score)平均分
FROM sc
GROUP BY c) B
ON A.c=B.c
LEFT JOIN
(
SELECT c,
(
CONVERT
(
(
(
sum(CASE WHEN score >=60 then 1
ELSE 0 END
)
*1.00
)/COUNT(*)
)*100,
DECIMAL(5,2)))及格率
FROM sc
GROUP BY c
)C
ON A.c=C.c
LEFT JOIN
(
SELECT c,
(
CONVERT
(
(
(
sum(CASE WHEN score >=70 AND score <80 then 1
ELSE 0 END
)*1.00
)/COUNT(*))*100,
DECIMAL(5,2)
)
)中等率
FROM sc
GROUP BY c
)D
ON A.c=D.c
LEFT JOIN
(
SELECT c,
(
CONVERT
(
(
(
sum(CASE WHEN score >=80 AND score <90 then 1
ELSE 0 END
)*1.00
)/COUNT(*)
)*100,
DECIMAL(5,2)
)
)優(yōu)良率
FROM sc
GROUP BY c
)E
ON A.c=E.c
LEFT JOIN
(
SELECT c,
(
CONVERT
(
(
(
sum(CASE WHEN score >=90 then 1
ELSE 0 END
)*1.00
)/COUNT(*)
)*100,
DECIMAL(5,2)
)
)優(yōu)秀率
FROM sc
GROUP BY c
)F
ON A.c=F.c;
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|c | cname |最高分 |最低分 |平均分 |及格率 |中等率 |優(yōu)良率 |優(yōu)秀率 |
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|01 |語文 | 80.0 | 31.0 | 64.50000 | 66.67 | 33.33 | 33.33 | 0.00 |
|02 |數(shù)學(xué) | 90.0 | 30.0 | 72.66667 | 83.33 | 0.00 | 50.00 | 16.67 |
|03 |英語 | 99.0 | 20.0 | 68.50000 | 66.67 | 0.00 | 33.33 | 33.33 |
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
15.按各科成績(jī)進(jìn)行排序，并顯示排名，Score重復(fù)時(shí)合并保留名次空缺
SELECT *,RANK() OVER(PARTITION BY c ORDER BY score DESC)排名
FROM sc;
+------+------+-------+--------+
| S | C | score |排名 |
+------+------+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 3 |
| 02 | 01 | 70.0 | 4 |
| 04 | 01 | 50.0 | 5 |
| 06 | 01 | 31.0 | 6 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 03 | 02 | 80.0 | 4 |
| 02 | 02 | 60.0 | 5 |
| 04 | 02 | 30.0 | 6 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
| 03 | 03 | 80.0 | 3 |
| 06 | 03 | 34.0 | 5 |
| 04 | 03 | 20.0 | 6 |
+------+------+-------+--------+
15.1按各科成績(jī)進(jìn)行排序，并顯示排名，Score重復(fù)時(shí)合并名次
SELECT *,DENSE_RANK() OVER(PARTITION BY c ORDER BY score DESC)排名
FROM sc;
+------+------+-------+--------+
| S | C | score | 排名 |
+------+------+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 2 |
| 02 | 01 | 70.0 | 3 |
| 04 | 01 | 50.0 | 4 |
| 06 | 01 | 31.0 | 5 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 03 | 02 | 80.0 | 4 |
| 02 | 02 | 60.0 | 5 |
| 04 | 02 | 30.0 | 6 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
| 03 | 03 | 80.0 | 3 |
| 06 | 03 | 34.0 | 4 |
| 04 | 03 | 20.0 | 5 |
+------+------+-------+--------+
16.查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺
SELECT s,RANK() OVER(ORDER BY sum(score) DESC)排名,sum(score)
FROM sc
GROUP BY s
ORDER BY sum(score) DESC;
+------+--------+------------+
| s |排名 | sum(score) |
+------+--------+------------+
| 01 | 1 | 269.0 |
| 03 | 2 | 240.0 |
| 02 | 3 | 210.0 |
| 07 | 4 | 187.0 |
| 05 | 5 | 163.0 |
| 04 | 6 | 100.0 |
| 06 | 7 | 65.0 |
+------+--------+------------+
16.1查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)不保留名次空缺
SELECT s,DENSE_RANK() OVER(ORDER BY sum(score) DESC)排名,sum(score)
FROM sc
GROUP BY s
ORDER BY sum(score) DESC;
+------+--------+------------+
| s |排名 | sum(score) |
+------+--------+------------+
| 01 | 1 | 269.0 |
| 03 | 2 | 240.0 |
| 02 | 3 | 210.0 |
| 07 | 4 | 187.0 |
| 05 | 5 | 163.0 |
| 04 | 6 | 100.0 |
| 06 | 7 | 65.0 |
+------+--------+------------+
17.統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比
SELECT A.c,100到85人數(shù),100到85占比, 85到70人數(shù),85到70占比, 70到60人數(shù),70到60占比,60到0人數(shù),60到0占比
FROM sc A
LEFT JOIN
(
SELECT s,c,sum(CASE WHEN score >85 then 1 ELSE 0 END)
AS 100到85人數(shù),
CONVERT
(
(
sum(CASE WHEN score >85 then 1 ELSE 0 END)*100
)/COUNT(score),decimal(5,1)
) AS 100到85占比
FROM sc
GROUP BY c)B
ON A.S=B.S
LEFT JOIN
(
SELECT s,c,
sum(CASE WHEN score >70 AND score<=85 then 1 ELSE 0 END)
AS 85到70人數(shù),
CONVERT
(
(
sum(CASE WHEN score >70 AND score<=85 then 1 ELSE 0 END)*100
)/COUNT(score),decimal(5,1)
) AS 85到70占比
FROM sc
GROUP BY c)C
ON A.S=C.S
LEFT JOIN
(
SELECT s,c,sum(CASE WHEN score >60 AND score <=70 then 1 ELSE 0 END)
AS 70到60人數(shù),
CONVERT
(
(
sum(CASE WHEN score >60 AND score<=70 then 1 ELSE 0 END)*100
)/COUNT(score),decimal(5,1)
) AS 70到60占比
FROM sc
GROUP BY c)D
ON A.S=D.S
LEFT JOIN
(
SELECT s,c,sum(CASE WHEN score >=0 AND score<=60 then 1 ELSE 0 END)
AS 60到0人數(shù),
CONVERT
(
(
sum(CASE WHEN score >=0 AND score <=60 then 1 ELSE 0 END)*100
)/COUNT(score),decimal(5,1)
) AS 60到0占比 FROM sc GROUP BY c)E
ON A.S=E.S
WHERE A.C=B.C AND A.C=C.C AND A.C=D.C AND A.C=E.C;
+------+----------------+----------------+---------------+---------------+---------------+---------------+--------------+--------------+
| c | 100到85人數(shù) | 100到85占比 | 85到70人數(shù) | 85到70占比 | 70到60人數(shù) | 70到60占比 | 60到0人數(shù) | 60到0占比 |
+------+----------------+----------------+---------------+---------------+---------------+---------------+--------------+--------------+
| 01 | 0 | 0.0 | 3 | 50.0 | 1 | 16.7 | 2 | 33.3 |
| 02 | 3 | 50.0 | 1 | 16.7 | 0 | 0.0 | 2 | 33.3 |
| 03 | 2 | 33.3 | 2 | 33.3 | 0 | 0.0 | 2 | 33.3 |
+------+----------------+----------------+---------------+---------------+---------------+---------------+--------------+--------------+
18.查詢各科成績(jī)前三名的記錄
SELECT *
FROM
(
SELECT *,row_number() over(PARTITION BY c ORDER BY score DESC)排名
FROM sc
ORDER BY c,排名
)A
WHERE 排名<=3;
+------+------+-------+--------+
| S | C | score |排名 |
+------+------+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 2 |
| 05 | 01 | 76.0 | 3 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
+------+------+-------+--------+
19.查詢每門課程被選修的學(xué)生數(shù)
SELECT c,COUNT(s)
FROM sc
GROUP BY c;
+------+----------+
| c | COUNT(s) |
+------+----------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+----------+
20.查詢出只選修兩門課程的學(xué)生學(xué)號(hào)和姓名
SELECT s,sname,COUNT(s)課程數(shù)
FROM
(
SELECT st.s,sname,c
FROM student AS st,sc
WHERE st.s=sc.s
)A
GROUP BY s
HAVING COUNT(s)=2;
+------+--------+-----------+
| s | sname |課程數(shù) |
+------+--------+-----------+
| 05 |周梅 | 2 |
| 06 |吳蘭 | 2 |
| 07 |鄭竹 | 2 |
+------+--------+-----------+
21.查詢男生、女生人數(shù)
SELECT ssex,COUNT(ssex)
FROM student st
GROUP BY ssex;
+------+-------------+
| ssex | COUNT(ssex) |
+------+-------------+
|男 | 4 |
|女 | 4 |
+------+-------------+
22.查詢名字中含有「風(fēng)」字的學(xué)生信息
SELECT *
FROM student st
WHERE sname LIKE '%風(fēng)%';
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 03 |孫風(fēng) | 1990-05-20 00:00:00 |男 |
+------+--------+---------------------+------+
23.查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
SELECT s,sname,COUNT(sname)
FROM student
GROUP BY sname
HAVING COUNT(sname) >=2;
Empty set (0.00 sec)
24.查詢1990年出生的學(xué)生名單
SELECT *
FROM student st
WHERE sage LIKE '1990-%';
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |趙雷 | 1990-01-01 00:00:00 |男 |
| 02 |錢電 | 1990-12-21 00:00:00 |男 |
| 03 |孫風(fēng) | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
| 08 |王菊 | 1990-01-20 00:00:00 |女 |
+------+--------+---------------------+------+
25.查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列
SELECT *,avg(score)
FROM sc
GROUP BY s
ORDER BY avg(score) DESC,c ASC;
+------+------+-------+------------+
| S | C | score | avg(score) |
+------+------+-------+------------+
| 07 | 02 | 89.0 | 93.50000 |
| 01 | 01 | 80.0 | 89.66667 |
| 05 | 01 | 76.0 | 81.50000 |
| 03 | 01 | 80.0 | 80.00000 |
| 02 | 01 | 70.0 | 70.00000 |
| 04 | 01 | 50.0 | 33.33333 |
| 06 | 01 | 31.0 | 32.50000 |
+------+------+-------+------------+
26.查詢平均成績(jī)大于等于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)
SELECT s,sname,avg(score)
FROM
(
SELECT st.s,sname,score
FROM student st,sc
WHERE st.s=sc.s
)A
GROUP BY s
HAVING avg(score)>=85;
+------+--------+------------+
| s | sname | avg(score) |
+------+--------+------------+
| 01 |趙雷 | 89.66667 |
| 07 |鄭竹 | 93.50000 |
+------+--------+------------+
27.查詢課程名稱為「數(shù)學(xué)」，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
SELECT st.s,score
FROM student AS st,sc
WHERE st.s=sc.s AND score<=60 AND c in
(
SELECT cname
FROM course
WHERE cname = '數(shù)學(xué)'
);
Empty set (0.00 sec)
28.查詢所有學(xué)生的課程及分?jǐn)?shù)情況(存在學(xué)生沒成績(jī)，沒選課的情況)
SELECT st.*,sc.c,sc.score
FROM student AS st
LEFT JOIN sc
ON st.s = sc.s
ORDER BY st.s,sc.c;
+------+--------+---------------------+------+------+-------+
| S | Sname | Sage | Ssex |c | score |
+------+--------+---------------------+------+------+-------+
| 01 |趙雷 | 1990-01-01 00:00:00 |男 | 01 | 80.0 |
| 01 |趙雷 | 1990-01-01 00:00:00 |男 | 02 | 90.0 |
| 01 |趙雷 | 1990-01-01 00:00:00 |男 | 03 | 99.0 |
| 02 |錢電 | 1990-12-21 00:00:00 |男 | 01 | 70.0 |
| 02 |錢電 | 1990-12-21 00:00:00 |男 | 02 | 60.0 |
| 02 |錢電 | 1990-12-21 00:00:00 |男 | 03 | 80.0 |
| 03 |孫風(fēng) | 1990-05-20 00:00:00 |男 | 01 | 80.0 |
| 03 |孫風(fēng) | 1990-05-20 00:00:00 |男 | 02 | 80.0 |
| 03 |孫風(fēng) | 1990-05-20 00:00:00 |男 | 03 | 80.0 |
| 04 |李云 | 1990-08-06 00:00:00 |男 | 01 | 50.0 |
| 04 |李云 | 1990-08-06 00:00:00 |男 | 02 | 30.0 |
| 04 |李云 | 1990-08-06 00:00:00 |男 | 03 | 20.0 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 | 01 | 76.0 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 | 02 | 87.0 |
| 06 |吳蘭 | 1992-03-01 00:00:00 |女 | 01 | 31.0 |
| 06 |吳蘭 | 1992-03-01 00:00:00 |女 | 03 | 34.0 |
| 07 |鄭竹 | 1989-07-01 00:00:00 |女 | 02 | 89.0 |
| 07 |鄭竹 | 1989-07-01 00:00:00 |女 | 03 | 98.0 |
| 08 |王菊 | 1990-01-20 00:00:00 |女 | NULL | NULL |
+------+--------+---------------------+------+------+-------+
29.查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)
SELECT st.s,st.sname,c,score
FROM student AS st
INNER JOIN sc
ON st.s=sc.s
WHERE score >=70
ORDER BY st.s,c;
+------+--------+------+-------+
| s | sname | c | score |
+------+--------+------+-------+
| 01 |趙雷 | 01 | 80.0 |
| 01 |趙雷 | 02 | 90.0 |
| 01 |趙雷 | 03 | 99.0 |
| 02 |錢電 | 01 | 70.0 |
| 02 |錢電 | 03 | 80.0 |
| 03 |孫風(fēng) | 01 | 80.0 |
| 03 |孫風(fēng) | 02 | 80.0 |
| 03 |孫風(fēng) | 03 | 80.0 |
| 05 |周梅 | 01 | 76.0 |
| 05 |周梅 | 02 | 87.0 |
| 07 |鄭竹 | 02 | 89.0 |
| 07 |鄭竹 | 03 | 98.0 |
+------+--------+------+-------+
30.查詢不及格的課程
SELECT c,score
FROM sc
WHERE score <60
ORDER BY c;
+------+-------+
| c | score |
+------+-------+
| 01 | 50.0 |
| 01 | 31.0 |
| 02 | 30.0 |
| 03 | 20.0 |
| 03 | 34.0 |
+------+-------+
31.查詢課程編號(hào)為01且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名
SELECT st.s,st.sname,c,score
FROM student AS st,sc
WHERE st.s=sc.s AND c='01' AND score >=80;
+------+--------+------+-------+
| s | sname | c | score |
+------+--------+------+-------+
| 01 |趙雷 | 01 | 80.0 |
| 03 |孫風(fēng) | 01 | 80.0 |
+------+--------+------+-------+
32.求每門課程的學(xué)生人數(shù)
SELECT c,COUNT(c)
FROM sc
GROUP BY c;
+------+----------+
| c | COUNT(c) |
+------+----------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+----------+
33.成績(jī)不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
SELECT st.s,st.sname,score
FROM student AS st,sc
WHERE st.s=sc.s AND c in
(
SELECT c
FROM course,teacher
WHERE course.t = teacher.t AND teacher.tname= '張三'
)
ORDER BY score DESC limit 1 ;
+------+--------+-------+
| s | sname | score |
+------+--------+-------+
| 01 |趙雷 | 90.0 |
+------+--------+-------+
34.成績(jī)有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
SELECT * FROM
(
SELECT st.s,st.sname,score,dense_rank() over
(
ORDER BY score DESC
)排名
FROM student AS st,sc
WHERE st.s=sc.s AND c in
(
SELECT c
FROM course,teacher
WHERE course.t = teacher.t AND teacher.tname = '張三'
)
)A
WHERE 排名='1';
+------+--------+-------+--------+
| s | sname | score |排名 |
+------+--------+-------+--------+
| 01 |趙雷 | 90.0 | 1 |
+------+--------+-------+--------+
35.查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)
SELECT s,c,score
FROM
(
SELECT max(score),avg(score),COUNT(score),student.s,c,score
FROM student
LEFT JOIN sc
ON student.s=sc.s
GROUP BY student.s
HAVING max(score)=avg(score) ANDCOUNT(score)>=2
)A;
+------+------+-------+
| s | c | score |
+------+------+-------+
| 03 | 01 | 80.0 |
+------+------+-------+
36.查詢每門功成績(jī)最好的前兩名
SELECT s,c,score,排名
FROM
(
SELECT s,c,score,row_number() over(partitionby c ORDER BY score DESC)排名
FROM sc
ORDER BY score DESC
)A WHERE 排名<=2
ORDER BY c,排名;
+------+------+-------+--------+
| s | c | score |排名 |
+------+------+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 2 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
+------+------+-------+--------+
37.統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)(超過5人的課程才統(tǒng)計(jì))。
SELECT c,COUNT(c)
FROM sc
GROUP BY c
HAVING COUNT(c)>5 ;
+------+----------+
| c | COUNT(c) |
+------+----------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+----------+
38.檢索至少選修兩門課程的學(xué)生學(xué)號(hào)
SELECT s,COUNT(c)
FROM sc
GROUP BY s
HAVING COUNT(c)>=2;
+------+----------+
| s | COUNT(c) |
+------+----------+
| 01 | 3 |
| 02 | 3 |
| 03 | 3 |
| 04 | 3 |
| 05 | 2 |
| 06 | 2 |
| 07 | 2 |
+------+----------+
39.查詢選修了全部課程的學(xué)生信息
SELECT sc.s,st.sname,COUNT(c)
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY s
HAVING COUNT(c)>=3;
+------+--------+----------+
| s | sname | COUNT(c) |
+------+--------+----------+
| 01 |趙雷 | 3 |
| 02 |錢電 | 3 |
| 03 |孫風(fēng) | 3 |
| 04 |李云 | 3 |
+------+--------+----------+
40.查詢各學(xué)生的年齡，只按年份來算
SELECT s,sname,(year(curdate())-year(sage))年齡
FROM student
ORDER BY 年齡;
+------+--------+--------+
| s | sname |年齡 |
+------+--------+--------+
| 06 |吳蘭 | 27 |
| 05 |周梅 | 28 |
| 01 |趙雷 | 29 |
| 02 |錢電 | 29 |
| 03 |孫風(fēng) | 29 |
| 04 |李云 | 29 |
| 08 |王菊 | 29 |
| 07 |鄭竹 | 30 |
+------+--------+--------+
41.按照出生日期來算，當(dāng)前月日<出生年月的月日則，年齡減一
SELECT s,sname,timestampdiff(year,sage,curdate())年齡
FROM student
ORDER BY 年齡;
+------+--------+--------+
| s | sname |年齡 |
+------+--------+--------+
| 06 |吳蘭 | 26 |
| 05 |周梅 | 27 |
| 02 |錢電 | 28 |
| 03 |孫風(fēng) | 28 |
| 04 |李云 | 28 |
| 08 |王菊 | 28 |
| 01 |趙雷 | 29 |
| 07 |鄭竹 | 29 |
+------+--------+--------+
42.查詢本周過生日的學(xué)生
--今天是2019.1.16
SET @day =8-dayofweek(curdate());
SELECT *
FROM student
WHERE date_format(sage, '%m%d')
BETWEEN date_format(curdate(),'%m%d')
AND
date_format (date_add(curdate(),interval @day day), '%m%d');
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 08 |王菊 | 1990-01-20 00:00:00 |女 |
+------+--------+---------------------+------+
43.查詢下周過生日的學(xué)生
set @day =9-dayofweek(curdate());
SELECT *
FROM student
WHERE date_format(sage, '%m%d')
BETWEEN date_format (date_add(curdate(),interval @day day), '%m%d')
AND
date_format (date_add(curdate(),interval @day+6 day), '%m%d');
Empty set (0.00 sec)
44.查詢本月過生日的學(xué)生
SELECT *
FROM student
WHERE date_format(sage,'%m')= date_format(curdate(),'%m');
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |趙雷 | 1990-01-01 00:00:00 |男 |
| 08 |王菊 | 1990-01-20 00:00:00 |女 |
+------+--------+---------------------+------+
45.查詢下月過生日的學(xué)生
SELECT *
FROM student
WHERE date_format(sage,'%m')= date_format(date_add(curdate(),interval 1 month),'%m');
Empty set (0.00 sec)
