Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

02

Chapter 2

Basic SELECT

The foundation. Selecting columns, ordering, limiting, and counting — the vocabulary every query is built from.

In this chapter

136

queries, easy → advanced

  1. 001Select all columns from employees tableRead
  2. 002Select specific columns: first_name, last_name, salaryRead
  3. 003Select distinct job titles from employeesRead
  4. 004Select top 10 highest paid employeesRead
  5. 005Count total number of employeesRead
  6. 006Find maximum, minimum, and average salaryRead
  7. 007Select employees with column aliasesRead
  8. 008Select all departments and their locationsRead
  9. 009Count employees in each department (just count)Read
  10. 010Select first 5 products by price descendingRead
  11. 011Select all active employeesRead
  12. 012Show total sum of all product prices in stockRead
  13. 013Select employees hired in the year 2023Read
  14. 014Show products with stock between 10 and 100Read
  15. 015Select customers from India or USARead
  16. 016Select all orders with NULL shipped_at (not shipped)Read
  17. 017Show total revenue from all ordersRead
  18. 018List all unique countries from customersRead
  19. 019Get 2nd page of employees (OFFSET pagination)Read
  20. 020Show employee salaries with a 10% bonus calculationRead
  21. 021Select employees using CASE to label salary bracketsRead
  22. 022Show products with profit margin percentageRead
  23. 023Select all employees and their years of experienceRead
  24. 024Show department-wise headcount and average salaryRead
  25. 025Select employees using NULLIF to avoid division by zeroRead
  26. 026Show all employees sorted by hire date newest firstRead
  27. 027Show all products with their category id sorted by priceRead
  28. 028Count distinct departments in employees tableRead
  29. 029Show employees hired this yearRead
  30. 030Show total, average salary across entire companyRead
  31. 031List all projects with their status and budgetRead
  32. 032Find employees with salary NULLRead
  33. 033Show customers grouped by country with countRead
  34. 034Show top 5 most expensive products per categoryRead
  35. 035Conditional count: active vs inactive employeesRead
  36. 036FETCH FIRST: SQL standard alternative to LIMITRead
  37. 037Create a basic table with primary keyRead
  38. 038Create table with IF NOT EXISTSRead
  39. 039Add a new column to an existing tableRead
  40. 040Rename a columnRead
  41. 041Change column data typeRead
  42. 042Drop a columnRead
  43. 043Rename an entire tableRead
  44. 044Create table using AS (CTAS)Read
  45. 045Create table LIKE another tableRead
  46. 046Drop table safelyRead
  47. 047Empty a table quicklyRead
  48. 048Add a NOT NULL constraintRead
  49. 049Remove a NOT NULL constraintRead
  50. 050Add a DEFAULT value to a columnRead
  51. 051Add a UNIQUE constraintRead
  52. 052Add a FOREIGN KEY constraintRead
  53. 053Add a CHECK constraintRead
  54. 054Multi-column UNIQUE constraintRead
  55. 055Add constraint NOT VALID (for large tables)Read
  56. 056Drop a constraintRead
  57. 057Cast data types using :: syntaxRead
  58. 058Cast data types using CAST() standardRead
  59. 059Array data typesRead
  60. 060Querying Array columnsRead
  61. 061Using UUIDsRead
  62. 062JSONB Data TypeRead
  63. 063Querying JSONB propertiesRead
  64. 064ENUM Data TypeRead
  65. 065Create a custom SequenceRead
  66. 066Use a SequenceRead
  67. 067DDL Practice: Create table variant #1Read
  68. 068Constraint Practice: Add CHECK constraint variant #1Read
  69. 069DDL Practice: Create table variant #2Read
  70. 070Constraint Practice: Add CHECK constraint variant #2Read
  71. 071DDL Practice: Create table variant #3Read
  72. 072Constraint Practice: Add CHECK constraint variant #3Read
  73. 073DDL Practice: Create table variant #4Read
  74. 074Constraint Practice: Add CHECK constraint variant #4Read
  75. 075DDL Practice: Create table variant #5Read
  76. 076Constraint Practice: Add CHECK constraint variant #5Read
  77. 077DDL Practice: Create table variant #6Read
  78. 078Constraint Practice: Add CHECK constraint variant #6Read
  79. 079DDL Practice: Create table variant #7Read
  80. 080Constraint Practice: Add CHECK constraint variant #7Read
  81. 081DDL Practice: Create table variant #8Read
  82. 082Constraint Practice: Add CHECK constraint variant #8Read
  83. 083DDL Practice: Create table variant #9Read
  84. 084Constraint Practice: Add CHECK constraint variant #9Read
  85. 085DDL Practice: Create table variant #10Read
  86. 086Constraint Practice: Add CHECK constraint variant #10Read
  87. 087DDL Practice: Create table variant #11Read
  88. 088Constraint Practice: Add CHECK constraint variant #11Read
  89. 089DDL Practice: Create table variant #12Read
  90. 090Constraint Practice: Add CHECK constraint variant #12Read
  91. 091DDL Practice: Create table variant #13Read
  92. 092Constraint Practice: Add CHECK constraint variant #13Read
  93. 093DDL Practice: Create table variant #14Read
  94. 094Constraint Practice: Add CHECK constraint variant #14Read
  95. 095DDL Practice: Create table variant #15Read
  96. 096Constraint Practice: Add CHECK constraint variant #15Read
  97. 097DDL Practice: Create table variant #16Read
  98. 098Constraint Practice: Add CHECK constraint variant #16Read
  99. 099DDL Practice: Create table variant #17Read
  100. 100Constraint Practice: Add CHECK constraint variant #17Read
  101. 101DDL Practice: Create table variant #18Read
  102. 102Constraint Practice: Add CHECK constraint variant #18Read
  103. 103DDL Practice: Create table variant #19Read
  104. 104Constraint Practice: Add CHECK constraint variant #19Read
  105. 105DDL Practice: Create table variant #20Read
  106. 106Constraint Practice: Add CHECK constraint variant #20Read
  107. 107DDL Practice: Create table variant #21Read
  108. 108Constraint Practice: Add CHECK constraint variant #21Read
  109. 109DDL Practice: Create table variant #22Read
  110. 110Constraint Practice: Add CHECK constraint variant #22Read
  111. 111DDL Practice: Create table variant #23Read
  112. 112Constraint Practice: Add CHECK constraint variant #23Read
  113. 113DDL Practice: Create table variant #24Read
  114. 114Constraint Practice: Add CHECK constraint variant #24Read
  115. 115DDL Practice: Create table variant #25Read
  116. 116Constraint Practice: Add CHECK constraint variant #25Read
  117. 117DDL Practice: Create table variant #26Read
  118. 118Constraint Practice: Add CHECK constraint variant #26Read
  119. 119DDL Practice: Create table variant #27Read
  120. 120Constraint Practice: Add CHECK constraint variant #27Read
  121. 121DDL Practice: Create table variant #28Read
  122. 122Constraint Practice: Add CHECK constraint variant #28Read
  123. 123DDL Practice: Create table variant #29Read
  124. 124Constraint Practice: Add CHECK constraint variant #29Read
  125. 125DDL Practice: Create table variant #30Read
  126. 126Constraint Practice: Add CHECK constraint variant #30Read
  127. 127DDL Practice: Create table variant #31Read
  128. 128Constraint Practice: Add CHECK constraint variant #31Read
  129. 129DDL Practice: Create table variant #32Read
  130. 130Constraint Practice: Add CHECK constraint variant #32Read
  131. 131DDL Practice: Create table variant #33Read
  132. 132Constraint Practice: Add CHECK constraint variant #33Read
  133. 133DDL Practice: Create table variant #34Read
  134. 134Constraint Practice: Add CHECK constraint variant #34Read
  135. 135DDL Practice: Create table variant #35Read
  136. 136Constraint Practice: Add CHECK constraint variant #35Read