Realización de consultas. Consultas complejas I

Se dispone de las siguientes tablas con los siguientes datos:

TABLA FABRICANTE

ID_Fab  Nombre                  País

1            Oracle                     Estados Unidos

2            Microsoft                 Estados Unidos

3             IBM                        Estados Unidos

4             Dinamic                  España

5            Borland                   Estados Unidos

6            Symantec               Estados Unidos

TABLA PROGRAMA

Código                Nombre                       Versión

1                          Application Server         9i

2                          Database                       8i

3                          Database                       9i

4                          Database                       10g

5                          Developer                       6i

6                          Access                            97

7                          Access                            2000

8                          Access                            XP

9                          Windows                          98

10                        Windows                          XP Professional

11                        Windows                          XP Home Edition

12                        Windows                          2003 Server

13                        Norton Internet Security   2004

14                        Freddy Hardest                 -

15                        Paradox                            2

16                        C++ Builder                      55

17                        DB/2                                 20

18                        OS/2                                10

19                        JBuilder                            X

20                        La prisión                        10

TABLA COMERCIO

CIF                      Nombre                     Ciudad

1                         El Corte Inglés            Sevilla

2                         El Corte Inglés            Madrid

3                         Jump                           Valencia

4                         Centro Mail                 Sevilla

5                         FNAC                         Barcelona

TABLA CLIENTE

DNI               Nombre                 Edad

1                   Pepe Pérez            45

2                   Juan González       45

3                   María Gómez         33

4                   Javier Casado        18

5                   Nuria Sánchez        29

6                   Antonio Navarro      58

TABLA DESARROLLA

ID_FAB Código

1            1

1            2

1            3

1            4

1            5

2            6

2            7

2            8

2            9

2           10

2           11

2           12

6           13

4           14

5           15

5           16

3           17

3           18

5           19

4           20

TABLA DISTRIBUYE

cif codigo cantidad

1   1           10

1   2           11

1   6            5

1   7            3

1   10          5

1   13          7

2   1            6

2   2            6

2   6            4

2   7            7

3   10          8

3   13          5

4   14          3

4   20          6

5   15          8

5   16          2

5   17          3

5   19          6

5   8            8

TABLA REGISTRA

cif         dni          codigo      medio

1           1              1                Internet

1           3              4                Tarjeta postal

4           2             10               Teléfono

4           1             10               Tarjeta postal

5           2             12               Internet

2           4             15               Internet

Las relaciones entre estas tablas son las siguientes.

1.- Averigua el DNI de todos los clientes:

SELECT dni FROM `cliente` ;

2 Consulta todos los datos de todos los programas: 

SELECT * FROM `programa`  ;

3 Obtén un listado con los nombres de todos los programas:

SELECT nombre FROM programa ;

4 Genera una lista con todos los comercios: 

SELECT * FROM comercio ;

5 Genera una lista de las ciudades con establecimientos donde se venden programas, sin que aparezcan valores duplicados (utiliza DISTINCT): 

SELECT DISTINCT ciudad FROM comercio, distribuye WHERE comercio.cif = distribuye.cif  ;

6 Obtén una lista con los nombres de programas, sin que aparezcan valores duplicados (utiliza DISTINCT) :

SELECT DISTINCT nombre FROM programa ;

7 Obtén el DNI más 4 de todos los clientes:

 SELECT dni+4 FROM cliente ;

  

8 Haz un listado con los códigos de los programas multiplicados por 7: 

SELECT codigo * 7 FROM programa ;

9 ¿Cuáles son los programas cuyo código es inferior o igual a 10?

 SELECT * FROM programa WHERE codigo <= 10 ;

10 ¿Cuál es el programa cuyo código es 11?

 SELECT * FROM programa WHERE codigo = 11 ;

11 ¿Qué fabricantes son de Estados Unidos?

 SELECT * FROM fabricante WHERE pais = “Estados Unidos” ;

12 ¿Cuáles son los fabricantes no españoles? Utilizar el operador IN.

SELECT * FROM fabricante WHERE pais NOT IN (“España”) ;

13 Obtén un listado con los códigos de las distintas versiones de Windows: 

SELECT version FROM programa WHERE nombre = “Windows” ;

14 ¿En qué ciudades comercializa programas El Corte Inglés? 

SELECT ciudad FROM comercio WHERE nombre = “El Corte Inglés” ;

15 ¿Qué otros comercios hay, además de El Corte Inglés? Utilizar el operador IN.

SELECT nombre FROM comercio WHERE nombre NOT IN (“El Corte Inglés”) ;

16 Genera una lista con los códigos de las distintas versiones de Windows y Access. Utilizar el operador IN.

 SELECT codigo FROM programa WHERE nombre IN (“Access”, “Windows”) ;

