· 8 years ago · Dec 14, 2017, 04:54 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--
91INSERT 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 (type INT DEFAULT NULL) to the musician table.
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-- VISUALISE ----------------------------------------------------------------------------------------------------------
449
450-- phpMyAdmin
451-- ==========
452
453-- From the main page for a particular database, click on the Designer tab to see the layout of the tables and how they
454-- relate to each other.
455
456-- MySQL Workbench
457-- ===============
458
459-- IMPORTING DATA -----------------------------------------------------------------------------------------------------
460
461-- IMPORT FROM CSV
462
463-- Actions:
464--
465-- 1. Download a raw copy of https://github.com/DataWookie/example-data/blob/master/csv/running-race-results.csv.
466-- 2. Create a new database for storing race results.
467-- 3. In the new database create a table called 'race_results' with the following structure:
468--
469-- Column Type
470-- ======== ============
471-- position INT
472-- time TIME
473-- gender CHAR(7)
474-- club VARCHAR(128)
475-- race VARCHAR(128)
476-- date DATE
477-- distance FLOAT
478-- name VARCHAR(128)
479--
480-- phpMyAdmin
481-- ==========
482--
483-- 4. Select the newly created table from the menu on the left.
484-- 5. On the Import tab do the following:
485--
486-- a) Select the CSV file we've just downloaded.
487-- b) Skip the first record in the file (the headers).
488-- c) Specify ";" as the column separator.
489--
490-- 6. Press the 'Go' button and be patient while the data are added to the database.
491-- 7. When the import is complete, go to the Browse tab to take a quick look at the data.
492--
493-- MySQL Workbench
494-- ===============
495--
496-- 4. From the Schemas section on the left menu select the new database.
497-- 5. Unde the Tables item right-click on the race_results table.
498-- 6. Choose the Table Data Import Wizard.
499-- 7. Select the CSV file and complete the wizard.
500--
501-- 8. Add a primary key to the race_results table.
502
503-- It can take a while to import those data, so a shortcut might be to duplicate an existing table.
504--
505-- Assuming that we copy the race_results table from the racing database.
506--
507-- CREATE TABLE race_results_copy LIKE racing.race_results;
508-- INSERT race_results_copy SELECT * FROM racing.race_results;
509
510-- DATA CLEANING ------------------------------------------------------------------------------------------------------
511
512-- First look at the data.
513--
514SELECT * FROM race_results LIMIT 10;
515
516-- Actions:
517--
518-- * Remove results for disqualified runners (indicated by name of 'Dq').
519-- * Remove results where time is '00:00:00'.
520-- * Look at the result for Vincent Masuku. Is it likely that there was a transcription error? Check surrounding
521-- results. If there's a problem, fix it.
522-- * Set club to NULL for "NA" values.
523
524-- NORMALISATION ------------------------------------------------------------------------------------------------------
525
526-- Create a table with distinct race details.
527--
528CREATE TABLE race AS (
529 SELECT DISTINCT race AS name, date, distance FROM race_results
530);
531
532-- Add a primary key.
533--
534ALTER TABLE race ADD COLUMN id INT PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
535
536-- Add a race_id foreign key to race_results.
537--
538ALTER TABLE race_results ADD COLUMN race_id INT;
539ALTER TABLE race_results ADD FOREIGN KEY (race_id) REFERENCES race(id);
540
541-- Update race_id foreign key.
542--
543UPDATE race_results
544INNER JOIN
545 race
546ON
547 race_results.race = race.name
548AND
549 race_results.date = race.date
550AND
551 race_results.distance = race.distance
552SET race_id = race.id;
553
554-- Drop other race columns from race_results.
555--
556ALTER TABLE race_results DROP COLUMN race;
557ALTER TABLE race_results DROP COLUMN date;
558ALTER TABLE race_results DROP COLUMN distance;
559
560-- Actions:
561--
562-- 1. Create a club table with a distinct club names extracted from the results table.
563-- 2. Add a primary key.
564-- 3. Add a club_id foreign key to race_results.
565-- 4. Update the club_id column by joining with the club table.
566-- 5. Drop the redundant club column from race_results.
567-- 6. Is there a way that we can further normalise these data?
568
569-- AGGREGATION --------------------------------------------------------------------------------------------------------
570
571-- Number of records.
572--
573SELECT COUNT(*) FROM race_results;
574--
575-- But that's the total across all races.
576
577-- When aggregating it almost always makes sense to first partition your data using GROUP BY.
578--
579SELECT race_id, COUNT(*) FROM race_results GROUP BY race_id;
580
581-- Actions:
582--
583-- * Write a query which will return race name, date, distance and number of finishers for each race.
584-- * Add the winning time for each race to the previous query. Is there more cleaning required?
585
586-- You can't use WHERE to filter on aggregate values. You need to use HAVING for that.
587--
588SELECT race_id, COUNT(*)
589FROM race_results
590GROUP BY race_id
591HAVING COUNT(*) > 200;
592
593-- SUBQUERY -----------------------------------------------------------------------------------------------------------
594
595-- It's possible to nest one SELECT inside another.
596--
597SELECT * FROM race_results WHERE club_id = (SELECT id FROM club WHERE name = 'DHS Old Boys');
598--
599-- The subquery appears between parenthesis.
600
601-- Notes:
602--
603-- * The outer query can be a SELECT, INSERT, UPDATE or DELETE.
604
605-- Actions:
606--
607-- * Get the same output as above by using a join.
608-- * Get the results for members of clubs with names beginning with an 'I'.
609-- * Find the names of the clubs which have more than 20 results.
610
611-- Notes:
612--
613-- * Common Table Expressions (something similar to a subquery) will be available for MySQL soon.
614
615-- VARIABLES ----------------------------------------------------------------------------------------------------------
616
617-- User defined variable names start with a '@'.
618--
619SET @club_name = 'DHS Old Boys';
620--
621-- Variable names are case insensitive.
622--
623SELECT @club_name;
624SELECT @Club_Name;
625SELECT @CLUB_NAME;
626
627SELECT * FROM club WHERE name = @club_name;
628
629-- You can perform simple operations on variables.
630--
631SET @counter = 1;
632SET @counter = @counter + 1;
633SELECT @counter;
634
635-- INDEX --------------------------------------------------------------------------------------------------------------
636
637-- An index can massively improve query times. Without an index the database often has to do a brute force search in
638-- order to filter out the required records.
639--
640-- Suppose that we wanted to find the athletes who finished the Kearsney Striders 21.1 km in less than 90 minutes.
641--
642SELECT *
643FROM race_results
644WHERE race_id = 2 AND time < '01:30:00';
645--
646-- To satisfy this query the database literally has to search through all of the records to find those which satisfy
647-- the time predicate.
648
649-- Add an index on the time column.
650--
651ALTER TABLE race_results ADD INDEX time (time);
652--
653-- This will make the space consumed by this table bigger. It'll also mean that adding data to the table will be
654-- slightly slower.
655--
656-- But it should make queries that involve the time column much quicker.
657
658-- This new index is not actually very useful for the above query because it references both race_id and time. If this
659-- is a common query pattern then it makes sense to create a multi-column index.
660--
661ALTER TABLE race_results ADD INDEX race_time (race_id, time);
662--
663-- Now try the query again and see how much faster it executes.
664
665-- If you no longer need an index you can drop it.
666--
667DROP INDEX time ON race_results;
668DROP INDEX race_time ON race_results;
669
670-- Actions:
671--
672-- * Run the following query and make a note of how long it takes.
673--
674SELECT * FROM race_results WHERE position <= 3;
675--
676-- * Add an index which improves the performance on this query.
677-- * Run the query again and compare times.
678-- * Discard the new index.
679
680-- VIEW ---------------------------------------------------------------------------------------------------------------
681
682-- If you find yourself running the same query repeatedly then perhaps it's time to create a view. A view is just a
683-- predefind SQL query which has been assigned a name.
684--
685-- We might frequently need to find the names of winners of all races.
686--
687SELECT name AS winner FROM race_results WHERE position = 1;
688
689-- We can then create a view from this query.
690--
691CREATE VIEW race_winners AS SELECT name AS winner FROM race_results WHERE position = 1;
692
693-- And then we simply treat the view as if it were a table.
694--
695SELECT * FROM race_winners;
696
697-- Actions:
698--
699-- * Create a query which adds a pace (minutes per km) column to the results. Hint: You might find TRUNCATE() and
700-- TIME_TO_SEC() functions useful.
701-- * Convert this query into a view.
702
703-- VISUALISE ----------------------------------------------------------------------------------------------------------
704
705-- Take a look at the table relationships to get a high level view of the database.
706
707-- TRANSACTIONS -------------------------------------------------------------------------------------------------------
708
709-- A transaction is a group of statements that are executed together. The principal advantage of this is that
710--
711-- - if something goes wrong then you can ROLLBACK any changes; or
712-- - if everything goes according to plan then you can COMMIT.
713
714-- Update when club is 'No Club'.
715--
716START TRANSACTION;
717UPDATE race_results SET club_id = NULL WHERE club_id IN (SELECT id FROM club WHERE name = 'No Club');
718DELETE FROM club WHERE name = 'No Club';
719--
720-- If we are happy with the changes then we can COMMIT.
721--
722COMMIT;
723
724START TRANSACTION;
725UPDATE race_results SET gender = NULL WHERE gender = 'Unknown';
726--
727-- If we change our minds or something goes wrong then we can ROLLBACK.
728--
729ROLLBACK;
730
731-- If you are not inside a transaction then autocommit applies.
732--
733UPDATE race_results SET gender = NULL WHERE gender = 'Unknown';
734
735-- STORED ROUTINES ----------------------------------------------------------------------------------------------------
736
737-- A 'stored routine' is a bit of SQL code which is stored on the database server.
738
739-- Notes:
740--
741-- * To get these to work in phpMyAdmin you need to create the routine in one transaction and then invoke it in a
742-- separate transaction.
743
744-- The default statement delimiter in SQL is ";". Since we could have multiple statements within a routine definition
745-- it's important to differentiate between these and the delimiter at the end of the definition. To do this we can
746-- change the delimeter.
747--
748DELIMITER $$
749SELECT 1$$
750DELIMITER ;
751
752-- FUNCTION
753--
754-- Function parameters are always of type IN.
755--
756DELIMITER $$
757CREATE FUNCTION time_to_min(time TIME) RETURNS FLOAT DETERMINISTIC
758BEGIN
759 RETURN TIME_TO_SEC(time) / 60;
760END$$
761DELIMITER ;
762--
763SELECT time_to_min('01:30:00');
764--
765SELECT *, TRUNCATE(time_to_min(time), 2) AS minutes FROM race_results;
766
767-- Actions:
768--
769-- * Create a function which calculates pace in minutes per km.
770
771-- PROCEDURE
772--
773DELIMITER $$
774CREATE PROCEDURE count_clubs () SELECT COUNT(*) FROM club; $$
775DELIMITER ;
776--
777CALL count_clubs;
778
779-- Creating a procedure with a compound statement and local variables.
780--
781DELIMITER $$
782CREATE PROCEDURE locals ()
783BEGIN
784 DECLARE a INT DEFAULT 10;
785 DECLARE b, c INT;
786 SET a = a + 100;
787 SET b = 15;
788 SET c = a - b;
789 SELECT a, b, c;
790END$$
791DELIMITER ;
792--
793CALL locals;
794
795-- Procedure parameters can be of type IN, OUT or INOUT.
796--
797DELIMITER $$
798CREATE PROCEDURE club_head (IN n INT)
799BEGIN
800 SELECT * FROM club LIMIT n;
801END$$
802DELIMITER ;
803--
804CALL club_head(6);
805--
806DELIMITER $$
807CREATE PROCEDURE longest_name (OUT length INT)
808BEGIN
809 SELECT MAX(CHAR_LENGTH(name)) INTO length FROM race_results;
810END$$
811DELIMITER ;
812--
813CALL longest_name(@longest);
814SELECT @longest;
815
816-- Drop procedures when we are done.
817--
818DROP PROCEDURE count_clubs;
819DROP PROCEDURE locals;
820DROP PROCEDURE club_head;
821DROP PROCEDURE longest_name;
822
823-- Actions:
824--
825-- * Create a procedure which returns the name of the club with the most results as an OUT parameter.
826
827-- Notes:
828--
829-- * It's possible to have conditionals and iterations inside routines.
830
831-- TRIGGER ------------------------------------------------------------------------------------------------------------
832
833-- You can trigger events when an INSERT, DELETE or UPDATE takes place on a table.
834
835-- ====================================================================================================================
836-- SOLUTIONS
837-- ====================================================================================================================
838
839-- UPDATE -------------------------------------------------------------------------------------------------------------
840
841UPDATE musician SET band_type = "Pop" WHERE band_name = "The Beatles";
842UPDATE musician SET birth = "1940-07-07" WHERE NAME LIKE "Ringo%";
843UPDATE musician SET birth = "1943-02-25" WHERE NAME LIKE "George%";
844
845-- ALTER --------------------------------------------------------------------------------------------------------------
846
847UPDATE musician SET death = "1980-12-08" WHERE NAME LIKE "John%";
848UPDATE musician SET death = "2001-11-29" WHERE NAME LIKE "George%";
849
850-- PRIMARY KEY --------------------------------------------------------------------------------------------------------
851
852ALTER TABLE musician ADD COLUMN band_id INT DEFAULT NULL;
853
854UPDATE musician SET band_id = 1 WHERE band_name = "The Beatles";
855
856ALTER TABLE musician DROP COLUMN band_name;
857ALTER TABLE musician DROP COLUMN band_type;
858
859-- FOREIGN KEY CONSTRAINTS --------------------------------------------------------------------------------------------
860
861-- RELATIONSHIPS ------------------------------------------------------------------------------------------------------
862
863-- JOINS --------------------------------------------------------------------------------------------------------------
864
865-- CREATING TABLES WITH QUERIES ---------------------------------------------------------------------------------------
866
867-- DROP TABLE ---------------------------------------------------------------------------------------------------------
868
869-- DROP DATABASE ------------------------------------------------------------------------------------------------------
870
871-- IMPORTING DATA -----------------------------------------------------------------------------------------------------
872
873CREATE DATABASE running;
874
875USE running;
876
877CREATE TABLE race_results (
878 position INT,
879 time TIME,
880 gender CHAR(7),
881 club VARCHAR(128),
882 race VARCHAR(128),
883 date DATE,
884 distance FLOAT,
885 name VARCHAR(128)
886);
887
888ALTER TABLE race_results ADD COLUMN id INT PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
889
890CREATE TABLE club AS (
891 SELECT DISTINCT club AS name FROM race_results
892);
893ALTER TABLE club ADD COLUMN id INT PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
894
895ALTER TABLE race_results ADD COLUMN club_id INT;
896ALTER TABLE race_results ADD FOREIGN KEY (club_id) REFERENCES club(id);
897
898UPDATE race_results
899INNER JOIN
900 club
901ON
902 race_results.club = club.name
903SET club_id = club.id;
904
905ALTER TABLE race_results DROP COLUMN club;
906
907-- SUB-QUERY ----------------------------------------------------------------------------------------------------------
908
909SELECT * FROM race_results WHERE club_id IN (SELECT id FROM club WHERE name LIKE 'I%');
910
911SELECT name
912FROM
913 club
914WHERE
915 id IN (SELECT club_id FROM race_results GROUP BY club_id HAVING COUNT(*) > 20);
916
917-- VIEW ---------------------------------------------------------------------------------------------------------------
918
919SELECT *, TRUNCATE(TIME_TO_SEC(time) / 60 / distance, 2) AS pace
920FROM
921 race_results A
922INNER JOIN
923 race B
924ON
925 A.race_id = B.id;
926
927-- STORED ROUTINES ----------------------------------------------------------------------------------------------------
928
929-- FUNCTION
930--
931DELIMITER $$
932CREATE FUNCTION pace(time TIME, km FLOAT) RETURNS FLOAT
933BEGIN
934 RETURN time_to_min(time) / km;
935END$$
936DELIMITER ;
937--
938SELECT pace('00:45:00', 10);
939--
940SELECT *, TRUNCATE(pace(time, distance), 2)
941FROM
942 race_results A
943INNER JOIN
944 race B
945ON
946 A.race_id = B.id;
947
948-- PROCEDURE
949--
950DELIMITER $$
951CREATE PROCEDURE biggest_club (OUT name VARCHAR(128))
952BEGIN
953 SELECT club.name INTO name
954 FROM
955 race_results
956 JOIN
957 club
958 ON
959 race_results.club_id = club.id
960 WHERE
961 club.name NOT IN ('NA', 'No Club')
962 GROUP BY club.id
963 ORDER BY COUNT(*) DESC
964 LIMIT 1;
965END$$
966DELIMITER ;
967--
968CALL biggest_club(@longest);
969SELECT @longest;