Aplicación práctica para bases de datos relacionales y SQL: uso de declaraciones básicas en SQL

Descripción general de la lección y conocimientos necesarios

En esta lección, escribirá declaraciones que recuperen datos de bases de datos SQL. Se requieren los siguientes conocimientos y habilidades para completar con éxito esta lección:

• Conceptos de bases de datos relacionales (relaciones, atributos, claves primarias, claves externas)
• Sentencias SQL y su estructura, por ejemplo, cómo se compone una sentencia SELECT
• Uso de MySQL para trabajar con bases de datos y mostrar datos

Los datos de muestra utilizados para esta lección se encuentran en la base de datos de modelos clásicos , instalada en esta lección Aplicación práctica para Introducción a SQL: Instalación de datos de muestra para MySQL.

Código de programa

Abra MySQL Workbench y expanda las Tablas bajo el esquema de modelos clásicos . Explore las tablas y columnas para tener una idea de la estructura de la base de datos. Por ejemplo, expanda la tabla Columnas de los clientes para ver todos los atributos de esta tabla, como se muestra en la Figura 1.

Figura 1: Atributos de la tabla de clientes

Al expandir la carpeta Claves externas en cada tabla, puede ver qué une una tabla específica a otras tablas. Por ejemplo, la tabla de clientes se une a otras tablas, mediante el atributo customerNumber (pedidos, pagos).

Clientes con pedidos en proceso o en espera

Si hace clic con el botón derecho en la tabla de pedidos, puede seleccionar las primeras 1000 filas de la tabla. Al observar la columna de estado, hay algunos pedidos que están enviados, en espera, en disputa y en proceso. Escribamos una instrucción SELECT para los pedidos que están en proceso o en espera. Recuerde que la clave entre estas tablas es customerNumber:

SELECCIONE orderNumber, orderDate, status 
FROM classicmodels.orders 
DONDE status = 'En proceso' O status = 'En espera';

Haga clic en el rayo para ejecutar la consulta (Figura 2):

Figura 2: Ejecutar SQL

Los resultados se muestran en la Figura 3:

Figura 3: Resultados de la consulta de la tabla de pedidos

¿Cuánto ha pagado el cliente 282 hasta ahora?

Mira la tabla de pagos . La tabla incluye customerNumber y los pagos realizados. Escribamos una declaración SQL para resumir la cantidad que el cliente número 282 ha pagado.

SELECCIONE SUM (monto) DE classicmodels.payments 
DONDE customerNumber = 282;

La Figura 4 muestra el monto total para ese cliente:

Figura 4: Importe total

Países con más de 10 clientes

Las siguientes declaraciones combinan varios conceptos vistos hasta ahora en este curso. Esto incluye COUNT, GROUP BY y ORDER BY. También introducimos la cláusula HAVING; podemos usar esto en combinación con la agrupación y el recuento para agregar datos. En el siguiente SQL, seleccionamos un recuento de clientes, pero solo si tenemos más de 10 clientes de su país.

SELECCIONE COUNT (customerNumber), país de 
classicmodels.customers 
GRUPO POR país 
TIENE COUNT (customerNumber)> 10 
ORDEN POR COUNT (customerNumber) ASC;

La salida se muestra en la Figura 5:

Figura 5: TENIENDO Salida

Aplicación de código

¡Ahora es su turno de escribir declaraciones SQL! En un comunicado anterior, buscamos pedidos que estaban en espera o en proceso. Pero, ¿y si quisiéramos pedidos que no estaban en proceso? En ese caso, puede utilizar el operador NOT.

Su tarea es completar las siguientes tareas:

1. Seleccionar pedidos que no están en proceso
2. Cuente estos pedidos; ¿cuántos hay?
3. Seleccione los pedidos que se realizaron entre el 1/1/2003 y el 31/12/2003

Nota: ¡A MySQL le gustan sus fechas entre comillas simples!

Preguntas de seguimiento

Utilice la palabra clave IN

Seleccione los pagos que se han realizado de los siguientes clientes: 311, 323, 379, 382 y 456. Ordene por la fecha de pago, del más antiguo al más nuevo.

