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.

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

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

2 / 10

Which are the following ways can you pass a parameter to a stored procedure or function?

Select all that apply?

Named Parameters

Positional Parameters

Keyword Parameters

Default Parameters

Categorical Parameters

Mixed Parameters

id173

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

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

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

3 / 10

Which of the following statements are correct regarding database tables?

Select all that apply.

A database trigger can be placed on a table and executed when certain DML events occur.

Large tables can be partitioned into smaller, more manageable pieces, yet still being treated as a single table.

Tables are mutable items.

Collation determines how data is sorted and compared in a table.

Constraints are the rules enforced on the data in the table.

id222

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)

4 / 10

Consider the use of the BETWEEN comparison operator in the SQL statements provided.

Which of the following statements are correct?

Select all that apply.

The BETWEEN statement is not valid in a CASE statement.

BETWEEN is inclusive, values of 1 and 2 will be included.

BETWEEN is exclusive, the result of this query is an empty set.

You can achieve the same result using the >= and <= operators.

id77

The BETWEEN operator in SQL is a comparison operator used to specify a range of values for a given column or expression. It allows you to conveniently define an inclusive range by specifying a starting point and an ending point, eliminating the need for multiple comparison operators.

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

5 / 10

Consider the following relations, Employees and Departments.

What is the output of the following SQL statement?

Select from the following options.

id199

In this example, the OUTER JOIN is preserved, but the Departments relation is limited to the records where the Department Name is equal to Sales.

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

In this example, the OUTER JOIN is preserved, but the Departments relation is limited to the records where the Department Name is equal to Sales.

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

In this example, the OUTER JOIN is preserved, but the Departments relation is limited to the records where the Department Name is equal to Sales.

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

6 / 10

In the context of a database management system, which term is used to describe the smallest unit of data that a database management system can read or write?

Cluster

Segment

Extent

Page

id133

A page, in the context of databases and computer systems, is a fixed-length unit of data storage. It is the smallest unit of data that can be read from, or written to, a disk by a database management system. The size of a page can vary depending on the specific database management system and the underlying hardware, but it is typically in the range of a few kilobytes. A page may contain multiple records or rows of a table, and it is the basic unit for storage allocation and data transfer between the disk and the main memory.

The use of pages helps to optimize the reading and writing operations, as data located on the same page can be read or written in a single I/O operation. A page is also fundamental to the implementation of virtual memory systems, where it is the unit of data exchanged between physical memory and secondary storage.

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

7 / 10

Consider the following Inventory relation that contains parts and their quantity.

What is the result of the following SQL statement?

The SQL statement fails as there is no GROUP BY clause.

The SQL statement returns a NULL marker because their exists a NULL marker in the Quantity column.

The SQL statement returns the value of 10; the record with the NULL marker is not included in the calculation.

The SQL statement returns the value of 8; the AVG function replaces the NULL marker with a 0 (zero).

id99

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 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 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

8 / 10

What affect does the COMMIT command have on a cursor?

The COMMIT command closes the cursor.

The COMMIT command moves the cursor to the next record.

The COMMIT command closes the current cursor and re-initializes it.

The COMMIT command does not directly affect the state or position of a cursor.

id31

The COMMIT command does not have any impact on a cursor.

The COMMIT command in SQL is used to permanently save the changes made within a transaction to the database. It ensures that the modifications are durable and visible to other transactions.

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

The COMMIT command does not have any impact on a cursor.

The COMMIT command in SQL is used to permanently save the changes made within a transaction to the database. It ensures that the modifications are durable and visible to other transactions.

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

The COMMIT command does not have any impact on a cursor.

The COMMIT command in SQL is used to permanently save the changes made within a transaction to the database. It ensures that the modifications are durable and visible to other transactions.

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

9 / 10

Given the following description, choose which type of index best describes the following?

This type of index determines the physical order of data rows in a table.
Each table can have only one of this type of index.
The data rows in a table with this type of index are physically stored on disk.
The leaf nodes of this type of index contain the actual data rows.

Clustered Index

Non-Clustered Index

Column Store Index

Hash Index

id116

A clustered index reorders the way records in the table are physically stored on disk. Each table can have only one clustered index, but it can contain multiple columns. The leaf nodes of a clustered index contain the actual data rows of the table, not just the index keys. When a query uses the clustered index to find its data, it can retrieve it directly using this index, which generally makes queries faster. However, as the data is stored in the order of the clustered index, any insertions, updates, or deletions can potentially require substantial reordering of the data, which can be resource-intensive.

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

10 / 10

Which term describes the management of simultaneous transactions to prevent conflicts?

Parallelism

Serialization

Database Control

Concurrency Control

id14

Concurrency control refers to the management of simultaneous transactions in a database system to prevent conflicts. It ensures that multiple transactions can execute concurrently without compromising data consistency and integrity.

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

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

During which phase of the database design do you determine the following constraints on table columns?

NOT NULL
UNIQUE
CHECK
PRIMARY KEY
FOREIGN KEY
DEFAULT

Conceptual

Logical

Physical

ERD Design

id29

The logical phase of the database design is when constraints on table columns such as NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY, and DEFAULT are determined. These constraints enforce data integrity in the database and are defined based on business rules and data requirements.

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

2 / 10

Given the following description, choose which type of index best describes the following?

This type of index determines the physical order of data rows in a table.
Each table can have only one of this type of index.
The data rows in a table with this type of index are physically stored on disk.
The leaf nodes of this type of index contain the actual data rows.

