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

Base de Datos - Modelo Entidad Relación

Solución Primer Parcial

Fundamentación Teórica. 

Enuncie dos políticas de seguridad para un DBA:

Crear las bases de datos con el usuario postgres.

NO dar permiso de creación de base de datos a los usuarios.

NO dar permiso de creación de usuarios a los usuarios.

LIMITAR el número de conexiones a las BD.

CONCEDER solo privilegios de lectura o escritura sobre ciertas tablas. 

Plantee la Sentencia SQL, que remueva todos los permisos para todos los usuarios de la base de datos “Inventarios”

REVOKE ALL ON DATABASE Inventarios FROM public;

Plantee la Sentencia SQL, para alterar un usuario llamado “atletico” con password “bucaros” y que tenga permisos de LOGIN y además no le permita crear bases de Datos.

ALTER user atletico LOGIN PASSWORD ‘bucaros’ NOCREATEDB;

Plantee las Sentencia SQL, para eliminar el usuario “nanaya” y otra para observar todos los usuarios del sistema.

DROP USER nanaya; -> Para eliminar un usuario.

SELECT * FROM pg_user; -> Para observar todos los usuarios del sistema.

Enuncie los pasos a seguir desde el PG Admin, para no ingresar con el usuario postgres sino con un nuevo usuario llamado “Santiago” y nos conectaremos a la base de datos “Inventarios”.

Clic derecho sobre el nombre del servidor -> propiedades, aparecerá una ventana emergente con la configuración de la conexión, cambie la propiedad username por Santiago y la propiedad Maintenance DB por Inventarios

SQL COMANDOS DDL Y DML

Fundamentación Práctica

Teniendo en cuenta las figuras CONSOLA, JUEGO y CONSOLA_JUEGO, utilizar el lenguaje SQL del Sistema Manejador de Bases de Datos postgresql para:

Crear la base de datos (relacionado con su contexto)

CREATE DATABASE consolas

Crear las tablas (con sus respectivas llaves primarias y foráneas, si es el caso)

CREATE TABLE consola (

id_consola int,

nombre varchar (50),

tipo varchar (15),

marca varchar (50),

PRIMARY KEY (id_consola)

);

CREATE TABLE juego(

id_juego int,

titulo varchar(50),

genero varchar(50),

PRIMARY KEY (id_juego)

);

CREATE TABLE consola_juego(

id_consola int,

id_juego int,

stock int, 

PRIMARY KEY (id_consola, id_juego),

FOREIGN KEY (id_consola) REFERENCES consola(id_consola),

FOREIGN KEY (id_juego) REFERENCES juego(id_juego)

);

Insertar los registros (que aparecen en las figuras)

INSERT INTO consola  VALUES(1,'PS4','Sobremesa','Sony');

INSERT INTO consola(id_consola, nombre, tipo, marca) VALUES(2,'XBOX ONE','Sobremesa','Microsoft');

INSERT INTO consola(id_consola, nombre, tipo, marca) VALUES(3,'3DS','Portatil','Nintendo');

SELECT * FROM consola;

INSERT INTO juego(id_juego, titulo, genero) VALUES(1,'Dark Souls III', 'ROL');

INSERT INTO juego (id_juego, titulo, genero) VALUES(2,'Project Cars', 'Conduccion');

INSERT INTO juego(id_juego, titulo, genero) VALUES(3,'Metal Gear Solid V', 'Accion');

INSERT INTO juego (id_juego, titulo, genero) VALUES(4,'Killer Instinct', 'Lucha');

INSERT INTO juego(id_juego, titulo, genero) VALUES(5,'Metroid Prime', 'Accion');

INSERT INTO juego(id_juego, titulo, genero) VALUES(6,'Dragon Quest VII', 'ROL');

SELECT * FROM juego;

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,1, 45);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,2, 30);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,3, 15);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(2,3, 15);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(2,4, 19);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(3,5, 34);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(3,6, 23);

SELECT * FROM consola_juego;

Consulta: Mostrar los tipos de consolas que hay registrados

SELECT tipo FROM consola;

Consulta: Nombre de los juegos que sólo están en 3DS

SELECT titulo FROM juego

