Write an SQL statement which copies all of the data in the departments table int

Question

Write an SQL statement which copies all of the data in the departments table into a new table that is called department_copy. But, only copy those rows with a department_id not less than 20 or greater than 60. Also alter the table by writing the SQL statement that will add a primary key constraint which will be called dep_pk_2, which adds the department ID column as the primary key for the newly created table. Next, Update the department_copy table and set the manager id to NULL for all the departments having the department name as 'Shipping' and 'Marketing' (these departments no longer will have a manager managing it). In addition, update the manager_id on the departments table for the Accounting department with the values that are of employee by the last name of 'Lorentz' (write the query using a subquery).

Explanation / Answer

If the copy table structure is already created then the first step is not required.

Step 1: create table department_copy as select * from departments;

Step 2: insert into department_copy select * from departments where dept_id>=20 or dept_id >60;

Step 3:alter table departments add primary key (dep_pk_2);

Step 4: alter table department_copy add primary key (department_id);

Step 5: update department_copy set manager_id='' where department_name in ('Shipping','Marketing');

Step 6: update department set manager_id = ( select distinct manager_id from departments where last_name='Lorentz');

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.