SQL

Esquema de HR

Descripción de Tablas

Recuperación de datos mediante la instrucción SQL SELECT

Esquema Básico

Sintaxis:

Select .- Identifica los columnas que se van a desplegar.

From.- identifica la tabla que contiene los campos.

Column/expression.- Selecciona los nombres de las comunas o expresiones.

DISTINCT.- Suprime duplicados.

Alias.- da un nombre diferente a la columna seleccionada

Selección de todos los Campos

Selección de columnas

Utilizando Operaciones matemáticas

Operador Precedente

Usando Alias

Registros Duplicados

Limitación de las filas del Select

Sintaxis:

Where.- Restringe la consulta para filas que cumplen una condición.

Logical Expression.- Esta definida por nombres de columnas, constantes y operador de comparación. En donde se especifica una combinación de una o más expresiones y Operadores booleanos que retornan un valor verdadero, falso o desconocido

Limitación de las filas del Select

Operadores de comparaciónSintaxis:Where expr operador valor

Comparación de Valores

Operador Between

Operador In

Operador Like

Like se usa para Buscar valores dentro de una cadena de caracteres

Usando la condición NULL

Definición de condiciones usando los operadores lógicos

Utilizando el Operador NOT

El Operador NOT se puede utilizar también con otros operadores SQL, tales como: BETWEEN, LIKE, Y NULL. Ejemplo:

Reglas de Precedencia o orden

Usted puede utilizar perentheses para anular reglas de precedencia.

Reglas de Precedencia

La primera condición es que el Job_id es ‘AD_PRES’ y el salario es mayor que $ 15.000

La segunda condición es job_id es ‘SA_REP’

Reglas de Precedencia

Reglas de Precedencia

La primera condición ejecutada es job_id es 'AD_PRES ' OR 'SA_REP‘.

La segunda condición es que el salario>15,000.

Utilizando la clausula order by

Test

Preguntas

Conjunto de Operaciones para obtener una determinada información de la base de datos.R: ConsultasEn el Modelo E-R adjetivo que define una característica de una entidad.R: atributo

Test

Conjunto de Normas que establecen limitaciones del modelo. Lo que el modelo no puede hacer.R: Restricciones

Test

Test

Restricción de datos en consultas básicas de SQL

Restricción de datos

Limitar las filas recuperadas por una consulta:La cláusula WHERE1.- Los operadores de comparación: =, <=, BETWEEN, IN, LIKE.2.- Utilizando las Condiciones lógicas AND, OR y NOT

Restricción de datos

Limitar filas utilizando una selección:

recuperar todos los empleados

en el departamento 90

Restricción de datos

Restringir las filas recuperadas utilizando la cláusula Where:

Donde:Where.-Restringe la consulta a las filas que cumplen una condición.

logical expression.- Compuesta de nombres de columna, constantes y un operador de comparación. Especifica una combinación de una o más expresiones y operadores booleanos y devuelve un valor de Verdad.

Restricción de datos

Restricción de datos

También puede utilizar el operador BETWEEN en valores de carácter:

Ordenar de datos

Ordenar filas utilizando la cláusula ORDER BY:

Ordenar de datos

Clasificar en orden descendente

Clasificación por alias de columna

Ordenar de datos

Clasificación por usar posición numérica de la columna

La clasificación por varias columnas

Funciones

Restricción de datos

Utilizando Funciones

Las funciones :1.- Reciben argumentos y devolver un valor2.- Retorna un resultado por fila3.- Puede modificar el tipo de datos4.- Acepta argumentos que pueden ser una columna o una expresión5.- Se pueden anidar o combinar.

Funciones Consultas SQL

Funciones de carácter

Funciones de carácter

Funciones de carácter

Funciones numéricas

Funciones numéricas

Funciones numéricas

Funciones numéricas

Funciones

Funciones

Select Sum(total) As suma_pedidos From pedidos

Select Sum(cantidad) as suma_articulos From pedidos Where id_articulo=6

Funciones generales

Funciones de la jerarquizaciónFunciones de una sola fila se pueden anidar a cualquier nivel.Las funciones anidadas se evalúan desde el nivel más profundo al nivel menos profundo.

