Question

Assume that the following table is created and stored in your database:

Staff (staff_no, name, position, salary, sex, job_id, hire_date)

Write SQL statements to do the following:

a. Increment the salary of managers by 5%;
b. Delete the records of all 'salesmen' from the Staff table;
c. Delete the record for the staff whose name is 'Smith' and staff number is 17.
d. Insert a new staff <18, 'Johnson', 'salesman, 2500, 'F'> in the database.
e. Update the salaries for all staff who have one of the job IDs similar to those in department 5.

Answer 1

a. update Staff set salary=salary*1.05 where position='manager'

b. delete from Staff where position='salesman'

c. delete from Staff where name='Smith' and staff_no=17

d. insert into Staff (staff_no, name, position, salary, sex) values (18, 'Johnson', 'salesman', 2500, 'F')

e. update Staff set salary=2000 where job_id like '5%'

Step-by-step explanation:

To update a column in a table 'update' command is used.

To remove a record from the table 'delete' command is used

To add a new record to the table 'insert into' command is used.