Back to Subject

Unit 3: Relational Operations - Practice Quiz

INT306 — Database Management Systems 60 Questions
0 Correct 0 Wrong 60 Left
0/60

1 Which of the following is an aggregate function used to find the total number of rows in a dataset?

Aggregate functions Easy
A. COUNT()
B. SUM()
C. MAX()
D. TOTAL()

2 Which SQL clause is typically used alongside aggregate functions to arrange identical data into groups?

Aggregate functions Easy
A. WHERE
B. ORDER BY
C. HAVING
D. GROUP BY

3 Which type of SQL join returns only the rows that have matching values in both tables?

Sql joins Easy
A. LEFT OUTER JOIN
B. INNER JOIN
C. RIGHT OUTER JOIN
D. FULL OUTER JOIN

4 Which join returns all rows from both tables, matching records where possible and filling in NULLs where there is no match?

Sql joins Easy
A. LEFT JOIN
B. FULL OUTER JOIN
C. INNER JOIN
D. CROSS JOIN

5 Which set operator combines the results of two queries and automatically removes duplicate rows?

set operators Easy
A. INTERSECT
B. UNION ALL
C. UNION
D. EXCEPT

6 Which set operator returns only the distinct rows that are present in the results of both queries?

set operators Easy
A. INTERSECT
B. EXCEPT
C. UNION
D. MINUS

7 What is a database view?

views Easy
A. A primary key constraint
B. A physical table storing redundant data
C. An index used to speed up queries
D. A virtual table based on the result-set of an SQL statement

8 Which SQL command is used to remove a view from the database?

views Easy
A. DROP VIEW
B. REMOVE VIEW
C. TRUNCATE VIEW
D. DELETE VIEW

9 What is a subquery in SQL?

subqueries Easy
A. A query that creates a new database
B. A query that uses only aggregate functions
C. A query that joins three or more tables
D. A query nested inside another query

10 Which SQL operator is commonly used in the WHERE clause to check if a value matches any value returned by a subquery?

subqueries Easy
A. IN
B. IS NULL
C. LIKE
D. BETWEEN

11 In relational algebra, which operation is used to extract specific rows from a relation based on a given condition?

relational algebra Easy
A. Cross Product ()
B. Projection ()
C. Join ()
D. Selection ()

12 Which relational algebra operation is used to extract specified columns from a relation?

relational algebra Easy
A. Union ()
B. Intersection ()
C. Projection ()
D. Selection ()

13 Which SQL clause is mandatory when defining a window function to specify the partitioning and ordering of the window?

Window functions Easy
A. OVER()
B. PARTITION OF
C. GROUP BY
D. HAVING

14 Which window function assigns a unique sequential integer to each row within a partition of a result set?

Window functions Easy
A. NTILE()
B. ROW_NUMBER()
C. RANK()
D. DENSE_RANK()

15 What is the primary purpose of a hash function in a database context?

Hashing Easy
A. To create temporary virtual tables
B. To map search keys to specific bucket or block addresses
C. To sort data alphabetically in tables
D. To join multiple tables together

16 What is it called when a hash function generates the same bucket address for two different search keys?

Hashing Easy
A. A hash collision
B. A query optimization
C. A syntax error
D. A deadlock

17 What is the primary benefit of creating an index on a database table?

Indexing Easy
A. It speeds up data retrieval operations
B. It reduces the total storage space required
C. It eliminates the need for primary keys
D. It automatically encrypts the data

18 Which data structure is most commonly used to implement indexing in a modern relational database system?

Indexing Easy
A. Queue
B. B-Tree (and its variants like B+ Tree)
C. Stack
D. Linked List

19 What is the primary role of a query optimizer in a Database Management System?

Query Optimization Easy
A. To determine the most efficient execution plan for a given query
B. To write SQL queries automatically for the user
C. To manage concurrent transactions
D. To enforce referential integrity constraints

20 Which type of query optimizer relies on database statistics (such as table size and data distribution) to evaluate alternative execution plans?

Query Optimization Easy
A. Syntax-based optimizer
B. Rule-based optimizer
C. Cost-based optimizer
D. Buffer-based optimizer

21 Consider a table Sales(Region, Amount). Which query correctly finds the regions where the average sales amount exceeds 500?

Aggregate functions Medium
A. SELECT Region FROM Sales HAVING AVG(Amount) > 500
B. SELECT Region FROM Sales WHERE AVG(Amount) > 500 GROUP BY Region
C. SELECT Region FROM Sales GROUP BY Region WHERE AVG(Amount) > 500
D. SELECT Region FROM Sales GROUP BY Region HAVING AVG(Amount) > 500

22 If a column Bonus contains the values [100, NULL, 200, 300, NULL], what will be the result of SELECT COUNT(Bonus), SUM(Bonus) FROM Employee?

Aggregate functions Medium
A. 5, 600
B. 3, NULL
C. 3, 600
D. 5, NULL

23 You need to find all customers, including those who have not placed any orders, along with their order details. Which type of join should be used between the Customers (left) and Orders (right) tables?

Sql joins Medium
A. RIGHT OUTER JOIN
B. NATURAL JOIN
C. LEFT OUTER JOIN
D. INNER JOIN

24 What is the primary purpose of a Self Join in SQL?

Sql joins Medium
A. To combine rows from two different tables based on a related column.
B. To automatically eliminate duplicate records from a table.
C. To return the Cartesian product of two identical tables.
D. To join a table to itself to compare rows within the same table.

25 When combining the results of two queries, what is the key difference between UNION and UNION ALL?

set operators Medium
A. UNION ALL automatically sorts the output, whereas UNION does not.
B. UNION ALL removes duplicates, making it slower than UNION.
C. UNION removes duplicates, making it generally slower than UNION ALL.
D. UNION only works with numeric data types.

26 Which SQL set operator is equivalent to the relational algebra set difference operation?

set operators Medium
A. CROSS JOIN
B. EXCEPT
C. UNION
D. INTERSECT

27 Under which of the following conditions is a standard SQL view generally NOT updatable?

views Medium
A. When the view contains a GROUP BY clause or aggregate functions.
B. When the view contains a simple SELECT from a single table.
C. When the view excludes some columns of the base table.
D. When the view is created with the WITH CHECK OPTION clause.

28 What is a Materialized View?

views Medium
A. A physical copy of the query results stored on disk to improve read performance.
B. A virtual table whose contents are generated dynamically at runtime every time it is queried.
C. A system-level view that restricts users from executing DML statements.
D. A view that temporarily holds data only during an active user session.

29 How does a correlated subquery differ from a non-correlated subquery?

subqueries Medium
A. A correlated subquery references columns from the outer query and must be evaluated for each row of the outer query.
B. A correlated subquery is faster because it does not depend on the outer query.
C. A correlated subquery can only be used in the FROM clause.
D. A correlated subquery executes exactly once and passes its result to the outer query.

30 Which keyword is used to test whether a subquery returns any rows at all, evaluating to TRUE if at least one row is returned?

subqueries Medium
A. IN
B. EXISTS
C. ALL
D. ANY

31 In relational algebra, if you want to find the names of employees who work on ALL projects, which operation is most appropriate?

relational algebra Medium
A. Division ()
B. Cartesian Product ()
C. Selection ()
D. Projection ()

32 Which relational algebra operation is logically equivalent to performing a Cartesian product followed by a Selection operation?

relational algebra Medium
A. Set Difference ()
B. Theta Join ()
C. Natural Join ()
D. Intersection ()

33 If two employees have the same highest salary, how will the RANK() and DENSE_RANK() functions assign ranks to the employee with the next highest salary?

Window functions Medium
A. RANK() assigns 3, while DENSE_RANK() assigns 2.
B. RANK() assigns 2, while DENSE_RANK() assigns 3.
C. Both will assign rank 2.
D. Both will assign rank 3.

34 In a window function, what is the purpose of the PARTITION BY clause?

Window functions Medium
A. To divide the result set into discrete groups over which the window function is applied.
B. To sort the entire result set in descending order.
C. To filter rows before the window function is applied.
D. To physically partition the database tables across multiple disks.

35 In a static hashing scheme, what is a common technique used to handle hash collisions where multiple keys map to the same bucket?

Hashing Medium
A. Creating a new hash table and rehashing all entries
B. Linear probing only
C. Using overflow buckets (chaining)
D. Deleting the older record to accommodate the new one

36 Which of the following is a characteristic of Extendible Hashing?

Hashing Medium
A. It relies entirely on overflow chains to handle all collisions.
B. It uses a directory of pointers that doubles in size when a bucket overflows.
C. It uses a fixed number of buckets that cannot be changed.
D. It guarantees that all data retrieval will always take time.

37 Why is a B+ tree generally preferred over a standard B-tree for database indexing?

Indexing Medium
A. B+ trees allow faster insertion times because they do not require balancing.
B. B+ trees use hash functions internally to locate records in time.
C. B+ trees store data pointers in internal nodes, saving space.
D. B+ trees only store keys in internal nodes, and all leaf nodes are linked, optimizing range queries.

38 What defines a Clustered Index in a relational database?

Indexing Medium
A. It uses a hash table structure rather than a tree structure.
B. A table can have multiple clustered indexes created on different columns.
C. It determines the physical sort order of the data rows in the table.
D. It is an index that stores data records in an unordered heap file.

39 During query optimization, a common heuristic rule is to "push down" selections in the relational algebra tree. What is the primary benefit of this technique?

Query Optimization Medium
A. It bypasses the need for using database indexes.
B. It reduces the size of intermediate relations before costly operations like joins are performed.
C. It converts all inner joins to outer joins.
D. It reduces the number of columns in the final result.

40 In cost-based query optimization, what metadata is heavily relied upon by the optimizer to choose the most efficient execution plan?

Query Optimization Medium
A. Database statistics such as table sizes, tuple counts, and index distributions.
B. The order in which the SQL query clauses are written by the developer.
C. The amount of free space remaining on the hard drive.
D. The specific names of the primary key columns.

41 In standard SQL, consider a table Employee with columns DeptID and Bonus. DeptID has values (1, 1, NULL, NULL). Bonus has values (100, 200, 300, 400). What is the output of SELECT DeptID, SUM(Bonus) FROM Employee GROUP BY DeptID; regarding the NULL DeptIDs?

Aggregate functions Hard
A. They are grouped into a single row with a DeptID of NULL and a sum of 700.
B. They produce two separate rows with DeptID as NULL, having sums 300 and 400 respectively.
C. The query throws a syntax error because NULL cannot be used in a GROUP BY clause.
D. They are ignored and omitted from the result set entirely.

42 Which of the following statements about aggregate functions is TRUE when evaluated over an empty set (0 rows)?

Aggregate functions Hard
A. COUNT(*) returns 0; SUM(col) returns NULL.
B. COUNT(*) returns NULL; SUM(col) returns 0.
C. COUNT(*) returns NULL; SUM(col) returns NULL.
D. COUNT(*) returns 0; SUM(col) returns 0.

43 Consider tables A (id, val) and B (id, val). What is the semantic difference between SELECT * FROM A LEFT JOIN B ON A.id = B.id AND B.val > 10 and SELECT * FROM A LEFT JOIN B ON A.id = B.id WHERE B.val > 10?

Sql joins Hard
A. The ON clause condition applies the filter to table A as well, eliminating rows from A before the join.
B. The ON clause throws an error because filtering conditions must be placed in the WHERE clause.
C. They produce identical results and query execution plans.
D. The WHERE clause condition acts after the join, effectively converting the LEFT JOIN into an INNER JOIN.

44 Can a self non-equi join be used on a table Matches (Team, Score) to strictly find teams that scored higher than the average score of all teams without utilizing subqueries, window functions, or explicit aggregate functions?

Sql joins Hard
A. No, because calculating an average inherently requires aggregation over a set.
B. Yes, by joining the table with itself on inequality conditions.
C. No, because self-joins only support equi-join (=) conditions.
D. Yes, by using a CROSS JOIN combined with a WHERE clause.

45 Given standard SQL precedence rules, how is the expression SELECT * FROM R UNION SELECT * FROM S INTERSECT SELECT * FROM T EXCEPT SELECT * FROM U evaluated?

Set operators Hard
A. Left to right: (((R UNION S) INTERSECT T) EXCEPT U)
B. INTERSECT first: (R UNION (S INTERSECT T)) EXCEPT U
C. EXCEPT first: R UNION (S INTERSECT (T EXCEPT U))
D. UNION and EXCEPT have higher precedence than INTERSECT

46 Let bag and bag . What is the cardinality of the result of the bag difference ?

Set operators Hard
A. 1
B. 2
C. 4
D. 3

47 View V1 is created as SELECT * FROM T WHERE A > 10. View V2 is created as SELECT * FROM V1 WHERE A < 20 WITH LOCAL CHECK OPTION. If a user attempts to insert a row with A = 5 through V2, what happens in a standard SQL compliant database?

Views Hard
A. Insertion fails because LOCAL CHECK OPTION inherently checks all underlying view conditions regardless of how they were defined.
B. Insertion succeeds because LOCAL CHECK OPTION only enforces V2's condition () and ignores V1 since V1 lacks a check option.
C. Insertion fails because the base table T restricts all inserts that do not satisfy .
D. Insertion succeeds but replaces the value 5 with a NULL.

48 Which of the following view definitions is generally considered automatically updatable in standard SQL?

Views Hard
A. A view defined using a set operator like UNION.
B. A view built on a single base table without GROUP BY, aggregate functions, or distinct constraints.
C. A view utilizing the DISTINCT keyword.
D. A view containing a derived table in the FROM clause.

49 What is the critical risk of evaluating SELECT * FROM A WHERE A.id NOT IN (SELECT B.id FROM B) if table B contains at least one NULL value in the id column?

Subqueries Hard
A. The query will return an empty set (0 rows) regardless of the values in table A.
B. The subquery will throw a runtime syntax error.
C. The query will return all rows from table A, effectively ignoring the NOT IN clause.
D. The NULL value is bypassed, and the query successfully filters out only the non-NULL matches.

50 Which specific type of subquery inherently forces a dependency on the outer query, often preventing the optimizer from evaluating it independently as a one-time operation?

Subqueries Hard
A. Correlated subquery
B. Derived table subquery
C. Scalar subquery
D. Uncorrelated subquery

51 The relational division operator can be expressed using fundamental relational algebra operators. Which formula correctly expresses , assuming schemas and ?

Relational algebra Hard
A.
B.
C.
D.

52 If relation has schema and relation has schema , what is the result of the natural join if relations and have zero common tuple values for their shared attribute ?

Relational algebra Hard
A. An execution error due to disjoint domains.
B. A left outer join equivalent relation containing NULLs.
C. An empty relation with schema .
D. A Cartesian product of and .

53 Which of the following identities correctly describes the equivalence of relational algebra operations?

Relational algebra Hard
A.
B. under all conditions.
C. The cross product of and yields a relation with cardinality .
D. The natural join operator () is not commutative.

54 In the standard SQL window frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, what is the default framing behavior if the ORDER BY clause within the OVER() function is completely omitted?

Window functions Hard
A. It throws a syntax error because a frame clause requires an explicit ORDER BY.
B. It evaluates strictly up to the current row based on a non-deterministic internal read order.
C. It automatically orders the partition by the primary key.
D. It evaluates the window function over the entire partition, effectively treating it as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

55 Given a table containing a score column with values: 100, 100, 90, 80. Using the window function DENSE_RANK() OVER (ORDER BY score DESC), what integer rank is assigned to the row with the score 90?

Window functions Hard
A. 2
B. 1
C. 3
D. 4

56 Why does standard SQL prohibit the usage of Window functions directly inside the WHERE and HAVING clauses of a query?

Window functions Hard
A. Because they require an explicit GROUP BY clause to function.
B. Because they return multiple rows for a single scalar filter condition.
C. Because window frames cannot interpret boolean logical operators.
D. Because window functions are logically evaluated after the WHERE and HAVING clauses have already determined the result set.

57 In the context of Extendible Hashing, a directory doubling operation is specifically triggered during key insertion when:

Hashing Hard
A. The global depth strictly exceeds the local depth of the overflowing bucket.
B. The primary bucket becomes full and a secondary overflow chain cannot be allocated.
C. The local depth of the overflowing bucket equals the current global depth.
D. The overall load factor of the hash table surpasses a predefined threshold.

58 In a tree of order (where represents the maximum number of block pointers per node), what is the minimum number of keys a non-root internal node must contain?

Indexing Hard
A.
B.
C.
D.

59 In heuristic query optimization, what is the primary structural reason for pushing selection () operators down the algebraic query tree as early as possible?

Query Optimization Hard
A. To eliminate the requirement for sorting operations at the root node.
B. To automatically convert highly expensive Cartesian products into efficient inner joins.
C. To reduce the cardinality of intermediate relations prior to executing expensive binary operations like joins.
D. To maximize the utilization of clustered and covering indexes at the leaf level.

60 The System R query optimizer pioneered the use of dynamic programming for determining the optimal join order. For a query joining tables, what is the time complexity of this join enumeration algorithm if it strictly restricts its search space to left-deep trees?

Query Optimization Hard
A.
B.
C.
D.
Previous MCQ
Unit 2
Next MCQ
Unit 4