Skip to main content

พื้นฐาน SQL Query

Basic Query (CRUD)​

CREATE​

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

READ​

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

UPDATE​

UPDATE Product SET name = 'Updated Product', price = 150 WHERE id = 1;
  • พูดเรื่องการใช้ Where

DELETE​

DELETE FROM Product WHERE id = 1;

Join Table​

เพิ่ม Table

  • Supplier with columns supplier_id, name

Basic JOIN​

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

LATERAL JOIN​

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;

Advanced SQL Queries (แบบ Table ตัวเอง)​

ปรับโครงสร้างเพิ่มเป็น

- 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)

Conditional Aggregation​

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

Aggregation with Group By​

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

Window function​

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

Common Table Expression (CTE)​

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

Subquery in From Clause​

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

Subquery in Select Clause​

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