Resumen BD U4
Resumen BD U4
Resumen BD U4
Cláusula WHERE
Permite seleccionar datos de una tabla que cumplan una condición específica.
Es esencial para restringir la selección a un subconjunto de filas.
Cláusula HAVING
Utilizada para condiciones que incluyen operadores aritméticos como SUM, MAX,
COUNT, MIN, AVG. A diferencia de WHERE, HAVING se aplica a grupos de registros.
ejemplos de la cláusula HAVING con cada uno de los operadores
aritméticos mencionados:
1. SUM:
sql
SELECT category, SUM(price) AS total_price
FROM products
GROUP BY category
HAVING SUM(price) > 1000;
2. MAX:
sql
SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 50000;
3. COUNT:
sql
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
4. MIN:
sql
SELECT product_type, MIN(stock_quantity) AS min_stock
FROM inventory
GROUP BY product_type
HAVING MIN(stock_quantity) < 50;
5. AVG:
sql
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 40000;
Operadores SQL
Operadores de comparación:
SQL
SQL
Menor que (<): Selecciona registros donde un valor es menor que otro.
SQL
Mayor que (>): Selecciona registros donde un valor es mayor que otro.
SQL
Menor o igual que (<=): Selecciona registros donde un valor es menor o igual
que otro.
SQL
Mayor o igual que (>=): Selecciona registros donde un valor es mayor o igual
que otro.
SQL
Operadores lógicos:
Y (AND): Combina dos condiciones para que ambas sean verdaderas para que se
incluya un registro.
SQL
O (OR): Combina dos condiciones para que al menos una sea verdadera para que
se incluya un registro.
SQL
SQL
Operadores aritméticos:
SQL
SQL
SQL
SQL
Operadores de concatenación:
Oracle (||): Concatena dos cadenas de texto.
SQL
SQL
Ejemplos adicionales:
Seleccionar todos los clientes de España con nombre que empiece por
"Ana":
SQL
SQL
SQL
Consultas Calculadas
SQL
SELECT *, precio_venta * (1 - descuento_porcentaje/100) AS
precio_descontado
FROM productos;
Si tenemos una tabla pedidos con campos subtotal y impuesto, podemos calcular el
total con IVA utilizando la siguiente consulta:
SQL
SELECT *, subtotal + subtotal * impuesto/100 AS total_con_iva
FROM pedidos;
SQL
SELECT *, precio_venta - costo_unitario AS ganancia_bruta
FROM ventas;
SQL
SELECT *, YEAR(CURDATE()) - YEAR(fecha_nacimiento) -
(MONTH(CURDATE()) < MONTH(fecha_nacimiento) OR
(MONTH(CURDATE()) = MONTH(fecha_nacimiento) AND
DAY(CURDATE()) < DAY(fecha_nacimiento))) AS edad
FROM clientes;
SQL
SELECT *, DATEDIFF(fecha_salida, fecha_llegada) AS duracion_dias
FROM reservas;
Cláusula GROUP BY
Supongamos que tenemos una tabla ventas con campos producto_id, categoria,
precio_venta y unidades_vendidas. Podemos obtener el total de ventas por categoría
utilizando la siguiente consulta:
SQL
SELECT categoria, SUM(precio_venta * unidades_vendidas) AS
total_ventas
FROM ventas
GROUP BY categoria;
SQL
SELECT pais, COUNT(*) AS numero_clientes
FROM clientes
GROUP BY pais;
Supongamos que tenemos una tabla pedidos con campos id_pedido, estado,
importe_total y fecha_pedido. Podemos obtener el promedio de importe por estado
de pedido utilizando la siguiente consulta:
SQL
SELECT estado, AVG(importe_total) AS promedio_importe
FROM pedidos
GROUP BY estado;
4. Agrupar productos por marca y obtener el producto con mayor precio de venta
de cada marca:
SQL
SELECT marca, producto_id, nombre, MAX(precio_venta) AS precio_maximo
FROM productos
GROUP BY marca;
SQL
SELECT departamento, id_empleado, nombre, MAX(salario) AS
salario_maximo
FROM empleados
GROUP BY departamento;
Subconsultas
Supongamos que tenemos dos tablas: clientes con campos id_cliente, nombre y
email y pedidos con campos id_pedido, id_cliente, fecha_pedido y
importe_total. Podemos obtener el nombre del cliente con el mayor número de
pedidos utilizando la siguiente consulta:
SQL
SELECT nombre
FROM clientes
WHERE id_cliente IN (
SELECT id_cliente
FROM pedidos
GROUP BY id_cliente
ORDER BY COUNT(*) DESC
LIMIT 1
);
SQL
SELECT p.*
FROM productos AS p
WHERE p.precio_venta > (
SELECT AVG(precio_venta)
FROM productos AS p2
WHERE p2.categoria = p.categoria
);
SQL
SELECT v.id_vendedor, v.nombre, COALESCE(SUM(pv.importe_total), 0) AS
total_ventas
FROM vendedores AS v
LEFT JOIN ventas AS pv ON v.id_vendedor = pv.id_vendedor
GROUP BY v.id_vendedor, v.nombre;
4. Mostrar los departamentos con la mayor cantidad de empleados que ganan más
del salario promedio de la empresa:
SQL
SELECT d.nombre_departamento, COUNT(*) AS
numero_empleados_por_encima_media
FROM empleados AS e
JOIN departamentos AS d ON e.departamento = d.id_departamento
WHERE e.salario > (
SELECT AVG(salario)
FROM empleados
)
GROUP BY d.nombre_departamento
ORDER BY numero_empleados_por_encima_media DESC;
Supongamos que tenemos una tabla productos con campos producto_id, nombre,
precio_venta, categoria y stock y una tabla pedidos_detalle con campos
id_detalle_pedido, id_pedido, id_producto, unidades y precio_venta_unidad.
Podemos actualizar el stock de un producto restando la cantidad vendida en un pedido
específico utilizando la siguiente consulta:
SQL
UPDATE productos
SET stock = stock - pd.unidades
FROM productos AS p
INNER JOIN pedidos_detalle AS pd ON p.producto_id = pd.id_producto
WHERE pd.id_pedido = 123;
Vistas
Las vistas son consultas almacenadas que pueden ser tratadas como tablas.
1. Crear una vista para mostrar los productos de una categoría específica:
Supongamos que tenemos una tabla productos con campos producto_id, nombre,
precio_venta, categoria y stock. Podemos crear una vista para mostrar solo los
productos de la categoría "Electrónica" utilizando la siguiente consulta:
SQL
CREATE VIEW vista_productos_electronica AS
SELECT producto_id, nombre, precio_venta, stock
FROM productos
WHERE categoria = 'Electrónica';
2. Crear una vista para mostrar el nombre completo del cliente y el total de sus
pedidos:
Si tenemos dos tablas: clientes con campos id_cliente, nombre, apellido y email
y pedidos con campos id_pedido, id_cliente, fecha_pedido y importe_total,
podemos crear una vista para mostrar el nombre completo del cliente y el total de sus
pedidos utilizando la siguiente consulta:
SQL
CREATE VIEW vista_clientes_pedidos AS
SELECT c.id_cliente, CONCAT(c.nombre, ' ', c.apellido) AS
nombre_completo, SUM(p.importe_total) AS total_pedidos
FROM clientes AS c
JOIN pedidos AS p ON c.id_cliente = p.id_cliente
GROUP BY c.id_cliente, nombre_completo;
3. Utilizar una vista como si fuera una tabla en otra consulta:
SQL
SELECT producto_id, nombre, precio_venta, stock
FROM vista_productos_electronica
ORDER BY precio_venta DESC
LIMIT 1;
SQL
UPDATE vista_productos_electronica
SET precio_venta = 1200
WHERE producto_id = 10;
SQL
DROP VIEW vista_productos_electronica;
Nota:
Las vistas no almacenan datos por sí mismas, sino que hacen referencia a los
datos de las tablas subyacentes.
Las vistas pueden ser útiles para simplificar consultas complejas, otorgar
permisos de acceso específicos a determinados datos o crear diferentes
perspectivas de los mismos datos.
SQL
CREATE PROCEDURE calcular_iva(
IN importe_base DECIMAL(10,2),
IN tipo_iva TINYINT,
OUT iva_resultante DECIMAL(10,2)
)
BEGIN
DECLARE iva_decimal DECIMAL(10,2);
SET iva_decimal = tipo_iva / 100;
SET iva_resultante = importe_base * iva_decimal;
END PROCEDURE;
SQL
CALL calcular_iva(100, 21, @iva_calculado);
SELECT @iva_calculado AS iva_resultante;
SQL
CREATE FUNCTION obtener_nombre_completo(
IN id_cliente INT
)
RETURNS VARCHAR(255)
BEGIN
DECLARE nombre_completo VARCHAR(255);
SELECT CONCAT(nombre, ' ', apellido) INTO nombre_completo
FROM clientes
WHERE id_cliente = id_cliente;
RETURN nombre_completo;
END FUNCTION;
Uso de la función:
SQL
SELECT obtener_nombre_completo(123) AS nombre_completo_cliente;
SQL
CREATE PROCEDURE actualizar_stock_venta(
IN id_producto INT,
IN unidades_vendidas INT
)
BEGIN
UPDATE productos
SET stock = stock - unidades_vendidas
WHERE producto_id = id_producto;
END PROCEDURE;
SQL
CALL actualizar_stock_venta(456, 10);
SQL
CREATE FUNCTION tiene_permiso_acceso(
IN id_usuario INT,
IN recurso_id INT
)
RETURNS TINYINT
BEGIN
DECLARE tiene_permiso TINYINT;
SELECT COUNT(*) INTO tiene_permiso
FROM permisos
WHERE id_usuario = id_usuario AND recurso_id = recurso_id;
RETURN tiene_permiso;
END FUNCTION;
Uso de la función:
SQL
SELECT tiene_permiso_acceso(789, 123) AS permiso_acceso;
5. Procedimiento almacenado para realizar una transferencia bancaria entre dos
cuentas:
SQL
CREATE PROCEDURE realizar_transferencia(
IN cuenta_origen INT,
IN cuenta_destino INT,
IN importe_transferencia DECIMAL(10,2)
)
BEGIN
DECLARE saldo_origen DECIMAL(10,2);
DECLARE saldo_destino DECIMAL(10,2);
UPDATE cuentas
SET saldo = saldo + importe_transferencia
WHERE id_cuenta = cuenta_destino;
-- Registrar la transacción
INSERT INTO transacciones (
id_cuenta_origen,
id_cuenta_destino,
importe,
fecha_transaccion
)
VALUES (
cuenta_origen,
cuenta_destino,
importe_transferencia,
CURRENT_TIMESTAMP
);
END PROCEDURE;
SQL
CALL realizar_transferencia(1001, 1002, 50.00);
Nota:
SQL
CREATE TRIGGER registrar_fecha_creacion
BEFORE INSERT ON mi_tabla
FOR EACH ROW
BEGIN
SET NEW.fecha_creacion = CURRENT_TIMESTAMP;
END TRIGGER;
Explicación:
SQL
CREATE TRIGGER actualizar_stock_venta
AFTER UPDATE ventas
FOR EACH ROW
BEGIN
IF NEW.unidades_vendidas > OLD.unidades_vendidas THEN
UPDATE productos
SET stock = stock - (NEW.unidades_vendidas -
OLD.unidades_vendidas)
WHERE producto_id = NEW.producto_id;
END IF;
END TRIGGER;
Explicación:
SQL
CREATE TRIGGER registrar_historial_cambios
BEFORE UPDATE OR DELETE ON mi_tabla
FOR EACH ROW
BEGIN
INSERT INTO historial_cambios (
tabla,
id_registro,
accion,
fecha_cambio,
datos_antes,
datos_despues
)
VALUES (
'mi_tabla',
NEW.id_registro,
CASE
WHEN NEW.id_registro IS NULL THEN 'DELETE'
ELSE 'UPDATE'
END,
CURRENT_TIMESTAMP,
OLD,
NEW
);
END TRIGGER;
Explicación:
SQL
CREATE TRIGGER validar_edad_registro
BEFORE INSERT ON usuarios
FOR EACH ROW
BEGIN
IF NEW.edad < 18 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La edad mínima para
registrarse es de 18 años';
END IF;
END TRIGGER;
Explicación:
SQL
CREATE TRIGGER auditar_acceso_tabla
AFTER SELECT
ON mi_tabla_sensible
FOR EACH ROW
BEGIN
INSERT INTO auditoria_accesos (
usuario,
fecha_acceso,
tabla_accsedida,
datos_accsedidos
)
VALUES (
CURRENT_USER(),
CURRENT_TIMESTAMP,
'mi_tabla_sensible',
NEW
);
END TRIGGER;
Explicación:
Nota:
a) Almacenar datos
b) Eliminar datos
c) Recuperar y manipular datos
d) Crear estructuras de base de datos
2. ¿Qué comando SQL se utiliza para obtener datos de una base de datos?
a) INSERT
b) SELECT
c) UPDATE
d) DELETE
Respuesta: b) SELECT
Preguntas Verdadero/Falso
Respuesta: Falso
Respuesta: Verdadero
Respuesta: Verdadero
Respuesta: Verdadero
Respuesta: Falso
Preguntas de Completar
1. Para eliminar todos los registros de una tabla sin borrar la tabla, se utiliza
el comando _________.
Respuesta: TRUNCATE
Respuesta: COUNT
Respuesta: JOIN
Respuesta: ORDER BY