1Which of the following SQL aggregate functions ignores NULL values by default?
A.COUNT(*)
B.SUM()
C.ROW_NUMBER()
D.ISNULL()
Correct Answer: SUM()
Explanation:Aggregate functions like SUM(), AVG(), MIN(), and MAX() ignore NULL values in their calculations. COUNT(*) counts all rows including those with NULLs.
Incorrect! Try again.
2In SQL, which clause is used to filter the results of an aggregate function?
A.WHERE
B.HAVING
C.GROUP BY
D.ORDER BY
Correct Answer: HAVING
Explanation:The WHERE clause filters rows before aggregation, whereas the HAVING clause filters groups after the aggregate function has been applied.
Incorrect! Try again.
3What is the result of COUNT(column_name) if the column contains the values: [10, NULL, 20, 10, NULL]?
A.2
B.3
C.4
D.5
Correct Answer: 3
Explanation:COUNT(column_name) counts non-NULL values. The values are 10, 20, and 10, totaling 3 non-NULL entries.
Incorrect! Try again.
4Which SQL keyword is used to eliminate duplicate rows before an aggregate function is applied?
A.UNIQUE
B.DIFFERENT
C.DISTINCT
D.SEPARATE
Correct Answer: DISTINCT
Explanation:The DISTINCT keyword is used inside an aggregate function, e.g., COUNT(DISTINCT col), to calculate the aggregate based only on unique values.
Incorrect! Try again.
5Which type of join returns all rows from the left table and the matched rows from the right table, filling NULL where no match is found?
A.INNER JOIN
B.RIGHT OUTER JOIN
C.LEFT OUTER JOIN
D.FULL OUTER JOIN
Correct Answer: LEFT OUTER JOIN
Explanation:A LEFT OUTER JOIN (or LEFT JOIN) preserves all records from the left table. If there is no match in the right table, the result contains NULL values for the right table's columns.
Incorrect! Try again.
6If Table A has rows and Table B has rows, how many rows will the Cartesian Product () yield?
A.
B.
C.
D.
Correct Answer:
Explanation:A Cartesian Product pairs every row of Table A with every row of Table B, resulting in rows.
Incorrect! Try again.
7Which join operation is equivalent to a Cartesian Product followed by a selection based on a condition?
A.CROSS JOIN
B.INNER JOIN
C.NATURAL JOIN
D.UNION
Correct Answer: INNER JOIN
Explanation:Conceptually, an INNER JOIN with a condition (theta join) is a subset of the Cartesian product where only rows satisfying the join predicate are retained.
Incorrect! Try again.
8A join in which a table is joined with itself is called a:
A.Self Join
B.Equi Join
C.Natural Join
D.Outer Join
Correct Answer: Self Join
Explanation:A Self Join is a regular join but the table is joined with itself. Table aliases are required to distinguish the two instances of the table.
Incorrect! Try again.
9Which set operator combines the result sets of two queries and removes duplicate rows?
A.UNION ALL
B.UNION
C.INTERSECT
D.MINUS
Correct Answer: UNION
Explanation:UNION combines results from two queries and performs duplicate elimination. UNION ALL combines them but keeps duplicates.
Incorrect! Try again.
10Which set operator returns only the rows that appear in BOTH result sets?
A.UNION
B.EXCEPT
C.INTERSECT
D.CROSS JOIN
Correct Answer: INTERSECT
Explanation:INTERSECT returns distinct rows that are output by both the left and right input queries.
Incorrect! Try again.
11To use set operators like UNION or INTERSECT, the participating queries must be:
A.Join compatible
B.Union compatible
C.Index compatible
D.View compatible
Correct Answer: Union compatible
Explanation:Union compatibility means both queries must have the same number of attributes and corresponding attributes must have compatible data types.
Incorrect! Try again.
12What is the equivalent of the SQL EXCEPT operator in Oracle SQL?
A.REMOVE
B.DELETE
C.MINUS
D.DISCARD
Correct Answer: MINUS
Explanation:The standard SQL operator is EXCEPT, but Oracle uses the keyword MINUS to return rows from the first query that are not in the second.
Incorrect! Try again.
13Which of the following statements about SQL Views is TRUE?
A.A view physically stores data.
B.A view is a virtual table derived from a query.
C.Views cannot be used in SELECT statements.
D.Views are always faster than the underlying query.
Correct Answer: A view is a virtual table derived from a query.
Explanation:A view does not store data physically (unless it is a materialized view). It is a stored query that presents data as a virtual table.
Incorrect! Try again.
14Which command is used to remove a view definition from the database?
A.DELETE VIEW
B.REMOVE VIEW
C.DROP VIEW
D.TRUNCATE VIEW
Correct Answer: DROP VIEW
Explanation:DROP VIEW view_name is the standard DDL command to remove a view definition from the database schema.
Incorrect! Try again.
15A view is generally considered updatable if:
A.It contains a GROUP BY clause.
B.It uses DISTINCT.
C.It maps 1-to-1 with a single base table without aggregation.
D.It involves a complex multi-table join.
Correct Answer: It maps 1-to-1 with a single base table without aggregation.
Explanation:Views containing aggregates, grouping, distinct, or complex joins are usually read-only. Simple views mapping to one table are updatable.
Incorrect! Try again.
16What is a Correlated Subquery?
A.A subquery that runs once for the entire parent query.
B.A subquery that references a column from the outer query.
C.A subquery used in the FROM clause.
D.A subquery that returns multiple columns.
Correct Answer: A subquery that references a column from the outer query.
Explanation:A correlated subquery depends on values from the outer query and must be re-evaluated for each row processed by the outer query.
Incorrect! Try again.
17Which operator is best suited for a subquery that returns multiple rows to be compared against a single value?
A.=
B.IN
C.IS
D.LIKE
Correct Answer: IN
Explanation:The = operator expects a scalar (single value). The IN operator checks if a value matches any value in a list or subquery result set.
Incorrect! Try again.
18The EXISTS operator returns TRUE if:
A.The subquery returns at least one row.
B.The subquery returns exactly one row.
C.The subquery returns NULL.
D.The subquery returns no rows.
Correct Answer: The subquery returns at least one row.
Explanation:EXISTS checks for the existence of rows. It returns TRUE immediately upon finding the first matching row in the subquery.
Incorrect! Try again.
19In Relational Algebra, which operator is represented by the symbol (sigma)?
A.Projection
B.Selection
C.Rename
D.Cartesian Product
Correct Answer: Selection
Explanation:The Selection operator () is used to select rows that satisfy a specific predicate.
Incorrect! Try again.
20In Relational Algebra, which operator is represented by the symbol (pi)?
A.Projection
B.Selection
C.Join
D.Union
Correct Answer: Projection
Explanation:The Projection operator () is used to choose specific columns (attributes) from a relation, discarding the rest.
Incorrect! Try again.
21Which Relational Algebra operator is used to change the name of a relation or its attributes, denoted by (rho)?
A.Project
B.Rename
C.Select
D.Divide
Correct Answer: Rename
Explanation:The Rename operator () allows us to rename the output relation or its attributes to resolve naming conflicts or for convenience.
Incorrect! Try again.
22The intersection of two relations and () can be expressed using fundamental operators as:
A.
B.
C.
D.
Correct Answer:
Explanation: gives elements in R not in S. Subtracting this result from R leaves only the elements that ARE in S (the intersection).
Incorrect! Try again.
23Which Relational Algebra operation is denoted by the symbol ?
A.Natural Join
B.Division
C.Cartesian Product
D.Selection
Correct Answer: Natural Join
Explanation:The bow-tie symbol () represents a Natural Join, which joins tuples with the same values on common attributes.
Incorrect! Try again.
24The Division operator () is typically used to answer queries containing the keyword:
A.ANY
B.AT LEAST ONE
C.ALL
D.NONE
Correct Answer: ALL
Explanation:Relational Division is used for 'for all' queries, such as finding students who have enrolled in all available courses.
Incorrect! Try again.
25In Relational Algebra, if relation has degree and relation has degree , what is the degree of ?
A.
B.
C.
D.
Correct Answer:
Explanation:The degree (number of columns/attributes) of a Cartesian Product is the sum of the degrees of the participating relations.
Incorrect! Try again.
26Which of the following is NOT a fundamental operator in Relational Algebra (i.e., it can be derived from others)?
A.Selection ()
B.Projection ()
C.Natural Join ()
D.Union ()
Correct Answer: Natural Join ()
Explanation:Natural Join is a derived operator. It can be constructed using Cartesian Product, Selection, and Projection.
Incorrect! Try again.
27What distinguishes an SQL Window Function from a standard Aggregate Function?
A.Window functions collapse rows into a single result.
B.Window functions return a value for every row in the window without collapsing them.
C.Window functions cannot be used with numbers.
D.Window functions must use a GROUP BY clause.
Correct Answer: Window functions return a value for every row in the window without collapsing them.
Explanation:Unlike standard aggregates which group rows into a single output row, window functions compute a value (like a moving average) for each row while preserving the original rows.
Incorrect! Try again.
28Which clause defines the scope (set of rows) for a Window Function?
A.WITH
B.OVER
C.INTO
D.USING
Correct Answer: OVER
Explanation:The OVER clause defines the window partition and ordering for the window function.
Incorrect! Try again.
29What is the difference between RANK() and DENSE_RANK()?
A.RANK() leaves gaps in numbering after ties; DENSE_RANK() does not.
B.DENSE_RANK() leaves gaps in numbering; RANK() does not.
C.RANK() is for numbers; DENSE_RANK() is for strings.
D.They are identical.
Correct Answer: RANK() leaves gaps in numbering after ties; DENSE_RANK() does not.
Explanation:If two rows are tied for 1st, RANK() assigns them both 1 and the next is 3. DENSE_RANK() assigns them both 1 and the next is 2.
Incorrect! Try again.
30Which window function assigns a unique sequential integer to rows within a partition?
A.RANK()
B.NTILE()
C.ROW_NUMBER()
D.LAG()
Correct Answer: ROW_NUMBER()
Explanation:ROW_NUMBER() assigns a unique, sequential number to each row, starting at 1, distinct even for tied values.
Incorrect! Try again.
31In hashing, a situation where two different search keys hash to the same bucket is called a:
A.Union
B.Collision
C.Overflow
D.Probe
Correct Answer: Collision
Explanation:A collision occurs when the hash function maps two distinct search keys to the same bucket address.
Incorrect! Try again.
32Which hashing technique allows the directory size to grow or shrink dynamically as records are added or deleted?
A.Static Hashing
B.Linear Probing
C.Extendible Hashing
D.Chained Hashing
Correct Answer: Extendible Hashing
Explanation:Extendible hashing uses a directory whose depth adjusts dynamically, allowing the hash structure to grow or shrink based on data volume.
C.The number of records exceeds the designed capacity, causing overflow chains.
D.The keys are integers.
Correct Answer: The number of records exceeds the designed capacity, causing overflow chains.
Explanation:Static hashing has a fixed number of buckets. If data grows beyond expectation, overflow buckets (chains) form, degrading access time from to .
Incorrect! Try again.
34A data structure that maps search keys to pointers where the records are stored is called an:
A.Index
B.View
C.Trigger
D.Schema
Correct Answer: Index
Explanation:An Index is an auxiliary data structure used to speed up the retrieval of records in a dataset.
Incorrect! Try again.
35In a Clustered Index, how is the data file sorted?
A.It is not sorted.
B.It is sorted according to the index key.
C.It is sorted by the primary key only.
D.It is sorted randomly.
Correct Answer: It is sorted according to the index key.
Explanation:A Clustered Index determines the physical order of data in the table. Consequently, there can be only one clustered index per table.
Incorrect! Try again.
36What is a Dense Index?
A.An index record appears for every search key value in the file.
B.An index record appears only for some search key values.
C.An index that takes up more space than the data.
D.An index on a non-unique column.
Correct Answer: An index record appears for every search key value in the file.
Explanation:In a dense index, an index entry serves as a pointer for every distinct search key value in the data file.
Incorrect! Try again.
37Which indexing structure consists of a balanced tree where all leaf nodes are at the same level?
A.Binary Search Tree
B.B+ Tree
C.Hash Index
D.Linear List
Correct Answer: B+ Tree
Explanation:A B+ Tree is a self-balancing tree structure that maintains sorted data and ensures all leaf nodes are at the same depth.
Incorrect! Try again.
38In a B+ Tree, pointers to the actual data records are stored in:
A.The root node only.
B.Internal nodes only.
C.Leaf nodes only.
D.Both internal and leaf nodes.
Correct Answer: Leaf nodes only.
Explanation:In a B+ Tree, internal nodes only store keys for navigation. All data pointers are stored in the leaf nodes, which are also linked together.
Incorrect! Try again.
39Which of the following creates a composite index?
A.CREATE INDEX idx ON Table(Col1)
B.CREATE INDEX idx ON Table(Col1, Col2)
C.CREATE INDEX idx ON Table(Col1) AND Table(Col2)
D.CREATE COMPOSITE idx ON Table(Col1)
Correct Answer: CREATE INDEX idx ON Table(Col1, Col2)
Explanation:A composite index is an index on two or more columns of a table.
Incorrect! Try again.
40Hash indices are optimized for which type of query?
A.Range queries (e.g., )
B.Equality queries (e.g., )
C.Pattern matching (e.g., LIKE 'A%')
D.Sorting (ORDER BY)
Correct Answer: Equality queries (e.g., )
Explanation:Hashing maps a key directly to a bucket. It is excellent for exact matches but cannot support range queries because hash values are not sorted.
Incorrect! Try again.
41What is Query Optimization in a DBMS?
A.Writing shorter SQL queries.
B.The process of selecting the most efficient execution plan for a query.
C.Removing unused indexes.
D.Compressing the database storage.
Correct Answer: The process of selecting the most efficient execution plan for a query.
Explanation:Query optimization involves analyzing different ways to execute a query (plans) and choosing the one with the least estimated cost (I/O, CPU).
Incorrect! Try again.
42In Query Optimization, what is a heuristic rule?
A.A rule based on precise mathematical proof.
B.A rule of thumb to reduce the search space, like 'perform selections early'.
C.A rule that uses brute force to check all plans.
D.A rule that requires user intervention.
Correct Answer: A rule of thumb to reduce the search space, like 'perform selections early'.
Explanation:Heuristics are general strategies (like pushing Selection operations down the tree) that generally lead to better performance by reducing intermediate result sizes.
Incorrect! Try again.
43Why is 'Pipelining' used in query execution?
A.To store all intermediate results on disk.
B.To pass the output of one operator directly to the next without waiting for the first to complete entirely.
C.To execute queries in parallel on different machines.
D.To rename columns automatically.
Correct Answer: To pass the output of one operator directly to the next without waiting for the first to complete entirely.
Explanation:Pipelining eliminates the need for temporary files by feeding the output of one operation into the next as it is generated, reducing I/O and latency.
Incorrect! Try again.
44Which information is primarily used by a Cost-Based Optimizer (CBO)?
A.The length of the SQL query string.
B.Database statistics (e.g., table size, histogram of values).
C.The name of the tables.
D.The creation date of the database.
Correct Answer: Database statistics (e.g., table size, histogram of values).
Explanation:A CBO uses statistical metadata (catalog info) about data distribution and volume to estimate the cost of different execution plans.
Incorrect! Try again.
45In Relational Algebra optimization, the expression is equivalent to:
A.
B.
C.
D.
Correct Answer:
Explanation:Applying two selection conditions in sequence is equivalent to selecting rows that satisfy both conditions simultaneously (AND logic).
Incorrect! Try again.
46What is a Materialized View?
A.A standard virtual view.
B.A view whose result is physically computed and stored on disk.
C.A view that is only available in memory.
D.A view that cannot be updated.
Correct Answer: A view whose result is physically computed and stored on disk.
Explanation:Unlike standard views, materialized views store the actual result set. This speeds up read queries but requires maintenance/refreshing when base data changes.
Incorrect! Try again.
47The 'Scan' operation in query processing refers to:
A.Reading every record in a relation.
B.Using an index to find a record.
C.Sorting the relation.
D.Joining two relations.
Correct Answer: Reading every record in a relation.
Explanation:A table scan (or file scan) involves reading the entire file containing the relation, which is usually the most expensive access method for small lookups.
Incorrect! Try again.
48In the context of Join Optimization, if we join a small table with a very large table, which algorithm is generally preferred if an index exists on the large table?
A.Nested Loop Join
B.Index Nested Loop Join
C.Block Nested Loop Join
D.Cartesian Product
Correct Answer: Index Nested Loop Join
Explanation:If the inner loop (large table) has an index, we can look up matches directly rather than scanning the whole table for every row in the outer (small) table.
Incorrect! Try again.
49Which transformation rule allows moving a Projection () down the query tree?
A.Projection Cascade
B.Selection Pushdown
C.Join Commutativity
D.Associativity
Correct Answer: Projection Cascade
Explanation:Projection Cascade allows projections to be performed earlier (pushed down) to reduce the width of tuples (number of columns) processed in intermediate steps.
Incorrect! Try again.
50What is the primary goal of the 'Selection Pushdown' optimization technique?
A.To sort the data earlier.
B.To reduce the number of rows (cardinality) as early as possible.
C.To increase the number of joins.
D.To convert the query into a view.
Correct Answer: To reduce the number of rows (cardinality) as early as possible.
Explanation:By performing selections (filtering) as early as possible (close to the leaf nodes/tables), the system processes fewer rows in subsequent joins and operations.