Lecture on More SQL in Database Systems

Chapter 7: More SQL

Chapter 7 Outline

• More Complex SQL Retrieval Queries

• Specifying Constraints as Assertions and Actions as Triggers

• Views (Virtual Tables) in SQL

• Schema Change Statements in SQL

More Complex SQL Retrieval Queries

• Features that allow for more complex queries from databases:

  • Derived values: Values calculated from other attributes.

  • Nested queries: Queries nested within another SQL query.

  • Joined tables: Combining multiple tables to retrieve necessary data.

  • Outer joins: Retrieves records from one table even if there are no matching records in the other table.

  • Aggregate functions and grouping: Summarizes data across multiple records.

Comparisons Involving NULL

• Meanings of NULL:

  • Represents an unknown value.

  • Denotes an unavailable or withheld value.

  • Indicates a not applicable attribute.

• Each individual NULL value is considered distinct from every other NULL value.

• SQL employs a three-valued logic which encompasses:

  • TRUE

  • FALSE

  • UNKNOWN

• Significance of NULL:

  • Impacts the result sets and logical operations in SQL queries.

Logical Connectives in Three-Valued Logic

• Table 5.1 Logical Connectives in Three-Valued Logic:

  • AND:

  • TRUE AND TRUE = TRUE

  • TRUE AND FALSE = FALSE

  • TRUE AND UNKNOWN = UNKNOWN

  • FALSE AND FALSE = FALSE

  • UNKNOWN AND UNKNOWN = UNKNOWN

  • OR:

  • TRUE OR FALSE = TRUE

  • TRUE OR UNKNOWN = TRUE

  • FALSE OR UNKNOWN = UNKNOWN

  • UNKNOWN OR UNKNOWN = UNKNOWN

  • NOT:

  • NOT TRUE = FALSE

  • NOT FALSE = TRUE

  • NOT UNKNOWN = UNKNOWN

SQL Queries for NULL

• SQL provides queries to check NULL values:

  • IS NULL: Checks if the attribute value is NULL.

  • IS NOT NULL: Checks if the attribute value is not NULL.

Nested Queries

• Nested Queries: Queries placed inside another SQL query.

  • In WHERE Clause:

  • General Syntax: WHERE [NOT] attribute ⟨comp_op⟩ (subquery)

  • Types of comparisons:

    • WHERE [NOT] attribute ⟨comp_op⟩ ANY (subquery)

    • WHERE [NOT] attribute ⟨comp_op⟩ ALL (subquery)

    • WHERE attribute [NOT] IN (subquery)

• In FROM Clause:

  • Creates a temporary table on-the-fly.

  • General Syntax: FROM (subquery) AS alias

IN Clause

• IN Operator: Compares value v against a set of known values V.

  • Evaluates to TRUE if v is present in V.

Example of Nested Queries

• Query with Nested Structure (Q4A):

  • SELECT DISTINCT Pnumber FROM PROJECT WHERE Pnumber IN (
    SELECT Pno FROM WORKSON, EMPLOYEE WHERE Dnum = Dnumber AND Mgrssn = Ssn AND Lname = 'Smith')

Explicit Set Examples

• Using Explicit Set in WHERE Clause:

  • Example Query (Q17):

  • SELECT DISTINCT Essn FROM WORKS_ON WHERE Pno IN (1, 2, 3);

Comparison Operators

• Additional Operators:

  • = ANY (or = SOME): Returns TRUE if value v is equal to some value in set V.

  • Supported operators: >, >=, <, <=, and <>.

Correlated Nested Queries

• A Correlated Nested Query references an attribute from the outer query.

  • Evaluated for each tuple in the outer query.

  • Example explained via tuple evaluations highlighting relationships.

EXISTS Functions in SQL

• EXISTS Clause: Used to check if a nested query returns any results.

  • No direct comparison to result set elements required.

• EXISTS and NOT EXISTS are typically paired with correlated nested queries.

Example with EXISTS

• Combined Query (Q16B):

  • SELECT E.Fname, E.Lname FROM EMPLOYEE AS E
    WHERE EXISTS (
    SELECT * FROM DEPENDENT AS D
    WHERE E.Ssn = D.Essn AND E.Sex = D.Sex AND E.Fname = D.Dependent_name);

Renaming of Attributes

• Renaming Attributes: Use AS followed by desired new name.

• Renaming can apply to any attribute appearing in query results.

Joins

• Joined Table: Facilitates table specification from a join operation in the FROM clause.

  • Example, a single joined table in query.

Types of Joins

• NATURAL JOIN: Implicitly equijoin conditions for attributes of same name.

• Inner Join: Default join; includes tuples only with matches in both relations.

• Outer Joins:

  • LEFT JOIN: All tuples from left table, NULL for no matches in right.

  • RIGHT JOIN: Not supported in SQLite.

  • FULL JOIN: Not supported in MySQL, SQLite; can be simulated using LEFT/RIGHT JOIN UNION.

Aggregate Functions, GROUP BY, and HAVING

• Aggregate Functions: Summarize data across multiple tuples.

  • Built-in functions include: COUNT, SUM, MAX, MIN, AVG

• GROUP BY Clause: Enables creation of subgroups for aggregation.

• HAVING Clause: Conditions applied to summary information, as opposed to WHERE clause.

Grouping in SQL

• Grouping: Partitions relations into subsets.

  • NULL values create separate groups.

Discussion Summary of SQL Queries

• Structure of SQL Query:

  • SELECT

  • FROM

    • [WHERE ]

    • [GROUP BY ]

    • [HAVING ]

    • [ORDER BY ];

    Views (Virtual Tables) in SQL

    • View Concept: Derived single table from other tables, considered virtual.

    Specification of Views in SQL

    • CREATE VIEW Command: Specifies view name, fields, and defining SQL query.

    • View Maintenance: Always reflects changes in underlying tables; accomplished by DBMS, not user.

    • DROP VIEW Command: Used to remove a view.

    View Implementation

    • Implementation Strategies:

      • Query modification: Transform the view query into a query on base tables (may be time-consuming).

      • Materialization: Create a temporary table on first query for efficiency, requiring updates for base tables changes.

      • Incremental updates: DBMS manages new updates in materialized views.

    View Updates and Inline Views

    • Views can be updated if they are defined on a single table without aggregates.

    • In-line views are defined directly in the FROM clause of queries.

    Administration and Schema Evolution

    • Schema Change Statements: Allow live updates without recompilation of the entire schema.

    DROP Command

    • DROP Command: To remove schema elements like tables/constraints with options for CASCADE or RESTRICT.

    • Example: DROP SCHEMA company CASCADE;

    ALTER Command

    • Modifications possible with ALTER: Add/drop columns, change definitions, and constraints.

    • Examples:

      • Adding column: ALTER TABLE company.employee ADD COLUMN job VARCHAR(12);

      • Dropping column: must specify either CASCADE or RESTRICT.

    ALTER Commands Continued

    • Change columns:

      • ALTER TABLE table_name CHANGE old_column_name new_column_definition;

      • Example: ALTER TABLE Employee CHANGE Dno Dnumber INT NOT NULL;

    Summary

    • Topics covered on complex SQL structures, including:

      • Nested queries,

      • Joined tables and outer joins,

      • Aggregate functions,

      • Grouping capabilities,

      • View definitions and implementations.