Sqlbolt网站试题答案

基础入门学习SQL语句网站https://sqlbolt.com/
由于网站都是全英文，可以使用网页翻译对照学习。以下代码不区分大小写，双引号或者单引号里面的要区分大小写。（答案不唯一）
SQL Lesson 1: SELECT queries 101

SELECT title FROM movies;

SELECT director FROM movies;

SELECT title,director FROM movies;

SELECT title,year FROM movies;

SELECT * FROM movies;

SQL Lesson 2: Queries with constraints (Pt. 1)

SELECT * FROM movies where id=6;（查询的内容只显示title也是正确的）

SELECT * FROM movies where year between 2000 and 2010;

SELECT * FROM movies where year not between 2000 and 2010;

SELECT * FROM movies title limit 5;

SQL Lesson 3: Queries with constraints (Pt. 2)

SELECT * FROM movies where title like 'Toy Story%';

SELECT * FROM movies where director like "John Lasseter";（Like或者=都可以，使用双引号和单引号都可以）

SELECT * FROM movies where director not like "John Lasseter";

SELECT * FROM movies where title like "WALL-%";

SQL Lesson 4: Filtering and sorting Query results

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;

SQL Review: Simple SELECT Queries

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

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;

SQL Lesson 6: Multi-table queries with JOINs

SELECT * FROM movies inner join Boxoffice on movies.id=Boxoffice.Movie_id;

SELECT * FROM movies inner join Boxoffice on movies.id=Boxoffice.Movie_id where domestic_sales<International_sales;

SELECT * FROM movies inner join Boxoffice on movies.id=Boxoffice.Movie_id order by rating desc;

SQL Lesson 7: OUTER JOINs

SELECT distinct building FROM employees;

SELECT * FROM Buildings;

SELECT distinct Building_name,Role FROM Buildings left join Employees on Buildings.Building_name=Employees.Building;

SQL Lesson 8: A short note on NULLs

SELECT Name,Role FROM employees where building is null;

SELECT Building_name FROM Buildings left join  Employees on Buildings.Building_name=Employees.Building where name is null;

SQL Lesson 9: Queries with expressions

SELECT title,(Domestic_sales+International_sales)/1000000.0 as total FROM movies as m left join Boxoffice as b on m.id=b.Movie_id;

SELECT title,rating*10 as total FROM movies as m left join Boxoffice as b on m.id=b.Movie_id;

SELECT * FROM movies where year%2==0;

SQL Lesson 10: Queries with aggregates (Pt. 1)

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;

SQL Lesson 11: Queries with aggregates (Pt. 2)

SELECT count(Role) FROM employees where Role="Artist";

SELECT role,count(*) FROM employees group by Role;

SELECT sum(Years_employed) FROM employees where role="Engineer";
或者SELECT sum(Years_employed) FROM employees group by Role having role="Engineer";

SQL Lesson 12: Order of execution of a Query

SELECT director,count(title) FROM movies group by director;

SELECT Director,sum(Domestic_sales+International_sales) FROM movies inner join Boxoffice on movies.id= Boxoffice.Movie_id group by director;

SQL Lesson 13: Inserting rows

INSERT INTO Movies(id,title,director,year,length_minutes) VALUES(4,'Toy Story 4','John Lasseter',2010,99);

INSERT INTO Boxoffice(Movie_id,Rating,Domestic_sales,International_sales) VALUES(4,8.7,270000000,340000000)

SQL Lesson 14: Updating rows

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 Title='Toy Story 3',Director='Lee Unkrich' where id=(SELECT id FROM Movies where title='Toy Story 8')

SQL Lesson 15: Deleting rows

DELETE FROM Movies where Year<2005;

DELETE FROM Movies where Director='Andrew Stanton';

SQL Lesson 16: Creating tables

CREATE TABLE IF NOT EXISTS database(Name TEXT,Version float,Download_count INTEGER);

SQL Lesson 17: Altering tables

ALTER TABLE movies ADD Aspect_ratio FLOAT;

ALTER TABLE movies ADD Language TEXT DEFAULT English;

SQL Lesson 18: Dropping tables

DROP TABLE movies;

DROP TABLE BoxOffice;