Write an insert statement to add jennier lawrences

3.9 Write an INSERT statement to add Jennier Lawrence's information to the EMP table
Column Value:
empno 8385
name LAWRENCE
job ACTOR
boss 8182
hiredate 2012-08-28
salary 8500
comm NULL
deptno 50
Confirm that both Soderbergh and Lawrence are both in department 50 'Hollywood'.
3.10 Write a query.to retrieve the employee number, name, their manager's employee number,
department number and their department name for all employees in department 50.
The President Mr King has decided to that number 50 is bad luck and want's the movie department to be
department number 60.
3.11 Update the MOVIE department in the DEPT table so it is now department number (deptno)
60.
3.12 Rerun the query you wrote in 3.10. Is there any differences to your result set? Why?
HINT: Because we had cascade on the foreign key constraint the change to the primary key has been
applied to all foreign keys referencing the primary key.
3.13 Change your query so that you return all employees in department (deptno) 60.
Stephen Soderbergh has been fired over creative differences with the president of the company Mr King. Mr
King has demanded that Soderbergh be deleted from the EMP table.
3.14 Write the delete statement to remove Soderbergh from the EMP table
What happened? why did it happen?
3.15. Update the record or records that need to be changed before we can delete Soderbergh from
the Emp table, then delete Soderbergh from the EMP table.
3.16 USING DDL Drop the DEPT, EMP, SALGRADE and BONUS tables in that order

11
INFO20003-2017-S2-Lab5
0
Section 4: Additional SQL to try:
4.1 List the names of the departments on the second floor
4.2 List the names of items delivered by each supplier. Arrange the report by supplier name, and
within supplier name, list the items in alphabetical order.
4.3 List the number of employees in each department.
4.4 Whom does Todd manage?
4.5 Find the first name of Sophie's boss.
4.6 List the names of each manager and their employees arranged by manager's name and
employee's name within manager.
4.7* Name the items delivered by at least two suppliers.
4.8 Who earns the lowest salary?
4.9 Find the department/s that sell at least 4 different items.
4.10 Find the departments that have made 4 or more transactions and list how many transactions
each department has made.

12
INFO20003-2017-S2-Lab5
Appendix A
Data in the tables
Delivery
Department

Adapted from Watson 2002, Data Management, Wiley 13
Employee
Item
Sale
Supplier

INFO20003 – T5 – S2 – 2017 14
Fig A. The Department Store Physical Model (rendered by MySQL Workbench)