練習問題

今までやってきたことをアウトプットして知識を定着していこう!!

練習問題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が降順で5番目まで」とし、

ターミナルに表示する。

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)を使用して、2つのテーブルに共通するデータを抽出しターミナルに表示する。

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