MYSQL code

Ecommerce Application

Description

This project involves using various Database concepts to solve E$-$commerce business use cases. This requires skills like Database concepts, SQL Queries, etc

In this project, the MySQL database schema consists of the following tables:

Customers: stores customer's data.

Products: stores a list of scale model cars.

ProductLines: stores a list of product line categories.

Orders: stores sales orders placed by customers.

OrderDetails: stores sales order line items for each sales order.

Payments: stores payments made by customers based on their accounts

Employees: stores all employee information as well as the organization structure such as who reports to whom.

Offices: stores sales office data.

Ecommerce$-$application$-$schemapng.png

Exercise

Find the customers with high spending habits

You need to write a SQL Query in order to solve the following problem:

Find customerNames who have orders, and whose spending habits are strictly $>60$ percentile of customers. Use the orders and orderdetails table to calculate spending habits of each customer. Orders having a status of cancelled or disputed are not considered as spends. The final output will have $1$ column, customerName. The final output is to be ordered by customerName in ascending order.

Constraints

Time Limit: $1$ sec$($s$)$

Important Notes

Please note that sample data has already been loaded in a dedicated database for you. It is not being shared by anyone else.

In order to run the SQL Commands, please directly use table names in the queries and exclude the database name. Refer to some sample queries below for better understanding$($Assume there is a table $`$customer$`$ with columns $`$id$`$and $`$name$`$ already loaded in a database $`$user$`)$:

Write a SQL query to find the id of the customer whose name is 'ABC'.

The query that you need to put in the SQL Editor below would be: SELECT id from customer where name $=$ 'ABC'

Write a SQL query to find the id of the customer whose name starts with 'ABC'.

The query that you need to put in the SQL Editor below would be: SELECT id from customer where name LIKE 'ABC$\%\text{'}$