AMCAT SQL and Database Concepts 1 — Questions and Answers
Question 1: 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?
- SELECT dept_name FROM Department d JOIN Employee e ON d.dept_id = e.dept_id GROUP BY dept_name HAVING AVG(salary) = (SELECT MAX(AVG(salary)) FROM Employee GROUP BY dept_id)
- 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 (Correct answer)
- SELECT dept_name, MAX(salary) FROM Department d JOIN Employee e ON d.dept_id = e.dept_id
- SELECT dept_name FROM Department WHERE salary = MAX(salary)
Correct 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.
Question 2: What is the purpose of normalization in database design?
- To increase data redundancy for faster reads
- To eliminate data redundancy and reduce anomalies during insert, update, and delete operations (Correct answer)
- To combine all data into a single table for simplicity
- To encrypt sensitive data in the database
Correct 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.
Question 3: Which of the following SQL statements will add a new column 'email' of type VARCHAR(100) to an existing 'Employees' table?
- INSERT INTO Employees ADD COLUMN email VARCHAR(100)
- ALTER TABLE Employees ADD COLUMN email VARCHAR(100) (Correct answer)
- UPDATE TABLE Employees ADD email VARCHAR(100)
- MODIFY TABLE Employees ADD email VARCHAR(100)
Correct 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.
Question 4: In a relational database, what does the ACID property 'Isolation' ensure?
- That a transaction either completes fully or not at all
- That concurrent transactions do not interfere with each other and produce the same result as if executed sequentially (Correct answer)
- That once a transaction is committed, its changes persist even after a system failure
- That the database moves from one valid state to another
Correct 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.
Question 5: 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.
- 10
- 3 (Correct answer)
- 5
- 8
Correct 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.
Question 6: Which type of JOIN returns all rows from both tables, including unmatched rows from either side?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN (Correct answer)
Correct 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.
AMCAT SQL and Database Concepts 1
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?