SQL*PLUS 9.

EJERCICIOS RESUELTOS MANIPULACIÓN

1.-Insertar un nuevo departamento alumnos con el código 44 sin localidad.

insert into department

values(44,'ALUMNOS',null);

-------- recuerda --------

COMMIT: guarda lo cambiado en la sesión O haciendo exit.

ROOLBACK: elimina lo guardado en memoria desde el inicio de la sesión.

2.-Insertar un empleado nuevo llamado JUAN GARCIA que va a trabajar en las

mismas condiciones que ALICE y va a ser su código + 1.

insert into employee

select employee_id + 1 , 'GARCIA', 'JUAN', middle_initial, job_id, manager_id,
sysdate, salary, commission, department_id
from employee where first_name = 'ALICE';

3.-Al final no se incorpora JUAN GARCÍA, sino que se incorpora PEPE PEREZ con
las mismas condiciones. Modificar el registro.

update employee
Set first_name = 'PEPE', last_name= 'PEREZ'
Where first_name ='JUAN';

4.-Subir el sueldo un 10% a los empleados del departamento 20 y un 5% al resto

Update employee set salary =salary * 1,05

Where department _id <> 20;

Update employee set salary * 1,10

Where department_id =20;

5.-Poner una comisión del 10% del salario a los empleados cuya función sean
analistas:

Update employee
Set commission =salary *0.10
Where job_id = (select job_id
from job
Where function='ANALYST');

Página 1 de 8
 SQL*PLUS 9. EJERCICIOS RESUELTOS MANIPULACIÓN

6.- Poner a SHAPE UP con el máximo crédito

update customer
Set credit_limit= (select max(credit_limit)
from customer)
Where name='SHAPE UP';

7.- Modificar el nombre de todos los departamentos anteponiendo su código de

departamento – y el resto del nombre.

update department
Set name=department_id||' '||name;

8.- Borrar todos los productos cuya fecha de comienzo sea enero de 1989

delete from Price

Where to_char(start_date, 'yyyy-mm')= '1989-01';

9.- poner una comisión del 10% del salario a aquellos empleados del
departamento 20 que no tienen comision

Update employee
Set commission = salary * 0.1
Where department_id =20 and commission is null;

10.- Modificar, del departamento 20, aquellos empleados que tienen sueldo
máximo, poniéndole como comisión el 50% del salario.

Update employee
Set commission= salary*0.5
Where department_id=20 and salary= (select max(salary)
From employee
Where department_id=20);

11.- Pon una comisión del 20% del salario al empleado que tenga mayor
comisión.

Update employee
Set commission= salary*0.2
Where commission = (select max(commission)
From employee);

Página 2 de 8
 SQL*PLUS 9. EJERCICIOS RESUELTOS MANIPULACIÓN

12. Aumentar el salario un10% mas la comisión a los empleados que trabajan en
el departamento 30 y tienen un salario inferior a la media salarial de su departamento,
estos empleados han de tener como función de trabajo analista.

update employee
Set salary=salary*1.1 +nvl(commission,0), job_id = (select job_id
From job
Where function='ANALYST')
Where department_id=30 and salary< ( select avg(salary)
From employee
Where department_id=30);

13.- El producto que menos se ha vendido ha de pasar a llamarse Patatas fritas.

Update product
Set description = 'Patatas fritas'
Where product_id = ( select product_id
From item
Group by product_id
Having sum(quantity) = ( select min(sum(quantity))
From item
Group by product_id));

14.- Dar de alta dos clientes cuyo vendedor asociado sea el 7499 y tengan como
crédito limite 6000.

Las restricciones para insertar son:

a.- campo customer_id que ha de tener valor único por ser primary_key.
b.- que el campo state ha de estar en mayúsculas
c.- el campo zip_code ha de tener 5 ó 9 caracteres
d.- el campo salesperson_id ha de tener un valor que coincida con alguno de los
valores del campo employee_id de la tabla employee.
Si los valores en dichos campos no cumplen las restricciones no nos deja
insertar.

insert into custome

Values(150, 'deportes Julio', 'lago 15', 'MADRID', 'MA',28045,23,5769823,7499,6000, 'ninguno');

Insert into customer

Values (151, 'deportes juan', 'hongo 43', ' TOLEDO', 'CM',45700,46,8789800,7499,6000, 'ninguno');

Página 3 de 8
 SQL*PLUS 9. EJERCICIOS RESUELTOS MANIPULACIÓN

15.- A los empleados con menos antigüedad en la empresa, considerando menos

antigüedad a todos los que entraron el ultimo año que se contrató, les vamos a poner
como fecha de entrada en la empresa 01/01/2006, el salario de DOYLE y como jefe a
JOHN.

Update employee
Set hire_date = to_date('01012006'), salary = ( select salary
From employee
Where last_name='DOYLE'), manager_id= (select employee_id
From employee
Where first_name='JOHN')
Where to_char(hire_date,'yyyy') = (select max ( to_char(hire_date,'yyyy'))
From employee);

16.- Insertar un empleado de apellido Fernández , nombre Julián,con nº de

identificación 6000, la fecha de alta será la de hoy el salario el de GREGORY mas el 20%
y el resto de los datos los mismos que los de GREGORY.
Insert into employee
Select 6000, 'fernandez', 'julian', middle_initial, job_id, manager_id, sysdate, salary*1.2, commission, department_id
From emplee
Where first_name=' GREGORY';

