Guia sql

cmd

sqlplus/nolog

sqlplus> conn / as sysdba

sqlplus> alter user hr identified by hr;

sqlplus>alter user hr account unlock;

SELECT employee_id, job_id,salary FROM employees WHERE job_id= (SELECT job_id FROM employees WHERE employee_id = 141)

AND salary>(SELECT salary FROM employees WHERE employee_id= 143);

SELECT employee_id, last_name, salary FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);

SELECT employee_id,job_id,salary FROM employees WHERE salary < all (SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id

<> 'IT_PROG' ORDER BY employee_id;

guia 2

Select max(salary)"salario maximo" ,min(salary)"salario minimo",sum(salary)"sumatoria de los salarios",

round(avg(salary))"salario promedio" from employees;

Select job_id ,max(salary) ,min(salary) ,sum(salary) ,round(avg(salary)) from employees group by job_id order by job_id;

select job_id"trabajo" , count(employee_id)"total empleado" from employees group by job_id;

select manager_id , count(employee_id)"total empleados" from employees group by manager_id order by 2 desc,1;

select department_id "departamento",count(employee_id)"total empleados",to_char(sum(salary),'$999g999')"salario total por dpt" from employees where department_id is not null group by department_id order by department_id;

select to_char(( max(salary)-min(salary)),'$99,999')"diferencia" from employees;

select count(distinct nvl( manager_id,2))"total jefes" from employees;

select department_id "departamento", count( employee_id) from employees group by department_id having count(employee_id)>5;

select department_id,job_id,min(salary)"salario minimo" from employees where department_id is not null group by department_id,job_id having min(salary)>6000 order by 1,2;

create OR REPLACE view v_datos_emp as select (numrut || '-' || dvrut) "RUT_EMPLEADO",(pnombre || ' ' || appaterno) " NOMBRE_EMPLEADO"

from empleado;

create OR REPLACE view ventas_mayor_promedio as select nro_boleta,monto_total from ventas where monto_total>(select avg(monto_total) from ventas);

create OR REPLACE view emp_trabajo as select (pnombre || ' ' || appaterno ) " NOMBRE_EMPLEADO", tip.DESC_TIPO_EMPLEADO "trabajo" from empleado emp join TIPO_EMPLEADO tip on(emp.tipo_empleado = tip.tipo_empleado) order by appaterno;

create OR REPLACE view v_ventas_emp as select (pnombre || ' ' || appaterno ) " NOMBRE_EMPLEADO"

,ven.nro_boleta"numero boleta",to_char(ven.fecha_boleta,'dd/mm/yyyy') ,

to_char(ven.monto_total,'999g999')"monto venta" from empleado emp join VENTAS ven on(emp.id_empleado= ven.id_empleado) order by ven.nro_boleta ;

Declare

v_fname VARCHAR(20);

BEGIN

SELECT first_name

INTO v_fname

FROM employees

WHERE employee_id= 100;

END;

Declare

v_fname VARCHAR(20);

BEGIN

SELECT first_name

INTO v_fname

FROM employees

WHERE employee_id= 100;

...