Unit 2 - Practice Quiz

DDL commands are used to define or modify the database schema. CREATE is used to create new database objects like tables, which is a core DDL function. SELECT, INSERT, and UPDATE are DML commands.

The SELECT statement is the primary command used in SQL to query a database and retrieve data that matches criteria that you specify.

COMMIT is a Transaction Control Language (TCL) command used to save all the work done in the current transaction. ROLLBACK undoes the work, and GRANT is a DCL command.

The UNIQUE constraint guarantees that all values in a column are distinct from one another. A PRIMARY KEY also ensures uniqueness but it cannot have NULL values, whereas a UNIQUE constraint can allow one NULL.

A Primary Key is a special relational database table column designated to uniquely identify all table records. It must contain unique values and cannot contain NULL values.

The WHERE clause is used to filter records and extract only those records that fulfill a specified condition.

DROP TABLE is a DDL command that deletes the entire table structure, data, and associated objects like indexes and constraints. DELETE is a DML command that only removes rows from a table.

The INSERT INTO statement is the standard SQL command used to add new records (rows) to a table.

DCL commands are used to manage permissions and access rights. GRANT is a DCL command used to give user access privileges to the database.

A FOREIGN KEY is a key used to link two tables together. It is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

A table can have multiple Candidate Keys. One is selected as the Primary Key, and the rest are known as Alternate Keys.

The standard and most basic order of clauses in an SQL query is SELECT (specifying columns), FROM (specifying the table), and then WHERE (specifying the filter condition).

The ALTER TABLE command is used to add, delete, or modify columns in an existing table, as well as add and drop various constraints on an existing table.

The UPDATE statement is used to modify the existing records in a table. The ALTER command is used to change the table's structure, not its data.

ROLLBACK is a TCL command that is used to undo transactions that have not already been saved to the database using the COMMIT command.

The NOT NULL constraint enforces that a column will not accept NULL values. This means a value must be entered for that column when a new record is inserted.

SQL is an acronym for Structured Query Language, which is the standard language for relational database management systems.

The DELETE command is a DML statement used to remove one or more existing records from a table, typically based on a condition specified in the WHERE clause.

REVOKE is the DCL command that removes user access rights or privileges to the database objects which were previously granted using the GRANT command.

A Super Key is a set of one or more attributes that, taken collectively, allows us to uniquely identify a record in a table. A candidate key is a minimal super key.

First, the column is added using ALTER TABLE ... ADD. This will initially allow NULLs (or set a default). To enforce the NOT NULL constraint on an existing table with data, you would typically add the column, populate it with non-null values for all existing rows, and then use ALTER TABLE ... MODIFY (or ALTER COLUMN depending on the SQL dialect) to add the NOT NULL constraint. Directly adding a NOT NULL column to a non-empty table fails because existing rows would have a null value for the new column.

The CHECK constraint requires that UnitPrice must be greater than 0 AND StockLevel must be greater than or equal to 0. The statement to insert 'Gadget' fails because the UnitPrice of -5.00 violates the UnitPrice > 0 part of the constraint.

A candidate key is any column or set of columns that can uniquely identify a row. In this case, StudentID and SSN are both candidate keys. When one candidate key is selected as the Primary Key, the remaining candidate keys are referred to as Alternate Keys.

The HAVING clause is used to filter groups created by the GROUP BY clause based on aggregate functions. The WHERE clause filters rows before they are grouped. Since COUNT() and SUM() are aggregate functions, they must be used in the HAVING clause to filter the grouped results.

The transaction starts, and the UPDATE is performed. A SAVEPOINT named 'A' is created, marking this state. The INSERT and DELETE statements are then executed. The ROLLBACK TO SAVEPOINT A command undoes all changes made after the savepoint was created (the INSERT and DELETE). The transaction then commits, making the initial UPDATE permanent.

TRUNCATE is a Data Definition Language (DDL) command. It removes all rows from a table quickly by deallocating the data pages. This operation is minimally logged and does not fire DELETE triggers. DELETE is a Data Manipulation Language (DML) command that removes rows one by one, logging each deletion. This makes DELETE slower but allows it to be part of a transaction that can be rolled back.

The ON DELETE SET NULL referential action specifies that if a referenced row in the parent table (Customers) is deleted, the foreign key values in the referencing rows of the child table (Orders) should be set to NULL. This preserves the order records while indicating they are no longer associated with a specific, existing customer.

