Advanced SQL Quiz - Advanced SQL Puzzles

Hello everyone. Step right up and take the SQL Quiz challenge!

I have a blog post here about this quiz if you want to hear my ramblings on it.

Few things.

If you are on a PC, click the icon in the upper right to make it full-screen.
Let me know if you find any bugs, misspellings, etc…
I’ve tried to make this as ANSI-compliant as possible; however, I am partial to using Microsoft SQL Server in this exam.
I have several hundred questions loaded into the quiz maker. Refresh your cache if you keep getting the same question set.

Take the SQL challenge!

In this quiz, you’ll encounter 10 questions selected at random.

1 / 10

Which of the following SQL commands does not serve the purpose of data aggregation?

DISTINCT

SUM

COUNT

MIN

id200

In SQL, functions are used to perform operations on data, including aggregating and grouping data. SUM, COUNT, and MIN are examples of functions commonly used for grouping data.

DISTINCT is not a function but an operator in SQL. It is used to eliminate duplicate rows from the result set of a query and is not directly related to aggregating data.

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

In SQL, functions are used to perform operations on data, including aggregating and grouping data. SUM, COUNT, and MIN are examples of functions commonly used for grouping data.

DISTINCT is not a function but an operator in SQL. It is used to eliminate duplicate rows from the result set of a query and is not directly related to aggregating data.

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

In SQL, functions are used to perform operations on data, including aggregating and grouping data. SUM, COUNT, and MIN are examples of functions commonly used for grouping data.

DISTINCT is not a function but an operator in SQL. It is used to eliminate duplicate rows from the result set of a query and is not directly related to aggregating data.

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

2 / 10

Identify the index operation that matches the given descriptions.

1. This database operation involves dropping an existing index and creating it anew, resulting in all index pages being updated and improved.

2. This database operation rearranges the existing index pages without recreating the entire index and is best suited when there is only a moderate level of fragmentation.

Rebuild, Reorganize

Reorganize, Rebuild

Refresh, Reorganize

Rebuild, Refresh

id119

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

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

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

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

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

3 / 10

Given the following MySQL stored procedure which executes successfully, what is the output of the SELECT statement?

10, 2, 3

1, 2, 3

10, 20, 30

1, 20, 30

id170

The SET command changes the values of the output parameters p2 and p3 but does not change the value of the input parameter p1. When the stored procedure finishes executing, the new values of p2 and p3 are returned and replace the old values of @p2 and @p3, while the value of @p1 remains the same as it was before the procedure was called. Hence, the result of the SELECT @p1, @p2, @p3 statement is @p1 = 10, @p2 = 2, @p3 = 3.

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

4 / 10

Consider the following relation which contains product information.

Which candidate key best serves as the primary key for this relation?

Start Date and End Date

Item Number and End Date

ID and Item Number

id67

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

5 / 10

Which of the following normal forms are concerned with functional dependencies?

Select all that apply.

First Normal Form

Second Normal Form

Third Normal Form

Boyce-Codd Normal Form

id96

Second, Third, and Boyce-Codd are concerned with functional dependencies.

First normal form focuses on ensuring that each column in a relational database table contains indivisible or atomic values.

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

Second, Third, and Boyce-Codd are concerned with functional dependencies.

First normal form focuses on ensuring that each column in a relational database table contains indivisible or atomic values.

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

Second, Third, and Boyce-Codd are concerned with functional dependencies.

First normal form focuses on ensuring that each column in a relational database table contains indivisible or atomic values.

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

6 / 10

True or False?

The following DDL statements will execute without error.

True

False

id136

The provided create table statements are correct and do not contain any errors.

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

The provided create table statements are correct and do not contain any errors.

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

The provided create table statements are correct and do not contain any errors.

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

7 / 10

What is a junk dimension in the context of a data warehouse?

A dimension table that contains a large amount of unnecessary or irrelevant data.

A dimension table that consolidates low-cardinality flags and indicators.

A dimension table that stores outdated or deprecated data.

A dimension table that represents complex hierarchical relationships.

id52

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)

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)

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)

8 / 10

True or False?

The following two SQL statements treat NULL markers identically.

True

False

id101

The COUNT(SalesType) function counts the number of non-NULL markers in the SalesType column. It will not include NULL markers.

The COUNT(*) function counts all rows, regardless of any NULL markers. It will include NULL markers.

https://www.oracletutorial.com/oracle-aggregate-functions/oracle-count/

The COUNT(SalesType) function counts the number of non-NULL markers in the SalesType column. It will not include NULL markers.

The COUNT(*) function counts all rows, regardless of any NULL markers. It will include NULL markers.

https://www.oracletutorial.com/oracle-aggregate-functions/oracle-count/

The COUNT(SalesType) function counts the number of non-NULL markers in the SalesType column. It will not include NULL markers.

The COUNT(*) function counts all rows, regardless of any NULL markers. It will include NULL markers.

https://www.oracletutorial.com/oracle-aggregate-functions/oracle-count/

9 / 10

True or False?

The minimum set of requirements for a database to be considered relational are the following.

Presents the data to the user as relations
Provide relational operators to manipulate the data in tabular form.

True

False

id156

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

10 / 10

Consider the following two relations, Employees and Departments.

What is the output of the following SQL statement?

Select from the following options.

id202

In this example, the filter in the WHERE clause forces the OUTER JOIN to become an INNER JOIN.

https://www.pluralsight.com/guides/using-on-versus-where-clauses-to-combine-and-filter-data-in-postgresql-joins

In this example, the filter in the WHERE clause forces the OUTER JOIN to become an INNER JOIN.

