Complete the tutorial at the link here
Fork and clone this repo, then for each of the 18 sections, copy and paste your solution in your fork. Submit by making a PR against this repo.
SECTION 1
-
SELECT title From movies
-
SELECT director From movies
-
SELECT director, title From movies
-
SELECT title, year From movies
-
SELECT * From movies
SECTION 2
-
SELECT * FROM movies WHERE id = 6
-
SELECT * FROM movies WHERE year BETWEEN 2000 AND 2010
-
SELECT * FROM movies WHERE year NOT BETWEEN 2000 AND 2010
-
SELECT *, year FROM movies WHERE id BETWEEN 1 AND 5
SECTION 3
-
SELECT * FROM movies WHERE title LIKE 'Toy Story%'
-
SELECT * FROM movies WHERE title LIKE 'Toy Story%'
-
SELECT * FROM movies WHERE director LIKE 'john lasseter'
-
SELECT * FROM movies WHERE director NOT LIKE 'John Lasseter'
-
SELECT * FROM movies WHERE title LIKE 'wall%'
SECTION 4
-
SELECT DISTINCT director FROM movies ORDER BY director ASC
-
SELECT * FROM movies ORDER BY year DESC LIMIT 4
-
SELECT * FROM movies ORDER BY title ASC LIMIT 5
-
SELECT * FROM movies ORDER BY title ASC LIMIT 5 OFFSET 5
SECTION 5
-
SELECT city, population
FROM north_american_cities WHERE country LIKE 'canada' -
SELECT city FROM north_american_cities WHERE country LIKE 'united states' ORDER BY latitude DESC
-
SELECT city FROM north_american_cities WHERE longitude < -87.629798 ORDER BY longitude ASC
-
SELECT city FROM north_american_cities WHERE country LIKE 'mexico' ORDER BY population DESC LIMIT 2
-
SELECT city FROM north_american_cities WHERE country LIKE 'united states' ORDER BY population DESC LIMIT 2 OFFSET 2
SECTION 6
-
SELECT * FROM movies INNER JOIN boxoffice ON movies.id = boxoffice.movie_id ORDER BY id
-
SELECT * FROM movies INNER JOIN boxoffice ON movies.id = boxoffice.movie_id WHERE boxoffice.domestic_sales < boxoffice.international_sales ORDER BY id
-
SELECT * FROM movies INNER JOIN boxoffice ON movies.id = boxoffice.movie_id ORDER BY boxoffice.rating DESC
SECTION 7
-
SELECT DISTINCT building FROM employees
-
SELECT * FROM buildings
-
SELECT DISTINCT role, building_name FROM buildings LEFT JOIN employees ON building = building_name
SECTION 8
-
SELECT name , role ,building FROM employees WHERE building IS null
-
SELECT DISTINCT building_name ,name FROM Buildings LEFT JOIN employees ON building_name = building WHERE name IS null
SECTION 9
-
SELECT title, (domestic_sales + international_sales) / 1000000 AS combined_sales_in_millions FROM movies JOIN boxoffice ON movies.id = boxoffice.movie_id
-
SELECT title,rating * 10 AS rating_in_percent FROM movies JOIN boxoffice ON movies.id = boxoffice.movie_id;
-
SELECT title, year FROM movies WHERE ((year/2) + (0.5)) * 2 != year // (also can use year % 2 = 0)
SECTION 10
-
SELECT name, MAX(years_employed) FROM employees;
-
SELECT name ,role , AVG(years_employed) FROM employees GROUP BY role
-
SELECT building, SUM(years_employed) as Total_years_employed FROM employees GROUP BY building
SECTION 11
-
SELECT name ,SUM(role LIKE 'artist') AS artist_sum FROM employees;
-
SELECT *, COUNT(name) AS number_of_employees_in_each_role FROM employees GROUP BY role
-
SELECT *, SUM(years_employed) FROM employees GROUP BY role HAVING role LIKE 'engineer'
SECTION 12
-
SELECT director, COUNT(title) AS sum_of_movies_directed_by_director FROM movies GROUP BY director
-
SELECT director, SUM(domestic_sales + international_sales) AS total_sales FROM movies INNER JOIN boxoffice ON id = movie_id GROUP BY director
SECTION 13
-
INSERT INTO movies VALUES(15,'Toy Story 4','John Lasseter',2100,95)
-
INSERT INTO boxoffice VALUES(15, 8.7,34000000,270000000)
SECTION 14
-
UPDATE movies SET director = 'John Lasseter' WHERE id = 2
-
UPDATE movies SET year = 1999 WHERE id = 3
-
UPDATE movies SET title = 'Toy Story 3', director = 'Lee Unkrich' WHERE id = 11
SECTION 15
-
DELETE FROM movies where year < 2005
-
DELETE FROM movies where director = 'Andrew Stanton'
SECTION 16
- CREATE TABLE IF NOT EXISTS Database ( name TEXT PRIMARY KEY, version INTERGER FLOAT, download_count INTERGER );
SECTION 17
-
ALTER TABLE movies ADD Aspect_ratio FLOAT
-
ALTER TABLE Movies ADD COLUMN LANGUAGE TEXT DEFAULT english;
SECTION 18
-
DROP TABLE IF EXISTS movies
-
DROP TABLE IF EXISTS boxoffice