create table customers(
id int primary key auto_increment,
name varchar(255),
age int (255),
email varchar (255),
address varchar (255)
);

create table traders(
id int primary key auto_increment,
name varchar(255),
address varchar (255)
);

create table goods(
id int primary key auto_increment,
name varchar(255),
price int
);

create table purchases(
id int primary key auto_increment,
customer_id int,
time datetime
);

create table goods_traders(
id int primary key auto_increment,
good_id int,
trader_id int
);

create table goods_purchases_traders(
id int primary key auto_increment,
goods_trader_id int,
purchase_id int
);

insert into customers (name,age,email,address) values ('maeda','21','maeda@gmail.com','tokyo'),
('kimura','33','kimura@gmail.com','nara'),('nomura','35','nomura@yahoo.co.jp','hyogo');

insert into traders (name,address) values ('mitsubishi','tokyo'),('mitsui','saitama');

insert into goods (name,price) values ('apple','400'),('banana','200'),('orange','300');

insert into goods_traders (good_id,trader_id) values (1,1),(1,2),(2,2),(3,1);

insert into purchases (customer_id,time) values (1,'2017-04-27 11:00:00'),(1,'2017-04-27 11:00:00'),(2,'2017-04-27 11:00:00'),(1,'2017-04-27 12:00:00'),(3,'2017-04-28 11:00:00'),(2,'2017-04-29 11:00:00');

insert into goods_purchases_traders (goods_trader_id,purchase_id) values (1,1),(2,2);

1.
select id,time from purchases where customer_id =1 order by time;
select goods_trader_id from goods_purchases_traders where purchase_id in ○ and ●;
select good_id from goods_traders where id = △;
select * from goods where id = □;

2.
select purchase_id from purchases where purchase_time is '2015-06-01';
select good_trader_id from goods_purchases_traders where purchase_id between ○ and ●;
select good_id from goods_traders where good_trader_id = △;
select * from goods where good_id = □;

3.
select trader_id from goods_traders where good_id =1;
select * from traders where trader_id = ○;

4.
select good_id from goods_traders where trader_id = 1;
select * from goods where good_id = ○;



--------------------------------------------------------------------------------------


create table classes(
id int auto_increment primary key,
name varchar(255)
);

create table students(
id int auto_increment primary key,
number int (255),
password varchar (255)
);

create table majors(
id int auto_increment primary key,
name varchar (255)
);

create table rooms(
id int auto_increment primary key,
name varchar (255)
);

create table teachers(
id int auto_increment primary key,
name varchar (255)
);

create table schedules(
id int auto_increment primary key,
day varchar(255),
number int (255)
);

create table classes_rooms(
id int auto_increment primary key,
class_id int (255),
room_id int (255)
);

create table classes_majors(
id int auto_increment primary key,
class_id int (255),
major_id int (255)
);

create table classes_students(
id int auto_increment primary key,
class_id int (255),
student_id int (255)
);

create table classes_teachers(
id int auto_increment primary key,
class_id int (255),
teacher_id int (255)
);

create table classes_schedules(
id int auto_increment primary key,
class_id int (255),
schedule_id int (255)
);


insert into classes (name) values ('suugaku1'),('suugaku2'),('suugaku3'),('seimei1'),('seimei2'),('seimei3');

insert into students(number,password) values
(560001,1111),(560002,2222),(560003,3333),(560004,4444),(560005,5555);

insert into majors(name) values
('ri1'),('ri2'),('ri3'),('bun1'),('bun2'),('bun3');

insert into rooms(name) values
(101),(102),(103),(104),(105),(106);

insert into teachers(name) values
('Maeda'),('Wada'),('Yoshimura'),('Minoda');

insert into schedules(day,number) values
('Monday','1'),('Tuesday','2'),('Wednesday','3'),('Monday','4'),('Tuesday','1'),('Wednesday','2'),('Friday','3'),('Friday','4');

insert into classes_rooms(class_id,room_id) values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

insert into classes_majors(class_id,major_id) values (1,1),(1,2),(2,1),(2,2),(2,3),(2,4),(3,3),(4,4),(5,5),(6,6);

