LU07.A03 - INSERT INTO

• Work type: Individual
• Timeframe: 15 Minutes
• Means of aid:
  • Only teaching materials, no websearch, no use of ai.
• Expected Result: employee's data are updated according to the requirements below

In assignment A2 you imported 30 lines of date to the table 'employees'. In this assignment your task is to perform some DML update commands.

As a company policy the attribute 'sex' is not required anymore and is to be emptied. Formulate the corresponding SQL statement that deletes all content from that column.

UPDATE employees
SET sex = ''
WHERE sex = 'M' OR sex = 'F';

After performing the update statement, the select on the table 'employee' shows that the column 'sex' is now empty.

The performance of our company's IT department was outstanding last year, resulting in a pay rise to 70'000 for all IT employees earning less than CHF 70,000. Create a DML update command that covers the requirements.

to make sure, that the outcome is correct we first need to find the rows concorned. The following SQL statements will give us the correct resultset.

Select *
FROM employees
WHERE department = 'IT'
AND salary < 70000;

This execution of the select results in the following image:

After executing the following update command, we check the result, in which the 4 lines the ‘Salary’ column are now updated to CHF 70'0000,-.

UPDATE employees
SET salary = 70000
WHERE department = 'IT'
AND salary < 70000;

Select *
FROM employees
WHERE department = 'IT';

Lösung

Volkan Demir