Análisis de consultas SQL

Estás trabajando con una base de datos PostgreSQL para una empresa minorista. La tabla sales tiene las columnas: sale_id (entero), product_id (entero), quantity (entero), price (numérico) y sale_date (fecha). Necesitas encontrar los ingresos totales (quantity * price) para cada producto donde las ventas ocurrieron en el año 2023, pero solo para productos que generaron más de $10,000 en ingresos totales.

• SELECT product_id, SUM(quantity price) AS total_revenue FROM sales WHERE EXTRACT(YEAR FROM sale_date) = 2023 GROUP BY product_id HAVING SUM(quantity price) > 10000;

• SELECT product_id, SUM(quantity * price) AS total_revenue FROM sales WHERE sale_date LIKE '2023%' GROUP BY product_id HAVING total_revenue > 10000;

• SELECT product_id, COUNT(quantity price) AS total_revenue FROM sales WHERE EXTRACT(YEAR FROM sale_date) = 2023 GROUP BY product_id HAVING SUM(quantity price) > 10000;

• SELECT product_id, SUM(quantity price) AS total_revenue FROM sales GROUP BY product_id WHERE EXTRACT(YEAR FROM sale_date) = 2023 HAVING SUM(quantity price) > 10000;

En una base de datos PostgreSQL para una escuela, la tabla students incluye: student_id (entero), name (texto), course_id (entero), grade (numérico) y enrollment_date (fecha). Deseas calcular el promedio de calificaciones por curso, pero solo incluir cursos con al menos 5 estudiantes inscritos después del 1 de enero de 2022.

• SELECT course_id, AVG(grade) AS avg_grade FROM students GROUP BY course_id WHERE enrollment_date > '2022-01-01' HAVING COUNT(student_id) >= 5;

• SELECT course_id, AVG(grade) AS avg_grade FROM students WHERE enrollment_date > '2022-01-01' GROUP BY course_id HAVING COUNT(student_id) >= 5;

• SELECT course_id, SUM(grade) / COUNT(grade) AS avg_grade FROM students WHERE enrollment_date > '2022-01-01' GROUP BY course_id HAVING AVG(grade) >= 5;

• SELECT course_id, AVG(grade) AS avg_grade FROM students WHERE enrollment_date > '2022-01-01' GROUP BY course_id HAVING SUM(student_id) >= 5;

• Ninguna de las anteriores

Para una base de datos PostgreSQL de comercio electrónico, la tabla orders tiene: order_id (entero), customer_id (entero), amount (numérico) y order_date (fecha). Necesitas encontrar clientes que hayan realizado pedidos con un total superior al promedio de los montos de pedidos de todos los clientes en 2024.

¿Cuál consulta SQL usa una subconsulta correctamente?

• SELECT customer_id, AVG(amount) AS total FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024 GROUP BY customer_id HAVING SUM(amount) > (SELECT AVG(amount) FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024);

• SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > (SELECT AVG(amount) FROM orders) AND EXTRACT(YEAR FROM order_date) = 2024;

• SELECT customer_id, SUM(amount) AS total FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024 GROUP BY customer_id HAVING SUM(amount) > (SELECT AVG(amount) FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024);

• SELECT customer_id, SUM(amount) AS total FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024 GROUP BY customer_id HAVING AVG(amount) > (SELECT SUM(amount) FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024);

En una base de datos PostgreSQL de recursos humanos, la tabla employees contiene: emp_id (entero), name (texto), department (texto), salary (numérico) y hire_date (fecha). Necesitas contar el número de empleados por departamento contratados antes de 2020, pero solo mostrar departamentos con un salario promedio superior a $50,000.

• SELECT department, COUNT(emp_id) AS emp_count FROM employees WHERE hire_date < '2020-01-01' GROUP BY department HAVING AVG(salary) > 50000;

• SELECT department, SUM(emp_id) AS emp_count FROM employees WHERE hire_date < '2020-01-01' GROUP BY department HAVING AVG(salary) > 50000;

• SELECT department, COUNT(emp_id) AS emp_count FROM employees GROUP BY department WHERE hire_date < '2020-01-01' HAVING AVG(salary) > 50000;

• SELECT department, COUNT(emp_id) AS emp_count FROM employees WHERE hire_date < '2020-01-01' GROUP BY department HAVING SUM(salary) > 50000;

Una base de datos PostgreSQL de inventario tiene la tabla products con: product_id (entero), name (texto), category (texto), stock (entero) y last_update (fecha). Deseas obtener el stock total por categoría actualizado después del 1 de junio de 2023, excluyendo categorías con menos de 3 productos.

• SELECT category, SUM(stock) AS total_stock FROM products WHERE last_update > '2023-06-01' GROUP BY category HAVING COUNT(product_id) >= 3;

• SELECT category, AVG(stock) AS total_stock FROM products WHERE last_update > '2023-06-01' GROUP BY category HAVING COUNT(product_id) >= 3;

• SELECT category, SUM(stock) AS total_stock FROM products GROUP BY category WHERE last_update > '2023-06-01' HAVING COUNT(product_id) >= 3;

• SELECT category, SUM(stock) AS total_stock FROM products WHERE last_update > '2023-06-01' GROUP BY category HAVING SUM(product_id) >= 3;

En una base de datos SQL Server para una librería, la tabla books incluye: book_id (entero), title (varchar), author_id (entero), sales (entero) y publish_date (fecha). Necesitas sumar las ventas por autor de libros publicados en 2022, mostrando solo autores con ventas totales superiores a 1000.

• SELECT author_id, SUM(sales) AS total_sales FROM books WHERE publish_date LIKE '2022%' GROUP BY author_id HAVING total_sales > 1000;