insert into classes_students(class_id,student_id) values (1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(2,2),(4,2),(3,3),(3,4),(3,5),(4,5),(6,5),(6,6);

insert into classes_teachers(class_id,teacher_id) values (1,1),(1,2),(2,1),(2,2),(2,3),(2,4),(3,3),(4,4);

insert into classes_schedules(class_id,schedule_id) values (1,1),(1,2),(2,1),(2,2),(2,3),(2,4),(3,3),(4,4),(5,5),(6,6);



1.
select class_id from classes_schedules where schedule_id = 2 or schedule_id = 5;
select name from classes where id = ○;

2.
select class_id from classes_students where student_id = 1;
select name from classes where id = ○;

3.
select class_id from classes_majors where major_id = 1;
select name from classes where id = ○;



--------------------------------------------------------------------------------------



create table users(
id int auto_increment primary key,
name varchar(255),
email varchar(255),
password varchar(255)
);

create table groups(
id int auto_increment primary key,
name varchar(255)
);


create table relations(
id int auto_increment primary key,
send_id int(255),
receive_id int(255),
relation int(255)
);
↑relationはsend側が(receiveに対して)1が承認された，2が承認を待っている，3がブロックした

create table individual_messages(
id int auto_increment primary key,
send_id int (255),
receive_id int (255),
message text
);

create table group_messages(
id int auto_increment primary key,
group_id int (255),
user_id int (255),
message text
);

create table groups_users(
id int auto_increment primary key,
group_id int (255),
user_id int (255)
);

insert into users(name,email,password) values ('Maeda','maeda@gamil.com','1111'),('Kimura','kimura@gamil.com','2222'),('Minoda','minoda@gamil.com','3333'),('Yano','yano@gamil.com','4444');

insert into groups (name) values ('あ'),('い'),('う'),('え');

insert into relations (send_id,receive_id,relation) values (1,2,1),(1,3,2),(1,4,1),(2,1,2),(2,4,2),(3,2,3),(4,2,3);

insert into individual_messages(send_id,receive_id,message) values (1,2,'ちぇけら'),(1,3,'おはよう'),(1,4,'こんにちは'),(3,2,'hoge');

insert into group_messages(group_id,user_id,message) values (1,1,'グループチェットです'),(1,2,'ふむふむ');

insert into groups_users(group_id,user_id) values (1,1),(1,2),(1,3);


1.
select message from individual_message where send_id=1 and receive_id=2;

2.
select message from group_messages where group_id=1;

3.
select message from individual_messages where individual_messages.message like '%hoge%';

select message from group_messages where group_messages.message like '%hoge%';




--------------------------------------------------------------------------------------



create table users(
id int auto_increment primary key,
name varchar(255),
email varchar(255),
password varchar(255)
);

create table follows(
id int auto_increment primary key,
follow_id int(255),
follower_id int(255)
);

create table tweets(
id int auto_increment primary key,
user_id int(255),
created_at datetime,
content text
);

create table favorites(
id int auto_increment primary key,
user_id int(255),
tweet_id int(255)
);

insert into users(name,email,password) values ('Maeda','maeda@gamil.com','1111'),('Kimura','kimura@gamil.com','2222'),('Minoda','minoda@gamil.com','3333'),('Yano','yano@gamil.com','4444');

insert into follows (follow_id,follower_id) values (1,2),(1,3),(1,4),(2,1),(2,4),(3,2),(4,2);

insert into tweets(user_id,created_at,content) values (1,'2017-04-30 12:00:00','4月も終わりです'),(1,'2017-05-01 12:00:00','5月が始まりました'),(2,'2017-04-25 12:00:00','hogehoge'),(2,'2017-04-26 12:00:00','hogehogehogehoge');

insert into favorites (user_id,tweet_id) values (1,2),(1,3),(1,4),(2,1),(2,4),(3,2),(4,2);

1.
select follower_id from follows where follow_id=1;

2.
select follower_id from follows where follow_id=1;
select content from tweets where user_id = ○;

3.
select tweet_id from favorites where user_id=1;
select content from tweets where id = ○;