-- (#1) SELECT ALL DOGS SELECT * FROM dogs; -- (#2) LIMIT SELECT * FROM dogs LIMIT 2; -- (#3) COLUMN LIST SELECT dog_id, breed FROM dogs LIMIT 2; -- (#4) CALCULATIONS SELECT breed, round(max_height/12.0, 2) FROM dogs LIMIT 2; -- (#5) AGGREGATES SELECT COUNT(*), MIN(max_height), MAX(max_weight) FROM dogs; -- (#6) Naming aggregate columns SELECT COUNT(*) AS "row count", MIN(max_height) AS "min height", MAX(max_weight) AS "max weight" FROM dogs; -- (#7) Naming regular columns SELECT breed AS "dog breed", round(max_height/12.0, 2) AS "height in inches" FROM dogs; -- (#8) SORTING ROWS WITH ORDER BY SELECT breed, type, general_health FROM dogs ORDER BY type, breed, general_health; -- (#9) SORTING ROWS (reverse order) SELECT type, breed, max_height FROM dogs ORDER BY type, max_height DESC, breed; --- (#10) FILTERING ROWS WITH WHERE select * FROM dogs WHERE type = 'Sporting Dogs'; -- (#11) Not sporting dogs select * FROM dogs WHERE type != 'Sporting Dogs'; -- (#12) Dogs with a height of 30 inches or larger select * FROM dogs WHERE max_height >= 30; -- (#13) Sporting and Working dogs select * FROM dogs WHERE type IN ('Sporting Dogs', 'Working Dogs'); -- (#14) NOT Sporting or Working dogs select * FROM dogs WHERE type NOT IN ('Sporting Dogs', 'Working Dogs'); -- (#15) A range of heights select * FROM dogs WHERE max_height BETWEEN 12 AND 18; -- (#16) - WILDCARD TO SEARCH WITHIN SELECT breed, type, general_health FROM dogs WHERE breed LIKE '%Pinscher%'; -- (#17) Convert case first... SELECT * FROM dogs WHERE lower(breed) LIKE '%pinscher%'; -- (#18) FILTERING ROWS WITH WHERE, AND, OR SELECT breed, type, general_health FROM dogs WHERE type = 'Sporting Dogs' AND ( general_health = 'Excellent' OR general_health = 'Very Good'); -- (#19) Grouped aggregates using GROUP BY SELECT type, count(*) AS 'Number of Dogs' FROM dogs GROUP BY type; -- (#20) Grouping and the WHERE clause -- only put rows that qualify into the groups SELECT type, count(*) AS 'Small Dogs' FROM dogs WHERE max_height < 30 GROUP BY type; -- (#21) Grouping and the HAVING clause SELECT type, count(*) AS 'Breeds' FROM dogs GROUP BY type HAVING count(*) > 30; -- (#22) 'HAVING' and 'WHERE' together -- how many small dogs in each type -- where the group's average life span is more than 14 years SELECT type, count(*) AS 'Small Dogs' FROM dogs WHERE max_height < 30 GROUP BY type HAVING count(*) > 14; -- (#23) The 'HAVING' clause and other aggregates -- Groups with longer-lived dogs on average SELECT type, count(*) AS 'Small Dogs' FROM dogs GROUP BY type HAVING avg(max_life_span) > 14; -- (#24) SELECT DISTINCT -- Show the unique values for a table's column SELECT DISTINCT type FROM dogs; -- (#25) Subqueries -- What dogs live longer than the -- average life span? SELECT breed AS "Above average lifespan dogs" FROM dogs WHERE max_life_span > ( SELECT AVG(max_life_span) FROM dogs ); -- (#26) Joins SELECT dogs.dog_id, breed, ranking FROM dogs INNER JOIN popularity ON (dogs.dog_id = popularity.dog_id) ORDER BY dogs.breed, popularity.year; -- (#27) CREATE A NEW TABLE CREATE TABLE commands ( spoken_word varchar(40) not null, gestures varchar(40) null, rating integer not null ); -- (#28) INSERT a ROW into the table INSERT INTO commands VALUES ('sit', 'point down', 5); -- (#29) INSERT a ROW into the table -- listing the fields to set INSERT INTO commands(spoken_word, rating) VALUES ('stop', 2); -- (#30) recreate the table commands with a primary key CREATE TABLE commands ( command_id integer primary key autoincrement, spoken_word varchar(40) not null, gestures varchar(40) null, rating integer not null ); -- (#31) Use the auto incrementing primary key for the command_id -- note we skip the field with the auto incrementing key INSERT INTO commands (spoken_word, gestures, rating) VALUES ('stop', 'wave hands', 2); -- (#32) for all commands, add 1 to the rating UPDATE commands SET rating = rating + 1; -- (#33) for a specific command UPDATE commands SET rating = 5 WHERE command_id = 1; -- (#34) add a 'notes' column to the dogs table ALTER TABLE dogs ADD COLUMN notes varchar(2000) null; -- (#35) set a single dog's notes UPDATE dogs SET notes = ‘Clownish dog’ where dog_id = 25; -- show me all dogs with notes (#36) SELECT * FROM dogs WHERE notes IS NOT NULL; -- (#37) show me all dogs without notes SELECT * FROM dogs WHERE notes IS NULL; -- remove ALL rows in the dogs table -- (#38) DO NOT DO THIS NOW DELETE FROM dogs; -- (#39) remove only the minpin! DELETE FROM dogs WHERE dog_id = 25; -- (#40) remove it by using a subquery to find DELETE FROM dogs WHERE dog_id IN ( SELECT dog_id FROM dogs WHERE breed = 'Miniature Pinscher');