AMCAT SQL and Database Concepts Flashcards
6 cards from real AMCAT practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 6 AMCAT SQL and Database Concepts flashcards as text
What is the primary purpose of an index in a database?
Answer: To speed up data retrieval operations at the cost of additional storage and slower writes
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.
Which normal form eliminates transitive dependencies?
Answer: Third Normal Form (3NF)
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.
What will the following query return? SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Departments WHERE location = 'Bangalore');
Answer: Names of employees who work in departments located in Bangalore
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.
In a transaction, what does a SAVEPOINT allow you to do?
Answer: Roll back to a specific point within the transaction without undoing all changes
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.
Which of the following is true about a VIEW in SQL?
Answer: A view is a virtual table based on the result of a SELECT query
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.
What is a deadlock in a database system?
Answer: When two or more transactions are waiting for each other to release locks, creating a circular dependency
A deadlock occurs when two or more transactions hold locks on resources that the other transactions need, creating a cycle of dependencies where none can proceed. For example, Transaction A holds Lock 1 and waits for Lock 2, while Transaction B holds Lock 2 and waits for Lock 1.