Parcial 2-2019

1. CREACIÓN DE BASE DE DATOS

Create database Contactos;

Use Contactos;

2. CREAR TABLAS

Create table Compania(CompaniaId int NOT NULL PRIMARY KEY, Nit Varchar(15) NOT NULL, Nombre Varchar(150) NOT NULL, NombreCorto Varchar(150) NOT NULL, Sigla Varchar(20) NOT NULL, Activa boolean NOT NULL);

Create table Contacto(ContactoId int NOT NULL PRIMARY KEY, DocTipoId char(2) NOT NULL, Documento Varchar(20) NOT NULL, Nombre Varchar(100) NOT NULL, Apellido Varchar(100) NOT NULL, Activo boolean NOT NULL, Sexo boolean NOT NULL, Email Varchar(250) NULL);

Create table Cargo(CargoId varchar(3) NOT NULL PRIMARY KEY, Nombre Varchar(50) NOT NULL);

Create table TipoDireccion(TipoId varchar(3) NOT NULL PRIMARY KEY, Nombre Varchar(50) NOT NULL);

Create table CompaniaContacto(CompaniaId int NOT NULL, ContactoId int, CargoId varchar(3) NOT NULL,

FOREIGN KEY (CompaniaId) REFERENCES Compania (CompaniaId) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (ContactoId) REFERENCES Contacto (ContactoId) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (CargoId) REFERENCES Cargo (CargoId) ON DELETE CASCADE ON UPDATE CASCADE);

Create table ContactoDireccion(ContactoId int NOT NULL, TipoId varchar(3) NOT NULL, Telefono Varchar(20) NOT NULL, Direccion Varchar(250) NOT NULL, MunicipioId Varchar(10) NOT NULL,

FOREIGN KEY (ContactoId) REFERENCES Contacto (ContactoId) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (TipoId) REFERENCES TipoDireccion (TipoId) ON DELETE CASCADE ON UPDATE CASCADE);

sse

show tables;

3. INSERTAR REGISTROS A LAS TABLAS

tabla compania:

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (1, '123456789', 'Compañia_AA 1', 'C1', 'AA', 1);

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (2, '154633169', 'Compañia_BB 2', 'C2', 'BB', 1);

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (3, '333333333', 'Compañia_CC 3', 'C3', 'CC', 1);

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (4, '444444444', 'Compañia_DD 4', 'C4', 'DD', 1);

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (5, '555555555', 'Compañia_EE 5', 'C5', 'EE', 1);

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (6, '666666666', 'Compañia_FF 6', 'C6', 'FF', 1);

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (7, '777777777', 'Compañia_GG 7', 'C7', 'GG', 1);

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (8, '888888888', 'Compañia_HH 8', 'C8', 'HH', 0);

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (9, '999999999', 'Compañia_II 9', 'C9', 'II', 0);

INSERT INTO Compania(CompaniaId, Nit, Nombre, NombreCorto, Sigla, Activa) VALUES (10, '646574979', 'Compañia_JJ 10', 'C10', 'JJ', 0);

select * from Compania;

