SQL 練習題
使用環境：db4free.net
題目來源：
http://link.zhihu.com/target=http%3A//blog.csdn.net/flycat296/article/details/63681089
一、建立資料表
-
學生表 Student
Student(SId, Sname ,Sage, Ssex)
SId=學生ID, Sname=學生姓名 ,Sage=學生年齡 , Ssex=學生性别
-
科目表 Course
Course(CId, Cname, TId)
CId=科目ID , Cname=科目名稱, TId=教師ID
-
教師表 Teacher
Teacher(TId, Tname)
TId=老師ID， Tname=老師姓名
-
成績表 Score
Score(SId, CId, score)
SId=學生ID， CId=科目ID， score=分數
#學生表
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(1));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風' , '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('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '趙六' , '2017-01-01' , '女');
insert into Student values('13' , '孫七' , '2018-01-01' , '女');
#科目表
create table Course(CId varchar(10),Cname varchar(10),TId varchar(10));
insert into Course values('01' , '國文' , '02');
insert into Course values('02' , '數學' , '01');
insert into Course values('03' , '英語' , '03');
#教師表
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
#成績表
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);
##更新主鍵
ALTER TABLE Student ADD PRIMARY KEY(SId);
ALTER TABLE Course ADD PRIMARY KEY(CId);
ALTER TABLE Teacher ADD PRIMARY KEY(TId);
ALTER TABLE SC ADD PRIMARY KEY(SId,CId);
二、題目練習
- 傳回教師為‘01’的所有得分資訊
SELECT *
FROM SC INNER JOIN Course
WHERE (SC.CId = Course.CId) AND (Course.TId = '01');
- 傳回所有男生的分數
SELECT *
FROM SC INNER JOIN Student
WHERE (SC.SId = Student.SId) AND Student.Ssex = '男';
3.國文分數大于60小于100的學生資訊 ，按成績排序
SELECT *
FROM Student INNER JOIN SC
WHERE Student.SID = SC.SId
AND SC.score BETWEEN 60 AND 100
AND SC.CId IN(SELECT CId FROM Course WHERE Cname = '國文')
ORDER BY score;
4.查詢姓“張”老師的數量
SELECT COUNT(Tname)
FROM Teacher
WHERE Tname LIKE '張%';
5.傳回1990年出生的學生姓名
SELECT Sname
FROM Student
WHERE Sage LIKE '1990%';
SELECT Sname, Sage
FROM Student
WHERE year(Sage) = 1990;
6.傳回有不及格（<60）的學生姓名、課程名稱及分數
SELECT Student.Sname, Course.Cname, SC.score
FROM Student INNER JOIN SC INNER JOIN Course
WHERE Student.SID = SC.SId
AND SC.CId = Course.CId
AND SC.score < 60；
7.國文課的最高分、最低分及平均分
SELECT MAX(score), MIN(score), AVG(score)
FROM SC
WHERE CId IN (SELECT CId FROM Course WHERE Cname = '國文');
8.查詢各科成績最高分、最低分和平均分
SELECT Course.Cname ,MAX(score),MIN(score),AVG(score)
FROM SC INNER JOIN Course
WHERE SC.CId = Course.CId
GROUP BY Cname;
9.查詢國文成績前三名的記錄
SELECT Course.Cname, SC.score
FROM Course INNER JOIN SC
WHERE Course.CId = SC.CId
AND Cname = '國文'
ORDER BY SC.score DESC
LIMIT 1,3;
10.查詢男生、女生人數
SELECT Ssex ,COUNT(SId)
FROM Student
GROUP BY Ssex;
11.求每門課程的學生人數
SELECT CId,COUNT(SId) as 學生人數
FROM SC
GROUP BY CId;
12.查詢沒學過"張三"老師講授的任一門課程的學生姓名
SELECT *
FROM Student
WHERE SId NOT IN(
SELECT SId FROM SC WHERE CId IN(
SELECT CId FROM Course WHERE TId IN(
SELECT TId FROM Teacher WHERE Tname='張三')));
13.查詢學生的總成績，并進行排名，總分重複時保留名次空缺
SELECT s.SId, s.sum_score,@rownum := @rownum + 1 AS RANK
FROM(
SELECT SId,SUM(score)AS sum_score
FROM SC
GROUP BY SId
ORDER BY sum_score) as s,
(SELECT @rownum := 0) r ;