WHERE juego.id_juego IN(

SELECT id_juego FROM consola_juego

WHERE consola_juego.id_consola IN(

SELECT id_consola FROM consola

WHERE consola.nombre='3DS')

);

Consulta: Stock total de aquellos juegos cuyo género sea de Acción

SELECT SUM(consola_juego.stock) FROM consola_juego

WHERE consola_juego.id_juego IN(

SELECT id_juego FROM juego

WHERE juego.genero='accion');

Consulta: Titulo de aquellos juegos con stock total que supere las 20 unidades

SELECT juego.titulo FROM juego

WHERE juego.id_juego IN(

SELECT id_juego FROM consola_juego

GROUP BY id_juego

HAVING SUM(consola_juego.stock) > 20);

Realizar un UPDATE – Utilizado para modificar los valores de los campos y registros especificados.

UPDATE juego SET titulo='The Legend of Zelda'

WHERE titulo='Metroid Prime';

Realizar un DELETE – Utilizado para eliminar registros de una tabla de una base de datos

DELETE FROM consola WHERE id_consola=1;

Insertar un registro adicional por cada tabla.

INSERT INTO consola  VALUES(1,'XS','Sobremesa','Sony');

Repaso Parcial - DatabaseNomina

CREATE TABLE temple1 (
nuempl CHAR(6) NOT NULL,
nombre CHAR(12) NOT NULL,
inicial CHAR(1) NOT NULL,
apellido CHAR(15) NOT NULL,
dept CHAR(3) NOT NULL,
tlfn CHAR(4),
feching  DATE NOT NULL,
codtra SMALLINT NOT NULL,
niveduc SMALLINT NOT NULL,
sexo CHAR(1) NOT NULL,
fechnac DATE  NOT NULL,
salario DECIMAL(9,2) NOT NULL
 );
 CREATE TABLE tdepar2 (
numdep CHAR(3) NOT NULL,
nomdep CHAR(36) NOT NULL,
numdirec CHAR(6) NOT NULL
);
 INSERT INTO temple1 ( nuempl, nombre, inicial, apellido, dept, tlfn, feching, codtra, niveduc, sexo, fechnac, salario )
