Beginner Challenges (Basic SELECT, WHERE, INSERT)

1. Find All Active Users

Table: users(id, name, status)

Task: Write a query to return all users with status = 'active'.

2. Add a New Product

Table: products(id, name, price)

Task: Write a query to insert a product called "Bluetooth Mouse" with a price of 20.99.

3. Get Orders from a Specific Year

Table: orders(id, customer_id, order_date)

Task: Fetch all orders placed in the year 2024.

4. List Employees Earning More than 50,000

Table: employees(id, name, salary)

Task: Write a query to list all employees with a salary greater than 50,000.

5. Top Customers by Number of Orders

Tables: customers, orders

Task: Return top 5 customers who have placed the most orders.

6. Get Product Sales Summary

Tables: products(id, name), order_items(product_id, quantity)

Task: Return each product with total quantity sold.

7. List Employees with Department Names

Tables: employees(department_id), departments(id, name)

Task: Show employee name with their department name using a JOIN.

8. Count Orders per Day

Table: orders(order_date)

Task: Group and count how many orders were made each day.

9. Second Highest Salary

Table: employees(id, name, salary)

Task: Write a query to return the second highest salary.

10. Customers with No Orders

Tables: customers, orders

Task: Return customers who haven't placed any orders.

11. Top 3 Products by Sales per Category

Tables: products(id, category_id), order_items(product_id, quantity)

Task: Return top 3 selling products per category using a window function.

12. Employee Classification

Table: employees(id, name, salary)

Task: Use CASE to categorize employees into:

• "Junior" if salary < 30,000
• "Mid" if salary between 30,000 and 70,000
• "Senior" if salary > 70,000

13. Find Users Who Ordered All Products

Tables: users, orders(user_id, product_id), products(id)

Task: Return users who have ordered every product at least once.

14. Running Total of Sales

Table: orders(order_date, amount)

Task: Return each order date with the running total of sales using a window function.

15. Most Recent Order for Each Customer

Tables: orders(customer_id, order_date)

Task: Return the latest order date for each customer.

16. Monthly Revenue Report

Table: orders(order_date, total_amount)

Task: Return total revenue for each month in 2023.

17. Find Gaps in Attendance

Table: attendance(student_id, date)

Task: Identify dates where a student missed attendance (based on gaps between rows).

18. Detect Duplicate Emails

Table: users(id, email)

Task: Return a list of duplicate emails.

19. Find Consecutive Days of Login

Table: logins(user_id, login_date)

Task: Return users who logged in 3 or more days in a row.

20. Get Products That Were Never Ordered

Tables: products, order_items

Task: Return products that don't appear in the order_items table.