WITH widget_prices AS (
  SELECT price
  FROM products
  WHERE category = 'Widget'
)
SELECT
  title,
  price,
  category
FROM
  products
WHERE
  price > ALL (SELECT price FROM widget_prices);

SELECT
  *
FROM
  products
WHERE
  category = 'Gizmo'
  AND price > 50;

SELECT
  title,
  price
FROM
  products
WHERE
  price > ANY (
    SELECT price
    FROM products
    WHERE category = 'Widget'
  );

SELECT ARRAY[price, 100, 200] AS price_array
FROM products
LIMIT 1;

SELECT
  AVG(price)
FROM
  products;

SELECT
  title AS "Product Name",
  category AS "Product Category"
FROM
  products;

SELECT
  *
FROM
  products
WHERE
  price BETWEEN 10 AND 20;

SELECT
  title,
  CASE
    WHEN price > 100 THEN 'Expensive'
    ELSE 'Affordable'
  END AS price_category
FROM
  products;

CAST(24.99 AS VARCHAR) AS price_text,
CAST('2024-01-01' AS DATE) AS order_date

SELECT
  COUNT(*)
FROM
  orders;

SELECT
  CURRENT_DATE AS today,
  CURRENT_DATE - INTERVAL '1' DAY AS yesterday,
  CURRENT_DATE + INTERVAL '1' DAY AS tomorrow;

-- Create a date from a string
SELECT DATE '2024-05-04' AS specific_date;

SELECT
  CURRENT_TIME AS right_now,
  CURRENT_TIME + INTERVAL '30' MINUTE AS thirty_mins_later;

SELECT
  DISTINCT category
FROM
  products;

SELECT
  id
FROM
  people
EXCEPT
SELECT
  user_id
FROM
  orders;

SELECT
  name
FROM
  people
WHERE
  EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.user_id = people.id
  );

SELECT
  created_at as "timestamp",
  EXTRACT(YEAR FROM created_at) AS "year",
  EXTRACT(MONTH FROM created_at) AS "month",
  EXTRACT(DAY FROM created_at) AS "day",
  EXTRACT(HOUR FROM created_at) AS "hour",
  EXTRACT(DOW FROM created_at) AS "day of week"
FROM
  orders;

| timestamp                  | year  | month | day | hour | day of week |
| -------------------------- | ----- | ----- | --- | ---- | ----------- |
| February 11, 2025, 9:40 PM | 2,025 | 2     | 11  | 21   | 3           |
| May 15, 2024, 8:04 AM      | 2,024 | 5     | 15  | 8    | 4           |
| December 6, 2025, 10:22 PM | 2,025 | 12    | 6   | 22   | 7           |
| August 22, 2025, 4:30 PM   | 2,025 | 8     | 22  | 16   | 6           |
| October 10, 2024, 3:34 AM  | 2,024 | 10    | 10  | 3    | 5           |
| November 6, 2025, 4:38 PM  | 2,025 | 11    | 6   | 16   | 5           |

SELECT
  *
FROM
  products;

SELECT
  category,
  COUNT(*)
FROM
  products
GROUP BY
  category;

SELECT
  category,
  COUNT(*)
FROM
  products
GROUP BY
  category
HAVING
  COUNT(*) > 2;

SELECT
  *
FROM
  products
WHERE
  category IN ('Gizmo', 'Widget');

SELECT
  *
FROM
  orders
WHERE
  user_id IN (
    SELECT id
    FROM people
    WHERE state = 'VT'
  );

SELECT
  created_at,
  -- Add 7 days to a date
  created_at + INTERVAL '7' DAY AS next_week
FROM
  orders;

SELECT
  created_at,
  -- Subtract 2 months from a date
  created_at - INTERVAL '2' MONTH AS two_months_ago
FROM
  orders;

SELECT
  orders.id,
  products.title
FROM
  orders
INNER JOIN products ON orders.product_id = products.id;

SELECT
  id
FROM
  people
INTERSECT
SELECT
  user_id
FROM
  orders;

SELECT
  *
FROM
  products
WHERE
  vendor IS NULL;

SELECT
  orders.id,
  products.title
FROM
  orders
JOIN products ON orders.product_id = products.id;

SELECT
  people.name,
  orders.id
FROM
  people
LEFT JOIN orders ON people.id = orders.user_id;

SELECT
  *
FROM
  products
WHERE
  title LIKE '%Wool%';

SELECT
  *
FROM
  products
LIMIT
  3;

SELECT
  MAX(price)
FROM
  products;

SELECT
  MIN(price)
FROM
  products;

SELECT
  *
FROM
  products
WHERE
  MOD(id, 10) = 3;

SELECT
  *
FROM
  products
WHERE
  NOT category = 'Gizmo';

SELECT
  *
FROM
  products
WHERE
  vendor IS NULL;

SELECT
  orders.id,
  products.title
FROM
  orders
JOIN products ON orders.product_id = products.id;

SELECT
  *
FROM
  products
WHERE
  category = 'Gizmo'
  OR price > 100;

SELECT
  title,
  price
FROM
  products
ORDER BY
  price DESC;

SELECT
  orders.id,
  people.name
FROM
  orders
RIGHT JOIN people ON orders.user_id = people.id;

-- Get all columns
SELECT
  *
FROM
  products;

-- Get specific columns
SELECT
  title,
  category
FROM
  products;

SELECT
  title,
  price
FROM
  products
WHERE
  price > SOME (
    SELECT price
    FROM products
    WHERE category = 'Widget'
  );

SELECT
  SUM(price)
FROM
  products;

SELECT
  title
FROM
  products
UNION
SELECT
  name
FROM
  people;

SELECT
  title
FROM
  products
UNION ALL
SELECT
  name
FROM
  people;

SELECT
  *
FROM
  products
WHERE
  MOD(id, 10) = 3;

WITH expensive_products AS (
  SELECT *
  FROM products
  WHERE price > 100
)
SELECT title FROM expensive_products;