SQL: An Introduction

What is SQL?

SQL (Structured Query Language) is a standardized programming language used for managing and manipulating relational databases. It allows you to create, read, update, and delete data stored in tables, which are organized into rows and columns.

---

Table of Contents

1. Basics of SQL

2. Data Definition Language (DDL)

3. Data Manipulation Language (DML)

4. Data Control Language (DCL)

5. Transaction Control Language (TCL)

6. ACID Properties

7. SQL Examples

8. Quick Revision Notes

---

Basics of SQL

Relational Databases

• Tables: Data is stored in tables consisting of rows and columns.

• Rows (Records): Each row represents a unique record.

• Columns (Fields): Each column represents a specific attribute of the data.

• Primary Key: A unique identifier for each record in a table.

• Foreign Key: A field in one table that uniquely identifies a row of another table.

SQL Syntax Overview

• Keywords: Reserved words in SQL, usually written in uppercase (e.g., SELECT, FROM).

• Clauses: Components of statements and queries (e.g., WHERE, ORDER BY).

• Statements: Complete instructions (e.g., SELECT * FROM table_name;).

---

Data Definition Language (DDL)

DDL statements are used to define and modify the database structure.

Common DDL Commands

1. CREATE: Create a new database object (e.g., table, index).

    CREATE TABLE employees (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        position VARCHAR(50),
        salary DECIMAL(10, 2)
    );
   

2. ALTER: Modify an existing database object.

    ALTER TABLE employees ADD COLUMN hire_date DATE;
   

3. DROP: Delete an existing database object.

    DROP TABLE employees;
   

4. TRUNCATE: Remove all records from a table, resetting identity counters.

    TRUNCATE TABLE employees;
   

5. RENAME: Rename a database object.

    ALTER TABLE employees RENAME TO staff;
   

---

Data Manipulation Language (DML)

DML statements are used to manipulate data within database objects.

Common DML Commands

1. SELECT: Retrieve data from one or more tables.

    SELECT name, position FROM employees WHERE salary > 50000;
   

2. INSERT: Add new records to a table.

    INSERT INTO employees (id, name, position, salary)
    VALUES (1, 'Alice Smith', 'Developer', 60000);
   

3. UPDATE: Modify existing records in a table.

    UPDATE employees SET salary = salary * 1.05 WHERE position = 'Developer';
   

4. DELETE: Remove records from a table.

    DELETE FROM employees WHERE id = 1;
   

---

Data Control Language (DCL)

DCL statements are used to control access to data within the database.

Common DCL Commands

1. GRANT: Give users access privileges to the database.

    GRANT SELECT, INSERT ON employees TO user_name;
   

2. REVOKE: Remove user access privileges.

    REVOKE INSERT ON employees FROM user_name;
   

---

Transaction Control Language (TCL)

TCL statements manage changes made by DML statements and ensure data integrity.

Common TCL Commands

1. COMMIT: Save all changes made in the current transaction.

    COMMIT;
   

2. ROLLBACK: Revert changes made in the current transaction.

    ROLLBACK;
   

3. SAVEPOINT: Set a point within a transaction to which you can roll back.

    SAVEPOINT savepoint_name;
   

4. RELEASE SAVEPOINT: Remove a savepoint, making it no longer available for rollback.

    RELEASE SAVEPOINT savepoint_name;
   

---

ACID Properties

ACID is an acronym representing the key properties of a reliable transaction system in databases.

1. Atomicity

• Definition: Ensures that all operations within a transaction are completed successfully; otherwise, the transaction is aborted.

• Example: In a bank transfer, if debiting from one account fails, the entire transaction is rolled back, and crediting to another account does not occur.

2. Consistency

• Definition: Guarantees that a transaction brings the database from one valid state to another, maintaining database rules (e.g., integrity constraints).

• Example: Ensuring that total debits equal total credits after a transaction.

3. Isolation

• Definition: Transactions are isolated from each other; concurrent transactions do not interfere.

• Example: Two transactions reading and writing to the same data will not affect each other's operations.

4. Durability

• Definition: Once a transaction is committed, the changes are permanent, even in the case of system failure.

• Example: After a successful transfer, the updated account balances remain recorded despite a crash.

---

SQL Examples