¿Cuál es el precio sugerido del producto promedio?

¿Consultar la tabla de productos para determinar el precio minorista promedio? La columna MSRP (precio minorista sugerido por el fabricante) enumera el precio minorista sugerido para un producto determinado.

A continuación, ajuste la consulta para mostrar solo los productos que tengan una escala de producto de 1:72.

Crear grupos para la escala de productos

De la tabla de productos, escriba una declaración SELECT que agrupe la escala del producto, de menor a mayor. Sugerencia: ¡Cuanto mayor sea el número después de los dos puntos en la escala, más pequeña será la escala!). Si sus resultados no tienen sentido, ¿por qué?

Clave de respuesta

A continuación se encuentran las respuestas a la aplicación del código y las preguntas de seguimiento.

Aplicación de código

Seleccionar pedidos que no estén en proceso

Dado que hay otros estados en la tabla, podemos usar la palabra clave NOT para filtrar cualquier cosa que no esté en proceso.

SELECCIONE * FROM classicmodels.orders 
DONDE el estado NO ESTÁ EN ('En proceso');

Los resultados se muestran en la Figura 6:

Figura 6: Pedidos no en proceso

Cuente estas órdenes; ¿Cuántos hay?

Para obtener el recuento de pedidos que no están en proceso, podemos modificar nuestra declaración SELECT original y eliminar el asterisco *, reemplazándolo con una palabra clave COUNT:

SELECCIONE COUNT (orderNumber) FROM classicmodels.orders 
WHERE status NOT In ('En proceso');

El resultado debería ser 320.

Seleccione los pedidos que se realizaron entre el 1/1/2003 y el 31/12/2003

Este ejercicio requiere el uso de la palabra clave BETWEEN para determinar un valor entre dos fechas. Recuerde que debe incluir las fechas entre comillas simples como se muestra en el siguiente SQL:

SELECCIONE * FROM classicmodels.orders 
WHERE orderDate ENTRE '2003-01-01' Y '2003-12-31';

Esto debería resultar en 111 filas.

Preguntas de seguimiento

Aquí están las respuestas a las preguntas de seguimiento.

Utilice la palabra clave IN

Para seleccionar los pagos de la lista de clientes, use la palabra clave IN en MySQL:

SELECCIONE * FROM classicmodels.payments 
DONDE customerNumber IN (311, 323, 379, 382, ​​456);

¿Cuál es el precio sugerido del producto promedio?

¿Consultar la tabla de productos para determinar el precio minorista promedio? La columna MSRP (precio minorista sugerido por el fabricante) enumera el precio minorista sugerido para un producto determinado.

El siguiente paso implica el uso de la función AVG para crear un valor promedio del atributo MSRP en la tabla de productos.

SELECCIONE AVG (MSRP) DE classicmodels.products;

La media debería rondar los 100 (100,438).

A continuación, puede modificar la declaración para mostrar la escala del producto de 1:72:

SELECCIONE AVG (MSRP) DE classicmodels.products 
DONDE productScale = '1:72';

Escala de productos grupales

El siguiente SQL agrupa y ordena el producto por escala de producto.

SELECCIONE productName, productScale FROM classicmodels.products 
GROUP BY productScale 
ORDEN POR productScale DESC;

Los resultados se muestran en la Figura 7.

Figura 7: ORDENAR POR RESULTADOS

¿Por qué aparece 1: 700 en segundo lugar? ¿No debería ser el primero?

La razón es que SQL está ordenando un campo de texto. 700 es más que 72, pero en el mundo del texto, ‘700’ es el siguiente después de ’72’, seguido de ’50’.

Hay una forma un poco complicada de solucionar esto: convertiremos los últimos tres elementos a un número entero (SIN FIRMAR en MySQL). Si bien lo siguiente no es un conocimiento obligatorio para este curso, es un enfoque bastante ordenado:

SELECCIONE productName, productScale FROM classicmodels.products 
GROUP BY productScale 
ORDEN POR CAST (MID (productScale, 3, 3) COMO SIN FIRMAR) DESC;

La figura 8 muestra los nuevos resultados:

Figura 8: Nuevos resultados ORDER BY