練習問題
今までやってきたことをアウトプットして知識を定着していこう!!
練習問題61:データの集計処理をしてみよう!③
条件:練習問題46で使用したテーブルを使用する。
「users」テーブルの「score」の最大値と最小値をターミナルに表示する。
SQL文
drop table if exists users;
create table users (
id int unsigned primary key auto_increment,
name varchar(20) unique,
score float
);
insert into users (name, score) values
('apple',150),
('orange',200),
('banana',100),
('melon',1000),
('strawberry',300),
('grape',350);
select max(score) from users;
select min(score) from users;
ターミナル文
\. ./myhonki.sql
練習問題62:グループ毎の合計を表示してみよう!
条件:練習問題56で使用したテーブルを使用する。「users_team」テーブルを表示する。
その後「team」のカラム名でグループ化し、「team」毎の「score」の合計をターミナルに表示する。
SQL文
drop table if exists users;
create table users (
id int unsigned primary key auto_increment,
name varchar(20) unique,
score float
);
insert into users (name, score) values
('apple',150),
('orange',200),
('banana',100),
('melon',1000),
('strawberry',300),
('grape',350);
drop table if exists users_team;
create table users_team as
select
id,
name,
score,
case
when score > 800 then 'A'
when score > 250 then 'B'
else 'C'
end as team
from users;
select * from users_team;
select sum(score),team from users_team group by team;
ターミナル文
\. ./myhonki.sql
練習問題63:グループ毎の合計を表示してみよう!②
条件:練習問題62で使用した「users_team」テーブルを使用する。
「team」毎の合計が「650」以上だけのデータを抽出し、ターミナルに表示する。
SQL文
drop table if exists users;
create table users (
id int unsigned primary key auto_increment,
name varchar(20) unique,
score float
);
insert into users (name, score) values
('apple',150),
('orange',200),
('banana',100),
('melon',1000),
('strawberry',300),
('grape',350);
drop table if exists users_team;
create table users_team as
select
id,
name,
score,
case
when score > 800 then 'A'
when score > 250 then 'B'
else 'C'
end as team
from users;
select * from users_team;
select sum(score),team from users_team group by team having sum(score) >= 650;
ターミナル文
\. ./myhonki.sql
練習問題64:サブクエリを使ってみよう！
条件:練習問題62で使用した「users_team」テーブルを使用し、
練習問題62の内容を「サブクエリ」に置き換えてターミナルに表示する。
SQL文
drop table if exists users;
create table users (
id int unsigned primary key auto_increment,
name varchar(30) unique,
score float
);
insert into users (name, score) values
('apple',150),
('orange',200),
('banana',100),
('melon',1000),
('Strawberry',300),
('grape',350);
drop table if exists users_team;
create table users_team as
select
id,
name,
score,
case
when score > 800 then 'A'
when score > 250 then 'B'
else 'C'
end as team
from
users;
select
sum(t.score),
t.team
from
(select
id,
name,
score,
case
when score > 800 then 'A'
when score > 250 then 'B'
else 'C'
end as team
from
users) as t
group by t.team;
ターミナル文
\. ./myhonki.sql
練習問題65:viewで抽出条件を保存してみよう!
「users」テーブルを作成する。カラムは、「id」「name」「score」。
レコードは、「apple」「150」、「orange」「200」、「banana」「100」、「melon」「1000」、「Strawberry」「300」、「grape」「350」と順に挿入する。
その後、「top5」という名前の「view」を作成し、抽出条件を「scoreが降順で５番目まで」とし、
ターミナルに表示する。
SQL文
drop table if exists users;
create table users (
id int unsigned primary key auto_increment,
name varchar(30) unique,
score float
);
insert into users (name, score) values
('apple',150),
('orange',200),
('banana',100),
('melon',1000),
('Strawberry',300),
('grape',350);
drop view if exists top5;
create view top5 as select * from users order by score desc limit 5;
select * from top5;
ターミナル文
\. ./myhonki.sql
練習問題66:トランザクションを使ってみよう!
練習問題65の「users」テーブルを使用する。
トランザクションを使用して「orange」の「score」を「banana」の「score」へ「50」移動し、ターミナルに表示する。
SQL文
drop table if exists users;
create table users (
id int unsigned primary key auto_increment,
name varchar(30) unique,
score float
);
insert into users (name, score) values
('apple',150),
('orange',200),
('banana',100),
('melon',1000),
('Strawberry',300),
('grape',350);
start transaction;
update users set score = score - 50 where name = 'orange';
update users set score = score + 50 where name = 'banana';
commit;
ターミナル文
\. ./myhonki.sql
練習問題67:索引を設定してみよう!
「users」テーブルを作成し、「score」に索引設定をする。索引名は「index_score」とする。
その後、「users」テーブルの索引一覧をターミナルに表示する。
SQL文
drop table if exists users;
create table users (
id int unsigned primary key auto_increment,
name varchar(30) unique,
score float
);
alter table users add index index_score (score);
show index from users;
ターミナル文
\. ./myhonki.sql
練習問題68:複数のテーブルを作成してみよう!
①「posts」テーブルを作成する。
内容条件:「id」は、int型「title」は、varchar型 「body」は、text型
②「comments」テーブルを作成する。
内容条件:「id」は、int型「posts_id」は、int型 「comment」は、text型
③「posts」テーブルに次の内容のレコードを挿入する。
「title1」「body1」、「title2」「body2」、「title3」「body3」
④「comments」テーブルに次の内容のレコードを挿入する。
「1」「初めのコメント」、「2」「二番目のコメント」、「3」「三番目のコメント」、「4」「四番目のコメント」
⑤「posts」「comments」テーブル内容をターミナルに表示する。
SQL文
drop table if exists posts;
create table posts(
id int unsigned primary key auto_increment,
title varchar(255),
comment text
);
drop table if exists comments;
create table comments(
id int unsigned primary key auto_increment,
posts_id int not null,
comment text
);
insert into posts (title,body) values ('title1','body1');
insert into posts (title,body) values ('title2','body2');
insert into posts (title,body) values ('title3','body3');
insert into comments (posts_id,comment) values(1,'初めてのコメント');
insert into comments (posts_id,comment) values(2,'二番目のコメント');
insert into comments (posts_id,comment) values(3,'三番目のコメント');
insert into comments (posts_id,comment) values(4,'四番目のコメント');
select * from posts;
select * from comments;
ターミナル文
\. ./myhonki.sql
練習問題69:内部結合でデーターを抽出してみよう!
練習問題68で作成したテーブルを使用する。
内部結合(inner join)を使用して、２つのテーブルに共通するデータを抽出しターミナルに表示する。
SQL文
drop table if exists posts;
create table posts(
id int unsigned primary key auto_increment,
title varchar(255),
comment text
);
drop table if exists comments;
create table comments(
id int unsigned primary key auto_increment,
posts_id int not null,
body text
);
insert into posts (title,body) values ('title1','body1');
insert into posts (title,body) values ('title2','body2');
insert into posts (title,body) values ('title3','body3');
insert into comments (posts_id,comment) values(1,'初めてのコメント');
insert into comments (posts_id,comment) values(2,'二番目のコメント');
insert into comments (posts_id,comment) values(3,'三番目のコメント');
insert into comments (posts_id,comment) values(4,'四番目のコメント');
select * from posts;
select * from comments;
select * from posts inner join comments on posts.id = comments.posts_id;
ターミナル文
\. ./myhonki.sql
練習問題70:外部結合でデータを抽出してみよう!①
練習問題68で作成したテーブルを使用する。
内部結合(left outer join)を使用して、データを抽出しターミナルに表示する。
SQL文
drop table if exists posts;
create table posts(
id int unsigned primary key auto_increment,
title varchar(255),
comment text
);
drop table if exists comments;
create table comments(
id int unsigned primary key auto_increment,
posts_id int not null,
body text
);
insert into posts (title,body) values ('title1','body1');
insert into posts (title,body) values ('title2','body2');
insert into posts (title,body) values ('title3','body3');
insert into comments (posts_id,comment) values(1,'初めてのコメント');
insert into comments (posts_id,comment) values(2,'二番目のコメント');
insert into comments (posts_id,comment) values(3,'三番目のコメント');
insert into comments (posts_id,comment) values(4,'四番目のコメント');
select * from posts;
select * from comments;
select * from posts left outer join comments on posts.id = comments.posts_id;
ターミナル文
\. ./myhonki.sql
練習問題71:外部結合でデータを抽出してみよう!②
練習問題68で作成したテーブルを使用する。
内部結合(right outer join)を使用して、データを抽出しターミナルに表示する。
SQL文
drop table if exists posts;
create table posts(
id int unsigned primary key auto_increment,
title varchar(255),
body text
);
drop table if exists comments;
create table comments(
id int unsigned primary key auto_increment,
posts_id int not null,
body text
);
insert into posts (title,body) values ('title1','body1');
insert into posts (title,body) values ('title2','body2');
insert into posts (title,body) values ('title3','body3');
insert into comments (posts_id,body) values(1,'初めてのコメント');
insert into comments (posts_id,body) values(2,'二番目のコメント');
insert into comments (posts_id,body) values(3,'三番目のコメント');
insert into comments (posts_id,body) values(4,'四番目のコメント');
select * from posts;
select * from comments;
select * from posts right outer join comments on posts.id = comments.posts_id;
ターミナル文
\. ./myhonki.sql
練習問題72:外部キー制約を使ってみよう!
練習問題68で作成したテーブルを使用する。
データの整合性をとる為に「comments」テーブルに外部キー制約を行い、
「posts」「comments」テーブルの内容をターミナルに表示する。
その後、次の練習問題の為に外部キー設定を削除する。
SQL文
drop table if exists posts;
create table posts(
id int unsigned primary key auto_increment,
title varchar(255),
body text
);
drop table if exists comments;
create table comments(
id int unsigned primary key auto_increment,
posts_id int unsigned not null,
comment text
);
alter table comments add constraint fk_comments foreign key (posts_id) references posts (id);
insert into posts (title,body) values ('title1','body1');
insert into posts (title,body) values ('title2','body2');
insert into posts (title,body) values ('title3','body3');
insert into comments (posts_id,comment) values(1,'初めてのコメント');
insert into comments (posts_id,comment) values(2,'二番目のコメント');
insert into comments (posts_id,comment) values(3,'三番目のコメント');
insert into comments (posts_id,comment) values(4,'四番目のコメント');
select * from posts;
select * from comments;
select * from posts right outer join comments on posts.id = comments.posts_id;
ターミナル文
\. ./myhonki.sql
alter table comments drop foreign key fk_comments;
練習問題73:「last_insert_id()」を使ってみよう!
練習問題68で作成したテーブルを使用する。
「posts」テーブルの「id」の「2」を削除し、同じテーブルに新たに「title4」と「body4」を挿入する。
「comments」テーブルに「posts_id」連番で「comment」に「四番目のコメント」を挿入する。
その後、「posts」「commnets」のテーブルの内容をターミナルに表示する。
SQL文
drop table if exists posts;
create table posts(
id int unsigned primary key auto_increment,
title varchar(255),
comment text
);
drop table if exists comments;
create table comments(
id int unsigned primary key auto_increment,
posts_id int unsigned not null,
comment text
);
insert into posts (title,body) values ('title1','body1');
insert into posts (title,body) values ('title2','body2');
insert into posts (title,body) values ('title3','body3');
insert into comments (posts_id,comment) values(1,'初めてのコメント');
insert into comments (posts_id,comment) values(2,'二番目のコメント');
insert into comments (posts_id,comment) values(3,'三番目のコメント');
delete from posts where id = 2;
insert into posts (title,body) values ('title4','body4');
insert into comments (posts_id,comment) values(last_insert_id(),'四番目のコメント');
select * from posts;
select * from comments;
ターミナル文
\. ./myhonki.sql
練習問題74:トリガーを使ってみよう!
練習問題68で作成したテーブルを使用し、「comments」テーブルは削除する。
「posts」テーブルにデータが挿入された際にログ残るように「logs」テーブルを作成し、
トリガー名は「posts_insert_trigger」とし「posts」「log」テーブルの内容をターミナルに表示する。
SQL文
drop table if exists posts;
create table posts(
id int unsigned primary key auto_increment,
title varchar(255),
comment text
);
drop table if exists logs;
create table logs(
id int unsigned primary key auto_increment,
msg varchar(255)
);
drop trigger if exists posts_insert_trigger;
create trigger posts_insert_trigger after insert on posts for each row insert into logs (msg) values ('post added!');
insert into posts (title,body) values ('title1','body1');
insert into posts (title,body) values ('title2','body2');
insert into posts (title,body) values ('title3','body3');
select * from posts;
select * from logs;
ターミナル文
\. ./myhonki.sql
練習問題75:挿入/更新時刻でレコードを更新しよう!
練習問題68で作成したテーブルを使用する。「comments」テーブルに関するものは削除する。
「posts」テーブルに作成日時と更新日時を追加して「posts」テーブルの内容をターミナルに表示する。
SQL文
drop table if exists posts;
create table posts(
id int unsigned primary key auto_increment,
title varchar(255),
comment text,
created datetime default current_timestamp,
updated datetime default current_timestamp on update
current_timestamp
);
insert into posts (title,body) values ('title1','body1');
insert into posts (title,body) values ('title2','body2');
insert into posts (title,body) values ('title3','body3');
select * from posts;
ターミナル文
\. ./myhonki.sql
練習問題76:日時の計算をしてみよう!
練習問題75で作成したテーブルを使用する。
「created」と「created」に3週間足した日時をターミナルに表示する。
SQL文
drop table if exists posts;
create table posts(
id int unsigned primary key auto_increment,
title varchar(255),
comment text,
created datetime default current_timestamp,
updated datetime default current_timestamp on update
current_timestamp
);
insert into posts (title,body) values ('title1','body1');
insert into posts (title,body) values ('title2','body2');
insert into posts (title,body) values ('title3','body3');
select created, date_add(created, interval 3 week) from posts;
ターミナル文
\. ./myhonki.sql