MySQL経典の練習問題と解答、常用SQL文の練習50題
15510 ワード
テーブル名とフィールド
–1.学生表Student(s_id,s_name,s_birth,s_sex)-学生番号、学生名、生年月日、学生性別–2.カリキュラムCourse(c_id,c_name,t_id)-カリキュラム番号、カリキュラム名、教師番号-3.教師表Teacher(t_id,t_name)-教師番号、教師名–4.成績表Score(s_id,c_id,s_score)-学生番号、カリキュラム番号、スコア
テストデータ
--
--
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--
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('08' , ' ' , '1990-01-20' , ' ');
--
insert into Course values('01' , ' ' , '02');
insert into Course values('02' , ' ' , '01');
insert into Course values('03' , ' ' , '03');
--
insert into Teacher values('01' , ' ');
insert into Teacher values('02' , ' ');
insert into Teacher values('03' , ' ');
--
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);練習問題とsql文
-- 1、 "01" "02"
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a
join score b on a.s_id=b.s_id and b.c_id='01'
left join score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL where b.s_score>c.s_score
-- 2、 "01" "02"
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a left join score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL
join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score=60;
-- 4、 60
-- ( )
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
student b
left join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)<60
union
select a.s_id,a.s_name,0 as avg_score from
student a
where a.s_id not in (
select distinct s_id from score);
-- 5、 、 、 、
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from
student a
left join score b on a.s_id=b.s_id
GROUP BY a.s_id,a.s_name;
-- 6、 " "
select count(t_id) from teacher where t_name like ' %';
-- 7、 " "
select a.* from
student a
join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = ' '));
-- 8、 " "
select * from
student c
where c.s_id not in(
select a.s_id from student a join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = ' ')));
-- 9、 "01" "02"
select a.* from
student a,score b,score c
where a.s_id = b.s_id and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';
-- 10、 "01" "02"
select a.* from
student a
where a.s_id in (select s_id from score where c_id='01' ) and a.s_id not in(select s_id from score where c_id='02')
-- 11、
select s.* from
student s where s.s_id in(
select s_id from score where s_id not in(
select a.s_id from score a
join score b on a.s_id = b.s_id and b.c_id='02'
join score c on a.s_id = c.s_id and c.c_id='03'
where a.c_id='01'))
-- 12、 "01"
select * from student where s_id in(
select distinct a.s_id from score a where a.c_id in(select a.c_id from score a where a.s_id='01')
);
-- 13、 "01"
select a.* from student a where a.s_id in(
select distinct s_id from score where s_id!='01' and c_id in(select c_id from score where s_id='01')
group by s_id
having count(1)=(select count(1) from score where s_id='01'));
-- 14、 " "
select a.s_name from student a where a.s_id not in (
select s_id from score where c_id =
(select c_id from course where t_id =(
select t_id from teacher where t_name = ' '))
group by s_id);
-- 15、 ，
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from
student a
left join score b on a.s_id = b.s_id
where a.s_id in(
select s_id from score where s_score<60 GROUP BY s_id having count(1)>=2)
GROUP BY a.s_id,a.s_name
-- 16、 "01" 60，
select a.*,b.c_id,b.s_score from
student a,score b
where a.s_id = b.s_id and b.c_id='01' and b.s_score<60 ORDER BY b.s_score DESC;
-- 17、
select a.s_id,(select s_score from score where s_id=a.s_id and c_id='01') as ,
(select s_score from score where s_id=a.s_id and c_id='02') as ,
(select s_score from score where s_id=a.s_id and c_id='03') as ,
round(avg(s_score),2) as from score a GROUP BY a.s_id ORDER BY DESC;
-- 18. 、 ： ： ID， name， ， ， ， ， ， ，
-- >=60， ：70-80， ：80-90， ：>=90
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as ,
ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as ,
ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as ,
ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name
-- 19、 ， ( )
-- mysql rank
select a.s_id,a.c_id,
@i:=@i +1 as i ,
@k:=(case when @score=a.s_score then @k else @i end) as rank ,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id='01' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id='02' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id='03' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
-- 20、
select a.s_id,
@i:=@i+1 as i,
@k:=(case when @score=a.sum_score then @k else @i end) as rank,
@score:=a.sum_score as score
from (select s_id,SUM(s_score) as sum_score from score GROUP BY s_id ORDER BY sum_score DESC)a,
(select @k:=0,@i:=0,@score:=0)s
-- 21、
select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score from course a
left join score b on a.c_id=b.c_id
left join teacher c on a.t_id=c.t_id
GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC;
-- 22、 2 3
select d.*,c. ,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@i:=@i+1 as from score a,(select @i:=0)s where a.c_id='01'
)c
left join student d on c.s_id=d.s_id
where BETWEEN 2 AND 3
UNION
select d.*,c. ,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@j:=@j+1 as from score a,(select @j:=0)s where a.c_id='02'
)c
left join student d on c.s_id=d.s_id
where BETWEEN 2 AND 3
UNION
select d.*,c. ,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@k:=@k+1 as from score a,(select @k:=0)s where a.c_id='03'
)c
left join student d on c.s_id=d.s_id
where BETWEEN 2 AND 3;
-- 23、 ： , ,[100-85],[85-70],[70-60],[0-60]
select distinct f.c_name,a.c_id,b.`85-100`,b. ,c.`70-85`,c. ,d.`60-70`,d. ,e.`0-60`,e. from score a
left join (select c_id,SUM(case when s_score >85 and s_score <=100 then 1 else 0 end) as `85-100`,
ROUND(100*(SUM(case when s_score >85 and s_score <=100 then 1 else 0 end)/count(*)),2) as
from score GROUP BY c_id)b on a.c_id=b.c_id
left join (select c_id,SUM(case when s_score >70 and s_score <=85 then 1 else 0 end) as `70-85`,
ROUND(100*(SUM(case when s_score >70 and s_score <=85 then 1 else 0 end)/count(*)),2) as
from score GROUP BY c_id)c on a.c_id=c.c_id
left join (select c_id,SUM(case when s_score >60 and s_score <=70 then 1 else 0 end) as `60-70`,
ROUND(100*(SUM(case when s_score >60 and s_score <=70 then 1 else 0 end)/count(*)),2) as
from score GROUP BY c_id)d on a.c_id=d.c_id
left join (select c_id,SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end) as `0-60`,
ROUND(100*(SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end)/count(*)),2) as
from score GROUP BY c_id)e on a.c_id=e.c_id
left join course f on a.c_id = f.c_id
-- 24、
select a.s_id,
@i:=@i+1 as ' ',
@k:=(case when @avg_score=a.avg_s then @k else @i end) as ' ',
@avg_score:=avg_s as ' '
from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id)a,(select @avg_score:=0,@i:=0,@k:=0)b;
-- 25、
-- 1. b a
-- 2. id
select a.s_id,a.c_id,a.s_score from score a
left join score b on a.c_id = b.c_id and a.s_score=85
-- 34、 " "， 60
select a.s_name,b.s_score from score b LEFT JOIN student a on a.s_id=b.s_id where b.c_id=(
select c_id from course where c_name =' ') and b.s_score<60
-- 35、 ；
select a.s_id,a.s_name,
SUM(case c.c_name when ' ' then b.s_score else 0 end) as ' ',
SUM(case c.c_name when ' ' then b.s_score else 0 end) as ' ',
SUM(case c.c_name when ' ' then b.s_score else 0 end) as ' ',
SUM(b.s_score) as ' '
from student a left join score b on a.s_id = b.s_id
left join course c on b.c_id = c.c_id
GROUP BY a.s_id,a.s_name
-- 36、 70 、 ；
select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id = c.c_id
left join student a on a.s_id=c.s_id where c.s_score>=70
-- 37、
select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course b on a.c_id = b.c_id
where a.s_score<60
--38、 01 80 ；
select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id = b.s_id
where a.c_id = '01' and a.s_score>80
-- 39、
select count(*) from score GROUP BY c_id;
-- 40、 " " ，
-- id
select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name=' '
-- （ ）
select MAX(s_score) from score where c_id='02'
--
select a.*,b.s_score,b.c_id,c.c_name from student a
LEFT JOIN score b on a.s_id = b.s_id
LEFT JOIN course c on b.c_id=c.c_id
where b.c_id =(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name=' ')
and b.s_score in (select MAX(s_score) from score where c_id='02')
-- 41、 、 、
select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score
-- 42、
--
select a.s_id,a.c_id,a.s_score from score a
where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id
-- 43、 （ 5 ）。 ， ， ，
select c_id,count(*) as total from score GROUP BY c_id HAVING total>5 ORDER BY total,c_id ASC
-- 44、
select s_id,count(*) as sel from score GROUP BY s_id HAVING sel>=2
-- 45、
select * from student where s_id in(
select s_id from score GROUP BY s_id HAVING count(*)=(select count(*) from course))
--46、
-- ， < ，
select s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') -
(case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') then 0 else 1 end)) as age
from student;
-- 47、
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)
select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
select WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
-- 48、
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =WEEK(s_birth)
-- 49、
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)
-- 50、
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)