· 8 years ago · Dec 13, 2017, 06:40 AM
1-- ====================================================================================================================
2-- INTRODUCTION TO SQL
3-- ====================================================================================================================
4
5-- COLUMN TYPES -------------------------------------------------------------------------------------------------------
6
7-- Not all column types are available in all database implementations. Below is a subset of the most important types.
8
9-- Text
10-- ====
11--
12-- CHAR Fixed length string.
13-- VARCHAR Variable length string.
14
15-- Numeric
16-- =======
17--
18-- TINYINT -128 to 127.
19-- SMALLINT -32768 to 32767.
20-- MEDIUMINT -8388608 to 8388607.
21-- INT -2147483648 to 2147483647.
22-- BIGINT -9223372036854775808 to 9223372036854775807.
23-- DECIMAL Stored as a string, allowing for a fixed decimal point.
24-- FLOAT Single precision floating point.
25-- DOUBLE Double precision floating point.
26--
27-- There are also UNSIGNED versions of the INT types.
28
29-- Date
30-- ====
31--
32-- DATE() YYYY-MM-DD.
33-- DATETIME() YYYY-MM-DD hh:mm:ss
34-- TIMESTAMP() Number of seconds since the Unix epoch (1970-01-01 00:00:00 UTC).
35-- TIME() hh:mm:ss
36
37-- USER ---------------------------------------------------------------------------------------------------------------
38
39SHOW PRIVILEGES;
40
41-- DATABASE -----------------------------------------------------------------------------------------------------------
42
43SHOW DATABASES;
44
45-- CREATE DATABASE ----------------------------------------------------------------------------------------------------
46
47-- Create a new database.
48--
49-- If there are multiple users on the same server then each will have to choose a unique name for their database.
50--
51CREATE DATABASE music;
52--
53-- To make the database name unique, append your username. For example, 'music_datawookie'.
54
55-- Select the newly created database.
56--
57USE music;
58
59-- CREATE TABLE -------------------------------------------------------------------------------------------------------
60
61-- Create a table in the database.
62--
63CREATE TABLE musician (
64 name VARCHAR(64) NOT NULL,
65 gender CHAR(1),
66 birth DATE,
67 band_name VARCHAR(64),
68 band_type VARCHAR(32)
69);
70
71-- Notes:
72--
73-- * NOT NULL is a constraint indicating that the field cannot be left empty.
74
75-- Take a look at the table structure.
76--
77EXPLAIN musician;
78
79-- INSERT -------------------------------------------------------------------------------------------------------------
80
81-- Inserting a subset of fields.
82--
83INSERT INTO musician (name, band_name) VALUES ('John Lennon', 'The Beatles');
84
85-- Inserting all fields.
86--
87INSERT INTO musician VALUES ('Paul McCartney', 'M', '1942-06-18', 'The Beatles', 'Pop');
88
89-- Inserting multiple records.
90--
91musicianINSERT INTO
92 musician (name, band_name)
93VALUES
94 ('Ringo Starr', 'The Beatles'),
95 ('George Harrison', 'The Beatles'),
96 ('Pete Best', 'The Beatles');
97
98-- Add in a few solo musicians.
99--
100INSERT INTO musician (name) VALUES ('Neil Young'), ('Bob Dylan'), ('Carole King'), ('Joni Mitchell');
101
102-- Test the NOT NULL constraint. This statement will fail.
103--
104INSERT INTO musician (name, gender, band_name) VALUES (NULL, 'M', 'The Troggs');
105
106-- Notes:
107--
108-- * There is nothing stopping you from inserting duplicate records.
109
110-- SELECT -------------------------------------------------------------------------------------------------------------
111
112-- Retrieve all fields from all records.
113--
114SELECT * FROM musician;
115
116-- Retrieve a selection of fields from all records.
117--
118SELECT name, gender FROM musician;
119
120-- A WHERE clause applies a filter, selecting only those records which satisfy a logical predicate.
121--
122SELECT * FROM musician WHERE birth = '1942-06-18';
123--
124SELECT * FROM musician WHERE birth IS NOT NULL;
125--
126SELECT * FROM musician WHERE name LIKE 'J%';
127
128-- Notes:
129--
130-- * Predicates can be combined with AND and OR.
131-- * Operations for predicates:
132--
133-- - arithmetic operations
134-- - relational operations (=, <>, >, <, >=, <=, BETWEEN)
135-- - IS NULL and IS NOT NULL
136-- - LIKE
137-- - IN
138
139-- Actions:
140--
141-- * Select musicians whose name begins with either 'J' or 'P'.
142-- * Select musicians whose name begins with either 'J' or 'P' and who are members of The Beatles.
143
144-- UPDATE -------------------------------------------------------------------------------------------------------------
145
146-- We need to make MySQL a little more permissive with UPDATE.
147--
148SET SQL_SAFE_UPDATES = 0;
149
150-- Change field in all records.
151--
152UPDATE musician SET gender = 'M';
153
154-- Change field in records selected by predicate.
155--
156UPDATE
157 musician
158SET
159 gender = 'F'
160WHERE
161 name IN ('Carole King', 'Joni Mitchell');
162
163-- Change multiple fields simultaneously.
164--
165UPDATE
166 musician
167SET
168 birth = '1940-10-09',
169 band_type = 'Pop'
170WHERE
171 name = 'John Lennon';
172
173-- Actions:
174--
175-- * Populate all fields for the remaining musicians.
176
177-- DELETE -------------------------------------------------------------------------------------------------------------
178
179DELETE FROM musician WHERE name = 'Pete Best';
180--
181-- Only *real* members of The Beatles!
182
183-- ALTER --------------------------------------------------------------------------------------------------------------
184
185-- Add a column to an existing table.
186--
187ALTER TABLE musician ADD COLUMN death DATE;
188
189-- Notes:
190--
191-- * To remove a column use DROP COLUMN.
192-- * To change the data type for a column use ALTER COLUMN or MODIFY COLUMN.
193
194-- Actions:
195--
196-- * Update the death field for the deceased musicians.
197
198-- NORMALISATION ------------------------------------------------------------------------------------------------------
199
200-- There are a variety of problems with the table we've been working with, foremost is the fact that information is
201-- duplicated between records. What happens if we want to change the band name?
202
203-- Normalisation is the process of organising tables and columns to reduce redundancy. Simply stated, normalisation
204-- breaks down data into a set of linked tables.
205
206-- PRIMARY KEY --------------------------------------------------------------------------------------------------------
207
208-- Create a table for bands.
209--
210-- The id column is a PRIMARY KEY, which is used to uniquely identify each record in the table.
211--
212CREATE TABLE band (
213 id INT NOT NULL AUTO_INCREMENT,
214 name VARCHAR(64) NOT NULL,
215 type VARCHAR(32),
216 PRIMARY KEY (id)
217);
218
219-- Add a primary key to the musician table.
220--
221ALTER TABLE musician ADD COLUMN id INT PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
222
223-- Insert a record for The Beatles.
224--
225INSERT INTO band (name, type) VALUES ('The Beatles', 'Pop');
226
227-- Actions:
228--
229-- * Add a band_id column to the musician table. This is a "foreign key" column.
230-- * Update the band_id values so that they point to the correct records in the band table. We'll do this manually at
231-- the moment but soon we'll see a way to do it with a query.
232-- * Drop the band_name and band_type columns from the musician table.
233
234-- FOREIGN KEY CONSTRAINTS --------------------------------------------------------------------------------------------
235
236-- The link between the musician and band tables is currently just a formality. We need to add a foreign key
237-- constraint to enforce this link.
238--
239ALTER TABLE musician
240ADD FOREIGN KEY (band_id) REFERENCES band(id);
241
242-- Actions:
243--
244-- * Try to remove The Beatles from the band table. What happened?
245
246CREATE TABLE album (
247 id INT NOT NULL AUTO_INCREMENT,
248 name VARCHAR(64) NOT NULL,
249 released DATE,
250 band_id INT NOT NULL,
251 PRIMARY KEY (id),
252 FOREIGN KEY (band_id) REFERENCES band(id)
253);
254CREATE TABLE song (
255 id INT NOT NULL AUTO_INCREMENT,
256 name VARCHAR(64) NOT NULL,
257 track TINYINT UNSIGNED, -- Allow this to be NULL for singles.
258 album_id INT, -- Allow this to be NULL for singles.
259 PRIMARY KEY (id),
260 FOREIGN KEY (album_id) REFERENCES album(id)
261);
262
263-- Actions:
264--
265-- * Create records for the 'Rubber Soul' and 'Abbey Road' albums.
266-- * Create records for the tracks 'Nowhere Man' and 'Drive My Car' on the 'Rubber Soul' album.
267-- * [EXTRA CREDIT] Add a new band with associated musicians, an album and a few songs. Everybody does this on a
268-- single database.
269
270-- RELATIONSHIPS ------------------------------------------------------------------------------------------------------
271
272-- ONE-TO-ONE
273--
274-- It's possible to establish a one-to-one relationship but this is rather a niche requirement, so we are not going to
275-- look at it in detail.
276
277-- ONE-TO-MANY
278--
279-- A one-to-many relationship is catered for via primary and foreign keys.
280--
281-- For example, one band is mapped to many musicians.
282
283-- MANY-TO-MANY
284--
285-- A many-to-many relationship is created using a junction table.
286--
287-- For example, we might create an instrument table. An artist might play multiple instruments and an instrument might
288-- be played by multiple artists.
289
290CREATE TABLE instrument (
291 id INT NOT NULL AUTO_INCREMENT,
292 name VARCHAR(64) NOT NULL,
293 PRIMARY KEY (id)
294);
295
296CREATE TABLE instrument_musician (
297 id INT NOT NULL AUTO_INCREMENT,
298 musician_id INT NOT NULL,
299 instrument_id INT NOT NULL,
300 PRIMARY KEY (id),
301 FOREIGN KEY (musician_id) REFERENCES musician(id),
302 FOREIGN KEY (instrument_id) REFERENCES instrument(id)
303);
304
305INSERT INTO instrument (name) VALUES ('vocals'), ('guitar'), ('keyboard'), ('harmonica'), ('drums'), ('sitar');
306
307INSERT INTO instrument_musician (musician_id, instrument_id)
308VALUES (1, 1), (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 3), (3, 1), (3, 5), (4, 1), (4, 2), (4, 6);
309
310-- JOINS --------------------------------------------------------------------------------------------------------------
311
312-- INNER JOIN
313--
314-- An inner join effectively considers the intersection of two tables.
315--
316-- +----------------------+
317-- | |
318-- | |
319-- | |
320-- | |
321-- | +----------------------+
322-- | |............| |
323-- | |............| |
324-- | |............| |
325-- | |............| |
326-- | |............| |
327-- | |............| |
328-- +---------|------------+ |
329-- | |
330-- | |
331-- | |
332-- | |
333-- +----------------------+
334--
335SELECT *
336FROM
337 musician
338INNER JOIN
339 band
340ON
341 musician.band_id = band.id;
342
343-- Notes:
344--
345-- * The result only contains records for which there is a match between the tables.
346
347-- Actions:
348--
349-- * Select the names of the members of The Beatles.
350
351-- OUTER JOINS
352--
353-- An outer join effectively considers the (one sided) union of two tables.
354--
355-- +----------------------+
356-- |......................|
357-- |......................|
358-- |......................|
359-- |......................|
360-- |.........+----------------------+
361-- |.........|............| |
362-- |.........|............| |
363-- |.........|............| |
364-- |.........|............| |
365-- |.........|............| |
366-- |.........|............| |
367-- +---------|------------+ |
368-- | |
369-- | |
370-- | |
371-- | |
372-- +----------------------+
373--
374SELECT *
375FROM
376 musician
377LEFT JOIN
378 band
379ON
380 musician.band_id = band.id;
381
382-- Notes:
383--
384-- * The result contains all records from musician regardless of whether there is a match in band.
385-- * There is also a RIGHT JOIN which does the same thing but for tables listed in opposite order.
386
387-- Actions:
388--
389-- * Select the only the musician and band names. Relabel the result columns accordingly.
390
391-- Joining via a junction table.
392--
393SELECT A.name AS musician, C.name AS instrument
394FROM
395 musician A
396INNER JOIN
397 instrument_musician B
398ON
399 A.id = B.musician_id
400INNER JOIN
401 instrument C
402ON
403 C.id = B.instrument_id
404ORDER BY
405 instrument;
406
407-- CREATING TABLES WITH QUERIES ---------------------------------------------------------------------------------------
408
409-- We can also create and populate a table using SELECT.
410--
411CREATE TABLE solo AS (
412 SELECT name FROM musician WHERE band_id IS NULL
413);
414
415-- Temporary tables can be very useful for decomposing complicated queries. However they only last for the duration of
416-- the session.
417--
418-- The following two statements must be executed in the same transaction under phpMyAdmin because it closes the
419-- database connection after each transaction.
420--
421CREATE TEMPORARY TABLE IF NOT EXISTS female_musicians
422AS (
423 SELECT name FROM musician WHERE gender = 'F'
424);
425--
426SELECT * FROM female_musicians;
427
428-- DROP TABLE ---------------------------------------------------------------------------------------------------------
429
430-- When we no longer need a table it can be dropped.
431--
432-- [!!!] WARNING: This is irreversible!
433--
434DROP TABLE solo;
435--
436-- Alternatively, if you're not sure whether a table exists...
437--
438DROP TABLE IF EXISTS solo;
439
440-- DROP DATABASE ------------------------------------------------------------------------------------------------------
441
442-- If we no longer need the database then we can drop it too.
443--
444-- [!!!] WARNING: This is irreversible!
445--
446DROP DATABASE music;
447
448-- IMPORTING DATA -----------------------------------------------------------------------------------------------------
449
450-- IMPORT FROM CSV
451
452-- Actions:
453--
454-- 1. Download a raw copy of https://github.com/DataWookie/example-data/blob/master/csv/running-race-results.csv.
455-- 2. Create a new database for storing race results.
456-- 3. In the new database create a table called 'race_results' with the following structure:
457--
458-- Column Type
459-- ======== ============
460-- position INT
461-- time TIME
462-- gender CHAR(7)
463-- club VARCHAR(128)
464-- race VARCHAR(128)
465-- date DATE
466-- distance FLOAT
467-- name VARCHAR(128)
468--
469-- phpMyAdmin
470-- ==========
471--
472-- 4. Select the newly created table from the menu on the left.
473-- 5. On the Import tab do the following:
474--
475-- a) Select the CSV file we've just downloaded.
476-- b) Skip the first record in the file (the headers).
477-- c) Specify ";" as the column separator.
478--
479-- 6. Press the 'Go' button and be patient while the data are added to the database.
480-- 7. When the import is complete, go to the Browse tab to take a quick look at the data.
481--
482-- MySQL Workbench
483-- ===============
484--
485-- 4. From the Schemas section on the left menu select the new database.
486-- 5. Unde the Tables item right-click on the race_results table.
487-- 6. Choose the Table Data Import Wizard.
488-- 7. Select the CSV file and complete the wizard.
489--
490-- 8. Add a primary key to the race_results table.
491
492-- It can take a while to import those data, so a shortcut might be to duplicate an existing table.
493--
494-- Assuming that we copy the race_results table from the racing database.
495--
496-- CREATE TABLE race_results_copy LIKE racing.race_results;
497-- INSERT race_results_copy SELECT * FROM racing.race_results;
498
499-- DATA CLEANING ------------------------------------------------------------------------------------------------------
500
501-- First look at the data.
502--
503SELECT * FROM race_results LIMIT 10;
504
505-- Remove results for athletes without names.
506--
507SELECT * FROM race_results WHERE name IS NULL;
508SELECT * FROM race_results WHERE name = '';
509--
510DELETE FROM race_results WHERE name = '';
511
512-- Actions:
513--
514-- * Remove results for disqualified runners.
515-- * Remove results where time is '00:00:00'.
516-- * Look at the result for Vincent Masuku. Is it likely that there was a transcription error? Fix it.
517-- * Set club to NULL for values "NA".
518
519-- NORMALISATION ------------------------------------------------------------------------------------------------------
520
521-- Create a table with distinct race details.
522--
523CREATE TABLE race AS (
524 SELECT DISTINCT race AS name, date, distance FROM race_results
525);
526
527-- Add a primary key.
528--
529ALTER TABLE race ADD COLUMN id INT PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
530
531-- Add a race_id foreign key to race_results.
532--
533ALTER TABLE race_results ADD COLUMN race_id INT;
534ALTER TABLE race_results ADD FOREIGN KEY (race_id) REFERENCES race(id);
535
536-- Update race_id foreign key.
537--
538UPDATE race_results
539INNER JOIN
540 race
541ON
542 race_results.race = race.name
543AND
544 race_results.date = race.date
545AND
546 race_results.distance = race.distance
547SET race_id = race.id;
548
549-- Drop other race columns from race_results.
550--
551ALTER TABLE race_results DROP COLUMN race;
552ALTER TABLE race_results DROP COLUMN date;
553ALTER TABLE race_results DROP COLUMN distance;
554
555-- Actions:
556--
557-- 1. Create a club table with a distinct club names extracted from the results table.
558-- 2. Add a primary key.
559-- 3. Add a club_id foreign key to race_results.
560-- 4. Update the club_id column by joining with the club table.
561-- 5. Drop the redundant club column from race_results.
562-- 6. Is there a way that we can further normalise these data?
563
564-- AGGREGATION --------------------------------------------------------------------------------------------------------
565
566-- Number of records.
567--
568SELECT COUNT(*) FROM race_results;
569--
570-- But that's the total across all races.
571
572-- When aggregating it almost always makes sense to first partition your data using GROUP BY.
573--
574SELECT race_id, COUNT(*) FROM race_results GROUP BY race_id;
575
576-- Actions:
577--
578-- * Write a query which will return race name, date, distance and number of finishers for each race.
579-- * Add the winning time for each race to the previous query. Is there more cleaning required?
580
581-- You can't use WHERE to filter on aggregate values. You need to use HAVING for that.
582--
583SELECT race_id, COUNT(*)
584FROM race_results
585GROUP BY race_id
586HAVING COUNT(*) > 200;
587
588-- SUB-QUERY ----------------------------------------------------------------------------------------------------------
589
590-- It's possible to nest one SELECT inside another.
591--
592SELECT * FROM race_results WHERE club_id = (SELECT id FROM club WHERE name = 'DHS Old Boys');
593--
594-- The subquery appears between parenthesis.
595
596-- Notes:
597--
598-- * The outer query can be a SELECT, INSERT, UPDATE or DELETE.
599
600-- Actions:
601--
602-- * Get the same output as above by using a join.
603-- * Get the results for members of clubs with names beginning with an 'I'.
604
605-- VARIABLES ----------------------------------------------------------------------------------------------------------
606
607-- User defined variable names start with a '@'.
608--
609SET @club_name = 'DHS Old Boys';
610--
611-- Variable names are case insensitive.
612--
613SELECT @club_name;
614SELECT @Club_Name;
615SELECT @CLUB_NAME;
616
617SELECT * FROM club WHERE name = @club_name;
618
619-- You can perform simple operations on variables.
620--
621SET @counter = 1;
622SET @counter = @counter + 1;
623SELECT @counter;
624
625-- INDEX --------------------------------------------------------------------------------------------------------------
626
627-- An index can massively improve query times. Without an index the database often has to do a brute force search in
628-- order to filter out the required records.
629--
630-- Suppose that we wanted to find the athletes who finished the Kearsney Striders 21.1 km in less than 90 minutes.
631--
632SELECT *
633FROM race_results
634WHERE race_id = 2 AND time < '01:30:00';
635--
636-- To satisfy this query the database literally has to search through all of the records to find those which satisfy
637-- the time predicate.
638
639-- Add an index on the time column.
640--
641ALTER TABLE race_results ADD INDEX time (time);
642--
643-- This will make the space consumed by this table bigger. It'll also mean that adding data to the table will be
644-- slightly slower.
645--
646-- But it should make queries that involve the time column much quicker.
647
648-- This new index is not actually very useful for the above query because it references both race_id and time. If this
649-- is a common query pattern then it makes sense to create a multi-column index.
650--
651ALTER TABLE race_results ADD INDEX race_time (race_id, time);
652--
653-- Now try the query again and see how much faster it executes.
654
655-- If you no longer need an index you can drop it.
656--
657DROP INDEX time ON race_results;
658DROP INDEX race_time ON race_results;
659
660-- Actions:
661--
662-- * Run the following query and make a note of how long it takes.
663--
664SELECT * FROM race_results WHERE name LIKE 'A%';
665--
666-- * Add an index which should improve the performance on this query.
667-- * Run the query again and compare times.
668-- * Discard the new index.
669
670-- VIEW ---------------------------------------------------------------------------------------------------------------
671
672-- If you find yourself running the same query repeatedly then perhaps it's time to create a view. A view is just a
673-- predefind SQL query which has been assigned a name.
674--
675-- We might frequently need to find the names of winners of all races.
676--
677SELECT name AS winner FROM race_results WHERE position = 1;
678
679-- We can then create a view from this query.
680--
681CREATE VIEW race_winners AS SELECT name AS winner FROM race_results WHERE position = 1;
682
683-- And then we simply treat the view as if it were a table.
684--
685SELECT * FROM race_winners;
686
687-- Actions:
688--
689-- * Create a query which adds a pace (minutes per km) column to the results. Hint: You might find TRUNCATE() and
690-- TIME_TO_SEC() functions useful.
691-- * Convert this query into a view.
692
693-- TRANSACTIONS -------------------------------------------------------------------------------------------------------
694
695-- A transaction is a group of statements that are executed together. The principal advantage of this is that
696--
697-- - if something goes wrong then you can ROLLBACK any changes; or
698-- - if everything goes according to plan then you can COMMIT.
699
700-- Update when club is 'No Club'.
701--
702START TRANSACTION;
703UPDATE race_results SET club_id = NULL WHERE club_id IN (SELECT id FROM club WHERE name = 'No Club');
704DELETE FROM club WHERE name = 'No Club';
705--
706-- If we are happy with the changes then we can COMMIT.
707--
708COMMIT;
709
710START TRANSACTION;
711UPDATE race_results SET gender = NULL WHERE gender = 'Unknown';
712--
713-- If we change our minds or something goes wrong then we can ROLLBACK.
714--
715ROLLBACK;
716
717-- If you are not inside a transaction then autocommit applies.
718--
719UPDATE race_results SET gender = NULL WHERE gender = 'Unknown';
720
721-- STORED ROUTINES ----------------------------------------------------------------------------------------------------
722
723-- A 'stored routine' is a bit of SQL code which is stored on the database server.
724
725-- The default statement delimiter in SQL is ";". Since we could have multiple statements within a routine definition
726-- it's important to differentiate between these and the delimiter at the end of the definition. To do this we can
727-- change the delimeter.
728--
729DELIMITER $$
730SELECT 1$$
731DELIMITER ;
732
733-- FUNCTION
734--
735-- Function parameters are always of type IN.
736--
737DELIMITER $$
738CREATE FUNCTION time_to_min(time TIME) RETURNS FLOAT DETERMINISTIC
739BEGIN
740 RETURN TIME_TO_SEC(time) / 60;
741END$$
742DELIMITER ;
743--
744SELECT time_to_min('01:30:00');
745--
746SELECT *, TRUNCATE(time_to_min(time), 2) AS minutes FROM race_results;
747
748-- Actions:
749--
750-- * Create a function which calculates pace in minutes per km.
751
752-- PROCEDURE
753--
754DELIMITER $$
755CREATE PROCEDURE count_clubs () SELECT COUNT(*) FROM club; $$
756DELIMITER ;
757
758--
759-- Now run the procedure.
760--
761CALL count_clubs;
762
763-- Creating a procedure with a compound statement and local variables.
764--
765DELIMITER $$
766CREATE PROCEDURE locals ()
767BEGIN
768 DECLARE a INT DEFAULT 10;
769 DECLARE b, c INT;
770 SET a = a + 100;
771 SET b = 15;
772 SET c = a - b;
773 SELECT a, b, c;
774END$$
775DELIMITER ;
776--
777CALL locals;
778
779-- Procedure parameters can be of type IN, OUT or INOUT.
780--
781DELIMITER $$
782CREATE PROCEDURE club_head (IN n INT)
783BEGIN
784 SELECT * FROM club LIMIT n;
785END$$
786DELIMITER ;
787--
788CALL club_head(6);
789--
790DELIMITER $$
791CREATE PROCEDURE longest_name (OUT length INT)
792BEGIN
793 SELECT MAX(CHAR_LENGTH(name)) INTO length FROM race_results;
794END$$
795DELIMITER ;
796--
797CALL longest_name(@longest);
798SELECT @longest;
799
800-- Drop procedures when we are done.
801--
802DROP PROCEDURE count_clubs;
803DROP PROCEDURE locals;
804DROP PROCEDURE club_head;
805DROP PROCEDURE longest_name;
806
807-- Actions:
808--
809-- * Create a procedure which returns the name of the club with the most results as an OUT parameter.
810
811-- Notes:
812--
813-- * It's possible to have conditionals and iterations inside routines.
814
815-- TRIGGER ------------------------------------------------------------------------------------------------------------
816
817-- You can trigger events when an INSERT, DELETE or UPDATE takes place on a table.
818
819-- ====================================================================================================================
820-- SOLUTIONS
821-- ====================================================================================================================
822
823CREATE DATABASE running;
824
825USE running;
826
827CREATE TABLE race_results (
828 position INT,
829 time TIME,
830 gender CHAR(7),
831 club VARCHAR(128),
832 race VARCHAR(128),
833 date DATE,
834 distance FLOAT,
835 name VARCHAR(128)
836);
837
838ALTER TABLE race_results ADD COLUMN id INT PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
839
840CREATE TABLE club AS (
841 SELECT DISTINCT club AS name FROM race_results
842);
843ALTER TABLE club ADD COLUMN id INT PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
844
845ALTER TABLE race_results ADD COLUMN club_id INT;
846ALTER TABLE race_results ADD FOREIGN KEY (club_id) REFERENCES club(id);
847
848UPDATE race_results
849INNER JOIN
850 club
851ON
852 race_results.club = club.name
853SET club_id = club.id;
854
855ALTER TABLE race_results DROP COLUMN club;
856
857-- SUB-QUERY ----------------------------------------------------------------------------------------------------------
858
859SELECT * FROM race_results WHERE club_id IN (SELECT id FROM club WHERE name LIKE 'I%');
860
861-- VIEW ---------------------------------------------------------------------------------------------------------------
862
863SELECT *, TRUNCATE(TIME_TO_SEC(time) / 60 / distance, 2) AS pace
864FROM
865 race_results A
866INNER JOIN
867 race B
868ON
869 A.race_id = B.id;
870
871-- STORED ROUTINES ----------------------------------------------------------------------------------------------------
872
873-- FUNCTION
874--
875DELIMITER $$
876CREATE FUNCTION pace(time TIME, km FLOAT) RETURNS FLOAT
877BEGIN
878 RETURN time_to_min(time) / km;
879END$$
880DELIMITER ;
881--
882SELECT pace('00:45:00', 10);
883--
884SELECT *, TRUNCATE(pace(time, distance), 2)
885FROM
886 race_results A
887INNER JOIN
888 race B
889ON
890 A.race_id = B.id;
891
892-- PROCEDURE
893--
894DELIMITER $$
895CREATE PROCEDURE biggest_club (OUT name VARCHAR(128))
896BEGIN
897 SELECT club.name INTO name
898 FROM
899 race_results
900 JOIN
901 club
902 ON
903 race_results.club_id = club.id
904 WHERE
905 club.name NOT IN ('NA', 'No Club')
906 GROUP BY club.id
907 ORDER BY COUNT(*) DESC
908 LIMIT 1;
909END$$
910DELIMITER ;
911--
912CALL biggest_club(@longest);
913SELECT @longest;