• SELECT author_id, COUNT(sales) AS total_sales FROM books WHERE YEAR(publish_date) = 2022 GROUP BY author_id HAVING SUM(sales) > 1000;

• SELECT author_id, SUM(sales) AS total_sales FROM books GROUP BY author_id WHERE YEAR(publish_date) = 2022 HAVING SUM(sales) > 1000;

• SELECT author_id, SUM(sales) AS total_sales FROM books WHERE YEAR(publish_date) = 2022 GROUP BY author_id HAVING SUM(sales) > 1000;

Para una base de datos SQL Server bancaria, la tabla transactions tiene: trans_id (entero), account_id (entero), amount (decimal), type (varchar) y trans_date (fecha). Calcula el promedio del monto de transacciones por cuenta para depósitos (type = 'deposit') en 2023, solo para cuentas con más de 10 transacciones.

¿Cuál consulta SQL logra esto?

• SELECT account_id, AVG(amount) AS avg_amount FROM transactions WHERE type = 'deposit' AND YEAR(trans_date) = 2023 GROUP BY account_id HAVING COUNT(trans_id) > 10;

• SELECT account_id, SUM(amount) / COUNT(amount) AS avg_amount FROM transactions GROUP BY account_id WHERE type = 'deposit' AND YEAR(trans_date) = 2023 HAVING COUNT(trans_id) > 10;

• SELECT account_id, AVG(amount) AS avg_amount FROM transactions WHERE type = 'deposit' AND YEAR(trans_date) = 2023 GROUP BY account_id HAVING SUM(trans_id) > 10;

• SELECT account_id, AVG(amount) AS avg_amount FROM transactions WHERE type = 'deposit' GROUP BY account_id HAVING COUNT(trans_id) > 10 AND YEAR(trans_date) = 2023;

En una base de datos SQL Server de logística, la tabla shipments contiene: ship_id (entero), customer_id (entero), weight (decimal) y ship_date (fecha). Encuentra clientes cuyo peso total de envíos en 2024 supera el peso promedio por cliente.

¿Cuál consulta SQL usa una subconsulta correctamente?

• SELECT customer_id, SUM(weight) AS total_weight FROM shipments WHERE YEAR(ship_date) = 2024 GROUP BY customer_id HAVING SUM(weight) > (SELECT AVG(total_weight) FROM (SELECT customer_id, SUM(weight) AS total_weight FROM shipments GROUP BY customer_id) AS avg_table);

• SELECT customer_id, SUM(weight) AS total_weight FROM shipments GROUP BY customer_id HAVING SUM(weight) > (SELECT AVG(weight) FROM shipments) AND YEAR(ship_date) = 2024;

• SELECT customer_id, AVG(weight) AS total_weight FROM shipments WHERE YEAR(ship_date) = 2024 GROUP BY customer_id HAVING SUM(weight) > (SELECT AVG(weight) FROM shipments WHERE YEAR(ship_date) = 2024);

• SELECT customer_id, SUM(weight) AS total_weight FROM shipments WHERE YEAR(ship_date) = 2024 GROUP BY customer_id HAVING AVG(weight) > (SELECT SUM(weight) FROM shipments);

Una base de datos SQL Server de gestión de proyectos tiene la tabla tasks con: task_id (entero), project_id (entero), duration (entero), status (varchar) y start_date (fecha). Cuenta las tareas por proyecto que comenzaron después del 1 de marzo de 2021 y están completadas (status = 'completed'), mostrando solo proyectos con una duración promedio superior a 30 días.

¿Cuál consulta SQL es adecuada?

• SELECT project_id, SUM(task_id) AS task_count FROM tasks WHERE start_date > '2021-03-01' AND status = 'completed' GROUP BY project_id HAVING AVG(duration) > 30;

• SELECT project_id, COUNT(task_id) AS task_count FROM tasks WHERE start_date > '2021-03-01' AND status = 'completed' GROUP BY project_id HAVING AVG(duration) > 30;

• SELECT project_id, COUNT(task_id) AS task_count FROM tasks GROUP BY project_id WHERE start_date > '2021-03-01' AND status = 'completed' HAVING AVG(duration) > 30;

• SELECT project_id, COUNT(task_id) AS task_count FROM tasks WHERE start_date > '2021-03-01' AND status = 'completed' GROUP BY project_id HAVING SUM(duration) > 30;

En una base de datos SQL Server de salud, la tabla patients incluye: patient_id (entero), doctor_id (entero), visit_count (entero), age (entero) y last_visit (fecha). Suma el conteo de visitas por doctor para pacientes mayores de 50 años con última visita en 2023, excluyendo doctores con menos de 5 pacientes.

¿Cuál consulta SQL funciona?

• SELECT doctor_id, AVG(visit_count) AS total_visits FROM patients WHERE age > 50 AND YEAR(last_visit) = 2023 GROUP BY doctor_id HAVING COUNT(patient_id) >= 5;

• SELECT doctor_id, SUM(visit_count) AS total_visits FROM patients WHERE age > 50 AND YEAR(last_visit) = 2023 GROUP BY doctor_id HAVING COUNT(patient_id) >= 5;

• SELECT doctor_id, SUM(visit_count) AS total_visits FROM patients GROUP BY doctor_id WHERE age > 50 AND YEAR(last_visit) = 2023 HAVING COUNT(patient_id) >= 5;

• SELECT doctor_id, SUM(visit_count) AS total_visits FROM patients WHERE age > 50 AND YEAR(last_visit) = 2023 GROUP BY doctor_id HAVING SUM(patient_id) >= 5;