· 4 years ago · Feb 17, 2021, 01:40 AM
1CREATE TABLE IF NOT EXISTS books (
2 book_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3 author_id INTEGER UNSIGNED,
4 title VARCHAR(100) NOT NULL,
5 year INTEGER UNSIGNED NOT NULL DEFAULT 1900,
6 language VARCHAR(2) NOT NULL DEFAULT 'es' COMMENT 'ISO 639-1 Language',
7 cover_url VARCHAR(500),
8 price DOUBLE(6,2) NOT NULL DEFAULT 10.0,
9 sellable TINYINT(1) DEFAULT 1,
10 copies INTEGER NOT NULL DEFAULT 1,
11 description TEXT
12);
13
14CREATE TABLE IF NOT EXISTS author (
15 author_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
16 name VARCHAR(100) NOT NULL,
17 nationality VARCHAR(3)
18);
19
20CREATE TABLE clients (
21 client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
22 `name` VARCHAR(50) NOT NULL,
23 email VARCHAR(100) NOT NULL UNIQUE,
24 birthdate DATETIME,
25 gender ENUM('M', 'F', 'ND') NOT NULL,
26 active TINYINT(1) NOT NULL DEFAULT 1,
27 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
28 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
29);
30
31CREATE TABLE IF NOT EXISTS operations (
32 operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
33 book_id INTEGER UNSIGNED NOT NULL,
34 client_id INTEGER UNSIGNED NOT NULL,
35 `type` ENUM('prestado', 'devuelto', 'vendido') NOT NULL,
36 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
37 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
38 finished TINYINT(1) NOT NULL DEFAULT 0
39);
40
41INSERT INTO author(author_id, name, nationality)
42VALUES (NULL, 'Juan Rulfo', 'MEX');
43
44INSERT INTO author(name, nationality)
45VALUES ('Gabriel Garcia Marquez', 'COL');
46
47INSERT INTO author()
48VALUES (NULL, 'Juan Gabriel Vasquez', 'COL');
49
50INSERT INTO author(name, nationality)
51VALUES ('Julio Cortazar', 'ARG'),
52 ('Isabel Allende', 'CHI'),
53 ('Octavio Paz', 'MEX'),
54 ('Juan Carlos Onetti', 'URU')
55 ;
56
57INSERT INTO author(author_id, name)
58VALUES (16, 'Pablo Neruda');
59
60INSERT INTO `clients`(client_id, name, email, birthdate, gender, active, created_at)
61VALUES (1,'Maria Dolores Gomez','Maria Dolores.95983222J@random.names','1971-06-06','F',1,'2018-04-09 16:51:30'),
62 (2,'Adrian Fernandez','Adrian.55818851J@random.names','1970-04-09','M',1,'2018-04-09 16:51:30'),
63 (3,'Maria Luisa Marin','Maria Luisa.83726282A@random.names','1957-07-30','F',1,'2018-04-09 16:51:30'),
64 (4,'Pedro Sanchez','Pedro.78522059J@random.names','1992-01-31','M',1,'2018-04-09 16:51:30')
65 ;
66
67INSERT INTO books(title, author_id)
68VALUES ('El Laberinto de la Soledad', 6);
69
70INSERT INTO books(title, author_id, `year`)
71VALUES ('Vuelta al laberinto de la Soledad',
72 (SELECT author_id FROM author
73 WHERE name = 'Octavio Paz'
74 LIMIT 1
75 ), 1960
76);
77
78-- Estos comandos son usados en: pruebaplatzi
79
80SELECT YEAR(NOW()) - YEAR(birthdate) FROM clients LIMIT 10;
81
82SELECT name, YEAR(NOW()) - YEAR(birthdate) FROM clients LIMIT 10;
83
84SELECT * FROM clients WHERE name LIKE '%Saave%';
85
86SELECT name, email, YEAR(NOW()) - YEAR(birthdate) AS edad, gender FROM clients
87WHERE gender = 'F'
88AND name LIKE '%Lop%'
89;
90
91SELECT * FROM authors WHERE author_id > 0 and author_id <= 5;
92
93SELECT * FROM books WHERE author_id BETWEEN 1 and 5;
94
95SELECT b.book_id, a.name, b.title
96FROM books as b
97JOIN authors as a
98ON a.author_id = b.author_id
99WHERE a.author_id BETWEEN 1 and 5
100;
101
102SELECT c.name, b.title, COUNT(b.book_id)
103FROM transactions as t
104JOIN books as b
105 ON t.book_id = b.book_id
106JOIN clients as c
107 ON t.client_id = c.client_id
108JOIN authors as a
109 ON b.author_id = a.author_id
110WHERE c.gender = 'F'
111 and t.type IN ('sell', 'lend')
112;
113
114SELECT b.title, a.name
115FROM authors as a, books as b
116WHERE a.author_id = b.author_id
117LIMIT 10
118;
119
120SELECT a.author_id, a.name, COUNT(b.book_id) AS total_book
121FROM authors as a
122LEFT JOIN books as b
123 on b.author_id = a.author_id
124WHERE a.author_id
125GROUP BY a.author_id
126ORDER BY total_book DESC
127;
128
129-- ¿Que nacionalidades hay?
130
131SELECT DISTINCT nationality
132FROM authors
133;
134
135-- ¿Cuantos escritores hay de cada nacionalidad?
136
137SELECT nationality, COUNT(author_id) AS c_authors
138FROM authors
139WHERE nationality IS NOT NULL
140 AND nationality != 'RUS'
141GROUP BY nationality
142ORDER BY c_authors DESC, nationality ASC
143;
144
145-- ¿Cual es el promedio/desvicación standard del precio de libros?
146
147SELECT nationality, COUNT(book_id) AS libros, AVG(price) AS prom,
148 STDDEV(price) AS std
149FROM books as b
150JOIN authors as a
151 ON a.author_id = b.author_id
152GROUP BY nationality
153ORDER BY prom DESC
154;
155
156-- ¿Cual es el precio maximo/minimo de un libro?
157
158SELECT nationality, MAX(price), MIN(price)
159FROM books AS b
160JOIN authors AS a
161 ON a.author_id = b.author_id
162GROUP BY nationality
163;
164
165SELECT c.name, t.type, b.title,
166 CONCAT(a.name, " (", a.nationality,")") AS author,
167 TO_DAYS(NOW())-TO_DAYS(t.created_at) AS ago
168FROM transactions AS t
169LEFT JOIN clients AS c
170 ON c.client_id = t.client_id
171LEFT JOIN books AS b
172 ON b.book_id = t.book_id
173LEFT JOIN authors AS a
174 ON b.author_id = a.author_id
175;
176
177-- UPDATE & DELETE
178
179DELETE FROM authors WHERE author_id=161
180LIMIT 1
181;
182
183UPDATE clients
184SET active = 0
185WHERE client_id = 80
186LIMIT 1
187;
188
189UPDATE clients
190SET active = 0
191WHERE client_id IN (1, 6, 8, 27, 90)
192OR name like '%Lopez%'
193;
194
195-- Vacia la tabla, no la elimina.
196TRUNCATE transactions;
197
198UPDATE authors
199SET nationality = 'GBR'
200WHERE nationality = 'ENG'
201;
202
203SELECT count(book_id)
204FROM books
205;
206
207SELECT SUM(price*copies)
208FROM books
209WHERE sellable = 1
210;
211
212SELECT COUNT(book_id), SUM(IF(year < 1950, 1, 0)) AS '<1950'
213FROM books
214;
215
216SELECT nationality, COUNT(book_id),
217 SUM(IF(year < 1950, 1,0)) AS '<1950',
218 SUM(IF(year >= 1950 AND year < 1990, 1, 0)) AS '<1990',
219 SUM(IF(year >= 1990 AND year < 2000, 1, 0)) AS '<2000',
220 SUM(IF(year >= 2000, 1, 0)) AS '<hoy'
221FROM books AS b
222JOIN authors as a
223 ON a.author_id = b.author_id
224WHERE a.nationality IS NOT NULL
225GROUP BY nationality
226;
227
228ALTER TABLE authors
229ADD COLUMN birthyear INT DEFAULT 1930
230AFTER name
231
232ALTER TABLE authors
233MODIFY COLUMN birthyear year DEFAULT 1920
234;
235
236ALTER TABLE authors
237DROP COLUMN birthyear
238;
239
240