create database CAMIONEROS go use CAMIONEROS go create table camion ( matricula varchar varchar( (6) primary key key, , modelo varchar varchar( (30), 30), ) create table camionero ( idCamionero int primary key key, , nombre varchar varchar( (40), 40), fono int int, , edad int ) create table registro ( idregistro int identity primary key key, , idCamionero int references camionero camionero( (idCamionero idCamionero), ), matricula varchar varchar( (6) references camion camion( (matricula matricula), ), fechaConduccion date date, , kmRecorridos int )
INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT
INTO INTO INTO INTO INTO INTO INTO INTO INTO
camionero camionero camionero camionero camionero camionero camionero camionero camionero
INSERT INSERT INSERT INSERT INSERT INSERT
INTO INTO INTO INTO INTO INTO
camion camion camion camion camion camion
INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT
INTO INTO INTO INTO INTO INTO INTO INTO
registro registro registro registro registro registro registro registro
VALUES(11111, VALUES( 11111,'Aquiles Brinco', Brinco',6911897, 6911897,56) 56) VALUES( VALUES (22222, 22222,'Aquiles Baeza Parada',NULL, Parada',NULL,33 33) ) VALUES( VALUES (33333, 33333,'Armando Casas', Casas',7484951, 7484951,49) 49) VALUES( VALUES (44444, 44444,'Aquiles Castro',NULL, Castro',NULL,60 60) ) VALUES( VALUES (55555, 55555,'Armando Mocha', Mocha',7489606, 7489606,52) 52) VALUES( VALUES (66666, 66666,'Guillermo Nigote', Nigote',7462884, 7462884,47) 47) VALUES( VALUES (77777, 77777,'Elvis Tek', Tek',7425763, 7425763,50) 50) VALUES( VALUES (88888, 88888,'Aquiles Bailo',NULL, Bailo',NULL,48 48) ) VALUES( VALUES (99999, 99999,'Alan Brito Delgado', Delgado',6915220, 6915220,39) 39)
VALUES('DBD001' VALUES( 'DBD001', ,'Scania R 560') 560') VALUES( VALUES ('DBD002' 'DBD002', ,'Scania R 620') 620') VALUES( VALUES ('DBD003' 'DBD003', ,'Freightliner Cascadia DDC 515') 515') VALUES( VALUES ('DBD004' 'DBD004', ,'Freightliner Cascadia DDC 475') 475') VALUES( VALUES ('DBD005' 'DBD005', ,'Mercedes-B 'Mercedes-Benz enz Actros 1841 LS' LS') ) VALUES( VALUES ('DBD006' 'DBD006', ,'Mercedes-Benz Axor 2533') 2533') VALUES(11111, VALUES( 11111,'DBD001' 'DBD001', ,'2012/10/12' '2012/10/12', ,3400) 3400) VALUES( VALUES (11111, 11111,'DBD002' 'DBD002', ,'2012/09/18' '2012/09/18', ,2500) 2500) VALUES( VALUES (22222, 22222,'DBD001' 'DBD001', ,'2012/07/05' '2012/07/05', ,4300) 4300) VALUES( VALUES (22222, 22222,'DBD002' 'DBD002', ,'2012/06/05' '2012/06/05', ,7500) 7500) VALUES( VALUES (11111, 11111,'DBD004' 'DBD004', ,'2012/02/12' '2012/02/12', ,1250) 1250) VALUES( VALUES (33333, 33333,'DBD004' 'DBD004', ,'2012/02/01' '2012/02/01', ,3500) 3500) VALUES( VALUES (44444, 44444,'DBD001' 'DBD001', ,'2012/01/23' '2012/01/23', ,2300) 2300) VALUES( VALUES (55555, 55555,'DBD006' 'DBD006', ,'2012/01/18' '2012/01/18', ,5400) 5400)
SELECT * FROM camionero SELECT * FROM camion SELECT * FROM registro /*1. Obtener el nombre del camionero y toda la información registrada de su salida*/ --Sin uso INNER JOIN
SELECT camionero.nombre, camion.matricula, registro.fechaConduccion, registro.kmRecorridos FROM camion, camionero, registro WHERE registro.idCamionero = camionero.idCamionero AND registro.matricula = camion.matricula --Usando INNER JOIN SELECT camionero.nombre, camion.matricula, registro.fechaConduccion, registro.kmRecorridos FROM registro INNER JOIN camionero ON registro.idCamionero = camionero.idCamionero INNER JOIN camion ON registro.matricula = camion.matricula
/*2. Obtener el nombre del camionero y la cantidad de kilómetros recorridos.*/ SELECT camionero.nombre, SUM(registro.kmRecorridos) AS [Km Recorridos] FROM registro INNER JOIN camionero ON registro.idCamionero = camionero.idCamionero GROUP BY camionero.nombre ORDER BY [Km Recorridos] DESC
/*3. Obtener el nombre y km recorridos de todos los camioneros que han manejado al menos 2 veces*/ SELECT camionero.nombre, SUM(registro.kmRecorridos) AS [Km Recorridos], COUNT(registro.idregistro) AS [Nro Viajes] FROM registro INNER JOIN camionero ON registro.idCamionero = camionero.idCamionero GROUP BY camionero.nombre HAVING COUNT(registro.idregistro) >= 2 ORDER BY [Km Recorridos] /*4. Obtener el nombre y km recorridos de todos los camioneros aun cuando no hayan manejado un camión*/ SELECT camionero.nombre, SUM(registro.kmRecorridos) AS [Km Recorridos] FROM camionero left JOIN registro ON camionero.idCamionero = registro.idCamionero GROUP BY camionero.nombre ORDER BY [Km Recorridos] /*5. Mostrar modelo del camión, nombre del chofer y fecha en la que fue conducido*/ SELECT camion.modelo, camionero.nombre, registro.fechaConduccion FROM camion INNER JOIN registro ON camion.matricula = registro.matricula INNER JOIN camionero ON registro.idCamionero = camionero.idCamionero ORDER BY camion.modelo /*6. Obtener el listado de choferes que no tienen telefono*/ SELECT * FROM camionero WHERE camionero.fono IS NULL /*7. Obtener el listado de los choferes que tienen teléfono*/ SELECT * FROM camionero WHERE camionero.fono IS NOT NULL
/*8. Obtener el listado de camioneros que no han conducido*/ SELECT camionero.nombre FROM registro right join camionero ON camionero.idCamionero = registro.idCamionero WHERE registro.kmRecorridos IS NULL SELECT camionero.nombre FROM camionero LEFT JOIN registro ON camionero.idCamionero = registro.idCamionero GROUP BY camionero.nombre HAVING SUM(registro.kmRecorridos) IS NULL /*9. Obtener el listado de camiones que no han sido utilizados*/ SELECT camion.matricula, camion.modelo FROM registro right JOIN camion ON registro.matricula = camion.matricula WHERE registro.kmRecorridos IS NULL SELECT camion.matricula, camion.modelo FROM camion LEFT JOIN registro ON registro.matricula = camion.matricula WHERE registro.kmRecorridos IS NULL /*10. Obtener el nombre del chofer y modelo de camión que fueron manejados durante el primer trimestre del año 2012*/ --Usando Operadores Relacionales SELECT camionero.nombre, camion.modelo, registro.fechaConduccion FROM camionero INNER JOIN registro ON camionero.idCamionero = registro.idCamionero INNER JOIN camion ON registro.matricula = camion.matricula WHERE registro.fechaConduccion >= '2012-01-01' AND registro.fechaConduccion <= '2012-03-31' ORDER BY registro.fechaConduccion --Usando BETWEEN SELECT camionero.nombre, camion.modelo, registro.fechaConduccion FROM camionero INNER JOIN registro ON camionero.idCamionero = registro.idCamionero INNER JOIN camion ON registro.matricula = camion.matricula WHERE registro.fechaConduccion BETWEEN '2012-01-01' AND '2012-03-31' ORDER BY registro.fechaConduccion --Usando Funciones MONTH y YEAR SELECT camionero.nombre, camion.modelo, registro.fechaConduccion FROM camionero INNER JOIN registro ON camionero.idCamionero = registro.idCamionero INNER JOIN camion ON registro.matricula = camion.matricula WHERE YEAR(registro.fechaConduccion) = 2012 AND MONTH(registro.fechaConduccion) IN (1,2,3) ORDER BY registro.fechaConduccion /*11. Obtener el listado de choferes que recorrieron 4000 o más kms.*/ SELECT camionero.nombre, SUM(registro.kmRecorridos) AS [Km Recorridos]
FROM camionero INNER JOIN registro ON camionero.idCamionero = registro.idCamionero GROUP BY camionero.nombre HAVING SUM(registro.kmRecorridos) >= 4000
/*12. Obtener el nombre del camionero que realizó el viaje más largo.*/ SELECT camionero.nombre, MAX(registro.kmRecorridos) FROM camionero INNER JOIN registro ON camionero.idCamionero = registro.idCamionero GROUP BY camionero.nombre HAVING MAX(registro.kmRecorridos) = ( SELECT MAX(registro.kmRecorridos) FROM registro ) SELECT camionero.nombre, registro.kmRecorridos FROM camionero INNER JOIN registro ON camionero.idCamionero = registro.idCamionero WHERE registro.kmRecorridos = ( SELECT MAX(registro.kmRecorridos) from registro ) /*13. Obtener el modelo del camión que realizó el viaje más corto.*/ SELECT camion.modelo, registro.kmRecorridos FROM camion INNER JOIN registro ON camion.matricula = registro.matricula WHERE registro.kmRecorridos = ( SELECT MIN(registro.kmRecorridos) FROM registro ) /*14. Indique el modelo del camión que realizó 2 o más viajes indicando la cantidad de kms recorridos*/ SELECT camion.modelo, COUNT(registro.idregistro) AS [Cantidad Viajes], SUM(registro.kmRecorridos) AS [Km Recorridos] FROM camion INNER JOIN registro ON camion.matricula = registro.matricula GROUP BY camion.modelo HAVING COUNT(registro.idregistro) >= 2 ORDER BY [Cantidad Viajes] /*15. Indique que choferes han conducido camiones Scania y Freightliner*/ SELECT camionero.nombre, camion.modelo, registro.fechaConduccion FROM camionero INNER JOIN registro ON camionero.idCamionero = registro.idCamionero INNER JOIN camion ON registro.matricula = camion.matricula WHERE camion.modelo LIKE 'Scania%' OR camion.modelo LIKE 'Freightliner%'
/*Actualización de Registros UPDATE SET = {[, = ,..., = ]} [ WHERE ] */ --Actualizar los kilometros recorridos por cada chofer a su equivalente en millas SELECT * FROM registro UPDATE registro SET kmRecorridos = kmRecorridos / 1.6093 --Actualizar el fono de los camiones que no poseen fono a 0 SELECT * FROM camionero UPDATE camionero SET fono = 0 WHERE fono IS NULL /*Eliminación de Registros DELETE FROM [ WHERE ] */ --Eliminar los viajes que se realizaron durante el primer semestre del año 2012 DELETE FROM registro WHERE fechaConduccion BETWEEN '2012-01-01' AND '2012-06-30' --Eliminar todos los camiones Scania DELETE FROM camion WHERE modelo LIKE 'Scania%' --Eliminar todos los viajes en los cuales participó un camión Scania DELETE registro FROM registro INNER JOIN camion ON registro.matricula = camion.matricula WHERE camion.modelo LIKE 'Scania%'