Funciones de la jerarquización

Result1 = SUBSTR (LAST_NAME, 1, 8)

Uso de las funciones del GrupoLas funciones de grupo operan en conjuntos de filas para dar un resultado por grupo.

Tipos de Funciones de Grupo

Tipos de Funciones de Grupo

Sintaxis

Funciones de Grupo

Puede utilizar AVG y SUM para los datos numéricos.

Uso de la función COUNT

COUNT(*) devuelve el número de filas de una tabla:

COUNT (expr) devuelve el número de filas, valores no nulos de la expr

Formatos de la función COUNTLa función COUNT tiene tres formatos:

COUNT (*) devuelve el número de filas de una tabla que satisfacen los criterios de la sentencia SELECT, incluidas las filas duplicadas y las filas que contienen valores nulos en cualquiera de las columnas. Si una cláusula WHERE se incluye en la instrucción SELECT, COUNT (*) devuelve el número de filas que satisfacen la condición en la cláusula WHERE.

Por el contrario, COUNT (expr) devuelve el número de valores no nulos que están en la columna identificada por expr.

COUNT (DISTINCT expr) devuelve el número de valores únicos, no nulos que están en la columna identificada por expr

Formatos de la función COUNT

Funciones de Grupo y Valores Nulos

Las funciones de grupo ignoran los valores nulos en la columna:

Creación de grupos de datos

Creación de grupos de datos

Puede dividir las filas de una tabla en grupos más pequeños mediante el uso de laCláusula GROUP BY.

Creación de grupos de datos

Todas las columnas de la lista SELECT que no están en funciones de grupo deben estar en la cláusula GROUP BY

Creación de grupos de datos

La columna GROUP BY no tiene que estar en la lista SELECT

Creación de grupos de datos

¿Puede utilizar la función de grupo en la cláusula ORDER BY?SELECT department_id, AVG(salary)

FROMemployees

GROUP BY department_id

ORDER BY AVG(salary);

Consultas ilegales en el uso de funciones de grupo

Cualquier columna o expresión en la lista SELECT que no es una función de agregación deben estar en la cláusula GROUP BY

Consultas ilegales en el uso de funciones de grupo

No se puede utilizar la cláusula WHERE para restringir grupos.Se utiliza la cláusula HAVING para restringir grupos

Consultas ilegales en el uso de funciones de grupo

No se puede utilizar la cláusula WHERE para restringir grupos.Se utiliza la cláusula HAVING para restringir grupos

Restringir los resultados del grupo con la Cláusula HAVING

Restringir los resultados del grupo con la Cláusula HAVING

Funciones anidamiento de grupos

Uso de sub consultas

Sintaxis

Puede colocar la sub consulta en una serie de cláusulas SQL, incluyendo las siguientes:

• Clausula WHERE

• Clausula HAVING

• Clausula FROM

En la sintaxis:operador incluye una condición de comparación como: >, =, o IN.

Ejemplo

Uso de las funciones del Grupo en una sub consulta

Clausula HAVING con sub consultas

Uso de operadores SET

Uso de operadores SET

Uso de operadores SET

Operadores de conjunto se combinan los resultados de dos o más consultas de componentes en un solo resultado. Las consultas que contienen los operadores establecidos se llaman consultas compuestas.

Operador UNION

1.- El número de columnas seleccionadas deben ser el mismo en las consultas.2.- Los tipos de datos de las columnas deben estar en el mismo grupo de tipos de datos (como numérico o de caracteres).3.- Los nombres de las columnas no tienen que ser idénticos.

Operador UNION

El operador UNION elimina los registros duplicados. Si los registros que se producen en los empleados y las tablas JOB_HISTORY son idénticos, los registros se muestran sólo una vez. Observe en el resultado que se muestra en la diapositiva que el registro del empleado con la EMPLOYEE_ID 200 aparece dos veces porque la JOB_ID es diferente en cada fila

Operador UNION

Considere el siguiente ejemplo

Operador UNION

Considere el siguiente ejemplo

Operador UNION ALL

Considere el siguiente ejemplo

Operador INTERSECT