VALUES ( 10, 'CRISTINA', 'I', 'HERNANDEZ', 'A00', 2070, '01.01.1965', 66, 18, 'M', '14.08.1933', 5275 ),
( 20, 'MIGUEL', 'L', 'TAPIA', 'B01', 2140, '10.10.1973', 61, 18, 'H', '02.02.1948', 4125 ),
( 30, 'SALOME', 'A', 'KEMPES', 'C01', 2080, '05.04.1975', 60, 20, 'M', '11.05.1941', 3825 ),
( 50, 'JUAN', 'B', 'GARRIDO', 'E01', 1507, '17.08.1949', 58, 16, 'H', '15.09.1925', 4017 ),
( 60, 'ISIDRO', 'F', 'SUAREZ', 'D11', 2290, '14.09.1973', 55, 16, 'H', '07.07.1945', 3225 ),
( 70, 'EVA', 'D', 'PUENTE', 'D21', 2400, '30.09.1980', 56, 16, 'M', '26.05.1953', 3617 ),
( 90, 'ELENA', 'W', 'HERRANZ', 'E11', 2230, '15.08.1970', 55, 16, 'M', '15.05.1941', 2945 ),
( 100, 'TOMAS', 'Q', 'SOLER', 'E21', 2170, '19.06.1980', 54, 14, 'H', '18.12.1956', 2615 ),
( 110, 'VICENTE', 'G', 'LUENGO', 'A00', 1140, '16.05.1958', 58, 19, 'H', '05.11.1929', 4650 ),
( 120, 'SIMON', '', 'OTERO', 'A00', 2022, '05.12.1963', 58, 14, 'H', '18.10.1942', 2925 ),
( 130, 'DOLORES', 'M', 'QUINTANA', 'C01', 2140, '28.07.1971', 55, 16, 'M', '15.09.1925', 2380 ),
( 140, 'HELIODORA', 'A', 'NIETO', 'C01', 2210, '15.12.1976', 56, 18, 'M', '19.01.1946', 2842 ),
( 150, 'BRUNO', '', 'ALVAREZ', 'D11', 2250, '12.02.1971', 55, 16, 'H', '17.05.1947', 2528 ),
( 160, 'ELISA', 'R', 'PINTO', 'D11', 1307, '11.10.1977', 54, 17, 'M', '12.04.1955', 2225 ),
( 170, 'MATEO', 'J', 'YARZA', 'D11', 2050, '15.09.1978', 54, 16, 'H', '05.01.1951', 2468 ),
( 180, 'MARINA', 'S', 'SANDOVAL', 'D11', 1050, '07.07.1973', 53, 17, 'M', '21.02.1949', 2134 ),
( 190, 'JAIME', 'H', 'WALKER', 'D11', 2100, '27.07.1974', 53, 16, 'H', '25.06.1952', 2045 ),
( 200, 'DAVID', '', 'BONDIA', 'D11', 2340, '03.03.1966', 55, 16, 'H', '29.05.1941', 2774 ),
( 210, 'WENCESLAO', 'T', 'JURADO', 'D11', 2310, '11.04.1979', 52, 17, 'H', '23.02.1953', 1827 ),
( 220, 'JIMENA', 'K', 'LUQUE', 'D11', 2300, '29.08.1968', 55, 18, 'M', '19.03.1948', 2984 ),
( 230, 'JAIME', 'J', 'JIMENEZ', 'D21', 1080, '21.11.1966', 53, 14, 'H', '30.05.1935', 2218 ),
( 240, 'SALVADOR', 'M', 'MARTINEZ', 'D21', 2260, '05.12.1979', 55, 17, 'H', '31.03.1954', 2876 ),
( 250, 'DANIEL', 'S', 'SIERRA', 'D21', 2357, '30.10.1969', 52, 15, 'H', '12.11.1939', 1918 ),
( 260, 'SUSANA', 'P', 'JUNQUERA', 'D21', 2302, '11.09.1975', 52, 16, 'M', '05.10.1936', 1725 ),
( 270, 'MARIA', 'L', 'PEREZ', 'D21', 2217, '30.09.1980', 55, 15, 'M', '23.05.1953', 2738 ),
( 280, 'ENGRACIA', 'R', 'SANCHEZ', 'E11', 2007, '24.03.1967', 54, 17, 'M', '15.05.1941', 2598 ),
( 290, 'JUAN', 'R', 'PALACIOS', 'E11', 2192, '30.05.1980', 42, 12, 'H', '09.07.1946', 1380 ),
( 300, 'PEDRO', 'I', 'SIERRA', 'E11', 3522, '19.06.1972', 48, 14, 'H', '27.10.1936', 1775 ),
( 310, 'MATILDE', 'F', 'SERNA', 'E11', 2130, '12.09.1964', 43, 12, 'M', '21.04.1931', 1574 ),
( 320, 'RAMON', 'V', 'MORAN', 'E21', 7112, '07.07.1965', 52, 16, 'H', '11.08.1932', 1995 ),
( 330, 'WILLY', '', 'LERMA', 'E21', 1132, '23.02.1976', 55, 14, 'H', '18.07.1941', 2537 ),
( 340, 'JAVIER', 'R', 'GIL', 'E21', 1162, '05.05.1947', 54, 16, 'H', '17.05.1926', 2384 ),
( 999, 'ANGEL', 'E', 'MALDONADO', 'E11', '----', '22.03.1988', 69, 20, 'H', '12.11.1965', 7500 );
INSERT INTO tdepar2 ( numdep, nomdep, numdirec )
VALUES ( 'A00', 'CENTRO PROCESOS', 000010 ),
( 'B01', 'PLANIFICACION', 000020 ),
( 'C01', 'INFORMACION', 000030 ),
( 'D01', 'DESARROLLO', '' ),
( 'D11', 'FABRICACION', 000060 ),
( 'D21', 'ADMINISTRACION', 000070 ),
( 'E01', 'SERVICIOS', 000050 ),
( 'E11', 'OPERACIONES', 000090 ),
( 'E21', 'SOFTWARE', 000100 );
select *from tdepar2;
select *from temple1;
SELECT nuempl, nombre, apellido, tlfn , nomdep, numdep
FROM temple1
INNER JOIN tdepar2 ON (temple1.dept = tdepar2.numdep)
WHERE sexo='M' AND (tdepar2.numdep LIKE 'D%' OR tdepar2.numdep LIKE 'E%');
SELECT numdirec, nombre, apellido, codtra, salario
FROM temple1
INNER JOIN tdepar2 ON (temple1.dept = tdepar2.numdep)
WHERE salario > 2000 AND feching > '01/01/1975'
ORDER BY salario DESC;
SELECT apellido, dept, salario
FROM temple1
WHERE dept IN ( 'A00', 'B01', 'C01' , 'D01')
ORDER BY dept, salario DESC;

