Muestra las diferencias entre dos versiones de la página.
|
materias:respuestas-taller-sql [2012/03/28 10:23] caolarte creado |
materias:respuestas-taller-sql [2012/03/28 10:26] (actual) caolarte |
||
|---|---|---|---|
| Línea 1: | Línea 1: | ||
| - | SELECT V.SERIE, COL.NOMBRE,V.KM | + | ====== Consultas ====== |
| - | FROM VEHICULOS V INNER JOIN COLORES COL ON (COL.ID = V.COL_ID) | + | |
| - | INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) | + | |
| - | WHERE M.NOMBRE='MAZDA' AND V.PRECIO BETWEEN 25000000::MONEY AND 30000000::MONEY AND V.MODELO=2008; | + | |
| - | serie | nombre | km | ||
| - | ---------+--------+------- | ||
| - | 7765654 | AZUL | 65000 | ||
| - | bd1214=> SELECT V.SERIE,V.PLACA,CIU.NOMBRE,KM,COL.NOMBRE,M.NOMBRE,PRECIO | + | * El serial, color y kilometraje de los vehículos Mazda que sean modelo 2008 y que su precio esté entre $25.000.000 y $30.000.000. |
| - | FROM VEHICULOS V LEFT JOIN CIUDADES CIU ON (CIU.ID = V.CIU_PLACA_ID) | + | |
| - | INNER JOIN COLORES COL ON (COL.ID = V.COL_ID) | + | |
| - | INNER JOIN MARCAS M ON (M.ID = V.MAR_ID); | + | |
| - | serie | placa | nombre | km | nombre | nombre | precio | + | |
| - | ------------+--------+----------+-------+--------+-----------+---------------- | + | |
| - | 3435436673 | FGT485 | BOGOTA | 56000 | ROJO | MAZDA | $34,000,000.00 | + | |
| - | 3435663233 | | | 0 | AZUL | RENAULT | $45,000,000.00 | + | |
| - | 22234234 | BFT346 | BOGOTA | 43200 | NEGRO | MAZDA | $19,000,000.00 | + | |
| - | 22367543 | POA211 | MEDELLIN | 23000 | BLANCO | MAZDA | $44,000,000.00 | + | |
| - | 22245444 | | | 0 | BLANCO | CHEVROLET | $56,000,000.00 | + | |
| - | 7887677676 | VVB322 | BOGOTA | 15000 | AZUL | RENAULT | $32,000,000.00 | + | |
| - | 4454536773 | | | 0 | NEGRO | MAZDA | $78,000,000.00 | + | |
| - | 3456776546 | AAQ111 | CALI | 98000 | BLANCO | RENAULT | $15,000,000.00 | + | |
| - | 7765654 | KLK232 | MEDELLIN | 65000 | AZUL | MAZDA | $25,000,000.00 | + | |
| - | 3345677 | MTR221 | BOGOTA | 34000 | NEGRO | CHEVROLET | $28,000,000.00 | + | |
| + | SELECT V.SERIE, COL.NOMBRE,V.KM | ||
| + | FROM VEHICULOS V INNER JOIN COLORES COL ON (COL.ID = V.COL_ID) | ||
| + | INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) | ||
| + | WHERE M.NOMBRE='MAZDA' AND V.PRECIO BETWEEN 25000000::MONEY AND 30000000::MONEY AND V.MODELO=2008; | ||
| - | bd1214=>; SELECT COUNT(*) FROM VEHICULOS WHERE PLACA IS NULL; | + | serie | nombre | km |
| - | count | + | ---------+--------+------- |
| - | ------- | + | 7765654 | AZUL | 65000 |
| + | |||
| + | |||
| + | |||
| + | * Serial, placa, nombre de la ciudad de la placa (si la tiene), kilometraje, (nombre del) color, (nombre de la) marca y precio de todos los vehículos del concesionario. | ||
| + | |||
| + | |||
| + | SELECT V.SERIE,V.PLACA,CIU.NOMBRE,KM,COL.NOMBRE,M.NOMBRE,PRECIO | ||
| + | FROM VEHICULOS V LEFT JOIN CIUDADES CIU ON (CIU.ID = V.CIU_PLACA_ID) | ||
| + | INNER JOIN COLORES COL ON (COL.ID = V.COL_ID) | ||
| + | INNER JOIN MARCAS M ON (M.ID = V.MAR_ID); | ||
| + | serie | placa | nombre | km | nombre | nombre | precio | ||
| + | ------------+--------+----------+-------+--------+-----------+---------------- | ||
| + | 3435436673 | FGT485 | BOGOTA | 56000 | ROJO | MAZDA | $34,000,000.00 | ||
| + | 3435663233 | | | 0 | AZUL | RENAULT | $45,000,000.00 | ||
| + | 22234234 | BFT346 | BOGOTA | 43200 | NEGRO | MAZDA | $19,000,000.00 | ||
| + | 22367543 | POA211 | MEDELLIN | 23000 | BLANCO | MAZDA | $44,000,000.00 | ||
| + | 22245444 | | | 0 | BLANCO | CHEVROLET | $56,000,000.00 | ||
| + | 7887677676 | VVB322 | BOGOTA | 15000 | AZUL | RENAULT | $32,000,000.00 | ||
| + | 4454536773 | | | 0 | NEGRO | MAZDA | $78,000,000.00 | ||
| + | 3456776546 | AAQ111 | CALI | 98000 | BLANCO | RENAULT | $15,000,000.00 | ||
| + | 7765654 | KLK232 | MEDELLIN | 65000 | AZUL | MAZDA | $25,000,000.00 | ||
| + | 3345677 | MTR221 | BOGOTA | 34000 | NEGRO | CHEVROLET | $28,000,000.00 | ||
| + | |||
| + | |||
| + | * El número de vehículos nuevos (i.e., que no tienen placa) disponibles en el concesionario. | ||
| + | |||
| + | SELECT COUNT(*) FROM VEHICULOS WHERE PLACA IS NULL; | ||
| + | count | ||
| + | ------- | ||
| 3 | 3 | ||
| + | * Las marcas de las cuales se tienen más de 2 vehículos en el inventario. | ||
| - | bd1214=> SELECT M.NOMBRE, COUNT(*) FROM VEHICULOS V INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) | + | SELECT M.NOMBRE, COUNT(*) FROM VEHICULOS V INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) |
| - | bd1214-> GROUP BY M.NOMBRE | + | GROUP BY M.NOMBRE |
| - | bd1214-> HAVING COUNT(*) >2; | + | HAVING COUNT(*) >2; |
| - | nombre | count | + | nombre | count |
| - | ---------+------- | + | ---------+------- |
| - | RENAULT | 3 | + | RENAULT | 3 |
| - | MAZDA | 5 | + | MAZDA | 5 |
| - | SELECT DISTINCT COL.NOMBRE | + | * Los diferentes colores disponibles de vehículos CHEVROLET en el concesionario. |
| - | FROM VEHICULOS V | + | |
| - | INNER JOIN COLORES COL ON (COL.ID = V.COL_ID) | + | |
| - | INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) | + | |
| - | WHERE M.NOMBRE='CHEVROLET'; | + | |
| - | nombre | + | SELECT DISTINCT COL.NOMBRE |
| - | -------- | + | FROM VEHICULOS V |
| - | BLANCO | + | INNER JOIN COLORES COL ON (COL.ID = V.COL_ID) |
| - | NEGRO | + | INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) |
| + | WHERE M.NOMBRE='CHEVROLET'; | ||
| + | nombre | ||
| + | -------- | ||
| + | BLANCO | ||
| + | NEGRO | ||
| + | |||
| + | |||
| + | * La marca de la cual se tiene un mayor número de vehículos | ||
| - | SELECT M.NOMBRE, COUNT(*) FROM VEHICULOS V INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) GROUP BY M.NOMBRE | + | SELECT M.NOMBRE, COUNT(*) FROM VEHICULOS V INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) GROUP BY M.NOMBRE |
| - | HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM VEHICULOS V INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) GROUP BY M.NOMBRE); | + | HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM VEHICULOS V INNER JOIN MARCAS M ON (M.ID = V.MAR_ID) GROUP BY M.NOMBRE); |
| - | nombre | count | + | nombre | count |
| - | --------+------- | + | --------+------- |
| - | MAZDA | 5 | + | MAZDA | 5 |
| + | |||
| + | |||
| + | * La marca de la cual no se tienen vehículos nuevos | ||
| - | SELECT NOMBRE FROM MARCAS WHERE ID NOT IN (SELECT MAR_ID FROM VEHICULOS WHERE PLACA IS NULL); | + | SELECT NOMBRE FROM MARCAS WHERE ID NOT IN (SELECT MAR_ID FROM VEHICULOS WHERE PLACA IS NULL); |
| - | nombre | + | nombre |
| - | -------- | + | -------- |
| - | (0 filas) | + | (0 filas) |