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.