List of SQL Commands (important resource)
Codecademy SQL Commands (also great)
Take the following schema for a N64 SQL database
CREATE TABLE Games (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
year INTEGER NOT NULL,
developers TEXT,
genre TEXT
);
Create a database with this schema and seed it with some sample data
INSERT INTO Games(title, year, developers, genre) VALUES('The Legend of Zelda: The Ocarina of Time', 1998, 'Nintendo EAD', 'Action-Adventure');
INSERT INTO Games(title, year, developers, genre) VALUES('Super Smash Bros.', 1999, 'Hal Laboratory', 'Fighting');
INSERT INTO Games(title, year, developers, genre) VALUES('Super Mario 64', 1996, 'Nintendo EAD', 'Platforming');
INSERT INTO Games(title, year, developers, genre) VALUES('Golden Eye 007', 1997, 'Rare', 'First-Person Shooter');
INSERT INTO Games(title, year, developers, genre) VALUES('Mario Kart 64', 1996, 'Nintendo EAD', 'Racing');
INSERT INTO Games(title, year, developers, genre) VALUES('Star Fox 64', 1997, 'Nintendo EAD', 'Rail/Scrolling Shooter');
INSERT INTO Games(title, year, developers, genre) VALUES('Perfect Dark', 2000, 'Rare', 'First-Person Shooter');
INSERT INTO Games(title, year, developers, genre) VALUES('Star Wars: Shadow of the Empire', 1996, 'Lucas Arts', 'Action');
INSERT INTO Games(title, year, developers, genre) VALUES('Banjo-Kazooie', 1998, 'Rare', 'Platforming');
INSERT INTO Games(title, year, developers, genre) VALUES('Mario Party', 1998, 'Hudson Soft', 'Party Game');
INSERT INTO Games(title, year, developers, genre) VALUES('Tony Hawk''s Pro Skater' , 1996, 'Neversoft', 'Extreme Sports');
Use SQL queries to complete the following tasks
-
Select the title of all games
-
Show all the years in the database.
-
Show the title of each game made by 'Rare'.
-
Select all games that were made before 1998.
-
Find the average release year of all games.
-
Show the developer names of each game, in alphabetical order.
-
Show the titles of games not made by 'Nintendo EAD'.
-
Add the the game 'F-Zero X', released in 1998, made by 'Nintendo EAD', and with the genre 'Racing'.
-
Show all the game titles in descending order of their release year.
-
Remove all games not made by 'Hudson Soft'.
-
Show the average release year for games that were made by Rare or have the title
Tony Hawk''s Pro Skater
in order of their title -
Show the id and title and genre of all games that were either:
Made before 1997,
Made by Hal Laboratory,
Or, have the genre Platforming AND are made by Rare.