Advanced SQL Quiz

A junk dimension in the context of a data warehouse is a dimension table that consolidates low-cardinality flags and indicators. Low-cardinality attributes are those with a small number of distinct values, such as binary flags or indicator fields. Rather than creating separate dimension tables for each of these attributes, a junk dimension combines them into a single table.

This approach helps to reduce the number of dimension tables in the data warehouse and simplifies the data model.

https://en.wikipedia.org/wiki/Dimension_(data_warehouse)

The RETURN statement is required to return a value in functions, as they are designed to calculate and return a specific result. However, in stored procedures, the RETURN statement is optional and can be used to exit the procedure early without returning a value.

In stored procedures, you can use the RETURN statement without specifying a value, or you can omit it altogether. The primary purpose of a stored procedure is to execute a set of actions or perform operations, rather than calculating and returning a specific result like a function does.

https://en.wikipedia.org/wiki/User-defined_function
https://en.wikipedia.org/wiki/Stored_procedure

Procedure extensions refer to additional functionality or features that can be added to the basic procedural language of a database system. These extensions provide enhanced capabilities for defining and executing stored procedures, which are sets of SQL statements grouped together and stored in the database for reuse.

Common procedure extensions include T-SQL (Transact-SQL) for Microsoft SQL Server, PL/SQL (Procedural Language/Structured Query Language) for Oracle Database, SQL/PSM (SQL/Persistent Stored Modules) for MySQL, and PL/pgSQL (Procedural Language/PostgreSQL) for PostgreSQL.

These extensions offer procedural programming constructs, control flow statements, exception handling mechanisms, and additional SQL features, enabling the creation of more complex and powerful stored procedures.

https://en.wikipedia.org/wiki/SQL#Procedural_extensions

Views are a mechanism that allow database users to have controlled access to the database by utilizing virtual tables. Views are saved queries that act as virtual tables presenting subsets of data or derived data from one or more underlying tables or views. By defining views, database administrators can control and restrict the data that users can access, providing an additional layer of security and abstraction. Users interact with the views as if they were regular tables, allowing them to query and manipulate the data without directly accessing the underlying tables.

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

A non-clustered index creates a separate data structure from the actual data. It contains a copy of the indexed columns along with a pointer to the corresponding data row, typically in the form of a row identifier. Unlike a clustered index, a non-clustered index does not affect the physical order of data rows in a table.

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

The ACID model is a set of principles that are vital to the design of reliable databases, ensuring the accuracy of transactions and facilitating recovery from failures. ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability.

Atomicity guarantees that a database transaction is treated as a single, indivisible unit, meaning that either all its changes are committed, or none are.

Consistency ensures that a transaction brings a database from one valid state to another, preserving its integrity.

Isolation provides that transactions executed concurrently appear as though they are executed serially, preventing interference.

Durability guarantees that once a transaction is committed, its changes are permanent and can survive any subsequent failures.

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

Unfortunately, understanding and troubleshooting poorly written and complex SQL statements are a common occurrence for SQL developers.

In this particular statement, the outcome is option A.

https://www.w3resource.com/sql/subqueries/nested-subqueries.php

Tables, views, and table-valued functions are all schema-bound objects. Most database vendors support the following schema bound objects: tables, views, sequences, synonyms, indexes, procedures, functions, and packages.

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

The following are examples of weak entities.

Address: In a customer database, an address entity represents the specific address associated with a customer. The address entity is considered weak because it depends on the customer entity for its existence and meaning. Without a customer, an address lacks context and significance.

Comment: In a blog or social media system, a comment entity represents a user’s comment on a post or article. The comment entity is weak as it relies on the post or article entity for its relevance and association. A comment cannot stand alone and needs a post or article to be meaningful.

Ticket: In a support ticketing system, a ticket entity represents a user’s request or issue. The ticket entity is weak because it depends on the user or customer entity for its purpose and identity. A ticket only holds meaning within the context of the user who raised it.

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

