aulaclic

Ejercicios de SQL (hoja 4ª, ejemplos con subconsultas)

Se parte de una base de datos BD2.mdb, con las tablas, campos y relaciones de la hoja 3ª :

En la intrucción SELECT los nombres de los campos que se pueden seleccionar no son únicamente los de tablas, también se pueden coger nombres de consultas ( obtenidas previamente mediante SELECT y almacenadas en la BD, o escritas directamente en la instrucción)

Ejemplo 1º

Si se creó una consulta llamada conHoja3_3 ( la consulta que tenía todos los campos de las tres tablas relacionados), se puede crear una consulta que obtenga para cada señor la ciudad donde tiene casas de las dos formas siguientes:

1ª conHoja4_0

SELECT tblNOmbres.Nombre AS NOMBRE, tblCiudad.NombreCiudad AS CIUDAD

FROM conHoja3_3;

2ª(conHoja4_1

SELECT tblNOmbres.Nombre AS NOMBRE, tblCiudad.NombreCiudad AS CIUDAD

FROM (SELECT *

FROM (tblCiudad INNER JOIN tblTienePisoEn ON tblCiudad.CodCiudad=tblTienePisoEn.CodCiudad) INNER JOIN tblNombres ON tblNOmbres.DNI= tblTienePisoEn.DNI);

Como se aprecia en esta segunda forma, el parámetro de FROM no es una tabla exactamente, sino el resultado de una consulta obtenida mediante SELECT, a esta consulta dentro de otra consulta se le llama subconsulta.

Ejemplo 2 º El ejercicio hoja3_4, para obtener el nombre y el número de casas habíamos agrupado por dos campos DNI, Nombre para poder obtener lo pedido, resaltando que agrupar por Nombre sólo para poder sacar el Nombre era una solución poco eficiente. La instrucción SQL escrita era:ConHoja3_4SELECT tblNombres.Nombre, Count(*) AS NcasasFROM tblNombres INNER JOIN tblTienePisoEn ON tblNombres.DNI=tblTienePisoEn.DNIGROUP BY tblNombres.DNI, tblNombres.Nombre;

Nombre Ncasasuno 2

dos 3tres 3cuatro 1

Este mismo problema utilizando una subconsulta( la grabamos como subconsulta3_4), sería1º) Si escribimos la siguiente consulta, obtendríamos:

subconsulta3_4SELECT tblTienepisoEn.DNI, Count(*) AS NcasasFROM tblTienePisoEN GROUP BY tblTienePisoEN.DNI;Donde sólo se agrupa por el campo DNI

Y hacemos un inner join de lo obtenido, con la tabla tblNombresConHoja4_2aSELECT tblNombres.nombre, NcasasFROM tblNombres INNER JOIN subconsulta3_4 ON tblNombres.DNI=subconsulta3_4.DNI;

Otra forma de obtener lo mismo, escribiendo directamente la consultaConHoja4_2bSELECT tblNombres.nombre, NcasasFROM tblNombres INNER JOIN (SELECT tblTienepisoEn.DNI, Count(*) AS Ncasa sFROM tblTienePisoEn GROUP BY blTienePisoEn.DNI) as subAUX ON tblNombres.DNI=subAUX.DNI;

Ejemplo 3.: ( conHoja4_3) Supongamos que deseamos hacer una consulta que obtenga los nombres de aquellos señores que no tienen ninguna casa. (en tblTienePisoen , el señor con DNI=55555, no tiene piso )Una forma distinta a la obtenida mediante LEFT JOIN ( ej. 7 de hoja3) utilizando subconsultas sería:

1º- Seleccionar los DNI de aquellos que tienen casa:

SELECT DISTINCT tblTienePisoEn.DNI FROM tblTienePisoEn;

2º- Seleccionar los nombres de señores que exiten en tblNombres y no están en la selección anterior.

