SQL 45題

15773 ワード

データテーブル
1.学生表Student（SId、Sname、Sage、Ssex）

SId     ,Sname     ,Sage     ,Ssex

2.課程表Course（CId、Cname、TId）

CId --    ,Cname     ,TId

3.教師表Techer（TId、Tname）

TId     ,Tname

4.成績表SC（SId、CId、score）

SId     ,CId     ,score

試験データの作成
学生時計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' , '  ' , '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 nvarchar(10),TId varchar(10));
insert into Course values('01' , '  ' , '02');
insert into Course values('02' , '  ' , '01');
insert into Course values('03' , '  ' , '03');

教師表Techer

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '  ');
insert into Teacher values('02' , '  ');
insert into Teacher values('03' , '  ');

成績表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)

練習問題
1.「01」コースは「02」コースより成績が高い学生の情報と課程点数を調べます。

#  " 01 "     
SELECT SId,score FROM SC
WHERE CId=01
#  " 02 "     
SELECT SId,score FROM SC
WHERE CId=02
#  " 01 "   " 02 "        
SELECT *
FROM 
(SELECT * FROM SC
WHERE CId='01') A
JOIN
(SELECT * FROM SC
WHERE CId='02') B
ON A.SId=B.SId
WHERE A.score>B.score

#      
SELECT A.SId,A.score class01,B.score class02
FROM (SELECT * FROM SC
WHERE CId='01') A
JOIN
(SELECT * FROM SC
WHERE CId='02') B
ON A.SId=B.SId
WHERE A.score>B.score

#  " 01 "   " 02 "                
SELECT * FROM 
`student` 
RIGHT JOIN 
(
SELECT A.SId,A.score class01,B.score class02
FROM (SELECT * FROM SC
WHERE CId='01') A
JOIN
(SELECT * FROM SC
WHERE CId='02') B
ON A.SId=B.SId
WHERE A.score>B.score
) C
ON `student`.`SId`=C.`SId`

1.1「01」コースと「02」コースが同時に存在する場合を検索する

#  01     
SELECT * FROM SC
WHERE CId='01';
#  02     
SELECT * FROM SC
WHERE CId='02'
 #      " 01 "   " 02 "     
SELECT * FROM 
 (SELECT * FROM SC
WHERE CId='01') a,
(SELECT * FROM SC
WHERE CId='02') b
WHERE a.`SId`=b.SId
#  
SELECT a.SId,a.CId CId01,a.score score01,b.CId CId02,b.score score02
FROM (SELECT * FROM SC
WHERE CId='01') a,
(SELECT * FROM SC
WHERE CId='02') b
WHERE a.`SId`=b.SId

1.2クエリに「01」コースがありますが、「02」コースが存在しない可能性があります。（存在しない場合はnullと表示されます。）

 #  " 01 "    
 SELECT * FROM `sc`
 WHERE `CId`='01'
 
 #  " 02 "    
 SELECT * FROM sc
 WHERE CId='02'
 
 #    " 01 "        " 02 "     
 SELECT * FROM 
 (SELECT * FROM `sc`
 WHERE `CId`='01') a
LEFT JOIN
( SELECT * FROM sc
 WHERE CId='02') b
 ON a.`SId`=b.SId

1.3クエリに「01」コースがないが、「02」コースがある場合

#  " 01 "       
SELECT `SId` FROM 
SC
WHERE `CId`=01
#  " 02 "       
SELECT `SId` FROM 
SC
WHERE `CId`=02

SELECT * FROM sc
WHERE SId NOT IN ( SELECT `SId` FROM 
SC
WHERE `CId`=01)
AND `CId`=02

2.平均成績が60点以上の学生の学生番号と学生の名前と平均成績を調べる。

#           60               
SELECT SId,AVG(`score`)
FROM `sc`
GROUP BY SId
HAVING AVG(`score`)>=60
#    ，           60                    
SELECT Student.`Sname`,a.SId,a.    
FROM `student`
RIGHT JOIN (SELECT `sc`.SId,AVG(`sc`.`score`)     
FROM `sc`
GROUP BY SId
HAVING AVG(`score`)>=60) a
ON `student`.`SId`=a.`SId`

3.SC表に成績がある学生情報を調べる

SELECT DISTINCT student.*
FROM student ,sc
WHERE student.SId=sc.SId

4.すべての学生の学生番号、学生名、選択総数、全コースの総成績（成績がない場合はnullと表示されます）を調べます。

#           、    、        
SELECT SId,COUNT(CId)     ,SUM(score)         
FROM SC
GROUP BY SId

SELECT Student.`SId`,Student.`Sname`,a.    ,a.        
FROM Student
LEFT JOIN (SELECT SId,COUNT(CId)     ,SUM(score)         
FROM SC
GROUP BY SId
) a
ON Student.`SId`=a.SId

