我的sqlbolt 第一阶段答案

推荐一个在线学习sql的网站，

附上我自己做的答案：

LESSION 1:
SELECT title FROM Movies;
SELECT director FROM Movies;
SELECT TITLE , director FROM Movies;
SELECT TITLE , YEar FROM Movies;
SELECT * FROM MOVIES;


LESSION 2:
SELECT * FROM movies WHERE id=6;
SELECT * FROM MOVIES WHERE year between 2000 and 2010;
SELECT * FROM MOVIES WHERE year > 2000 and year < 2010;
SELECT * FROM MOVIES WHERE not (year > 2000 and year <= 2010);
SELECT TITLE,YEAR from MOVIES LIMIT 0,5;
SELECT TITLE,YEAR from MOVIES LIMIT 5;


LESSION 3:
SELECT * FROM movies WHERE TITLE like "%Toy Story%";
SELECT * FROM movies WHERE DIRECTOR = "John Lasseter";
SELECT * FROM movies WHERE DIRECTOR != "John Lasseter";
SELECT * FROM MOVIES WHERE TITLE like "WALL%";


LESSION 4:
SELECT DISTINCT DIRECTOR FROM movies order by DIRECTOR;
SELECT * FROM movies ORDER BY YEAR DESC LIMIT 0,4;
SELECT * FROM movies ORDER BY TITLE ASC LIMIT 0,5;
SELECT * FROM movies ORDER BY TITLE ASC LIMIT 5 OFFSET 5;


LESSION 5:
SELECT City,Population FROM north_american_cities WHERE Country="Canada";
SELECT * FROM north_american_cities where Country="United States" order by latitude DESC;
SELECT * FROM north_american_cities where Longitude < (SELECT Longitude from north_american_cities where City="Chicago") ORDER BY Longitude;
SELECT * from North_american_cities Where Country="Mexico" ORDER BY Population DESC LIMIT 2;
SELECT * from North_american_cities WHERE Country="United States" ORDER BY Population DESC LIMIT 2 OFFSET 3,
SELECT * from North_american_cities WHERE Country="United States" ORDER BY Population DESC LIMIT 2 OFFSET 2;


LESSION 6:
SELECT Domestic_sales,International_sales,Title from Boxoffice INNER JOIN Movies ON Boxoffice.Movie_id = Movies.Id;
SELECT Domestic_sales,International_sales,Title from Boxoffice INNER JOIN Movies ON Boxoffice.Movie_id = Movies.Id WHERE International_sales > Domestic_sales;
SELECT Rating , Title from Boxoffice INNER JOIN Movies ON Boxoffice.Movie_id = Movies.Id ORDER BY Rating DESC;


LESSION 7: ONLY LEFT JOIN is supported in the exercise below.
SELECT DISTINCT Building FROM Employees LEFT JOIN Buildings ON Employees.Building = Buildings.Building_name;
SELECT Building_name, Capacity from Buildings ;
SELECT DISTINCT Role, Building_name FROM Buildings LEFT JOIN Employees ON Buildings.Building_name = Employees.Building ;


LESSION 8:
SELECT Role,Name FROM employees where Building is null;
SELECT DISTINCT building_name FROM buildings LEFT JOIN employees ON building_name = building WHERE role IS NULL;


LESSION 9:
SELECT Title,(Domestic_sales+International_sales)/1000000 AS "commbind sales" FROM Movies 
LEFT JOIN Boxoffice ON Movies.ID=Boxoffice.Movie_id;
#实在不知道为什么是乘10
SELECT title, rating * 10 AS rating_percent FROM movies JOIN boxoffice ON movies.id = boxoffice.movie_id;
SELECT title FROM movies WHERE Year%2=0;


LESSION 10:
SELECT MAX(Years_employed) FROM employees;
SELECT Role,AVG(Years_employed) from Employees group by Role;
SELECT Building,sum(Years_employed) FROM Employees group by Building;


LESSION 11:
SELECT count(Name) FROM employees where Role ="Artist";
SELECT Role,count(Name) FROM employees GROUP BY Role;
SELECT Role,sum(years_employed) from Employees where Role="Engineer";


LESSION 12:
SELECT count(*),Director FROM movies GROUP BY Director;
SELECT Director,sum(Domestic_sales+International_sales) as total FROM movies LEFT JOIN Boxoffice ON Movies.id=Boxoffice.Movie_id group by Director;


LESSION 13:
INSERT INTO Movies VALUES (4, "Toy Story 4","John Lasseter",2000,9);
INSERT INTO BoxOffice VALUES(4, 8.7, 340000000, 270000000);
INSERT INTO BoxOffice VALUES(4, 8.7, 340000000, 270000000);


LESSION 14:
UPDATE Movies set Director="John Lasseter" WHERE Title="A Bug's Life";
UPDATE Movies set Year=1999 WHERE Title="Toy Story 2";
UPDATE Movies set Director="Lee Unkrich" ,Title="Toy Story 3" WHERE Title="Toy Story 8";


LESSION 15:
DELETE FROM Movies where Year < 2005;
DELETE FROM Movies where Director = "Andrew Stanton";


LESSION 16:
CREATE TABLE Database (Name text,Version float, Download_count int);


LESSION 17:
ALTER TABLE Movies ADD Aspect_ratio FLOAT;
ALTER TABLE Movies ADD Language TEXT DEFAULT "English";


LESSION 18:
Drop TABLE MOVIES;
Drop TABLE BoxOffice;