https://www.pluralsight.com/guides/using-on-versus-where-clauses-to-combine-and-filter-data-in-postgresql-joins

In this example, the filter in the WHERE clause forces the OUTER JOIN to become an INNER JOIN.

https://www.pluralsight.com/guides/using-on-versus-where-clauses-to-combine-and-filter-data-in-postgresql-joins

Your score is

Take the SQL challenge!

In this quiz, you’ll come across 10 randomly selected questions that don’t contain images.

If you’re using a smartphone, try this quiz if you find the images too hard to read on a smaller screen.

1 / 10

Database design involves a series of steps to create a well-structured and efficient database. What is the sequence of modeling steps involved in the database design process?

Conceptual → Physical → Logical

Logical → Conceptual → Physical

Physical → Logical → Conceptual

Conceptual → Logical → Physical

id25

The correct sequence for the process of database design begins with the conceptual phase, where the high-level, abstract design of the database is created, outlining what data will be stored and the relationships among the data.

This is followed by the logical phase, where the conceptual schema is transformed into a logical data model and constraints are defined.

Finally, the physical design phase involves the actual implementation of the database on a physical medium, considering the specifics of the storage system and optimizing for performance.

https://www.linkedin.com/pulse/4-phases-create-database-design-martin-tolovski/

This is followed by the logical phase, where the conceptual schema is transformed into a logical data model and constraints are defined.

Finally, the physical design phase involves the actual implementation of the database on a physical medium, considering the specifics of the storage system and optimizing for performance.

https://www.linkedin.com/pulse/4-phases-create-database-design-martin-tolovski/

This is followed by the logical phase, where the conceptual schema is transformed into a logical data model and constraints are defined.

Finally, the physical design phase involves the actual implementation of the database on a physical medium, considering the specifics of the storage system and optimizing for performance.

https://www.linkedin.com/pulse/4-phases-create-database-design-martin-tolovski/

2 / 10

What is a junk dimension in the context of a data warehouse?

A dimension table that contains a large amount of unnecessary or irrelevant data.

A dimension table that consolidates low-cardinality flags and indicators.

A dimension table that stores outdated or deprecated data.

A dimension table that represents complex hierarchical relationships.

id52

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)

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)

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)

3 / 10

Which SQL statement is used to define a named group of related tables, views, domains and other database objects?

CREATE ENTITY

CREATE INDEX

CREATE SCHEMA

CREATE DOMAIN

id187

A schema in a database represents a logical container that holds a collection of database objects. It provides a way to organize and group related database objects together, facilitating better management, security, and organization of the database structure.

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

4 / 10

Which of the following SQL data types supports ternary logic?

INTEGER

FLOAT

BOOLEAN

DATE

id184

The BIT data type can support 1,0 and NULL.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver16

Note, using the BIT data type is considered a bad practice by some database developers. See the following article to learn more.

BIT of a Problem

The BIT data type can support 1,0 and NULL.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver16

Note, using the BIT data type is considered a bad practice by some database developers. See the following article to learn more.

BIT of a Problem

The BIT data type can support 1,0 and NULL.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver16

Note, using the BIT data type is considered a bad practice by some database developers. See the following article to learn more.

BIT of a Problem

5 / 10

Which process is used to prevent the current database operation from reading or writing a data item while that data item is being accessed by another operation?

Table Lock

Deadlock

Timestamp

Batch

id20

A table lock is a type of lock that restricts access to the entire table, ensuring that only one transaction or operation can read or modify the table at a time.

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

A table lock is a type of lock that restricts access to the entire table, ensuring that only one transaction or operation can read or modify the table at a time.

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

A table lock is a type of lock that restricts access to the entire table, ensuring that only one transaction or operation can read or modify the table at a time.

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

6 / 10

True or False?

When modifying a stored procedure or a function, the code must be re-executed to validate and store it in the database.

True

False

id180

When modifying procedure code, such as stored procedures or functions, the code does not need to be re-executed to validate and store it in the database.

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

When modifying procedure code, such as stored procedures or functions, the code does not need to be re-executed to validate and store it in the database.

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

When modifying procedure code, such as stored procedures or functions, the code does not need to be re-executed to validate and store it in the database.

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

7 / 10

Which of the following operations do not have corresponding SQL set operators?

Select all that apply.

Intersection

Union

Symmetric Difference

Relative Complement

Absolute Complement

id160

SQL does not have built-in operators specifically dedicated to symmetric difference or absolute complement.

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

SQL does not have built-in operators specifically dedicated to symmetric difference or absolute complement.

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

SQL does not have built-in operators specifically dedicated to symmetric difference or absolute complement.

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

8 / 10

True or False?

The minimum set of requirements for a database to be considered relational are the following.

Presents the data to the user as relations
Provide relational operators to manipulate the data in tabular form.

True

False

id156

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

9 / 10

Which of the following terms describes a function in SQL where the return values are expected to be the same for the same input parameters, regardless of the number of times it is run?

Aggregate

Scalar

Deterministic

Non-Deterministic

id167

A deterministic function in SQL (or any other programming language) will always produce the same output when given the same set of input parameters, no matter how many times it’s executed.

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

A deterministic function in SQL (or any other programming language) will always produce the same output when given the same set of input parameters, no matter how many times it’s executed.

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

A deterministic function in SQL (or any other programming language) will always produce the same output when given the same set of input parameters, no matter how many times it’s executed.

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

10 / 10

What term is used in database management systems to describe a contiguous set of data blocks or pages, reserved for storing a particular type of data, requested from the operating system as a unit?

Segment

Page

Extent

Cluster

id129

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

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

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

Your score is