Clustered Index

Non-Clustered Index

Column Store Index

Hash Index

id116

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

3 / 10

While the SQL syntax is traditionally written in a specific sequence – SELECT, FROM, WHERE, GROUP BY, HAVING, and finally LIMIT – the actual processing of these clauses by the SQL engine does not follow this same sequence.

Which of the following is the correct processing order of an SQL statement?

SELECT → FROM → WHERE → GROUP BY → HAVING → LIMIT

FROM → WHERE → HAVING → GROUP BY → SELECT → LIMIT

WHERE → GROUP BY → FROM → SELECT → HAVING → LIMIT

FROM → WHERE → GROUP BY → HAVING → LIMIT → SELECT

id205

The actual processing order of SQL clauses is not determined by the order in which they are written in the syntax. The correct processing order is as follows:

FROM -> WHERE -> GROUP BY -> HAVING -> LIMIT -> SELECT

https://sqlbolt.com/lesson/select_queries_order_of_execution

The actual processing order of SQL clauses is not determined by the order in which they are written in the syntax. The correct processing order is as follows:

FROM -> WHERE -> GROUP BY -> HAVING -> LIMIT -> SELECT

https://sqlbolt.com/lesson/select_queries_order_of_execution

The actual processing order of SQL clauses is not determined by the order in which they are written in the syntax. The correct processing order is as follows:

FROM → WHERE → GROUP BY → HAVING → LIMIT → SELECT

https://sqlbolt.com/lesson/select_queries_order_of_execution

4 / 10

Which of the following SQL statements belong to the category of Data Definition Language (DDL) statements?

Select all that apply.

DROP

SELECT

DELETE

CREATE

TRUNCATE

ALTER

UPDATE

RENAME

id196

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

5 / 10

Which of the following ACID properties ensures that concurrent transactions are executed separately?

Atomicity

Consistency

Isolation

Durability

id19

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

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

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

6 / 10

In the context of a data warehouse, what is meant by data mining?

Data mining refers to the process of extracting raw data from external sources and loading it into the data warehouse.

Data mining refers to the process of transforming and cleansing data to ensure its quality and accuracy in the data warehouse.

Data mining refers to the process of exploring and analyzing large datasets in the data warehouse to discover patterns, trends, and insights.

Data mining refers to the process of archiving and storing historical data in the data warehouse for future reference.

id49

In the context of a data warehouse, data mining refers to the process of exploring and analyzing large datasets within the data warehouse to discover patterns, trends, and insights. It involves using various statistical and machine learning techniques to extract valuable information from the data.

Data mining helps uncover hidden patterns, associations, correlations, and anomalies that may not be readily apparent. This information can be used for business intelligence, predictive modeling, market analysis, customer segmentation, and other data-driven decision-making processes.

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

7 / 10

What is the definition of a tuple in the context of database systems?

A tuple refers to a single column or attribute in a relation, containing a specific data value for a given record.

A tuple refers to a collection of tables in a database system that are joined together based on common keys or attributes.

A tuple refers to a data structure used to store temporary results during the execution of a SQL statement, typically in memory or disk storage.

A tuple refers to a single row or record in a relation, containing a collection of related data points or values that correspond to specific attributes or columns in the relation.

id163

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

8 / 10

Which of the following best describes an entity in the context of a database?

An entity is a collection of data stored in a database.

An entity is a set of relationships between tables in a database.

An entity is a user-defined function that performs calculations on data in a database.

An entity is a real-world item that can be uniquely identified and described using attributes or properties.

id27

An entity, in the context of a database, refers to a real-world object, concept, or item that can be uniquely identified and described. Entities are fundamental building blocks in database design and play a crucial role in organizing and representing data. Each entity is characterized by its attributes or properties, and are used to create tables in a database, serving as containers for storing and managing related data.

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

9 / 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

10 / 10

SQL is based upon what mathematical foundations?

Select all that apply.

Tuple Calculus

Relational Algebra

Set Theory

Statistic Inference

Propositional Calculus

id154

SQL is based on tuple calculus, relational algebra and set theory.

Tuple calculus provides the theoretical basis for selecting and projecting data from a table, while relational algebra defines the operations for working with relational databases. Set theory is fundamental to SQL as it deals with sets of data and set operations.

Propositional calculus, while a branch of formal logic, is not directly used as a mathematical foundation for SQL. While SQL involves logic in terms of its query capabilities, it does not directly utilize propositional calculus as a mathematical foundation.

https://en.wikipedia.org/wiki/SQL
https://en.wikipedia.org/wiki/Relational_algebra
https://en.wikipedia.org/wiki/Tuple_relational_calculus
https://en.wikipedia.org/wiki/Propositional_calculus

SQL is based on tuple calculus, relational algebra and set theory.

https://en.wikipedia.org/wiki/SQL
https://en.wikipedia.org/wiki/Relational_algebra
https://en.wikipedia.org/wiki/Tuple_relational_calculus
https://en.wikipedia.org/wiki/Propositional_calculus

SQL is based on tuple calculus, relational algebra and set theory.

https://en.wikipedia.org/wiki/SQL
https://en.wikipedia.org/wiki/Relational_algebra
https://en.wikipedia.org/wiki/Tuple_relational_calculus
https://en.wikipedia.org/wiki/Propositional_calculus

Your score is