Advanced SQL Quiz

Triggers are often used for logging, performing business rules, deriving additional data, and enforcing referential integrity.

https://www.ibm.com/docs/en/informix-servers/14.10?topic=triggers-when-use

The AVG() function in SQL calculates the average of a set of values. If there are NULL markers in the column, AVG() ignores them.

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

The functions MAX, MIN, and COUNT can be used with any data type. They are not dependent on the specific data type and can operate on various types such as numeric, string, date, and others.

Function overloading is a programming concept that allows multiple functions of the same name to be defined, but with different types or numbers of parameters.

https://www.postgresql.org/docs/current/xfunc-overload.html#:~:text=More%20than%20one%20function%20can,function%20names%20can%20be%20overloaded.

The most important service provided by a database management system is typically the support for a data manipulation language (DML) such as SQL. A database management system allows users to interact with the database by executing queries, retrieving data, modifying data, and performing various operations using a DML.

SQL, as the standard language for relational databases, enables users to manage and manipulate data efficiently and effectively. It provides a standardized way to interact with the database and perform tasks such as querying, updating, inserting, and deleting data.

https://en.wikipedia.org/wiki/Relational_database#RDBMS

In the context of database systems, a tuple refers to a single row or record in a relation. It represents a collection of related data points or values that are grouped together to form a complete set of information. A tuple consists of a fixed number of attributes or fields, each corresponding to a specific column or attribute of the relation.

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

The ORDER BY clause can be applied in a subquery in conjunction with the TOP/LIMIT, OFFSET, or FOR XML clause.

The ORDER BY clause cannot be used outside of this scenario as the purpose of a subquery is to retrieve data to be used in the main query, and the subquery itself does not affect the ordering of the final result set.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver16

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)

A functional dependency X → Y means that the values of X determine the values of Y. In this case, the values of B determine the values of B, so B → B is a functional dependency.

A dependency X → Y is trivial if Y is a subset of X. Here, B is a subset of B, so B → B is a trivial dependency.

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

A data dictionary is a centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format, essentially providing a formal description of the database structure and its contents. It is a crucial component for managing, controlling, and auditing the use of data, making it a fundamental part of the database management system.

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

The minimum set of requirements for a database to be considered relational includes presenting the data to the user as relations (in tabular form) and providing relational operators to manipulate the data. These two requirements form the fundamental principles of the relational model.

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

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

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)

In database management systems, an extent is a specific term referring to a contiguous block of data pages or blocks. The purpose of an extent is to provide an efficient way to allocate and manage the storage space in a database. When a table or an index needs space to store data, rather than allocating one page or block at a time, the system allocates an extent, which contains multiple pages or blocks. This strategy significantly reduces the system overhead associated with data storage and management.

The size and nature of extents can vary based on the specific database system in use. The use of extents helps ensure effective space utilization and improve overall system performance.

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

To retrieve a data value from a relational database, you need the attribute, relation name and the primary key.

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

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

The most important service provided by a database management system is typically the support for a data manipulation language (DML) such as SQL. A database management system allows users to interact with the database by executing queries, retrieving data, modifying data, and performing various operations using a DML.

SQL, as the standard language for relational databases, enables users to manage and manipulate data efficiently and effectively. It provides a standardized way to interact with the database and perform tasks such as querying, updating, inserting, and deleting data.

https://en.wikipedia.org/wiki/Relational_database#RDBMS

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

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

SQL is a declarative programming language. Being declarative means that SQL focuses on specifying the desired outcome or result rather than providing explicit step-by-step instructions for how to achieve it. In SQL, programmers describe the data they want to retrieve or modify, and the database management system takes care of the underlying implementation details.

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

Data Definition Language (DDL) is used to create, and name database entities.

CREATE, DROP, ALTER and TRUNCATE are all DDL commands.

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