Understanding the University Database Schema

The question asks us to find the correct SQL query to update the salary of employees belonging to the 'SCIENCE' department. To do this, we need to understand how the employee and department information is stored and linked in the database. Let's look at the structure of the relevant tables provided:

From this schema, we can see that:

• The EMPLOYEE table holds employee details, including their SALARY and DEPTNO (Department Number).
• The DEPARTMENT table holds department details, including the DNAME (Department Name) and DID (Department ID).
• The DEPTNO column in the EMPLOYEE table is a foreign key that references the DID column in the DEPARTMENT table. This is the link between employees and their departments.

Our goal is to update the SALARY column in the EMPLOYEE table for those employees whose DEPTNO matches the DID of the department where DNAME is 'SCIENCE'. The salary needs to be increased by 20%, which means multiplying the current salary by 1.20 (Current Salary $\times$ 1.20).

Analyzing the Proposed SQL Queries for Salary Increase

Let's examine each provided query to see which one correctly performs this task.

Analysis of Query (A)

Query (A) is:

UPDATE EMPLOYEE
SET SALARY = SALARY*1.20
WHERE DEPT NO. IN (SELECT DID FROM DEPARTMENT WHERE DNAME='SCIENCE');

• This query starts with UPDATE EMPLOYEE, which is the standard SQL command to modify data in the EMPLOYEE table. This is the correct table to update as it contains the SALARY column.
• The clause SET SALARY = SALARY*1.20 correctly calculates the new salary by increasing the current salary by 20%.
• The WHERE clause uses a subquery: (SELECT DID FROM DEPARTMENT WHERE DNAME='SCIENCE'). This subquery finds the DID (Department ID) for the department whose DNAME is 'SCIENCE'.
• The outer condition WHERE DEPT NO. IN (...) filters the rows in the EMPLOYEE table, selecting only those where the DEPTNO matches one of the DID values returned by the subquery. Since DNAME is unique, the subquery will return a single DID corresponding to 'SCIENCE'.
• Assuming DEPT NO. in the query refers to the DEPTNO column in the schema, this query correctly identifies employees in the 'SCIENCE' department and updates their salary.

Query (A) uses the appropriate command and logic to achieve the desired salary update based on the department name.

Analysis of Query (B)

Query (B) is:

UPDATE TABLE EMPLOYEE
SET SALARY = SALARY*1.20
WHERE DNAME='SCIENCE’;

• The syntax UPDATE TABLE EMPLOYEE is not the standard SQL syntax for updating a table. The correct syntax is simply UPDATE EMPLOYEE.
• The WHERE DNAME='SCIENCE’ condition attempts to filter the EMPLOYEE table based on the DNAME column. However, the EMPLOYEE table does not contain a DNAME column. Department names are stored in the DEPARTMENT table.

Query (B) has syntactic errors and incorrect logic for filtering employees by department name.

Analysis of Query (C)

Query (C) is:

ALTER TABLE EMPLOYEE
SET SALARY=SALARY*1.20
WHERE DEPTNO. IN (SELECT DNAME FROM DEPARTMENT WHERE DNAME ='SCIENCE’)

• The command used here is ALTER TABLE EMPLOYEE. This command is used to modify the structure of a table (like adding or removing columns, changing data types), not to update the data within rows. The correct command for updating data is UPDATE.
• The SET SALARY=SALARY*1.20 syntax is used with an UPDATE command, not ALTER TABLE.
• The WHERE clause contains a subquery (SELECT DNAME FROM DEPARTMENT WHERE DNAME ='SCIENCE’). This subquery selects the department name string 'SCIENCE'.
• The condition becomes WHERE DEPTNO. IN ('SCIENCE'). This tries to compare an integer column (DEPTNO) with a string literal ('SCIENCE'). This is a data type mismatch and will not correctly identify the target employees. To link correctly, DEPTNO should be compared with the department's DID (which is an integer).

Query (C) uses the wrong command and has logical errors in its filtering condition.

Conclusion on Correct Salary Update Query

Comparing the three queries, only Query (A) correctly uses the UPDATE statement, targets the EMPLOYEE table, specifies the correct salary increase calculation, and employs a valid method (using a subquery with DEPTNO and DID) to identify the employees belonging to the 'SCIENCE' department based on the database schema.

Revision Table: SQL Data Modification Basics

Additional Information: Referencing Related Tables in SQL

When you need to update or select data from one table based on a condition in another related table, you typically use joins or subqueries. Query (A) effectively uses a subquery to find the relevant department ID (DID) and then uses that ID to filter the employees based on their DEPTNO.

Another common way to perform updates based on related tables, depending on the specific SQL database system, is using a JOIN within the UPDATE statement. For instance, in some systems, you might write something similar to:

UPDATE EMPLOYEE E
SET E.SALARY = E.SALARY * 1.20
FROM EMPLOYEE E
JOIN DEPARTMENT D ON E.DEPTNO = D.DID
WHERE D.DNAME = 'SCIENCE';

However, the subquery method shown in Query (A) is widely supported and a valid approach for this task.