In SQL LIKE patterns, the underscore _ is a wildcard that matches exactly one character. The percent sign % matches zero or more characters. To find a name that is exactly 4 characters long and ends in 'n', the pattern requires three single-character wildcards followed by 'n', which is '___n'.

To uniquely identify each enrollment record according to the business rule, you need a combination of all three columns. StudentID alone is not unique (student takes multiple courses). (StudentID, CourseID) is not unique (a student could retake the course in a different semester). The combination of all three (StudentID, CourseID, Semester) ensures that each row is unique.

The REVOKE command is used to take away previously granted privileges. To specifically remove the INSERT privilege while leaving the SELECT privilege intact, you must specify the exact privilege to revoke (INSERT) on the specific object (Employees) from the specific user (junior_dba).

Most SQL aggregate functions, including AVG(), SUM(), and COUNT(column_name), ignore NULL values in their calculation. Therefore, the average will be calculated as (0.10 + 0.20 + 0.10) / 3, which equals 0.40 / 3, or approximately 0.1333...

The primary distinctions are: 1) A table can have only one PRIMARY KEY but multiple UNIQUE constraints. 2) A PRIMARY KEY constraint is a combination of NOT NULL and UNIQUE. A UNIQUE constraint, by itself, allows NULL values (most RDBMS implementations permit a single NULL, as NULL is not equal to NULL).

The standard SQL command to remove a database object like a table, view, or index is DROP. The syntax DROP INDEX <index_name> ON <table_name> is commonly used across many database systems (though some, like MySQL, may use ALTER TABLE ... DROP INDEX). DROP INDEX is the most standard DDL answer.

Atomicity ensures that a transaction is an "all or nothing" operation. If any part of the transaction fails, the entire transaction is rolled back, and the database is left in the state it was in before the transaction started. In this case, since the transaction did not complete, atomicity guarantees the initial withdrawal is reversed.

A foreign key is a column (or set of columns) in one table that refers to the primary key of another table. Its purpose is to enforce referential integrity, ensuring that a relationship between two tables remains consistent. Here, it ensures that a professor can only be assigned to a department that actually exists.

The ORDER BY clause processes columns from left to right. To sort salary from highest to lowest, Salary DESC is used. To then sort by last name alphabetically (ascending) for ties in salary, LastName ASC is added as the second criterion. ASC is the default sort order, so ORDER BY Salary DESC, LastName; would also work.

When an UPDATE statement is executed without a WHERE clause, the operation is applied to all rows in the specified table. In this case, the Price column for all 1,000 product records will be updated to 90% of its original value.

