Advanced SQL Quiz

Edgar F. Codd’s Rule 0, also known as the foundational rule, emphasizes the fundamental requirement for an RDBMS to manage data using its relational capabilities. This means that the RDBMS should provide comprehensive functionality based on the relational model, allowing users to define, manipulate, and query data using relational concepts such as tables, rows, and columns. It highlights the importance of the RDBMS being able to handle all aspects of data management within the realm of relational capabilities.

https://en.wikipedia.org/wiki/Codd%27s_12_rules

Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE, and DELETE, are used to manipulate and modify data within database tables. These statements allow users to retrieve specific data based on conditions, insert new records, update existing records, or delete unwanted data.

https://en.wikipedia.org/wiki/Data_manipulation_language

Here is the output of each of the SELECT statements.

1.  WHERE NULL IS NULL is True, so 1 is included in the CTE.
2.  WHERE NULL IS NOT NULL is False, so 2 is not included.
3.  WHERE NULL IS DISTINCT FROM NULL is False, so 3 is not included.
4.  WHERE NULL IS NOT DISTINCT FROM NULL is True, so 4 is included.

The CTE includes the values 1 and 4, therefore the answer is 5.

https://en.wikipedia.org/wiki/Null_(SQL)

This SQL statement does not represent a self-join. Although the Employees table is used twice in this example, the first use of the table in the CTE creates an entirely new relation. Joining to this new relation is not considered a self-join.

Both inner and outer joins are considered restricted Cartesian products, where the join criteria determines the restriction. In this SQL statement, the CROSS JOIN along with the predicate logic create a restricted Cartesian product that has the same behavior as an INNER JOIN.

https://en.wikipedia.org/wiki/Join_(SQL)#Self-join

Named Parameters are passed by associating the parameter name in the procedure with the value in the calling program.

Positional Parameters are passed by the order (position) they are written in the procedure definition.

Mixed Parameters are passed using a combination of named and positional parameters.

If a value for the parameter is not provided when the procedure or function is called, the default value will be used.

Please note that the specific ways parameters can be passed to stored procedures or functions can vary based on the specific SQL dialect or other programming language being used.

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/parameters?view=sql-server-ver16

In SQL, when passing arguments to a stored procedure or function, it is not possible to directly pass a table name as an argument. The reason for this limitation is that table names in SQL are considered to be schema objects and are used to define the structure of a table.

If you need a table structure to be passed to a stored procedure, a table variable can be used.

https://en.wikipedia.org/wiki/Stored_procedure

The candidate keys that best serve as the primary key for this relation are ID and Item Number. These attributes, when combined, uniquely identify each tuple or row in the relation. The combination of ID and Item Number forms a composite key that satisfies the properties of uniqueness and minimalism for the relation.

https://en.wikipedia.org/wiki/Primary_key

When fetching data using an explicit cursor, the values of the INTO clause can be either scalar values or record types. Scalar values are single data items, such as a number or string, while record types are composite data structures that can hold multiple related values of different types.

https://en.wikipedia.org/wiki/Cursor_(databases)

Optimistic concurrency control, also known as optimistic locking, is a concurrency control method applied to transactional systems such as relational database management systems and software transactional memory.

Optimistic concurrency control assumes that multiple transactions can frequently complete without interfering with each other.

https://en.wikipedia.org/wiki/Concurrency_control

This relation has two candidate keys, ID and Activity

There is a transitive dependency on ID → Activity → Fee. This transitive dependency violates the rules of third normal form, therefore the highest normal form is second normal form.

https://en.wikipedia.org/wiki/Second_normal_form

While Edgar F. Codd’s work focused on the development of the relational model and the foundation of modern relational database management systems, ACID transactions were not part of his contributions.

ACID transactions were defined by Jim Gray and Andreas Reuter in the 1980s as a set of properties that guarantee the reliability and consistency of database transactions. Although ACID transactions are crucial for ensuring data integrity, they were developed independently of Edgar F. Codd’s work and are not directly attributed to him.

