MySQLデータベース文練習(参考答案付)

一、テストデータテーブル

①テストデータ

学生表1.1 student(Sid,Sname,Sage,Ssex)1.2 Sid学生番号、Sname学生名前、Sage生年月日、Ssex学生性別

カリキュラム2.1 course(Cid,Cname,Tid)2.2 Cidカリキュラム番号、Cnameカリキュラム名、Tid教師番号

教師表3.1 teacher(Tid,Tname)3.2 Tid教師番号、Tname教師名前

成績表4.1 sc(Sid,Cid,score)4.2 Sid学生番号,Cid課程番号,score点数

②試験要求

必要に応じて以上の4枚の表を作成し、対応するテストデータを追加し、テストデータは以下の通りである.

学生表

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('08' , ' ' , '1990-01-20' , ' ');

カリキュラム

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);

二、sql文の練習

「01「課程比」02」課程の成績が高い学生の情報と課程点数

を照会する

学号「01」を修正した国語の成績は100

である.

クエリー「01カリキュラムと02」カリキュラムが同時に存在する場合

平均成績が70点以上の学生番号と名前を検索する

照会平均成績が85以上のすべての学生の学号、氏名、平均成績

は、カリキュラムの成績が70点以上の名前、カリキュラム名、スコア

をクエリーします.

1990年生まれの学生リスト

を検索

各課程の学生の人数の

を求めます

男子、女子の人数を調べます

名前に「風」の文字が含まれている学生情報を検索

三、参考答案

「01「課程比」02」課程の成績が高い学生の情報と課程点数

を照会する

select a.*, b.*, c.* from (select * from sc where Cid = '01') a
left join (select * from sc where Cid = '02') b
on a.Sid = b.Sid
left join student c
on a.Sid = c.Sid
where a.score > b.score;

学号「01」を修正した国語の成績は100

である.

update sc set score=100 where Sid="01" and Cid=(select  Cid from course where Cname=" ");

クエリー「01カリキュラムと02」カリキュラムが同時に存在する場合

select a.*, b.* from (select * from sc where Cid = '01') a
left join (select * from sc where Cid = '02') b
on a.Sid = b.Sid
where b.Sid is not null;

平均成績が70点以上の学生番号と名前を検索する

select Sid, Sname  from student where Sid in 
(select Sid from sc group by Sid having avg( score) >= 70);

照会平均成績が85以上のすべての学生の学号、氏名、平均成績

select a.Sid, a.score, b.Sname from (
select Sid, avg(score) as score from sc group by Sid having avg(score) >= 85)a
left join student b
on a.Sid = b.Sid;

は、カリキュラムの成績が70点以上の名前、カリキュラム名、スコア

をクエリーします.

select a.Sid, b.Sname, c.Cname, a.score from sc a
left join student b
on a.Sid = b.Sid
left join course c
on a.Cid = c.Cid
where score > 70;

1990年生まれの学生リスト

を検索

select * from student where year(Sage) = 1990;

各課程の学生の人数の

を求めます

select Cid, count(distinct Sid) as counts from sc group by C

男子、女子の人数を調べます

select Ssex, count(distinct Sid) as counts from student group by Ssex;

名前に「風」の文字が含まれている学生情報を検索

select * from student where Sname like '% %';