tabla contacto:

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (1, 'CC', '70000000', 'Cristian', 'Gutierrez', 1, 1, 'cristian.g@aa.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (2, 'CC', '42000000', 'Katerin', 'Perez', 1, 0, 'katerin.p@aa.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (3, 'CC', '70000001', 'Juan', 'Ochoa', 1, 1, 'juan.0@aa.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (4, 'CC', '42000001', 'Clara', 'Lopez', 1, 0, 'clara.l@aa.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (5, 'CC', '70000002', 'Milton', 'Londoño', 1, 1, 'milton.l@bb.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (6, 'CC', '42000002', 'Sofia', 'Cartagena', 1, 0, 'sofia.c@bb.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (7, 'CC', '70000003', 'Hernan', 'Gonzalez', 1, 1, 'hernan.g@bb.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (8, 'CC', '42000003', 'Marcela', 'Rivera', 1, 0, 'marcela.r@cccom');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (9, 'CC', '70000004', 'Juan Andres', 'Gonzalez', 1, 1, 'juanandres.g@cc.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (10, 'CC', '42000004', 'Ana Isabel', 'Olaya Ruiz', 0, 0, 'anaisabel.o@cc.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (11, 'CC', '70000005', 'Jaime', 'Rios', 1, 1, 'jaime.r@dd.com');

INSERT INTO Contacto(ContactoId, DocTipoId, Documento, Nombre, Apellido, Activo, Sexo, Email) VALUES (12, 'CC', '42000005', 'Diana Patricia', 'Aguirre', 1, 0, 'dianapatricia.a@dd.com');

select * from Contacto;

tabla cargo:

INSERT INTO Cargo(CargoId, Nombre) VALUES ('GG', 'Gerente general');

INSERT INTO Cargo(CargoId, Nombre) VALUES ('GO', 'Gerente de operaciones');

INSERT INTO Cargo(CargoId, Nombre) VALUES ('DC', 'Director comercial');

INSERT INTO Cargo(CargoId, Nombre) VALUES ('DRH', 'Director recursos humanos');

INSERT INTO Cargo(CargoId, Nombre) VALUES ('OPE', 'Operario');

INSERT INTO Cargo(CargoId, Nombre) VALUES ('SEC', 'Secretaria');

INSERT INTO Cargo(CargoId, Nombre) VALUES ('CON', 'Contador');

INSERT INTO Cargo(CargoId, Nombre) VALUES ('TI', 'Tecnologia e informatica');

INSERT INTO Cargo(CargoId, Nombre) VALUES ('AS', 'Analista de sistemas');

select * from Cargo;

tabla tipo direccion:

INSERT INTO TipoDireccion(TipoId, Nombre) VALUES ('OFI', 'Oficina');

INSERT INTO TipoDireccion(TipoId, Nombre) VALUES ('HOG', 'Hogar');

INSERT INTO TipoDireccion(TipoId, Nombre) VALUES ('DES', 'Despachos');

INSERT INTO TipoDireccion(TipoId, Nombre) VALUES ('XXX', 'Desconocida');

select * from TipoDireccion;

tabla compania contacto

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (1, 1, 'GG');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (1, 2, 'DC');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (2, 3, 'GG');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (2, 4, 'DRH');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (3, 5, 'GG');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (3, 6, 'DRH');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (4, 7, 'GO');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (4, 8, 'DC');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (5, 9, 'GO');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (5, 10, 'DC');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (6, 11, 'GO');

INSERT INTO CompaniaContacto(CompaniaId, ContactoId, CargoId) VALUES (6, 12, 'AS');

select * from CompaniaContacto;

tabla contacto direccion:

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (1, 'OFI', '3001234567', 'Calle 10 No. 40-20', 'MED');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (1, 'HOG', '2502010', 'Carrera 80 No. 50-1', 'MED');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (2, 'OFI', '3001234568', 'Calle 20 No. 40-20', 'BOG');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (2, 'HOG', '2502011', 'Carrera 90 No. 50-1', 'BOG');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (3, 'OFI', '3011234567', 'Calle 60 No. 40-20', 'MED');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (3, 'HOG', '2802010', 'Carrera 40 No. 50-1', 'MED');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (4, 'OFI', '3211234599', 'Calle 15 No. 40-20', 'BOG');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (4, 'HOG', '2802010', 'Carrera 88 No. 50-1', 'BOG');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (5, 'OFI', '3151234588', 'Calle 99 No. 40-20', 'MED');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (5, 'HOG', '2202011', 'Carrera 22 No. 50-1', 'MED');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (6, 'OFI', '3654234567', 'Calle 56 No. 40-20', 'BOG');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (6, 'HOG', '2565456', 'Carrera 8 No. 50-1', 'BOG');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (7, 'OFI', '3922234567', 'Calle 1 No. 40-20', 'CAL');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (7, 'HOG', '2951310', 'Carrera 45 No. 50-1', 'CAL');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (8, 'OFI', '3025634567', 'Calle 77 No. 40-20', 'CAL');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (9, 'HOG', '2960050', 'Carrera 78A No. 50-1', 'CAL');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (9, 'OFI', '3015790567', 'Calle 10B No. 40-20', 'CAL');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (9, 'HOG', '4393160', 'Carrera 80C No. 50-1', 'CAL');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (10, 'XXX', '9421321', 'Carrera 45F No. 50-1', 'BUC');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (11, 'XXX', '3632114567', 'Calle 8 No. 40-20', 'BUC');

INSERT INTO ContactoDireccion(ContactoId, TipoId, Telefono, Direccion, MunicipioId) VALUES (12, 'XXX', '1624654567', 'Calle 9 No. 40-20', 'BUC');

select *from contactodireccion;

4. MODIFICAR DATOS CON DATOS DE OTRAS TABLAS

a. Insertar registros con valores de otra tabla

-Insertar en la tabla CompaniaContacto2, los contactos de la compañia que vivan en medellin o en cali

comando:

INSERT INTO CompaniaContacto2(CompaniaId, ContactoId, Nombre, Apellido, MunicipioId, Telefono, Direccion)

SELECT CC.CompaniaId, CC.ContactoId, C.Nombre, C.Apellido, CD.MunicipioId, CD.Telefono, CD.Direccion

FROM CompaniaContacto CC

INNER JOIN Contacto C ON C.ContactoId = CC.ContactoId

INNER JOIN ContactoDireccion CD ON CD.ContactoId = CC.ContactoId

WHERE CD.MunicipioId IN ('CAL', 'MED');

SELECT * FROM CompaniaContacto2;

b. Actualizar datos con valores de otra tabla

En la tabla CompaniaContacto2, actualizar el nombre del cargo, el cual no fue adicionado en la instruccion anterior

comando:

update CompaniaContacto2 CC2

INNER JOIN CompaniaContacto CC ON CC.CompaniaId = CC2.CompaniaId AND CC.ContactoId = CC2.ContactoId

INNER JOIN Cargo CA ON CA.CargoId = CC.CargoId

SET CC2.Cargo = CA.Nombre;

SELECT * FROM CompaniaContacto2;

c. Borrar registros consultando otras tablas

Borrar de la tabla ContactoDireccion, los registros que tengan tipo de direccion desconocida

comando:

select cd.* from ContactoDireccion CD inner join TipoDireccion TD on CD.TipoId = TD.TipoId where TD.Nombre = 'Desconocida';

delete ContactoDireccion from ContactoDireccion inner join TipoDireccion TD on ContactoDireccion.TipoId = TD.TipoId where TD.Nombre = 'Desconocida';

select cd.* from ContactoDireccion CD inner join TipoDireccion TD on CD.TipoId = TD.TipoId where TD.Nombre = 'Desconocida';

DESCARGAR Parc...ql-4.txt

tee con todos los procedimientos.