• Author / Uploaded
• Francisco Hernan'

Citation preview

---

Ejercicios Sentencias SQL - 2012 Gestión Cliente-Orden Create Database GestionOrdenCliente Use GestionOrdenCliente Create Table Cliente( Rut int Primary Key, Nombre Varchar(20) not null ); Create Table Orden( Num_Orden int Primary Key, Monto int, Fecha varchar(10), Rut int Foreign Key References Cliente(Rut) );

Insert Into Cliente values ('11','Diaz Pedro'); Insert Into Cliente values ('22','Caro Ana'); Insert Into Cliente values ('33','Tapia Sofia'); Insert Into Orden values ('1','150','12_10_2012','11'); Insert Into Orden values ('3','175','12_10_2012','11'); Insert Into Orden values ('5','320','11_10_2012','22'); Insert Into Orden values ('7','110','12_10_2012','33'); Insert Into Orden values ('8','320','13_10_2012','11'); 

Mostrar Tabla Orden Select * From Orden;



Mostrar Tabla Cliente Select * From Cliente;



Mostrar listas de órdenes de compra del 12_10_2012 Select * From Orden Where Fecha= '12_10_2012';

Francisco Hernán Ramírez Fernández

Página 1

Ejercicios Sentencias SQL - 2012

Join

Mostrar listas de órdenes de compra del 12_10_2012 indicando el nombre del cliente Select C.Nombre, O.* From Orden O, Cliente C Where Fecha= '12_10_2012' and O.Rut=C.Rut;

SubConsultas



Select * From Cliente Where Rut =(Select Rut From Orden Where Fecha='12_10_2012'); 

Lista de los Rut con su nombre distintos en orden descendente Select Distinct Rut,Nombre From Cliente order by Rut desc;



Desplegar monto de todas las ordenes aumentado un 10% Select Monto*1.1 From Orden

Gestión Empleado Depto. Create database GestionEmpleadoDepto Use GestionEmpleadoDepto Create Table Depto( Num_Depto int Primary Key, Nombre Varchar(20) not null ); Create Table Empleado( Ci int Primary Key, Nombre varchar(20) not null, Dir varchar(5) not null, Sueldo int not null, Comision int, Num_Depto int Foreign Key References Depto(Num_Depto) );

Francisco Hernán Ramírez Fernández

Página 2

Ejercicios Sentencias SQL - 2012

Insert into Depto values('10','Ventas'); Insert into Depto values('20','Informatica'); Insert into Depto values('30','Marketing'); Insert into Empleado values('11','Diaz Pedro','D1','760','35','10'); Insert into Empleado values('33','Araneda Carolina','D3','120', null,'20'); Insert into Empleado values('55','Perez Magaly','D1','380','22','10'); Insert into Empleado values('77','Bravo Ana','D7','910',null,'30'); Insert into Empleado values('88','Soto Hector','D8','650','15','10'); 

SubConsultas

Join

Select E.Nombre From Empleado E, Empleado D where D.Nombre='Perez Magaly' and E.Num_Depto=D.Num_Depto;

Select Nombre From Empleado where Num_Depto in (Select Num_Depto From Empleado where Nombre='Perez Magaly');

Join



SubConsultas

Lista de todos los empleados que trabajen en el mismo departamento que Pérez Magaly

Listar todos los empleados que trabajan en el departamento de informática ordenados alfabéticamente Select E.Nombre From Empleado E, Depto D Where D.Nombre='Informatica' and E.Num_depto=D.Num_Depto Order by Nombre asc; Select Nombre From Empleado Where Num_Depto = (Select Num_Depto From Depto Where Nombre='Informatica'); Order by Nombre asc;

Francisco Hernán Ramírez Fernández

Página 3

Ejercicios Sentencias SQL - 2012



Obtener el total del dinero que se gastan en sueldos Select SUM(Sueldo) As Total_Sueldo From Empleado



Contabilizar los empleados del departamento 20 Select COUNT(Comision) From Empleado Where Comision>0;



Calcular el promedio de los sueldo de la empresa Select AVG(Sueldo) as Sueldo_Promedio From Empleado



Calcular el promedio de los sueldo de cada departamento de la empresa Select Num_Depto, AVG(Sueldo) as Sueldo_Promedio From Empleado group by Num_Depto



