FIT 9132 Week 10

Write a SQL query to retrieve the top 5 salespeople with the highest total sales amount for the month of January 2023

• SELECT SalespersonID, SUM(SalesAmount) AS TotalSales

  FROM Sales

  WHERE MONTH(SalesDate) = 1 AND YEAR(SalesDate) = 2023

  GROUP BY SalespersonID

  ORDER BY TotalSales DESC

  LIMIT 5;

• SELECT TOP 5 SalespersonID, SUM(SalesAmount) AS TotalSales

  FROM Sales

  WHERE MONTH(SalesDate) = 1 AND YEAR(SalesDate) = 2023

  GROUP BY SalespersonID

  ORDER BY TotalSales DESC;

• SELECT SalespersonID, SUM(SalesAmount) AS TotalSales

  FROM Sales

  WHERE SalesDate >= '2023-01-01' AND SalesDate < '2023-02-01'

  GROUP BY SalespersonID

  ORDER BY TotalSales DESC

  LIMIT 5;

• SELECT SalespersonID, SUM(SalesAmount) AS TotalSales

  FROM Sales

  WHERE SalesDate BETWEEN '2023-01-01' AND '2023-01-31'

  GROUP BY SalespersonID

  ORDER BY TotalSales DESC

  FETCH FIRST 5 ROWS ONLY;

Write a SQL query to retrieve the average salary for employees in each department, including only those departments where the average salary is greater than $50,000

• SELECT Department, AVG(Salary) AS AvgSalary

  FROM Employees

  GROUP BY Department

  HAVING AVG(Salary) > 50000;

• SELECT Department, AVG(Salary) AS AvgSalary

  FROM Employees

  WHERE AVG(Salary) > 50000

  GROUP BY Department;

• SELECT Department, AVG(Salary) AS AvgSalary

  FROM Employees

  HAVING AVG(Salary)> 50000

  GROUP BY Department

  ORDER BY AvgSalary DESC;

• SELECT Department, AVG(Salary) AS AvgSalary

  FROM Employees

  WHERE Department IN (SELECT Department FROM Employees GROUP BY Department HAVING AvgSalary > 50000)

  GROUP BY Department;

Write a SQL query to retrieve the names of all customers who have placed orders for more than 5 distinct products, along with the number of distinct products they have ordered.

• SELECT customer_name, COUNT(DISTINCT product_id) AS num_distinct_products

  FROM orders

  HAVING COUNT(DISTINCT product_id) > 5;

• SELECT customer_name, COUNT(DISTINCT product_id) AS num_distinct_products

  FROM orders

  WHERE COUNT(DISTINCT product_id) > 5

  GROUP BY customer_id;

• SELECT customer_name, COUNT(DISTINCT product_id) AS num_distinct_products

  FROM orders

  GROUP BY customer_id

  HAVING COUNT(DISTINCT product_id) > 5;

• SELECT customer_name, COUNT(DISTINCT product_id) AS num_distinct_products

  FROM orders

  WHERE customer_id IN (

  SELECT customer_id

  FROM orders

  GROUP BY customer_id

  HAVING COUNT(product_id) > 5

  )

  GROUP BY customer_name;

Write a SQL query to retrieve the names of all products that have been ordered more than 10 times, along with the total number of units ordered and the average order quantity.

• SELECT product_name, COUNT(quantity) AS total_units_ordered, AVG(quantity) AS avg_order_quantity

  FROM order_items

  GROUP BY product_id

  HAVING COUNT(quantity) > 10;

• SELECT product_name, SUM(quantity) AS total_units_ordered, AVG(quantity) AS avg_order_quantity

  FROM order_items

  GROUP BY product_id

  HAVING SUM(quantity) > 10;

• SELECT product_name, SUM(quantity) AS total_units_ordered, AVG(quantity) AS avg_order_quantity

  FROM (

  SELECT *

  FROM order_items

  WHERE product_id IN (

  SELECT product_id

  FROM order_items

  GROUP BY product_id

  HAVING COUNT(*) > 10

  )

  ) AS subquery

  GROUP BY product_id;

• SELECT product_name, SUM(quantity) AS total_units_ordered, AVG(quantity) AS avg_order_quantity

  FROM order_items

  GROUP BY product_type

  HAVING SUM(quantity) > 10;

Write a SQL query to retrieve the names of all customers who have placed orders with a total amount greater than the average total amount of all orders, along with the total amount of their orders.

• SELECT customer_name, SUM(order_total) AS order_total

  FROM orders

  GROUP BY customer_id

  HAVING order_total > (

  SELECT AVG(order_total)

  FROM orders

  );

• SELECT customer_name, SUM(order_total) AS total_order_amount

  FROM orders

  GROUP BY customer_id

  HAVING SUM(order_total) > (

  SELECT AVG(order_total)

  FROM orders

  );

• SELECT customer_name, SUM(order_total) AS total_order_amount

  FROM orders

  WHERE SUM(order_total) > (

  SELECT AVG(order_total)

  FROM orders

  )

  GROUP BY customer_id;

• SELECT customer_name, SUM(order_total) AS total_order_amount

  FROM orders

  GROUP BY customer_id

  HAVING SUM(order_total) > (

  SELECT total_order_amount / COUNT(*)

  FROM orders

  );

Which of the following SELECT query returns the department number with maximum salary compensated to an employee? (Consider the table structure as given)

• SELECT department_id , max(salary ) FROM employees ;

• SELECT department_id , max(salary ) FROM employees GROUP BY department_id ;

• SELECT max(salary ) FROM employees GROUP BY department_id ;

• SELECT max(salary ) FROM employees ;

If you don’t specify ASC or DESC after a SQL ORDER BY clause, the following is used by default

• ASC

• DESC

• There is no default value

• None of the mentioned

What is true of using group functions on columns that contain NULL values?

• Group functions on columns ignore NULL values.

• Group functions on columns returning dates include NULL values.

• Group functions on columns returning numbers include NULL values.

• Group functions on columns cannot be accurately used on columns that contain NULL values.

Which character returns all the data found in a table?

• %

• #

• *

• /

What is the correct order of clauses in a SQL statement?

• SELECT, FROM, ORDER BY, WHERE

• SELECT, FROM, WHERE, ORDER BY

• SELECT, WHERE, FROM, ORDER BY

• WHERE, FROM, SELECT, ORDER BY