FIT 9132 Week 9

Which of the following SQL queries would retrieve the total sales amount for each month in the year 2022 from the "sales" table?

• SELECT MONTH(TO_DATE(sale_date)) AS month, SUM(sale_amount) AS total_sales FROM sales WHERE YEAR(TO_DATE(sale_date)) = 2022 GROUP BY MONTH(TO_DATE(sale_date))

• SELECT MONTH(sale_date), SUM(sale_amount) FROM sales WHERE sale_date BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY MONTH(sale_date)

• SELECT EXTRACT(MONTH FROM sale_date), SUM(sale_amount) FROM sales WHERE EXTRACT(YEAR FROM sale_date) = 2022 GROUP BY EXTRACT(MONTH FROM sale_date)

• SELECT MONTH(sale_date), SUM(sale_amount) FROM sales WHERE YEAR(sale_date) = 2022 GROUP BY sale_date

Suppose you have two tables: "employees" and "departments". The "employees" table has the columns emp_id, emp_name, salary, dept_name, dep_id. The "departments" table has the columns dept_id and dept_name. Write a SQL query to find the average salary for each department, along with the department name.

• SELECT departments.dept_name, AVG(employees.salary) AS avg_salary FROM employees JOIN departments ON employees.dep_id = departments.dept_id GROUP BY departments.dept_name;

• SELECT dept_name, AVG(salary) AS avg_salary FROM employees, departments WHERE employees.dep_id = departments.dept_id GROUP BY departments.dept_name;

• SELECT dept_name, AVG(salary) AS avg_salary FROM employees NATURAL JOIN departments GROUP BY dept_name;

• SELECT dept_name, AVG(salary) AS avg_salary FROM departments LEFT JOIN employees ON departments.dept_id = employees.dept_id GROUP BY dept_name;

Suppose you have a table called "orders" with the columns order_id, customer_id, order_date, and order_total. Write a SQL query to find the top 10 customers with the highest total order amount.

• SELECT customer_id,

  SUM(order_total) AS total_spent

  FROM orders

  GROUP BY

  customer_id

  HAVING

  total_spent > 0

  ORDER BY

  total_spent

  LIMIT 10;

• SELECT

  customer_id,

  SUM(order_total) AS total_spent

  FROM orders

  WHERE

  order_total > 0

  GROUP BY

  customer_id

  ORDER BY

  total_spent

  LIMIT 10;

• SELECT

  customer_id,

  SUM(order_total) AS total_spent

  FROM orders

  GROUP BY

  customer_id

  ORDER BY

  2 DESC

  LIMIT 10;

• SELECT

  customer_id,

  SUM(order_total) AS total_spent

  FROM orders

  GROUP BY

  customer_id

  ORDER BY

  total_spent DESC

  LIMIT 10;

Suppose you have a table called "products" with the columns product_id, product_name, price, and category. Write a SQL query to find the product with the highest price in each category.

• SELECT product_name, MAX(price) FROM products GROUP BY category ORDER BY MAX(price) DESC LIMIT 1;

• SELECT product_name, price FROM products WHERE price = ( SELECT MAX(price) FROM products WHERE category = p.category ) GROUP BY category;

• SELECT p1.product_name, p1.price FROM products p1 JOIN ( SELECT category, MAX(price) AS max_price FROM products GROUP BY category ) p2 ON p1.category = p2.category AND p1.price = p2.max_price ORDER BY p1.category ASC;

• SELECT product_name, MAX(price) FROM products GROUP BY category HAVING MAX(price) = ( SELECT MAX(price) FROM products );

Suppose you have two tables: "orders" and "order_details". The "orders" table has the columns order_id, customer_id, order_date, and order_total. The "order_details" table has the columns order_id, product_id, quantity, and price. Write a SQL query to find the total revenue generated by each customer.

• SELECT o.customer_id, SUM(od.quantity) * SUM(od.price) AS revenue FROM orders o JOIN order_details od ON o.order_id = od.order_id GROUP BY o.customer_id

