Facebook Comments Master

Este articulo ha sido leído 188 veces!

En la Universidad de Sonsonate en la instructoria de Base de Datos nos realizaron una evaluasion que me parecieron interesantes asi que se los comparto y mi forma de darle solucion es esta:

PARTE A.

  1. Crear un espacio de tabla llamado Espacio1 con las siguientes caracteristicas: 1 datafile llamado datos1, tamaño 10MB, autoextendible hasta 100MB.
  2. Crear un espacion de tabla temporal llamada tempo con las siguientes caracteristicas: Un tempfile llamado temporal1, tamaño 100MB
  3. Crear un esquema llamado examen1 con contraseña examen1 asignarle el espacio de tabla Espacio1 y el espacio de tabla temporal tempo, no olvidar asignarle los privilegios minimos para crear objetos:

 

Solucion:

 

CREATE SMALLFILE TABLESPACE Espacio1
DATAFILE 'c:/datos1.dbf'
SIZE 10M 
AUTOEXTEND ON NEXT 100M
online;
 

PARTE B

Crear las siguientes tablas:

  1. Campos char tamaño 4, campos varchar2 tamaño 60, campos number 10 con 2 decimales.
  2. No aceptar valores nulos en los campos id_trabajadores, nombre_completo, fecha_contratacion, id_departamento.
  3. El nombre_completo no debe permitir el ingreso de letras minusculas.
  4. El salario debe ser mayor a 8000.
  5. La fecha de contratacion debe tener por defecto la contratacion del sistema.
  6. Para las llaves foraneas de la tabla trabajadores aplicar borrado en cascada deje una anotacion explicando para que sirve esta restriccion.

 

–EJERCICIO 1
CREATE TABLE Departamentos(
Id_departamento char(4) not null,
Departamento varchar2(60),
primary key(Id_departamento)
) tablespace users;
–EJERCICIO 2
CREATE TABLE Trabajadores(
id_trabajador char(4) not null,
Nombre_completo varchar(60) check(Nombre_completo=upper(Nombre_completo)) not null,
Salario number(10,2) check (Salario>8000),
Fecha_contratacion date default sysdate not null,
Id_departamento char(4) not null,
primary key(Id_trabajador),
foreign key (Id_departamento) references Departamentos(Id_departamento) on delete cascade
–ON DELETE CASCADE: SIGNIFICA QUE SI BORRAMOS UNA FILA DE UNA TABLA MAESTRA, LAS DE DETALLE RELACIONADAS CON ESA FILA EN REFERENCIA SERAN BORRADAS TAMBIEN
) tablespace Espacio1;
 

PARTE C. Realizar las siguientes consultas al esquema HR

  1. Mostrar el id del departamento, el nombre del departamento, la cantidad de empleados en cada departamento y el promedio de salario por departamento en formato con 2 decimales solo si dicho promedio es mayor  a 7000, tambien al final de la consulta mostrar el total de empleados por todos los departamentos, asi como el promedio de salario para esos empleados (En una sola consulta)

             Solucion propuesta:

select d.department_id as ID,d.department_name as departamento,
  count(e.employee_id) as "Cantidad de empleados", 
    CASE WHEN AVG(e.SALARY) > 7000 THEN to_char(round(AVG(e.SALARY),2))
                   WHEN AVG(e.SALARY) < 7001 THEN to_char(round(AVG(e.SALARY),0))
                   ELSE 'Ninguno'
    END AS "Promedio Salarios"
  FROM hr.departments d join hr.employees e on (d.department_id=e.department_id)
  GROUP BY d.department_id, d.department_name 
union 
select null,'TOTAL',sum(count(e.employee_id)),
       –to_char((SUM(AVG(e.SALARY))/count(avg(e.salary))))
       CASE WHEN SUM(AVG(e.SALARY))/count(avg(e.salary))>7000 THEN
                              to_char(round(SUM(AVG(e.SALARY))/count(avg(e.salary)),2))
                   WHEN SUM(AVG(e.SALARY))/count(avg(e.salary))<7001 THEN
                              to_char((SUM(AVG(e.SALARY))/count(avg(e.salary))))
                   ELSE 'Ninguno'
       END     
  FROM hr.departments d join hr.employees e on (d.department_id=e.department_id)
GROUP BY d.department_id, d.department_name;
  1. Mostrar el id del empleado, el primer nombre del empleado, el salario, el ISSS 3% que se le descontara al salario, el AFP (6.25%), el nombre del trabajador que posee, el nombre del departamento para el que trabaja, la ciudad y el estado al que pertenece el departamento solo si la ciudad no esta en Estados Unidos, no listar al presidente de la empresa.

 

             Solucion propuesta:

Mostrare 2 soluciones que propongo la primera mostrando todos los empleados y no mostrando sus campos de ciudad y estado si pertenece a US

 

  select e.employee_id as "ID empleado",e.first_name as "Primero Nombre", e.salary as "Salario", 
     e.salary*0.03 AS "ISSS", e.salary*0.625 AS "AFP",
     j.job_title as "Puesto", d.department_name AS "NOMBRE DEPARTAMENTO",
       case when l.country_id != 'US' then (to_char(l.city))
       ELSE ' ' END AS "CIUDAD (DEPARTAMENTO)",
       case when l.country_id != 'US' then (to_char(l.state_province ))
       ELSE ' ' END AS "ESTADO (DEPARTAMENTO)"
       from hr.employees e join hr.departments d
       on (d.department_id=e.department_id) join hr.jobs j on (e.job_id=j.job_id)
       join hr.locations l on (d.location_id=l.location_id) where j.job_title != 'President';
       –where l.country_id != 'US';
La segunda forma es esta, excluyendo los resultados de US:
select e.employee_id, e.first_name as Nombre, e.salary as Salario, 
(e.salary *0.03) as ISSS,(e.salary * 0.0625) as AFP, j.job_title as Trabajo,
d.department_name as Departamento, l.city as Ciudad , 
l.state_province as Estado from employees e , jobs j, departments d , 
locations l where (e.job_id = j.job_id and j.job_title != 'President') 
and (e.department_id = d.department_id) and (d.location_id = l.location_id 
and l.country_id !='US');

PARTE D. Realize los siguientes INSERT a las tablas anteriormente creadas recuerde que para ellos las tablas deben estar en el esquema HR

  • En la tabla departamentos  insertar todos los registros de la tabla departaments, pero solo los campos department_id y department_name.

 

insert into HR.DEPARTAMENTOS select d.department_id, d.department_name from HR.DEPARTMENTS d where '1' = '1';
  • En la tabla trabajadores insertar los registros de la tabla employees que cumplan con las restricciones establecidas para ella, pero solo los campos employee_id, first_name, salary, hire_date y department_id. RECORDAR TOMAR EN CUENTA LAS RESTRICCIONES DE LA TABLA  PARA HACER EL INSERT.

insert into HR.Trabajadores select e.employee_id, upper(e.first_name),e.salary, e.hire_date, e.department_id from HR.employees e where e.employee_id is not null and e.first_name is not null and e.hire_date is not null and e.department_id is not null and e.salary>8000;

Saludos!

Articulos similares:

  • No se encontraron articulos similares

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">