SQL: Advanced Queries, Assertions, Triggers and Views

SQL: Advanced Queries, Assertions, Triggers and Views

SQL offers advanced features for complex data retrieval, including nested queries, joined tables, outer joins, aggregate functions, and grouping.

Comparisons Involving NULL and Three-Valued Logic

  • Meanings of NULL:
    • Unknown value.
    • Unavailable or withheld value.
    • Not applicable attribute.
  • Each NULL value is considered distinct from every other NULL value.
  • SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.
  • SQL allows queries to check for NULL values using IS NULL or IS NOT NULL.

Nested Queries, Tuples, and Set/Multiset Comparisons

  • Nested queries: Complete SELECT-FROM-WHERE blocks within the WHERE clause of another query.
  • Outer query
  • Comparison operator IN: evaluates to TRUE if a value is in a set of values.

Example Query (Q4A)

To list project numbers for projects involving an employee named 'Smith' as a worker or department manager:

(SELECT DISTINCT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith')
UNION
(SELECT DISTINCT Pnumber
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber = Pno AND Essn = Ssn AND Lname = 'Smith');

Alternative using IN:

SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber IN
    (SELECT Pnumber
     FROM PROJECT, DEPARTMENT, EMPLOYEE
     WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith')
   OR Pnumber IN
    (SELECT Pno
     FROM WORKS_ON, EMPLOYEE
     WHERE Essn = Ssn AND Lname = 'Smith');
  • Tuples of values can be used in comparisons within parentheses.
  • = ANY (or = SOME) operator: Returns TRUE if the value is equal to some value in the set; equivalent to IN.
  • Other operators combined with ANY (or SOME): >, >=,
  • Avoid potential errors by creating tuple variables (aliases) for all tables referenced in SQL query
  • Correlated nested query: Evaluated once for each tuple in the outer query

The EXISTS and UNIQUE Functions in SQL

  • EXISTS function: Checks whether the result of a correlated nested query is empty or not.
  • EXISTS and NOT EXISTS are typically used with correlated nested queries.
  • UNIQUE(Q) function: Returns TRUE if there are no duplicate tuples in the result of query Q.

Explicit Sets and Renaming of Attributes in SQL

  • Use explicit sets of values in the WHERE clause.
  • Use qualifier AS followed by desired new name to rename any attribute in the result of a query.

Joined Tables in SQL and Outer Joins

  • Joined table: Specifies a table resulting from a join operation in the FROM clause.
  • Different types of joins: NATURAL JOIN, various types of OUTER JOIN.
  • NATURAL JOIN on relations R and S: Implicit EQUIJOIN condition for each pair of attributes with the same name from R and S.
  • Inner join: Default join type; tuple included only if a matching tuple exists in the other relation.
  • LEFT OUTER JOIN: Every tuple in the left table must appear in the result. If no matching tuple, padded with NULL values for attributes of the right table.
  • RIGHT OUTER JOIN: Every tuple in the right table must appear in the result. If no matching tuple, padded with NULL values for attributes of the left table.
  • FULL OUTER JOIN
  • Join specifications can be nested.

Examples of Joins

Assuming tables foot and tennis with a common column scode:

  • Inner Join:

    SELECT * FROM foot INNER JOIN tennis ON foot.scode = tennis.scode

  • Left Outer Join:

    SELECT * FROM foot LEFT OUTER JOIN tennis ON foot.scode = tennis.scode

  • Right Outer Join:

    SELECT * FROM foot RIGHT OUTER JOIN tennis ON foot.scode = tennis.scode

  • Full Outer Join:

    Requires specific database support, may be simulated using UNION of left and right outer joins.

Aggregate Functions in SQL

  • Used to summarize information from multiple tuples into a single-tuple summary.
  • Grouping: Create subgroups of tuples before summarizing.
  • Built-in aggregate functions: COUNT, SUM, MAX, MIN, and AVG.
  • Functions can be used in the SELECT clause or in a HAVING clause.
  • NULL values are discarded when aggregate functions are applied to a column.

Grouping: The GROUP BY and HAVING Clauses

  • Partition relation into subsets of tuples based on grouping attribute(s).
  • Apply function to each group independently.
  • GROUP BY clause: Specifies grouping attributes.
  • If NULLs exist in grouping attribute: Separate group created for all tuples with a NULL value.
  • HAVING clause: Provides a condition on the summary information.

Example Queries

  1. For each department, retrieve the department number, the number of employees, and their average salary:

    SELECT Dno, COUNT(*), AVG(Salary)
FROM EMPLOYEE
GROUP BY Dno;

  2. Count the total number of employees in each department, but only for departments where more than five employees work whose salaries exceed $40,000:

    SELECT Dno, COUNT(*)
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber = Dno AND Salary > 40000
GROUP BY Dno
HAVING COUNT(*) > 5;

  3. Retrieve the department number, the number of employees in the department, and the maximum and minimum salary per department, for departments where the employee is not supervised:

    SELECT Dno, COUNT(*), MAX(Salary), MIN(Salary)
FROM EMPLOYEE
WHERE Super_SSN is not NULL
GROUP BY Dno
HAVING COUNT(*) >= 1;

Specifying Constraints as Assertions and Actions as Triggers

  • CREATE ASSERTION: Specifies additional constraints outside the scope of built-in relational model constraints.
  • CREATE TRIGGER: Specifies automatic actions that the database system will perform when certain events and conditions occur.

Specifying General Constraints as Assertions in SQL

  • CREATE ASSERTION: Specifies a query that selects any tuples that violate the desired condition.
  • Ensures a condition is satisfied or stops action being taken on a database object.
  • Used only when CHECK on attributes and domains is insufficient.
  • An assertion must always be true at transaction boundaries; any modification that causes it to become false is rejected.
  • Assertions can be dropped using a DROP command.

Example Assertion

CREATE ASSERTION RichMGR CHECK (
    NOT EXISTS (
        SELECT *
        FROM dept, emp
        WHERE emp.name = dept.mgrname AND emp.salary < 50000
    )
);

This assertion guarantees that each manager makes more than $50,000. Any insertion/update violating this condition will be rejected.

Introduction to Triggers in SQL

  • A statement that is automatically executed by the system as a side effect of an event.

  • CREATE TRIGGER statement: Used to monitor the database.

  • Typical trigger components:

    • Event(s)
    • Condition
    • Action

  • Format:

    CREATE TRIGGER <trigger_name>
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
[FOR EACH ROW [WHEN (<trigger_condition>)]]
<trigger_body>

  • If FOR EACH ROW is specified, the trigger is row-level; otherwise, statement-level.

Triggers: Example

CREATE TRIGGER SALARY_VIOLATION
BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN ON EMPLOYEE
FOR EACH ROW
WHEN (NEW.SALARY > (SELECT SALARY FROM EMPLOYEE WHERE SSN = NEW.SUPERVISOR_SSN))
INFORM_SUPERVISOR(NEW.Supervisor_ssn, New.Ssn);

Schema Change Statements in SQL

  • Schema evolution commands can be done while the database is operational and do not require recompilation.

The DROP Command

  • Used to drop named schema elements, such as tables, domains, or constraints.

  • Drop behavior options: CASCADE and RESTRICT.

    DROP SCHEMA COMPANY CASCADE;

The ALTER Command

  • Alter table actions include:

    • Adding or dropping a column (attribute).
    • Changing a column definition.
    • Adding or dropping table constraints.

  • Example:

    ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12);

  • To drop a column, choose either CASCADE or RESTRICT.

  • Change constraints specified on a table.

  • Add or drop a named constraint

Views (Virtual Tables) in SQL

  • A single table derived from other tables; considered a virtual table.

Specification of Views in SQL

  • CREATE VIEW command: Give table name, list of attribute names, and a query to specify the contents of the view.
  • Specify SQL queries on a view.
  • The view is always up-to-date; the responsibility of the DBMS, not the user.
  • DROP VIEW command is used to dispose of a view.

View Implementation, View Update, and Inline Views

  • Complex problem of efficiently implementing a view for querying.
  • Query modification approach: Modify the view query into a query on underlying base tables.
    • Disadvantage: inefficient for views defined via complex queries that are time-consuming to execute.
  • View materialization approach: Physically create a temporary view table when the view is first queried and keep that table on the assumption that other queries on the view will follow
    • Requires efficient strategy for automatically updating the view table when the base tables are updated.

View Implementation (cont’d.)

  • Incremental update strategies
  • DBMS determines what new tuples must be inserted, deleted, or modified in a materialized view table.

View Update and Inline Views

  • Update on a view defined on a single table without any aggregate functions can be mapped to an update on the underlying base table.
  • For views involving joins, it is often not possible for DBMS to determine which of the updates is intended.
  • Clause WITH CHECK OPTION must be added at the end of the view definition if a view is to be updated.
  • In-line view: Defined in the FROM clause of an SQL query.

Summary

  • Complex SQL includes nested queries, joined tables, outer joins, aggregate functions, grouping.
  • CREATE ASSERTION and CREATE TRIGGER add constraints and actions.
  • Views: Virtual or derived tables. They simplify queries and provide data abstraction.