Subconsultas - Ejercicios realizados en Clase

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

Evaluación Tercer Corte: Junio 10 de 2020

______________________________________________________

Script del taller realizado en clase

drop table if exists usuario;

 drop table if exists autor;

 

create table editoriales(

  codigo serial,

  nombre varchar(30),

  primary key (codigo)

 );

 

 create table libros (

  codigo serial,

  titulo varchar(40),

  autor varchar(30),

  codigoeditorial smallint,

  primary key(codigo)

 );

 

 insert into editoriales(nombre) values('Planeta');

 insert into editoriales(nombre) values('Emece');

 insert into editoriales(nombre) values('Paidos');

 insert into editoriales(nombre) values('Siglo XXI');

 insert into libros(titulo,autor,codigoeditorial) values('Uno','Richard Bach',1);

 insert into libros(titulo,autor,codigoeditorial) values('Ilusiones','Richard Bach',1);

 insert into libros(titulo,autor,codigoeditorial) values('Aprenda PHP','Mario Molina',4);

 insert into libros(titulo,autor,codigoeditorial) values('El aleph','Borges',2);

 insert into libros(titulo,autor,codigoeditorial) values('Puente al infinito','Richard Bach',2);

 

 select *from editoriales;

 

 select *from libros;

 -- Queremos conocer el nombre de las editoriales que han publicado libros del autor "Richard Bach":

 select nombre

  from editoriales

  where codigo in

   (select codigoeditorial

     from libros

     where autor='Richard Bach');

 

-- Probamos la subconsulta separada de la consulta exterior para verificar que retorna una lista

 -- de valores de un solo campo:

 select distinct codigoeditorial

  from libros

  where autor='Richard Bach';

  

  -- Podemos reemplazar por un "join" la primera consulta:

 select distinct nombre

  from editoriales as e

  join libros

  on codigoeditorial=e.codigo

  where autor='Richard Bach';

  

  -- También podemos buscar las editoriales que no han publicado libros de "Richard Bach":

 select nombre

  from editoriales

  where codigo not in

   (select codigoeditorial

     from libros

     where autor='Richard Bach');

_________________________________

drop table if exists libros;

 

 create table libros(

  codigo serial,

  titulo varchar(40),

  autor varchar(30),

  editorial varchar(20),

  precio decimal(5,2),

  primary key(codigo)

 );

 insert into libros(titulo,autor,editorial,precio) 

  values('Alicia en el pais de las maravillas','Lewis Carroll','Emece',20.00);

 insert into libros(titulo,autor,editorial,precio)

  values('Alicia en el pais de las maravillas','Lewis Carroll','Plaza',35.00);

 insert into libros(titulo,autor,editorial,precio)

  values('Aprenda PHP','Mario Molina','Siglo XXI',40.00);

 insert into libros(titulo,autor,editorial,precio)

  values('El aleph','Borges','Emece',10.00);

 insert into libros(titulo,autor,editorial,precio)

  values('Ilusiones','Richard Bach','Planeta',15.00);

 insert into libros(titulo,autor,editorial,precio)

  values('Java en 10 minutos','Mario Molina','Siglo XXI',50.00);

 insert into libros(titulo,autor,editorial,precio)

  values('Martin Fierro','Jose Hernandez','Planeta',20.00);

 insert into libros(titulo,autor,editorial,precio)

  values('Martin Fierro','Jose Hernandez','Emece',30.00);

 insert into libros(titulo,autor,editorial,precio)

  values('Uno','Richard Bach','Planeta',10.00);

  

select *from libros;

 -- Obtenemos el título, precio de un libro específico y 

 -- la diferencia entre su precio y el máximo valor:

 select titulo,precio,

  precio-(select max(precio) from libros) as diferencia

  from libros

  where titulo='Uno';

 -- Mostramos el título y precio del libro más costoso:

 select titulo,autor, precio

  from libros

  where precio=

   (select min(precio) from libros);

 -- Actualizamos el precio del libro con máximo valor:

 update libros set precio=45

  where precio=

   (select max(precio) from libros);

  -- Eliminamos los libros con precio menor:

 delete from libros

  where precio=

   (select min(precio) from libros);

______________________________________

