Postgres Commands

• \? : Help for Postgres commands
• \h <command> : Help for SQL commands
• \d : List relations
• \dn : List namespaces (Don’t forget to “set search_path to <namespace>;”)

SQL Commands

Aggregation

• GROUP BY <attr> : Find an aggregate of all rows having the same value for <attr>
• HAVING <cond> : Same with WHERE but filter groups with aggregate functions (e.g. HAVING avg(grade) > 80)

Set Operations

• Bags/Multisets: Sets but allow duplicates, with no ordering
• Set Operations: Duplicates are removed when performing set operations in SQL.
  • UNION, INTERSECT, EXCEPT
• SELECT DISTINCT <attrs> : Remove duplicate tuples (entire row match)
• <q1> UNION <q2> ：Set operation on two subqueries
• <q1> UNION ALL <q2> : Set operation with duplicates

Subqueries

Subqueries: Query inside a query

• Must be in parentheses
• In FROM, as a table: Must be named (so you can refer to it in outer query) SELECT … FROM (SELECT … ) <name> WHERE …;
• In WHERE, as a value SELECT * FROM Student WHERE gpa > (SELECT gpa FROM …);
  • WHERE <comparison> ****(<sub>): Sub must return ≤1 tuple or error will occur. (No results if the subquery returns null.)
  • WHERE <comparison> ANY (<sub>) : True if ≥1 results in the subq match
  • WHERE <comparison> ALL (<sub>) : True if every result in the subq match
  • WHERE <attrs> IN (<sub>) : True if a tuple is in the subq table
  • WHERE EXISTS (<sub>) : True if sub returns ≥1 value.