SELECT tblNombres.DNI, tblNombres.NOMBREFROM tblNombresWHERE tblNombres.DNI NOT IN (SELECT DISTINCT tblTienePisoEn.DNI FROM tblTienePisoEn);

Ejercicio 4º- Visualizar el (los) nombre(s) y el número de casas que tiene el (los) que más. . Los pasos podrían ser:

• obtener el número de pisos que tiene cada señor.( contar –count()- las casas agrupadas por señor)

• obtener el número máximo de los obtenidos anteriormente – función agregada MAX()-

DNI Ncasas11111 222222 333333 344444 1

• seleccionar el nombre de aquellos señores que el número de casas que tiene ( obtenido en el punto 1º) coincida con el máximo ( obtenido en el punto 2º).

Ejercicio 5º- Visualizar el(los) nombre(s) del (los) que tienen el mayor número de casas junto a las ciudades donde las poseen.

Ejercicio 6º - Obtener el (los) nombre(s) de ciudade(s) donde más casas hay, y el número de ellas.

Ejercicio 7º - Nombre (s) de ciudad(es) donde más casas hay, y el nombre de sus propietarios.

Nombre TOTALdos 3tres 3

nombre NombreCiudaddos Salamanca

dos Zamorados Zamoratres Salamancatres Salamancatres Salamanca

NombreCiudad PisosEnCiudadSalamanca 4Zamora 4

NombreCiudad nombreSalamanca dosSalamanca tresSalamanca tresSalamanca tresZamora dosZamora dosZamora unoZamora uno

Ejercicios de SQL (hoja 6ª)

Partimos de la BD MSLibros, con las siguientes tablas:

Se quieren realizar las siguientes consultas:

1ª - conAutoresDelCuadroCombinado

con dos campos: IDAutor,y NombreA con el formato “apellidos,nombre,inicial” de todos los autores que haya en tblAutores, ordenados por NombreA.

SELECT IDAUTOR, APELLIDOS&"," & NOMBRE & INICIALNOMBRE AS NOMBREC

FROM tblAutores;

2ª - conLibrosDelCuadroCombinado

con tres campos: NúmeroISBN, Título, NúmeroEdición de tblLibros, ordenados por títulos y número de edición.

SELECT NúmeroISBN, Título, NúmeroEdición

FROM tblLibros

ORDER BY Título, NúmeroEdición

3ª - cEjempAlgúnLibro

Nombre de autores que hayan escrito algún libro.

SELECT DISTINCT IDAUTOR, Nombre+ Apellidos

FROM tblAutores INNER JOIN tblAutorLibro ON tblAutores.IDAutor = tblAutorLibro.IDAutor

WHERE IDAutor IN (SELECT DISTINCT IDAutor FROM tblAutorLibro)

4ª - cEjempAutorYNumeroDeLibros

Nombres de autores y número de libros que han escrito ordenados de mayor a menor nº de libros

SELECT nombre, apellidos, nlibros FROM tblautores INNER JOIN [SELECT idautor,count(*) as nlibros

FROM tblAutorlibro

GROUP BY IDAUTOR]. AS aux ON tblautores.idautor=aux.idautor

ORDER BY nlibros DESC;

5ª - cEjempAutorSinLibro.

Nombres de Autores que no hayan escrito ningún libro.

SELECT *

FROM TBLAUTORES

WHERE IDAUTOR NOT IN

(SELECT DISTINCT IDAUTOR

FROM TBLAUTORLIBRO);

SELECT *

FROM TBLAUTORES

LEFT JOIN TBLAUTORLIBRO ON TBLAUTORES.IDAUTOR=TBLAUTORLIBRO.IDAUTOR

WHERE tblautorlibro.NúmeroISBN is NULL

6ª - conEjempLibrosYAutores

Consulta que informe acerca de los libros y sus autores con todos los campos de tblLibros (tblLibros.*) y todos los campos de tblAutores.

SELECT *