drop table if exists clientes;

 drop table if exists facturas;

 drop table if exists detalles; 

 

 create table clientes(

  codigo serial,

  nombre varchar(30),

  domicilio varchar(30),

  primary key(codigo)

 );

 create table facturas(

  numero int not null,

  fecha date,

  codigocliente int not null,

  primary key(numero)

 );

 create table detalles(

  numerofactura int not null,

  numeroitem int not null, 

  articulo varchar(30),

  precio decimal(5,2),

  cantidad int,

  primary key(numerofactura,numeroitem)

 );

 insert into clientes(nombre,domicilio) values('Juan Lopez','Colon 123');

 insert into clientes(nombre,domicilio) values('Luis Torres','Sucre 987');

 insert into clientes(nombre,domicilio) values('Ana Garcia','Sarmiento 576');

 

 select * from detalles;

 insert into facturas values(1200,'2017-01-15',1);

 insert into facturas values(1201,'2017-01-15',2);

 insert into facturas values(1202,'2017-01-15',3);

 insert into facturas values(1300,'2017-01-20',1);

 insert into detalles values(1200,1,'lapiz',1,100);

 insert into detalles values(1200,2,'goma',0.5,150);

 insert into detalles values(1201,1,'regla',1.5,80);

 insert into detalles values(1201,2,'goma',0.5,200);

 insert into detalles values(1201,3,'cuaderno',4,90);

 insert into detalles values(1202,1,'lapiz',1,200);

 insert into detalles values(1202,2,'escuadra',2,100);

 insert into detalles values(1300,1,'lapiz',1,300);

 

 

 select f.*,

  (select sum(d.precio*cantidad)

    from detalles as d

    where f.numero=d.numerofactura) as total

 from facturas as f;

 

 

select td.numero,c.nombre,td.total

  from clientes as c

  join (select f.*,

   (select sum(d.precio*cantidad)

    from detalles as d

    where f.numero=d.numerofactura) as total

  from facturas as f) as td

  on td.codigocliente=c.codigo;

Modelo Entidad Relación y Modelo Relacional

Ejercicios

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 taller realizado en clase

create table libros(

cod_libro int not null,

nombre varchar (30) not null,

genero varchar (20) not null,

ISBN numeric (10) not null,

anho int not null,

idioma varchar not null,

cod_autor int not null,

cod_editorial int not null,

CONSTRAINT pk_cod_libro PRIMARY KEY (cod_libro));

 

insert into libros values (10, 'Lo que el viento se llevó','Drama',4321,1990, 'Portugués',20,30);

insert into libros values (11, 'Penélope','Ficción',9999,2001, 'Español',21,33);

insert into libros values (12, 'Bases de Datos Relacionales','Académico',8888,1980, 'Español',20,30);

insert into libros values (13, 'Top Story','Animados',5555,2010, 'Inglés',22,33);

insert into libros values (14, 'Canibal','Terror',2222,2016, 'Español',22,32);

select * from editorial;

create table usuario(

cod_usuario int NOT null,

RUT numeric(20)null,

nombre varchar(30)null,

CONSTRAINT pk_cod_usuario PRIMARY KEY (cod_usuario)

);

insert into usuario values (1121,578,'Daniel Castellanos');

insert into usuario values (2351,321,'Andres Palacios');

insert into usuario values (4470,890,'Ferney Ballesteros');

create table autor(

cod_autor int null,

nom_autor varchar(30) null,

CONSTRAINT pk_cod_autor PRIMARY KEY (cod_autor)

);

insert into autor values (20,'Mario Puzo');

insert into autor values (21,'Isabella Portilla');

insert into autor values (22,'Mario Mendoza');

create table editorial(

cod_editorial int NOT null,

nom_editorial varchar(30) null,

ciudad_editorial varchar(30) null,

nom_contacto varchar(50),

anho int NOT null,

CONSTRAINT pk_cod_editorial PRIMARY KEY (cod_editorial)

);

insert into editorial values (30,'Androide','Silicon Valley','Ricardo Ernesto',1991);

insert into editorial values (31,'El Mundo','Madrid','Daniel Amado',2000);

insert into editorial values (32,'Planeta','Bogota','David Gonzalez',1999);

insert into editorial values (33,'Biblioteca mundial','Lima','Camila Trespalacios',1988);

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