17 Obtén un listado que incluya los nombres de los clientes de edades comprendidas entre 10 y 25 y de los mayores de 50 años. Da una solución con BETWEEN y otra sin BETWEEN:

 SELECT nombre FROM cliente WHERE (edad BETWEEN 10 AND 25) OR edad >= 50 ;

18 Saca un listado con los comercios de Sevilla y Madrid. No se admiten valores duplicados:

 SELECT DISTINCT nombre FROM comercio WHERE ciudad IN (“Sevilla”, “Madrid”) ;

19 ¿Qué clientes terminan su nombre en la letra “o”?

SELECT * FROM cliente WHERE nombre  LIKE “%o” ;

20 ¿Qué clientes terminan su nombre en la letra “o” y, además, son mayores de 30 años? 

SELECT * FROM cliente WHERE nombre LIKE “%O” AND edad > 30 ;

21 Obtén un listado en el que aparezcan los programas cuya versión finalice por una letra i, o cuyo nombre comience por una A o por una W:

 SELECT * FROM programa WHERE version LIKE “%i” OR nombre LIKE “A%” OR nombre LIKE “W%” ;

22 Obtén un listado en el que aparezcan los programas cuya versión finalice por una letra i, o cuyo nombre comience por una A y termine por una S.

 SELECT nombre, version FROM programa

WHERE version LIKE "%i"

OR nombre LIKE "A%S" ;

23 Obtén un listado en el que aparezcan los programas cuya versión finalice por una letra i, y cuyo nombre no comience por una A.

 SELECT nombre, version FROM programa

WHERE version LIKE "%i"

AND nombre NOT LIKE "A%" ;

24 Obtén una lista de empresas por orden alfabético ascendente.

 SELECT * FROM comercio ORDER BY nombre ASC ;

25 Genera un listado de empresas por orden alfabético descendente: 

SELECT  nombre FROM comercio ORDER BY nombre DESC ;

26 Obtén un listado de programas por orden de versión:

SELECT * FROM programa ORDER BY version ;

27 Genera un listado de los programas que desarrolla Oracle.

 SELECT programa.* FROM fabricante, desarrolla, programa

WHERE fabricante.id_fab=desarrolla.id_fab

AND desarrolla.codigo=programa.codigo

AND fabricante.nombre="ORACLE" ;

28 ¿Qué comercios distribuyen Windows? 

SELECT comercio.nombre FROM comercio, distribuye, programa

WHERE comercio.cif = distribuye.cif

AND distribuye.codigo = programa.codigo

AND programa.nombre = "Windows" ;

29 Genera un listado de los programas y cantidades que  ha distribuido  El

Corte Inglés de Madrid: 

SELECT programa.nombre,distribuye.cantidad FROM comercio, distribuye, programa

WHERE comercio.cif = distribuye.cif

AND distribuye.codigo = programa.codigo

AND comercio.nombre=”El Corte Inglés” ;

30 ¿Qué fabricante ha desarrollado Freddy Hardest?

SELECT fabricante.nombre FROM fabricante,desarrolla,programa

WHERE fabricante.id_fab = desarrolla.id_fab

AND desarrolla.codigo = programa.codigo

AND programa.nombre=”Freddy Hardest” ;

31 Selecciona el nombre de los programas que se registran por Internet: 

SELECT DISTINCT programa.nombre FROM programa, registra

WHERE programa.codigo = registra.codigo

AND medio = “Internet” ;

32 Selecciona el nombre de las personas que se registran por Internet.

SELECT cliente.nombre FROM cliente, registra

WHERE cliente.dni = registra.dni

AND       medio = “INTERNET” ;

33 ¿Qué medios ha utilizado para registrarse Pepe Pérez? 

SELECT medio FROM registra, cliente

WHERE registra.dni = cliente.dni

AND nombre = “Pepe Pérez” ;

34 ¿Qué usuarios han optado por Internet como medio de registro? 

SELECT cliente.nombre FROM cliente, registra

WHERE cliente.dni = registra.dni

AND medio = “Internet” ;

35 ¿Qué programas han recibido registros por tarjeta postal? 

SELECT programa.nombre FROM programa, registra

WHERE programa.codigo = registra.codigo

AND medio = “tarjeta postal” ;

36 ¿En qué localidades se han vendido productos que se han registrado por

Internet? 

SELECT comercio.ciudad FROM comercio, distribuye, programa, registra

WHERE comercio.cif = distribuye.cif

AND distribuye.codigo = programa.codigo

AND programa.codigo =registra.codigo

AND registra.medio = "INTERNET" ;

37 Obtén un listado de los nombres de las personas que se han registrado por

Internet, junto al nombre de los programas para los que ha efectuado el

registro:

SELECT cliente.nombre, programa.nombre FROM cliente, registra, programa

WHERE cliente.dni = registra.dni

AND registra.codigo = programa.codigo

AND registra.medio = "INTERNET"  ;

38 Genera un listado en el que aparezca cada cliente junto al programa que ha

registrado, el medio con el que lo ha hecho y el comercio en el que lo ha

adquirido:

Nota: se ha ampliado la consulta para que se vea que son tuplas distintas (no repetidas)

SELECT cliente.nombre, programa.nombre, programa.version, registra.medio, comercio.nombre, comercio.ciudad FROM cliente, registra, programa, distribuye, comercio

WHERE cliente.dni = registra.dni

AND registra.codigo = programa.codigo

AND programa.codigo = distribuye.codigo

AND distribuye.cif = comercio.cif ;

39 Genera un listado con las ciudades en las que se pueden obtener los productos de Oracle.

SELECT DISTINCT comercio.ciudad FROM comercio, distribuye, programa, desarrolla, fabricante

WHERE comercio.cif=distribuye.cif

AND distribuye.codigo = programa.codigo

AND programa.codigo = desarrolla.codigo

AND desarrolla.id_fab = fabricante.id_fab

AND fabricante.nombre = "ORACLE" ;

40 Obtén el nombre de los usuarios que han registrado Access XP: 

SELECT cliente.nombre FROM cliente, registra, programa

WHERE cliente.dni = registra.dni

AND registra.codigo = programa.codigo

AND programa.nombre = "Access"

AND programa.version = "XP" ;

41 Nombre de aquellos fabricantes cuyo país es el mismo que ʻOracleʼ. (Subconsulta): 

SELECT nombre FROM fabricante

WHERE pais = (SELECT pais FROM fabricante WHERE nombre = "ORACLE" ) ;

42 Nombre de aquellos clientes que tienen la misma edad que Pepe Pérez.(Subconsulta):

SELECT nombre FROM cliente WHERE edad = (SELECT edad FROM cliente WHERE nombre = "Pepe Pérez") ;

43 Genera un listado con los comercios que tienen su sede en la misma ciudad que tiene el comercio ʻFNACʼ. (Subconsulta):

 SELECT nombre FROM comercio WHERE ciudad = (SELECT ciudad FROM comercio WHERE nombre = "FNAC") ;

44 Nombre de aquellos clientes que han registrado un producto de la misma forma que el cliente ʻPepe Pérezʼ. (Subconsulta):

SELECT DISTINCT cliente.nombre FROM cliente, registra

WHERE cliente.dni = registra.dni

AND medio IN

(SELECT DISTINCT medio FROM cliente, registra WHERE cliente.dni = registra.dni AND cliente.nombre = "Pepe Pérez" ) ;

45 Obtener el número de programas que hay en la tabla programas.

SELECT COUNT(codigo) FROM programa ;

46 Calcula el número de clientes cuya edad es mayor de 40 años.

SELECT COUNT(dni) FROM cliente WHERE edad > 40 ;

47 Calcula el número de productos que ha vendido el establecimiento cuyo CIF es 1.

SELECT SUM(cantidad) FROM distribuye WHERE cif = 1 ;

48 Calcula la media de programas que se venden cuyo código es 7. 

SELECT AVG(cantidad) FROM distribuye,programa

WHERE distribuye.codigo = programa.codigo

AND programa.codigo = 7 ;

49 Calcula la mínima cantidad de programas de código 7 que se ha vendido

SELECT MIN(cantidad) FROM distribuye,programa

WHERE distribuye.codigo = programa.codigo

AND programa.codigo = 7 ;

50 Calcula la máxima cantidad de programas de código 7 que se ha vendido.

SELECT MAX(cantidad) FROM distribuye,programa

WHERE distribuye.codigo = programa.codigo

AND programa.codigo = 7 ;

51 ¿En cuántos establecimientos se vende el programa cuyo código es 7? 

SELECT COUNT(comercio.cif) FROM comercio,distribuye

WHERE distribuye.cif = comercio.cif

AND distribuye.codigo = 7 ;

52 Calcular el número de registros que se han realizado por Internet.

SELECT COUNT(dni) FROM registra

WHERE medio="INTERNET" ;

53 Obtener el número total de programas que se han vendido en ʻsevillaʼ.

SELECT SUM(distribuye.cantidad) FROM comercio,distribuye

WHERE comercio.cif=distribuye.cif

AND comercio.ciudad="SEVILLA" ;

54 Calcular el número total de programas que han desarrollado los fabricantes cuyo país es ʻEstados Unidosʼ. 

SELECT COUNT(programa.codigo) FROM fabricante, desarrolla, programa

WHERE fabricante.id_fab=desarrolla.id_fab

AND desarrolla.codigo=programa.codigo

AND fabricante.pais="Estados Unidos" ;

55 Visualiza el nombre de todos los clientes en mayúscula. En el resultado de la consulta debe aparecer también la longitud de la cadena nombre. 

SELECT UPPER(nombre), LENGTH(nombre) FROM cliente ;

56 Con una consulta concatena los campos nombre y versión de la tabla PROGRAMA.

SELECT CONCAT(nombre," ",version) FROM programa ;