Ejercicios y mas Ejercicios

Aqui encontraras muchos ejercicios resueltos de todos los temas, se que en cualquier momento tener la guia de lo que deseamos realizar es buena técnica...Espero les sirvan de mucha ayuda.

 

--parcial--1

select * from tab;

--tab muestra las tablas del esquema

 select * from cat;--muestra los catalogos del esquema 

--2 la descripcion del comntenido de la tabla departmentsdesc departments;
select department_id ||'  ' || department_name as "NOMBRE DEPTO"from departments;

--3select distinct department_id from departments;

--4select e.last_name, d.department_name from employees e join departments don e.department_id = d.department_idwhere d.department_id in (10 , 60);

--5select first_name, TO_CHAR(salary*12, '$9,999,999.00') SALARY, to_char(hire_date, 'fmdd day Month YYYY') as fechafrom employees;

--6 select l.city, e.first_name, e.last_name, e.salaryfrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_idwhere e.commission_pct is  null order by salary; 

--7select sysdate from dual;

--8select upper (last_name), length (first_name) from employeeswhere  first_name like '%J' and first_name like '%M';

--9select last_name,  trunc (months_between  (sysdate , hire_date)) as "meses trabajados"from employees;

--10select last_name, first_name , trim (('sin comision')) as "sin comision"from employeeswhere commission_pct is null ;

--11select c.country_name, d.department_name, e.first_namefrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_idjoin countries c on l.country_id = c.country_idwhere d.department_id not in ('60');

--12select emp.first_name , jef.first_namefrom employees emp join employees jefon emp.manager_id = jef.employee_id;

--13select r.region_name, e.first_name, e.emailfrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_idjoin countries c on l.country_id = c.country_idjoin regions ron r.region_id = c.region_idwhere e.email like '@%';

--14--select d.department_name ||'  '|| e. first_name  ,lpad (to_char (trunc (months_between  (sysdate , hire_date)/12))) select d.department_name ||'  '|| e. first_name  ,lpad ((months_between  (sysdate , hire_date))) from employees e join departments don e.department_id = d.department_id;
select last_name, lpad (salary, 15, '*')from employees;

--15SELECT distinct job_title,      CASE job_id WHEN 'AD_PRES0' THEN 'A'                WHEN 'ST_MAN' THEN 'B'                WHEN 'IT_PROG' THEN 'C'                WHEN 'SA_REP' THEN 'D'                WHEN 'ST_CLERK' THEN 'E'                WHEN job_id THEN 'no'    ELSE JOB_ID END "COCO"FROM JOBS;

---------------------
--PRACTICA 4 PARTE 1--1

muestre el nombre de empleado y el id  y nombre de los departamentos select employees.last_name, departments.department_id, departments.department_namefrom employees , departments where employees.department_id = departments.department_id;--1.2select e.last_name, d.department_id, d.department_namefrom employees e join departments don (e.department_id = d.department_id);

--2 muestra todos los trabajos en el departamento 30 incluyedo la localizacion select distinct e.job_id, d.department_id, d.location_idfrom employees e , departments dwhere e.department_id = d.department_idand d.department_id= 30;

--2.2select distinct e.job_id, d.department_id, d.location_idfrom employees e join departments don (e.department_id = d.department_id)where d.department_id = 30;--3  muestre el apellido  el nombre del departamento el id de la localozacion y la ciudad, de los q tengan comisionselect e.last_name, d.department_name, l.location_id, l.cityfrom employees e , departments d, locations lwhere e.department_id = d.department_id      and d.location_id = l.location_id     and e.commission_pct is not null;

--3.2select e.last_name, d.department_name, l.location_id, l.cityfrom employees e join departments d 
---------------------------