4.1成績のある学生情報を調べる

SELECT DISTINCT `students`.*
FROM `students`
LEFT JOIN  SC
ON  SC.`SId`=Students.`SId`
WHERE score IS NOT NULL

または

SELECT *
FROM students
WHERE EXISTS(SELECT * FROM sc WHERE students.SId=sc.SId)

5.「李」という名前の先生の数を調べる

SELECT COUNT(*)
FROM Teacher
WHERE Teacher.Tname LIKE ' %'

6.「張三」先生の授業を受けた学生の情報を調べる

SELECT Student.* FROM
Teacher ,Course,SC,Student
WHERE Teacher.`TId`=Course.`TId`
AND Course.`CId`=SC.`CId`
AND SC.`SId`=Student.`SId`
AND Teacher.`Tname`='  '

*7.全コースを学んでいない学生の情報を調べる

SELECT DISTINCT student.*
FROM 
(SELECT student.`SId` ,course.`CId`
FROM `student`,Course) a
LEFT JOIN (SELECT SC.`SId`,sc.`CId`
FROM sc) b
ON a.`SId`=b.`SId`
AND a.`CId`=b.`CId`,student
WHERE b.SId IS NULL
AND   a.SId=student.SId

*8.少なくとも一つの授業と学名が「01」の学生が学んだのと同じクラスの情報を調べます。

SELECT DISTINCT student.*
FROM sc,student
WHERE cid IN (SELECT SC.CId FROM SC
WHERE SC.`SId`='01')
AND sc.SId=student.`SId`

*9.「01」の学生が勉強している授業と全く同じ他の学生の情報を調べる

SELECT DISTINCT Student.*
FROM (SELECT Student.`SId`,a.cid
FROM Student,(SELECT SC.CId FROM SC
WHERE SC.`SId`='01')a) b LEFT JOIN SC
ON b.`SId`=SC.`SId`
AND b.cid=sc.`CId`,student
WHERE sc.cid IS NULL 
AND b.SId=student.SId

10.「張三」先生が教えたどの科目の学生名を調べてください。

SELECT Student.* FROM Student
WHERE Student.`SId` NOT IN (SELECT SC.`SId` FROM 
SC,(SELECT Course.`CId` FROM 
Course,(SELECT Teacher.`TId` FROM  Teacher
WHERE Teacher.`Tname`='  ') a
WHERE Course.`TId`=a.`TId`) b
WHERE SC.`CId`=b.`CId`)

*11.2つ以上の不合格コースの学生番号、氏名及び平均成績を調べる

SELECT Student.`SId`,Student.`Sname`,AVG(SC.`score`)
FROM Student,SC
WHERE Student.`SId`=sc.`SId`
AND   sc.score<60
GROUP BY sc.SId
HAVING COUNT(*)>=2

12.「01」を検索する授業の点数が60以下で、点数順に並べた学生の情報

SELECT Student.*,A.SCORE FROM Student,
(SELECT SC.* FROM SC
WHERE CId='01'
AND score<60) A
WHERE Student.`SId`=A.`SId`
ORDER BY  A.SCORE DESC

13.平均成績で高から低まですべての学生の全課程の成績と平均成績を表示する

SELECT SC.`SId`,SC.`CId`,SC.`score`,B.a
FROM SC,(SELECT SC.`SId`,AVG(SC.`score`) a
FROM sc
GROUP BY SC.`SId`) B
WHERE SC.`SId`=B.`SId`
ORDER BY a DESC

14.各科目の成績の最高点、最低点と平均点を調べる：課程ID、課程name、最高点、最低点、平均点、合格率、中程度率、優良率、優秀率は>=60で、中ぐらいは70-80で、優秀なのは：80-90で、優秀なのは：>=90は出力課程番号と選択人数を要求して、検索結果は人数順に並べます。人数が同じなら、カリキュラム番号の昇順に並べます。

SELECT sc.CId ,Course.`Cname`,MAX(sc.score)AS    ,MIN(sc.score)AS    ,AVG(sc.score)AS    ,
COUNT(*)AS     ,
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    ,
SUM(CASE WHEN sc.score>=90 THEN 1 ELSE 0 END )/COUNT(*)AS     
FROM sc LEFT JOIN Course
ON sc.`CId`=course.`CId`
GROUP BY sc.CId
ORDER BY COUNT(*)DESC,sc.CId ASC

*15.各科の成績によって順位を並べ、スコアが重複した場合は席が空きます。

SELECT sc.CId ,@curRank:=@curRank+1 AS rank,sc.score
FROM (SELECT @curRank:=0) AS t ,sc
ORDER BY sc.score DESC