Contar a los empleados del departamento 10 Select COUNT(*)as Conteo From Empleado where Num_Depto=10;



Listar departamentos donde trabajen mas de 2 empleados Select Num_Depto, COUNT(ci) From Empleado Group by Num_Depto having COUNT(*)>2;



Desplegar el promedio de sueldo de todos aquellos departamentos que tienen mas de 2 empleados y ademan donde cada empleado gana mas de 500 Select AVG(Sueldo) as Sueldo, Num_Depto From Empleado Where Sueldo>500 group by Num_Depto Having COUNT(*)>=2;

Francisco Hernán Ramírez Fernández

Página 4

Ejercicios Sentencias SQL - 2012

Join

Listar empleados que solo trabajen en el departamento de Soto Héctor Select E.* From Empleado E, Empleado D Where E.Num_Depto=D.Num_Depto and D.Nombre='Soto Hector'

SubConsultas



Select * From Empleado where Num_Depto=(Select Num_Depto From Empleado Where Nombre='Soto Hector');

Join

Listar Nombre y sueldo de los empleados que trabajen en el departamento de ventas y ordenar ascendentemente por sueldos Select E.Nombre, E.Sueldo From Empleado E, Depto D Where D.Nombre='Ventas' and D.Num_Depto=E.Num_Depto Order by Sueldo;

SubConsultas



Select Nombre, Sueldo From Empleado where Num_Depto=(Select Num_Depto From Depto Where Nombre='Ventas') Order by Sueldo;

SubConsultas



Select * From Empleado Where Sueldo>(Select AVG(Sueldo) From Empleado); 

SubConsultas

Listar de empleados que ganan mas del promedio de sueldo de la empresa

Listar Empleados que ganan mas que el promedio de sueldo de los empleados del departamento de ventas Select * From Empleado Where Sueldo>(Select AVG(Sueldo) From Empleado Where Num_Depto=(Select Num_Depto From Depto Where Nombre='Ventas'));

Francisco Hernán Ramírez Fernández

Página 5

Ejercicios Sentencias SQL - 2012 

Lista de los empleados que tienen sueldo mayor que algún empleado del departamento 10

SubConsultas

Select * From Empleado Where Sueldo>(Select MIN(Sueldo) From Empleado Where Num_Depto='10'); Select * From Empleado Where Sueldo>Any(Select Sueldo From Empleado Where Num_Depto='10'); 

Lista de los empleados que tienen el Sueldo mayor que todos los empleados del departamento 10

SubConsultas

Select * From Empleado Where Sueldo>(Select Max(Sueldo) From Empleado Where Num_Depto='10'); Select * From Empleado Where Sueldo>All(Select Sueldo From Empleado Where Num_Depto='10'); 

Listar los nombres de todos los departamentos donde hay empleados que ganan mas de 300 y menos de 1000

SubConsultas

Select Nombre From Depto Where Num_Depto in (Select Num_Depto From Empleado Where Sueldo between 300 and 1000);



Insertamos datos a la tabla “depto” (Deben ser lo mismos de abajo). --Cuando conocemos el orden de los parametros Insert Into Depto Values ('40','Calidad') --Cuando no conocemos el orden de los parametros Insert Into Depto (Nombre,Num_Depto) Values ('Contabilidad','50')

Francisco Hernán Ramírez Fernández

Página 6

Ejercicios Sentencias SQL - 2012

SubConsultas Corr.



Lista de los empleados que tienen un sueldo mayor que el promedio de su departamento Select E.* From Empleado E Where Sueldo>(Select AVG(Sueldo) From Empleado D Where E.Num_Depto=D.Num_Depto);

SubConsultas Corr.



Encontrar departamentos que no tienen empleados Select * From Depto Where not exists (Select * From Empleado Where Empleado.Num_Depto=Depto.Num_Depto);



Listar todos los empleados que son vendedores Select * From Empleado Where Comision is not null;

Join

Select E.* From Empleado E, Depto D Where E.Num_Depto=D.Num_Depto AND D.Nombre='Ventas';

SubConsultas

Select * From Empleado Where Num_Depto= (Select Num_Depto From Depto Where Nombre='Ventas'); 

Listar sueldo mas comisiones de todos los empleados del depto. Ventas

Join

