6.4.2. Relational Data Questions

Question 6

A developer needs to create a new Customers table with columns for CustomerID, Name, and Email. Which SQL command category should be used?

• A. DML (Data Manipulation Language)
• B. DDL (Data Definition Language)
• C. DCL (Data Control Language)
• D. TCL (Transaction Control Language)

Answer: B. DDL (Data Definition Language)

Explanation: DDL commands (CREATE, ALTER, DROP) define and modify database structure. Creating a table is a structural change. DML commands (SELECT, INSERT, UPDATE, DELETE) manipulate data within existing structures.

Question 7

A company is migrating a legacy application to Azure. The application uses SQL Server Agent jobs and cross-database queries. The IT team wants to minimize management overhead while maintaining compatibility. Which Azure SQL option should they choose?

• A. Azure SQL Database (Single Database)
• B. Azure SQL Database (Elastic Pool)
• C. Azure SQL Managed Instance
• D. SQL Server on Azure VM

Answer: C. Azure SQL Managed Instance

Explanation: Managed Instance provides near 100% compatibility with on-premises SQL Server, including SQL Agent and cross-database queries, while being fully managed (no OS/patching responsibility). SQL Database lacks these features; SQL on VM requires full management.

Question 8

A SaaS company has 200 customer databases with unpredictable usage patterns—some peak in the morning, others at night. They want to optimize costs while ensuring performance. Which deployment option is most cost-effective?

• A. 200 Single Databases (Basic tier)
• B. Azure SQL Managed Instance
• C. Azure SQL Database Elastic Pool
• D. SQL Server on Azure VM

Answer: C. Azure SQL Database Elastic Pool

Explanation: Elastic Pool allows multiple databases to share a pool of resources (eDTUs or vCores). When databases peak at different times, they can borrow from the shared pool, reducing overall provisioned capacity compared to individual databases.

Question 9

A database stores customer orders in a single table with columns: OrderID, CustomerName, CustomerAddress, ProductName, ProductPrice. When a customer moves, their address must be updated in hundreds of rows. What database design problem does this represent?

• A. Insert anomaly
• B. Update anomaly
• C. Delete anomaly
• D. Referential integrity violation

Answer: B. Update anomaly

Explanation: Update anomaly occurs when the same data is stored redundantly, requiring multiple updates to maintain consistency. Normalization (separating Customers into their own table) would store the address once, requiring only one update.