Ejercicios SQL - Definición de Datos - Modelo Entidad Relación

Modelo Entidad Relación: Qué es? - Para que sirve? Herramientas para elaborarlo y un ejemplo. Favor incluir en el blog - Trabajo Independiente - 30 %

Script del ejercicio realizado en clase virtual:

CREATE TABLE pub (

    cod_pub         VARCHAR(5)   NOT NULL,

    nombre          VARCHAR(60)  NOT NULL,

    licencia_fiscal VARCHAR(60)  NOT NULL,

    domicilio       VARCHAR(60)         ,

    fecha_apertura  DATE         NOT NULL,

    horario         VARCHAR(60)  NOT NULL,

    cod_localidad   INTEGER      NOT NULL ) ;

CREATE TABLE titular (

    dni_titular     VARCHAR(8)   NOT NULL,

    nombre          VARCHAR(60)  NOT NULL,

    domicilio       VARCHAR(60)         ,

    cod_pub         VARCHAR(5)   NOT NULL ) ;

CREATE TABLE empleado (

    dni_empleado    VARCHAR(8)   NOT NULL,

    nombre          VARCHAR(60)  NOT NULL,

    domicilio       VARCHAR(60)          ) ;

CREATE TABLE existencias (

    cod_articulo    VARCHAR(10)  NOT NULL,

    nombre          VARCHAR(60)  NOT NULL,

    cantidad        INTEGER      NOT NULL,

    precio          DECIMAL      NOT NULL,

    cod_pub         VARCHAR(5)   NOT NULL ) ;

CREATE TABLE localidad (

    cod_localidad   INTEGER      NOT NULL,

    nombre          VARCHAR(60)  NOT NULL ) ;

CREATE TABLE pub_empleado (

    cod_pub         VARCHAR(5)   NOT NULL,

    dni_empleado    VARCHAR(8)   NOT NULL,

    funcion         VARCHAR(9)   NOT NULL ) ;

ALTER TABLE pub ADD CONSTRAINT pk_pub

 PRIMARY KEY (cod_pub) ;

ALTER TABLE localidad ADD CONSTRAINT pk_localidad

 PRIMARY KEY (cod_localidad) ;

ALTER TABLE titular ADD CONSTRAINT pk_titular

 PRIMARY KEY (dni_titular) ;

ALTER TABLE empleado ADD CONSTRAINT pk_empleado

 PRIMARY KEY (dni_empleado) ;

ALTER TABLE existencias ADD CONSTRAINT pk_existencias

 PRIMARY KEY (cod_articulo) ;

ALTER TABLE pub_empleado ADD CONSTRAINT pk_pub_empleado

 PRIMARY KEY (cod_pub, dni_empleado, funcion) ;

ALTER TABLE pub ADD CONSTRAINT fk_pub_localidad

 FOREIGN KEY (cod_localidad)

 REFERENCES localidad (cod_localidad) ;

ALTER TABLE titular ADD CONSTRAINT fk_titular_pu

 FOREIGN KEY (cod_pub)

 REFERENCES pub (cod_pub) ;

ALTER TABLE existencias ADD CONSTRAINT fk_existencias_pub

 FOREIGN KEY (cod_pub)

 REFERENCES pub (cod_pub) ;

ALTER TABLE pub_empleado ADD CONSTRAINT fk_pubemple_pub

 FOREIGN KEY (cod_pub)

 REFERENCES pub(cod_pub) ;

ALTER TABLE pub_empleado ADD CONSTRAINT fk_pubemple_empleado

 FOREIGN KEY (dni_empleado)

 REFERENCES empleado (dni_empleado) ;

ALTER TABLE pub ADD CONSTRAINT ck_horario

 CHECK (horario IN ('HOR', 'HOR2', 'HOR3')) ;

ALTER TABLE existencias ADD CONSTRAINT ck_precio

 CHECK (precio <> 0) ;

ALTER TABLE pub_empleado ADD CONSTRAINT ck_funcion

 CHECK (funcion IN ('CAMARERO', 'SEGURIDAD', 'LIMPIEZA')) ; 

select *from pub;

insert into pub values (11111,'La tienda de Leo','LF-01','Calle 46 # 1occ-70 Campohermoso','2010-12-12','HOR',11);

select *from pub;

insert into localidad values (11,'Cabecera');

select *from localidad;

Solución Segundo Parcial

Teniendo en cuenta el Script suministrado de la Base de Datos “pedidos”, favor resolver las siguientes preguntas:

1)    Actualizar el precio unitario de los productos de la categoría CARNICOS, subiéndolos en un 10%. (Valor 0,5)

UPDATE PRODUCTOS SET PRECIOUNIT=PRODUCTOS.PRECIOUNIT*1.1

FROM CATEGORIAS C INNER JOIN PRODUCTOS P

ON (C.CATEGORIAID=P.CATEGORIAID)

WHERE NOMBRECAT='CARNICOS';

2)    Actualizar el teléfono celular del proveedor cuyo contacto es MANUEL ANDRADE, con el valor 099010291. (Valor 0,5)

UPDATE PROVEEDORES SET CELUPROV='099010292'

WHERE CONTACTO='MANUEL ANDRADE';

3)    Borrar el producto YOGURT DE SABORES. (Valor 0,5)

DELETE FROM PRODUCTOS WHERE DESCRIPCION='YOGURT DE SABORES';

4)    Realizar una consulta que muestre: el id de la orden, el apellido y nombre del empleado que la atendió, el nombre de la compañía cliente y la fecha de orden. (Valor 0,5)

SELECT ORDENID, APELLIDO|| ' ' ||NOMBRE AS EMPLEADO,NOMBRECIA, FECHAORDEN

FROM EMPLEADOS E INNER JOIN ORDENES O

ON(E.EMPLEADOID=O.EMPLEADOID)

INNER JOIN CLIENTES C

ON(O.CLIENTEID=C.CLIENTEID)

5)    Realizar una consulta que muestre: la suma total de cada tipo de producto (categorías) pedidos en todas las órdenes. (Valor 1,0)

SELECT SUM(CANTIDAD) AS CANTIDAD,NOMBRECAT

FROM DETALLE_ORDENES D INNER JOIN PRODUCTOS P

ON(D.PRODUCTOID=P.PRODUCTOID)

INNER JOIN CATEGORIAS C

ON(C.CATEGORIAID=P.CATEGORIAID)

GROUP BY NOMBRECAT

6)    Realizar una consulta que muestre: el número de órdenes atendidas por cada empleado, incluidos los que tienen 0 órdenes. (Valor 1,0)

SELECT COUNT(ORDENID) AS NUMERO_DE_ORDENES,APELLIDO|| ' ' ||NOMBRE AS EMPLEADO

FROM ORDENES O RIGHT JOIN EMPLEADOS E

ON(O.EMPLEADOID=E.EMPLEADOID)

GROUP BY (APELLIDO|| ' ' ||NOMBRE)

ORDER BY NUMERO_DE_ORDENES,EMPLEADO

7)    Realizar una consulta que muestre: los proveedores y la suma de dinero vendido en los productos (venta total) de ese proveedor. (Valor 1,0)

SELECT NOMBREPROV AS PROVEEDOR, SUM(PRECIOUNIT*CANTIDAD) AS VENTA_TOTAL

FROM PROVEEDORES P JOIN PRODUCTOS PR

ON(P.PROVEEDORID=PR.PROVEEDORID)

JOIN DETALLE_ORDENES D

ON(D.PRODUCTOID=PR.PRODUCTOID)

GROUP BY NOMBREPROV

Vistas - Ejercicios de Repaso Parcial

Vistas

create table secciones(

  codigo serial,

  nombre varchar(20),

  sueldo decimal(5,2),

  primary key (codigo)

 );

 

 create table empleados(

  codigo_emp serial,

  documento char(8),

  sexo char(1),

  apellido varchar(20),

  nombre varchar(20),

  domicilio varchar(30),

  seccion smallint not null,

  cantidadhijos smallint,

  estadocivil char(10),

  fechaingreso date,

   primary key (codigo_emp)

 );

 

 insert into secciones(nombre,sueldo) values('Administracion',300);

 insert into secciones(nombre,sueldo) values('Contaduría',400);

 insert into secciones(nombre,sueldo) values('Sistemas',500);

 select *from secciones;

 

 insert into empleados

(documento,sexo,apellido,nombre,domicilio,seccion,cantidadhijos,estadocivil,fechaingreso)

 values('22222222','f','Lopez','Ana','Colon 123',1,2,'casado','1990-10-10');

 insert into empleados

(documento,sexo,apellido,nombre,domicilio,seccion,cantidadhijos,estadocivil,fechaingreso)

 values('23333333','m','Lopez','Luis','Sucre 235',1,0,'soltero','1990-02-10');

 insert into empleados

(documento,sexo,apellido,nombre,domicilio,seccion,cantidadhijos,estadocivil,fechaingreso) 

values('24444444','m','Garcia','Marcos','Sarmiento 1234',2,3,'divorciado','1998-07-12');

 insert into empleados

(documento,sexo,apellido,nombre,domicilio,seccion,cantidadhijos,estadocivil,fechaingreso) 

values('25555555','m','Gomez','Pablo','Bulnes 321',3,2,'casado','1998-10-09');

 insert into empleados

(documento,sexo,apellido,nombre,domicilio,seccion,cantidadhijos,estadocivil,fechaingreso) 

values('26666666','f','Perez','Laura','Peru 1254',3,3,'casado','2000-05-09');

 select *from empleados;

 

  

 create view vista_empleados as

  select (apellido||' '||e.nombre) as nombre,sexo,

   s.nombre as seccion, cantidadhijos

   from empleados as e

   join secciones as s

   on codigo=seccion;

 

 select *from vista_empleados;

 

 select seccion,count(*) as cantidad

  from vista_empleados

  group by seccion;

 

 create view vista_empleados_ingreso (fecha,cantidad)

  as

  select extract(year from fechaingreso),count(*)

   from empleados

   group by extract(year from fechaingreso);

 

 select *from vista_empleados_ingreso;

 

Consultas Anidadas

SELECT avg(prod_existencias) AS minimo
FROM productos
WHERE prod_id between 1 and 5;

select *from productos;

SELECT prod_nombre, prod_existencias
FROM productos
WHERE prod_precio <some (
SELECT AVG(prod_precio)
from productos);

SELECT cli_nombre, cli_ciudad
FROM clientes
WHERE cli_cedula > some
(SELECT cli_cedula
FROM clientes
WHERE cli_ciudad = 'LEBRIJA')
AND cli_nombre LIKE 'A%';

SELECT distinct ped_emp_cedula
FROM pedidos
WHERE ped_emp_cedula IN (SELECT emp_cedula
FROM empleados
WHERE emp_fecha_nac > '19/06/1990');

select * from empleados;

select * from pedidos;

Creación y manejo de Vistas (View)

Vistas en PostgreSQL