1. ACID properties of a database transaction stand for: A) Access, Concurrency, Integrity, Durability B) Atomicity, Consistency, Isolation, Durability C) Atomicity, Concurrency, Integrity, Distribution D) Access, Consistency, Isolation, Distribution ✅ Answer: B) Atomicity, Consistency, Isolation, Durability 💡 Explanation: ACID: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions), Durability (committed data persists). ──────────────────────────────────────────────────────────────────────────────── 2. In SQL, which command removes all rows from a table without removing the table structure? A) DELETE B) DROP C) TRUNCATE D) REMOVE ✅ Answer: C) TRUNCATE 💡 Explanation: TRUNCATE removes all rows quickly and cannot be rolled back, but preserves table structure unlike DROP. ──────────────────────────────────────────────────────────────────────────────── 3. A primary key must be: A) Unique only B) Non-null only C) Both unique and non-null D) Neither unique nor non-null ✅ Answer: C) Both unique and non-null 💡 Explanation: A primary key must uniquely identify each record and cannot contain NULL values. ──────────────────────────────────────────────────────────────────────────────── 4. Which normal form deals with transitive dependencies? A) 1NF B) 2NF C) 3NF D) BCNF ✅ Answer: C) 3NF 💡 Explanation: 3NF (Third Normal Form) eliminates transitive dependencies (non-key attributes depending on other non-key attributes). ──────────────────────────────────────────────────────────────────────────────── 5. The SQL command 'HAVING' is used with: A) WHERE clause B) ORDER BY C) GROUP BY D) JOIN ✅ Answer: C) GROUP BY 💡 Explanation: HAVING filters groups created by GROUP BY, just as WHERE filters rows. It can use aggregate functions. ──────────────────────────────────────────────────────────────────────────────── 6. An 'index' in a database is used to: A) Sort data alphabetically B) Speed up data retrieval operations C) Enforce data integrity D) Backup data ✅ Answer: B) Speed up data retrieval operations 💡 Explanation: An index creates a data structure that allows faster lookup of records without scanning the entire table. ──────────────────────────────────────────────────────────────────────────────── 7. What is a 'foreign key' in a relational database? A) A key that uniquely identifies a row B) A key that references the primary key of another table C) A composite key D) An alternate key ✅ Answer: B) A key that references the primary key of another table 💡 Explanation: A foreign key establishes a referential relationship between two tables, ensuring data integrity. ──────────────────────────────────────────────────────────────────────────────── 8. Which type of JOIN returns all rows from both tables, with NULLs where there is no match? A) INNER JOIN B) LEFT JOIN C) RIGHT JOIN D) FULL OUTER JOIN ✅ Answer: D) FULL OUTER JOIN 💡 Explanation: FULL OUTER JOIN returns all records from both tables, with NULL values for unmatched rows from either side. ──────────────────────────────────────────────────────────────────────────────── 9. NoSQL databases are preferred over SQL databases when: A) Data is highly structured B) ACID compliance is critical C) Handling large volumes of unstructured or semi-structured data D) Complex transactions are needed ✅ Answer: C) Handling large volumes of unstructured or semi-structured data 💡 Explanation: NoSQL is preferred for big data, flexible schemas, horizontal scaling with unstructured or semi-structured data. ──────────────────────────────────────────────────────────────────────────────── 10. In ER diagram, a diamond shape represents: A) Entity B) Attribute C) Relationship D) Key attribute ✅ Answer: C) Relationship 💡 Explanation: In ER (Entity-Relationship) diagrams: rectangles = entities, ellipses = attributes, diamonds = relationships. ──────────────────────────────────────────────────────────────────────────────── 11. 'Deadlock' in a database occurs when: A) Database server crashes B) Two or more transactions wait indefinitely for each other to release locks C) Network connection is lost D) Query takes too long ✅ Answer: B) Two or more transactions wait indefinitely for each other to release locks 💡 Explanation: Database deadlock: Transaction A holds lock needed by B, and B holds lock needed by A — circular wait. ──────────────────────────────────────────────────────────────────────────────── 12. Which SQL clause is used to combine rows from two or more tables based on a related column? A) UNION B) JOIN C) MERGE D) COMBINE ✅ Answer: B) JOIN 💡 Explanation: JOIN combines rows from multiple tables based on a related column (usually foreign/primary key relationship). ──────────────────────────────────────────────────────────────────────────────── 13. Data warehousing is used primarily for: A) Real-time transaction processing B) Analytical and reporting purposes C) Storing small volumes of data D) Network monitoring ✅ Answer: B) Analytical and reporting purposes 💡 Explanation: Data warehouses store historical, consolidated data for analytics, business intelligence, and reporting (OLAP). ──────────────────────────────────────────────────────────────────────────────── 14. Which of the following is a document-oriented NoSQL database? A) Redis B) Cassandra C) MongoDB D) Neo4j ✅ Answer: C) MongoDB 💡 Explanation: MongoDB is a document-oriented NoSQL database storing data as JSON-like BSON documents. ──────────────────────────────────────────────────────────────────────────────── 15. The CAP theorem states that a distributed system cannot simultaneously provide: A) Consistency, Availability, Performance B) Consistency, Availability, Partition tolerance C) Consistency, Accuracy, Performance D) Concurrency, Availability, Persistence ✅ Answer: B) Consistency, Availability, Partition tolerance 💡 Explanation: CAP Theorem: In a distributed system, only two of three can be guaranteed: Consistency, Availability, Partition Tolerance. ──────────────────────────────────────────────────────────────────────────────── 16. Which SQL function returns the total number of rows in a column? A) SUM() B) TOTAL() C) COUNT() D) MAX() ✅ Answer: C) COUNT() 💡 Explanation: COUNT() returns the number of rows matching a query condition. COUNT(*) counts all rows including NULLs. ──────────────────────────────────────────────────────────────────────────────── 17. Referential integrity in a database ensures: A) Data is encrypted B) Foreign key values match existing primary key values C) All queries are optimized D) Database is backed up ✅ Answer: B) Foreign key values match existing primary key values 💡 Explanation: Referential integrity ensures that relationships between tables remain consistent — no orphan foreign keys. ──────────────────────────────────────────────────────────────────────────────── 18. A stored procedure in a database is: A) A pre-compiled SQL program stored in the database B) A backup of the database C) A trigger event D) An index type ✅ Answer: A) A pre-compiled SQL program stored in the database 💡 Explanation: A stored procedure is a precompiled SQL code that can be saved and reused, improving performance and security. ──────────────────────────────────────────────────────────────────────────────── 19. Which key uniquely identifies a row in a table and is selected from candidate keys? A) Foreign Key B) Super Key C) Primary Key D) Composite Key ✅ Answer: C) Primary Key 💡 Explanation: The Primary Key is chosen from candidate keys to uniquely identify each row. It must be unique and NOT NULL. ──────────────────────────────────────────────────────────────────────────────── 20. ORM in the context of databases stands for: A) Object Relational Mapping B) Online Resource Manager C) Object Reference Model D) Optimized Retrieval Method ✅ Answer: A) Object Relational Mapping 💡 Explanation: ORM (Object Relational Mapping) allows developers to interact with databases using object-oriented programming instead of raw SQL. ────────────────────────────────────────────────────────────────────────────────