RPSC MCQs SQL 2011.pdf

RPSC PROGRAMMR IMPORTANT MCQs SQL and database : 1. Which of the following SQL query uses a self-join? a) SELECT * FROM employees e1 JOIN employees e2 ON e1.department_id = e2.department_id; b) SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE location = 'New York'); c) SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id; d) SELECT * FROM employees WHERE salary > 50000; Answer: A Explanation: A self-join is when a table is joined with itself. In option A, the employees table is joined with itself using two aliases (e1 and e2). This is an example of a self-join. 2. What is the result of the following SQL query? SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-06-30'; a) Returns all orders placed between January 1, 2023, and June 30, 2023. b) Returns the total count of orders placed between January 1, 2023, and June 30, 2023. c) Returns the sum of order amounts between January 1, 2023, and June 30, 2023. d) Returns orders placed after June 30, 2023. Answer: B Explanation: The COUNT(*) function returns the total number of rows in the result set. The BETWEEN clause filters the rows within the specified date range. Therefore, this query counts all orders between January 1 and June 30, 2023. 3. Which SQL clause is used to remove duplicate rows from the result set? a) DISTINCT b) UNIQUE c) DELETE d) REMOVE Answer: A Explanation: The DISTINCT keyword is used to return unique values in the result set, eliminating duplicates. 4. What is the purpose of the HAVING clause in SQL? a) It filters records after GROUP BY is applied. b) It groups the records based on a condition. c) It updates the values after GROUP BY. d) It deletes the rows from a table. Answer: A Explanation: The HAVING clause is used to filter groups of rows after the GROUP BY clause. It's similar to WHERE but applies to groups rather than individual rows. 5. In SQL, what does the following query do? SELECT employee_id, MAX(salary) FROM employees GROUP BY employee_id HAVING MAX(salary) > 50000; A) Selects the maximum salary of employees whose salary is greater than 50,000. B) Selects the employee ID and maximum salary of employees grouped by their employee ID. C) Selects employee ID and maximum salary for employees whose salary exceeds 50,000. D) Deletes the employee data where salary is greater than 50,000. Answer: C Explanation: This query selects the employee_id and their maximum salary where the maximum salary exceeds 50,000. The HAVING clause filters the groups after grouping by employee_id. 6. What happens if you execute the following SQL query? DELETE FROM employees WHERE 1 = 1; A) Deletes a single employee. B) Deletes all employees. C) No rows will be deleted. D) The query will throw an error. Answer: B Explanation: The condition 1 = 1 is always true, so the query deletes all rows in the employees table. 7. Which of the following is true regarding the UNION operator in SQL? A) Combines the result sets of two queries and removes duplicates. B) Combines the result sets of two queries without removing duplicates. C) Combines only unique rows from the first query with the second query. D) Removes duplicates from the second query only. Answer: A Explanation: The UNION operator combines the result sets of two queries and removes duplicates from the final result set. 8. What is the purpose of the COALESCE function in SQL? A) Returns the first non-null value in a list of expressions. B) Concatenates multiple string values. C) Joins two tables based on a common column. D) Calculates the sum of values. Answer: A Explanation: The COALESCE function returns the first non-null value from a list of expressions. It's used to handle NULL values. 9. Which SQL query would return the third highest salary from the employees table? A) SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); B) SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 2, 1; C) SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2; D) SELECT TOP 3 salary FROM employees ORDER BY salary DESC; Answer: C Explanation: The LIMIT 1 OFFSET 2 syntax skips the first two rows (highest and second- highest salary) and returns the third row, which is the third-highest salary. 10. In SQL, which of the following constraints ensures that all values in a column are unique? A) PRIMARY KEY B) FOREIGN KEY C) CHECK D) UNIQUE Answer: D Explanation: The UNIQUE constraint ensures that all values in a column are distinct, preventing duplicate values. 11. Which SQL operation is used to change a column's data type? A) MODIFY B) ALTER C) UPDATE D) CHANGE Answer: B Explanation: The ALTER command is used to modify the structure of a table, such as changing the data type of a column. 12. Which SQL command is used to remove a table from the database? A) DROP B) DELETE C) TRUNCATE D) REMOVE Answer: A Explanation: The DROP command completely removes a table from the database, including its structure and data. 13. What does the following SQL query return? SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5; A) Departments with fewer than 5 employees. B) All departments and their employee counts. C) Departments with more than 5 employees. D) Departments and their respective total salaries. Answer: C Explanation: This query groups employees by department_id and filters those departments that have more than 5 employees using the HAVING clause. 14. What will happen if the following SQL query is executed? SELECT * FROM employees WHERE employee_id = NULL; A) Returns all employees with NULL employee ID. B) Returns no rows. C) Throws an error. D) Updates the employees with NULL employee ID. Answer: B Explanation: In SQL, NULL represents an unknown value, and comparisons with NULL using = will always return FALSE. The correct way to check for NULL is using IS NULL. 15. Which SQL query correctly performs an INNER JOIN between two tables customers and orders? A) SELECT * FROM customers JOIN orders ON customers.customer_id = orders.customer_id; B) SELECT * FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id; C) SELECT * FROM customers, orders WHERE customers.customer_id = orders.customer_id; D) SELECT * FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id; Answer: A Explanation: An INNER JOIN returns rows that have matching values in both tables. The syntax JOIN ON is used to specify the join condition. 16. Which SQL function is used to return the total number of rows in a result set? A) COUNT() B) SUM() C) AVG() D) MAX() Answer: A Explanation: The COUNT() function counts the number of rows in a result set. 17. What is the difference between the DELETE and TRUNCATE commands in SQL? A) DELETE removes rows one at a time, while TRUNCATE removes all rows at once. B) TRUNCATE can delete specific rows, while DELETE removes all rows. C) Both commands perform the same function. D) DELETE is faster than TRUNCATE. Answer: A Explanation: The DELETE command removes rows one at a time and logs each deletion, whereas TRUNCATE removes all rows at once without logging individual row deletions, making it faster. 18. In SQL, which of the following is true about the EXISTS operator? A) It checks if any row is returned by a subquery. B) It removes duplicate rows in a query. C) It checks if all rows meet a certain condition. D) It performs an outer join between two tables. Answer: A Explanation: The EXISTS operator checks whether a subquery returns any rows. If it does, the EXISTS condition is true. 19. What is the use of the GROUP BY clause in SQL? A) It is used to group rows that have the same values in specified columns. B) It filters rows after an aggregation function. C) It joins two tables based on a condition. D) It updates rows in the table. Answer: A Explanation: The GROUP BY clause is used to group rows that have the same values in specified columns, often used with aggregate functions like SUM() or COUNT(). 20. Which of the following SQL commands is used to create a view? A) CREATE VIEW B) CREATE TABLE C) CREATE SCHEMA D) CREATE INDEX Answer: A Explanation: The CREATE VIEW command is used to create a virtual table based on the result of an SQL query. This view can be used like a table in further queries. 21. Which of the following is true about an INNER JOIN? A) Returns rows that have matching values in both tables. B) Returns all rows from the left table, and the matching rows from the right table. C) Returns all rows from both tables, with NULL in places where no match exists. D) Returns rows that only exist in the left table. Answer: A Explanation: INNER JOIN returns rows when there is a match in both the joined tables. It does not include unmatched rows from either table. 22. What is the result of a FULL OUTER JOIN between two tables A and B? A) Returns only rows that match between A and B. B) Returns rows that exist in either A or B, including non-matching rows. C) Returns only rows that exist in A. D) Returns only rows that exist in B. Answer: B Explanation: A FULL OUTER JOIN returns all rows when there is a match in either table. It includes rows from both tables, even if they do not have matching rows in the other table, filling with NULL where no match exists. 23. Which SQL query demonstrates the use of a self-join? A) SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id; B) SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.employee_id; C) SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments); D) SELECT * FROM employees e JOIN salaries s ON e.employee_id = s.employee_id; Answer: B Explanation: A self-join is a join where a table is joined with itself. In option B, employees is joined with itself using two aliases (e1 and e2). 24. What does a LEFT JOIN return? A) All rows from the left table and matching rows from the right table. B) All rows from the right table and matching rows from the left table. C) Only rows with matches in both tables. D) Only rows from the left table that do not have matches in the right table. Answer: A Explanation: A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL is returned for columns from the right table. 25. What is the purpose of a CROSS JOIN? A) To return all rows from the left table with matching rows from the right table. B) To return all possible combinations of rows from both tables. C) To return only the distinct combinations of rows from both tables. D) To return rows from the left table that have no match in the right table. Answer: B Explanation: A CROSS JOIN returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows between the two tables. 26. What is the main characteristic of a correlated subquery? A) It runs once and provides the result to the outer query. B) It references columns from the outer query. C) It can return multiple rows and columns. D) It does not depend on the outer query. Answer: B Explanation: A correlated subquery references columns from the outer query, meaning the subquery is executed once for each row of the outer query. 27. Which of the following is true about a subquery in SQL? A) A subquery can only be used in the WHERE clause. B) A subquery must always return a single value. C) A subquery can be used in any clause that allows expressions, like SELECT, FROM, or WHERE. D) A subquery cannot be nested inside another subquery. Answer: C Explanation: Subqueries can be used in the SELECT, FROM, or WHERE clauses, as long as they return results that are valid in the context of the outer query. 28. What does the following query return? SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments WHERE course_id = 101); A) Students who are not enrolled in course 101. B) Students who are enrolled in course 101. C) All students. D) Students who are enrolled in any course except 101. Answer: B Explanation: This query returns the names of students whose id exists in the enrollments table with a course_id of 101. It effectively lists the students enrolled in course 101. 29. Which of the following queries demonstrates a correlated subquery? A) SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); B) SELECT e.name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.manager_id = e.employee_id); C) SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York'); D) SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 2); Answer: B Explanation: A correlated subquery is one where the subquery depends on the outer query. In option B, the subquery references e.employee_id from the outer query, making it a correlated subquery. 30. What is the result of a subquery that returns multiple rows in a WHERE clause? A) The query will throw an error. B) The query will return multiple rows, each corresponding to the subquery result. C) The query will return no rows. D) The query can only succeed if used with IN or a set operation. Answer: D Explanation: If a subquery returns multiple rows, it can only be used in combination with operators like IN, ANY, or ALL. Using it with comparison operators (like =, >, etc.) will result in an error unless the subquery returns a single row. 31. Which of the following operators can be used with subqueries that return multiple rows? A) = B) IN C) BETWEEN D) LIKE Answer: B Explanation: The IN operator is used to compare a value with a set of values returned by a subquery. Other operators like =, BETWEEN, or LIKE are not valid for subqueries that return multiple rows. 32. What does the following SQL query do? SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); A) Selects all employees with the highest salary. B) Selects employees whose salary is greater than the average salary. C) Selects employees who have the maximum salary. D) Selects all employees. Answer: C Explanation: The query selects the employee(s) whose salary matches the maximum salary in the employees table. The subquery returns the maximum salary, and the outer query selects employees with that salary. 33. What is the key difference between a subquery and a JOIN? A) Subqueries are faster than JOINs. B) JOINs can return more columns than subqueries. C) Subqueries can only return one value, while JOINs return multiple rows. D) JOINs combine tables horizontally, while subqueries provide a scalar or table result to the outer query. Answer: D Explanation: A JOIN combines tables horizontally, meaning columns from both tables are combined in the result set. Subqueries, on the other hand, can return a scalar (single value) or table result to be used in the outer query. 34. What is the output of the following SQL query? SELECT department_id, (SELECT COUNT(*) FROM employees WHERE employees.department_id = departments.department_id) AS num_employees FROM departments; A) Returns the total number of employees in each department. B) Returns the department ID and employee names. C) Returns the total number of departments. D) Returns employees with no departments. Answer: A Explanation: This query returns the department_id from the departments table and a count of employees in each department by using a subquery to count rows in the employees table where department_id matches. 35. Which of the following is true about a subquery in the FROM clause? A) It acts like a table. B) It must return only one row. C) It cannot be used in a JOIN. D) It Answer: A) It acts like a table. Explanation: A subquery in the FROM clause is treated as a derived table or inline view, allowing you to reference its results as if it were a regular table. This enables complex queries to be simplified by breaking them down into manageable parts. 36. Which of the following is a requirement for a table to be in First Normal Form (1NF)? A) No partial dependency. B) No transitive dependency. C) All attributes must have atomic (indivisible) values. D) No repeating groups or arrays. Answer: C Explanation: For a table to be in 1NF, all attributes must have atomic values, meaning each value must be indivisible. This eliminates repeating groups and ensures that each cell in a table holds a single value. 37. Which of the following is not a characteristic of Second Normal Form (2NF)? A) It is in First Normal Form (1NF). B) It has no partial dependency of any column on the primary key. C) It allows transitive dependency. D) It ensures that all non-key attributes are fully functional dependent on the primary key. Answer: C Explanation: A table in 2NF must not have partial dependencies on any part of the primary key, and it is also required to be in 1NF. It also does not allow transitive dependencies; that condition is checked in 3NF. 38. What is a transitive dependency? A) A dependency where a non-key attribute depends on another non-key attribute. B) A dependency where a non-key attribute depends on the primary key. C) A dependency that violates the 1NF. D) A functional dependency that cannot exist in 3NF. Answer: A Explanation: A transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than directly on the primary key. This type of dependency must be eliminated to achieve Third Normal Form (3NF). 39. Which of the following describes a table that is in Boyce-Codd Normal Form (BCNF)? A) It is in 3NF and has no partial or transitive dependencies. B) Every determinant is a candidate key. C) It contains only atomic values. D) It allows for partial dependency on the primary key. Answer: B Explanation: A table is in BCNF if it is in 3NF and every determinant (attribute on the left side of a functional dependency) is a candidate key. This ensures no anomalies in data operations. 40. Which of the following is an example of a violation of 2NF? A) A table with a composite primary key and a non-key attribute that depends only on part of the key. B) A table where all non-key attributes depend on the primary key. C) A table where there are no repeating groups. D) A table that has a primary key and a non-key attribute depending on another non-key attribute. Answer: A Explanation: A violation of 2NF occurs when a non-key attribute is functionally dependent on only part of a composite primary key, leading to redundancy and potential update anomalies. 41. Which of the following is a characteristic of a table in Third Normal Form (3NF)? A) It must be in Second Normal Form (2NF). B) It allows transitive dependency. C) It has at least one non-key attribute. D) It allows partial dependency. Answer: A Explanation: A table in 3NF must first be in 2NF and must not have any transitive dependencies, meaning all non-key attributes must depend directly on the primary key. 42. Which of the following does NOT indicate a need for normalization? A) Redundant data. B) Inconsistent data. C) Inability to enforce data integrity. D) Large data types. Answer: D Explanation: Large data types do not directly indicate a need for normalization. However, redundant, inconsistent data, and difficulties in enforcing data integrity typically signal the need for normalization to eliminate anomalies. 43. If a relation is in BCNF, which of the following is guaranteed? A) There are no composite keys. B) There are no transitive dependencies. C) Every non-key attribute is fully functionally dependent on the primary key. D) There are no anomalies during data insertion, deletion, or update. Answer: D Explanation: A relation in BCNF is guaranteed to have no anomalies during data operations such as insertion, deletion, or update. This is because all dependencies are properly managed, ensuring data integrity. 44. What is the main purpose of normalization in a relational database? A) To increase the number of tables. B) To eliminate redundancy and improve data integrity. C) To make queries faster. D) To ensure that all data types are uniform. Answer: B Explanation: The main purpose of normalization is to eliminate redundancy and enhance data integrity. By organizing data into separate tables and establishing relationships, normalization minimizes the risk of inconsistent data. 45. Consider the following relation: Student (StudentID, CourseID, Instructor, InstructorPhone) What is a potential issue in this relation? A) The relation is not in 1NF. B) The relation is not in 2NF. C) The relation is not in 3NF due to transitive dependency. D) There is no issue; it is well-structured. Answer: C Explanation: This relation has a transitive dependency because InstructorPhone is dependent on Instructor, which is a non-key attribute. Therefore, this relation violates 3NF. 46. Which of the following is not a property of a database transaction? A) Atomicity B) Consistency C) Durability D) Isolation Level Answer: D Explanation: The ACID properties of transactions are Atomicity, Consistency, Isolation, and Durability. "Isolation Level" refers to the degree to which a transaction is isolated from others, but it is not one of the four fundamental ACID properties. 47. Which ACID property ensures that once a transaction has been committed, it will remain so even in the event of a system failure? A) Atomicity B) Consistency C) Durability D) Isolation Answer: C Explanation: Durability ensures that after a transaction has been committed, its effects are permanent and will persist, even in case of a system crash or failure. 48. What does the Atomicity property of a transaction guarantee? A) All parts of a transaction are completed, or none are. B) The database will remain in a consistent state after a transaction. C) No two transactions can interfere with each other. D) All changes made during a transaction are permanent once committed. Answer: A Explanation: Atomicity ensures that a transaction is treated as a single "unit of work." Either all the operations in the transaction are performed, or none of them are, ensuring no partial updates. 49. Which of the following SQL command is used to start a transaction explicitly? A) START TRANSACTION B) BEGIN TRANSACTION C) COMMIT D) ROLLBACK Answer: B Explanation: The BEGIN TRANSACTION or simply BEGIN command is used to explicitly start a transaction. START TRANSACTION can also be used in some databases. 50. In a database, which of the following is an example of a "lost update" problem? A) Two transactions are reading the same data and updating it without awareness of each other's actions. B) A transaction reads data while another is in the process of writing it. C) A transaction reads dirty data from another uncommitted transaction. D) Two transactions are writing the same data, and one overwrites the other’s changes. Answer: D Explanation: A lost update occurs when two transactions update the same data simultaneously, but one transaction's changes overwrite the other, causing loss of data. 51. Which locking mechanism allows multiple transactions to read a resource but restricts them from writing to it? A) Shared Lock B) Exclusive Lock C) Deadlock D) Two-phase Locking Answer: A Explanation: A shared lock allows multiple transactions to read a resource, but it prevents any of them from writing to the resource until the lock is released. 52. Which type of lock must a transaction obtain if it wants to update or write to a resource? A) Shared Lock B) Read Lock C) Write Lock D) Exclusive Lock Answer: D Explanation: An exclusive lock is required when a transaction needs to update or write to a resource, ensuring that no other transaction can read or write to the resource during that time. 53. What is the purpose of two-phase locking (2PL) in database systems? A) To ensure atomicity of transactions. B) To prevent deadlock situations. C) To ensure serializability of concurrent transactions. D) To allow transactions to read uncommitted data. Answer: C Explanation: Two-phase locking (2PL) is a concurrency control mechanism that ensures serializability of transactions by having two distinct phases: a growing phase where locks are acquired and a shrinking phase where locks are released. 54. What is a potential drawback of using two-phase locking? A) Increased consistency of the database. B) Possible deadlock situations. C) Uncommitted data being read. D) Atomicity not being maintained. Answer: B Explanation: A drawback of two-phase locking is the possibility of deadlocks, where two or more transactions are waiting for each other to release locks, resulting in a situation where none can proceed. 55. Which ACID property is violated when uncommitted data from one transaction is read by another transaction? - A) Isolation - B) Consistency - C) Durability - D) Atomicity Answer: A Explanation “Isolation” is violated when a transaction reads uncommitted data from another transaction. This is often referred to as the “dirty read” problem. 56. In a deadlock situation, what is typically the solution? - A) Restart all transactions. - B) Use shared locks only. - C) Roll back one of the transactions. - D) Use read locks instead of write locks. Answer: C Explanation: In a deadlock, one of the transactions is typically rolled back (chosen based on priority or timing) to resolve the deadlock and allow the other transactions to proceed. 57. Which of the following represents a dirty read in transaction processing? - A) Reading data that has been committed by another transaction. - B) Reading data from a table while another transaction is updating it. - C) Reading uncommitted data from another transaction. - D) Writing data that violates integrity constraints. Answer: C Explanation: A “dirty read” occurs when a transaction reads data that has been modified by another transaction that has not yet been committed, which can lead to inconsistencies. 58. Which isolation level provides the least concurrency control but the highest level of performance? - A) Serializable - B) Repeatable Read - C) Read Committed - D) Read Uncommitted Answer: D Explanation: “Read Uncommitted” is the least restrictive isolation level, allowing transactions to read uncommitted data. This improves performance but risks issues like dirty reads. 59. Which isolation level guarantees that a transaction will not see any changes made by other transactions until it is committed? - A) Read Uncommitted - B) Serializable - C) Repeatable Read - D) Read Committed Answer: D Explanation: “Read Committed” ensures that a transaction only reads data that has been committed, preventing dirty reads but allowing non-repeatable reads and phantom reads. 60. What is the highest isolation level in SQL that guarantees complete serializability? - A) Serializable - B) Repeatable Read - C) Read Committed - D) Snapshot Answer: A Explanation: “Serializable” is the highest isolation level that guarantees complete serializability, meaning that transactions are executed in a manner that ensures the same result as if they were executed sequentially, one after the other. 61. What type of lock is typically involved in a deadlock situation? - A) Exclusive Lock - B) Shared Lock - C) Intent Lock - D) Both Exclusive and Shared Locks Answer: D Explanation: “Deadlocks” typically involve both “exclusive” and “shared locks”. Two transactions might be holding different locks and waiting for the other to release a lock, leading to a deadlock. 62. Which of the following methods is commonly used to prevent deadlocks in transactions? - A) Transaction Timeouts - B) Using the highest isolation level - C) Two-phase locking - D) Using fewer transactions Answer : A Explanation: “Transaction timeouts” are a common method to prevent deadlocks. If a transaction takes too long to acquire a lock, it is automatically rolled back to avoid deadlock. 63. Which problem occurs when a transaction reads the same row multiple times and sees different data each time? - A) Phantom Read - B) Non-repeatable Read - C) Dirty Read - D) Serialization Error Answer: B Explanation: A “non-repeatable read” occurs when a transaction reads the same row multiple times and sees different data due to another transaction committing changes in between the reads. 64. In which isolation level are phantom reads possible? - A) Serializable - B) Repeatable Read - C) Read Committed - D) Read Uncommitted Answer: C Explanation: In the “Read Committed” isolation level, “phantom reads” can occur. Phantom reads happen when a transaction reads a set of rows 65. Which ACID property is most closely related to the concept of locking in transaction management? - A) Atomicity - B) Consistency - C) Isolation - D) Durability Answer: C Explanation: “Isolation” is the ACID property most related to “locking”, as it ensure transactions are isolated from each other, often achieved by using locks to prevent concurrent access to the same data. Relational Algebra 1. Which operation in relational algebra returns a relation that contains all tuples that are present in both relations? A) Union B) Intersection C) Difference D) Cartesian Product Answer: B Explanation: The Intersection operation returns tuples that are present in both relations, whereas Union combines all tuples and Difference returns those tuples that are present in one relation but not in the other. 2. Which relational algebra operation is used to select specific columns from a table? A) Select (σ) B) Project (π) C) Join D) Union (∪) Answer: B Explanation: Project (π) operation selects specific columns from a relation, whereas Select (σ) chooses specific rows based on a condition. 3. What is the result of a Cartesian product in relational algebra? A) A relation with all rows that satisfy a given condition B) A relation with tuples formed by every combination of tuples from two relations C) A relation that contains only common tuples from two relations D) A relation with tuples from one relation that are not in the other Answer: B Explanation: The Cartesian product (also called Cross Product) combines each tuple of one relation with every tuple of another, generating a relation with all possible combinations of rows from the two relations. 4. Which of the following is NOT a basic operation in relational algebra? A) Select B) Project C) Cross Product D) Aggregate Function Answer: D Explanation: The basic operations in relational algebra are Select, Project, Union, Difference, Intersection, and Cartesian Product. Aggregate functions (like SUM, COUNT) are part of SQL but not a fundamental operation in relational algebra. 5. In relational algebra, the division operator (÷) is used for which of the following purposes? A) To find tuples in one relation that match tuples in another B) To divide one relation into smaller parts C) To find a set of tuples in one relation that are associated with all tuples in another relation D) To combine relations based on a common attribute Answer: C Explanation: The division operator (÷) is used to find tuples in a relation that are associated with all tuples in another relation. It is typically used in queries involving "for all" conditions. 6. Which of the following relational algebra operations is used to retrieve tuples that meet a specific condition? A) Select (σ) B) Project (π) C) Join D) Rename (ρ) Answer: A Explanation: Select (σ) is used to retrieve tuples from a relation based on a condition, whereas Project (π) selects specific columns. 7. Which of the following is the correct order of precedence for relational algebra operators? A) Select, Project, Cartesian Product, Union B) Cartesian Product, Select, Project, Union C) Select, Cartesian Product, Union, Project D) Project, Select, Union, Cartesian Product Answer: A Explanation: The correct order of precedence in relational algebra is Select (σ), Project (π), Cartesian Product (×), and Union (∪). This ensures operations are executed in a specific order. Relational Calculus 1. Which of the following is true about Tuple Relational Calculus (TRC)? A) TRC is a procedural query language. B) TRC uses variables to represent the result of a query. C) TRC explicitly states the steps to obtain the result. D) TRC is less expressive than relational algebra. Answer: B Explanation: Tuple Relational Calculus (TRC) is a non-procedural query language where the variables represent tuples, and it specifies what result is desired rather than the steps to obtain it. The user defines the conditions the resulting tuples must satisfy. TRC is considered equally expressive as relational algebra. 2. Which of the following is a fundamental difference between Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC)? A) TRC uses domain variables, while DRC uses tuple variables. B) TRC uses tuple variables, while DRC uses domain variables. C) TRC is procedural, while DRC is non-procedural. D) TRC is used in SQL, while DRC is not. Answer: B Explanation: Tuple Relational Calculus (TRC) uses tuple variables (representing entire rows), while Domain Relational Calculus (DRC) uses domain variables (representing individual field values). Both TRC and DRC are non-procedural query languages. 3. In Domain Relational Calculus (DRC), what symbol is used to denote existential quantification? A) ∀ (For all) B) ∃ (There exists) C) --| (Not) D) ⇒ (Implies) Answer: B Explanation: In Domain Relational Calculus (DRC), the existential quantifier (∃) is used to indicate that a condition is satisfied for at least one domain element. For example, if ∃x (x > 10), it means there exists a value of x that is greater than 10. 4. Which of the following is NOT possible in Tuple Relational Calculus (TRC)? A) Filtering tuples based on a condition. B) Using existential quantification. C) Retrieving specific attributes from a tuple. D) Direct specification of how data is retrieved. Answer: D Explanation: Tuple Relational Calculus (TRC) is a non-procedural query language, meaning it does not directly specify how the data is retrieved but only defines what the result should be by using conditions and logical operators. The actual retrieval steps are handled by the underlying query processor. 5. What is the correct definition of the safety condition in Tuple Relational Calculus (TRC)? A) Ensures that a query only returns finite results. B) Ensures that all tuples in a relation are returned. C) Ensures that only a subset of the tuples is returned. D) Ensures that infinite results are always returned. Answer: A Explanation: The safety condition in Tuple Relational Calculus (TRC) ensures that the query produces finite results. Without this condition, certain queries could theoretically return an infinite number of tuples, which would not be practical in a real database system. 6. Which of the following is true for tuple relational calculus? A) It is a procedural language. B) It involves only select and project operations. C) It uses logical predicates to describe queries. D) It cannot express recursive queries. Answer: C Explanation: Tuple Relational Calculus (TRC) is a non-procedural query language where queries are expressed by specifying the properties of the desired result through logical predicates. 7. In domain relational calculus (DRC), what does the expression {t | t ∈ r ∧ φ(t)} represent? A) A set of tuples from relation r that satisfy condition φ(t) B) A set of columns from relation r that satisfy condition φ(t) C) A set of relations from tuple t that match relation r D) A set of tuples from relation r without duplicates Answer: A Explanation: In Domain Relational Calculus (DRC), the expression {t | t ∈ r ∧ φ(t)} represents a set of tuples t from relation r that satisfy the condition φ(t). 8. Which of the following is a major difference between relational algebra and relational calculus? A) Relational algebra is non-procedural, and relational calculus is procedural. B) Relational algebra provides a set of operations, while relational calculus is based on logic. C) Relational calculus allows recursive queries, while relational algebra does not. D) Relational calculus requires the use of aggregate functions. Answer: B Explanation: The key difference is that relational algebra is a procedural query language where the user specifies how to perform operations, while relational calculus is a non- procedural language that focuses on what to retrieve based on logical conditions. Data Integrity 9. Which of the following ensures that the values in a column are unique across the table? A) Foreign Key B) Check Constraint C) Unique Constraint D) Primary Key Answer: C Explanation: The Unique constraint ensures that all values in a specific column are distinct across the table. A Primary Key also ensures uniqueness but additionally does not allow NULL values. 10. What is a foreign key primarily used for in relational databases? A) To uniquely identify a record in a table B) To maintain referential integrity between two tables C) To store large amounts of text data D) To index the table for faster access Answer: B Explanation: A Foreign Key is used to maintain referential integrity by creating a link between two tables, ensuring that a value in one table corresponds to a valid value in another. 11. What is the primary purpose of the CHECK constraint in relational databases? A) To prevent NULL values in a column B) To enforce uniqueness of data in a column C) To ensure data meets a specific condition D) To automatically generate values for a column Answer: C Explanation: The CHECK constraint is used to enforce that all values in a column meet a specified condition, such as limiting values to a certain range or format. 12. Which of the following integrity constraints ensures that no attribute in a primary key can have NULL values? A) Entity Integrity B) Referential Integrity C) Domain Integrity D) Tuple Integrity Answer: A Explanation: Entity Integrity ensures that the primary key of a table does not contain any NULL values, as the primary key uniquely identifies each row. 13. Which integrity constraint is violated if a foreign key references a non-existent primary key value in the parent table? A) Domain Integrity B) Entity Integrity C) Referential Integrity D) Null Integrity Answer: C Explanation: Referential Integrity is violated when a foreign key references a primary key that does not exist in the referenced table, which breaks the relationship between the tables. 14. Which of the following is an example of domain integrity? A) A foreign key pointing to a primary key in another table B) Values in a column conforming to a specific data type C) A composite primary key ensuring uniqueness D) Two tables related through a join condition Answer: B Explanation: Domain Integrity refers to the validity of data within a particular domain, ensuring that the values in a column conform to a specific data type or range of allowed values. 15. What does referential integrity guarantee in relational databases? A) Each row in a table is unique. B) Each foreign key value matches a primary key value in the related table. C) All columns in a table are indexed. D) No attribute in a table contains a NULL value. Answer: B Explanation: Referential Integrity ensures that a foreign key in one table matches an existing primary key value in the related table, preserving relationships between records. Database Architecture (Levels of Abstraction, Physical and Logical Design) 1. Which of the following correctly describes the levels of abstraction in a DBMS? A) Physical level, Logical level, View level B) Data level, Logical level, Physical level C) Application level, User level, Physical level D) Schema level, Table level, Data level Answer: A Explanation: The three levels of abstraction in a DBMS are the Physical level, which describes how data is stored, the Logical level, which describes what data is stored, and the View level, which provides user-specific views of the data. 2. The physical level of a database system describes: A) The logical structure of the entire database. B) How data is stored in memory and on disk. C) The relationships between tables in a database. D) The constraints applied to the database schema. Answer: B Explanation: The physical level is concerned with how data is actually stored on the disk or memory, including file structures, indexing, and optimization techniques. 3. Which of the following is the highest level of abstraction in the database architecture? A) Physical level B) Logical level C) View level D) Conceptual level Answer: C Explanation: The view level is the highest level of abstraction in database architecture, providing different views of the data for different users. Each user can have a personalized view of the database, hiding complexity. 4. In which level of database abstraction are user-defined views created? A) Physical level B) Logical level C) View level D) Conceptual level Answer: C Explanation: User-defined views are created at the view level of database abstraction. This level provides different ways to look at the data, depending on user needs, without altering the underlying schema. 5. Which of the following best describes the logical level of abstraction in a DBMS? A) Describes how the data is actually stored on disk. B) Describes what data is stored and the relationships among them. C) Defines user interfaces and views of the data. D) Defines security constraints and access controls. Answer: B Explanation: The logical level describes what data is stored in the database and the relationships between the data, making it the intermediary between the physical level and the view level. 6. In the physical design of a database, which of the following is the focus? A) The structure of the user interface. B) Optimization of data storage and access methods. C) Relationships between data elements. D) The logical structure of the data. Answer: B Explanation: Physical design focuses on the optimization of data storage and access methods, including the layout of data on disks, indexing, partitioning, and performance considerations. 7. Which of the following is NOT part of the logical design of a database? A) Data models B) Entity-relationship diagrams (ERD) C) Indexing strategies D) Normalization Answer: C Explanation: Indexing strategies are part of the physical design of a database. The logical design involves data models, relationships, and normalization. 8. Which level of abstraction provides a user with a tailored view of the database while hiding details of the actual data storage? A) Logical level B) Physical level C) Conceptual level D) View level Answer: D Explanation: The view level provides a tailored view for users, hiding details of how the data is physically stored or logically structured. Data Independence 9. What is meant by "data independence" in DBMS? A) The ability to modify the database structure without affecting the application programs. B) The ability to store data independently of the DBMS. C) The ability to define data independently of its schema. D) The ability to change user interfaces without affecting the database. Answer: A Explanation: Data independence refers to the ability to modify the schema at one level without affecting the schema at the next higher level, meaning changes in the database structure do not require changes in the application programs. 10. Which of the following types of data independence allows changes to the physical storage of data without affecting the logical structure? A) Logical Data Independence B) Physical Data Independence C) External Data Independence D) View Data Independence Answer: B Explanation: Physical Data Independence allows changes to how the data is stored physically (e.g., file structure, indexing) without affecting the logical structure or the application programs. 11. What is an example of logical data independence in a DBMS? A) Changing the indexing strategy of the database. B) Modifying the conceptual schema without affecting the external schema. C) Changing the hardware used to store the data. D) Changing how data is accessed by the database engine. Answer: B Explanation: Logical Data Independence refers to the ability to modify the conceptual schema (such as adding new tables or attributes) without affecting the external schema or user applications. 12. Which of the following is harder to achieve: Logical Data Independence or Physical Data Independence? A) Physical Data Independence B) Logical Data Independence C) Both are equally hard D) Neither is hard to achieve Answer: B Explanation: Logical Data Independence is harder to achieve because changes in the conceptual schema can affect the application programs, whereas Physical Data Independence is easier to implement as it involves changes in the storage mechanisms without impacting the logical structure. 13. What is the key benefit of data independence in database systems? A) Enhances query performance. B) Reduces data redundancy. C) Facilitates schema evolution without affecting the application. D) Ensures data integrity. Answer: C Explanation: The key benefit of data independence is that it facilitates changes in the schema at one level (e.g., adding fields, changing table structures) without affecting the applications that rely on the data, allowing easier maintenance and evolution of the database. 14. Which of the following is an example of physical data independence? A) Adding new fields to a table. B) Changing the format of storage (e.g., from a file system to a database system) without affecting the logical schema. C) Removing a column from a view. D) Renaming a table. Answer: B Explanation: Physical Data Independence allows changes to the physical storage mechanisms (such as moving from a file system to a database system) without impacting the logical schema or the application programs that use the database. Views 1. Which of the following best defines a view in SQL? A) A stored procedure that returns a table. B) A virtual table based on the result-set of an SQL query. C) A table that stores data physically. D) A trigger that automatically updates a table. Answer: B Explanation: A view in SQL is a virtual table that does not store data itself but provides a dynamic result-set of an SQL query. The data in the view is generated from the underlying base tables. 2. Which of the following is a key advantage of using views in SQL? A) Reducing the size of the database. B) Enhancing query performance by precomputing data. C) Providing a level of abstraction for security and simplifying complex queries. D) Automatically indexing the database. Answer: C Explanation: Views provide a level of abstraction by hiding the complexity of underlying queries and enhancing security by allowing users to access data without giving them access to the underlying tables. 3. Which of the following operations cannot be performed directly on a view? A) SELECT B) INSERT C) DELETE D) Creating indexes on view columns Answer: D Explanation: Views are virtual tables, and indexes cannot be created directly on view columns. However, you can create indexes on the base tables that support the view. 4. What happens when a view is dropped in SQL? A) The data within the view is deleted. B) The base tables associated with the view are deleted. C) The view definition is removed, but the data in the base tables remains unaffected. D) The schema associated with the view is deleted. Answer: C Explanation: When a view is dropped, the view definition is removed, but the base tables and the data they contain remain unaffected because a view does not store data itself. 5. Can a view in SQL contain data from multiple tables? A) No, a view can only be created from one table. B) Yes, a view can join data from multiple tables using SQL queries. C) No, views can only show data from a single query. D) Yes, but only using union operations. Answer: B Explanation: A view can contain data from multiple tables by using JOIN operations or UNION in the underlying SQL query. This allows combining related data from various tables into a single result set. Natural Join 6. What is a key characteristic of a natural join in SQL? A) It joins tables on a specified condition, regardless of column names. B) It joins tables automatically on all columns with the same name. C) It joins tables using the Cartesian product. D) It allows for cross joins without any conditions. Answer: B Explanation: A natural join automatically joins two tables based on all columns with the same name and compatible data types. It does not require an explicit condition and eliminates duplicate columns from the result set. 7. What happens if there are no matching columns in a natural join? A) The query throws an error. B) A Cartesian product is performed. C) An empty result set is returned. D) All rows from the first table are returned. Answer: B Explanation: If there are no matching columns with the same name between the two tables, a Cartesian product is performed, which results in a combination of all rows from both tables. 8. Which SQL operation is closest in functionality to a natural join when column names do not match? A) Cross Join B) Outer Join C) Inner Join with explicit condition D) Self Join Answer: C Explanation: When column names don't match, you must use an Inner Join with an explicit condition specifying the matching columns. This ensures that the result is based on the condition rather than a Cartesian product. 9. Which of the following will result in the same output as a natural join, given that column names match between the tables? A) Inner join with an ON condition specifying the columns to join. B) Cross join. C) Union. D) Left join without conditions. Answer: A Explanation: A natural join behaves like an inner join where the ON condition is automatically defined for all columns with the same names. Hence, an explicit inner join with a matching condition would produce the same result. 10. Can a natural join return more columns than the input tables? A) Yes, if there are duplicate columns. B) No, it removes duplicate columns based on common attribute names. C) Yes, but only if a WHERE clause is applied. D) No, unless an explicit alias is used. Answer: B Explanation: A natural join eliminates duplicate columns from the output. If two tables have common columns, the resulting table will only have one column for each matching attribute. Cartesian Product 11. Which of the following best defines a Cartesian product in SQL? A) The operation that combines all rows of one table with all rows of another. B) The operation that finds the intersection of rows between two tables. C) The operation that selects unique rows from two tables. D) The operation that selects rows from one table based on a condition. Answer: A Explanation: A Cartesian product is the operation that combines all rows of one table with all rows of another. It results in every possible pair of rows from the two tables being combined. 12. How many rows will the Cartesian product of two tables have if Table A has 10 rows and Table B has 5 rows? A) 50 B) 10 C) 5 D) 15 Answer: A Explanation: The Cartesian product combines each row of Table A with each row of Table B, so if Table A has 10 rows and Table B has 5 rows, the result will have 10 * 5 = 50 rows. 13. Which of the following SQL joins will produce the same result as a Cartesian product? A) Inner Join without an ON condition. B) Outer Join with a WHERE clause. C) Self Join on a key column. D) Cross Join without any condition. Answer: D Explanation: A Cross Join without any condition produces a Cartesian product, where each row of the first table is combined with every row of the second table, generating all possible combinations of rows. 14. What is the difference between a Cartesian product and an Inner Join? A) Cartesian product returns only matching rows, while Inner Join returns all rows. B) Cartesian product creates all possible row combinations, while Inner Join only returns rows with matching conditions. C) There is no difference; both return the same result. D) Inner Join removes duplicates, while Cartesian product does not. Answer: B Explanation: A Cartesian product generates all possible row combinations, whereas an Inner Join returns only the rows that satisfy a given join condition. 15. Which of the following scenarios would lead to an unintentional Cartesian product in SQL? A) Using an Inner Join with a WHERE clause. B) Omitting the ON clause in an Inner Join. C) Using a Cross Join explicitly. D) Using a Self Join with a condition. Answer: B Explanation: Omitting the ON clause in an Inner Join leads to an unintentional Cartesian product, because there is no condition specified to match rows between the tables, resulting in all possible combinations of rows. Set Operations in Databases 1. Which of the following set operations automatically removes duplicates in SQL? A) UNION ALL B) UNION C) INTERSECT D) MINUS/EXCEPT Answer: B Explanation: The UNION operation combines the result sets of two queries and automatically removes duplicates, keeping only distinct values. In contrast, UNION ALL does not remove duplicates. 2. Which set operation returns only the rows that are common to both queries in SQL? A) UNION B) UNION ALL C) INTERSECT D) MINUS/EXCEPT Answer: C Explanation: The INTERSECT operation returns only the rows that are common between the result sets of two queries, i.e., rows that appear in both result sets. 3. What does the MINUS (or EXCEPT) set operation do in SQL? A) Returns all rows from the first query except the rows found in the second query. B) Combines rows from both queries, keeping duplicates. C) Removes duplicates and returns unique rows from both queries. D) Joins two tables based on a matching condition. Answer: A Explanation: The MINUS (in Oracle) or EXCEPT (in SQL Server) operation returns the rows from the first query that are not present in the result of the second query. It essentially performs subtraction between two result sets. 4. Which of the following set operations can return duplicate rows in SQL? A) INTERSECT B) UNION C) UNION ALL D) MINUS/EXCEPT Answer: C Explanation: UNION ALL combines the result sets of two queries without removing duplicates, meaning it returns all rows, including any duplicates that appear in both result sets. 5. Which of the following conditions must be true for set operations to work in SQL? A) The number of columns and their data types must match between the result sets. B) The number of rows in both result sets must be the same. C) The result sets must come from tables with foreign key relationships. D) The column names in both result sets must be the same. Answer: A Explanation: For set operations like UNION, INTERSECT, and MINUS/EXCEPT to work, the number of columns in the result sets must be the same, and the corresponding columns must have compatible data types. 6. Which set operation would you use to get a combined result from two tables, including all duplicates? A) UNION B) UNION ALL C) INTERSECT D) MINUS/EXCEPT Answer: B Explanation: UNION ALL combines the result sets from two queries and returns all rows, including any duplicates. If you want a combined result that includes duplicates, UNION ALL is the correct operation. 7. What happens if you use the UNION operation with incompatible column data types in SQL? A) The query executes successfully, but some data might be truncated. B) The query throws a syntax error. C) The query throws a data type mismatch error. D) The query ignores the incompatible columns. Answer: C Explanation: If you try to perform a UNION (or other set operations) on columns with incompatible data types, the query will result in a data type mismatch error because the columns in each query must have compatible types. 8. Which of the following will return an empty result set if the two queries have no common rows? A) UNION B) UNION ALL C) INTERSECT D) MINUS/EXCEPT Answer: C Explanation: The INTERSECT operation returns only the rows that are common to both result sets. If there are no common rows, the result set will be empty. 9. Which of the following statements about MINUS/EXCEPT is correct? A) It combines the results of two queries, returning all distinct rows. B) It returns rows present in the second query but not in the first. C) It returns rows present in the first query but not in the second. D) It returns the Cartesian product of two result sets. Answer: C Explanation: The MINUS/EXCEPT operation returns rows that are present in the first query but not present in the second query. It performs a subtraction between the two result sets. 10. In which situation would you prefer to use UNION ALL instead of UNION? A) When you need to remove duplicates from the combined result set. B) When you want to avoid removing duplicates and want better performance. C) When you are performing a subtraction operation. D) When you need to join two tables. Answer: B Explanation: UNION ALL does not remove duplicates, which can make it faster than UNION, especially when working with large datasets. Use UNION ALL if you want to combine result sets and do not need to remove duplicates. 11. Which of the following set operations is not available in all database systems (such as MySQL)? A) UNION ALL B) INTERSECT C) UNION D) Cartesian Product Answer: B Explanation: The INTERSECT operation is not available in all database systems, such as MySQL. Some systems require you to simulate INTERSECT using JOINs and WHERE clauses. 12. Which set operation can return rows with NULL values if the result sets contain NULLs? A) UNION ALL B) UNION C) INTERSECT D) All of the above Answer: D Explanation: UNION, UNION ALL, and INTERSECT can return rows with NULL values if the underlying result sets contain NULLs. SQL considers NULL values as unknowns, so they are included unless explicitly filtered out. 13. Which of the following SQL set operations can be used to find the difference between two result sets? A) UNION ALL B) INTERSECT C) MINUS/EXCEPT D) FULL OUTER JOIN Answer: C Explanation: MINUS (or EXCEPT in some systems) finds the difference between two result sets by returning the rows that are present in the first query but not in the second. 14. Which of the following set operations in SQL does not require sorting or duplicate elimination? A) UNION B) UNION ALL C) INTERSECT D) MINUS/EXCEPT Answer: B Explanation: UNION ALL does not require sorting or duplicate elimination because it includes all rows, including duplicates. This can make it faster than UNION, which needs to eliminate duplicates. 15. Which of the following conditions is false regarding the INTERSECT operation? A) INTERSECT returns common rows from both queries. B) INTERSECT can work with columns that have different data types. C) INTERSECT removes duplicates from the result set. D) INTERSECT requires the same number of columns in both queries. Answer: B Explanation: The INTERSECT operation requires that the columns in both queries have compatible data types and the same number of columns. It also removes duplicates from the result set, showing only common rows. Data Manipulation Language (DML) 1. Which of the following is a Data Manipulation Language (DML) command in SQL? A) CREATE B) INSERT C) ALTER D) GRANT Answer: B Explanation: INSERT is a DML command used to add new rows into a table. In contrast, CREATE and ALTER are DDL (Data Definition Language) commands, and GRANT is a DCL (Data Control Language) command. 2. What will happen if you omit the WHERE clause in an UPDATE statement? A) No rows will be updated. B) Only the first row will be updated. C) All rows in the table will be updated. D) An error will occur. Answer: C Explanation: If the WHERE clause is omitted in an UPDATE statement, all rows in the table will be updated with the new value(s). The WHERE clause is used to specify which rows should be affected. 3. Which of the following DML operations can result in a "Cartesian Product" if not handled correctly? A) SELECT B) UPDATE C) INSERT D) DELETE Answer: A Explanation: A SELECT statement can produce a Cartesian Product when combining multiple tables without a proper JOIN condition. This results in every possible combination of rows between the tables. 4. Which of the following SQL commands would you use to modify existing records in a table? A) INSERT B) UPDATE C) DELETE D) SELECT Answer: B Explanation: The UPDATE command is used to modify existing records in a table. INSERT is used to add new records, and DELETE removes records from a table. 5. What does the DELETE statement do in SQL? A) Deletes the entire table from the database. B) Deletes selected rows from a table. C) Deletes a column from the table. D) Deletes duplicate rows. Answer: B Explanation: The DELETE statement in SQL is used to remove selected rows from a table based on the specified condition(s). If no condition is provided, all rows in the table will be deleted. 6. Which DML statement retrieves data from one or more tables? A) UPDATE B) DELETE C) SELECT D) INSERT Answer: C Explanation: The SELECT statement is used to retrieve data from one or more tables in a database. It is one of the most commonly used DML commands to query data. 7. In an UPDATE statement, what happens if the WHERE clause condition is not satisfied for any row? A) All rows are updated. B) Only the first row is updated. C) No rows are updated. D) An error is thrown. Answer: C Explanation: If the condition in the WHERE clause is not met by any row, then no rows are updated. The operation will complete successfully but without affecting any rows. 8. Which DML command can be rolled back if issued inside a transaction? A) CREATE B) UPDATE C) GRANT D) ALTER Answer: B Explanation: UPDATE is a DML operation and can be rolled back if it is part of a transaction. In contrast, CREATE and ALTER are DDL commands and cannot be rolled back in most systems unless explicitly supported. 9. Which of the following is true about the TRUNCATE command? A) It can be rolled back. B) It is a DML command. C) It removes rows without logging individual row deletions. D) It requires a WHERE clause. Answer: C Explanation: TRUNCATE is not a DML command but a DDL command. It removes all rows from a table without logging individual row deletions, making it faster than DELETE. It cannot be rolled back, and no WHERE clause is allowed. 10. Which DML command allows you to delete a subset of rows based on a specific condition? A) DELETE B) DROP C) TRUNCATE D) ALTER Answer: A Explanation: The DELETE command allows you to delete a subset of rows from a table based on a condition specified in the WHERE clause. DROP and TRUNCATE remove entire tables or all rows, respectively. 11. What is the main difference between DELETE and TRUNCATE commands in SQL? A) DELETE cannot be rolled back, but TRUNCATE can. B) DELETE removes rows based on a condition, whereas TRUNCATE removes all rows. C) DELETE is a DDL command, and TRUNCATE is a DML command. D) DELETE is faster than TRUNCATE. Answer: B Explanation: DELETE removes rows from a table based on a condition, while TRUNCATE removes all rows without logging individual row deletions. TRUNCATE is faster but cannot be rolled back, and it's a DDL command, not DML. 12. Which of the following DML commands can potentially violate a foreign key constraint? A) SELECT B) DELETE C) INSERT D) Both B and C Answer: D Explanation: Both INSERT and DELETE can potentially violate foreign key constraints. An INSERT could fail if a referenced foreign key does not exist, while a DELETE could fail if it tries to remove a row that is referenced by a foreign key in another table. 13. What happens if you omit the column list in an INSERT INTO statement? A) SQL will automatically assign NULL values to all columns. B) SQL will insert values into all columns in the order defined in the table. C) An error will occur. D) Only primary key columns will be filled. Answer: B Explanation: If you omit the column list in an INSERT INTO statement, SQL assumes that you are providing values for all columns in the order they are defined in the table. If the values don't match this order, an error will occur. 14. In SQL, which DML command is used to remove duplicate rows from a table? A) DELETE DISTINCT B) DELETE C) SELECT DISTINCT D) DELETE FROM table WHERE ROWNUM = 1 Answer: A Explanation: To remove duplicate rows, you can use the DELETE DISTINCT command in combination with a ROW_NUMBER() or other filtering mechanisms. The standard DELETE does not have a built-in feature to remove duplicates on its own. 15. Which of the following DML commands can be executed without causing any modification to the data in a table? A) UPDATE B) DELETE C) INSERT D) SELECT Answer: D Explanation: The SELECT statement is a read-only DML operation that retrieves data without causing any modifications to the database. It does not insert, update, or delete any records. 16. Which clause must always be used in a DELETE statement to avoid accidental deletion of all records? A) ORDER BY B) WHERE C) GROUP BY D) HAVING Answer: B Explanation: The WHERE clause is crucial in a DELETE statement to specify which records should be deleted. If the WHERE clause is omitted, all rows in the table will be deleted. 17. Which DML operation can be combined with a JOIN to modify multiple tables in a single query? A) DELETE B) INSERT C) UPDATE D) SELECT Answer: C Explanation: An UPDATE statement can be combined with a JOIN to modify rows across multiple tables. This is useful when the update needs to depend on data from a related table. 18. What happens if an INSERT statement violates a NOT NULL constraint? A) The NULL value is inserted. B) The default value is inserted. C) An error occurs. D) The row is inserted with a NULL value, and a warning is raised. Answer: C Explanation: If an INSERT statement attempts to insert a NULL value into a column that has a NOT NULL constraint, the operation will fail, and an error will be thrown. Entity-Relationship (ER) Model 1. In an ER diagram, an entity is represented by which of the following? A) Rectangle B) Ellipse C) Diamond D) Triangle Answer: A Explanation: In an ER diagram, an entity is represented by a rectangle. Attributes are represented by ellipses, and relationships are depicted with diamonds. 2. Which of the following statements is true regarding an attribute in an ER model? A) An attribute can have multiple values. B) An attribute cannot be derived from other attributes. C) An attribute can be composite. D) All attributes are mandatory. Answer: C Explanation: An attribute can be composite, meaning it can be divided into smaller sub- attributes (e.g., an address can be divided into street, city, and zip code). Some attributes can have multiple values (multi-valued), and not all attributes are mandatory. 3. In an ER model, a relationship can be categorized into which of the following types? A) One-to-One, One-to-Many, Many-to-Many B) Mandatory, Optional C) Simple, Complex D) All of the above Answer: A Explanation: Relationships in an ER model are typically categorized as One-to-One, One-to- Many, and Many-to-Many, based on how many instances of one entity relate to instances of another entity. 4. What does a dashed ellipse represent in an ER diagram? A) Multivalued attribute B) Weak entity C) Composite attribute D) Regular attribute Answer: A Explanation: A dashed ellipse in an ER diagram represents a multivalued attribute, which can hold multiple values for a single entity. 5. In the context of an ER diagram, what is a weak entity? A) An entity that cannot exist without another entity. B) An entity with a primary key. C) An entity that does not have any relationships. D) An entity with optional attributes. Answer: A Explanation: A weak entity is one that cannot be uniquely identified by its own attributes alone and depends on a strong entity for identification. It typically has a partial key. Keys in the ER Model 6. What is the primary key in an ER model? A) A key that can identify duplicates. B) A key that allows null values. C) A unique identifier for each entity in a set. D) A key that relates two entities. Answer: C Explanation: The primary key is a unique identifier for each entity in an entity set. It ensures that no two entities have the same value for this attribute. 7. Which of the following is a candidate key? A) An attribute that uniquely identifies an entity and can have NULL values. B) An attribute that is a unique identifier for a weak entity. C) An attribute that can uniquely identify an entity but is not selected as the primary key. D) An attribute that is used to link two entities. Answer: C Explanation: A candidate key is an attribute (or set of attributes) that can uniquely identify an entity but is not selected as the primary key. A table may have multiple candidate keys. 8. What is a foreign key in an ER model? A) A key that uniquely identifies an entity in a relationship. B) A key that references the primary key of another entity. C) A key that has NULL values. D) A key used to identify weak entities. Answer: B Explanation: A foreign key is an attribute (or set of attributes) in one entity that references the primary key of another entity, establishing a relationship between the two entities. 9. Which of the following keys can accept NULL values? A) Primary key B) Foreign key C) Candidate key D) None of the above Answer: B Explanation: A foreign key can accept NULL values, allowing for the possibility that there may not be a corresponding entity in the referenced table. Primary keys and candidate keys must always contain unique values and cannot accept NULLs. 10. What distinguishes a composite key from a simple key? A) A composite key contains multiple attributes; a simple key contains only one. B) A composite key is always a foreign key. C) A simple key can have NULL values, while a composite key cannot. D) A composite key is used for weak entities. Answer: A Explanation: A composite key is a key that consists of two or more attributes that together uniquely identify an entity, while a simple key consists of only one attribute. Entity Sets 11. What is an entity set in an ER model? A) A collection of similar entities. B) A relationship between two entities. C) A collection of attributes. D) A specific instance of an entity. Answer: A Explanation: An entity set is a collection of similar entities that share the same attributes but have different values. For example, all students in a school can be represented as an entity set. 12. How are entity sets represented in an ER diagram? A) As rectangles B) As diamonds C) As ovals D) As circles Answer: A Explanation: Entity sets are represented by rectangles in an ER diagram, clearly distinguishing them from attributes and relationships. 13. What type of relationship exists between two entity sets if every entity in the first set must relate to at least one entity in the second set? A) One-to-One B) One-to-Many C) Many-to-Many D) Mandatory Relationship Answer: D Explanation: A mandatory relationship exists when every entity in one entity set must relate to at least one entity in another set. This indicates a dependency between the entities. 14. Which of the following correctly describes a weak entity set? A) It has a primary key that is derived from its own attributes. B) It relies on a strong entity set for its identification. C) It is the only entity set in the ER model. D) It can exist independently without any relationship. Answer: B Explanation: A weak entity set relies on a strong entity set for its identification. It does not have a primary key of its own and is dependent on a strong entity for its existence. 15. In an ER model, if an entity set has a unique attribute that can identify it, this attribute is called what? A) Weak attribute B) Primary attribute C) Strong attribute D) Candidate key Answer: D Explanation: An attribute that can uniquely identify an entity set is known as a candidate key. It can serve as a primary key if selected. Two-Phase Locking Protocol (2PL) 1. What are the two phases of the Two-Phase Locking (2PL) protocol? A) Locking Phase and Unlocking Phase B) Growing Phase and Shrinking Phase C) Request Phase and Release Phase D) Wait Phase and Execute Phase Answer: B Explanation: The Two-Phase Locking protocol consists of a Growing Phase, where a transaction can obtain locks but cannot release them, followed by a Shrinking Phase, where a transaction can release locks but cannot obtain any new locks. 2. In the Two-Phase Locking protocol, what is the potential drawback of not releasing locks during the Growing Phase? A) Deadlock B) Starvation C) Lost Update D) Dirty Read Answer: A Explanation: Not releasing locks during the Growing Phase can lead to deadlock, where two or more transactions are waiting for each other to release locks, preventing any of them from proceeding. 3. What type of locking does the Two-Phase Locking protocol utilize to ensure serializability? A) Read Locks B) Write Locks C) Shared and Exclusive Locks D) Optimistic Locking Answer: C Explanation: The Two-Phase Locking protocol uses shared locks for reading and exclusive locks for writing. This allows multiple transactions to read data simultaneously while preventing others from writing until the lock is released. 4. Which of the following statements is true regarding Strict Two-Phase Locking (S2PL)? A) A transaction can release locks during the Growing Phase. B) All transactions must obtain locks before any of them can release them. C) Locks are released immediately after the transaction is complete. D) A transaction can release locks at any time. Answer: C Explanation: In Strict Two-Phase Locking (S2PL), locks are not released until the transaction is complete. This ensures that no other transaction can access the locked data until the current transaction finishes, guaranteeing strict serializability. 5. Which of the following scenarios can occur if the Two-Phase Locking protocol is not followed correctly? A) No lost updates B) Increased performance C) Inconsistent data D) Serializable schedules Answer: C Explanation: If the Two-Phase Locking protocol is not followed correctly, it can lead to inconsistent data being read or written by transactions, violating the integrity and consistency of the database. 6. What is a potential limitation of the Two-Phase Locking protocol? A) It guarantees the absence of deadlocks. B) It can lead to resource starvation. C) It allows for maximum concurrency. D) It requires less overhead than other protocols. Answer: B Explanation: One limitation of the Two-Phase Locking protocol is that it can lead to resource starvation, where certain transactions may not get the resources they need to proceed, especially if higher-priority transactions continually obtain locks. 7. In the Two-Phase Locking protocol, what happens if a transaction requests a lock that it cannot obtain immediately? A) The transaction is rolled back. B) The transaction waits until it can obtain the lock. C) The transaction terminates. D) The transaction is allowed to continue without the lock. Answer: B Explanation: If a transaction requests a lock that it cannot obtain immediately due to another transaction holding the lock, the transaction will wait until it can obtain the lock, ensuring that the locking protocol is respected. 8. What is the main advantage of using the Two-Phase Locking protocol? A) It eliminates deadlocks completely. B) It guarantees serializability of transactions. C) It reduces the overhead of lock management. D) It allows transactions to execute in parallel without any restrictions. Answer: B Explanation: The main advantage of the Two-Phase Locking protocol is that it guarantees serializability of transactions, ensuring that the outcome of concurrent transaction execution is equivalent to some serial execution of those transactions. 9. Which of the following is a variation of the Two-Phase Locking protocol? A) Optimistic Concurrency Control B) Strict Two-Phase Locking C) Timestamp Ordering D) Multi-Version Concurrency Control Answer: B Explanation: Strict Two-Phase Locking (S2PL) is a variation of the Two-Phase Locking protocol that enforces stricter rules on lock management to ensure serializability and prevent anomalies. 10. In the context of the Two-Phase Locking protocol, what does the term "lock escalation" refer to? A) Converting multiple fine-grained locks into a single coarse-grained lock. B) A situation where a lock is requested too frequently. C) The act of promoting a shared lock to an exclusive lock. D) The release of locks before the transaction completes. Answer: A Explanation: Lock escalation refers to the process of converting multiple fine-grained locks (such as row locks) into a single coarse-grained lock (such as a table lock) to reduce overhead and improve performance. Timestamp-based Transactions 1. What is the primary goal of timestamp-based concurrency control? A) To maximize the throughput of transactions B) To ensure serializability of transactions C) To reduce the response time of transactions D) To avoid deadlocks in transaction execution Answer: B Explanation: The primary goal of timestamp-based concurrency control is to ensure serializability of transactions, meaning that the outcome of executing concurrent transactions should be the same as if they were executed in some serial order. 2. In a timestamp-based system, what does a lower timestamp value indicate? A) A transaction has been executed later than others. B) A transaction has a higher priority. C) A transaction is more likely to be rolled back. D) A transaction has been aborted. Answer: B Explanation: In a timestamp-based system, a lower timestamp value indicates that a transaction has been issued earlier than others, which gives it a higher priority for execution. 3. What happens when a transaction requests a resource that has been modified by another transaction with a later timestamp? A) The transaction is granted access to the resource immediately. B) The transaction waits until the resource is released. C) The transaction is aborted immediately. D) The transaction is allowed to read the uncommitted data. Answer: C Explanation: When a transaction requests a resource modified by another transaction with a later timestamp, it will be aborted immediately to maintain consistency, as it violates the rules of timestamp ordering. 4. Which of the following is a disadvantage of timestamp-based concurrency control? A) Increased overhead for managing timestamps B) Simplicity of implementation C) Improved transaction throughput D) Elimination of deadlocks Answer: A Explanation: A disadvantage of timestamp-based concurrency control is the increased overhead for managing timestamps and ensuring that transactions are executed in a way that respects the ordering imposed by their timestamps. 5. What is the primary component of a transaction's state in a timestamp-based system? A) Transaction ID B) Read and Write Set C) Timestamp D) Lock Status Answer: C Explanation: The primary component of a transaction's state in a timestamp-based system is its timestamp, which determines the order of transaction execution and helps in maintaining consistency. 6. Which of the following mechanisms is used to prevent cascading rollbacks in a timestamp- based transaction system? A) Locking B) Write-ahead logging C) Strict Timestamp Ordering D) Snapshot Isolation Answer: C Explanation: Strict Timestamp Ordering is used to prevent cascading rollbacks by ensuring that a transaction can only read committed data from transactions with lower timestamps. 7. In timestamp-based systems, what does the term "wait-die" protocol refer to? A) A method to resolve deadlocks B) A strategy for rolling back transactions C) A mechanism for handling conflicting transactions D) A scheme to prioritize transactions based on timestamps Answer: C Explanation: The wait-die protocol is a mechanism for handling conflicting transactions where older transactions (with lower timestamps) can wait for younger transactions to release resources, while younger transactions that conflict with older ones are aborted. 8. What is the role of timestamps in ensuring serializability in timestamp-based systems? A) To track the execution time of transactions B) To determine the order of transaction execution C) To manage memory allocation for transactions D) To maintain the history of transaction states Answer: B Explanation: Timestamps play a critical role in determining the order of transaction execution, ensuring that transactions are executed in a manner that respects the order implied by their timestamps, thus ensuring serializability. 9. Which of the following statements is true regarding the "timestamp" of a transaction? A) It is generated randomly to enhance performance. B) It is assigned based on the time of the transaction's arrival. C) It is constant throughout the transaction's lifecycle. D) It can be modified after the transaction starts. Answer: B Explanation: The timestamp of a transaction is assigned based on the time of its arrival (or execution request) in the system, which establishes its priority over other transactions. 10. Which concurrency control protocol allows transactions to read uncommitted data? A) Timestamp-based B) Strict Two-Phase Locking C) Snapshot Isolation D) Read Committed Answer: C Explanation: Snapshot Isolation allows transactions to read uncommitted data, providing a view of the database as of a certain point in time, while still preventing anomalies by controlling how changes are committed. DDL (Data Definition Language) 1. Which of the following statements is used to create a new table in SQL? A) CREATE TABLE B) NEW TABLE C) ADD TABLE D) DEFINE TABLE Answer: A Explanation: The CREATE TABLE statement is used to create a new table in the database, specifying the table name and its columns along with their data types. 2. What does the DROP TABLE statement do? A) Deletes all data from a table B) Removes a table and all its data from the database C) Modifies the structure of a table D) Creates a backup of the table Answer: B Explanation: The DROP TABLE statement removes a table and all its associated data and structure from the database permanently. 3. Which of the following commands is used to modify the structure of an existing table? A) CHANGE TABLE B) UPDATE TABLE C) ALTER TABLE D) MODIFY TABLE Answer: C Explanation: The ALTER TABLE command is used to modify the structure of an existing table, such as adding or dropping columns or changing data types. 4. Which of the following DDL commands is used to enforce a unique constraint on a column? A) CREATE UNIQUE B) ADD UNIQUE C) ALTER UNIQUE D) CREATE TABLE Answer: D Explanation: The CREATE TABLE command can enforce a unique constraint on a column by specifying the UNIQUE keyword during table creation. 5. What is the purpose of the PRIMARY KEY constraint in a table? A) To ensure that all values in a column are unique B) To enforce referential integrity between two tables C) To identify each row in a table uniquely D) To allow duplicate values in a column Answer: C Explanation: The PRIMARY KEY constraint is used to identify each row in a table uniquely, ensuring that no two rows have the same key value. 6. What does the command TRUNCATE TABLE do? A) Deletes the table structure B) Removes all rows from a table without logging individual row deletions C) Modifies the table structure D) Creates a new table based on an existing one Answer: B Explanation: The TRUNCATE TABLE command removes all rows from a table without logging individual row deletions, which is typically faster than a DELETE statement. 7. Which of the following statements is true regarding the CREATE INDEX command? A) It can only be used on primary keys. B) It speeds up data retrieval operations. C) It automatically deletes duplicate rows. D) It can only be applied to a single column. Answer: B Explanation: The CREATE INDEX command is used to create an index on a table, which speeds up data retrieval operations but does not affect the data itself. 8. What is the correct syntax for renaming an existing table? A) RENAME TABLE old_name TO new_name B) CHANGE TABLE old_name TO new_name C) ALTER TABLE old_name RENAME new_name D) UPDATE TABLE old_name SET name = new_name Answer: A Explanation: The correct syntax for renaming an existing table is RENAME TABLE old_name TO new_name, which changes the name of the specified table. 9. Which command would you use to add a new column to an existing table? A) ADD COLUMN B) INSERT COLUMN C) ALTER TABLE D) UPDATE TABLE Answer: C Explanation: The ALTER TABLE command is used to add a new column to an existing table by using the ADD COLUMN clause within it. 10. What will happen if you try to drop a table that is referenced by a foreign key constraint? A) The table will be dropped without any errors. B) The foreign key constraint will be automatically removed. C) An error will occur, preventing the drop operation. D) The related table will also be dropped. Answer: C Explanation: An error will occur if you try to drop a table that is referenced by a foreign key constraint in another table, preventing the drop operation to maintain referential integrity. DML (Data Manipulation Language) 11. Which SQL statement is used to insert new data into a table? A) ADD DATA B) INSERT INTO C) CREATE DATA D) APPEND TO Answer: B Explanation: The INSERT INTO statement is used to add new records (rows) into a specified table in the database. 12. What will happen if you try to insert a duplicate value into a column defined with a UNIQUE constraint? A) The value will be accepted, and a warning will be generated. B) The insertion will be ignored silently. C) An error will occur, preventing the insertion. D) The existing value will be updated with the new one. Answer: C Explanation: If you try to insert a duplicate value into a column defined with a UNIQUE constraint, an error will occur, preventing the insertion to maintain data integrity. 13. Which command is used to update existing records in a table? A) MODIFY B) UPDATE C) CHANGE D) SET Answer: B Explanation: The UPDATE command is used to modify existing records in a table, allowing changes to be made to one or more columns. 14. What is the purpose of the DELETE statement in SQL? A) To remove all rows from a table B) To drop a table structure from the database C) To modify data in a table D) To remove specific rows from a table based on a condition Answer: D Explanation: The DELETE statement is used to remove specific rows from a table based on a condition provided in the WHERE clause. 15. Which SQL command is used to retrieve data from a database? A) GET B) FETCH C) SELECT D) PULL Answer: C Explanation: The SELECT command is used to retrieve data from one or more tables in a database, allowing various options for filtering and sorting. 16. What is the effect of using the WHERE clause in an UPDATE statement? A) It applies the update to all records in the table. B) It restricts the update to specific records that meet the condition. C) It prevents any updates from occurring. D) It automatically creates a backup of the updated records. Answer: B Explanation: The WHERE clause in an UPDATE statement restricts the update to specific records that meet the defined condition, preventing unintended changes to all records. 17. Which command would you use to remove all records from a table while preserving its structure? A) DELETE B) REMOVE C) DROP D) TRUNCATE Answer: D Explanation: The TRUNCATE command removes all records from a table while preserving its structure, unlike the DELETE command, which can also be used but requires logging each row deletion. 18. What is the purpose of the LIMIT clause in a SELECT statement? A) To specify the order of retrieved records B) To set the maximum number of records returned C) To filter records based on a condition D) To group records for aggregation Answer: B Explanation: The LIMIT clause in a SELECT statement is used to set the maximum number of records returned by the query, allowing for pagination and control over result sets. 19. What will happen if you run a DELETE statement without a WHERE clause? A) The operation will be canceled. B) All rows in the table will be deleted. C) A prompt will ask for confirmation. D) An error will occur, preventing the deletion. Answer: B Explanation: If you run a DELETE statement without a **

RPSC MCQs SQL 2011.pdf

Document Details

Related

Full Transcript

Upgrade to continue