Back to Subject

Unit 3 - Practice Quiz

INT306 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. SUM()
B. COUNT()
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. FULL OUTER JOIN
B. INNER JOIN
C. RIGHT OUTER JOIN
D. LEFT 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. CROSS JOIN
B. FULL OUTER JOIN
C. LEFT JOIN
D. INNER JOIN

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

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

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

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

7 What is a database view?

views Easy
A. An index used to speed up queries
B. A physical table storing redundant data
C. A primary key constraint
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. TRUNCATE VIEW
B. DELETE VIEW
C. DROP VIEW
D. REMOVE VIEW

9 What is a subquery in SQL?

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

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. LIKE
B. IS NULL
C. BETWEEN
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. Projection ()
B. Cross Product ()
C. Selection ()
D. Join ()

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

relational algebra Easy
A. Projection ()
B. Intersection ()
C. Selection ()
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. HAVING
B. PARTITION OF
C. OVER()
D. GROUP BY

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. DENSE_RANK()
D. RANK()

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

Hashing Easy
A. To join multiple tables together
B. To create temporary virtual tables
C. To map search keys to specific bucket or block addresses
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 deadlock
B. A syntax error
C. A query optimization
D. A hash collision

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 automatically encrypts the data
C. It speeds up data retrieval operations
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. Stack
B. Queue
C. Linked List
D. B-Tree (and its variants like B+ Tree)

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

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

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. Buffer-based optimizer
B. Rule-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 WHERE AVG(Amount) > 500 GROUP BY Region
B. SELECT Region FROM Sales GROUP BY Region HAVING AVG(Amount) > 500
C. SELECT Region FROM Sales GROUP BY Region WHERE AVG(Amount) > 500
D. SELECT Region FROM Sales 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. 5, NULL
C. 3, 600
D. 3, 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. INNER JOIN
D. LEFT OUTER JOIN

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

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

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

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

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

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

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

views Medium
A. When the view contains a simple SELECT from a single table.
B. When the view contains a GROUP BY clause or aggregate functions.
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 system-level view that restricts users from executing DML statements.
B. A view that temporarily holds data only during an active user session.
C. A physical copy of the query results stored on disk to improve read performance.
D. A virtual table whose contents are generated dynamically at runtime every time it is queried.

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

subqueries Medium
A. A correlated subquery is faster because it does not depend on the outer query.
B. A correlated subquery can only be used in the FROM clause.
C. A correlated subquery executes exactly once and passes its result to the outer query.
D. A correlated subquery references columns from the outer query and must be evaluated for each row of 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. 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. Selection ()
C. Projection ()
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. Theta Join ()
B. Natural Join ()
C. Set Difference ()
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 2, while DENSE_RANK() assigns 3.
B. Both will assign rank 3.
C. Both will assign rank 2.
D. RANK() assigns 3, while DENSE_RANK() assigns 2.

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

Window functions Medium
A. To sort the entire result set in descending order.
B. To physically partition the database tables across multiple disks.
C. To filter rows before the window function is applied.
D. To divide the result set into discrete groups over which 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. Creating a new hash table and rehashing all entries
C. Deleting the older record to accommodate the new one
D. Linear probing only

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

Hashing Medium
A. It guarantees that all data retrieval will always take time.
B. It relies entirely on overflow chains to handle all collisions.
C. It uses a fixed number of buckets that cannot be changed.
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 only store keys in internal nodes, and all leaf nodes are linked, optimizing range queries.
B. B+ trees use hash functions internally to locate records in time.
C. B+ trees allow faster insertion times because they do not require balancing.
D. B+ trees store data pointers in internal nodes, saving space.

38 What defines a Clustered Index in a relational database?

Indexing Medium
A. A table can have multiple clustered indexes created on different columns.
B. It is an index that stores data records in an unordered heap file.
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 reduces the number of columns in the final result.
D. It bypasses the need for using database indexes.

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 amount of free space remaining on the hard drive.
B. The specific names of the primary key columns.
C. The order in which the SQL query clauses are written by the developer.
D. Database statistics such as table sizes, tuple counts, and index distributions.

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

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 0.
B. COUNT(*) returns NULL; SUM(col) returns 0.
C. COUNT(*) returns 0; SUM(col) returns NULL.
D. COUNT(*) returns NULL; 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 WHERE clause condition acts after the join, effectively converting the LEFT JOIN into an INNER JOIN.
B. They produce identical results and query execution plans.
C. The ON clause throws an error because filtering conditions must be placed in the WHERE clause.
D. The ON clause condition applies the filter to table A as well, eliminating rows from A before the 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. Yes, by joining the table with itself on inequality conditions.
B. No, because self-joins only support equi-join (=) conditions.
C. Yes, by using a CROSS JOIN combined with a WHERE clause.
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. INTERSECT first: (R UNION (S INTERSECT T)) EXCEPT U
B. UNION and EXCEPT have higher precedence than INTERSECT
C. EXCEPT first: R UNION (S INTERSECT (T EXCEPT U))
D. Left to right: (((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. 2
B. 3
C. 4
D. 1

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 succeeds because LOCAL CHECK OPTION only enforces V2's condition () and ignores V1 since V1 lacks a check option.
B. Insertion fails because LOCAL CHECK OPTION inherently checks all underlying view conditions regardless of how they were defined.
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 built on a single base table without GROUP BY, aggregate functions, or distinct constraints.
C. A view utilizing the DISTINCT keyword.
D. A view defined using a set operator like UNION.

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 subquery will throw a runtime syntax error.
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 query will return all rows from table A, effectively ignoring the NOT IN clause.

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

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

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

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

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. 3
B. 1
C. 2
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 return multiple rows for a single scalar filter condition.
B. Because window frames cannot interpret boolean logical operators.
C. Because window functions are logically evaluated after the WHERE and HAVING clauses have already determined the result set.
D. Because they require an explicit GROUP BY clause to function.

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 global depth strictly exceeds the local depth of the overflowing bucket.
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 maximize the utilization of clustered and covering indexes at the leaf level.
B. To eliminate the requirement for sorting operations at the root node.
C. To automatically convert highly expensive Cartesian products into efficient inner joins.
D. To reduce the cardinality of intermediate relations prior to executing expensive binary operations like 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