Launch pricing — Azure certification prep for €19,95 €29,95

← All DP-900 topicsDP-900 · Relational Data on Azure

DP-900 Relational Database Concepts and SQL Practice Questions

Relational concepts are a reliable source of DP-900 points. Know how tables, primary keys and foreign keys work, why databases are normalized, and what the ACID properties of a transaction guarantee.

You should also recognize SQL statement types (DDL, DML and DCL), and know what views, indexes and stored procedures are for. Some questions show a short query and ask what it returns.

Below are 12 free questions on this topic. Try each one before you open the answer.

Question 1

A database administrator wants to speed up queries that frequently filter records by the LastName column in a large Customers table. No other indexes exist on the table. Which type of index should be created?

  • A.A clustered index on LastName
  • B.A non-clustered index on LastName
  • C.A primary key index on LastName
  • D.A unique index on LastName
chevron_rightShow answer and explanation

Correct answer: B. A non-clustered index on LastName

A non-clustered index creates a separate structure that points to the data rows, making lookups by LastName faster without reordering the physical table. A clustered index defines the physical sort order of the table and each table can only have one — it is typically reserved for the primary key. A primary key index enforces uniqueness and is usually the clustered index. A unique index would also enforce uniqueness, which is not required here since multiple customers can share the same last name.

Question 2

A query uses a LEFT JOIN between the Orders table and the Customers table. Which result does a LEFT JOIN produce?

  • A.Only rows where both tables have matching values
  • B.All rows from both tables, with NULLs where there is no match
  • C.All rows from the left table, with NULLs for non-matching rows in the right table
  • D.All rows from the right table, with NULLs for non-matching rows in the left table
chevron_rightShow answer and explanation

Correct answer: C. All rows from the left table, with NULLs for non-matching rows in the right table

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matched rows from the right table. Where there is no match in the right table, NULL values are returned for the right table's columns. An INNER JOIN returns only matched rows from both tables. A FULL OUTER JOIN returns all rows from both tables with NULLs where there is no match. A RIGHT JOIN is the mirror of LEFT JOIN.

Question 3

A developer writes a T-SQL query and needs to return only rows where the value in the Salary column is greater than 50000. Which clause should be used?

  • A.GROUP BY Salary > 50000
  • B.HAVING Salary > 50000
  • C.ORDER BY Salary > 50000
  • D.WHERE Salary > 50000
chevron_rightShow answer and explanation

Correct answer: D. WHERE Salary > 50000

WHERE filters individual rows before any grouping or aggregation. It is used to restrict which rows are returned based on a condition. HAVING is used after GROUP BY to filter aggregated results — it cannot be used to filter individual rows without grouping. ORDER BY sorts results and cannot filter rows. GROUP BY groups rows but does not filter them by itself.

Question 4

A database designer adds a CustomerID column to the Orders table and configures it to only accept values that exist in the CustomerID column of the Customers table. Which constraint enforces this rule?

  • A.CHECK constraint
  • B.FOREIGN KEY constraint
  • C.PRIMARY KEY constraint
  • D.UNIQUE constraint
chevron_rightShow answer and explanation

Correct answer: B. FOREIGN KEY constraint

A FOREIGN KEY constraint enforces referential integrity by ensuring that a column's value must exist in the referenced table's primary key (or unique key). This prevents orphaned records — for example, you cannot insert an order for a customer that does not exist in the Customers table. A CHECK constraint validates values against a logical condition. A PRIMARY KEY uniquely identifies a row. A UNIQUE constraint prevents duplicates but does not link to another table.

Question 5

Which T-SQL statement is used to add a new row of data to an existing table?

  • A.ADD
  • B.CREATE
  • C.INSERT INTO
  • D.UPDATE
chevron_rightShow answer and explanation

Correct answer: C. INSERT INTO

INSERT INTO is the T-SQL statement used to add new rows to a table. The syntax specifies the target table, the columns to populate, and the values to insert — for example: INSERT INTO Customers (Name, Email) VALUES ('Alice', 'alice@example.com'). CREATE is used to create new database objects. UPDATE modifies existing rows. ADD is a clause used within ALTER TABLE to add columns, not to insert data rows.

Question 6

A developer writes a SELECT query that includes another SELECT query nested inside the WHERE clause to filter results based on a calculated value from a different table. What is this nested SELECT called?

  • A.A common table expression (CTE)
  • B.A subquery
  • C.A window function
  • D.A derived column
chevron_rightShow answer and explanation

Correct answer: B. A subquery

A subquery (also called an inner query or nested query) is a SELECT statement embedded within another SQL statement. It can appear in the WHERE, FROM, or SELECT clause. When placed in the WHERE clause, it typically returns one or more values that the outer query uses for filtering. A CTE is defined with the WITH keyword before the main query. Window functions use OVER() to compute values across a set of rows without reducing the result set.

Question 7