FROM tblLibros INNER JOIN (tblAutores INNER JOIN tblAutorLibro ON tblAutores.IDAutor = tblAutorLibro.IDAutor) ON tblLibros.NúmeroISBN = tblAutorLibro.NúmeroISBN;

7ª - conAutoresLibros

Consulta que obtenga el ISBN, Título,NúmSeq, Apellidos ,Nombre e Inicial, de todos los libros ordenados por ISBN, y dentro de él por número de secuencia (NúmSeq= orden de autor)

8ª - conAutoresOrdenadosConsulta con todos los campos de tblAutores, ordenados por Apellidos.

9ª - conLibrosAutorConsulta con ISBN, IDAutor, NumSeq, Título del libro y Número de Edición Ordenada por número ISBN.

10ª conLibrosOrdenados

Todos los campos de tblLibros, ordenados por Título y Número de Edición.


Seguimos con MSLIBROS y con las tablas siguientes:

Las consultas a confeccionar son:

1º - conCategorías

Consulta que obtenga: IDCategoría, Categoría y NombreClase .

SELECT IDCategoría, NombreClase, CategoríaFROM tblClases INNER JOIN tblCategorías ON tblClases.IDClase = tblCategorías.IDClase;

2 º - conCategoríasDelCuadroCombinado

Igual a la 1ª pero el orden de campos es el siguiente: Categoría, NombreClase e IDCategoría, ordenada por Categoría y por Nombre de Clase.

SELECT Categoría, NombreClase, IDCategoría

FROM tblClases INNER JOIN tblCategorías ON tblClases.IDClase = tblCategorías.IDClase

ORDER BY Categoría, NombreClase

3º - conCategoríasAutor

Cada Autor tiene varios libros, cada libro puede ser de varias categorías. Se quiere una consulta que obtenga para cada autor las categorías de libros que tiene. Basta con IDAutor y IDCategoria.

SELECT tblAutores.IDAutor, tblAutores.Nombre, tblCategorías.IDCategoría

FROM (tblLibros INNER JOIN (tblAutores INNER JOIN tblAutorLibro ON tblAutores.IDAutor = tblAutorLibro.IDAutor) ON tblLibros.NúmeroISBN = tblAutorLibro.NúmeroISBN) INNER JOIN (tblCategorías INNER JOIN tblCategoríasLibro ON tblCategorías.IDCategoría = tblCategoríasLibro.IDCategoría) ON tblLibros.NúmeroISBN = tblCategoríasLibro.NúmeroISBN;

4º - conEjempCategoríasAutor

Igual que la 3º, pero con nombre de autor, título del libro, nombre de la categoría y de la clase de libro; ordenado por autores y por categoría.

SELECT tblAutores.Nombre, tblLibros.Título, tblCategorías.Categoría, tblClases.NombreClaseFROM (tblLibros INNER JOIN (tblAutores INNER JOIN tblAutorLibro ON tblAutores.IDAutor = tblAutorLibro.IDAutor) ON tblLibros.NúmeroISBN = tblAutorLibro.NúmeroISBN) INNER JOIN (tblClases INNER JOIN (tblCategorías INNER JOIN tblCategoríasLibro ON tblCategorías.IDCategoría = tblCategoríasLibro.IDCategoría) ON tblClases.IDClase = tblCategorías.IDClase) ON tblLibros.NúmeroISBN = tblCategoríasLibro.NúmeroISBNORDER BY tblAutores.Nombre, tblCategorías.Categoría

5º - conCategoríasLibro

Para cada referencia de libro que aparece en tblCategoríasLibros, consulta que contiene númeroISBN, IDCategoria, NombreClase y Categoría.

SELECT tblLibros.NúmeroISBN, tblCategorías.IDCategoría, tblClases.NombreClase, tblCategorías.CategoríaFROM tblLibros INNER JOIN (tblClases INNER JOIN (tblCategorías INNER JOIN tblCategoríasLibro ON tblCategorías.IDCategoría = tblCategoríasLibro.IDCategoría) ON tblClases.IDClase = tblCategorías.IDClase) ON tblLibros.NúmeroISBN = tblCategoríasLibro.NúmeroISBN;

