Best Practices
## Learning Objectives
- Write maintainable SQL
- Optimize query performance
- Ensure data security
- Follow naming conventions
## Code Style
### Formatting
```sql
-- Good: readable and indented
SELECT
e.first_name,
e.last_name,
d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.id
WHERE e.salary > 50000
ORDER BY e.last_name;
-- Bad: all on one line
SELECT e.first_name,e.last_name,d.department_name FROM employees e INNER JOIN departments d ON e.department_id=d.id WHERE e.salary>50000 ORDER BY e.last_name;
```
### Capitalization
```sql
-- Keywords uppercase, names lowercase
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';
-- Also acceptable: all lowercase
select first_name, last_name
from employees
where department = 'Sales';
```
## Naming Conventions
### Tables
```sql
-- Singular, lowercase with underscores
CREATE TABLE employee; -- Bad
CREATE TABLE employees; -- Acceptable
CREATE TABLE employee_record; -- Good
CREATE TABLE person; -- Good (singular)
```
### Columns
```sql
-- Descriptive, lowercase with underscores
first_name -- Good
last_name -- Good
date_of_birth -- Good
emp_id -- Acceptable
```
### Constraints
```sql
-- Consistent naming
pk_employees -- Primary key
fk_employees_department -- Foreign key
uq_employees_email -- Unique constraint
chk_employees_salary -- Check constraint
```
### Aliases
```sql
-- Meaningful or standard
SELECT
e.first_name,
d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
-- Use 'as' for clarity
SELECT first_name AS "First Name"
```
## Query Optimization
### Use Appropriate Indexes
```sql
-- Index on frequently filtered columns
CREATE INDEX idx_employees_department ON employees(department);
CREATE INDEX idx_employees_salary ON employees(salary);
```
### Avoid SELECT *
```sql
-- Bad: retrieves unnecessary columns
SELECT * FROM employees;
-- Good: only needed columns
SELECT first_name, last_name, email FROM employees;
```
### Use EXISTS over COUNT
```sql
-- Faster: stops at first match
WHERE EXISTS (SELECT 1 FROM orders WHERE employee_id = e.id)
-- Slower: counts all
WHERE (SELECT COUNT(*) FROM orders WHERE employee_id = e.id) > 0
```
### Avoid Functions on Indexed Columns
```sql
-- Bad: can't use index
WHERE YEAR(hire_date) = 2020
-- Good: can use index
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01'
```
### Use JOINs Efficiently
```sql
-- Prefer JOIN over subquery (usually)
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
-- Sometimes subquery is clearer
SELECT *
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'NYC');
```
## Security
### Prevent SQL Injection
```sql
-- Bad: vulnerable to injection
"SELECT * FROM users WHERE name = '" + user_input + "'"
-- Good: parameterized queries
"SELECT * FROM users WHERE name = ?"
-- Parameters: [user_input]
```
### Limit Privileges
```sql
-- Application user with limited access
GRANT SELECT, INSERT, UPDATE ON employees TO app_user;
REVOKE DELETE ON employees FROM app_user;
```
### Never Store Plain Passwords
```sql
-- Bad
INSERT INTO users (password) VALUES ('secret123');
-- Good: store hash only
INSERT INTO users (password_hash)
VALUES ('sha256$abc123...');
```
## Maintenance
### Use Transactions
```sql
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT; -- Or ROLLBACK if error
```
### Document Complex Queries
```sql
-- Purpose: Find top earner in each department
SELECT
department,
first_name,
salary
FROM employees e1
WHERE salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e2.department = e1.department
);
```
### Test Destructive Queries
```sql
-- 1. First, SELECT to preview
SELECT * FROM employees WHERE department = 'Temp';
-- 2. Then DELETE
DELETE FROM employees WHERE department = 'Temp';
```
## Schema Design
### Normalization
1. **1NF**: Atomic values, no repeating groups
2. **2NF**: No partial dependencies (composite keys)
3. **3NF**: No transitive dependencies
### Denormalization
Sometimes intentional for performance:
```sql
-- Add computed column
ALTER TABLE orders
ADD COLUMN total_amount DECIMAL(10,2)
GENERATED ALWAYS AS (quantity * unit_price);
```
## Error Handling
### Check Affected Rows
```sql
UPDATE employees SET salary = 75000 WHERE employee_id = 999;
-- If 0 rows affected, employee doesn't exist
```
### Use Appropriate Data Types
```sql
-- For fixed precision (money)
DECIMAL(10,2) -- Good for currency
-- For approximate values
FLOAT -- Good for scientific data
```
## Documentation
### Comment Your Code
```sql
-- Calculate bonus based on tenure
SELECT
first_name,
hire_date,
CASE
WHEN hire_date < '2015-01-01' THEN salary * 0.15
WHEN hire_date < '2020-01-01' THEN salary * 0.10
ELSE salary * 0.05
END AS bonus
FROM employees;
```
## Summary
- Use consistent formatting and naming
- Write readable, well-indented SQL
- Optimize with appropriate indexes
- Use parameterized queries for security
- Test destructive queries first with SELECT
- Document complex queries
- Use transactions for related changes
- Choose appropriate data types
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →