网站地址:
https://sqlbolt.com/
Lesson1:
-- https://sqlbolt.com/lesson/select_queries_introduction
-- Find the title of each film ✓ SELECT `TITLE ` FROM `movies`; -- Find the director of each film ✓ SELECT `DIRECTOR` FROM movies; -- Find the title and director of each film ✓ SELECT `TITLE`,`DIRECTOR` FROM movies; -- Find the title and year of each film ✓ SELECT `TITLE`,`YEAR` FROM movies; -- Find all the information about each film ✓ SELECT * FROM movies;
Lesson2:
-- https://sqlbolt.com/lesson/select_queries_with_constraints -- Find the movie with a row id of 6 ✓ SELECT * FROM movies WHERE `ID` = 6; -- Find the movies not released in the years between 2000 and 2010 ✓ SELECT * FROM movies WHERE `YEAR` BETWEEN 2000 AND 2010; -- Find the movies not released in the years between 2000 and 2010 ✓ SELECT `TITLE`, `YEAR` FROM movies WHERE `YEAR` < 2000 OR `YEAR` > 2010; SELECT * FROM movies WHERE `YEAR` NOT BETWEEN 2000 AND 2010; -- Find the first 5 Pixar movies and their release year ✓ SELECT `TITLE`, `YEAR` FROM movies WHERE `YEAR` <= 2003;
Lesson3:
https://sqlbolt.com/lesson/select_queries_with_constraints_pt_2 -- Find all the Toy Story movies ✓ SELECT * FROM movies WHERE `TITLE ` LIKE '%Toy Story%'; -- Find all the movies directed by John Lasseter ✓ SELECT * FROM movies WHERE `DIRECTOR` = 'John Lasseter' -- Find all the movies (and director) not directed by John Lasseter ✓ SELECT * FROM movies WHERE `DIRECTOR` != 'John Lasseter' -- Find all the WALL-* movies ✓ SELECT * FROM movies WHERE `TITLE` LIKE '%WALL-%'
Lesson4:
-- https://sqlbolt.com/lesson/filtering_sorting_query_results -- List all directors of Pixar movies (alphabetically), without duplicates ✓ SELECT DISTINCT `DIRECTOR` FROM movies ORDER BY `DIRECTOR` -- List the last four Pixar movies released (ordered from most recent to least) ✓ SELECT * FROM movies ORDER BY `YEAR` DESC LIMIT 4 -- List the first five Pixar movies sorted alphabetically ✓ SELECT * FROM movies ORDER BY `TITLE` ASC LIMIT 5 -- List the next five Pixar movies sorted alphabetically ✓ SELECT * FROM movies ORDER BY `TITLE` ASC LIMIT 5, 5
Lesson5:
-- https://sqlbolt.com/lesson/select_queries_review -- List all the Canadian cities and their populations ✓ SELECT * FROM north_american_cities WHERE `COUNTRY` = 'Canada'; -- Order all the cities in the United States by their latitude from north to south ✓ SELECT * FROM north_american_cities WHERE `COUNTRY` = 'United States' ORDER BY `LATITUDE` DESC; -- List all the cities west of Chicago, ordered from west to east ✓ SELECT `CITY`,`LONGTITUDE` FROM north_american_cities WHERE `LONGTITUDE` < -87.629798 ORDER BY `LONGTITUDE` ASC; -- List the two largest cities in Mexico (by population) ✓ SELECT * FROM north_american_cities WHERE `COUNTRY` = 'Mexico' ORDER BY `POPULATION` DESC LIMIT 2 -- List the third and fourth largest cities (by population) in the United States and their population ✓ SELECT * FROM north_american_cities WHERE `COUNTRY` = 'United States' ORDER BY `POPULATION` DESC LIMIT 2, 2
Lesson6:
-- https://sqlbolt.com/lesson/select_queries_with_joins -- Find the domestic and international sales for each movie ✓ SELECT A.*, B.`DOMESTIC_SALES`, B.`INTERNATIONAL_SALES` FROM movies AS A JOIN `boxoffice` AS B ON A.ID = B.MOVIE_ID; -- Show the sales numbers for each movie that did better internationally rather than domestically ✓ SELECT `title`, `domestic_sales`, `international_sales` FROM `MOVIES` AS A JOIN `BOXOFFICE` AS B ON A.id = B.movie_id WHERE B.`international_sales ` > B.`domestic_sales`; -- List all the movies by their ratings in descending order ✓ SELECT A.`title`, B.`rating` FROM `movies` AS A JOIN `boxoffice` AS B ON movies.id = boxoffice.movie_id ORDER BY rating DESC;
Lesson7:
-- https://sqlbolt.com/lesson/select_queries_with_outer_joins -- Find the list of all buildings that have employees ✓ SELECT DISTINCT building FROM employees; -- Find the list of all buildings and their capacity ✓ SELECT * FROM Buildings -- List all buildings and the distinct employee roles in each building (including empty buildings) ✓ SELECT DISTINCT A.`building_name`, B.`role ` FROM buildings AS A LEFT JOIN employees AS B ON A.building_name = B.building;
Lesson8:
-- https://sqlbolt.com/lesson/select_queries_with_nulls -- Find the name and role of all employees who have not been assigned to a building ✓ SELECT * FROM Employees WHERE Building IS NULL -- Find the names of the buildings that hold no employees ✓ SELECT A.Building_name FROM Buildings AS A LEFT JOIN Employees AS B ON A.Building_name = B.Building WHERE B.Building IS NULL
Lesson9:
-- https://sqlbolt.com/lesson/select_queries_with_expressions -- List all movies and their combined sales in millions of dollars ✓ SELECT A.title, (domestic_sales + international_sales) / 1000000 AS gross_sales_millions FROM movies AS A, JOIN boxoffice AS B ON A.id = B.movie_id; -- List all movies and their ratings in percent ✓ SELECT A.TITLE, B.RATING * 10 AS rating_percent FROM movies AS A JOIN Boxoffice AS B ON A.ID = B.MOVIE_ID -- List all movies that were released on even number years ✓ SELECT title, year FROM movies WHERE year % 2 = 0;
Lesson10:
-- https://sqlbolt.com/lesson/select_queries_with_aggregates -- Find the longest time that an employee has been at the studio ✓ SELECT * FROM employees ORDER BY Years_employed DESC LIMIT 1; -- For each role, find the average number of years employed by employees in that role ✓ SELECT ROLE, AVG(Years_employed) FROM Employees GROUP BY ROLE -- Find the total number of employee years worked in each building ✓ SELECT building, SUM(years_employed) as Total_years_employed FROM employees GROUP BY building;
Lesson11:
-- https://sqlbolt.com/lesson/select_queries_with_aggregates_pt_2 --Find the number of Artists in the studio (without a HAVING clause) ✓ SELECT ROLE, COUNT(NAME) FROM employees WHERE ROLE = 'Artist' GROUP BY ROLE; SELECT role, COUNT(*) as Number_of_artists FROM employees WHERE role = "Artist"; -- Find the number of Employees of each role in the studio ✓ SELECT role, COUNT(*) as Number_of_artists FROM employees GROUP BY ROLE -- Find the total number of years employed by all Engineers ✓ SELECT SUM(Years_employed) FROM Employees WHERE ROLE = 'Engineer';
Lesson12:
-- https://sqlbolt.com/lesson/select_queries_order_of_execution -- Find the number of movies each director has directed ✓ SELECT Director,COUNT(*) FROM movies GROUP BY Director; -- Find the total domestic and international sales that can be attributed to each director ✓ SELECT Director, SUM(B.Domestic_sales + B.International_sales) AS Cumulative_sales_from_all_movies FROM Movies AS A JOIN Boxoffice AS B ON A.ID = B.MOVIE_ID GROUP BY A.Director
Lesson13:
-- https://sqlbolt.com/lesson/inserting_rows -- Add the studio's new production, Toy Story 4 to the list of movies (you can use any director) ✓ INSERT INTO Movies (Id, Title, Director, Year, Length_minutes) VALUES (NULL, 'Toy Story 4', 'John Lasseter', NULL, NULL) -- Toy Story 4 has been released to critical acclaim! It had a rating of 8.7, and made 340 million domestically and 270 million internationally. Add the record to the BoxOffice table. ✓ INSERT INTO Boxoffice (Movie_id, Rating, Domestic_sales, International_sales) VALUES (15, ' 8.7', 34000000, 27000000)
Lesson14:
-- https://sqlbolt.com/lesson/updating_rows -- The director for A Bug's Life is incorrect, it was actually directed by John Lasseter ✓ UPDATE `Movies` SET DIRECTOR = 'John Lasseter' WHERE TITLE = "A Bug's Life" -- The year that Toy Story 2 was released is incorrect, it was actually released in 1999 ✓ UPDATE `Movies` SET YEAR = 1999 WHERE TITLE = 'Toy Story 2' -- Both the title and director for Toy Story 8 is incorrect! The title should be "Toy Story 3" and it was directed by Lee Unkrich ✓ UPDATE `Movies` SET DIRECTOR = 'Lee Unkrich', TITLE = 'Toy Story 3' WHERE TITLE = 'Toy Story 8'
Lesson15:
-- https://sqlbolt.com/lesson/deleting_rows -- This database is getting too big, lets remove all movies that were released before 2005. ✓ DELETE FROM movies WHERE YEAR < 2005; -- Andrew Stanton has also left the studio, so please remove all movies directed by him. ✓ DELETE FROM movies WHERE DIRECTOR = 'Andrew Stanton';
Lesson16:
-- https://sqlbolt.com/lesson/creating_tables -- Create a new table named Database with the following columns: – Name A string (text) describing the name of the database – Version A number (floating point) of the latest version of this database – Download_count An integer count of the number of times this database was downloaded This table has no constraints. CREATE TABLE `Database`( NAME VARCHAR, VERSION FLOAT, Download_count INT )
Lesson17:
-- Add a column named Aspect_ratio with a FLOAT data type to store the aspect-ratio each movie was released in. ✓ ALTER TABLE `Movies` ADD COLUMN `Aspect_ratio` FLOAT DEFAULT 2.39 -- Add another column named Language with a TEXT data type to store the language that the movie was released in. Ensure that the default for this language is English. ✓ ALTER TABLE `Movies` ADD COLUMN `Language` TEXT DEFAULT "English";
Lesson18:
-- https://sqlbolt.com/lesson/dropping_tables -- We've sadly reached the end of our lessons, lets clean up by removing the Movies table ✓ DROP TABLE `MOVIES`; -- And drop the BoxOffice table as well ✓ DROP TABLE `BOXOFFICE`;