6º - cEjempCategoríasLibroMS-DOS

Consulta que obtenga ISBN y Título de los libros catalogados como IDCategoría= 25 (”MS-DOS”)

SELECT tblLibros.NúmeroISBN, tblLibros.Título

FROM tblLibros INNER JOIN (tblCategorías INNER JOIN tblCategoríasLibro ON tblCategorías.IDCategoría = tblCategoríasLibro.IDCategoría) ON tblLibros.NúmeroISBN = tblCategoríasLibro.NúmeroISBN

WHERE tblCategorías.IDCategoría=25

7º - cEjempClasesLibrosS-O

Consulta que obtenga ISBN y Título de los libros catalogados con IDClase=1 (”Sistema operativo”)

SELECT tblLibros.NúmeroISBN, tblLibros.Título, tblClases.IDClase

FROM tblClases INNER JOIN (tblCategorías INNER JOIN (tblLibros INNER JOIN tblCategoríasLibro ON tblLibros.NúmeroISBN = tblCategoríasLibro.NúmeroISBN) ON tblCategorías.IDCategoría = tblCategoríasLibro.IDCategoría) ON tblClases.IDClase = tblCategorías.IDClase

WHERE tblClases.IDClase=1

8º - cEjempCategoríasLibroACCESS

Consulta que obtenga ISBN , Título y Autor (es) de los libros catalogados como IDCategoría=4 (”Microsoft Access”).

SELECT tblLibros.NúmeroISBN, tblLibros.Título, tblAutores.Nombre

FROM (tblLibros INNER JOIN (tblAutores INNER JOIN tblAutorLibro ON tblAutores.IDAutor = tblAutorLibro.IDAutor) ON tblLibros.NúmeroISBN = tblAutorLibro.NúmeroISBN) INNER JOIN (tblCategorías INNER JOIN tblCategoríasLibro ON tblCategorías.IDCategoría = tblCategoríasLibro.IDCategoría) ON tblLibros.NúmeroISBN = tblCategoríasLibro.NúmeroISBN


9º - cEjempCategoríasLibroVisualBasic

Consulta que obtenga Títulos de libros catalogados como IDCategoría= 8 (”Visual Basic”)

SELECT tblLibros.Título, tblCategorías.IDcategoría

FROM tblLibros INNER JOIN (tblCategorías INNER JOIN tblCategoríasLibro ON tblCategorías.IDCategoría = tblCategoríasLibro.IDCategoría) ON tblLibros.NúmeroISBN = tblCategoríasLibro.NúmeroISBN


10º - CejempTodasCategoríasLibrosVisualBasic

Para cada libro catalogado con categoría “Visual Basic”, visualizar las demás categorías que tiene el libro.


Dentro de MSLibros , vamos a trabajar con las siguientes tablas:

Realizar las siguientes consultas:

1º - conClientesdelCuadroCombinado

De tblClientes, IDClientes,NombreC( apellidos,”,”,nombre y segundo nombre( la incial) como en conAutoresdelCuadroCombinado) , Ciudad, ordenado por NombreC.

SELECT ciudad

FROM tblClientes

ORDER BY ciudad

2º - conLibreríasdelCuadroCombinado

De tblLibrerías, los campos IDLibrería, NombreLibrería, y el tercer campo la unión del texto de la Ciudad y Estado separados por coma ( ej.: “Berkeley, CA”) ordenadas por: EstadoOProvincia,Ciudad y nombre de librería

SELECT IDLibrería, NombreLibrería, CIUDAD+","+ESTADOOPROVINCIA

FROM tblLibrerías

ORDER BY ESTADOOPROVINCIA, CIUDAD, NOMBRELIBRERÍA

3º - conClientesOrdenados

Todos los campos de tblClientes, ordenados por Apellidos,Nombre,SegundoNombre.

