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

Syllabus Topics:

• Lecture:
  • Relational Algebra Operations (Selection, Projection, Union, Intersect*, Minus*, Cross/Cartesian)
  • Joins (Equi, Inner, Outer, Natural, Cross), SQL Standard Syntax for Joins
  • Copying Table Structure/Data
  • Sequences (AUTO_INCREMENT)
• Lab:
  • Union/Union ALL
  • Queries on Various Types of Joins using OLD and SQL Standard Syntax
  • Copying Table Structure, Copying Data from One Table to Another
  • Using AUTO_INCREMENT

Note: The syllabus marks Intersect and Minus with an asterisk, indicating they may not be directly supported in MySQL. Workarounds (e.g., using subqueries or joins) will be discussed.

Lecture Notes (In-Depth)

1. Relational Algebra Operations

Relational algebra is a theoretical framework for manipulating relational databases, forming the basis for SQL queries. It provides a set of operations to query and manipulate data.

• Selection (σ):

  • Filters rows based on a condition.
  • Syntax: σ_condition(R) where R is a relation (table).
  • Example: σ_salary > 50000(Employees) selects employees with a salary greater than 50,000.
  • SQL Equivalent: SELECT * FROM Employees WHERE salary > 50000;.

• Projection (π):

  • Selects specific columns from a table, eliminating duplicates.
  • Syntax: π_columns(R).
  • Example: π_name, dept_id(Employees) retrieves unique name and dept_id pairs.
  • SQL Equivalent: SELECT DISTINCT name, dept_id FROM Employees;.

• Union (∪):

  • Combines rows from two tables, removing duplicates.
  • Syntax: R ∪ S.
  • Example: Combine employees from two branches.
  • SQL Equivalent: SELECT emp_id FROM Employees_Branch1 UNION SELECT emp_id FROM Employees_Branch2;.

• Intersect (∩):

  • Returns rows common to two tables.

  • Note: MySQL does not support INTERSECT directly. Use subqueries or joins.

  • Example: Find employees in both Employees_Branch1 and Employees_Branch2.

  • SQL Workaround:

    SELECT emp_id FROM Employees_Branch1
    WHERE emp_id IN (SELECT emp_id FROM Employees_Branch2);
    
    

• Minus (–):

  • Returns rows in one table not present in another.

  • Note: MySQL does not support MINUS (or EXCEPT). Use NOT IN or LEFT JOIN.

  • Example: Find employees in Employees_Branch1 but not in Employees_Branch2.

  • SQL Workaround:

    SELECT emp_id FROM Employees_Branch1
    WHERE emp_id NOT IN (SELECT emp_id FROM Employees_Branch2);
    
    

• Cross/Cartesian Product (×):

  • Combines all rows from two tables, producing every possible combination.
  • Syntax: R × S.
  • Example: Pair every employee with every department.
  • SQL Equivalent: SELECT * FROM Employees CROSS JOIN Departments;.
  • Note: Rarely used alone due to large result sets (m × n rows for tables with m and n rows).

2. Joins (Equi, Inner, Outer, Natural, Cross)

Joins combine rows from two or more tables based on a related column.

• Equ性:
  • Joins tables based on equality of columns (e.g., Employees.dept_id = Departments.dept_id).
  • SQL Syntax: SELECT * FROM Employees JOIN Departments ON Employees.dept_id = Departments.dept_id;.
• Inner Join:
  • Returns only the matched rows from both tables.
  • Example: Retrieve employees and their department names where matches exist.
  • SQL: SELECT e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id = d.dept_id;.
• Outer Join:
  • Left Outer Join: Includes all rows from the left table, with NULLs for non-matching rows in the right table.
    • Example: List all employees, including those without a department.
    • SQL: SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.dept_id;.
  • Right Outer Join: Includes all rows from the right table, with NULLs for non-matching rows in the left table.
    • Example: List all departments, including those with no employees.
    • SQL: SELECT e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.dept_id = d.dept_id;.
  • Full Outer Join: Not directly supported in MySQL. Use UNION of LEFT JOIN and RIGHT JOIN.

    • Example: List all employees and departments, including unmatched rows.

    • SQL Workaround:

      (SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.dept_id)
      UNION
      (SELECT e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.dept_id = d.dept_id WHERE e.dept_id IS NULL);