Creating a Database and Table

-- Create a new database
CREATE DATABASE company_db;

-- Use the new database
USE company_db;

-- Create a new table
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

Inserting Data

INSERT INTO departments (dept_id, dept_name)
VALUES (1, 'Human Resources'), (2, 'Engineering'), (3, 'Marketing');

Querying Data

SELECT * FROM departments;

-- Output:
-- dept_id | dept_name
-- --------------------
--    1    | Human Resources
--    2    | Engineering
--    3    | Marketing

Updating Data

UPDATE departments SET dept_name = 'Sales' WHERE dept_id = 3;

Deleting Data

DELETE FROM departments WHERE dept_id = 1;

Using Transactions

START TRANSACTION;

UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 2;
SAVEPOINT before_bonus;

UPDATE employees SET bonus = 5000 WHERE position = 'Manager';

-- An error occurs here, decide to rollback to savepoint
ROLLBACK TO SAVEPOINT before_bonus;

-- Commit the transaction
COMMIT;

---

Quick Revision Notes

SQL Sub-Languages

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE, RENAME

• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE

• DCL (Data Control Language): GRANT, REVOKE

• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT

ACID Properties

• Atomicity: All or nothing execution of transactions.

• Consistency: Transactions maintain database integrity.

• Isolation: Concurrent transactions do not interfere.

• Durability: Committed transactions persist permanently.

Key SQL Concepts

• Primary Key: Unique identifier for table records.

• Foreign Key: Field that links two tables.

• Indexes: Improve the speed of data retrieval.

• Constraints: Rules enforced on data columns (e.g., NOT NULL, UNIQUE).

Common SQL Clauses

• WHERE: Filter records.

• ORDER BY: Sort records.

• GROUP BY: Group records sharing a property.

• HAVING: Filter groups.

• JOIN: Combine rows from two or more tables.

Types of Joins

• INNER JOIN: Returns records with matching values in both tables.

• LEFT JOIN: All records from the left table, and matched records from the right.

• RIGHT JOIN: All records from the right table, and matched records from the left.

• FULL OUTER JOIN: All records when there is a match in either left or right table.

---

Additional Notes

Constraints

• NOT NULL: Ensures a column cannot have a NULL value.

• UNIQUE: Ensures all values in a column are unique.

• DEFAULT: Sets a default value for a column.

• CHECK: Ensures all values in a column satisfy a specific condition.

• FOREIGN KEY: Ensures referential integrity between tables.

Indexes

• Purpose: Speed up the retrieval of rows.

• Creating an Index:

    CREATE INDEX idx_employee_name ON employees(name);
  

Subqueries

• Definition: A query nested inside another query.

• Example:

    SELECT name FROM employees WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Engineering');
  

Aggregate Functions

• Common Functions: COUNT(), SUM(), AVG(), MIN(), MAX()

• Example:

    SELECT dept_id, COUNT(*) as total_employees
    FROM employees
    GROUP BY dept_id;
  

---

Conclusion

Understanding SQL and its various sub-languages is essential for working with relational databases. Mastery of DDL, DML, DCL, and TCL commands, along with the ACID properties, equips you with the tools to create robust and reliable database applications.

---

Practice Exercise

1. Create a Table: Write a SQL statement to create a table named products with columns for product_id (primary key), product_name, category, and price.

    CREATE TABLE products (
        product_id INT PRIMARY KEY,
        product_name VARCHAR(100),
        category VARCHAR(50),
        price DECIMAL(10, 2)
    );
   

2. Insert Data: Insert three products into the products table.

    INSERT INTO products (product_id, product_name, category, price)
    VALUES
    (1, 'Laptop', 'Electronics', 1200.00),
    (2, 'Desk Chair', 'Furniture', 150.00),
    (3, 'Coffee Mug', 'Kitchenware', 12.99);
   

3. Query Data: Write a query to select all products in the 'Electronics' category.

    SELECT * FROM products WHERE category = 'Electronics';
   

4. Update Data: Increase the price of all products in the 'Furniture' category by 10%.

    UPDATE products SET price = price * 1.10 WHERE category = 'Furniture';
   

5. Delete Data: Remove the product with product_id 3.

    DELETE FROM products WHERE product_id = 3;
   

---