4º - conLibreríasOrdenadas

Todos los campos de tblLibrerías ordenados por CódigoPostal, y dentro de él por Nombre de Librería.

5º - cEjempLibrosPedidosPor cada línea de detalle de tblDetallesPedidos, obtener una línea de consulta con el contenido como el siguiente: ( debe estar ordenado por IdPedido)

SELECT tblDetallesPedido.IDPedido, tblPedidos.IDCliente, tblLibros.título, tblDetallesPedido.cantidadFROM tblLibros INNER JOIN (tblPedidos INNER JOIN tblDetallesPedido ON tblPedidos.IDPedido = tblDetallesPedido.IDPedido) ON tblLibros.NúmeroISBN = tblDetallesPedido.NúmeroISBNORDER BY tblDetallesPedido.IDPedido;

ID Pedido

Cliente Título Cantidad1 7 Running Microsoft® PowerPoint®

for Windows® 9511

1 7 Microsoft® Excel 97 Step by Step 91 7 Understanding Groupware in the

Enterprise8

1 7 Field Guide to Microsoft® Access 2 for Windows®

22 1 Running Microsoft® Windows®

984

6º - cEjempImporteLibrosPedidosIgual al anterior pero teniendo en cuenta que IMPORTE= cantidad*preciosug

DTO=IMPORTE*descuento COSTE=IMPORTE-DTO

SELECT tblDetallesPedido.IDPedido, tblPedidos.IDCliente, tblLibros.título, tblDetallesPedido.cantidad, tblLibros.PrecioSug AS PRECIO, tblLibros.PrecioSug*tblDetallesPedido.Cantidad AS IMPORTE, IMPORTE*tblDetallesPedido.Descuento AS DTO, IMPORTE-DTO AS COSTEFROM tblLibros INNER JOIN (tblPedidos INNER JOIN tblDetallesPedido ON tblPedidos.IDPedido = tblDetallesPedido.IDPedido) ON tblLibros.NúmeroISBN = tblDetallesPedido.NúmeroISBNORDER BY tblDetallesPedido.IDPedido;

7 º - cEjempImportesporPedido

con el total del importe de cada pedido

SELECT tblPedidos.IDPedido, SUM(COSTE) AS TOTAL

FROM tblPedidos INNER JOIN cEjempImporteLibrosPedidos ON tblPedidos.IDPedido = cEjempImporteLibrosPedidos.IDPedido

GROUP BY tblPedidos.IDPedido

SELECT tblPedidos.IDPedido, tblPedidos.FechaPedido, tblPedidos.IDCliente, tblPedidos.CiudadEnvío, TOTAL

FROM tblPedidos INNER JOIN subCejemImportesporPedido ON tblPedidos.IDPedido = subCejemImportesporPedido.IDPedido;

ID Pedi

Client

Título Cantidad

Precio IMPORTE DTO COSTE1 7 Running Microsoft® PowerPoint®

for Windows® 9511 25 275 27,5 247,5

1 7 Microsoft® Excel 97 Step by Step 9 30 270 13,5 256,51 7 Understanding Groupware in the

Enterprise8 20 160 8 152

1 7 Field Guide to Microsoft® Access 2 for Windows®

2 10 20 0 202 1 Running Microsoft® Windows®

984 40 160 0 160

ID Pedido

Fecha Pedido Cliente Ciudad Envío TOTAL1 01/01/99 7 London 6762 01/01/99 1 Seattle 6563 01/01/99 8 Bellevue 436,25

Ejercicios de SQL (hoja 9ª )

1º cEjempMayorPedidoPorCódigoPostal ( código Postal de Pedidos)

Partiendo de cEjempImportesporPedido Obtener el mayor importe neto de cada código postal.

¿y si además se quisiera el IDPedido de ese importe máximo?

SELECT tblPedidos.CódigoPostalEnvío, MAX(TOTAL) AS MaxTotaldePedido

