★ SQL playground ★

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)