A developer wants to define a temporary, named result set at the beginning of a query using the WITH keyword, and then reference it multiple times within the same query. Which T-SQL feature enables this?

  • A.A derived table (subquery in FROM)
  • B.A temporary table (#TempTable)
  • C.A common table expression (CTE)
  • D.A table variable (@TableVar)
chevron_rightShow answer and explanation

Correct answer: C. A common table expression (CTE)

A Common Table Expression (CTE) is defined using the WITH keyword before the main SELECT statement. It creates a named, temporary result set that exists only for the duration of the query. CTEs improve readability compared to nested subqueries, can be referenced multiple times within the same query, and support recursive queries. Example syntax: `WITH CTE_Name AS (SELECT ...) SELECT * FROM CTE_Name`. Derived tables (subqueries in FROM) cannot be referenced more than once. Temporary tables and table variables persist beyond a single query expression.

Question 8

A developer uses `DENSE_RANK() OVER (ORDER BY Score DESC)` on a result set where two students share the top score. Which behavior distinguishes DENSE_RANK() from RANK() in this scenario?

  • A.DENSE_RANK() assigns unique numbers with no ties; RANK() allows ties
  • B.DENSE_RANK() skips rank numbers after a tie; RANK() does not skip
  • C.DENSE_RANK() does not skip rank numbers after a tie; RANK() does skip
  • D.DENSE_RANK() restarts numbering at each partition; RANK() does not
chevron_rightShow answer and explanation

Correct answer: C. DENSE_RANK() does not skip rank numbers after a tie; RANK() does skip

Both RANK() and DENSE_RANK() assign the same number to tied rows. The key difference is what happens to the next rank: RANK() skips numbers equal to the count of tied rows (e.g., 1, 1, 3), creating a gap. DENSE_RANK() never skips (e.g., 1, 1, 2), resulting in a consecutive sequence. Choose DENSE_RANK() when you need to know the number of distinct rank positions; choose RANK() when you need the position each row would occupy if ties were broken.

Question 9

A developer combines the results of two SELECT statements using UNION. The first query returns 30 rows and the second returns 20 rows, but 5 rows are identical across both result sets. How many rows does the final UNION result contain?

  • A.50 rows
  • B.45 rows
  • C.25 rows
  • D.10 rows
chevron_rightShow answer and explanation

Correct answer: B. 45 rows

UNION combines the results of two SELECT statements and automatically removes duplicate rows from the combined result set. With 30 + 20 = 50 total rows, and 5 duplicate rows that appear in both result sets, UNION removes those 5 duplicates, returning 45 unique rows. To include all rows including duplicates, use UNION ALL, which would return all 50 rows. Both queries in a UNION must have the same number of columns and compatible data types.

Question 10

A query must return a result that combines rows from a table with itself — for example, to find all pairs of employees in the same department. Which type of JOIN enables this?

  • A.CROSS JOIN
  • B.SELF JOIN
  • C.FULL OUTER JOIN
  • D.NATURAL JOIN
chevron_rightShow answer and explanation

Correct answer: B. SELF JOIN

A self join joins a table to itself by referencing it twice with different aliases. This is useful for comparing rows within the same table — for example, finding employees in the same department, or matching an employee to their manager in a hierarchy table. Example: `SELECT a.Name, b.Name FROM Employees a JOIN Employees b ON a.DepartmentID = b.DepartmentID AND a.ID <> b.ID`. A CROSS JOIN produces a Cartesian product (every row paired with every other row) without a join condition. NATURAL JOIN automatically joins on columns with matching names (not supported in T-SQL). FULL OUTER JOIN returns all rows from both tables with NULLs where there is no match.

Question 11

Determine whether each statement about normalization in relational databases is correct.

  1. 1.Normalization separates each entity into its own table to minimize data duplication.
  2. 2.A foreign key is used to uniquely identify each row within its own table.
  3. 3.Referential integrity ensures that a foreign key value corresponds to an existing primary key in the related table.
chevron_rightShow answer and explanation
  1. Statement 1: Yes
  2. Statement 2: No
  3. Statement 3: Yes

Statement 1 is correct: normalization places each entity in its own table to reduce duplication. Statement 2 is incorrect: a foreign key references the primary key of a related table — it is the primary key that uniquely identifies each row within its own table. Statement 3 is correct: referential integrity enforced by the RDBMS prevents orphaned foreign key values.

Question 12

A database ___ is a virtual table created from a SELECT query that provides a simplified or filtered view of data in one or more underlying tables.

  • A.index
  • B.stored procedure
  • C.view
  • D.schema
chevron_rightShow answer and explanation

Correct answer: C. view

A view is a virtual table based on a SELECT query. It does not store data itself but presents data from underlying tables in a defined way. An index is a sorted structure to speed up data retrieval. A stored procedure is reusable SQL logic that can accept parameters. A schema is the structural definition of the database.

These are 12 of 53 Relational Database Concepts and SQL questions in the full bank

Practice all 400 DP-900 questions in exam format, including drag-and-drop and multi-select questions, with a mock exam and progress tracking.