*15.1各科の成績によって順位を並べて表示し、スコアが重複した場合の連結順位

SELECT sc.`CId`,sc.`score`,
CASE WHEN @fontscore=score THEN @curRank
WHEN @fontscore:=score THEN @curRank:=@curRank+1
END AS rank
FROM (SELECT @fontscore := NULL,@curRank:=0) t,sc
ORDER BY sc.score DESC

*16.学生の総成績を調べて順位を決め、合計点が重複した場合は席が空きます。

SELECT a.*,
@curRank:=@curRank+1 AS rank
FROM (SELECT  sc.sid,SUM(score) AS scos
FROM sc
GROUP BY sc.sid
ORDER BY scos DESC) a,
(SELECT @curRank:=0) t

*16.1学生の総成績を調べて順位を決め、合計点が重複している場合は席が空いていない

SELECT a.*,
CASE WHEN @fontscore=a.scos THEN @curRank
WHEN @fontscore:=a.scos THEN @curRank:=@curRank+1
END AS rank
FROM (SELECT  sc.sid,SUM(score) AS scos
FROM sc
GROUP BY sc.sid
ORDER BY scos DESC) a,
(SELECT @sco=NULL ,@curRank:=0) t

*17.各科目の成績の各点数の人数を統計する：課程番号、課程名、[100-85]、[85-70]、[70-60]、[60-0]とパーセンテージを占めます。

SELECT Course.`Cname`,a.*
FROM (SELECT  SC.CId,
CONCAT(SUM(CASE WHEN score>=85 THEN 1 ELSE 0 END)/COUNT(*)*100,"%") AS '[85-100]',
CONCAT(SUM(CASE WHEN sc.score>=70 AND sc.score<85 THEN 1 ELSE 0 END)/COUNT(*)*100,"%") AS '[70-85]',
CONCAT(SUM(CASE WHEN score>=60 AND sc.score<70 THEN 1 ELSE 0 END)/COUNT(*)*100,"%") AS '[60-70]',
CONCAT(SUM(CASE WHEN score<60 THEN 1 ELSE 0 END)/COUNT(*)*100,"%") AS '[0-60]' 
FROM sc
GROUP BY sc.CId) a LEFT JOIN Course
ON a.CId=Course.`CId`

＊18.各科目の成績上位3位の記録を調べる