Utilice el operador INTERSECT para devolver todas las filas que son comunes a varias consultas.

DirectricesEl número de columnas y los tipos de datos de las columnas que se seleccionan por las sentencias SELECT en las consultas deben ser idénticos en todas las sentencias SELECT utilizadas en la consulta. Los nombres de las columnas, sin embargo, no tienen por qué ser idénticos.Invertir el orden de las tablas intersecadas no altera el resultado.INTERSECT no ignora los valores NULL

Operador INTERSECT

Operador MINUS

Muestra los employees ids de los empleados que no han cambiado sus puestos de trabajo ni una sola vez

El uso de la cláusula ORDER BY en operaciones de conjunto

La cláusula ORDER BY sólo puede aparecer una vez al final de la consulta compuesta.

La cláusula ORDER BY sólo reconoce las columnas de la primera consulta SELECT.

Por defecto, la primera columna de la primera consulta SELECT se utiliza para ordenar la salida en un orden ascendente

Manipulación de Datos

Sentencia INSERT

Añade nuevas filas a una tabla. El formato de la sentencia INSERT es:

Con esta sentencia, únicamente podemos insertar una fila en un instante de tiempo.

Sentencia INSERT

1.- Inserta una nueva fila que contiene los valores para cada columna.2.- Los valores de la lista deben estar en el orden predeterminado de las columnas de la tabla.3.- De forma opcional, se puede definir la lista de las columnas a insertar en la cláusula INSERT.

Sentencia INSERT

Copiar filas de otra tabla

Actualizar datos en una tabla

Update modifica los valores existentes en una tabla. Sintaxis:

Actualiza más de una fila si fuese necesario.

Actualizar datos en una tabla

Actualizar columnas con una sub consulta

Actualice el job_id, salario del empleado 113 con job_id y salario del employee 205

Eliminar filas de una tabla

Test

Test

Usando una sub consulta es equivalente a realizar dos consultas secuenciales y usando el resultado de la primera consulta como los valores de búsqueda en la segunda consultaa.- Verdaderob.- Falso

Test

Usando una sub consulta es equivalente a realizar dos consultas secuenciales y usando el resultado de la primera consulta como los valores de búsqueda en la segunda consultaa.- Verdaderob.- Falso

Test

Tres de los siguientes operadores son válidos para la condición WHERE. ¿Cuáles?a.- >=

b.- !=

d.- IS LIKE

c.- IN BETWEEN

e.- <>

Test

Tres de los siguientes operadores son válidos para la condición WHERE. ¿Cuáles?a.- >=

b.- !=

d.- IS LIKE

c.- IN BETWEEN

e.- <>

Test

Identificar las dos consultas SELECT que se ejecutan con éxito

Test

Identificar las dos consultas SELECT que se ejecutan con éxito

Test

¿Qué hay de malo con esta afirmación?

Test

¿Cuál será el resultado de la siguiente función? ROUND(144.24,-1) a. - 140 b. - 144 c. - 150 d. – 100

Test

¿Cuál será el resultado de la siguiente función? ROUND(144.24,-1) a. - 140 b. - 144 c. - 150 d. – 100

Test

¿Cuál de las siguientes afirmaciones es verdadera con respecto a la función COUNT? a.- COUNT (*) Cuenta los valores duplicados y los valores NULL en columnas de cualquier tipo de datos.

b.- La Función COUNT no puede trabajar con tipos de datos de fecha.

c.- COUNT (DISTINCT job_id) devuelve el número de filas con exclusión de las filas que contienen los duplicados y los valores NULL en la columna job_id.

d.- Una instrucción SELECT utilizando la función CONTAR con una palabra clave DISTINCT y no se puede incluir una cláusula WHERE.

Test

¿Cuál de las siguientes afirmaciones es verdadera con respecto a la función COUNT? a.- COUNT (*) Cuenta los valores duplicados y los valores NULL en columnas de cualquier tipo de datos.

b.- La Función COUNT no puede trabajar con tipos de datos de fecha.

c.- COUNT (DISTINCT job_id) devuelve el número de filas con exclusión de las filas que contienen los duplicados y los valores NULL en la columna job_id.

