Basic Queries
## Learning Objectives
- Master SELECT statements
- Use column aliases
- Perform arithmetic in queries
- Understand DISTINCT and ALL
## The SELECT Statement
### Select All Columns
```sql
SELECT *
FROM employees;
```
### Select Specific Columns
```sql
SELECT first_name, last_name, salary
FROM employees;
```
### Column Order
```sql
SELECT last_name, first_name, salary
FROM employees;
```
## Column Aliases
### AS Keyword
```sql
SELECT
first_name AS "First Name",
last_name AS "Last Name",
salary AS "Annual Salary"
FROM employees;
```
### Without AS
```sql
SELECT
first_name "First Name",
salary * 12 "Annual Total"
FROM employees;
```
## Arithmetic Operations
```sql
SELECT
first_name,
salary,
salary * 12 AS annual_salary,
salary / 12 AS monthly_salary,
salary + 1000 AS raise
FROM employees;
```
### Operators
| Operator | Description |
|----------|-------------|
| + | Addition |
| - | Subtraction |
| * | Multiplication |
| / | Division |
| % | Modulo (remainder) |
### Division Considerations
```sql
-- Integer division in some databases
SELECT 10 / 3; -- Returns 3 in SQL Server, 3.333 in MySQL
-- Use decimal for precision
SELECT CAST(10 AS FLOAT) / 3;
```
## String Concatenation
### SQL Server
```sql
SELECT first_name + ' ' + last_name AS full_name
FROM employees;
```
### MySQL
```sql
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
```
### PostgreSQL / SQLite
```sql
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
```
## NULL Values
### What is NULL?
NULL represents missing or unknown data.
```sql
-- Employees with no email
SELECT first_name, email
FROM employees
WHERE email IS NULL;
```
### NULL in Calculations
```sql
-- Any arithmetic with NULL returns NULL
SELECT salary + bonus FROM employees; -- If bonus is NULL, result is NULL
```
### Handling NULL
```sql
-- COALESCE returns first non-NULL value
SELECT
first_name,
COALESCE(bonus, 0) AS bonus
FROM employees;
```
## DISTINCT
### Remove Duplicates
```sql
SELECT DISTINCT department
FROM employees;
```
### Multiple Columns
```sql
SELECT DISTINCT department, city
FROM employees;
```
## ALL Keyword
```sql
-- Default behavior
SELECT ALL salary FROM employees;
-- Same as
SELECT salary FROM employees;
```
## Expressions
### Column Expressions
```sql
SELECT
first_name,
last_name,
salary * 1.10 AS new_salary
FROM employees;
```
### WHERE with Expressions
```sql
SELECT
first_name,
salary,
salary * 12 AS annual
FROM employees
WHERE salary * 12 > 50000;
```
## LIMIT / TOP
### MySQL / PostgreSQL / SQLite
```sql
SELECT *
FROM employees
LIMIT 10;
```
### TOP Clause (SQL Server)
```sql
SELECT TOP 10 *
FROM employees;
```
### With OFFSET (Pagination)
```sql
-- MySQL / PostgreSQL
SELECT *
FROM employees
LIMIT 10 OFFSET 20;
-- SQL Server
SELECT *
FROM employees
ORDER BY employee_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
```
## Summary
- `SELECT *` retrieves all columns
- Use aliases with `AS` for readable column names
- Arithmetic operators work in SELECT statements
- String concatenation varies by database
- NULL represents missing data; use `IS NULL` / `IS NOT NULL`
- `DISTINCT` removes duplicate rows
- `LIMIT` / `TOP` restricts number of rows returned
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →