SQL

-- Example tabel
CREATE TABLE big_game (berkeley INTEGER, stanford INTEGER, year INTEGER); 

INSERT INTO big_game (berkeley, stanford, year) VALUES 
    (30, 7, 2002), 
    (28, 16, 2003), 
    (17, 38, 2014);

-- Selecting From Tables
SELECT [columns] FROM [tables] WHERE [condition] ORDER BY [columns] LIMIT [limit];

sqlite> SELECT berkeley FROM big_game WHERE year > 2002; 
28 
17

sqlite> SELECT berkeley, stanford FROM big_game WHERE berkeley > stanford; 
30|7 
28|16

-- SQL运算符
sqlite> select berkeley + stanford from big_game where berkeley > 10 and stanford > 10; 
55  -- 输出两队得分均超过 10 分的年份的分数总和
44

-- Joins
CREATE TABLE coaches ( name TEXT, start INTEGER, end INTEGER ); 

INSERT INTO coaches (name, start, end) VALUES 
    ('Jeff Tedford', 2002, 2012), 
    ('Sonny Dykes', 2013, 2016), 
    ('Justin Wilcox', 2017, 2025);
    -- 当我们连接两个或多个表时，默认输出是笛卡尔积。

sqlite> SELECT * FROM big_game JOIN coaches ON year >= start AND year <= end; -- 匹配

sqlite> SELECT b.Berkeley - a.Berkeley, b.Stanford - a.Stanford, a.Year, b.Year ...> FROM big_game AS a, big_game AS b WHERE a.Year < b.Year;
    -- 消除歧义

-- SQL Aggregation
CREATE TABLE flights ( departure TEXT, arrival TEXT, price INTEGER); INSERT INTO flights (departure, arrival, price) VALUES 
    ('SFO', 'LAX', 97), 
    ('SFO', 'AUH', 848), 
    ('LAX', 'SLC', 115), 
    ('SFO', 'PDX', 192), 
    ('AUH', 'SEA', 932), 
    ('SLC', 'PDX', 79), 
    ('SFO', 'LAS', 40), 
    ('SLC', 'LAX', 117), 
    ('SEA', 'PDX', 32), 
    ('SLC', 'SEA', 42), 
    ('SFO', 'SLC', 97), 
    ('LAS', 'SLC', 50), 
    ('LAX', 'PDX', 89);

sqlite> SELECT departure, MIN(price) FROM flights GROUP BY departure; 
departure MIN(price) 
--------- ---------- 
AUH 932 
LAS 50 
LAX 89 
SEA 32 
SFO 40 
SLC 42

CREATE table newest AS
  SELECT title, year
  FROM titles
  ORDER BY year DESC
  LIMIT 10;


CREATE table dog_movies AS 
  SELECT title, character
  FROM titles 
  JOIN principals ON titles.tconst = principals.tconst
  WHERE character LIKE "%dog%"
  ;


CREATE table leads AS 
  SELECT name, count(*)
  FROM names JOIN principals ON names.nconst = principals.nconst
  WHERE ordering = 1
  GROUP BY names.nconst
  HAVING count(*) > 10;


CREATE table long_movies AS 
  SELECT ((year / 10) * 10 ) || "s" AS decade, COUNT(*) AS count
  FROM titles
  WHERE runtime > 180
  GROUP BY year / 10;

CREATE TABLE parents (parent TEXT, child TEXT);

INSERT INTO parents VALUES
  ('ace', 'bella'),
  ('ace', 'charlie'),
  ('daisy', 'hank'),
  ('finn', 'ace'),
  ('finn', 'daisy'),
  ('finn', 'ginger'),
  ('ellie', 'finn');

CREATE TABLE dogs (name TEXT, fur TEXT, height INTEGER);

INSERT INTO dogs VALUES
  ('ace',     'long',  26),
  ('bella',   'short', 52),
  ('charlie', 'long',  47),
  ('daisy',   'long',  46),
  ('ellie',   'short', 35),
  ('finn',    'curly', 32),
  ('ginger',  'short', 28),
  ('hank',    'curly', 31);

CREATE TABLE sizes (size TEXT, min INTEGER, max INTEGER);

INSERT INTO sizes VALUES
  ('toy',      24, 28),
  ('mini',     28, 35),
  ('medium',   35, 45),
  ('standard', 45, 60);


-- All dogs with parents ordered by decreasing height of their parent
CREATE TABLE by_parent_height AS
  SELECT child FROM parents, dogs WHERE name = parent ORDER BY height DESC;


-- The size of each dog
CREATE TABLE size_of_dogs AS
  SELECT name, size FROM dogs, sizes WHERE height > min AND height <= max;


-- [Optional] Filling out this helper table is recommended
CREATE TABLE siblings AS
  SELECT a.child AS first, b.child AS second FROM parents AS a, parents AS b
    WHERE a.parent = b.parent AND a.child < b.child;

-- Sentences about siblings that are the same size
CREATE TABLE sentences AS
  SELECT "The two siblings, " || first || " and " || second || ", have the same size: " || a.size
    FROM siblings, size_of_dogs AS a, size_of_dogs AS b
    WHERE a.size = b.size AND a.name = first AND b.name = second;


-- Height range for each fur type where all of the heights differ by no more than 30% from the average height
CREATE TABLE low_variance AS
  SELECT fur, MAX(height) - MIN(height) AS height_range FROM dogs GROUP BY fur
      HAVING MIN(height) >= .7 * AVG(height) and MAX(height) <= 1.3 * AVG(height);