https://en.wikipedia.org/wiki/Edgar_F._Codd

Savepoints allow defining intermediate points within a transaction where the state of the transaction can be saved. This enables the possibility of rolling back to a specific savepoint instead of rolling back the entire transaction.

Savepoints provide the ability to roll back only specific parts of a transaction while keeping the rest intact. If an error occurs, the transaction can be rolled back to a specific savepoint, undoing the changes made after that savepoint.

https://www.ibm.com/docs/en/i/7.2?topic=integrity-savepoints

In a relational database, a functional dependency describes a relationship between two attributes, where the value of one attribute (known as the determinant) uniquely determines the value of another attribute (known as the dependent attribute).

Functional dependencies play a crucial role in database normalization, where they help eliminate redundancy and ensure data integrity by identifying and organizing relationships between attributes.

https://en.wikipedia.org/wiki/Functional_dependency

The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver16

Tables in a database are schema-bound structures that hold data and can be modified through various operations, making them mutable. Tables serve as the fundamental building blocks of a database, providing a structured way to organize and store data. They can be created, modified, and updated to accommodate changes in data over time. Tables define the structure, columns, and data types of the stored data, allowing for efficient storage, retrieval, and manipulation of information.

https://en.wikipedia.org/wiki/Table_(database)

Relational division is a Relational Algebra operation that represents the division of one relation into another relation based on certain conditions. In SQL, relational division can be achieved by using multiple join operations and conditional statements to divide the data in one table into multiple groups based on certain criteria. This can be useful for solving problems such as finding all employees who have had a shift in every department, or employees that have the same licenses or skillsets.

The example of “Airplanes in the Hangar” is frequently cited online as shown in the following link.

Divided We Stand: The SQL of Relational Division

Rebuilding and reorganizing are two common techniques used for index maintenance in database systems.

Index rebuild involves dropping an existing index and recreating it from scratch. During the rebuild process, all index pages are rebuilt, resulting in a fresh and optimized index structure. This operation is typically more resource-intensive and time-consuming compared to reorganizing.

Index reorganization is a lighter-weight operation that aims to optimize the existing index without recreating it entirely. It rearranges the physical order of the index pages, reducing fragmentation and improving query performance. Reorganization is generally a quicker operation as it only involves moving and restructuring existing index pages rather than rebuilding the entire index.

The choice between rebuilding and reorganizing an index depends on factors such as the level of fragmentation, database size, available resources, and maintenance window. Rebuilding is typically recommended when there is significant fragmentation or when other changes to the index structure are required. Reorganizing is more suitable for scenarios where there is moderate fragmentation and a need for quick optimization without the overhead of a full rebuild.

Both rebuilding and reorganizing indexes are essential maintenance tasks to ensure optimal performance in database systems, and the choice between them should be based on careful consideration of the specific database environment and requirements.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver16

Database collation refers to a set of rules that determine how data is sorted and compared in a database. These rules encompass factors such as character classification, case sensitivity, accent marks, and character width. Depending on the collation setting, the sort order of text strings can vary significantly.

For example, under some collations, ‘a’ and ‘A’ are considered equal, while under others, they are not. Collation settings can also dictate how accents are treated, for instance, whether ‘é’ and ‘e’ are the same or different. Additionally, collation determines the sorting order for special characters like ‘@’, ‘#’, and so on. The choice of collation has broad implications, affecting everything from query performance to the correct functioning of search operations, thus it should be set with care during database design and configuration.

https://en.wikipedia.org/wiki/Collation

PERCENTILE_CONT() is not a ranking function. Is is considered an analytical function along with CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC

https://learn.microsoft.com/en-us/sql/t-sql/functions/ranking-functions-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/t-sql/functions/analytic-functions-transact-sql?view=sql-server-ver16

Denormalization in a data warehouse involves combining and consolidating data from various sources into a unified and optimized structure for efficient querying and analysis. This denormalized structure allows for easier and faster data retrieval and supports complex analytical queries across multiple dimensions.

https://en.wikipedia.org/wiki/Data_warehouse