17.- Modificar el nº de departamento de Julian Fernández, el nº de

departamento será el departamento donde hay más empleados cuya función sea
CLERK.

Update employee
Set department_id = (select department_id
From employee
Where job_id = (select job_id
From job
Where function='CLERK')
Group by department_id
Having count(*) = (select max(count(*))
From employee
Where job_id = ( select job_id
From job
Where function='CLERK')
Group by department_id))
Where last_name='Fernandez' and first_name='julian';

Página 4 de 8
 SQL*PLUS 9. EJERCICIOS RESUELTOS MANIPULACIÓN

18.- Borrar todos los departamentos de la tabla departamentos para los cuales no
existen empleados en la tabla empleados.

Delete from department

Where department_id in ( Select department_id
From department
Minus
Select department_id
From employee);

19.- Borrar aquellos clientes que no han realizado compras.

Delete from customer

Where customer_id not in ( Select distinct (customer_id)
From sales_order);

20.- Borrar todos los empleados cuyo jefe es DOYLE.

Delete from item

Where order_id in ( select order_id
From sales_order
Where customer_id in ( select customer_id
From customer
Where salesperson_id in ( select employee_id
From employee
Where manager_id = ( select employee_id
From employee
Where last_name='DOYLE'))));

Delete from sales_order

Where customer_id in ( select customer_id
From customer
Where salesperson_id in ( select employee_id
From employee
Where manager_id = ( select employee_id
From employee
Where last_name='DOYLE')));

Delete from customer

Where salesperson_id in ( select employee_id
From employee
Where manager_id = ( select employee_id
From employee
Where last_name='DOYLE'));

Delete from employee

Where manager_id= ( select employee_id
From employee
Where last_name='DOYLE');

Página 5 de 8
 SQL*PLUS 9. EJERCICIOS RESUELTOS MANIPULACIÓN

21.- Borrar el departamento 43

1º.- Borrar los empleados que no son del departamento 43 cuyo manager_id pertenece al
departamento 43 (Para borrar un empleado que es manager en el departamento 43 este no tiene que ser
jefe de ningun empleado en ningun departamento)

delete from item

where order_id in (select order_id
from sales_order
where customer_id in ( select customer_id
from customer
where salesperson_id in (select employee_id
from employee
where department_id!=43 and manager_id in ( select employee_id
from employee
where department_id=43))));

delete from sales_order

where customer_id in ( select customer_id
from customer
where salesperson_id in (select employee_id
from employee
where deparment_id!=43 and manager_id in ( select employee_id
from employee
where department_id=43)));

delete from customer

where salesperson_id in (select employee_id
from employee
where department_id!=43 and manager_id in ( select employee_id
from employee
where department_id=43));
delete from employee
where department_id!=43 and manager_id in ( select employee_id
from employee
where department_id=43);

2º.- Borrar todos los empleados que pertenecen al departamento 43 y su manager tb al 43

delete from item

where order_id in (select order_id
from sales_order
where customer_id in ( select customer_id
from customer
where salesperson_id in (select employee_id
from employee
where department_id =43 and manager id in (select employee_id
from employee
where department_id =43))));
delete from sales_order
where customer_id in ( select customer_id
from customer
where salesperson_id in (select employee_id
from employee
where department_id =43 and manager _id in (select employee_id
from employee
where department_id =43)));

delete from customer

where salesperson_id in (select employee_id
from employee
where department_id = 43 and manager_id in (select employee_id
from employee
where department_id =43));

Página 6 de 8
 SQL*PLUS 9. EJERCICIOS RESUELTOS MANIPULACIÓN

delete from employee

where department_id=43 and manager_id in ( select employee_id
from employee
where department_id =43);

3º.- borrar los empleados que pertenecen al departamento 43 cuyo manager no pertenece al
departamento 43

delete from item

where order_id in (select order_id
from sales_order
where customer_id in ( select customer_id
from customer
where salesperson_id in (select employee_id
from employee
where department_id =43 and manager id in (select employee_id
from employee
where department_id!=43))));

delete from sales_order

where customer_id in ( select customer_id
from customer
where salesperson_id in (select employee_id
from employee
where department_id =43 and manager _id in (select employee_id
from employee
where department_id!=43)));

delete from customer

where salesperson_id in (select employee_id
from employee
where department_id = 43 and manager_id in (select employee_id
from employee
where department_id !=43));

delete from employee

where department_id=43 and manager_id in ( select employee_id
from employee
where department_id !=43);

4º.- borrar el departamento 43

delete from department

where department_id=43;

Página 7 de 8
 SQL*PLUS 9. EJERCICIOS RESUELTOS MANIPULACIÓN

comprobar el resultado del ejercicio anterior insertando los siguientes

empleados en la tabla empleados:

insert into employee

values(8000, 'camacho', 'maria', 'q',669,7820,sysdate,1300,50,20);

empleado que no pernetece al departamento 43 pero tiene como jefe a un

empleado del departamento 43.

insert into employee

values(9000, 'gonzalez', 'bernabe', 's',670,7820,sysdate,6000,600,43);

empleado del departamento 43 que tiene como jefe un empleado del

departamento 43.

Página 8 de 8