SELECT salario, nombre
FROM (
SELECT AVG(salario) as salario, COUNT(*) AS nombre, dept
FROM temple1
WHERE NOT codtra > 54
GROUP BY dept
) AS result
WHERE NOT nombre < 3
ORDER BY dept ASC;

SELECT dept, AVG(salario) , COUNT(*)
    FROM temple1
    WHERE codtra <= 54
    GROUP BY dept
    HAVING COUNT(*) > 2
    ORDER BY dept
;

SELECT *
FROM temple1
WHERE dept IN ( 'D11', 'E11' ) AND apellido LIKE 'S%';

Consultas - Primary Key - Foreing Key

drop table if exists libros;
drop table if exists editoriales;

 create table libros(
  codigo serial,
  titulo varchar(40),
  autor varchar(30),
  codigoeditorial smallint,
  primary key(codigo)
 );

 create table editoriales(
  codigo serial,
  nombre varchar(20),
  primary key (codigo)
 );

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

 select *from editoriales;


 insert into libros(titulo,autor,codigoeditorial) values('El aleph','Borges',1);
 insert into libros(titulo,autor,codigoeditorial) values('Martin Fierro','Jose Hernandez',2);
 insert into libros(titulo,autor,codigoeditorial) values('Aprenda PHP','Mario Molina',2);

 select *from libros;

 -- Agregamos una restricción "foreign key" a la tabla "libros":
 alter table libros
   add constraint FK_libros_codigoeditorial
   foreign key (codigoeditorial)
   references editoriales(codigo);

 -- Ingresamos un libro con un código de editorial existente:
 insert into libros(titulo,autor,codigoeditorial) values('Aprenda ASP.Net','Jose Paez',2);

 -- Ingresamos un libro con un código de editorial inexistente:
 insert into libros(titulo,autor,codigoeditorial) values('JSP basico','Tornado Luis',3);
 --Aparece un mensaje de error y no se ejecuta la inserción.


https://www.tutorialesprogramacionya.com/postgresqlya/temarios/descripcion.php?inicio=50&cod=211&punto=53

Ejercicios desarrollados en clase - Abril 16 de 2020

CREATE TABLE MUNICIPIOS1(
id_municipio int,
nombre varchar,
nombre INT,
poblacion int
);


insert into MUNICIPIOS values (10, 'Zapatoca', 20000);
insert into MUNICIPIOS1 values (20, 'Medellin', 400000);
insert into MUNICIPIOS values (30, 'Bogotá', 9000000);
insert into MUNICIPIOS values (40, 'Oiba', 5000);
insert into MUNICIPIOS values (50, 'Cali', 2000000);

select *from MUNICIPIOS1;

SELECT nombre FROM MUNICIPIOS WHERE poblacion>5000 ORDER BY poblacion;

SELECT nombre FROM MUNICIPIOS WHERE poblacion>=5000 ORDER BY nombre;

CREATE TABLE MUNICIPIOS_PRUEBA (
id_municipio int,
nombre varchar,
poblacion int,
primary key (id_municipio)
);

SElect * from MUNICIPIOS1;
SElect * from MUNICIPIOS_PRUEBA;


INSERT INTO MUNICIPIOS1
SELECT * FROM MUNICIPIOS_PRUEBA;

UPDATE MUNI SET nombre='Medellín' WHERE id_municipio=10;

ALTER TABLE MUNICIPIOS_PRUEBA RENAME TO MUNI;

SELECT * FROM MUNI;

DELETE FROM MUNICIPIOS WHERE id_municipio=10;

DELETE FROM MUNICIPIOS1 WHERE id_municipio=20;

drop table MUNICIPIOS1;

SELECT nombre FROM MUNI WHERE poblacion=900000 and id_municipio=20;