SQL Schema Evolution and DROP Command

Nested Queries

• A nested query is a complete select-from-where block within the WHERE clause of another query
• An outer query and nested subqueries are used to compare a value with a set (or multiset) of values
• The IN operator is used to compare a value with a set (or multiset) of values

Tuple Comparisons

• Tuples of values can be used in comparisons by placing them within parentheses
• The = ANY (or = SOME) operator returns TRUE if the value is equal to some value in the set V and is equivalent to IN
• Other comparison operators that can be combined with ANY (or SOME) are >, >=, etc.

Combining WHERE and HAVING Clauses

• The WHERE clause applies tuple by tuple, whereas the HAVING clause applies to entire groups of tuples
• The correct specification of the query is: GROUP BY Dno HAVING COUNT (*) > 5;

WITH Clause

• The WITH clause allows a user to define a table that will only be used in a particular query
• It is used for convenience to create a temporary “View” and use that immediately in a query
• It allows a more straightforward way of looking at a step-by-step query

Recursive Queries

• A recursive operation is used to retrieve all supervisees of a supervisory employee at all levels
• The WITH RECURSIVE clause is used to define a recursive query
• The query starts with an empty table and successively builds the table by computing immediate supervisees first, then second-level supervisees, and so on, until a fixed point is reached and no more supervisees can be added

Expanded Block Structure of SQL Queries

• The expanded block structure of SQL queries includes SELECT, FROM, WHERE, GROUP BY, and HAVING clauses

Specifying Constraints and Actions

• Semantic constraints are those beyond the scope of the EER and relational model
• CREATE ASSERTION is used to specify additional types of constraints outside the scope of built-in relational model constraints
• CREATE TRIGGER is used to specify automatic actions that the database system will perform when certain events and conditions occur

Triggers

• A trigger has three components: Event(s), Condition, and Action
• Triggers are used to monitor the database and perform automatic actions when certain events and conditions occur

Views

• A view is a virtual table derived from other tables called the defining tables
• A view is considered to be a virtual table that is not necessarily populated
• CREATE VIEW command is used to specify the contents of the view

Update on Views

• An update on a view defined on a single table without any aggregate functions can be mapped to an update on the underlying base table
• Updates are not permitted on aggregate views

View Update and Inline Views

• Views involving joins often cannot be updated because it is not possible for the DBMS to determine which of the updates is intended
• The WITH CHECK OPTION clause must be added at the end of the view definition if a view is to be updated to make sure that tuples being updated stay in the view
• Inline views are defined in the FROM clause of an SQL query

Views as Authorization Mechanism

• Views can be used to hide certain attributes or tuples from unauthorized users
• SQL query authorization statements (GRANT and REVOKE) are used to control access to views

Schema Change Statements

• Schema evolution commands are used to change the schema while the database is operational
• Schema evolution commands do not require recompilation of the database schema
• DROP, ALTER, and CREATE commands are used to change the schema