--Usando 1 de los 3 ejemplos anteriores Select E.Sueldo+E.Comision As SueldoTotal, E.Nombre From Empleado E, Depto D Where E.Num_Depto=D.Num_Depto AND D.Nombre='Ventas'; --NOTA: 120+null=null 

Función NVL --Si Comision es nula entonces es un 0 temporal Where Nvl(Comision,0)100

Francisco Hernán Ramírez Fernández

Página 7

Ejercicios Sentencias SQL - 2012 Sentencias DML    

Select Delete Insert Update

1. Select Select From [Where Group By Having Order By ] 2. Delete Delete [From] [Where ] 

Borrar todas las filas de empleado Delete Empleado

SubConsultas



Borrar todos los empleados del depto. 10 Delete Empleado Where Num_Depto = 10 Delete Empleado Where Num_Depto in (Select Num_Depto From Depto Where Nombre='Ventas');

Francisco Hernán Ramírez Fernández

Página 8

Ejercicios Sentencias SQL - 2012 3. Insert Insert into [Col1,Col2…] values(’’, ’’, ’’); 

Agregar Depto. 50 correspondiente a Contabilidad Insert Into Depto Values ('50','Contabilidad');

4. Update Update Set

Where ; 

Modificar el sueldo de todos los empleados, aumentando en un 50% Update Empleado Set Sueldo=Sueldo*1.5

Sentencias DDL 1. Create Create Table

( Lista{} ); //Se borran parentesis se refiere a repeticion 

Crear tabla Departamento con 2 atributos Create Table Departamento( Numero int,--Numerico 2 caracteres o byte number oracle Nombre varchar (20) );



Crear tabla 1 a partir de 2 tomando C1,C2yC3 Create Table A1 as Select C1,C2,C3 From A2;

Francisco Hernán Ramírez Fernández

Página 9

Ejercicios Sentencias SQL - 2012 2. ALTER: Solo se puede ampliar datos columnas y no achicar ALTER TABLE {ADD |MODIFY }; 

Agregar y modificar un atributo a la tabla Departamento ALTER TABLE Departamento ADD Ubicacion varchar(20); ALTER TABLE Departamento MODIFY Nombre Varchar(40));

3. DROP: Eliminar tablas DROP TABLE ; 

Eliminar Tabla Departamento DROP TABLE Departamento;

4. VIEW: Vistas, se queda guardado en el diccionario de base de datos, permite usarse siempre no como la consulta que es temporal ¿Que se puede hacer con vistas?. Se puede crear vistas de varias tablas y vistas con funciones de agregacion ¿Que no debe haber?. No se debe poner Order By dentro del create porque no hay datos para ordenar CREATE VIEW [(Col1,Col2,…)] AS {Consulta SELECT} ; 

Necesitamos que ciertos usuarios accedan a la tabla usuario y solo puede consultar el codigo y el nombre del usuario CREATE VIEW Dataemp AS SELECT Ci,Nombre From Empleado; --Mostrar Vista Select * From Dataemp;



Contabilizar todos los empleados de la empresa Select COUNT(*) AS N_empleado From Dataemp;

Francisco Hernán Ramírez Fernández

Página 10

Ejercicios Sentencias SQL - 2012 

Para borrar la vista Drop View Dataemp;



Crear vista de la tabla empleado que contenga nombre sueldo y num_depto CREATE VIEW Clon AS SELECT Nombre,Sueldo,Num_Depto FROM Empleado; SELECT * FROM Clon WHERE Num_depto=10;



Desplegar o listar para cada departamento el promedio de sueldo por departamento SELECT Num_Depto, AVG(Sueldo)AS Prom_Sueldo FROM Clon GROUP BY Num_Depto;



Desplegar la cantidad de empleados por cada departamento indicando el nombre del detpo y enseguida la cantidad de ellos CREATE VIEW Cuenta(Codigo,Cantidad) AS SELECT Num_Depto, COUNT(*) FROM Empleado GROUP BY Num_Depto;

View + Join

SELECT * FROM Cuenta; SELECT Nombre, Cantidad FROM Cuenta, Depto WHERE Codigo=Num_Depto Order By Nombre;

Francisco Hernán Ramírez Fernández

Página 11

Ejercicios Sentencias SQL - 2012

Restricciones de integridad Cada vez que se ingresan datos, cada vez que haya problemas con restriccion de integridad la base de datos tira mensaje con problema Son utiles al momento de ingreso de codigo

Importante El SGBD guarda las restricciones en el DD(diccionario de datos) y asegura que se cumplan

Acciones usuario INSERT, DELETE, UPDATE

DBMS

Datos Usuarios

DD

Tipos de Restricciones de integridad 

Datos Requeridos: Se prohíbe valores nulos en una columna



Dominio: Solo se permite valores que permanezcan a un dominio de cada columna



Unidad: Exigir valores únicos en columnas que con llave primaria



Integridad referencial: Todo valore de una llave foránea debe ser igual a algún valor de la llave primaria

Francisco Hernán Ramírez Fernández

Página 12

Ejercicios Sentencias SQL - 2012 Declaración de restricciones de integridad CREATE TABLE Departamento ( Numero INT not null PRIMARY KEY, Nombre VARCHAR(20) not null, Ubicacion VARCHAR(15) );

--Dato Requerido Unicidad --Dato Requerido

CREATE TABLE Empleado( Numero INT PRIMARY KEY, --Unicidad Nombre VARCHAR(30) not null, --Dato Requerido Jefe INT REFERENCES Empleado(Numero), --Integridad Referencial Grado INT CHECK(Grado in(1,2,3,4)), --Dominio Sueldo INT, Num_Depto INT not null FOREIGN KEY (Num_Depto) --Integridad Referencial Dato Requerido REFERENCES Departamento (Numero) ON DELETE CASCADE ); INSERT INTO Departamento VALUES ('10','Informatica','D2'); INSERT INTO Departamento VALUES ('20','Ventas','D5'); INSERT INTO Departamento VALUES ('30','Marketinf','D1'); INSERT INTO Empleado VALUES ('11','Diaz',null,'1','700','20'); INSERT INTO Empleado VALUES ('22','Zapata','55','2','600','10'); INSERT INTO Empleado VALUES ('33','Soto','11','3','180','20'); INSERT INTO Empleado VALUES ('55','Rojas',null,1,'900','10'); INSERT INTO Empleado VALUES ('77','Perez','11','2','500','20'); INSERT INTO Empleado VALUES ('88','Caro','55','3','400','10'); Select * From Departamento Select * From Empleado 

Desplegar el nombre y el sueldo de todos los empleados que ganan mas de 400 y menos de 800, listar ordenado por sueldo de mayor a menor (between acepta limites) Select Nombre, Sueldo From Empleado where Sueldo between 400 and 800 order by Sueldo desc;

Francisco Hernán Ramírez Fernández

Página 13

Ejercicios Sentencias SQL - 2012

Join

Desplegar todos los empleados que trabajan en el depto de informatica ordenados alfabeticamente Select E.* From Empleado E, Departamento D where E.Num_Depto=D.Numero and D.Nombre='Informatica' order by E.Nombre asc;

SubConsultas



Select * From Empleado where Num_Depto=(Select Numero From Departamento Where Nombre='Informatica' ) order by Nombre; 

Join

Select E.* From Empleado E, Empleado D Where D.Nombre='Zapata' and E.Num_Depto=D.Num_Depto;

SubConsultas

Select * From Empleado where Num_Depto in (Select Num_Depto From Empleado where Nombre='Zapata');

Join



SubConsultas

Desplegar a todos los empleado que trabajan en el mismo departamento que Alvarez

Desplegar todos los empelados cuyo jefe es Rojas Select O.* From Empleado R, Empleado O Where R.Numero = O.Jefe and R.Nombre='Rojas' Select * From Empleado where Jefe in ( Select Numero From Empleado where Nombre ='Rojas');

Francisco Hernán Ramírez Fernández

Página 14

Ejercicios Sentencias SQL - 2012

Join

Listar todos los atributos del jefe de Pérez Select O.* From Empleado P, Empleado O Where P.Jefe = O.Numero and P.Nombre='Perez'

SubConsultas



Select * From Empleado Where Numero in (Select Jefe From Empleado Where Nombre ='Perez');



Crear vista que contenga Nombre, sueldo y num_depto Create View Clase As Select Nombre, Sueldo, Num_Depto From Empleado



Contabilizar todos los empleados de la empresa Select* From Clase Select COUNT (*) From Clase



Contar por cada departamento la cantidad de empleados que existen Select COUNT(*) , Num_Depto From Empleado Group by Num_Depto

Francisco Hernán Ramírez Fernández

Página 15