Intro to SQL Queries in PostgreSQL

What is SQL and Why Does It Matter?

SQL stands for Structured Query Language—the standard language for communicating with relational databases. In PostgreSQL, SQL allows us to create, read, update, and delete data. In this lesson, we'll focus on the most common and foundational command: SELECT.

Think of SELECT as a way to ask questions to your database and retrieve answers in a tabular format.

Setting Up Sample Data

Let's create a sample table named employees to practice with:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT,
  salary NUMERIC(10,2),
  hire_date DATE
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
('Alice', 'Engineering', 75000.00, '2020-03-15'),
('Bob', 'HR', 50000.00, '2019-08-01'),
('Charlie', 'Engineering', 72000.00, '2021-01-10'),
('Diana', 'Marketing', 65000.00, '2022-05-21');

Now that we have data, let's learn how to query it!

Basic SELECT Statement Syntax

Here's the most basic structure of a SELECT query:

SELECT column1, column2 FROM table_name;

Or to select all columns:

SELECT * FROM table_name;

Selecting All Data

SELECT * FROM employees;

This query returns all rows and all columns from the employees table.

Selecting Specific Columns

SELECT name, department FROM employees;

This helps reduce data noise and improves performance when only a few fields are needed.

Filtering Data with WHERE Clause

To filter data, we use the WHERE clause:

-- Get employees from the Engineering department
SELECT * FROM employees WHERE department = 'Engineering';

-- Get employees with salary more than 70000
SELECT name, salary FROM employees WHERE salary > 70000;

Enhancing SELECT Queries

Column and Table Aliases

Rename output columns for better readability:

SELECT 
  name AS employee_name, 
  salary AS monthly_salary 
FROM employees;

-- Table aliases
SELECT e.name, e.department 
FROM employees AS e;

Ordering Results

Sort query results by one or more columns:

-- Sort by salary (descending)
SELECT name, salary FROM employees ORDER BY salary DESC;

-- Sort by multiple columns
SELECT name, department, salary 
FROM employees 
ORDER BY department ASC, salary DESC;

Limiting Results

Control the number of rows returned:

-- Get first 2 employees
SELECT * FROM employees LIMIT 2;

-- Skip first 2 and get next 2 (pagination)
SELECT * FROM employees OFFSET 2 LIMIT 2;

Removing Duplicates

Use DISTINCT to remove duplicate values:

-- Get unique departments
SELECT DISTINCT department FROM employees;

-- Distinct combinations
SELECT DISTINCT department, salary FROM employees;

Important SQL Considerations

Case Sensitivity

Statement Termination

Always end SQL statements with a semicolon (;):

SELECT * FROM employees; -- Correct
SELECT * FROM employees  -- Will work but not recommended

Complete Query Examples

Basic Reporting Query

SELECT 
  name AS "Employee Name",
  department AS "Department",
  salary AS "Annual Salary",
  hire_date AS "Start Date"
FROM employees
WHERE salary >= 60000
ORDER BY salary DESC
LIMIT 5;

Department Summary

SELECT DISTINCT 
  department,
  COUNT(*) OVER (PARTITION BY department) AS employee_count
FROM employees
ORDER BY department;

Common Query Patterns

Practice Exercises

Try these exercises with the sample data:

1. Basic Selection: Write a query to show only the names and hire dates of all employees.
2. Filtering: Write a query to show the names of employees with salary over 60,000.
3. Date Filtering: Retrieve all data of employees hired after January 1, 2021.
4. Distinct Values: Get a list of all unique departments.
5. Top Records: Show the top 2 highest-paid employees.
6. Sorting: Display all employees sorted by hire date descending.
7. Pagination: Skip the first 2 employees and show the next 2.

Exercise Solutions

-- 1. Names and hire dates
SELECT name, hire_date FROM employees;

-- 2. High earners
SELECT name FROM employees WHERE salary > 60000;

-- 3. Recent hires
SELECT * FROM employees WHERE hire_date > '2021-01-01';

-- 4. Unique departments
SELECT DISTINCT department FROM employees;

-- 5. Top 2 salaries
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 2;

-- 6. Sort by hire date
SELECT * FROM employees ORDER BY hire_date DESC;

-- 7. Pagination
SELECT * FROM employees OFFSET 2 LIMIT 2;

Best Practices

1. Be specific: Select only the columns you need instead of using SELECT *
2. Use aliases: Make your output more readable with meaningful column names
3. Add comments: Document complex queries for future reference
4. Format consistently: Use consistent indentation and capitalization
5. Test incrementally: Build complex queries step by step

Common Mistakes to Avoid

• Forgetting semicolons: Always terminate statements properly
• Case sensitivity errors: Remember that string values are case-sensitive
• Missing quotes: String values must be enclosed in single quotes
• Typos in column names: Double-check spelling of table and column names