พื้นฐาน SQL Query

INSERT INTO Product (name, price) VALUES ('Example Product', 100);

SELECT * FROM Product;
SELECT * FROM Product LIMIT 1; -- เอาแค่ 1 อัน
SELECT * FROM Product ORDER BY id desc; -- เรียง id ย้อนกลับ

UPDATE Product SET name = 'Updated Product', price = 150 WHERE id = 1;

DELETE FROM Product WHERE id = 1;

SELECT p.name, p.price, s.name AS supplier_name
FROM Product p
JOIN Supplier s ON p.supplier_id = s.supplier_id;

SELECT p.name, p.category, p.price, c.cheapest_price
FROM Product p
JOIN LATERAL (
  SELECT MIN(price) as cheapest_price
  FROM Product
  WHERE category = p.category
) c ON true;

- id: primary key, integer
- name: string
- price: integer
- category: string (e.g., 'Electronics', 'Clothing')
- quantity: integer (number of items in stock)
- created_at: timestamp (date and time when the product was added)

SELECT category, COUNT(*) as total_products,
  COUNT(*) FILTER (WHERE quantity > 50) as high_stock_products
FROM Product
GROUP BY category;

SELECT category, AVG(price) AS average_price
FROM Product
GROUP BY category;

SELECT name, category, price, RANK() OVER (PARTITION BY category ORDER BY price DESC) as price_rank
FROM Product;

SELECT p.name, p.price, p.category
FROM Product p
WHERE p.price > (
  SELECT AVG(price)
  FROM Product
  WHERE category = p.category
);

SELECT p.name, p.price, p.category
FROM Product p
WHERE p.price > (
  SELECT AVG(price)
  FROM Product
  WHERE category = p.category
);

SELECT name, price, category, 
  (SELECT COUNT(*) FROM Product WHERE category = p.category) as total_in_category
FROM Product p;