1 You have a table named Orders with the following columns:
order_id, customer_id, order_date, total_amount. Write a SQL
query to find the total number of orders placed by each
customer and the total amount spent by each customer.
Ans.
SELECT customer_id, COUNT(order_id) as `total_orders`, SUM(total_amount) AS `total_spent` FROM
orders GROUP BY customer_id;
2 Given a table employees with columns employee_id and
salary, find the second highest salary.
Ans. SELECT * FROM `employees` ORDER BY salary DESC LIMIT 1,1;
3 Given a table employees with columns employee_id,
department_id, and salary, find the average salary for each
department.
Ans. SELECT department_id, AVG(salary) as avg_salary FROM `employees` GROUP BY department_id;
4. Given a table order_items with columns order_id,
product_id, and quantity, find the product that is ordered most
frequently.
Ans. SELECT product_id, SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id
ORDER BY total_quantity DESC
LIMIT 1;
5. Given a table sales with columns date and amount,
calculate the running total of sales over time.
Ans.
SELECT date, amount, SUM(amount) over (ORDER BY date) as running_total FROM sales ORDER BY d
ate;
6 You have a table named Employee with columns employee_id,
department_id, and salary. Find the department with the highest
average salary.
Ans.
SELECT department_id, AVG(salary) AS average_salary FROM employees GROUP BY department_id O
RDER BY average_salary DESC LIMIT 1;
7. Given a table orders with columns order_id, product_id, and
customer_id, find the product that has been ordered the most
number of times.
Ans. SELECT product_id, COUNT(*) AS order_count
FROM orders
GROUP BY product_id
ORDER BY order_count DESC
LIMIT 1;
8 Given a table orders with columns order_id, customer_id,
and order_date, find the first and last order dates for each
customer.
Ans. SELECT
customer_id,
MIN(order_date) AS first_order_date,
MAX(order_date) AS last_order_date
FROM
orders
GROUP BY
customer_id;
9 Given a table orders with columns order_id, order_date,
and total_amount, calculate the average order value for each
day of the week.
Ans. SELECT
DAYNAME(order_date) AS day_of_week,
AVG(total_amount) AS average_order_value
FROM
orders
GROUP BY
day_of_week
ORDER BY
FIELD(day_of_week, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday');
