RDBMS Lab (ME-606) - Unit 2 Short Notes

UNIT 2: SQL FOUNDATIONS & DATA RETRIEVAL

I. SQL DATA DEFINITION LANGUAGE (DDL)

Commands to define and modify database structure.

• CREATE TABLE Statement

  • Syntax: CREATE TABLE table_name ( column1 datatype(size) constraint, column2 datatype(size) constraint, ... );

  • Column-level constraint: Defined immediately after the column's datatype.

  • Table-level constraint: Defined after all columns, often used for composite keys or multi-column constraints.

  • Common Data Types: INT/INTEGER, VARCHAR(n) (variable string), CHAR(n) (fixed string), DATE, DECIMAL(p,s).

• ALTER TABLE Statement

  • Add Column: ALTER TABLE table_name ADD column_name datatype;

  • Modify Column (change datatype/size): ALTER TABLE table_name MODIFY column_name new_datatype; (Oracle/MySQL). SQL Server: ALTER COLUMN.

  • Drop Column: ALTER TABLE table_name DROP COLUMN column_name;

  • Add/Drop Constraint: ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_def; / DROP CONSTRAINT constraint_name;

• DROP TABLE Statement

  • Syntax: DROP TABLE table_name;

  • Implication: Permanently removes table structure and all data. May fail if referenced by foreign keys unless CASCADE is used (vendor-specific).

• TRUNCATE TABLE vs. DELETE

  • TRUNCATE: DDL command. Removes all rows quickly by deallocating data pages. Cannot have a WHERE clause. Resets identity counters. Minimal logging. Cannot be rolled back in many systems if autocommit is on.

  • DELETE: DML command. Removes rows one by one, can use WHERE. Fully logged. Can be rolled back. Does not reset identity counters.

  [!TIP] Exam Focus: TRUNCATE is faster but less flexible. DELETE is row-level and transactional.

II. ENTITY & REFERENTIAL INTEGRITY CONSTRAINTS

Rules enforced by the RDBMS to ensure data validity.

[!TIP] Common Pitfall: PRIMARY KEY vs. UNIQUE – PK identifies the row; UNIQUE just prevents duplicates and allows NULLs.

III. INDEXES

Database objects to speed up data retrieval.

• Purpose: Improve query performance (especially WHERE, JOIN, ORDER BY). Can enforce uniqueness (UNIQUE index).

• CREATE INDEX Statement

  • Single-column: CREATE INDEX idx_name ON table_name (column_name);

  • Composite: CREATE INDEX idx_name ON table_name (col1, col2); (Order matters for queries).

• Implicit Indexes: Automatically created for PRIMARY KEY and UNIQUE constraints.

IV. SQL DATA MANIPULATION LANGUAGE (DML) - DATA MODIFICATION

• INSERT Statement

  • Single row (explicit columns): INSERT INTO table (col1, col2) VALUES (val1, val2);

  • All columns: INSERT INTO table VALUES (val1, val2, ...); (Must match table order).

  • Multi-row (MySQL/Oracle 23c+): INSERT INTO table (col) VALUES (v1), (v2), ...;

  • From another table: INSERT INTO table1 (col1, col2) SELECT colA, colB FROM table2 WHERE ...;

• UPDATE Statement

  • Syntax: UPDATE table SET column1 = value1, column2 = value2 WHERE condition;

  • Caution: Omitting WHERE updates ALL rows.

  • Update from another table (Correlated/Join): UPDATE t1 SET col = (SELECT col FROM t2 WHERE t2.id = t1.id);

• DELETE Statement

  • Syntax: DELETE FROM table_name WHERE condition;

  • Omitting WHERE deletes ALL rows (table remains, unlike DROP).

  • Interacts with FOREIGN KEY ... ON DELETE rules (e.g., CASCADE deletes child rows).

V. BASIC SQL QUERIES & SELECT STATEMENT

• General Syntax: SELECT [DISTINCT] column_list FROM table_name [WHERE condition] [ORDER BY column_list];

• WHERE Clause Operators:

  • Comparison: =, <>/!=, >, <, BETWEEN low AND high, IN (list), LIKE 'pattern' (% = multi-char, _ = single-char), IS NULL.

  • Logical: AND, OR, NOT.

• ORDER BY: ORDER BY column_name [ASC|DESC], .... Can use column position number (e.g., ORDER BY 1).

• DISTINCT: Eliminates duplicate rows from the result set.

VI. AGGREGATE FUNCTIONS & GROUP BY

• Aggregate Functions: Operate on a set of values and return a single value.

  • COUNT(*) / COUNT(column) – Number of rows/non-NULL values.

  • SUM(column) – Total sum.

  • AVG(column) – Average.

  • MIN(column) / MAX(column) – Smallest/Largest value.

• GROUP BY Clause: Groups rows that have the same values in specified columns. Aggregate functions are then applied per group.

  • Rule: Any column in the SELECT list that is not inside an aggregate function must appear in the GROUP BY clause.

  
  SELECT dept_id, COUNT(*), AVG(salary)
  
  FROM employees
  
  GROUP BY dept_id; -- Correct
  
  

• HAVING Clause: Filters groups after GROUP BY has been applied. Used with aggregate conditions.

  
  SELECT dept_id, AVG(salary) as avg_sal
  
  FROM employees
  
  GROUP BY dept_id
  
  HAVING AVG(salary) > 50000; -- Filters groups
  
  

  [!TIP] WHERE vs HAVING: WHERE filters rows before grouping. HAVING filters groups after aggregation. \boxed{\text{WHERE} \rightarrow \text{GROUP BY} \rightarrow \text{HAVING}}

