Duration: 2 hours (Lecture: 2 hours, Lab: 2 hours)

Syllabus Topics:

• Lecture:
  • Subquery, Correlated Subquery
  • EXISTS/NOT EXISTS
  • TCL Commands (Commit/Rollback/Savepoint)
  • DCL Commands (GRANT/REVOKE/GRANT OPTION)
  • Views, Types of Views, Simple and Complex Views
• Lab:
  • Subqueries, Correlated Queries
  • Using EXISTS/NOT EXISTS
  • Using Commit/Rollback/Savepoint
  • Granting/Revoking Privileges on Database Objects
  • Creating Views, Querying Using Views
  • Creating Indexes
  • Creating Temporary Tables

Lecture Notes (In-Depth)

1. Subquery, Correlated Subquery

• Subquery:
  • A query nested within another query, used to return data that the outer query processes.
  • Enclosed in parentheses and typically used in WHERE, SELECT, or FROM clauses.
  • Types:
    • Single-Row Subquery: Returns one row (used with operators like =, >, <).
      • Example: SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);.
    • Multi-Row Subquery: Returns multiple rows (used with IN, ANY, ALL).
      • Example: SELECT name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 100000);.
  • Key Notes:
    • Subqueries execute first, and their result is used by the outer query.
    • Can be less efficient than joins for large datasets.
• Correlated Subquery:

  • A subquery that references columns from the outer query, executed repeatedly for each row of the outer query.

  • Example: Find employees with salaries above their department’s average:

    SELECT name, salary
    FROM employees e
    WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id);
    
    

  • Key Notes:

    • Correlated subqueries are slower because they execute for each row of the outer query.
    • Useful when comparing a row to aggregated data within the same table.

2. EXISTS/NOT EXISTS

• EXISTS:

  • Tests whether a subquery returns at least one row.

  • Returns TRUE if the subquery has results, FALSE otherwise.

  • Example: Find departments with at least one employee:

    SELECT dept_name
    FROM departments d
    WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);
    
    

• NOT EXISTS:

  • Returns TRUE if the subquery returns no rows.

  • Example: Find departments with no employees:

    SELECT dept_name
    FROM departments d
    WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);
    
    

• Key Notes:

  • EXISTS is more efficient than IN for large datasets, as it stops processing once a match is found.
  • Often used with correlated subqueries.
  • The subquery’s SELECT clause is irrelevant (e.g., SELECT 1 or SELECT *).

3. TCL Commands (Commit/Rollback/Savepoint)

• Transaction Control Language (TCL):
  • Manages transactions to ensure data consistency and integrity.
  • Transactions are a sequence of operations treated as a single unit (ACID properties: Atomicity, Consistency, Isolation, Durability).
• COMMIT:
  • Saves all changes made in a transaction permanently.
  • Syntax: COMMIT;.
  • Example: After inserting records, commit to make changes permanent.
• ROLLBACK:
  • Undoes all changes made in the current transaction.
  • Syntax: ROLLBACK;.
  • Example: If an error occurs during an update, roll back to revert changes.
• SAVEPOINT:
  • Sets a point within a transaction to which you can roll back.
  • Syntax: SAVEPOINT savepoint_name;, ROLLBACK TO savepoint_name;.
  • Example: Insert a record, set a savepoint, update it, then roll back to the savepoint if needed.
• Key Notes:

  • MySQL’s InnoDB engine supports transactions; MyISAM does not.

  • Use START TRANSACTION; to begin a transaction explicitly.

  • Example:

    START TRANSACTION;
    INSERT INTO employees (emp_id, name) VALUES (1, 'Alice');
    SAVEPOINT sp1;
    UPDATE employees SET name = 'Bob' WHERE emp_id = 1;
    ROLLBACK TO sp1; -- Reverts to Alice
    COMMIT; -- Saves Alice
    
    

4. DCL Commands (GRANT/REVOKE/GRANT OPTION)