Podcast Beta
Questions and Answers
What is the purpose of schema evolution commands in SQL?
What is the main function of the DROP command in SQL?
What is the effect of using the CASCADE option with the DROP command?
What is the purpose of the ALTER table command in SQL?
Signup and view all the answers
What is the effect of adding a constraint to a table using the ALTER table command?
Signup and view all the answers
How can you drop a column from a table using the ALTER table command?
Signup and view all the answers
What happens when you drop a default value using the ALTER table command?
Signup and view all the answers
What is the purpose of the RESTRICT option with the DROP command?
Signup and view all the answers
What is the effect of using the CASCADE option with the ALTER table command to drop a column?
Signup and view all the answers
What is the purpose of the ALTER table command with the SET DEFAULT option?
Signup and view all the answers
Study Notes
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
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Learn about schema change statements in SQL, including schema evolution commands and the DROP command used to drop named schema elements.