d.- Una instrucción SELECT utilizando la función CONTAR con una palabra clave DISTINCT y no se puede incluir una cláusula WHERE.

Test

¿Cuál de las siguientes opciones muestra relaciones correctas entre el criterio de búsqueda de datos, dentro de una base de datos utilizando la cláusula WHERE y la función de cada uno?

1.- 1C, 2A, 3B

2.- 1A, 2B, 3D

3.- 1D, 2A, 3C

4.- 1B, 2C,3A

Test

¿Cuál de las siguientes opciones muestra relaciones correctas entre el criterio de búsqueda de datos, dentro de una base de datos utilizando la cláusula WHERE y la función de cada uno?

1.- 1C, 2A, 3B

2.- 1A, 2B, 3D

3.- 1D, 2A, 3C

4.- 1B, 2C,3A

Test

Identifique dos conceptos para las funciones de grupo y la cláusula GROUP BY.a.Ud. no puede utilizar un alias de columna en la cláusula GROUP BY.

b.La columna GROUP BY debe estar en la cláusula SELECT.

c.Mediante el uso de una cláusula WHERE, puede excluir filas antes de dividirse en grupos.

d.La cláusula GROUP BY asegura el orden de los registros y/o conjunto de resultados.

e.Si incluye una función de grupo en una cláusula SELECT, debe incluir una cláusula GROUP BY.

Test

Identifique dos conceptos para las funciones de grupo y la cláusula GROUP BY.a.Ud. no puede utilizar un alias de columna en la cláusula GROUP BY.

b.La columna GROUP BY debe estar en la cláusula SELECT.

c.Mediante el uso de una cláusula WHERE, puede excluir filas antes de dividirse en grupos.

d.La cláusula GROUP BY asegura el orden de los registros y/o conjunto de resultados.

e.Si incluye una función de grupo en una cláusula SELECT, debe incluir una cláusula GROUP BY.

Test

Suponiendo que los apellidos de los empleados están bien registrados en la tabla, ¿cuál será el resultado de la siguiente consulta?

SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'smith';

a.- Puede mostrar los detalles del empleado con el último nombre que Smith

b.-Le dará ningún resultado.

c.-Le dará los detalles para que el empleado tenga el apellido que 'Smith' en minúsculas.

d.-Le dará los detalles del empleado tenga el apellido 'Smith'.

Test

Suponiendo que los apellidos de los empleados están bien registrados en la tabla, ¿cuál será el resultado de la siguiente consulta?

SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'smith';

a.- Puede mostrar los detalles del empleado con el último nombre que Smith

b.-Le dará ningún resultado.

c.-Le dará los detalles para que el empleado tenga el apellido que 'Smith' en minúsculas.

d.-Le dará los detalles del empleado tenga el apellido 'Smith'.

Test

Usted necesita saber cuales son los empleados que pertenecen al departamento de 'Jessica Butcher' y tienen sueldo mayor que el salario de 'Jessica Butcher', ella tiene un ID de empleado número 40. ¿Cuál de las siguientes consultas va a funcionar? a.- SELECT first_name, last_name

FROM employees

WHERE last_name = 'Butcher'

AND first_name = 'Jessica'

AND salary > 10000;

Test

Usted necesita saber cuales son los empleados que pertenecen al departamento de 'Jessica Butcher' y tienen sueldo mayor que el salario de 'Jessica Butcher', ella tiene un ID de empleado número 40. ¿Cuál de las siguientes consultas va a funcionar? b.- SELECT first_name, last_name

FROM employees

WHERE department = 100;

Test

Usted necesita saber cuales son los empleados que pertenecen al departamento de 'Jessica Butcher' y tienen sueldo mayor que el salario de 'Jessica Butcher', ella tiene un ID de empleado número 40. ¿Cuál de las siguientes consultas va a funcionar? c.- SELECT first_name, last_name FROM employees

WHERE department = (SELECT department FROM employees

WHERE first_name = 'Jessica‘ AND last_name = 'Butcher'

AND employee_id = 40)

AND salary > (SELECT salary FROM employees

WHERE first_name = 'Jessica‘ AND last_name = 'Butcher'

AND employee_id = 40);

