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. MAX()
B. COUNT()
C. SUM()
D. TOTAL()

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

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

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. FULL OUTER JOIN
C. RIGHT OUTER JOIN
D. INNER 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. CROSS JOIN
B. INNER JOIN
C. FULL OUTER JOIN
D. LEFT JOIN

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

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

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

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

7 What is a database view?

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

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

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

9 What is a subquery in SQL?

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

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. IS NULL
B. BETWEEN
C. LIKE
D. IN

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. Join ()
C. Selection ()
D. Projection ()

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

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

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

Window functions Easy
A. GROUP BY
B. PARTITION OF
C. OVER()
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. RANK()
B. DENSE_RANK()
C. NTILE()
D. ROW_NUMBER()

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 join multiple tables together
D. To sort data alphabetically in tables

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 eliminates the need for primary keys
B. It speeds up data retrieval operations
C. It automatically encrypts the data
D. It reduces the total storage space required

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

Indexing Easy
A. B-Tree (and its variants like B+ Tree)
B. Queue
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 write SQL queries automatically for the user
B. To determine the most efficient execution plan for a given query
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. Rule-based optimizer
B. Buffer-based optimizer
C. Cost-based optimizer
D. Syntax-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 GROUP BY Region WHERE AVG(Amount) > 500
B. SELECT Region FROM Sales HAVING AVG(Amount) > 500
C. SELECT Region FROM Sales GROUP BY Region HAVING AVG(Amount) > 500
D. SELECT Region FROM Sales WHERE AVG(Amount) > 500 GROUP BY Region

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. 5, NULL
D. 3, 600

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. LEFT OUTER JOIN
B. INNER JOIN
C. NATURAL JOIN
D. RIGHT OUTER JOIN

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

Sql joins Medium
A. To return the Cartesian product of two identical tables.
B. To automatically eliminate duplicate records from a table.
C. To combine rows from two different tables based on a related column.
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 removes duplicates, making it slower than UNION.
B. UNION only works with numeric data types.
C. UNION ALL automatically sorts the output, whereas UNION does not.
D. UNION removes duplicates, making it generally slower than UNION ALL.

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

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

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

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

28 What is a Materialized View?

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

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

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

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. ANY
B. ALL
C. IN
D. EXISTS

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. Projection ()
C. Selection ()
D. Cartesian Product ()

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

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

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. Both will assign rank 3.
B. RANK() assigns 3, while DENSE_RANK() assigns 2.
C. Both will assign rank 2.
D. RANK() assigns 2, while DENSE_RANK() assigns 3.

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

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

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. Using overflow buckets (chaining)
B. Linear probing only
C. Deleting the older record to accommodate the new one
D. Creating a new hash table and rehashing all entries

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 fixed number of buckets that cannot be changed.
C. It guarantees that all data retrieval will always take time.
D. It uses a directory of pointers that doubles in size when a bucket overflows.

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

Indexing Medium
A. B+ trees store data pointers in internal nodes, saving space.
B. B+ trees allow faster insertion times because they do not require balancing.
C. B+ trees use hash functions internally to locate records in time.
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 is an index that stores data records in an unordered heap file.
B. A table can have multiple clustered indexes created on different columns.
C. It uses a hash table structure rather than a tree structure.
D. It determines the physical sort order of the data rows in the table.

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 reduces the size of intermediate relations before costly operations like joins are performed.
B. It converts all inner joins to outer joins.
C. It bypasses the need for using database indexes.
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. The order in which the SQL query clauses are written by the developer.
B. The amount of free space remaining on the hard drive.
C. Database statistics such as table sizes, tuple counts, and index distributions.
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. The query throws a syntax error because NULL cannot be used in a GROUP BY clause.
B. They are ignored and omitted from the result set entirely.
C. They are grouped into a single row with a DeptID of NULL and a sum of 700.
D. They produce two separate rows with DeptID as NULL, having sums 300 and 400 respectively.

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 NULL; SUM(col) returns 0.
B. COUNT(*) returns NULL; SUM(col) returns NULL.
C. COUNT(*) returns 0; SUM(col) returns 0.
D. COUNT(*) returns 0; SUM(col) returns NULL.

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 WHERE clause condition acts after the join, effectively converting the LEFT JOIN into an INNER JOIN.
C. The ON clause throws an error because filtering conditions must be placed in the WHERE clause.
D. They produce identical results and query execution plans.

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

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. UNION and EXCEPT have higher precedence than INTERSECT
B. EXCEPT first: R UNION (S INTERSECT (T EXCEPT U))
C. Left to right: (((R UNION S) INTERSECT T) EXCEPT U)
D. INTERSECT first: (R UNION (S INTERSECT T)) EXCEPT U

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

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

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 containing a derived table in the FROM clause.
B. A view defined using a set operator like UNION.
C. A view built on a single base table without GROUP BY, aggregate functions, or distinct constraints.
D. A view utilizing the DISTINCT keyword.

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

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. Uncorrelated subquery
B. Scalar subquery
C. Correlated subquery
D. Derived table 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. A left outer join equivalent relation containing NULLs.
B. An execution error due to disjoint domains.
C. A Cartesian product of and .
D. An empty relation with schema .

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

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

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 the window function over the entire partition, effectively treating it as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
C. It automatically orders the partition by the primary key.
D. It evaluates strictly up to the current row based on a non-deterministic internal read order.

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 functions are logically evaluated after the WHERE and HAVING clauses have already determined the result set.
D. Because window frames cannot interpret boolean logical operators.

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

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

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 reduce the cardinality of intermediate relations prior to executing expensive binary operations like joins.
C. To maximize the utilization of clustered and covering indexes at the leaf level.
D. To automatically convert highly expensive Cartesian products into efficient inner joins.

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