Use Casestudy1;
Select * from Product;
Select * from fact;
Select * from location;
---- Display the number of states present in the LocationTable.
select COUNT(distinct state)as StateCount from Location;
---- How many products are of regular type?
select count(type) as Count_Grouptype,type from product group by type having
type='Regular';
select COUNT(PRODUCT) as Count_Type from product where type='regular';
---- How much spending has been done on marketing of product ID 1?
select SUM(Marketing) as TotalSpend_Marketing, ProductId from fact group by
ProductId having ProductId=1;
select Sum(Marketing) as TotalSpend_Marketing from fact where ProductId=1;
---- What is the minimum sales of a product?
Select MIN(sales) as Min_Sale from fact; --- wrong
select MIN([Link]) as Min_Sale,[Link] from Product P join Fact F on
[Link]=[Link] GROUP BY [Link]
---- Display the max Cost of Good Sold (COGS).
Select MAX(COGS)as Max_COGS FROM Fact;
---- Display the details of the product where product type is coffee.
Select * from Product where Product_Type='Coffee';
---- Display the details where total expenses are greater than 40.
Select * from fact where Total_Expenses>40 order by Total_Expenses desc;
-----What is the average sales in area code 719?
Select AVG(SALES) as Avg_Sales, Area_Code from fact group by Area_Code having
Area_Code=719;
Select AVG(SALES) as Avg_Sales from fact where Area_Code=719;
---- Find out the total profit generated by Colorado state.
Select SUM([Link]) as Total_Profit,[Link] from fact F join Location L on
F.Area_Code=L.Area_Code group by [Link] having [Link]='Colorado';
Select SUM([Link]) as Total_Profit from fact F join Location L on
F.Area_Code=L.Area_Code where [Link]='Colorado';
---- Display the average inventory for each product ID.
Select AVG([Link]) as AVG_Inventory,[Link] from Product P join Fact F on
[Link]=[Link] GROUP BY [Link]
Select AVG([Link]) as AVG_Inventory,[Link] from Product P join Fact F on
[Link]=[Link] GROUP BY [Link] order by [Link];
Select AVG(Inventory) as AVG_Inventory,ProductId from Fact GROUP BY ProductId order
by ProductId;
---- Display state in a sequential order in a Location Table.
Select *from Location order by State;
Select distinct(State) from Location order by state;
-----Display the average budget of the Product where the average budget margin
should be greater than 100.
Select * from fact;
Select AVG(Budget_Margin) as AVG_Budget_Margin from Fact group by ProductId;
Select AVG(Margin) as AVG_Budget_Product, AVG(Budget_Margin) as
AVG_Budget_Margin,ProductId from Fact group by ProductId having
AVG(Budget_Margin)>100
--- What is the total sales done on date 2010-01-01?
Select SUM(Sales) as Total_Sales from Fact where Date='2010-01-01';
--- Display the average total expense of each product ID on an individual date.
Select AVG(Total_Expenses) as Avg_Total_Expenses,ProductId,date from Fact group by
ProductId,date order by ProductId;
---- Display the table with the following attributes such as date,
productID,product_type, product, sales, profit, state, area_code.
Select
[Link],[Link],P.Product_Type,[Link],[Link],[Link],[Link],L.area_Code
from Product P join fact F on [Link]=[Link] join Location L on
F.Area_Code=L.Area_Code;
--- Display the rank without any gap to show the sales wise rank.
Select PRODUCTId,Sales,Profit, DENSE_RANK() over (order by Sales desc) as
Sales_Rank FROM fact;
--- Find the state wise profit and sales.
select * from fact;
select * from location;
Select * from product;
Select Sum(Profit) as Total_Profit,Sum(Sales) as Total_Sales, State from Fact join
Location on Fact.Area_Code=Location.Area_Code group by State;
---- Find the state wise profit and sales along with the product name.
Select Sum(Profit) as Total_Profit,Sum(Sales) as Total_Sales, [Link], [Link]
from Fact F join Location L on F.Area_Code=L.Area_Code join product P on
[Link] = [Link] group by [Link],[Link];
---- If there is an increase in sales of 5%, calculate the increasedsales.