Test

Identifique dos ideas de los operadores establecidos.

a. Las expresiones en las consultas SELECT deben coincidir en número.b. Los paréntesis no puede ser utilizado para alterar la secuencia de ejecución.c. El tipo de datos de cada columna en la segunda consulta debe coincidir con el tipo de datos de su columna correspondiente de la primera consulta.d. La cláusula ORDER BY sólo se puede utilizar una vez en una consulta compuesta, a menos que se utilice el operador UNION ALL.

Test

Identifique dos ideas de los operadores establecidos.

a. Las expresiones en las consultas SELECT deben coincidir en número.b. Los paréntesis no puede ser utilizado para alterar la secuencia de ejecución.c. El tipo de datos de cada columna en la segunda consulta debe coincidir con el tipo de datos de su columna correspondiente de la primera consulta.d. La cláusula ORDER BY sólo se puede utilizar una sola vez en una consulta compuesta, a menos que se utilice el operador UNION ALL.

Test

Dentro de base de datos, las sub consultas con SQL permiten:

a.- Utilizar los resultados de una consulta como parte de otra.

b.- Consultar información que se encuentra en más de tres tablas.

c.- Consultar información comparando campos que tienen el mismo tipo de dato.

c.- Ejecutar las consultas de manera más rápida

Test

Dentro de base de datos, las sub consultas con SQL permiten:

a.- Utilizar los resultados de una consulta como parte de otra.

b.- Consultar información que se encuentra en más de tres tablas.

c.- Consultar información comparando campos que tienen el mismo tipo de dato.

c.- Ejecutar las consultas de manera más rápida

Ejercicio 1

Listar el Apellido y el sueldo de los empleados que no ganan entre 4000 y 15000.

Select last_name, salaryfrom employeeswhere salary not between 4000 and 15000

Ejercicio 2

Listar el apellido y el sueldo de los empleados que pertenecen a los departamentos 20 o 50, que ganan entre 4000 y 15000.

Select last_name, salaryfrom employeeswhere salary between 4000 and 15000 and department_id in (20, 50)

Ejercicio 3

Listar el apellido de los empleados que tengan un ‘a’ en la segunda letra del apellido.

Select last_namefrom employeesWhere last_name like ‘_a%’

Ejercicio 4

Listar el apellido y sueldo de los empleados pertenecientes a departamentos radicados en ‘Seattle’.Select last_name, salaryfrom employeesWhere department_id in (select department_id from departments d, locations l where d.location_id=l.location_id and city=‘Seattle’)

Ejercicio 5

Listar cod departamento cuya media de salarios es inferior a 7000 (Contando solo los empleados que ganan menos de 4000)Select department_id, avg(salary)from employeesWhere salary < =4000Group by department_idHaving avg(salary)<7000

Ejercicio 6

Encontrar el nombre y salario de los empleados que ganan menos de 2000. De este conjunto, seleccionar los que tengan una ‘a’ en el nombre. Select *from (select last_name, salary from employees where salary > 2000)Where last_name like ‘%a%’

Ejercicio 7

Seleccionar el ID empleado, apellido y sueldo de los empleados que ganan más que la media de la empresa. Ordenar los registros por el salarioSelect employee_id, last_name, salaryfrom employeesWhere salary > (select avg(salary) from employees)Order by salary

Ejercicio 8

Seleccionar el ID empleado, apellido, de todos los colegas de empleados que tengan una ‘u’ en su apellido. (Colega: persona que trabaja en el mismo departamento.)Select employee_id, last_namefrom employeesWhere department_id in (select department_id from employees where last_name like ‘%u%’)

Ejercicio 8

Seleccionar ID empleado, apellido, sueldo de los colegas de empleados que tengan una ‘u’ en su apellido, y que ganen sueldos (los colegas) mayor que la media de sueldos de la empresa.Select employee_id, last_name, salaryfrom employeesWhere department_id in (select department_id from employees where last_name like ‘%u%’) and salary > (select avg(salary) from employees)

Creación de Tablas

Sentencia: CREATE TABLE, su formato:

Donde la definición de columna es: