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

Which subset of SQL is used to limit access to a database or its data?

Data Definition Language

Data Control Language

Data Entity Language

Data Manipulation Language

id188

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

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

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

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

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

2 / 10

Join algorithms are utilized by the database query optimizer to generate an efficient execution plan.

Identify the three standard join algorithms from the following list.

Nested-Loop Join

Equi-Join

Theta Join

Merge Join

Hash Join

id120

There are several types of join algorithms that the SQL Server optimizer can choose from, including nested loop join, hash join, and merge join. The choice of the join algorithm depends on various factors such as the size of the datasets, the presence of indexes, and the distribution of data. The optimizer makes a cost-based decision to choose the most efficient algorithm for the given query in order to return the results in the quickest possible time. These types of joins are created by the optimizer and not the developer (although you can add query hints).

https://en.wikipedia.org/wiki/Hash_join
https://en.wikipedia.org/wiki/Nested_loop_join
https://en.wikipedia.org/wiki/Sort-merge_join

3 / 10

True or False?

The following are all different procedural extensions offered by different database vendors.

T-SQL
PL/SQL
SQL/PSM
PL/pgSQL

True

False

id204

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

4 / 10

What is the output of the following SQL statement?

id112

UNION ALL combines the result sets of two relations and includes duplicate rows. Set operators treat NULL markers differently than joins. Set operators use the logic of IS NOT DISTINCT FROM.

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

UNION ALL combines the result sets of two relations and includes duplicate rows. Set operators treat NULL markers differently than joins. Set operators use the logic of IS NOT DISTINCT FROM.

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

UNION ALL combines the result sets of two relations and includes duplicate rows. Set operators treat NULL markers differently than joins. Set operators use the logic of IS NOT DISTINCT FROM.

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

5 / 10

During the 1970s, while at IBM, what two computer scientists developed a version of SQL (originally called SEQUEL) to manipulate and retrieve data stored in IBM’s original quasi-relational database management system, System R.

E.F. Codd and James Gray

Donald D. Chamberlin and Raymond F. Boyce

Edgar F. Codd and Alan Turing

Grace Hopper and Ada Lovelace

id148

SQL, originally known as SEQUEL (Structured English Query Language), was co-developed by Raymond F. Boyce and Donald D. Chamberlin at IBM in the early 1970s. It was based on their earlier language called SQUARE (Specifying Queries As Relational Expressions). SEQUEL was specifically designed to manipulate and retrieve data from relational databases, incorporating Edgar F. Codd’s relational model.

In 1974, Chamberlin and Boyce published a paper titled “SEQUEL: A Structured English Query Language,” which outlined the refinements made to SQUARE and introduced the data retrieval capabilities of SEQUEL. Notably, the name SEQUEL had to be changed to SQL due to trademark issues with the Hawker Siddeley aircraft company. The new name was derived by dropping the vowels.

Since its inception, SQL has evolved to become the most widely used language for interacting with relational databases. Its intuitive syntax and powerful functionality have contributed to its widespread adoption and application in the field of data management.

https://en.wikipedia.org/wiki/Donald_D._Chamberlin
https://en.wikipedia.org/wiki/Raymond_F._Boyce

6 / 10

Consider the following relation which contains customer IDs and their corresponding city.

Identify which SQL statements would return a list of cities with more than one customer.

Select all that apply.

id58

SQL offers great flexibility, allowing for multiple ways to construct queries and solve problems. The most effective method can depend on the specific use case, data size, database structure, performance requirements, and code readability.

While there’s often more than one way to solve a problem in SQL, it’s crucial to understand the nuances of the language and the specifics of the problem to identify the optimal approach.

7 / 10

What is meant by OLAP in the context of data warehousing?

OLAP stands for Operational Load and Analysis Process and refers to the process of loading and analyzing live transactional data in real-time.

OLAP stands for Online Application Processing and refers to the process of building and maintaining online web applications for data warehousing.

OLAP stands for Online Load and Analysis Problem, which refers to the challenges faced when data warehouses become too large to handle, causing performance issues and difficulties in analysis.

OLAP stands for Online Analytical Processing and is a computing method that enables users to easily and selectively extract and query data in order to analyze it from different points of view.

id53

OLAP stands for Online Analytical Processing and is a computing method that enables users to easily and selectively extract and query data in order to analyze it from different points of view.

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

OLAP stands for Online Analytical Processing and is a computing method that enables users to easily and selectively extract and query data in order to analyze it from different points of view.

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

OLAP stands for Online Analytical Processing and is a computing method that enables users to easily and selectively extract and query data in order to analyze it from different points of view.

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

8 / 10

True or False?

Views are often thought of as virtual tables. However, you can index a view, causing it to be materialized and storing its data physically in the database rather than re-evaluating the view definition each time it is accessed.

True

False

id218

Views are considered virtual tables because they provide a logical representation of data derived from one or more underlying tables. However views can be indexed, which leads to materialization. Materialized views store the view’s data physically in the database, allowing for faster data retrieval since the view’s definition is not re-evaluated each time it is accessed. By indexing a view, the underlying data is pre-calculated and stored, providing improved performance for queries that utilize the view.

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

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

Microsoft SQL Server offers the following ten table types.

Which of the following table types are schema bound objects?

Select all that apply.

Table

View

Values Constructor

Table Valued Function

Subquery

Derived Table

Common Table Expression

Temporary Table

Table Variable

External Table

id220

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

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

What is the correct sequence of commands to process a set of records using a cursor?

DECLARE → INITIALIZE → GET → CLOSE

DECLARE → CURSOR → GET → FETCH → CLOSE

DECLARE→ OPEN → FETCH → CLOSE

DECLARE → CURSOR → FETCH → CLOSE

DECLARE → GET → SEEK → CLOSE

id35

The correct sequence is DECLARE → OPEN → FETCH → CLOSE.

DECLARE: This command defines the query used to build the result set on which the cursor operates.
OPEN: This command opens the cursor, preparing it for fetching records from the result set.
FETCH: This command retrieves the next record from the result set using the cursor. It is typically used in a loop to fetch records one by one until there are no more records left.
CLOSE: This command closes the cursor, releasing any resources associated with it and ending the result set traversal.

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

The correct sequence is DECLARE → OPEN → FETCH → CLOSE.

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

The correct sequence is DECLARE → OPEN → FETCH → CLOSE.

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

2 / 10

Join algorithms are utilized by the database query optimizer to generate an efficient execution plan.

Identify the three standard join algorithms from the following list.

Nested-Loop Join

Equi-Join

Theta Join

Merge Join

Hash Join

id120

https://en.wikipedia.org/wiki/Hash_join
https://en.wikipedia.org/wiki/Nested_loop_join
https://en.wikipedia.org/wiki/Sort-merge_join

3 / 10

Which of the following are examples of relational division?

Check all that apply.

A. Employee Skills Matching: Suppose you have two tables, one listing employees and their skills, and another listing the skills required for a job. Relational division can be used to find employees who have all the skills required for the job.

B. Student Course Enrollment: If you have one table of students and the courses they are enrolled in, and another table listing all the courses required for a particular degree, relational division can be used to find students who are enrolled in all the necessary courses for that degree.

C. Product Bundles: Suppose you have a table listing all the products that customers have bought, and another table listing the products included in a specific bundle. Relational division can be used to find customers who have bought all the products in the bundle.

D. All of these are examples that would require relational division.

id80

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

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

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

4 / 10

In databases, collations dictate how data is sorted and compared in a database and are defined for a particular character set.

Which of the following is incorrect about database collation?

Collation determines the sort order of characters.

Collation determines case sensitivity.

Collation determines accent sensitivity.

Collation determines width sensitivity and Kana sensitivity for Japanese data.

These are all correct statements about collation.

id126

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

5 / 10

True or False?

The following are all different procedural extensions offered by different database vendors.

T-SQL
PL/SQL
SQL/PSM
PL/pgSQL

True

False

id204

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

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

7 / 10

If you do not specify a mode for a stored procedure’s parameter, what is the default mode?

IN OUT

OUT

DEFAULT

id171

The IN mode is used when you want to pass values to the stored procedure. It indicates that the parameter is read-only within the stored procedure, and any modifications made to the parameter’s value will not be reflected outside the procedure.

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

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

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

10 / 10

Which of the following logic statements are correct?

Select all that apply.

True AND Unknown is Unknown

False AND Unknown is False

Unknown AND Unknown is Unknown

True AND False is False

NOT(Unknown) is Unknown

id82

All of these statements are correct.

SQL uses a three-valued logic system: True, False, and Unknown. In SQL we often use the NULL markers or the empty string to denote unknown.

The following are all true statements.

AND

True AND Unknown is Unknown.
False AND Unknown is False.
Unknown AND Unknown is Unknown.
True AND False is False.

True OR Unknown is True.
False OR Unknown is Unknown.
True OR False is True.
Unknown OR Unknown is Unknown.

Negation

NOT(Unknown) is Unknown.

https://en.wikipedia.org/wiki/Three-valued_logic

All of these statements are correct.

SQL uses a three-valued logic system: True, False, and Unknown. In SQL we often use the NULL markers or the empty string to denote unknown.

The following are all true statements.

AND

True AND Unknown is Unknown.
False AND Unknown is False.
Unknown AND Unknown is Unknown.
True AND False is False.

True OR Unknown is True.
False OR Unknown is Unknown.
True OR False is True.
Unknown OR Unknown is Unknown.

Negation

NOT(Unknown) is Unknown.

https://en.wikipedia.org/wiki/Three-valued_logic

All of these statements are correct.

SQL uses a three-valued logic system: True, False, and Unknown. In SQL we often use the NULL markers or the empty string to denote unknown.

The following are all true statements.

AND

True AND Unknown is Unknown.
False AND Unknown is False.
Unknown AND Unknown is Unknown.
True AND False is False.

True OR Unknown is True.
False OR Unknown is Unknown.
True OR False is True.
Unknown OR Unknown is Unknown.

Negation

NOT(Unknown) is Unknown.

https://en.wikipedia.org/wiki/Three-valued_logic

Your score is