sqlbolt.com答案附带易错点

SQL入门极好的交互式网站，答案附带易错点
网址：sqlbolt

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%";

sql连接正则表达式需要用LIKE来连接

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;

DISTINCT 丢弃具有重复列值的行

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 *,SUM(Domestic_sales+International_sales) FROM Movies 
LEFT JOIN BoxofficeON Id=Movie_id GROUP BY Director;

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 2;

OFFSET 2指的是第二个之后，不包括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 Movie_id =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 Building_name, Role FROM Buildings
LEFT JOIN EmployeesON Building_name=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;

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 *,sum(Domestic_sales+International_sales) 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);

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;