1. Introduction to SQL

  • Definition: SQL (Structured Query Language) is used to manage and manipulate relational databases.
  • Key Uses:
    • Create and modify database structures (DDL).
    • Insert, update, and delete data (DML).
    • Query and retrieve data (DQL).
    • Manage database permissions and transactions (DCL/TCL).
  • Database Systems: MySQL, PostgreSQL, Oracle, SQL Server, SQLite.

2. Types of SQL Commands

2.1. Data Definition Language (DDL)

  • Purpose: Define and modify database structure.
  • Commands:
    Command Description
    CREATE Creates a database, table, or view
    ALTER Modifies existing database objects
    DROP Deletes database objects
    TRUNCATE Deletes all rows in a table

Examples:

-- Create a table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- Alter a table
ALTER TABLE employees ADD COLUMN department VARCHAR(50);

-- Drop a table
DROP TABLE employees;

-- Truncate a table
TRUNCATE TABLE employees;

2.2. Data Manipulation Language (DML)

  • Purpose: Manipulate data within tables.
  • Commands:
    Command Description
    INSERT Adds new data
    UPDATE Modifies existing data
    DELETE Deletes specific data

Examples:

-- Insert data
INSERT INTO employees (id, name, position, salary, hire_date) 
VALUES (1, 'Alice', 'Manager', 75000, '2023-01-10');

-- Update data
UPDATE employees SET salary = 80000 WHERE id = 1;

-- Delete data
DELETE FROM employees WHERE id = 1;

2.3. Data Query Language (DQL)

  • Purpose: Retrieve data from the database.
  • Command:
    Command Description
    SELECT Queries data from tables

Examples:

-- Retrieve all columns
SELECT * FROM employees;

-- Retrieve specific columns
SELECT name, position FROM employees;

-- Use WHERE clause
SELECT * FROM employees WHERE salary > 50000;

2.4. Data Control Language (DCL)

  • Purpose: Manage permissions.
  • Commands:
    Command Description
    GRANT Grants privileges to users
    REVOKE Removes privileges from users

Examples:

-- Grant privileges
GRANT SELECT, INSERT ON employees TO user1;

-- Revoke privileges
REVOKE INSERT ON employees FROM user1;

2.5. Transaction Control Language (TCL)

  • Purpose: Manage database transactions.
  • Commands:
    Command Description
    COMMIT Saves changes permanently
    ROLLBACK Undoes changes
    SAVEPOINT Sets a rollback point

Examples:

BEGIN;
INSERT INTO employees (id, name) VALUES (2, 'Bob');
ROLLBACK;

SAVEPOINT sp1;
UPDATE employees SET salary = 60000 WHERE id = 2;
ROLLBACK TO sp1;

COMMIT;

3. SQL Constraints

Common Constraints:

Constraint Description
NOT NULL Ensures a column cannot have NULL values
UNIQUE Ensures all values in a column are unique
PRIMARY KEY Combines NOT NULL and UNIQUE
FOREIGN KEY Enforces a link between two tables
CHECK Ensures values meet a specific condition
DEFAULT Assigns a default value when no value is provided

Examples:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE,
    amount DECIMAL(10, 2) CHECK (amount > 0),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

4. Joins

Types of Joins:

Join Type Description
INNER JOIN Returns matching rows from both tables
LEFT JOIN Returns all rows from the left table
RIGHT JOIN Returns all rows from the right table
FULL OUTER JOIN Returns rows matching in either table
CROSS JOIN Returns Cartesian product of two tables

Examples:

-- Inner Join
SELECT e.name, d.department_name 
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- Left Join
SELECT e.name, d.department_name 
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

5. Subqueries

  • Definition: A query nested within another query.

Examples:

-- Subquery in WHERE clause
SELECT name FROM employees WHERE salary > 
    (SELECT AVG(salary) FROM employees);

-- Subquery in FROM clause
SELECT avg_salary FROM 
    (SELECT department_id, AVG(salary) AS avg_salary 
     FROM employees GROUP BY department_id) dept_avg;

6. Advanced Concepts

6.1. Indexes

  • Purpose: Improve query performance.
  • Types: Single-column, Composite, Unique.

Example:

CREATE INDEX idx_salary ON employees (salary);

6.2. Views

  • Purpose: Create a virtual table.

Example:

CREATE VIEW high_salary_employees AS 
SELECT name, salary FROM employees WHERE salary > 100000;

SELECT * FROM high_salary_employees;

6.3. Stored Procedures

  • Definition: Precompiled SQL code for reuse.

Example:

CREATE PROCEDURE increase_salary(IN increment DECIMAL)
BEGIN
    UPDATE employees SET salary = salary + increment;
END;

CALL increase_salary(5000);

6.4. Triggers

  • Definition: Automatic actions triggered by table events.

Example:

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, timestamp) 
    VALUES ('Employee added', NOW());
END;

7. SQL Best Practices

  1. Use proper indexing to optimize performance.
  2. Normalize data to reduce redundancy.
  3. Use prepared statements to prevent SQL injection.
  4. Follow consistent naming conventions.
  5. Regularly analyze and optimize queries.
  6. Use transactions for critical operations.
  7. Avoid using SELECT *; specify required columns.