SQL何もわからんな人へ捧げる『SQL Bolt』

SQLBolt - Learn SQL - Introduction to SQL

SQLをWeb上で学べる教材。記述したクエリの結果がリアルタイムに出力される。

以下、コードサンプルといくつかの演習問題に対する解答例。簡単すぎる問題は省略。

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

-- 1. Find all the Toy Story movies
SELECT * FROM movies
where title
  like '%Toy Story%'
;

-- 3. Find all the movies (and director) not directed by John Lasseter
SELECT * FROM movies
where director
    not like '%John Lasseter%'
;

SQL Review: Simple SELECT Queries

SELECT * FROM north_american_cities
where Country like 'United States'
order by latitude ASC;

SQL Lesson 7: OUTER JOINs

-- Find the list of all buildings that have employees
SELECT distinct e.building
FROM employees e
join Buildings b
on b.building_name = e.building
;

-- List all buildings and the distinct employee roles in each building (including empty buildings)
SELECT distinct
    b.building_name,
    e.role
FROM Buildings b
left outer join employees e
on b.building_name = e.building
;

SQL Lesson 8: A short note on NULLs

SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

Exercise

-- 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 * 
FROM buildings b
    left outer join employees e
    on e.building = b.building_name
where
    e.building is null
;

SQL Lesson 9: Queries with expressions

-- Example query with expressions
SELECT particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;

-- Select query with expression aliases
SELECT col_expression AS expr_description, …
FROM mytable;

-- Example query with both column and table name aliases
SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
  ON mywidgets.id = widget_sales.widget_id;

Exercise

-- List all movies and their combined sales in millions of dollars
SELECT
    m.title,
    (b.domestic_sales + b.international_sales) / 1000000
FROM movies m
left outer join Boxoffice b
on b.movie_id = m.id
;

-- List all movies that were released on even number years
SELECT
    m.title
FROM movies m
left outer join Boxoffice b
on b.movie_id = m.id
where m.year % 2 = 0
;

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

-- Select query with aggregate functions over all rows
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression;

Exercise

-- Find the longest time that an employee has been at the studio
SELECT max(years_employed)
FROM employees
;

-- 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)
FROM employees
group by building
;

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

-- Select query with HAVING constraint
SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;

Did you know? If you aren't using the GROUP BY clause, a simple WHERE clause will suffice.

Exercise

-- Find the number of Artists in the studio (without a HAVING clause)
SELECT count(*)
FROM employees
where role = 'Artist'
;

-- Find the number of Employees of each role in the studio
SELECT role,count(*)
FROM employees
group by role
;

-- Find the total number of years employed by all Engineers
SELECT role,sum(Years_employed)
FROM employees
group by role
having role = 'Engineer'
;

SQL Lesson 12: Order of execution of a Query

-- Complete SELECT query
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

Exercise

-- Find the number of movies each director has directed
SELECT m.director, count(*)
FROM movies m
    left outer join boxoffice b
    on b.movie_id = m.id
group by director
order by director
;

-- Find the total domestic and international sales that can be attributed to each director
SELECT m.director, sum(domestic_sales+international_sales)
FROM movies m
    left outer join boxoffice b
    on b.movie_id = m.id
group by director
order by director
;

SQL Lesson 13: Inserting rows

-- Insert statement with values for all columns
INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;
-- Insert statement with specific columns
INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

SQL Lesson 14: Updating rows

-- Update statement with values
UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

Exercise

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

• 文字列の中でシングルクォーテーション「'」を使う場合は全体の括弧はダブルクォーテーション「"」を使う

SQL Lesson 15: Deleting rows

-- Delete statement with condition
DELETE FROM mytable
WHERE condition;

delete from movies
where director = 'Andrew Stanton'

SQL Lesson 16: Creating tables

-- Create table statement w/ optional table constraint and default value
CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

SQL Lesson 17: Altering tables

-- Altering table to add new column(s)
ALTER TABLE mytable
ADD column DataType OptionalTableConstraint
    DEFAULT default_value;

-- Altering table to remove column(s)
ALTER TABLE mytable
DROP column_to_be_deleted;

-- Altering table name
ALTER TABLE mytable
RENAME TO new_table_name;

-- Add a column named Aspect_ratio with a FLOAT data type to store the aspect-ratio each movie was released in.
alter talbe movies
add Aspect_ratio float;

-- 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 Language text
default 'English';

SQL Lesson 18: Dropping tables

-- Drop table statement
DROP TABLE IF EXISTS mytable;

SQL Lesson X: To infinity and beyond!

これでクリアです。お疲れさまでした！