AMCAT SQL and Database Concepts Flashcards

Consider the tables: Employee(emp_id, name, dept_id, salary) and Department(dept_id, dept_name). Which SQL query correctly finds the department name with the highest average salary?

Answer: SELECT dept_name FROM Department d JOIN Employee e ON d.dept_id = e.dept_id GROUP BY dept_name ORDER BY AVG(salary) DESC LIMIT 1

Option B correctly joins the tables, groups by department name, orders by average salary in descending order, and uses LIMIT 1 to get the top result. Option A uses nested aggregate MAX(AVG(...)) which is not valid in standard SQL. Option C misuses MAX without GROUP BY. Option D references salary in the wrong table.

What is the purpose of normalization in database design?

Answer: To eliminate data redundancy and reduce anomalies during insert, update, and delete operations

Normalization organizes data in a database to reduce redundancy and improve data integrity. By decomposing tables into smaller, well-structured relations, it prevents insertion, update, and deletion anomalies that arise from storing the same data in multiple places.

Which of the following SQL statements will add a new column 'email' of type VARCHAR(100) to an existing 'Employees' table?

Answer: ALTER TABLE Employees ADD COLUMN email VARCHAR(100)

ALTER TABLE is the DDL (Data Definition Language) command used to modify the structure of an existing table. The syntax 'ALTER TABLE table_name ADD COLUMN column_name datatype' adds a new column. INSERT is for data, UPDATE modifies existing data, and MODIFY TABLE is not standard SQL.

In a relational database, what does the ACID property 'Isolation' ensure?

Answer: That concurrent transactions do not interfere with each other and produce the same result as if executed sequentially

Isolation ensures that concurrent transactions execute independently without interfering with each other. The result of concurrent execution should be the same as if transactions were executed serially. Option A describes Atomicity, C describes Durability, and D describes Consistency.

What is the output of the following SQL query? SELECT COUNT(*) FROM Students WHERE marks > 80 AND marks IS NOT NULL; Given the Students table has 10 rows, where 3 have marks > 80, 2 have marks = NULL, and 5 have marks <= 80.

Answer: 3

The query counts rows where marks > 80 AND marks IS NOT NULL. Since any comparison with NULL returns UNKNOWN (not TRUE), the condition 'marks > 80' already excludes NULL values. Of the 10 rows, only 3 have marks > 80 (and these are not NULL), so the answer is 3.

Which type of JOIN returns all rows from both tables, including unmatched rows from either side?

Answer: FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables. Where there is a match, it combines the rows; where there is no match, it fills in NULLs for the missing side. INNER JOIN returns only matched rows, LEFT JOIN includes all left-table rows, and RIGHT JOIN includes all right-table rows.