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;