AMCAT SQL and Database Concepts 3

Question 1

What is the primary purpose of an index in a database?

Accepted Answer

To speed up data retrieval operations at the cost of additional storage and slower writes

Answer

A database index is a data structure that improves the speed of data retrieval operations on a table. However, indexes require additional storage space and can slow down INSERT, UPDATE, and DELETE operations because the index must also be updated.

Question 2

Which normal form eliminates transitive dependencies?

Accepted Answer

Third Normal Form (3NF)

Answer

Third Normal Form (3NF) requires that all non-key attributes depend only on the primary key and not transitively through another non-key attribute. 1NF eliminates repeating groups, 2NF eliminates partial dependencies, and BCNF is a stricter version of 3NF.

Question 3

What will the following query return?
SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Departments WHERE location = 'Bangalore');

Accepted Answer

Names of employees who work in departments located in Bangalore

Answer

The subquery returns all dept_id values from departments located in Bangalore. The outer query then returns the names of employees whose dept_id matches any of those values. The IN operator checks for membership in the subquery result set.

Question 4

In a transaction, what does a SAVEPOINT allow you to do?

Accepted Answer

Roll back to a specific point within the transaction without undoing all changes

Answer

A SAVEPOINT creates a named marker within a transaction. If an error occurs later, you can ROLLBACK TO SAVEPOINT to undo changes made after that point while preserving changes made before it, without rolling back the entire transaction.

Question 5

Which of the following is true about a VIEW in SQL?

Accepted Answer

A view is a virtual table based on the result of a SELECT query

Answer

A VIEW is a virtual table that does not store data physically. It is defined by a SELECT query, and every time the view is queried, the underlying query is executed. Views provide abstraction, security, and simplification of complex queries.

AMCAT Practice Test

AMCAT Practice Test

AMCAT SQL and Database Concepts 3