The NOT NULL constraint enforces that a column cannot accept a NULL value. During an INSERT operation, a value must be explicitly provided for this column. It does not enforce uniqueness (that's a UNIQUE or PRIMARY KEY constraint) or mandate its presence in an UPDATE unless you try to set it to NULL.

Privileges are categorized into system and object privileges. Object privileges apply to specific database objects like tables or views (e.g., SELECT ON Employees). System privileges grant the ability to perform broader database-level actions, such as connecting to the database (CREATE SESSION), creating tables (CREATE TABLE), or creating users (CREATE USER).

The DISTINCT keyword inside the COUNT function tells the database to first find the unique values in the Country column and then count them. The unique values are 'USA', 'UK', 'Canada', and 'Mexico'. Therefore, the count of these distinct values is 4.

The SQL standard specifies that a CHECK constraint expression cannot contain subqueries, aggregate functions, window functions, or non-deterministic functions. This is because the constraint must be verifiable by looking only at the values within the row being inserted or updated. Allowing subqueries could lead to significant performance issues and complex transactional anomalies, as checking the constraint for one row would require querying other rows or even entire tables, potentially leading to inconsistencies in a concurrent environment. While some database systems offer workarounds (like using user-defined functions that encapsulate the subquery), the standard definition itself is invalid.

In many major database systems (like Oracle and MySQL), Data Definition Language (DDL) statements such as CREATE TABLE cause an implicit commit of the current transaction before they are executed. Therefore, when the CREATE TABLE command runs at step 3, the preceding UPDATE statement is automatically committed. A new transaction is often started for the DDL statement itself, which is also committed. The subsequent INSERT and ROLLBACK operate on a new, separate transaction. The ROLLBACK at the end only rolls back the INSERT into temp_log, but the table creation and the initial product update are already permanently saved.

A recursive CTE consists of an anchor member and a recursive member. The recursive member executes repeatedly, taking its own output as the input for the next iteration, until it produces no new rows. If a data cycle exists and the logic does not explicitly prevent re-visiting nodes (e.g., by tracking a path), the recursive member will continuously find the same employees in the cycle, producing them as output in each iteration. This creates an infinite loop. Most database systems implement a safeguard, such as a maximum recursion depth (e.g., 100 in SQL Server), and will terminate the query with an error when this limit is reached.

• {A,B}+ = {A,B,C,D} (Does not determine E). Not a superkey.
• {C,E}+ = {C,E} -> {C,D,E} (from C->D) -> {C,D,E,A} (from D->A) -> {C,D,E,A,B} (from E->B and A,B->C). This determines all attributes. Let's check minimality. C+={C,D,A}. E+={E,B}. Neither determines all attributes. So {C,E} is a candidate key.
• {A,E}+ = {A,E} -> {A,E,B} (from E->B) -> {A,E,B,C} (from AB->C) -> {A,E,B,C,D} (from C->D). This determines all attributes. Let's check minimality. A+ = {A}. E+ = {E,B}. Neither determines all attributes. So {A,E} is a candidate key.
• {D,E}+ = {D,E} -> {D,E,A} (from D->A) -> {D,E,A,B} (from E->B) -> {D,E,A,B,C} (from AB->C). This determines all attributes. Let's check minimality. D+ = {D,A}. E+ = {E,B}. Neither determines all attributes. So {D,E} is a candidate key.

All {C, E}, {A, E}, and {D, E} are candidate keys. There must be a subtle error in my analysis or the question setup. Let's re-read the FDs: {AB -> C, C -> D, D -> A, E -> B}. Let's try to derive other keys. (C,E)+ gave (C,D,A,B,E). Correct. (A,E)+ gave (A,B,C,D,E). Correct. (D,E)+ gave (D,A,B,C,E). Correct. The question implies there is only one correct option. Let's re-derive everything.

• Attributes: A, B, C, D, E.
• FDs: AB→C, C→D, D→A, E→B.

Let's test (A,E)+:

1. Start with {A, E}
2. From E→B, we get {A, E, B}
3. From AB→C, we get {A, E, B, C}
4. From C→D, we get {A, E, B, C, D}. All attributes are determined. So it's a superkey.
5. Is it minimal? A+ = {A}. E+ = {E, B}. Yes, it's minimal. So {A,E} is a candidate key.

Let's test (C,E)+:

1. Start with {C, E}
2. From C→D, we get {C, E, D}
3. From D→A, we get {C, E, D, A}
4. From E→B, we get {C, E, D, A, B}. All attributes are determined. So it's a superkey.
5. Is it minimal? C+ = {C, D, A}. E+ = {E, B}. Yes, it's minimal. So {C,E} is a candidate key.

Let's test (D,E)+:

1. Start with {D, E}
2. From D→A, we get {D, E, A}
3. From E→B, we get {D, E, A, B}
4. From AB→C, we get {D, E, A, B, C}. All attributes are determined. So it's a superkey.
5. Is it minimal? D+ = {D, A}. E+ = {E, B}. Yes, it's minimal. So {D,E} is a candidate key.

This is a classic 'group-wise maximum' problem.

• Option A fails if there's no second-highest salary (i.e., all employees in a department have the same salary), in which case it incorrectly returns no row for that department. It also returns the third-highest if the top two salaries are identical.
• Option B, using the DENSE_RANK() window function, is the most robust and modern approach. It correctly handles ties (e.g., if two employees share the second-highest salary, it returns both) and correctly partitions the ranking by department. Using DENSE_RANK over RANK ensures that if the highest salary is tied, the next rank is 2, not 3.
• Option C uses a correlated subquery to count how many distinct salaries are higher. This works correctly and is clever, but it is generally less performant than window functions for large datasets because the subquery must be executed for each row.
• Option D is incorrect because GROUP BY department collapses all rows in a department into one, and the choice of salary becomes arbitrary or non-deterministic in most SQL dialects without an aggregate function. LIMIT/OFFSET works on the entire result set, not per group.

The referential actions are executed sequentially.

1. Deleting aid=1 from table A triggers the ON DELETE CASCADE rule for table B.
2. This causes the corresponding row bid=10 in table B to be deleted.
3. The deletion of the row bid=10 from table B then triggers the ON DELETE SET NULL rule for table C.
4. Consequently, the foreign key column bid in the referencing row (cid=100) in table C is updated to NULL. The row in C itself is not deleted.

When privileges are revoked, the behavior depends on the CASCADE or RESTRICT keyword (where RESTRICT is often the default). If REVOKE ... RESTRICT were used, the command would fail because UserA has granted the privilege to UserB. However, the CASCADE keyword explicitly tells the database to revoke the privilege not only from the specified user (UserA) but also from any other users to whom that user has granted the privilege. This is known as a cascading revoke, and it traverses the entire chain of grants originating from the privilege being revoked.

In standard SQL, NULL is a special marker for missing or unknown data, and the result of any comparison involving NULL (e.g., NULL = NULL) is unknown, not true. Because a UNIQUE constraint requires all values to be distinct, and NULL is not considered equal to another NULL, the standard interpretation is that a UNIQUE constraint should allow multiple NULL values. Most RDBMS like PostgreSQL, Oracle, and MySQL (InnoDB) follow this interpretation. However, Microsoft SQL Server deviates from this standard behavior; for the purpose of a UNIQUE or INDEX constraint, it treats all NULL values as a single group, thus allowing only one row to have a NULL in the constrained column.

A 'Phantom Read' occurs when a transaction re-runs a query and finds new rows that have been inserted (or deleted) by another committed transaction since the first read.

• REPEATABLE READ prevents non-repeatable reads (updates to the same row) by holding locks on the specific rows it has read, but it doesn't prevent new rows from being inserted into the range of the query.
• SERIALIZABLE is the highest isolation level and is designed to prevent all concurrency anomalies, including phantom reads. It achieves this by taking broader locks, not just on the rows that exist but on the conditions of the query. This is often implemented as 'predicate locking' or 'range locking', which prevents other transactions from inserting new rows that would match the WHERE clause of the query in the first transaction.

GROUP BY CUBE (a, b) generates groupings for all possible combinations of the columns in the cube list. For (department, job_title), this includes:

1. Groupings by (department, job_title): 3 * 5 = 15 rows.
2. Groupings by (department) alone (with job_title as NULL): 3 rows.
3. Groupings by (job_title) alone (with department as NULL): 5 rows.
4. A grand total grouping (with both department and job_title as NULL): 1 row.

The total number of rows is the sum of these: 15 + 3 + 5 + 1 = 24 rows. This represents the power set of the dimensions being cubed, with a total of grouping sets where is the number of columns in the CUBE.

This is a tricky point because behavior varies. However, the most broadly 'false' statement relates to its transactional nature.

• In Oracle and MySQL, TRUNCATE is a DDL command that cannot be rolled back and causes an implicit commit.
• In PostgreSQL and SQL Server, TRUNCATE is transaction-safe and can be rolled back if it's part of a transaction block (BEGIN...COMMIT/ROLLBACK).
  Given the significant difference, and that major systems like Oracle do not support rollback, stating it can be rolled back is a generalization that is false for some of the biggest players. The other statements are almost universally true:
• It is a DDL command (true).
• It bypasses row-level processing, so ON DELETE triggers do not fire (true).
• It typically resets the high-water mark and identity columns (true).

The MATCH FULL rule imposes a strict condition on composite foreign keys: either all components of the foreign key must be NULL, or all components must be non-NULL and together must match an existing primary/unique key in the referenced table.

• Options A and B are invalid because they have a mix of NULL and non-NULL values (partially null), which violates MATCH FULL.
• Option D is only valid if the row (10, 20) exists in table A, which is a condition not guaranteed by the question.
• Option C, where all components of the foreign key are NULL, is always permitted under MATCH FULL, as it represents a case where the reference does not point to any specific parent row.

The MERGE statement is designed to synchronize a target table with a source table. The WHEN NOT MATCHED BY SOURCE clause handles rows that exist in the target table but do not exist in the source table. Adding THEN DELETE to this clause instructs the database to remove these 'orphaned' rows from the target table, effectively making the target table's content a subset of the source's content with respect to the join keys. This is a common pattern in data warehousing and ETL processes to handle deletions from the source system.

This problem requires calculating a ratio against a grand total without collapsing the rows. This is a perfect use case for window functions.

• Option A is incorrect because GROUP BY emp_name, salary would make SUM(salary) operate on individual rows, which is just the salary itself, leading to a division by self (or an error depending on the SQL dialect).
• Option B is the correct and most efficient solution. SUM(salary) OVER () calculates the sum of all salaries in the entire set defined by the query (the empty OVER() clause means the whole set) and makes this total available to each row without aggregation. The calculation (salary * 100.0) / total can then be performed for each row. The 100.0 ensures floating-point division.
• Option C works correctly but is less efficient. It uses a scalar subquery that will likely be executed once, but the window function approach is generally more idiomatic and can be optimized better by the query planner in more complex scenarios.
• Option D is syntactically incorrect; you cannot nest aggregate functions like SUM() inside a window function call like AVG(...) OVER().

The SQL standard allows a foreign key to reference any column or set of columns that has a PRIMARY KEY or a UNIQUE constraint. This ensures that the value being referenced is, in fact, unique in the parent table. Therefore, referencing PROJECTS.lead_dept_id is possible. However, there's a critical caveat: a standard foreign key constraint does not allow NULL matches by default (unless MATCH PARTIAL is used, which is rare). The target of a foreign key, the UNIQUE key, can often contain NULLs (in most systems). If the lead_dept_id column in PROJECTS allows NULLs, some databases might reject the foreign key creation or exhibit complex behavior. To be a reliable and unambiguous target for a standard foreign key, the unique key should also be NOT NULL.

While the intent is clear, the syntax has several issues in different SQL dialects, but the most fundamental and widely applicable error is that the LIMIT (or TOP or FETCH FIRST) clause requires a literal integer, not an expression or a subquery. The database planner needs to know the number of rows to fetch at parse time. The correct way to implement this logic would be using window functions like NTILE(10) or CUME_DIST() to calculate percentiles and then updating based on the result of the window function in a subquery or CTE.

Let's trace the state of the transaction:

1. START TRANSACTION; - Transaction begins.
2. INSERT INTO T VALUES (1); - T contains {1}.
3. SAVEPOINT s1; - A marker is set. State: {1}.
4. INSERT INTO T VALUES (2); - T contains {1, 2}.
5. SAVEPOINT s2; - Another marker is set. State: {1, 2}.
6. DELETE FROM T; - T is now empty {}.
7. ROLLBACK TO SAVEPOINT s1; - This is the key step. It undoes all changes made after s1. This means the DELETE and the INSERT of 2 are undone. The state of T reverts to what it was at the moment s1 was created. T now contains {1}. The savepoint s2 is discarded.
8. INSERT INTO T VALUES (3); - T now contains {1, 3}.
9. COMMIT; - The final state {1, 3} is made permanent.

DEFERRABLE constraints are a powerful feature for complex data modifications.

• DEFERRABLE means the constraint's checking can be postponed.
• INITIALLY DEFERRED means that for any new transaction, the constraint will automatically be in a deferred state.
  This combination allows a user to, for example, insert a row where start_date is greater than end_date, and then in a subsequent statement within the same transaction, update the row to make it valid. The constraint is only validated as a final step right before the COMMIT command is executed. If the constraint is still violated at commit time, the transaction will be rolled back. This is useful for resolving chicken-and-egg problems or for multi-statement updates that are only consistent as a whole.

This is the fundamental difference between the two functions. For example, given the scores 100, 90, 90, 80:

• RANK() would assign ranks: 1, 2, 2, 4. The two scores of 90 both get rank 2. Since two rows occupy rank 2, the next rank is 4 (the count of preceding rows + 1). A gap is left at rank 3.
• DENSE_RANK() would assign ranks: 1, 2, 2, 3. The two scores of 90 both get rank 2. The next distinct value gets the very next rank in the sequence, which is 3. No gap is left. DENSE_RANK always produces a consecutive sequence of ranks.

First, let's find the candidate key(s). We check the attribute closures:

• A+ = {A, B, C, D}. Since A determines all other attributes, {A} is a candidate key. As it is a single attribute, it is minimal.

Now, let's analyze the options:
A superkey is any set of attributes that contains a candidate key.
A candidate key is a minimal superkey.

• {A}: This is the candidate key itself. So it's not a superkey that isn't a candidate key.
• {A, B}: The closure (A, B)+ = {A, B, C, D}. It is a superkey because it determines all attributes. However, it contains the candidate key {A} as a proper subset. Therefore, {A, B} is not minimal and is a superkey but not a candidate key.
• {B, D}: The closure (B, D)+ = {B, C, D}. It does not determine A, so it is not a superkey.
• {D}: The closure D+ = {D}. It is not a superkey.