Transcript
UN NIVERSID DAD TECN NOLOGICA A DE LOS ANDES CP: INGEN NIERIA DE SISTEMAS S E INFORMÁ ÁTICA GUÍA DE LA ABORATOR RIO Nº 2 Tema: Ejercicios E de SQL
CICLLO 2012-II
Asignaatura: Base de d Datos I Docentte: Ing. Rafaael Ricardo Quispe Q Merm ma
OBJETTIVOS ESPEC CIFICOS Utilice alias en los nom mbres de las tablas. Desarrolle,, practique y mejore la habilidad h de listar registros con la seentencia SELECT.
1
1. INTRODUCCCION
Ejercicio: Crear la base de datos servicios. El script es el siguiente: use master; GO --Eliminar
la base de datos servicios si existe.
IF EXISTS(SELECT * from sys.databases WHERE name='servicios') BEGIN DROP DATABASE servicios; END --Crear la nueva base de datos servicios CREATE DATABASE servicios; go
Se tienen las tablas siguientes: TRABAJADOR(ID_T,NOMBRE, TARIFA,OFICIO,ID_SUP) Cada fila representa un trabajador, identificado por su ID_T. Su nombre es NOMBRE, su tarifa por hora, en euros,su oficio y el identificador de su supervisor. EDIFICIO(ID_E,DIR,TIPO,NIVEL_CALIDAD,CATEGORIA) Cada fila representa un edificio, que se identifica por su ID_E. Se guarda su dirección; el tipo de edificio, su nivel de calidad y su categoría. ASIGNACION(ID_T,ID_E,FECHA_INICIO,NUM_DIAS) Una fila por cada vez que un trabajador es asignado a un edificio. El script es el siguiente
2
use servicios go --Creacion de tablas CREATE TABLE TRABAJADOR ( ID_T INT NOT NULL PRIMARY KEY, NOMBRE CHAR(20) NOT NULL, TARIFA NUMERIC(5,2) NOT NULL, OFICIO CHAR(15) NOT NULL, ID_SUP INT NULL, FOREIGN KEY (ID_SUP) REFERENCES TRABAJADOR(ID_T) ) go CREATE TABLE EDIFICIO ( ID_E INT NOT NULL PRIMARY KEY, DIR CHAR(15) NOT NULL, TIPO CHAR(10) NOT NULL, NIVEL_CALIDAD int NOT NULL, CATEGORIA INT NOT NULL) Go CREATE TABLE ASIGNACION ( ID_T INT NOT NULL, ID_E INT NOT NULL, FECHA_INICIO DateTime, NUM_DIAS INT, PRIMARY KEY (ID_T,ID_E,FECHA_INICIO), FOREIGN KEY (ID_T) REFERENCES TRABAJADOR(ID_T), FOREIGN KEY (ID_E) REFERENCES EDIFICIO(ID_E)) go
3
Inserte los siguientes datos: INSERT INTO TRABAJADOR VALUES (1311,'C. COULOMB',15.5, 'ELECTRICISTA', Null) go INSERT INTO TRABAJADOR VALUES (1234,'M. FARADAY',12.5, 'ELECTRICISTA',1311) Go INSERT INTO TRABAJADOR VALUES (1520,'H. RICKOVER',11.75, 'FONTANERO', Null)
go INSERT INTO TRABAJADOR VALUES (1412,'C. NEMO',13.75, 'FONTANERO',1520) go INSERT INTO TRABAJADOR VALUES (2920,'R. GARRET',10.00, 'ALBAÑIL', Null) go INSERT INTO TRABAJADOR VALUES (3231,'P. MASON',17.40, 'CARPINTERO', Null) go INSERT INTO TRABAJADOR VALUES (3001,'J. BARRISTER',8.20, 'CARPINTERO',3231) go INSERT INTO EDIFICIO VALUES(111,'1231 ASPEN','OFICINA',4,1) go INSERT INTO EDIFICIO VALUES(210,'1011 BIRCH','OFICINA',3,1) go INSERT INTO EDIFICIO VALUES(312,'123 ELM','OFICINA',2,2) go
4
INSERT INTO EDIFICIO VALUES(435,'456 MAPLE','COMERCIO',1,1) Go INSERT INTO EDIFICIO VALUES(460,'1415 BEACH','ALMACEN',3,3) Go INSERT INTO EDIFICIO VALUES(515,'789 OAK','RESIDENCIA',3,2) go INSERT INTO ASIGNACION VALUES (1235,312,10/10/2012, 5) go INSERT INTO ASIGNACION VALUES (1235,515,17/10/2012, 22) go INSERT INTO ASIGNACION VALUES (1311,435,8/10/2012, 12) Go INSERT INTO ASIGNACION VALUES (1311,460,23/10/2012, 24) go INSERT INTO ASIGNACION VALUES (1412,111,1/12/2012, 4) go INSERT INTO ASIGNACION VALUES (1412,210,15/11/2012, 12)
go
5
INSERT INTO ASIGNACION VALUES (1412,312,1/10/2012, 10)
go INSERT INTO ASIGNACION VALUES (1412,435,15/10/2012, 15)
go INSERT INTO ASIGNACION VALUES (1412,460,8/10/2012, 18)
go INSERT INTO ASIGNACION VALUES (1412,515,5/11/2012, 8)
go INSERT INTO ASIGNACION VALUES (1520,312,30/10/2012, 17)
go INSERT INTO ASIGNACION VALUES (1520,515,9/10/2012, 14)
go INSERT INTO ASIGNACION VALUES (2920,210,10/11/2012, 15)
go INSERT INTO ASIGNACION VALUES (2920,435,28/10/2012, 10)
go INSERT INTO ASIGNACION VALUES (2920,460,5/10/2012, 18)
go
6
INSERT INTO ASIGNACION VALUES (3001,111,8/10/2012, 14) go INSERT INTO ASIGNACION VALUES (3001,210,27/10/2012, 14) go INSERT INTO ASIGNACION VALUES (3231,111,10/10/2012, 8) go INSERT INTO ASIGNACION VALUES (3231,312,24/10/2012, 20) go
El Diagrama de base de datos es:
7
Realice las siguientes consultas: 1. Nombre de los trabajadores cuya tarifa estan entre 10 y 12 euros. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.
¿Cuáles son los oficios de los trabajadores asignados al edificio 435? Indicar el nombre del trabajador y el su supervisor. Nombre de los trabajadores asignados a oficinas. ¿Qué trabajadores reciben una tarifa por hora mayor que la de su supervisor? ¿Cuál es el numero total de dias que se han dedicado a fontaneria en el edificio 312? ¿Cuantos tipos de oficios diferentes hay? Para cada supervisor, ¿Cuál es la tarifa por hora mas alta que se paga a un trabajador que informa a ese supervisor? Para cada supervisor que supervisa a mas de un trabajador, ¿Cuál es la tarifa mas alta que se paga a un trabajador que informa a ese supervisor? ¿Qué trabajadores reciben una tarifa por hora menor que la del promedio? ¿Qué trabajadores reciben una tarifa por hora menor que la del promedio de los trabajadores que tienen su mismo oficio? ¿Qué trabajadores reciben una tarifa por hora menor que la del promedio de los trabajadores que dependen del mismo supervisor que el? Seleccione el nombre de los electricistas asignados al edificio 435 y la fecha en la que empezaron a trabajar en él. ¿Qué supervisores tienen trabajadores que tienen una tarifa por hora por encima de los 12 euros? Incrementar en un 5 por cierto la tarifa por hora de todos los trabajadores a los que supervisa el supervisor C. COULOMB.
8