select c.country_name, e.last_name, e.first_name, e.hire_datefrom employees e join departments d join locations l join countries con l.country_id = c.country_idon d.location_id = l.location_idon e.department_id = d.department_idwhere hire_date between '01-01-80' and '31-10-88';
select e.last_name, e.salaryfrom employees e join jobs j  join job_history jhon j.job_id = jh.job_idon e.job_id =j.job_id;
select e.last_name, e.salaryfrom employees e , jobs j, job_history jhwhere e.job_id =j.job_id  and j.job_id = jh.job_id(+);

select count (*) , max (e.salary), min (e.commission_pct), avg(e.salary), e.last_name, d.department_name from employees e join departments d on e.department_id  = d.department_id where hire_date > '31/10/1988' and d.department_name = 'Accounting' group by e.last_name, d.department_name; 

select department_name from departments;
-----------------------------

select * from tab;

select last_name ,nvl(to_char(commission_pct), 'sin comision') from employees;select d.department_name, concat ( e.first_name , e.last_name), rpad(' ', trunc(months_between(sysdate ,hire_date)/12)+1,'*')from employees e join departments don e.department_id = d.department_id;SELECT employee_id, last_name, salary, department_idFROM employeesWHERE employee_id = &employee_num ;SELECT last_name, department_id, salary*12FROM employeesWHERE job_id = '&job_title';
SELECT employee_id, last_name, job_id,&column_name, FROM employeesWHERE &conditionORDER BY &order_column ;
SELECT job_id "Job",SUM(DECODE(department_id , 20, salary)) "Dept 20",SUM(DECODE(department_id , 50, salary)) "Dept 50",SUM(DECODE(department_id , 80, salary)) "Dept 80",SUM(DECODE(department_id , 90, salary)) "Dept 90",SUM(salary) "Total"FROM employeesGROUP BY job_id;

--practica 8--1

crea un atabla llamada my_employee
create table MY_EMPLOYEE   (ID number(4) not null,  last_name varchar2(25),  first_name varchar2(25),  userid varchar2(8),  salary number(9,2));
  commit;

--2 describala desc my_employee;

--3 insert into my_employee (id,last_name, first_name, userid, salary) values (1, 'Patel', 'Rralph', 'rpatel', 895);insert into my_employee (id,last_name, first_name, userid, salary) values (2, 'Dancs', 'Betty', 'bdancs', 860);insert into my_employee (id,last_name, first_name, userid, salary) values (3, 'Biri', 'Ben', 'bbiri', 1100);insert into my_employee (id,last_name, first_name, userid, salary) values (4, 'Newman', 'Chad', 'cnewman', 750);insert into my_employee (id,last_name, first_name, userid, salary) values (5, 'Ropeburn', 'Audrey', 'aropebur', 1550);

--4 execute

--5 commitcommit;

--6--crear un .sql--10update my_employeeset last_name = 'Drexler'where id = 3;

--11 update my_employeeset salary  = 1000 where salary < 900;

--12create table tab1as  (select last_name apellido, salary salario, email correo from employees);
drop table tab1;select * from mi_primer_tabla;
alter table tab1  add nombre varchar2(25) not null;truncate table tab1;
alter table mi_primer_tabla--rename tab1 to mi_primer_tabla;rename column salario to plata; 
alter table mi_primer_tabladrop column plata; commit;select * from all_tab_comments where table_name = 'MI_PRIMER_TABLA';comment on table MI_PRIMER_TABLA is 'cojjjjj1';

---------------------

