SQL, constraints, views and PL/SQL notes — Unit 3

SQL (Structured Query Language) is the standard language for interacting with Relational Database Management Systems. It was developed by IBM in the 1970s (originally called SEQUEL) and is now an ANSI/ISO standard.

SQL is a comprehensive language that encompasses several sub-languages:

• DDL (Data Definition Language): Defines the database structure (`CREATE`, `ALTER`, `DROP`).
• DML (Data Manipulation Language): Manipulates the data (`SELECT`, `INSERT`, `UPDATE`, `DELETE`).
• DCL (Data Control Language): Manages security and permissions (`GRANT`, `REVOKE`).
• TCL (Transaction Control Language): Manages transactions (`COMMIT`, `ROLLBACK`).

SQL is declarative; you state what you want the database to do, and the DBMS execution engine determines the most efficient procedural path to retrieve or modify the data.

The `CREATE TABLE` command is used to design the structural blueprint of a relation.

⇒2.1 Creating Tables and Data Types

SQL requires strict data typing for every column.

```sql
CREATE TABLE Employee (
EmpID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Salary DECIMAL(10, 2)
);
```

⇒2.2 Modifying Tables

The `ALTER TABLE` command changes the schema of an existing table without deleting its data.

`ALTER TABLE Employee ADD DepartmentID INT;`
`ALTER TABLE Employee DROP COLUMN DateOfBirth;`

⇒2.3 Destroying Tables

`DROP TABLE Employee;` completely deletes the table structure and all contained data from the hard drive.

To ensure data integrity, constraints must be defined directly within the `CREATE TABLE` statement. The DBMS will actively reject any data that violates these rules.

⇒3.1 Common Constraints

• `NOT NULL`: Ensures the column cannot be left empty.
• `UNIQUE`: Ensures all values in the column are completely distinct (no duplicates).
• `CHECK (condition)`: Ensures values meet a specific logical condition (e.g., `CHECK (Salary > 0)`).
• `DEFAULT value`: Automatically inserts a default value if the user omits the column during an INSERT.

⇒3.2 Primary and Foreign Keys

```sql
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
DeptID INT,
Name VARCHAR(50) NOT NULL,
-- Implementing Referential Integrity:
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
```

If you try to insert an Employee with a `DeptID` of 99, but Department 99 doesn't exist in the `Department` table, SQL will throw a severe constraint violation error.

⇒4.1 Inserting Data

```sql
INSERT INTO Employee (EmpID, Name, Salary)
VALUES (101, 'Alice Smith', 75000);
```

⇒4.2 Updating Data

Modifies existing rows. CRITICAL WARNING: Always use a `WHERE` clause. If you omit it, the DBMS will update every single row in the table.

```sql
UPDATE Employee
SET Salary = Salary * 1.10
WHERE DeptID = 3;
```

⇒4.3 Deleting Data

Removes rows entirely. Again, forgetting the `WHERE` clause will erase the entire table's contents.

```sql
DELETE FROM Employee
WHERE EmpID = 101;
```

The `SELECT` statement is the most frequently used and complex command in SQL. It is the direct implementation of Relational Algebra's $\pi$ (Project) and $\sigma$ (Select).

⇒5.1 Basic Syntax

```sql
SELECT column1, column2
FROM table_name
WHERE condition;
```

To retrieve all columns, use the wildcard ``: `SELECT FROM Employee;`

⇒5.2 Filtering with WHERE

The `WHERE` clause applies boolean logic. You can use standard operators (`=`, `>`, `<`), logical operators (`AND`, `OR`, `NOT`), and specialized SQL operators:

• `BETWEEN x AND y`: Inclusive range check.
• `IN (val1, val2)`: Checks if a value matches any item in a list.
• `LIKE`: For string pattern matching. `%` represents any sequence of characters, `_` represents a single character. (e.g., `WHERE Name LIKE 'A%'` finds all names starting with A).
• `IS NULL`: The only mathematical way to check for missing data. You cannot use `= NULL`.

⇒6.1 Ordering Results

Because mathematical relations are unordered, SQL returns rows in a random order. To enforce an order, use `ORDER BY`.

`SELECT Name, Salary FROM Employee ORDER BY Salary DESC, Name ASC;`

⇒6.2 Aggregate Functions

SQL provides built-in functions that perform calculations across multiple rows to return a single summarized value.

• `COUNT(*)`: Returns the total number of rows.
• `SUM(column)`: Adds all values in a numeric column.
• `AVG(column)`: Calculates the mean average.
• `MIN(column)` / `MAX(column)`: Finds the lowest/highest value.

Example: `SELECT MAX(Salary), AVG(Salary) FROM Employee;` returns exactly one row containing two numbers.

What if you don't want the average salary of the entire company, but rather the average salary per department?

⇒7.1 The GROUP BY Clause

This clause partitions the output into distinct groups based on the values in a specific column, and applies the aggregate function to each group individually.

```sql
SELECT DeptID, AVG(Salary)
FROM Employee
GROUP BY DeptID;
```

Crucial Rule: If you use a `GROUP BY`, any column in your `SELECT` list that is NOT wrapped in an aggregate function MUST appear in the `GROUP BY` clause. Otherwise, the SQL engine throws a syntax error.

⇒7.2 The HAVING Clause

How do you filter the groups themselves? You cannot use `WHERE AVG(Salary) > 50000` because the `WHERE` clause filters individual rows before aggregation happens.

The `HAVING` clause is specifically designed to filter groups after aggregation.

```sql
SELECT DeptID, AVG(Salary)
FROM Employee
GROUP BY DeptID
HAVING AVG(Salary) > 50000;
```