• SELECT o.customer_id, SUM(od.quantity * od.price) AS revenue FROM orders o JOIN order_details od ON o.order_id = od.order_id GROUP BY o.customer_id

• SELECT o.customer_id, SUM(od.quantity) + SUM(od.price) AS revenue FROM orders o JOIN order_details od ON o.order_id = od.order_id GROUP BY o.customer_id

• SELECT o.customer_id, SUM(od.quantity) * AVG(od.price) AS revenue FROM orders o JOIN order_details od ON o.order_id = od.order_id GROUP BY o.customer_id

Consider a table called "students" with the following columns: student_id, student_name, major, and gpa. Write a SQL query to find the student(s) with the highest GPA for each major.

• SELECT major, student_id, student_name, gpa FROM students s1 WHERE gpa = ( SELECT MAX(gpa) FROM students s2 WHERE s1.major = s2.major )

• SELECT major, MAX(gpa) AS max_gpa FROM students GROUP BY major

• SELECT major, student_id, student_name, gpa FROM students WHERE gpa = MAX(gpa) GROUP BY major

• SELECT major, student_id, student_name, gpa FROM students s1 WHERE NOT EXISTS ( SELECT 1 FROM students s2 WHERE s1.major = s2.major AND s2.gpa > s1.gpa )

Suppose you have a table called "invoices" with the following columns: invoice_id, customer_id, invoice_date, and invoice_amount. Write a SQL query to find all invoices that have a duplicate invoice amount, and display them in descending order of invoice amount.

• SELECT invoice_amount, COUNT(*) as count FROM invoices GROUP BY invoice_amount HAVING COUNT(*) > 1 ORDER BY invoice_amount DESC;

• SELECT invoice_id, customer_id, invoice_date, invoice_amount FROM invoices WHERE invoice_amount IN (SELECT invoice_amount FROM invoices DISTINCT invoice_amount HAVING COUNT(*) > 1) ORDER BY invoice_amount DESC;

• SELECT i1.invoice_id, i1.customer_id, i1.invoice_date, i1.invoice_amount FROM invoices i1 JOIN (SELECT invoice_amount FROM invoices GROUP BY invoice_amount HAVING COUNT(*) > 1) i2 ON i1.invoice_amount = i2.invoice_amount ORDER BY i1.invoice_amount DESC;

• SELECT invoice_id, customer_id, invoice_date, invoice_amount FROM invoices WHERE invoice_amount IN (SELECT invoice_amount FROM invoices WHERE invoice_amount IS NOT NULL GROUP BY invoice_amount) ORDER BY invoice_amount DESC;

Consider a table called "sales" with the following columns: sale_id, product_id, sale_date, sale_amount. Write a SQL query to find the top 5 selling products in the year 2022, excluding any products with a NULL value in the product_id column

• SELECT product_id, SUM(sale_amount) AS total_sales FROM sales WHERE YEAR(sale_date) = 2022 AND product_id IS NOT NULL GROUP BY product_id ORDER BY total_sales DESC LIMIT 5;

• SELECT TOP 5 product_id, SUM(sale_amount) AS total_sales FROM sales WHERE YEAR(sale_date) = 2022 AND product_id IS NOT NULL GROUP BY product_id ORDER BY total_sales DESC;

• SELECT DISTINCT product_id, SUM(sale_amount) OVER (PARTITION BY product_id) AS total_sales FROM sales WHERE YEAR(sale_date) = 2022 AND product_id IS NOT NULL ORDER BY total_sales DESC FETCH FIRST 5 ROWS ONLY;

• SELECT product_id, SUM(sale_amount) AS total_sales FROM sales WHERE YEAR(sale_date) = 2022 AND product_id IS NOT NULL GROUP BY product_id HAVING COUNT(*) < 1 ORDER BY total_sales DESC LIMIT 5;