AMCAT SQL and Database Concepts

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?

Accepted 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

Answer

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)

Answer

SELECT dept_name, MAX(salary) FROM Department d JOIN Employee e ON d.dept_id = e.dept_id

Answer

SELECT dept_name FROM Department WHERE salary = MAX(salary)

Question 2

What is the purpose of normalization in database design?

Accepted Answer

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

Answer

To increase data redundancy for faster reads

Answer

To combine all data into a single table for simplicity

Answer

To encrypt sensitive data in the database

Question 3

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

Accepted Answer

ALTER TABLE Employees ADD COLUMN email VARCHAR(100)

Answer

INSERT INTO Employees ADD COLUMN email VARCHAR(100)

Answer

UPDATE TABLE Employees ADD email VARCHAR(100)

Answer

MODIFY TABLE Employees ADD email VARCHAR(100)

Question 4

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

Accepted Answer

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

Answer

That a transaction either completes fully or not at all

Answer

That once a transaction is committed, its changes persist even after a system failure

Answer

That the database moves from one valid state to another

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.

Accepted Answer

3

Answer

10

Answer

5

Answer

8

Question 6

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

Accepted Answer

FULL OUTER JOIN

Answer

INNER JOIN

Answer

LEFT JOIN

Answer

RIGHT JOIN