SQL- Cases

SQL QUESTIONS THAT I HAVE SOLVED FROM LEADCODE AND VARIOUS WEBSITE.

CASE 01 - Order Fulfillment

You are given two tables: products and orders. The products table contains information about each product, including the product ID and available quantity in the warehouse. The orders table contains details about customer orders, including the order ID, product ID, order date, and quantity requested by the customer. Write an SQL query to generate a report listing the orders that can be fulfilled based on the available inventory in the warehouse, following a first-come-first-serve approach based on the order date. Each row in the report should include the order ID, product name, quantity requested by the customer, quantity actually fulfilled, and a comments column as below: If the order can be completely fulfilled then 'Full Order',If the order can be partially fullfilled then 'Partial Order',If order can not be fulfilled at all then 'No Order'. DATA

SOLUTION

OUTPUT

CASE 02 - DYNAMIC PRICING

You are given a products table where a new row is inserted every time the price of a product changes. Additionally, there is a transaction table containing details such as order_date and product_ID for each order. Write an SQL query to calculate the total sales value for each product, considering the cost of the product at the time of the order date. DATA

SOLUTION

CASE 03 - Highly Paid Employees

You are given the data of employees along with their salary and department . Write an SQL to find list of employees who has salary greater than average employee salary of the company. However , While calculating the company average salary to compare with an employee salary do not consider salaries of that employee's department.

DATA

SOLUTION

CASE 04 - Zomato Customer Behaviour

solution -

output -

CASE 05 - Cricket Score Table

Solution -

Output -

SQL Questions with Easy - Medium level difficulty

Question 1

Namastekart, an e-commerce company, has observed a notable surge in return orders recently. They suspect that a specific group of customers may be responsible for a significant portion of these returns. To address this issue, their initial goal is to identify customers who have returned more than 50% of their orders. This way, they can proactively reach out to these customers to gather feedback.

Write an SQL to find list of customers along with their return percent (Round to 2 decimal places), display the output in ascending order of customer name.

Solution -