★ SQL playground ★

This playground is an accompaniment to Become a SELECT Star!. It contains all the SQL example tables in the zine so you can practice SELECTs a little bit.

Tables: , , , , , , , , , , , , ,

Examples

SELECT * from cats
WHERE name = 'daisy'
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1
SELECT item
      , COUNT(*)
      , MAX(price)
FROM sales
GROUP BY item
SELECT month, SUM(price)
FROM sales
GROUP BY month
HAVING SUM(price) > 6
SELECT *
FROM owners INNER JOIN cats
ON cats.owner = owners.id
SELECT * from cats
ORDER BY LENGTH(name) ASC
SELECT name, class, grade,
    ROW_NUMBER() 
      OVER (
        PARTITION BY class
        ORDER BY grade DESC
      )
      AS rank_in_class
FROM grades
SELECT name,
       price - COALESCE(discount,0)
          AS net_price
FROM products
SELECT event, hour, 
      hour - LAG(hour) OVER(
        PARTITION BY event
        ORDER BY hour ASC)
       AS time_since_last
FROM baby_log
WHERE event IN ('feeding', 'diaper')
ORDER BY hour ASC
SELECT customer
       , COALESCE(
          mailing_state,
          billing_state,
          ip_address_state
       ) AS state
FROM addresses
SELECT first_name, age, CASE
  WHEN age < 13 THEN 'child'
  WHEN age < 20 THEN 'teenager'
  ELSE 'adult' END as age_range
FROM people
SELECT genus,
       COUNT(DISTINCT species)
FROM plants
GROUP BY 1
ORDER BY 2 DESC
SELECT owner
, SUM(CASE 
    WHEN type = 'dog' THEN 1 
    ELSE 0 END) AS num_dogs
, SUM(CASE 
    WHEN type = 'cat' THEN 1
    ELSE 0 END) AS num_cats
, SUM(CASE
    WHEN type NOT IN ('dog', 'cat') THEN 1
    ELSE 0 END) AS num_other
FROM pets
GROUP BY owner
SELECT owner
FROM dogs
WHERE name in (
  SELECT name
  FROM dogs
  GROUP BY name
  HAVING count(*) > 2)