SQL - Assignment Part 1

 

 

SQL Assignment Part 1

 

Let's consider two tables: "Customers" and "Orders".

 

CustomerID | CustomerName   | City ----------------------------------- 1          | John Doe       | New York 2          | Jane Smith     | Los Angeles 3          | David Johnson  | Chicago

 

 

OrderID | CustomerID | OrderDate ------------------------------- 1       | 1          | 2023-05-01 2       | 2          | 2023-05-02 3       | 1          | 2023-05-03 4       | 3          | 2023-05-04 5       | 2          | 2023-05-05

 

 

Now,

1. Retrieve all orders with customer information (CustomerID, CustomerName, City).
2. Retrieve all customers who have placed an order.
3. Retrieve the total number of orders for each customer.
4. Retrieve the customer(s) who placed the earliest order.
5. Retrieve the customer(s) who placed the latest order.
6. Retrieve the order(s) with the highest order ID.
7. Retrieve the customer(s) who have never placed an order.
8. Retrieve the customer(s) who have placed more than two orders.
9. Retrieve the order(s) placed on a specific date.
10. Retrieve the customer(s) who have placed orders in both New York and Los Angeles.
11. Retrieve the customer(s) who have placed orders in at least three different cities.
12. Retrieve the customer(s) who have placed orders in all cities.
13. Retrieve the customer(s) who have placed orders in the same city more than once.
14. Retrieve the customer(s) who have placed orders on consecutive days.
15. Retrieve the order(s) placed by customers whose names contain the word "Smith".
16. Retrieve the order(s) placed by customers whose names start with the letter "J".
17. Retrieve the order(s) placed by customers whose names end with the letter "n".
18. Retrieve the customer(s) who have placed orders after a specific date.
19. Retrieve the customer(s) who have placed orders before a specific date.
20. Retrieve the customer(s) who have placed orders between two specific dates.
21. Retrieve the customer(s) who have placed an order and their order count.
22. Retrieve the customer(s) who have placed an order and the total amount spent on orders.
23. Retrieve the customer(s) with their latest order information (OrderID, OrderDate).
24. Retrieve the customer(s) with the average order count.
25. Retrieve the customer(s) with the maximum and minimum order dates.
26. Retrieve the customer(s) who have placed an order in every month of a given year.
27. Retrieve the customer(s) who have placed orders in more than three different months.
28. Retrieve the customer(s) who have placed orders on weekdays (Monday to Friday).
29. Retrieve the customer(s) who have placed an order with the highest total order amount.
30. Retrieve the customer(s) who have placed an order with the lowest total order amount.
31. Retrieve the customer(s) who have placed an order with a total order amount above the average.
32. Retrieve the customer(s) who have placed an order with a total order amount below the average.
33. Retrieve the customer(s) who have placed an order immediately after another order.
34. Retrieve the customer(s) who have placed an order immediately before another order.
35. Retrieve the customer(s) who have placed orders in all consecutive months.
36. Retrieve the customer(s) who have placed an order on the same date as another customer.
37. Retrieve the customer(s) who have placed an order on a date that no other customer has.
38. Retrieve the customer(s) who have placed an order on a date that all other customers have placed an order.
39. Retrieve the customer(s) who have placed an order with the highest number of items.
40. Retrieve the customer(s) who have placed an order with the lowest number of items.
41. Retrieve the customer(s) who have placed an order with at least one item that costs more than a specific amount.
42. Retrieve the customer(s) who have placed an order with all items costing less than a specific amount.
43. Retrieve the customer(s) who have placed an order containing items from at least three different categories.
44. Retrieve the customer(s) who have placed an order containing items from all available categories.
45. Retrieve the customer(s) who have placed an order where the total order amount is equal to the sum of the individual item prices.
46. Retrieve the customer(s) who have placed an order with the highest average item price.
47. Retrieve the customer(s) who have placed an order with the lowest average item price.
48. Retrieve the customer(s) who have placed an order with the highest number of distinct items.
49. Retrieve the customer(s) who have placed an order with the lowest number of distinct items.
50. Retrieve the customer(s) who have placed an order with the longest time interval between two consecutive orders