iXhvvPQm

1DROP DATABASE IF EXISTS one_to_one;
2CREATE DATABASE one_to_one;
3USE one_to_one;
4
5CREATE TABLE Person(
6    id int not null primary key auto_increment,
7    name varchar(100) unique
8);
9
10CREATE TABLE Pet(
11    id int not null primary key auto_increment,
12    name varchar(100) unique,
13    type ENUM("dog", "cat"),
14    person_id int not null unique,
15    FOREIGN KEY (person_id) References Person(id)
16);
17
18INSERT INTO Person(name) VALUES("misho");
19
20INSERT INTO Pet(name, type, person_id) VALUES("spas", "dog", 1);
21-- INSERT INTO Pet(name, type, person_id) VALUES("kety", "cat", 1);
22
23SELECT * FROM Person LEFT JOIN Pet
24ON Person.id = Pet.person_id;
25
26------
27
28DROP DATABASE IF EXISTS many_to_many;
29CREATE DATABASE many_to_many;
30USE many_to_many;
31
32CREATE TABLE Person(
33    id int not null primary key auto_increment,
34    name varchar(100) unique
35);
36
37CREATE TABLE Pet(
38    id int not null primary key auto_increment,
39    name varchar(100) unique,
40    type ENUM("dog", "cat")
41);
42
43CREATE TABLE PersonPet(
44    id int not null primary key auto_increment,
45    person_id int not null,
46    FOREIGN KEY (person_id) References Person(id),
47    pet_id int not null,
48    FOREIGN KEY (pet_id) References Pet(id),
49    CONSTRAINT UniquePersonIdAndPetId UNIQUE (person_id,pet_id)
50);
51
52CREATE TABLE PersonPetWalk(
53    id int not null primary key auto_increment,
54    person_id int not null,
55    FOREIGN KEY (person_id) References Person(id),
56    pet_id int not null,
57    FOREIGN KEY (pet_id) References Pet(id),
58    date datetime default now(),
59    CONSTRAINT PersonPetDate UNIQUE (person_id, pet_id, date),
60    CONSTRAINT PersonDate UNIQUE (person_id, date),
61    CONSTRAINT PetDate UNIQUE (pet_id, date)
62);
63
64
65
66INSERT INTO Pet(name, type) VALUES("spas", "dog");
67INSERT INTO Pet(name, type) VALUES("molly", "cat");
68INSERT INTO Pet(name, type) VALUES("rex", "dog");
69
70
71INSERT INTO Person(name) VALUES("misho");
72INSERT INTO Person(name) VALUES("mimi");
73
74INSERT INTO PersonPet(person_id, pet_id) VALUES(1,1);
75INSERT INTO PersonPet(person_id, pet_id) VALUES(2,1);
76INSERT INTO PersonPet(person_id, pet_id) VALUES(2,2);
77INSERT INTO PersonPet(person_id, pet_id) VALUES(1,3);
78
79INSERT INTO PersonPetWalk(person_id, pet_id, date) VALUES(1,1, "2020-01-01");
80INSERT INTO PersonPetWalk(person_id, pet_id, date) VALUES(2,1, "2020-01-02");
81INSERT INTO PersonPetWalk(person_id, pet_id, date) VALUES(2,2, "2020-01-01");
82INSERT INTO PersonPetWalk(person_id, pet_id, date) VALUES(1,3, "2020-01-02");
83-- INSERT INTO PersonPetWalk(person_id, pet_id, date) VALUES(2,1, "2020-01-01");
84
85--
86-- INSERT INTO PersonPet(person_id, pet_id) VALUES(1,1);
87-- INSERT INTO PersonPet(person_id, pet_id) VALUES(2,1);
88-- INSERT INTO PersonPet(person_id, pet_id) VALUES(2,2);
89-- INSERT INTO PersonPet(person_id, pet_id) VALUES(1,3);
90-- INSERT INTO PersonPet(person_id, pet_id) VALUES(1,1);
91-- INSERT INTO PersonPet(person_id, pet_id) VALUES(2,1);
92-- INSERT INTO PersonPet(person_id, pet_id) VALUES(2,2);
93-- INSERT INTO PersonPet(person_id, pet_id) VALUES(1,3);
94
95SELECT Person.name, Pet.name, PersonPetWalk.date FROM Person
96RIGHT JOIN PersonPetWalk
97ON Person.id = PersonPetWalk.person_id
98LEFT JOIN Pet
99ON Pet.id = PersonPetWalk.pet_id;
100
101----
102
103DROP DATABASE IF EXISTS many_to_many;
104CREATE DATABASE many_to_many;
105USE many_to_many;
106
107CREATE TABLE Team(
108    id int not null primary key auto_increment,
109    name varchar(100) unique
110);
111
112CREATE TABLE Scores(
113    id int not null primary key auto_increment,
114    scorrer_id int not null,
115    FOREIGN KEY (scorrer_id) References Team(id),
116    scored_on_id int not null,
117    FOREIGN KEY (scored_on_id) References Team(id),
118    date datetime not null default now(),
119    CONSTRAINT ScorrerDate UNIQUE (scorrer_id, date),
120    CONSTRAINT ScoredOnDate UNIQUE (scored_on_id, date)
121);
122
123INSERT INTO Team(name) VALUES("Bulgaria");
124INSERT INTO Team(name) VALUES("England");
125INSERT INTO Team(name) VALUES("Mexico");
126
127INSERT INTO Scores(scorrer_id, scored_on_id) VALUES(1,2);
128INSERT INTO Scores(scorrer_id, scored_on_id, date) VALUES(1,1, "2020-01-01");
129INSERT INTO Scores(scorrer_id, scored_on_id, date) VALUES(1,3, "2020-01-02");
130-- INSERT INTO Scores(scorrer_id, scored_on_id) VALUES(1,3);
131INSERT INTO Scores(scorrer_id, scored_on_id, date) VALUES(2,2, "2020-01-01");
132INSERT INTO Scores(scorrer_id, scored_on_id, date) VALUES(3,2, "2020-01-02");
133
134
135SELECT count(winner.id) FROM Team as winner
136RIGHT JOIN Scores 
137ON Scores.scorrer_id = winner.id
138LEFT JOIN Team as looser
139ON Scores.scored_on_id = looser.id
140Where winner.name = "Bulgaria" AND 
141looser.name = "England";
142
143
144