IndianTechnoEra: Level Up Your SQL Skills: A Challenge with Solutions

Are you ready to put your SQL knowledge to the test? SQL (Structured Query Language) is a powerful tool for managing and manipulating relational databases. 

Whether you're a beginner or an experienced developer, there's always room to sharpen your SQL skills. 

In this challenge, we present you with a series of SQL questions ranging from basic to advanced levels. Each question is followed by a solution to help you understand and master SQL concepts. Let's dive in!

Basic Level:

1. Question 1:

   Consider a table named Students with the following schema:

                   Students Table:
                   - id (INT)
                   - name (VARCHAR)
                   - age (INT)
               

   Write an SQL query to retrieve the names of all students.

   Solution:

   SELECT name FROM Students;

2. Question 2:

   Suppose you have a table named Orders with columns order_id and order_date. Write an SQL query to retrieve all orders placed after January 1, 2023.

   Solution:

   SELECT * FROM Orders WHERE order_date > '2023-01-01';

Intermediate Level:

1. Question 1:

   Consider two tables, Employees and Departments, with the following schemas:

                   Employees Table:
                   - emp_id (INT)
                   - emp_name (VARCHAR)
                   - department_id (INT)
                   
                   Departments Table:
                   - department_id (INT)
                   - department_name (VARCHAR)
               

   Write an SQL query to retrieve the names of employees along with the names of their respective departments.

   Solution:

                   SELECT e.emp_name, d.department_name
                   FROM Employees e
                   INNER JOIN Departments d ON e.department_id = d.department_id;
               

2. Question 2:

   Suppose you have a table named Orders with columns order_id, order_date, and total_amount. Write an SQL query to find the total amount of orders placed in each month of the year 2023.

   Solution:

                   SELECT MONTH(order_date) AS month, SUM(total_amount) AS total_order_amount
                   FROM Orders
                   WHERE YEAR(order_date) = 2023
                   GROUP BY MONTH(order_date);
               

3. Question 3: Product Sales per City

   For each pair of city and product, return the names of the city and product, as well the total amount spent on the product to 2 decimal places. Order the result by the amount spent from high to low then by city name and product name in ascending order.

   Solution:

   SELECT ci.city_name, pr.product_name, ROUND(SUM(ii.line_total_price), 2) AS tot
   FROM city ci, customer cu, invoice i, invoice_item ii, product pr
   WHERE ci.id = cu.city_id AND cu.id = i.customer_id AND i.id = ii.invoice_id AND ii.product_id = pr.id
   GROUP BY ci.city_name, pr.product_name
   ORDER BY tot DESC, ci.city_name, pr.product_name;
   

4. Question 4: Customer Spending

   List all customers who spent 25% or less than the average amount spent on all invoices. For each customer, display their name and the amount spent to 6 decimal places. Order the result by the amount spent from high to low.

   Solution:

   SELECT c.customer_name, CAST(SUM(i.total_price) AS DECIMAL(9,6)) AS total
   FROM customer c
   INNER JOIN invoice i ON c.id = i.customer_id
   GROUP BY c.customer_name
   HAVING SUM(i.total_price) <= 0.25 * (
     SELECT AVG(total_price) AS avg_total_price
     FROM invoice
   );
   

Advanced Level:

1. Question 1:

   Consider a table named Transactions with columns transaction_id, amount, and transaction_date. Write an SQL query to calculate the total amount of transactions for each day of the week (Monday, Tuesday, etc.) for the year 2023.

   Solution:

                   SELECT DAYOFWEEK(transaction_date) AS day_of_week, SUM(amount) AS total_amount
                   FROM Transactions
                   WHERE YEAR(transaction_date) = 2023
                   GROUP BY DAYOFWEEK(transaction_date);
               

2. Question 2:

   Suppose you have a table named Inventory with columns product_id, quantity, and purchase_date. Write an SQL query to find the top 5 products with the highest total quantities sold.

   Solution:

                   SELECT product_id, SUM(quantity) AS total_quantity_sold
                   FROM Inventory
                   GROUP BY product_id
                   ORDER BY total_quantity_sold DESC
                   LIMIT 5;
               

Mastering SQL requires practice and understanding of fundamental concepts. By attempting these SQL challenges, you can strengthen your SQL proficiency and tackle more complex database tasks with confidence. Keep exploring, keep learning, and keep coding! Happy querying!