FROM tblPedidos INNER JOIN cEjempImportesporPedido ON tblPedidos.IDPedido = cEjempImportesporPedido.IDPedido

GROUP BY tblPedidos.CódigoPostalEnvío

2º cEjempPromedioCiudades

Partiendo de cEjempImportesporPedido, nombre de ciudades con el promedio de ventas.

SELECT tblPedidos.CiudadEnvío, AVG(TOTAL) AS MediaCiudad

FROM cEjempImportesporPedido INNER JOIN tblPedidos ON cEjempImportesporPedido.IDPedido = tblPedidos.IDPedido

GROUP BY tblPedidos.CiudadEnvío

3º cEjempPromedioCiudadesMenosDeMedia

Partiendo de cEjempImportesporPedido, localizar las ciudades en las que el promedio de compras de los clientes es inferior al promedio de compras realizadas por todos los clientes.

SELECT AVG(TOTAL) AS MEDIATOTAL

FROM tblPedidos INNER JOIN cEjempImportesporPedido ON tblPedidos.IDPedido = cEjempImportesporPedido.IDPedido;

SELECT cEjempPromedioCiudades.CiudadEnvío, cEjempPromedioCiudades.MediaCiudad

FROM cEjempPromedioCiudades, subcEjempPromedioCiudadesMenosDeMedi

WHERE MEDIACIUDAD<MEDIATOTAL;

4º cEjempPedidosenMes=6

Para cada día del mes 6 ( las facturas están entre 1/6/99 y 1/7/99) visualizar la suma de los pedidos de esos días. ( consultar ayuda de funciones de fechas: date(), year(),month(), day() )

SELECT DAY(tblPedidos.FechaPedido) AS DiaMes_6, SUM(TOTAL) AS TotalDia


WHERE MONTH(tblPedidos.FechaPedido)=6

GROUP BY DAY(tblPedidos.FechaPedido)

5º cEjempMaxPedidosWA<2200

Obtener la media y el importe del pedido con cantidad máxima de cada uno de los meses , de los clientes del estado de Washington EstadoOProvincia=”WA”, con la condición de que la cantidad máxima del pedido se encuentre por debajo de 2200.

( para obtener el mes : Month(fechaPedido) )

SELECT MONTH(tblPedidos.FechaPedido) AS MES, AVG(TOTAL) AS MediaMes, MAX(TOTAL) AS MaximoDelMes


WHERE tblPedidos.EstadoOProvinciaEnvío="WA"

GROUP BY MONTH(tblPedidos.FechaPedido)

HAVING (MAX(TOTAL)<2200)

6º -Nombre del Cliente, ciudad del Cliente, ciudad del Destino y cantidad del pedido con máximo importe.

SELECT TOP 1 *

FROM subCejemImportesporPedido;

SELECT tblClientes.Nombre, tblClientes.Ciudad, tblPedidos.CiudadEnvío, TOTAL

FROM [MAYOR PEDIDO] INNER JOIN (tblClientes INNER JOIN tblPedidos ON tblClientes.IDCliente = tblPedidos.IDCliente) ON [MAYOR PEDIDO].IDPedido = tblPedidos.IDPedido;

7º - De cada mes el nº de pedidos realizados y el importe total de los pedidos realizados en ese mes.

SELECT tblPedidos.IDPedido, tblPedidos.FechaPedido, TOTAL

FROM tblPedidos INNER JOIN cEjempImportesporPedido ON tblPedidos.IDPedido = cEjempImportesporPedido.IDPedido;

SELECT MONTH(FECHAPEDIDO) AS MES, COUNT(*)AS NPEDIDOS, SUM(TOTAL) AS TOTALMES

FROM SUBHOJA9_7

GROUP BY MONTH(FECHAPEDIDO)

8º - De cada mes, el nombre del cliente que más:

• Número de libros ha comprado• Nº de pedidos ha realizado• Más compras ( suma de sus pedidos) ha realizado

aulaclic

Documents