Databases are designed to split data across many tables (Normalization). To piece the data back together into a useful report, we use JOINs.

⇒8.1 INNER JOIN

Returns only the rows that have matching values in BOTH tables. If an employee has no department assigned, they will not appear in the result.

```sql
SELECT Employee.Name, Department.DeptName
FROM Employee
INNER JOIN Department ON Employee.DeptID = Department.DeptID;
```

⇒8.2 LEFT (OUTER) JOIN

Returns ALL rows from the Left table (Employee), even if there is no match in the Right table. The missing Department columns will be filled with `NULL`.

```sql
SELECT Employee.Name, Department.DeptName
FROM Employee
LEFT JOIN Department ON Employee.DeptID = Department.DeptID;
```

Similarly, a `RIGHT JOIN` returns all rows from the right table.

A Subquery is a `SELECT` statement completely enclosed inside another SQL statement. It allows you to perform complex filtering based on dynamic data.

⇒9.1 Single-Row Subqueries

Used when the inner query is guaranteed to return exactly one value.
Example: Find employees who earn more than the company average.

```sql
SELECT Name, Salary
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
```

⇒9.2 Multi-Row Subqueries

Used when the inner query returns a list of values. You cannot use `=` or `>`. You must use operators like `IN`, `ANY`, or `ALL`.

```sql
SELECT Name
FROM Employee
WHERE DeptID IN (SELECT DeptID FROM Department WHERE Location = 'New York');
```

This finds all employees working in any department located in New York.

A View is a "Virtual Table" based on the result-set of an SQL statement. It contains no data of its own; it simply acts as a saved window into the underlying base tables.

⇒10.1 Creating a View

```sql
CREATE VIEW NY_Employees AS
SELECT Name, Salary
FROM Employee
WHERE DeptID = 5;
```

Once created, a user can query it just like a real table: `SELECT * FROM NY_Employees;`

⇒10.2 Advantages of Views

• Security: You can grant a user access to the `NY_Employees` view while completely denying them access to the main `Employee` table, hiding sensitive data from other departments.
• Simplicity: A view can hide a massive, ugly 5-table JOIN query behind a simple virtual table name, making life easier for frontend developers.
• Data Independence: Fulfills the External Level of the Three-Schema architecture.

SQL is a declarative language. It has no loops (`for`, `while`), no conditional logic (`if/else`), and no variables. It cannot handle complex business logic.

PL/SQL (Procedural Language extension to SQL), developed by Oracle, wraps standard SQL queries inside a procedural programming structure.

⇒11.1 The Block Structure

Every PL/SQL program is composed of Blocks.

```plsql
DECLARE
-- Variables are declared here
emp_salary NUMBER(10,2);
BEGIN
-- Procedural logic and SQL queries execute here
SELECT Salary INTO emp_salary FROM Employee WHERE EmpID = 1;

IF emp_salary < 50000 THEN
UPDATE Employee SET Salary = Salary + 5000 WHERE EmpID = 1;
END IF;
EXCEPTION
-- Error handling
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found!');
END;
```

When an SQL `SELECT` query returns multiple rows, PL/SQL cannot handle it in a simple variable, because a variable can only hold one value at a time. The program will crash with a `TOO_MANY_ROWS` exception.

⇒12.1 Explicit Cursors

A Cursor is a pointer to a dedicated memory area (Context Area) that holds the multi-row result set of a query. It allows the PL/SQL program to iterate through the result set one row at a time, exactly like a file pointer reading a file.

The sequence of operations is strict:

• `DECLARE` the cursor and define its SQL query.
• `OPEN` the cursor (This actually executes the query and populates the memory).
• `FETCH` the data row-by-row inside a LOOP into local variables.
• `CLOSE` the cursor to free the memory.

A Trigger is a specialized stored PL/SQL block that the DBMS automatically executes (fires) when a specific DML event occurs on a specific table.

You do not manually call a trigger; the database engine guarantees it will run.

⇒13.1 Use Cases

• Auditing: Automatically logging exactly who changed a salary and when.
• Enforcing Complex Business Rules: Ensuring a new employee's salary is not higher than their manager's (which cannot be done with simple SQL `CHECK` constraints).
• Automatic Calculations: Updating a `Total_Stock` table automatically whenever a new `Sale` is inserted.

⇒13.2 Syntax Example

```plsql
CREATE TRIGGER Audit_Salary_Change
AFTER UPDATE OF Salary ON Employee
FOR EACH ROW
BEGIN
INSERT INTO Salary_Audit (EmpID, OldSal, NewSal, ChangeDate)
VALUES (:OLD.EmpID, :OLD.Salary, :NEW.Salary, SYSDATE);
END;
```

Unit 3 is heavily practical. You must be able to write correct, syntax-perfect SQL code on paper.

⇒14.1 University Exam Checklist

• Write the `CREATE TABLE` syntax, including Primary Key, Foreign Key, NOT NULL, and CHECK constraints.
• Write SQL queries using `WHERE`, `ORDER BY`, and `LIKE` pattern matching.
• Explain the difference between `WHERE` and `HAVING`.
• Write complex queries using Aggregate Functions coupled with `GROUP BY` and `HAVING`.
• Write queries utilizing `INNER JOIN` and `LEFT OUTER JOIN` across three different tables.
• Write nested queries (Subqueries) using the `IN` operator.
• What is a View in SQL? What are its primary advantages regarding security?
• Explain the block structure of PL/SQL (Declare, Begin, Exception, End).
• What is a Cursor? Explain the four steps required to use an Explicit Cursor.
• What is a Trigger? Write a basic trigger to audit table changes.