TEMA 4. FUNCIONES - - FUNCIONES ARITMETICAS

1.       FUNCIONES ARITMETICAS

a.       FUNCIONES DE VALORES SIMPLES

Las funciones de valores simples son funciones sencillas que trabajan con valores simples, por ejemplo, son valores simples: un número, una variable o una columna de una tabla.

·         ABS(n): Devuelve el valor absoluto de n.

·         CEIL(n): Obtiene el valor entero inmediatamente superior o igual a n.

·         FLOOR(n): Es lo opuesto a CEIL devuelve el valor entero inmediatamente inferior o igual a n.

·         MOD(m,n): Devuelve el resto de m/n.

·         NVL(valor,expresión): Sustituye un valor nulo por otro valor.

·         POWER(m,exponente): Calcula la potencia de un numero.

·         ROUND: (numero,[,m]): Devuelve el valor de un numero redondeado a m decimales.

·         SIGN(valor): Indica el signo del valor.

·         SORT(n): Devuelve la raíz cuadrada de n.

·         TRUNC(numero,[m]): Trunca los números para que tengan un cierto numero de digitos de precisión.

b.      FUNCIONES DE GRUPOS DE VALORES

·         AVG (n): Calcula el valor medio de n ignorando valores nulos.

·         COUNT(*|expresión): Cuenta el numero de veces que la expresión evalua algún dato con valor no nulo.

·         MAX (expresión): Calcula el valor máximo de la expresión.

·         MIN(expresión): Calcula el minimo valor de la expresión.

·         SUM(expresión): Obtiene la suma de valores de la expresión distintos de nulo.

·         VARIANCE(expresión): Obtiene la varianza de los valores de expresión distintos de nulo.

DISTINCT Y ALL, son clausulas que se pueden utilizar en todas las funciones de grupo. DISTINCT hace una selección de filas cuyos valores en la columna especificada no están duplicados.ALL, recoge todas las filas aunque sus valores estén duplicados.

El formato COUNT con estas clausulas seria:

COUNT(*|[DISTINCT|ALL] expresión);

c.       FUNCIONES DE LISTAS

Trabajan sobre un grupo de columnas dentro de una misma fila.

·         GREATEST (valor1,valor2…): Obtiene el valor mayor de la lista.

·         LEAST(valor1,valor2…): Obtiene el valor menor de la lista.

Ejercicios refuerzo TEMA 3.Introduccion a SQL

1.        Con la tabla EMPLE calcula la media de los salarios de los registros del departamento ‘VENTAS’.

SELECT AVG(SALARIO) FROM EMPLE WHERE DEPT_NO=(SELECT DEPT_NO FROM DEPART

WHERE DNOMBRE='VENTAS')

/

2.        Visualiza los empleados del departamento de JIMENEZ.

SELECT * FROM EMPLE WHERE DEPT_NO IN(SELECT DEPT_NO FROM EMPLE WHERE APELLIDO='JIMENEZ')

/

3.        Visualiza los empleados con el mismo oficio que GIL.

SELECT * FROM EMPLE WHERE OFICIO IN(SELECT OFICIO FROM EMPLE WHERE APELLIDO='GIL')

/

4.        ¿Cuántos empleados hay en el departamento de ALONSO con el oficio de JIMENEZ?

SELECT COUNT(*) FROM EMPLE WHERE DEPT_NO=(SELECT DEPT_NO FROM EMPLE WHERE APELLIDO='ALONSO') AND (SELECT OFICIO FROM EMPLE WHERE APELLIDO= 'JIMENEZ')

/

5.        ¿Cuántos empleados hay en el departamento 10? (count(*))

SELECT COUNT(*) FROM EMPLE WHERE DEPT_NO=10

/

6.        Obtener los empleados que tienen una ‘o’ en su oficio en cualquier posición.

SELECT * FROM EMPLE WHERE OFICIO LIKE '%O%'

/

7.        Obtener los empleados que estén en el departamento 10 o en el 20.

SELECT * FROM EMPLE WHERE DEPT_NO IN(10,20)

8.        Obtener los temas que tienen más de 10 ejemplares.

SELECT TEMA FROM LIBRERIA WHERE EJEMPLARES>10

/

9.        ¿Quiénes son los empleados que ganan más en su oficio? (función Max.)

SELECT* FROM EMPLE E WHERE SALARIO=(SELECT MAX(SALARIO) FROM EMPLE WHERE OFICIO=E.OFICIO)

/

10.       ¿Quiénes son los empleados que ganan menos en su departamento? (función Min)

SELECT* FROM EMPLE E WHERE SALARIO=(SELECT MIN(SALARIO) FROM EMPLE WHERE DEPT_NO=E.DEPT_NO)

/

11.      ¿Qué alumnos han suspendido FOL?

SELECT * FROM ALUMNOS,ASIGNATURAS WHERE NOMBRE='FOL' AND DNI IN(SELECT DNI FROM NOTAS WHERE NOTA<5)

/

12.      ¿Qué alumnos han aprobado ‘Aplic. Entornos 4ªGen’?

SELECT*FROM ALUMNOS WHERE DNI =(SELECT DNI FROM NOTAS WHERE NOTA>=5 AND COD=(SELECT COD FROM ASIGNATURAS WHERE NOMBRE=

'Aplic. Entornos 4 Gen'))

/

13.      ¿Qué alumnos están matriculados en 1º?

SELECT * FROM ALUMNOS WHERE DNI =(SELECT DNI FROM NOTAS WHERE COD =(SELECT COD FROM ASIGNATURAS WHERE NOMBRE in ('prog.leng.estr.','sist.informaticos','analisis')))

/

14.      ¿Qué alumnos tienen asignaturas suspensas en 1º?

SELECT * FROM ALUMNOS WHERE DNI IN (SELECT DNI FROM NOTAS WHERE NOTA<5 AND COD IN

(SELECT COD FROM ASIGNATURAS WHERE NOMBRE IN ('ANALISIS','PROG.LENG.ESTRUCT','SIST.INFORMATICOS')))

/

15.      Visualiza los alumnos de Madrid que no tienen ninguna asignatura suspensa. (Observación: ¿Te sirve el ejercicio 12 de la página 73 del libro?)

SELECT * FROM ALUMNOS WHERE POBLA='Madrid' AND DNI IN (SELECT DNI FROM NOTAS WHERE NOTA<5)

/

16.      Busca los alumnos con asignaturas pendientes. Muestra además el nombre de la asignatura.

SELECT* FROM ALUMNOS AL,NOTAS N,ASIGNATURAS ASIG  WHERE AL.DNI=N.DNI AND ASIG.COD=N.COD AND N.NOTA<5

/

17.      Busca los alumnos que no tengan ninguna asignatura pendiente, muestra además todas sus notas, las asignaturas y ordenado por alumno.

SELECT* FROM ALUMNOS A,NOTAS N,ASIGNATURAS ASIG WHERE A.DNI=N.DNI AND ASIG.COD=N.COD AND N.DNI NOT IN(SELECT DISTINCT DNI FROM NOTAS WHERE NOTA<5)

/

18.      Busca las notas de alumnos suspensos de Madrid, su nombre, asignatura y nota.

SELECT * FROM ALUMNOS,NOTAS,ASIGNATURAS ASIG WHERE NOTA<5 AND POBLA='Madrid' AND ALUMNOS.DNI=NOTAS.DNI AND ASIG.COD=NOTAS.COD

/

19.      Obtener los artículos (PK: Articulo, Cod_fabricante, Peso, Categoria) que no tienen ni pedidos ni ventas.

SELECT ARTICULO, COD_FABRICANTE,PESO,CATEGORIA FROM ARTICULOS WHERE Cod_fabricante, Peso, Categoria NOT IN (SELECT Cod_fabricante, Peso, Categoria FROM PEDIDOS) AND Cod_fabricante, Peso, Categoria NOT IN (SELECT Cod_fabricante, Peso, Categoria FROM VENTAS)

/

20.      Obtener los artículos (PK: Articulo, Cod_fabricante, Peso, Categoria) que tienen pedidos y ventas

21.      Busca los artículos de fabricantes que tienen artículos vendidos. (tabla VENTAS)

SELECT ARTICULO,COD_FABRICANTE,PESO,CATEGORIA FROM ARTICULOS WHERE COD_FABRICANTE IN(SELECT COD_FABRICANTE FROM VENTAS)

/

Soluciones ejercicios Tema 3.Case

EJERCICIOS TEMA3.CASE-LIBRO

TABLA EMPLE Y DEPART

1.       Selecciona el apellido, oficio y localidad de los departamentos de aquellos empleados cuyo oficio sea ANALISTA.

SELECT APELLIDO, OFICIO, LOC FROM EMPLE, DEPART

WHERE OFICIO='ANALISTA'

/

2.       Obtén los datos de los empleados cuyo director (columna DIR de la tabla EMPLE) sea ‘CEREZO’

SELECT * FROM EMPLE

WHERE DIR= (SELECT EMP_NO FROM EMPLE WHERE OFICIO='DIRECTOR' AND APELLIDO= 'CEREZO')

/

3.       Obtén los datos de los empleados del departamento de VENTAS.

SELECT*FROM EMPLE

WHERE DEPT_NO= (SELECT DEPT_NO FROM DEPART WHERE DNOMBRE='VENTAS')

/

4.       Obtén los datos de los departamentos que no tengan empleados.

                SELECT * FROM DEPART

WHERE DEPT_NO NOT IN (SELECT DISTINCT DEPT_NO FROM EMPLE)

/

5.       Obtén los datos de los departamentos que tengan empleados.

SELECT*FROM DEPART

WHERE EXISTS (SELECT * FROM EMPLE, DEPART WHERE EMPLE.DEPT_NO=DEPART.DEPT_NO)

6.       Obtén el apellido y el salario de los empleados que superen todos los salarios de los empleados del departamento 20.

SELECT APELLIDO, SALARIO FROM EMPLE

WHERE SALARIO> (SELECT MAX (SALARIO) FROM EMPLE WHERE DEPT_NO=20)

TABLA LIBRERIA

7.      Visualiza el tema, estante y ejemplares de las filas de librería con ejemplares entre 8 y 15.

SELECT * FROM LIBRERIA WHERE EJEMPLARES BETWEEN 8 AND 15

8.      Visualiza las columnas tema, estante y ejemplares de las filas cuyo estante no este comprendido entre la “B” y la “D”.

SELECT TEMA, ESTANTE, EJEMPLARES FROM LIBRERIA

WHERE ESTANTE NOT BETWEEN 'B' AND 'D';

9.      Visualiza con una sola orden select todos los temas de librería cuyo número de ejemplares sea inferior a los que hay en medicina.

SELECT TEMA FROM LIBRERIA

 WHERE EJEMPLARES < (SELECT EJEMPLARES FROM LIBRERIA WHERE TEMA ='MEDICINA')

10.  Visualiza los temas de librería cuyo número de ejemplares no esté entre 15 y 20, ambos incluidos.

SELECT TEMA FROM LIBRERIA WHERE EJEMPLARES NOT BETWEEN 15 AND 20

11.  Visualiza todas las asignaturas que contengan tres letras “o” en su interior y tengan alumnos matriculados de “Madrid”.

SELECT * FROM ASIGNATURAS

WHERE NOMBRE LIKE '%O%' AND COD IN (SELECT COD FROM NOTAS

WHERE DNI IN (SELECT DNI FROM ALUMNOS WHERE POBLA='Madrid'))

12.  Visualiza los nombres de los alumnos de Madrid que tengan alguna asignatura suspensa.

SELECT APENOM FROM ALUMNOS

WHERE POBLA='Madrid' AND DNI IN (SELECT DNI FROM NOTAS WHERE NOTA<5);

13.  Muestra los nombres de alumnos que tengan la misma nota que tiene “Díaz Fernández, María” en FOL en alguna asignatura.

SELECT APENOM FROM ALUMNOS, NOTAS WHERE NOTA IN (SELECT NOTA FROM NOTAS WHERE COD IN

(SELECT COD FROM ASIGNATURAS WHERE NOMBRE='FOL')AND DNI IN (SELECT DNI FROM ALUMNOS

WHERE APENOM='Díaz Fernández, María') AND ALUMNOS.DNI=NOTAS.DNI)

14.  Obtén los datos de las asignaturas que no tengan alumnos.

SELECT*FROM ASIGNATURAS

WHERE COD NOT IN (SELECT COD FROM NOTAS)

/

15.  Obtén el nombre y apellido de los alumnos que tengan nota en la asignatura con código 1.

SELECT APENOM FROM ALUMNOS, NOTAS

WHERE ALUMNOS.DNI=NOTAS.DNI AND COD=1

16.  Obtén el nombre y apellido de los alumnos que no tengan nota en la asignatura con código 1.

SELECT UNIQUE APENOM FROM ALUMNOS

WHERE DNI NOT IN (SELECT DNI FROM NOTAS WHERE COD=1)