Clauses

:GET User WHERE condition
:UPDATE User SET field = value WHERE condition
:DELETE User WHERE condition

:GET User WHERE id = 1
:GET User WHERE age > 21 AND status = "active"
:GET User WHERE role IN ("admin", "moderator")

:GET Entity ORDER BY column
:GET Entity ORDER BY column ASC
:GET Entity ORDER BY column DESC
:GET Entity ORDER BY column1 ASC, column2 DESC

:GET User ORDER BY name
:GET User ORDER BY created_at DESC
:GET User ORDER BY status ASC, name ASC

:GET Entity LIMIT n

:GET User LIMIT 10
:GET User WHERE active = true ORDER BY created_at DESC LIMIT 5

:GET Entity LIMIT n OFFSET m

:GET User LIMIT 10 OFFSET 0
:GET User LIMIT 10 OFFSET 10
:GET User LIMIT 10 OFFSET 20

-- Page 1
:GET User ORDER BY id LIMIT 20 OFFSET 0

-- Page 2
:GET User ORDER BY id LIMIT 20 OFFSET 20

-- Page 3
:GET User ORDER BY id LIMIT 20 OFFSET 40

:COUNT * FROM Entity GROUP BY column
:SUM field FROM Entity GROUP BY column
:GET Entity WITH column, SUM(field) AS alias GROUP BY column

:COUNT * FROM User GROUP BY status
:SUM total FROM Order GROUP BY user_id
:GET Product WITH category, AVG(price) AS avg_price GROUP BY category

:COUNT * FROM Entity GROUP BY column HAVING COUNT(*) > n
:SUM field FROM Entity GROUP BY column HAVING SUM(field) > n

:COUNT * FROM User GROUP BY status HAVING COUNT(*) > 10
:SUM total FROM Order GROUP BY user_id HAVING SUM(total) > 1000

-- WHERE: filters rows BEFORE grouping
:COUNT * FROM Order WHERE created_at > "2024-01-01" GROUP BY status

-- HAVING: filters groups AFTER aggregation
:COUNT * FROM Order GROUP BY status HAVING COUNT(*) > 5

-- Combined
:COUNT * FROM Order 
  WHERE created_at > "2024-01-01" 
  GROUP BY status 
  HAVING COUNT(*) > 5

:GET Entity DISTINCT
:GET col1, col2 FROM Entity DISTINCT

:GET User DISTINCT
:GET status FROM User DISTINCT
:GET user_id, status FROM Order DISTINCT

:GET Entity WITH column AS alias
:GET Entity WITH SUM(field) AS alias

:GET User WITH id, name AS full_name
:GET Order WITH status, SUM(total) AS revenue GROUP BY status

:UPDATE Entity SET field = value WHERE condition
:UPDATE Entity SET field1 = value1, field2 = value2 WHERE condition

:UPDATE User SET name = "John" WHERE id = 1
:UPDATE User SET status = "active", updated_at = CURRENT_TIMESTAMP WHERE id = 1
:UPDATE Product SET price = price * 1.1 WHERE category = "electronics"

:GET Entity WITH column1, column2, column3
:GET Entity WITH column, SUM(field) AS alias GROUP BY column

:CREATE Entity WITH field = value, field = value

:GET User WITH id, name, email
:GET Order WITH status, SUM(total) AS revenue GROUP BY status
:CREATE User WITH name = "John", email = "[email protected]", age = 30
:CREATE Product WITH name = "Widget", price = 9.99, quantity = 100

:INNER JOIN Entity1 Entity2 ON field1 = field2

:UPSERT Entity WITH fields ON conflict_column

:INNER JOIN Order User ON user_id = id
:UPSERT User WITH email = "[email protected]", name = "John" ON email

:ROW NUMBER OVER (ORDER BY column) FROM Entity
:RANK OVER (PARTITION BY column ORDER BY column) FROM Entity

:ROW NUMBER OVER (ORDER BY created_at) FROM User
:RANK OVER (PARTITION BY department ORDER BY salary DESC) FROM User
:DENSE RANK OVER (PARTITION BY category ORDER BY price) FROM Product

:FUNCTION OVER (PARTITION BY column ORDER BY column) FROM Entity

:ROW NUMBER OVER (PARTITION BY department ORDER BY name) FROM User
:LAG salary OVER (PARTITION BY department ORDER BY hire_date) FROM Employee

:GRANT permission ON Entity TO user
:REVOKE permission ON Entity FROM user
:ASSIGN ROLE role TO user
:REVOKE ROLE role FROM user

:GRANT READ ON User TO analyst
:REVOKE DELETE ON Order FROM intern
:ASSIGN ROLE admin TO john

:GET Entity
  WITH columns
  WHERE conditions
  GROUP BY column
  HAVING condition
  ORDER BY column
  LIMIT n
  OFFSET m

:SUM total FROM Order
  WHERE created_at > "2024-01-01"
  GROUP BY user_id
  HAVING SUM(total) >= 500
  ORDER BY sum DESC
  LIMIT 100
  OFFSET 0