/*PRACTICA 14 Presentada por Jeimy Natalia Suarez 

drop table member;

drop table title;drop table title_copy;

drop table rental;drop table reservation;

drop SEQUENCE MEMBER_ID_SEQ;

drop SEQUENCE TITLE_ID_SEQ;

drop VIEW TITLE_AVAIL;--1

--a. Tabla Member, con sus correspondientes constraints.
create table Member(member_id number(10) constraint memberr_id_pk primary key, first_name varchar2(25),last_name VARCHAR2(25) CONSTRAINT member_last_name_nn NOT NULL,address varchar2(100), city varchar2 (30), phone varchar2(15), join_date DATE DEFAULT SYSDATE);

--create table correcto.

--b. 
create table title(title_id number (10) constraint title_id_pk primary key,title varchar2(60) constraint tittle_title_nn not null, description varchar2(400) constraint title_description_nn not null,rating varchar2(4) constraint title_rating_ck check (rating in ('G', 'PG', 'R', 'NC17', 'NR')), category varchar2(20) constraint title_category_ck check (category in ('DRAMA', 'COMEDY', 'ACTION','CHILD', 'SCIFI', 'DOCUMENTARY')), release_date date);

--create table correcto.--c
create table title_copy(copy_id number (10), title_id number (10),status varchar2(15)constraint title_copy_status_nn not null constraint title_copy_status_ck check (status in ('AVAILABLE', 'DESTROYED','RENTED','RESERVED')));--create table correcto.
alter table title_copyadd constraint copy_id_pk_ primary key(copy_id,title_id);--alter table title_copy correcto.
alter table title_copyadd constraint title_copy_title_id_fk foreign key (title_id) references title (title_id);--alter table title_copy correcto.

--d.
create table rental(book_date date default sysdate,member_id number(10),copy_id number (10) ,act_ret_date date,exp_ret_date date default sysdate + 2,title_id number (10));--create table correcto.
alter table rental add constraint renta_book_date_pk primary key (book_date, member_id, copy_id, title_id);--alter table rental correcto.
alter table rental add constraint ren_member_id_fk foreign key (member_id) references member (member_id);--alter table rental correcto.
alter table rental add constraint rent_copy_id_fk foreign key (copy_id, title_id) references title_copy (copy_id ,title_id);-- alter table rental correcto.

--e
create table reservation(res_date date,member_id number(10),title_id number(10));--create table correcto.
alter table reservationadd constraint reservation_res_date_pk primary key (res_date, member_id, title_id);--alter table reservation correcto.
alter table reservationadd constraint reservation_member_id_fk foreign key (member_id) references member (member_id);--alter table reservation correcto.
alter table reservationadd constraint reservation_title_id_fk foreign key (title_id) references title (title_id);--alter table reservation correcto.

--2.
select table_name from user_tableswhere  table_name in ( 'MEMBER','RENTAL', 'RESERVATION','TITLE','TITLE_COPY')
TABLE_NAME                     

------------------------------ 

--4.A
INSERT INTO TITLEVALUES (TITLE_ID_SEQ.NEXTVAL, 'Willie and Christmas Too', 'All of Willie’s friends make a Christmas list for Santa, but Willie has yet to add his own wish list','G','CHILD','05-OCT-1995')

1 rows inserted

INSERT INTO TITLEVALUES (TITLE_ID_SEQ.NEXTVAL, 'Alien Again', 'Yet another installation ofscience fiction history. Can the heroine save the planet from the alien life form?','R','SCIFI','19-MAY-1995')

1 rows inserted

INSERT INTO TITLE VALUES (TITLE_ID_SEQ.NEXTVAL, 'The Glob', 'A meteor crashes near a small American town and unleashes carnivorous goo in this classiC','NR','SCIFI', '12-08-1995')

1 rows inserted

INSERT INTO TITLE VALUES (TITLE_ID_SEQ.NEXTVAL, 'My Day Off', 'AWith a little luck and a lot of ingenuity, a teenager skips school for a day in New York','PG','COMEDY','12-JUL-1995')

1 rows insertedINSERT INTO TITLE VALUES (TITLE_ID_SEQ.NEXTVAL, 'Miracles on Ice', 'AWsix-year-old has doubts about Santa Claus, but she discovers that miracles really do exist.','PG','DRAMA','12-SEP-1995')
1 rows inserted

INSERT INTO TITLE VALUES (TITLE_ID_SEQ.NEXTVAL, 'Soda Gang', 'After discovering a cache of drugs, a young couple find themselves pitted against a vicious gang.','NR','ACTION','01-JUN-1995')

1 rows inserted

--4.B
INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Carmen' , 'Velasquez', '283 King Street','Seattle','206-899-6666','08-MAR-1990')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'LaDoris', 'Ngao', '5 Modrany ','Bratislava ','586-355-8882','08-MAR-1990')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Midori', 'Nagayama','68 ViaCentrale ','Sao Paolo ','254-852-5764','17-JUN-1991')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Mark', 'Quick-to-See','6921 King Way','Lagos','63-559-7777','07-04-1990')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Audr', 'Ropeburn' , '86 Chu Street' , 'Hong Kong' , '41-559-87' , '18-06-1991')

1 rows inserted

INSERT INTO MEMBER VALUES (MEMBER_ID_SEQ.NEXTVAL, 'Molly',' Urguhart ','3035 Laurier ','Quebec',' 418-542-9988',' 18-06-1991')

1 rows inserted

--4.c

                                   
CREATE VIEW TITLE_AVAILAS   select t.title ,tc.copy_id, tc.status, r.exp_ret_date  from title t join title_copy tc  on t.title_id = tc.title_id  join rental r  on tc.copy_id =  r.copy_id and tc.title_id = r.title_id
CREATE VIEW correcto.--6

--6.a
INSERT INTO TITLEVALUES (TITLE_ID_SEQ.NEXTVAL, 'Interstellar Wars', 'Futuristic interstellaraction movie. Can the rebels save the humans from the evil empire?','PG','SCIFI','07-JUL-77')

1 rows inserted

insert into title_copyvalues ('1','98','AVAILABLE')

1 rows inserted

insert into title_copyvalues ('2','98','AVAILABLE')

1 rows inserted

--6.b
insert into reservationvalues (sysdate, '101', '98')

1 rows inserted

insert into reservationvalues (sysdate, '104', '97')

1 rows inserted

commit
commited

--6.c
INSERT INTO rental(title_id, copy_id, member_id)VALUES (98,1,101)

1 rows inserted

UPDATE title_copySET status= 'RENTED'WHERE title_id = 98AND copy_id = 1
1 rows updatedDELETEFROM reservationWHERE member_id = 101
1 rows deletedSELECT * FROM title_availORDER BY title, copy_id

---suconsultas

SELECT last_nameFROM employeesWHERE salary =              (SELECT salary                FROM employees                WHERE last_name = 'Abel');

--empleados cuya job histori la fecha final seeste entre 1-01-80 31-10-98
select employee_id from 
;

select salary ,last_name, employee_idfrom employeeswhere employee_id in (  select employee_id   from job_history  where end_date between '01/01/1900' and '31/10/2000');  select distinct e.salary , e.last_name, e.employee_idfrom EMPLOYEES e join job_history jhon E.EMPLOYEE_ID = jh.employee_id  where end_date between '01/01/1900' and '31/10/2000';   

 -----traiga la ciudad donde el salario sea > a 1000select city from locations where location_id in  (select location_id from departments where department_id in     (select department_id from employees where salary > 10000));
select distinct city from LOCATIONS l join DEPARTMENTS don l.location_id = D.LOCATION_IDwhere department_id in (select department_id from EMPLOYEES where salary > 1000);

--empleados q el codigo postal no sea 2901 3095
select employee_id from employees where department_id in  (select department_id from departments where location_id in    (select location_id from locations where postal_code not in ('1730' ,'2901', '3095')));          

 --practica 6--1 Escribe una consulta que mostrar el apellido y fecha de contratación de los empleados en el mismo --departamento que . Excluir Zlotkey......select last_name, hire_date from employees where department_id =  (select department_id from employees where last_name = 'Zlotkey')and last_name <> 'Zlotkey';

--2 Mostrar el numero, el apellido de todos los  los empleados quienes ganan mas que en promedio --salarial, ordenarlo ascendentemente.  select employee_id , last_name , salary from employees where salary  <     (select avg (salary) from employees) order by salary;

  --3 Mostrar una consulta que muestre el numero y el apellido de todos los empleados quienes trabajan --en los departamento,  cuyo apellido contiene una u. select employee_id, last_name FROM employees where department_id in     (select department_id from employees where last_name like '%u%');

--4 Muestra el apellido, numero del departamento, y el id del trabajo  de todos los empleados,  --quienes el departamento esta localizado en 1700.select last_name , department_id, job_id FROM employees where DEPARTMENT_ID in(select department_id from DEPARTMENTS where location_id = 1700);

--5 Muestre el apellido y salario  de cada empleado, quienes reportan a kingselect last_name from EMPLOYEES where manager_id in  (select employee_id FROM EMPLOYEES where last_name = 'King');

--6 Muestre el numero del departamento, apellido y job_id en el departamento ejecutivoselect department_id, last_name , job_id from employees where department_id =   (select department_id from departments where DEPARTMENT_NAME = 'Executive');

--7 Modifica el punto 3, aumentandole el empleado que gane mas q el promedio.select employee_id, last_name, salary from employees WHERE department_id IN   (SELECT department_id FROM employees WHERE last_name like '%u%')    AND salary > (select round( avg (salary)) from employees);    




------------------------------

apellidos de los empleados que ganan mas de 12000select last_name, salary from employeeswhere salary > 12000;--Muestra apellidos y departamentos de los empleados que sean con codigo igual a 176select last_name, department_idfrom employeeswhere employee_id = 176;--3select last_name, salaryfrom employees where salary <= 5000 or salary >= 12000;where salary not between 5000 and 12000; 

--4 select last_name, job_id, hire_datefrom employeeswhere hire_date between '20/02/98' and '01/03/98'order by hire_date;

--Fecha actualselect sysdate from dual;--5select last_name as nombre , department_id as platafrom employeeswhere department_id in (20,50)order by first_name;--6select last_name as nombre , salary as platafrom employeeswhere (salary <= 5000 or salary >= 12000)  and (department_id in (20,50));

--7select last_name, hire_datefrom employeeswhere hire_date LIKE '%94';--8select last_name, job_idfrom employeeswhere manager_id is null;
---9select last_name, salary , commission_pctfrom employeeswhere commission_pct is not nullorder by salary desc , commission_pct desc;

--10select first_name, last_name, salary , commission_pctfrom employeeswhere last_name like '__a%';

-11select last_name, salary , commission_pctfrom employeeswhere last_name like '%a%' and last_name like '__e%';
-12select last_name, job_id, salaryfrom employeeswhere job_id IN ('ST_CLERK','SA_REP') and salary not in (2500, 3500, 7000) ;
-13select commission_pct, last_name as nombre , salary as platafrom employeeswhere commission_pct = 0.2;

desc employees;

---------------------------------

PRACTICA 3  PARTE 1--1

IMPRIME LA FECHA ACTUAL

 select sysdate as fechafrom dual ;

--2 seleccione los campoy y al salario increntelo con el 15% y redondee el salariy select employee_id, last_name, salary as salario,round (salary*0.15) as "new salary"from employees;

--3 ejecure el 2--4 agrege una columba con  el inbremento del salaro select employee_id, last_name, salary as salario,(salary +(salary*0.15)) as "nuevo salario ", (salary*0.15) as incremento  from employees;

--5muestre el aplellido la primera en mayuscual y las demas en minusculas y muestre su tamaño de caracteres de los  empleados q inician con A, J, Mselect INITCAP (last_name), length (last_name) as tamaño  from employees  where last_name like 'J%' or last_name like 'A%' or last_name like 'M%';

--6 muestra los empleados contradados desde la fecha de inicio hasta hoy em meses select last_name,  trunc (months_between  (sysdate , hire_date)) as "meses trabajados"from employees;

--7 concatene con || el apellido el salario y la frace pero le gustaria ganar y multiplique el salario por 3 select last_name || ' gana $'||salary || ' '||'pero le gustaria ganar'|| ' $' || (salary*3) as "salario soñado" from employees;

--8 Muestra el apellido, el salario formateado con * hasta completar 15 caracteres.select last_name, lpad (salary, 15, '*')from employees;
--9select last_name, hire_date,to_char ( next_day( add_months (hire_date, 6),'lunes'), 'fmday ",the" ddspth "of" month "," yyyy')from employees;
desc employees; 

--10 select last_name, hire_date, day hire_datefrom employees;
----------------------------
--practica 5

--1 verdadero

--2 falso

--3 verdadero

--4select max (salary) , min (salary),  sum (salary), round (avg (salary))from employees ;--5select job_id , max (salary) , min (salary),  sum (salary), round (avg (salary))from employees group by job_id ;--6select job_id, count (job_id)from employees group by job_id;

--7select count (manager_id)from employees;--8select max (salary) - min (salary) as diferenciafrom employees;

--9 select manager_id , min(salary)from employeeswhere manager_id is not nullgroup by manager_id;

--10--MONBRE DEMPO  LOCACION NOMBRE EMPLEADOselect count (e.employee_id) as empleado , d.department_name, d.location_id, avg (e.salary)from employees e join departments don e.department_id =  d.department_id group by d.department_name, d.location_id;

--11 ?SELECT COUNT(*) total,SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1995,1,0))"1995",SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1996",SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1997",SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1998"FROM employees;--12 ?SELECT job_id "Job",SUM(DECODE(department_id , 20, salary)) "Dept 20",SUM(DECODE(department_id , 50, salary)) "Dept 50",SUM(DECODE(department_id , 80, salary)) "Dept 80",SUM(DECODE(department_id , 90, salary)) "Dept 90",SUM(salary) "Total"FROM employeesGROUP BY job_id;
-----------------------------

select * from tab;select last_name ,nvl(to_char(commission_pct), 'sin comision') from employees;select d.department_name, concat ( e.first_name , e.last_name), rpad(' ', trunc(months_between(sysdate ,hire_date)/12)+1,'*')from employees e join departments don e.department_id = d.department_id;SELECT employee_id, last_name, salary, department_idFROM employeesWHERE employee_id = &employee_num ;SELECT last_name, department_id, salary*12FROM employeesWHERE job_id = '&job_title';

SELECT employee_id, last_name, job_id,&column_name, FROM employeesWHERE &conditionORDER BY &order_column ;

SELECT job_id "Job",SUM(DECODE(department_id , 20, salary)) "Dept 20",SUM(DECODE(department_id , 50, salary)) "Dept 50",SUM(DECODE(department_id , 80, salary)) "Dept 80",SUM(DECODE(department_id , 90, salary)) "Dept 90",SUM(salary) "Total"FROM employeesGROUP BY job_id;

--practica 8--1

crea un atabla llamada my_employee
create table MY_EMPLOYEE   (ID number(4) not null,  last_name varchar2(25),  first_name varchar2(25),  userid varchar2(8),  salary number(9,2));
  commit;--2 describala desc my_employee;

--3insert into my_employee (id,last_name, first_name, userid, salary) values (1, 'Patel', 'Rralph', 'rpatel', 895);insert into my_employee (id,last_name, first_name, userid, salary) values (2, 'Dancs', 'Betty', 'bdancs', 860);insert into my_employee (id,last_name, first_name, userid, salary) values (3, 'Biri', 'Ben', 'bbiri', 1100);insert into my_employee (id,last_name, first_name, userid, salary) values (4, 'Newman', 'Chad', 'cnewman', 750);insert into my_employee (id,last_name, first_name, userid, salary) values (5, 'Ropeburn', 'Audrey', 'aropebur', 1550);

--4 execute 

--5 commitcommit;

--6--crear un .sql--10update my_employeeset last_name = 'Drexler'where id = 3;

--11update my_employeeset salary  = 1000 where salary < 900;

--12

select * from my_employee;