mysqlクエリー文面接問題

テーブル構造

学生表student(id,name)

カリキュラムcourse(id,name)

学生カリキュラムstudent_course(sid,cid,score)

テーブルのsqlコードの作成

create table student(
id int unsigned primary key auto_increment,
name char(10) not null
);
insert into student(name) values('  '),('  ');

create table course(
id int unsigned primary key auto_increment,
name char(20) not null
);
insert into course(name) values('  '),('  ');

create table student_course(
sid int unsigned,
cid int unsigned,
score int unsigned not null,
foreign key (sid) references student(id),
foreign key (cid) references course(id),
primary key(sid, cid)
);
insert into student_course values(1,1,80),(1,2,90),(2,1,90),(2,2,70);

質問:

studentテーブルの重複する学生をクエリーし、結果はidとnameを含み、name、id昇順

select id,name
from student
where name in (
select name from student group by name having(count(*) > 1)
) order by name;

    2.student_courseテーブルで平均点が不合格の学生を検索し、学生idと平均点をリストします.

select sid,avg(score) as avg_score
from student_course
group by sid having(avg_score<60);

group byとhavingは最もよく試験されます.where句では条件式として集約関数を使用することはできませんが、havingフレーズは可能です.whereとhavingの違いは、オブジェクトが異なること、whereが記録に作用し、havingがグループに作用することです.
    3.student_courseテーブルでは、各授業の成績が80を下回らない学生idを検索します.

select distinct sid
from student_course
where sid not in (
select sid from student_course
where score < 80);

   4.各学生の総成績を調べると、学生の名前と総成績がリストされます.次のsqlを使用すると、成績のない人がフィルタされます.

select name,sum(score) total
from student,student_course
where student.id=student_course.sid
group by sid;

  5.総成績が最も高い学生は、結果として学生idと総成績の下のsqlをリストする効率が低い.すべての総成績を繰り返し計算するためだ.

select sid,sum(score) as sum_score
from student_course group by sid
order by sum_score desc limit 1;

  

select sid,sum(score) as sum_score
from student_course group by sid having sum_score>=all
(select sum(score) from student_course group by sid);

   6.student_course表はカリキュラム1の成績が2番目に高い学生を検索し、2番目に高い学生が1つ以上であればすべての学生をリストします.

select score from student_course where cid = 1 group by score order by score desc limit 1,1;


select * from student_course
where cid=1 and score = (
select score from student_course where cid = 1 group by score order by score desc limit 1,1
);

   7.student_course表は各科の成績が最も高い学生を検索し、結果は学生id、課程idと対応する成績をリストします.

select * from student_course as x where score>=
(select max(score) from student_course as y where cid=x.cid);

関連するネストされたクエリーは、内層クエリーを行う際に外層クエリーを使用する必要があります.いくつかの注意点があります.

サブクエリにはカッコ

が必要です.

asは

を省略することができる.

関連クエリーを使用します.>=maxは>=allに等しいが、集約関数はanyまたはallを用いるよりも

効率が高い.
   8.student_courseテーブルでは、各レッスンの上位2名をクエリーし、結果はカリキュラムidで昇順し、同じカリキュラムは成績で降順します.

select * from student_course x where
2>(select count(*) from student_course y where y.cid=x.cid and y.score>x.score)
order by cid,score desc;

これも関連するネストされたクエリーです.各スコアについて、同じカリキュラムで0個、1個のスコアがこのスコアよりも高い場合、このスコアは上位2位の1つに違いありません.
   9.チームという表で、中には1つのフィールドnameしかなく、全部で4つの記録があります.それぞれa,b,c,dで、4つのチームに対応して、2つの試合を行い、1つのsql文ですべての可能な試合の組み合わせを表示します.

select a.name, b.name
from team a, team b
where a.name < b.name

   10.
テーマ:データベースにsalesというテーブルがあります.
年
四半期
販売
1991
1
11
1991
2
12
1991
3
13
1991
4
14
1992
1
21
1992
2
22
1992
3
23
1992
4
24
要求:SQL文を書くと、次のような結果が得られます.
年
第1四半期
第2四半期
第3四半期
四半期
1991
11
12
13
14
1992
21
22
23
24
 

select  , 
sum(case when   =1 then     else 0 end) as    , 
sum(case when   =2 then     else 0 end) as    , 
sum(case when   =3 then     else 0 end) as    , 
sum(case when   =4 then     else 0 end) as     
from sales group by  ;

同様に、一人一人の授業の成績を調べるには、以下のsqlを使用します.

create view temp as select student.name as sname,course.name as cname,score
from student_course join (student,course)
on(student_course.sid=student.id and student_course.cid=course.id)
;
select sname,
sum(case when cname='  ' then score else 0 end) as   ,
sum(case when cname='  ' then score else 0 end) as   
from temp
group by sname;