DP_20_1_Practice_esp-editado

academy.oracle.com Programación de Bases de Datos con SQL 20-1 : Garantía de Resultados de Consultas de Calidad Técnica

Views 128 Downloads 3 File size 180KB

Report DMCA / Copyright

DOWNLOAD FILE

Citation preview

academy.oracle.com

Programación de Bases de Datos con SQL 20-1 : Garantía de Resultados de Consultas de Calidad Técnica Avanzada Actividades de Práctica Objetivos  

Crear una consulta para producir datos especificados Modificar una consulta para producir datos especificados

Inténtelo/Resuélvalo 1. Cree las tablas adicionales que se utilizan en esta sección ejecutando las siguientes sentencias: CREATE TABLE emp AS SELECT * FROM employees; CREATE TABLE dept AS SELECT * FROM departments; select chld.table_name "subject", chldcols.column_name "subject column name", chld.constraint_name "constraint_name in subject", chld.constraint_type "constraint_type in subject", prnt.table_name "parent of fk", prntcols.column_name "parent's column name", prnt.constraint_name "parent pk" from user_constraints chld left outer join user_constraints prnt on chld.r_constraint_name = prnt.constraint_name left outer join user_cons_columns chldcols on chld.constraint_name = chldcols.constraint_name left outer join user_cons_columns prntcols on prnt.constraint_name = prntcols.constraint_name where chld.table_name = upper('table_name'); 2.

Cree un informe que muestre el nombre de restricción, el tipo, el nombre de columna y la posición de columna de todas las restricciones de la tabla JOB_HISTORY, además de las restricciones no nulas. create table my_temp_table as (select cons.constraint_name, cons.constraint_type, cols.column_name, cols.position, to_lob(cons.search_condition) search_condition from user_constraints cons inner join user_cons_columns cols on cons.constraint_name = cols.constraint_name where cons.table_name = 'job_history' );

Copyright © 2019, Oracle y/o sus filiales. Todos los derechos reservados. Oracle y Java son marcas comerciales registradas de Oracle y sus filiales. Todos los demás nombres pueden ser marcas comerciales de sus respectivos propietarios.

select * from my_temp_table where not ( constraint_type = 'c' and column_name in (select column_name from user_tab_columns where table_name = upper('job_history') and nullable = 'n') and dbms_lob.compare(search_condition, concat('"', concat(column_name, '" is not null'))) = 0);

3. Cree una restricción de clave primaria en la columna employee_id de la tabla emp. alter table emp add constraint emp_employee_id_pk primary key (employee_id); select constraint_name, constraint_type, table_name, status, index_name from user_constraints where table_name = upper('emp') and constraint_type = 'p'; 4. Cree una clave primaria en la columna department_id de la tabla dept. alter table dept add constraint dept_department_id_pk primary key (department_id); select constraint_name, constraint_type, table_name, status, index_name from user_constraints where table_name = upper('dept') and constraint_type = 'p'; 5.

Agregue una restricción ajena entre DEPT y EMP, de modo que solo se puedan introducir departamentos válidos en la tabla EMP. Asegúrese de que puede suprimir cualquier fila de la tabla DEPT y de que se suprimen las filas a las que se hace referencia en la tabla EMP. alter table emp add constraint emp_dept_department_id_fk foreign key (department_id) references dept (department_id) on delete cascade;

Copyright © 2019, Oracle y/o sus filiales. Todos los derechos reservados. Oracle y Java son marcas comerciales registradas de Oracle y sus filiales. Todos los demás nombres pueden ser marcas comerciales de sus respectivos propietarios.

6.

Pruebe la restricción de clave ajena que acaba de crear: Cuente el número de filas en la tabla EMP. select count(*) from emp; Elimine el departamento 10 de la tabla dept. delete from dept where department_id = 10; Ahora vuelva a contar los empleados. Debería haber menos empleados. select count(*) from emp

Copyright © 2019, Oracle y/o sus filiales. Todos los derechos reservados. Oracle y Java son marcas comerciales registradas de Oracle y sus filiales. Todos los demás nombres pueden ser marcas comerciales de sus respectivos propietarios.

2

7.

Genere un informe que devuelva el apellido, el salario, el número de departamento y el salario medio de todos los departamentos en los que el salario es mayor que el salario medio. with avg_sal_by_dept as (select nvl(department_id, -1) dpt_id, avg(nvl(salary,0)) avg_sal from employees group by nvl(department_id, -1)) select emp.last_name "Apellido", to_char (round(emp.salary,2),'$999999.99') "Salario", case when avgqry.dpt_id = -1 then null else avgqry.dpt_id end "Numero del departamento", to_char(round(avgqry.avg_sal,2),'$999999.99') "average salary" from employees emp inner join (select * from avg_sal_by_dept) avgqry on nvl(emp.department_id, -1) = avgqry.dpt_id where emp.salary > avgqry.avg_sal;

8.

Cree una vista denominada V2 que devuelva el salario más alto, el salario más bajo, el salario medio y el nombre del departamento. create or replace view v2 ("salario más alto", "salario más bajo", "promedio salario", "nombre del departamento") as select to_char(round(max(nvl(emp.salary,0)),2),'$999999.99'), to_char(round(min(nvl(emp.salary,0)),2),'$999999.99'), to_char(round(avg(nvl(emp.salary,0)),2),'$999999.99'), dpt.department_name from departments dpt left outer join employees emp on dpt.department_id = emp.department_id group by (dpt.department_id, dpt.department_name); select * from v2;

