All language subtitles for 2. Case Study Part 2 - How SQL is Used

The first thing the analyst must know is the database and the data stored in it.

Now our company is storing data in three tables.

First is the customers table in this table.

Each registering customer is assigned a unique customer ID and against this ID, all other customer

details such as customer name, age, city, state of residence, etc. are being stored.

Second is the products table.

In this table, our supply chain team maintains a list of all products we are selling.

With this.

They also stored the name, subcategory and category of each product.

Each product is also assigned a unique ID called Product ID.

Third is the transaction table or the sales table.

All its transactions are stored into it.

It has product ID of product being sold, it has customer ID of customer who is buying it.

And it has other details such as order, date, ship, date, selling, price and discount, etc..

Let us go and look at these tables first.

The first query.

Get the result in the window below.

The customer table as this structure in the columns, it has column ID, column name, segment, age,

country, city, state, postal code and the region.

In the rose.

It has all the records of different customers.

Let us look at the product table.

It has only four columns product ID, category subcategory and the product name.

And each product is in the different rows.

That's the third query to look at the sales table.

In this exhibit.

We have lots of rows and columns.

In the columns you can see we have customer ID also, this is the ID of customer who is ordering it.

We have product ID also.

This is the product being ordered.

We have other details, such as sales value, quantity ordered, discount given and the profit earned.

Now, let us see if we can answer the questions of our colleagues.

Now, the marketing manager is asking us to provide the number of customers belonging to the three age

groups in all the four regions.

To do this, I have this query written.

If I run this query.

I get the following result.

You can see that in every region except south Category two as the maximum count.

These are the people between the age of 36 to 54, whereas in south category three has maximum count.

So marketing managers can now choose appropriate marketing channel in South region to focus on customers

who have more than 56 years of age in all the three other regions.

The marketing manager can go ahead with a common marketing channel aimed at middle aged customer.

Let us see what ops manager is asking us.

He wants to find out the top selling products of East and least selling products of South Region.

So we go to the database.

I will write and run the corresponding queries.

First to get the top selling products of East.

We run this query.

You can see these are the top five selling products of East region.

The operations manager should be ordering these five products since these are high selling products

in the East region.

Now let us run the second query to find out the least selling products of South.

We can see that these are the five products with minimum sales.

The operations manager can recall these products to make some space in the South warehouse.

Next, let us see what finance manager is asking us.

He wants the total revenue loss from discounts and the comparison of discounts given versus revenue

generated by each product.

So we write the corresponding query and run it.

If I select this first query and run it.

We get that 322,000 units is the total revenue loss because of the discount given.

When I select and run the second query.

We find out the sales of each product in the revenue column.

We find out the discount given for each product in the discount column.

Indeed.

Each of these two.

I've ordered it in the descending order of this ratio, which means that for the first product, we

have given four times the discount than the revenue it has generated.

Similarly, the situation with the next few product where we have given more discount than the revenue

that particular product was generating, the finance manager needs to study these products and decide

either to raise the price of these products or to discontinue the coupon or to discontinue the sale

of this product altogether.

You can see that by writing these few lines, which seem to be in simple English language, you are

able to get meaningful information out of your data.

We will learn everything about these queries in the videos to come, and I assure you that it is simple

and you will be able to write complex queries after completing the course.

But before starting with query straightaway, let's first learn some basic theory of data management.

For this.

See you in the next section.