SELECT * FROM sc
WHERE (SELECT COUNT(*) FROM sc a 
WHERE sc.`CId`=a.`CId` AND sc.score

19.各コースごとに選択された学生数を調べる

select sc.CId,count(*)
from sc 
GROUP BY sc.CId

＊20.2科目のみ選択した学生の学名と名前を調べます。

SELECT SC.SId,Student.`Sname`
FROM SC LEFT JOIN Student
ON SC.`SId`=Student.`SId`
GROUP BY SC.`SId`
HAVING COUNT(*)=2

21.男子、女子の人数を調べる

SELECT student.Ssex ,COUNT(*) AS   
FROM student 
GROUP BY student.Ssex

22.名前に「風」の文字が含まれている学生情報を調べる

SELECT student.* FROM student
WHERE Student.`Sname` LIKE "% %"

*23.同名の同性学生リストを調べ、同名の学生数を統計する

SELECT *
FROM student LEFT JOIN (SELECT Sname,Ssex,COUNT(*)     FROM Student GROUP BY Sname,Ssex) AS t1
ON student.Sname =t1.Sname AND student.Ssex=t1.Ssex
WHERE t1.    >1

*24.1990年生まれの学生リストを調べる

SELECT * FROM student 
WHERE YEAR(student.`Sage`)=1990

25.各コースの平均成績を調べて、平均成績順に並べて、平均成績は同じで、課程番号によって昇順に並べます。

SELECT SC.`CId`,AVG(SC.`score`) AS      FROM sc
GROUP BY SC.`CId` 
ORDER BY      DESC,SC.`CId`

26.調査の平均成績は85に等しいすべての学生の学名、氏名、平均成績より大きい。

SELECT student.SId,student.Sname,t1.     
FROM student,(SELECT  SC.SId,AVG(SC.`score`)      FROM sc
GROUP BY SC.`SId`
HAVING     >=85) t1
WHERE student.`SId`=t1.`SId`

27.授業名は「数学」で、しかも点数が60以下の学生の名前と点数を調べます。

SELECT Student.`Sname`,t.`score`
FROM Student,(SELECT  SC.`SId`,SC.`score` FROM SC
WHERE SC.`CId` IN (SELECT Course.`CId` FROM Course
WHERE Course.`Cname`='  ')
AND SC.`score`<60)t
WHERE Student.`SId`=t.`SId`

28.すべての学生の授業と点数の状況を調べる（学生が成績がなくて、授業を選んでいない場合があります）

SELECT student.sid,SC.`CId`,sc.`score`
FROM student LEFT JOIN SC
ON student.`SId`=sc.`SId`

29.どの科目の成績が70点以上の名前、課程名と点数を調べます。

SELECT Student.`Sname`,Course.`Cname`,SC.`score`
FROM Student,SC,Course
WHERE Student.`SId`=SC.`SId`
AND SC.`CId`=course.`CId`
AND SC.`score`>=70

30.不合格の授業を調べる

SELECT DISTINCT SC.`CId`
FROM SC
WHERE SC.`score`<60

31.授業番号が01で、授業成績が80点以上の学生の学名と名前を調べます。

SELECT Student.`SId`,Student.`Sname`
FROM Student,SC
WHERE Student.`SId`=sc.`SId`
AND SC.`CId`='01'
AND SC.`score`>80

32.各コースの学生数を求めます。

SELECT SC.`CId`,COUNT(*)     
FROM SC
GROUP BY SC.`CId`

*33.成績は重複せず、「張三」先生が講義した学生の中で、成績が一番高い学生情報と成績を調べてください。

select student.*,sc.score
from student ,course ,teacher ,sc
where course.CId=sc.CId
and course.TId=teacher.TId
and teacher.Tname='  '
and student.SId =sc.SId
LIMIT 1

*34.成績が重複している場合、「張三」先生が講義した学生の中で、成績が一番高い学生情報と成績を調べる

SELECT Student.*,c.score
FROM Student,(SELECT a.SId,a.score, 
CASE WHEN @fontage=a.score THEN @rank 
WHEN @fontage:=a.score THEN @rank:=@rank+1 END  AS rank
FROM 
(SELECT Sc.SId,SC.`score`  
FROM sc,course ,teacher 
WHERE sc.CId=course.CId
AND course.TId=teacher.TId
AND teacher.Tname='  '
ORDER BY sc.score DESC) a,(SELECT @fontage:=NULL,@rank:=0) b) c
WHERE Student.`SId`=c.SId
AND c.rank=1

＊35.授業成績が同じ学生の番号、授業番号、学生の成績を調べる

SELECT DISTINCT a.sid,a.cid,a.score FROM sc a LEFT JOIN sc b
ON a.SId=b.SId
WHERE a.cid!=b.cid
AND a.score=b.score

*36.各仕事の成績が一番いい上位2名を調べる

SELECT * FROM sc a
WHERE
(SELECT COUNT(*) FROM sc b
WHERE a.CId=b.CId
AND b.score>a.score)<2
ORDER BY a.cid

37.各科目の学生選択人数を統計する（5人を超える授業は統計する）

SELECT SC.`CId`,COUNT(*)
FROM sc
GROUP BY SC.`CId`
HAVING COUNT(*)>5

*38.少なくとも二つの科目を履修した学生番号を検索する

SELECT sc.`SId`,COUNT(*) FROM sc
GROUP BY sc.`SId`
HAVING COUNT(*)>=2

39.全コースを選択した学生の情報を調べる

select student.*
from sc ,student 
where sc.SId=student.SId
GROUP BY sc.SId
HAVING count(*) = (select DISTINCT count(*) from course )

*40.各学生の年齢を調べ、年によって計算します。

select *, year(now())-year(sage) as age from student;

*41.生年月日により、現在の月日（生年月日の月日は、年齢が1.

SELECT *,TIMESTAMPDIFF(YEAR,sage,NOW())AS      
FROM student

＊42.今週誕生日の学生を調べます。

SELECT *, SUBSTR(YEARWEEK(student.Sage),5,2) AS birth_week, 
SUBSTR(YEARWEEK(CURDATE()),5,2) AS now_week 
FROM student 
HAVING SUBSTR(YEARWEEK(student.Sage),5,2)=SUBSTR(YEARWEEK(CURDATE()),5,2);

＊43.来週誕生日の学生を調べます。

select     *,    substr(YEARWEEK(student.Sage),5,2) as birth_week,    substr(YEARWEEK(CURDATE()),5,2) as now_week from student having substr(YEARWEEK(student.Sage),5,2)=        substr(YEARWEEK(CURDATE()),5,2)+1;

*44.今月誕生日の学生を調べます。

select *, month(sage) as birth_month, month(now()) as now_month from student where month(sage)=month(now());

*45.来月誕生日の学生を調べます。

select *, month(sage) as birth_month, month(now()) as now_month from student where month(sage)=month(now())+1

elasticsearchは重合後の2つのフィールドの相殺、加算、相乗、演算を実現します。

NSManagedObject Context