Copyright © 2019, Oracle y/o sus filiales. Todos los derechos reservados. Oracle y Java son marcas comerciales registradas de Oracle y sus filiales. Todos los demás nombres pueden ser marcas comerciales de sus respectivos propietarios.

3

9.

Cree una vista denominada Dept_Managers_view que devuelva una lista de nombres de departamento junto con las iniciales y el apellido del jefe para dicho departamento. Pruebe la vista devolviendo todas sus filas. Asegúrese de que no se pueda actualizar ninguna fila a través de la vista. Pruebe a ejecutar una sentencia UPDATE en la vista. create or replace view dept_managers_view as select distinct substr(nvl(mgr.first_name, '_'),1, 1) || substr(mgr.last_name,1, 1) initials, mgr.last_name surname, dpt.department_name from employees mgr inner join employees emp on mgr.employee_id = emp.manager_id left outer join departments dpt on mgr.department_id = dpt.department_id; update dept_managers_view set surname = 'aydiosito' where department_name = ‘sales'; 10. Cree una secuencia denominada ct_seq con todos los valores por defecto. create sequence ct_seq;

Copyright © 2019, Oracle y/o sus filiales. Todos los derechos reservados. Oracle y Java son marcas comerciales registradas de Oracle y sus filiales. Todos los demás nombres pueden ser marcas comerciales de sus respectivos propietarios.

4 11. Examine la siguiente sentencia de inserción y corrija los errores. INSERT INTO emp (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (ct_seq.nextvalue, "Kaare", 'Hansen', 'KHANSEN', '44965 832123', sysdate, 'SA_REP', $6500, null, 100, 20); insert into dept (department_id, department_name, manager_id, location_id) values (20, 'marketing', 201, 1800); insert into emp (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) values (ct_seq.nextval, 'kaare', 'hansen', 'khansen', '44965 832123', sysdate, 'sa_rep', 6500, null, 100, 20); 12. Escriba la sentencia SQL para mostrar todas las tablas de usuario que contienen el nombre PRIV. select * from all_tables where regexp like(table_name, '(priv)'); 13. Conceda acceso de selección a público en la tabla EMP y verifique que se ha otorgado mediante la ejecución esta consulta. SELECT * FROM user_tab_privs WHERE table_name = 'EMP'; grant select on emp to public; 14. Sustituya ?? en la siguiente consulta mediante expresiones regulares para devolver solo los números de la siguiente cadena: 'Oracle Academy9547d6905%&^ db apex'. SELECT REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex',??,'') regexpreplace FROM DUAL; select regexp_replace('oracle academy9547d6905%&^ db apex','[^0-9]','') regexpreplace from dual;

Copyright © 2019, Oracle y/o sus filiales. Todos los derechos reservados. Oracle y Java son marcas comerciales registradas de Oracle y sus filiales. Todos los demás nombres pueden ser marcas comerciales de sus respectivos propietarios.

5

15. Corrija la consulta anterior mediante expresiones regulares para devolver el número de dígitos de la siguiente cadena: 'Oracle Academy9547d6905 %y;^ db' SELECT LENGTH(REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex','??','')) regexpreplace FROM DUAL; select length(regexp_replace('oracle academy9547d6905%&^ db apex','[^[:digit:]]','')) regexpreplace from dual; 16. Corrija la consulta de nuevo para devolver solo los caracteres no numéricos. SELECT REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex','??','') regexpreplace FROM DUAL; select regexp replace('oracle academy9547d6905%&^ db apex','[[:digit:]]','') regexpreplace from dual; select regexp replace('oracle academy9547d6905%&^ db apex','[0-9]','') regexpreplace from dual; 17. Mediante las uniones propiedad de Oracle, construya una instrucción que devuelva todos los employee_ids unidos a todos los department_names. select em.employee_id, dp.department_name from employees em, departments dp; 18. Vuelva a utilizar las uniones Oracle para corregir la sentencia anterior de modo que devuelva solo el nombre del departamento en el que está trabajando el empleado actualmente. select em.employee_id, dp.department_name from employees em, departments dp where em.department_id = dp.department_id; 19. Vuelva a utilizar las uniones Oracle para crear una consulta que muestre el apellido de los empleados, el nombre de departamento, el salario y el nombre del país de todos los empleados. select em.employee_id, dp.department_name from employees em, departments dp where em.department_id = dp.department_id;

Copyright © 2019, Oracle y/o sus filiales. Todos los derechos reservados. Oracle y Java son marcas comerciales registradas de Oracle y sus filiales. Todos los demás nombres pueden ser marcas comerciales de sus respectivos propietarios.

6 20. Vuelva a utilizar la sintaxis de unión de Oracle para modificar la consulta anterior, de modo que incluya también incluye el registro de empleado del empleado sin department_id, 'Grant'. select em.last_name "last name", dp.department_name "department name",em.salary, con.country_name "country name" from employees em, departments dp, locations loc, countries con where em.department_id = dp.department_id(+) and dp.location_id = loc.location_id(+) and loc.country_id = con.country_id(+);

Copyright © 2019, Oracle y/o sus filiales. Todos los derechos reservados. Oracle y Java son marcas comerciales registradas de Oracle y sus filiales. Todos los demás nombres pueden ser marcas comerciales de sus respectivos propietarios.