This relation does not meet second normal form because the Manufacturer Country is only partially dependent on the primary key.

In this relation, the primary key is {Manufacturer, Model}. The attribute Manufacturer Country has a partial dependency on the primary key since it is functionally dependent on Model rather than the entire primary key of {Manufacturer, Model}.

This example is taken from the Wikipedia page on second normal form.

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

Procedure extensions refer to additional functionality or features that can be added to the basic procedural language of a database system. These extensions provide enhanced capabilities for defining and executing stored procedures, which are sets of SQL statements grouped together and stored in the database for reuse.

Common procedure extensions include T-SQL (Transact-SQL) for Microsoft SQL Server, PL/SQL (Procedural Language/Structured Query Language) for Oracle Database, SQL/PSM (SQL/Persistent Stored Modules) for MySQL, and PL/pgSQL (Procedural Language/PostgreSQL) for PostgreSQL.

These extensions offer procedural programming constructs, control flow statements, exception handling mechanisms, and additional SQL features, enabling the creation of more complex and powerful stored procedures.

https://en.wikipedia.org/wiki/SQL#Procedural_extensions

Often these types of problems are best worked out on paper where you can graph the relationships.

Consider the following deductions that we can make from the given functional dependencies.  If we know {E, F} we know {G, I, J} and if we know {E, H} we know {K, L. M, N}. Therefore, the primary key is {E, F, H}.

If we collectively know the values of these attributes, we can determine all other attributes in the relation.

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

A snowflake schema in a data warehouse is characterized by normalized dimension tables with additional levels of normalization. Unlike the star schema, where dimension tables are denormalized, the snowflake schema further normalizes the dimension tables by breaking down hierarchies into multiple related tables.

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

All of these are natural keys.

Natural keys refer to unique identifiers that exist inherently within the data being stored. Unlike surrogate keys, which are artificially created and assigned to records, natural keys are derived from meaningful attributes or properties of the data itself. For example, in a database of employees, the social security number can serve as a natural key, as it is a unique identifier already associated with each individual. Natural keys provide the advantage of being familiar and intuitive, as they directly correspond to real-world attributes. They can simplify data integration and maintain referential integrity by leveraging existing unique identifiers. However, there are potential drawbacks to using natural keys, such as the possibility of changes to the attributes they are based on, the potential for longer key lengths, and the increased complexity of dealing with composite natural keys. Careful consideration should be given to choosing appropriate natural keys based on stability, uniqueness, and consistency to ensure the integrity and efficiency of database operations.

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

See the following website for a good article on the advantages vs disadvantages of relational databases.

https://www.techtarget.com/searchdatamanagement/definition/relational-database

These modes define how parameters are passed to and from a stored procedure. IN mode is used for input parameters. OUT mode is used for output parameters.

Note, some databases such as MySSQL support an INOUT parameter as well.

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

When executing a SELECT statement, the columns specified in the SELECT clause represent the projection of the query.

https://en.wikipedia.org/wiki/Projection_(relational_algebra)

Data Control Language (DCL) is used to control access to a database or its data. DCL statements allow administrators or authorized users to grant or revoke privileges, manage user permissions, and define security constraints to restrict access to specific data or operations within the database.

Examples of DCL statements include GRANT, REVOKE, and DENY.

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

These statements are categorized as Data Definition Language (DDL) statements because they are used to define or modify the structure and organization of database objects, such as tables, indexes, schemas, and views.

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

The ACID model is a set of principles that are vital to the design of reliable databases, ensuring the accuracy of transactions and facilitating recovery from failures. ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability.

Atomicity guarantees that a database transaction is treated as a single, indivisible unit, meaning that either all its changes are committed, or none are.

Consistency ensures that a transaction brings a database from one valid state to another, preserving its integrity.

Isolation provides that transactions executed concurrently appear as though they are executed serially, preventing interference.

Durability guarantees that once a transaction is committed, its changes are permanent and can survive any subsequent failures.

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