VII. TABLE JOINING OPERATIONS

Combining rows from two or more tables based on a logical relationship.

• Equi-Join / Inner Join: Returns rows where there is a match in both tables.

  • ANSI Syntax (Preferred): SELECT ... FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id;

  • Legacy Syntax: SELECT ... FROM t1, t2 WHERE t1.id = t2.t1_id;

• Outer Joins: Retain rows from one/both tables even if no match exists.

  • LEFT [OUTER] JOIN: All rows from left table, matched rows from right (NULLs for non-matches).

  • RIGHT [OUTER] JOIN: All rows from right table, matched rows from left.

  • FULL [OUTER] JOIN: All rows from both tables, with NULLs where no match.

• Self-Join: Joins a table to itself. Requires table aliases.

  
  SELECT e1.name, e2.name as manager
  
  FROM employees e1
  
  LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;
  
  

• Cartesian Product (CROSS JOIN): Joins every row of first table with every row of second. Result size = rows(t1) * rows(t2). Caused by omitting ON clause in an inner join.

• Joining >2 Tables: Chain joins: FROM t1 JOIN t2 ON ... JOIN t3 ON ...

VIII. SUBQUERIES (NESTED QUERIES)

A query nested inside another query (outer query).

• WHERE Clause Subqueries:

  • Single-row operators (=, >, <>): Subquery must return exactly one row/column.

  • Multi-row operators (IN, ANY, ALL): Subquery returns a set.

    • IN (subquery) – True if value matches any in the list.

    • > ANY (subquery) – True if greater than at least one value.

    • > ALL (subquery) – True if greater than every value.

  • EXISTS / NOT EXISTS: Tests for existence of rows returned by subquery. Often more efficient for correlated queries.

• Correlated Subquery: Inner query references a column from the outer query. Executed once for each outer row.

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

• FROM Clause Subquery (Derived Table): Subquery acts as a virtual table.

  
  SELECT dept_avg.dept_id, dept_avg.avg_sal
  
  FROM (SELECT dept_id, AVG(salary) as avg_sal FROM employees GROUP BY dept_id) dept_avg
  
  WHERE dept_avg.avg_sal > 60000;
  
  

• SELECT List Subquery (Scalar Subquery): Must return exactly one value (one row, one column). Used to compute a value for each row of the outer query.

IX. SET OPERATIONS

Combine results of two or more SELECT queries.

• UNION / UNION ALL: Combines results (removes duplicates / keeps all). Requires: Same number of columns, compatible data types, same column order.

  
  SELECT col FROM table1
  
  UNION
  
  SELECT col FROM table2;
  
  

• INTERSECT: Returns only rows common to both queries.

• EXCEPT (SQL Server/PostgreSQL) / MINUS (Oracle): Returns rows from first query that are not in the second.

[!TIP] Rule: All queries in a set operation must have identical column structures (number & compatible types).

X. VIEWS

A virtual table based on the result of a stored SELECT query.

• CREATE VIEW Statement:

  
  CREATE VIEW view_name AS
  
  SELECT columns
  
  FROM tables
  
  WHERE conditions;
  
  

• Benefits:

  • Security: Restrict access to specific rows/columns (grant permissions on view, not base table).

  • Simplicity: Encapsulate complex joins/aggregations.

  • Logical Data Independence: Shield applications from changes in base table structure.

• Querying a View: SELECT * FROM view_name; (Querying a view runs the underlying SELECT).

• Updatable Views: Can perform INSERT, UPDATE, DELETE through the view if it meets conditions (typically: single base table, no aggregates, DISTINCT, GROUP BY, etc.).

• DROP VIEW Statement: DROP VIEW view_name;

XI. TRANSACTION CONTROL LANGUAGE (TCL)

Commands that manage transactions (logical units of work).

• BEGIN TRANSACTION / Implicit Start: Starts a transaction (often auto-started on first DML).

• COMMIT: Makes all changes in the current transaction permanent. Releases locks.

• ROLLBACK: Undoes all changes in the current transaction. Returns database to state before transaction began.

• SAVEPOINT: Creates a named point within a transaction to which you can later roll back partially.

  
  SAVEPOINT sp1;
  
  -- Some updates
  
  ROLLBACK TO SAVEPOINT sp1; -- Undo only changes after sp1
  
  COMMIT; -- Make remaining changes permanent
  
  

[!TIP] ACID Context: These commands implement the Atomicity (all-or-nothing via ROLLBACK) and Durability (COMMIT) properties.

XII. ADVANCED OBJECTS (If Covered)

• Triggers: Stored procedures that automatically execute (FIRE) in response to an event (INSERT, UPDATE, DELETE) on a table.

  • Timing: BEFORE (row/statement), AFTER (row/statement), INSTEAD OF (for views).

  • Use Cases: Auditing, enforcing complex business rules, maintaining derived data.

  
  CREATE TRIGGER trg_name
  
  BEFORE INSERT ON table_name
  
  FOR EACH ROW
  
  BEGIN
  
      SET NEW.created_at = NOW();
  
  END;
  
  

• Stored Procedures / Functions: Named PL/SQL/Stored Procedure blocks stored in the DB.

  • Procedure: Performs an action. May have IN, OUT, INOUT parameters. Invoked with CALL proc_name(...);.

  • Function: Returns a single value. Can be used in SQL expressions. Invoked with SELECT func_name(...);.

  • Benefits: Encapsulation (business logic in DB), Performance (pre-compiled, reduced network traffic), Security (grant EXECUTE privilege).