Case study

-

create database casestudy;

use casestudy;

-----------------------------------------------------------------------------------

select top 10 * from fact

select top 10 * from location

select top 10 * from product

----------------------------------------------------------------------------------

/* Problem Statement:

 You are a database administrator. You want to use the data to answer a few

 questions about your customers, especially about the sales and profit coming

 from different states, money spent in marketing and various other factors such as

 COGS (Cost of Goods Sold), budget profit etc. You plan on using these insights

 to help find out which items are being sold the most. You have been provided

 with the sample of the overall customer data due to privacy issues. But you hope

 that these samples are enough for you to write fully functioning SQL queries to

 help answer the questions.*/

---------------------------------------------------------------------------------------

 /* Dataset:

 The 3 key datasets for this case study:


 a. FactTable: The Fact Table has 14 columns mentioned below and 4200

 rows. Date, ProductID, Profit, Sales, Margin, COGS, Total Expenses,

 Marketing, Inventory, Budget Profit, Budget COGS, Budget Margin, Budget

 Sales, and Area Code

 Note: COGS stands for Cost of Goods Sold


 b. ProductTable: The ProductTable has four columns named Product Type,

 Product, ProductID, and Type. It has 13 rows which can be broken down

 into further details to retrieve the information mentioned in theFactTable.


 c. LocationTable: Finaly, the LocationTable has 156 rows and follows a

 similar approach to ProductTable. It has four columns named Area Code,

 State, Market, and Market Size.*/

----------------------------------------------------------------------------------------

--Tasks to be performed:


-- 1. Display the number of states present in theLocationTable.

select count(distinct state) [No of States] from Location;

---------------------------------------------------------------------------------------

-- 2. How many products are of regular type?

select count(product) from product where type='Regular';

----------------------------------------------------------------------------------------

-- 3. How much spending has been done on marketing of product ID1?

select sum(marketing) [total_marketing_id1] from fact where ProductId=1;

-------------------------------------------------------------------------------------

-- 4. What is the minimum sales of a product?

select min(sales) as [minimum sale ] from fact;

select * from fact where sales=(select min(sales) from fact);

-------------------------------------------------------------------------------------

--5. Display the max Cost of Good Sold (COGS)

select max(COGS) as [Max Cost of good sold] from fact;

--------------------------------------------------------------------------------------

-- 6 Display the details of the product where product type is coffee

select * from Product p

left join fact f on p.ProductId=f.ProductId

where p.[Product Type]='Coffee';

---------------------------------------------------------------------------------------

-- 7.  Display the details where total expenses are greater than 40

select * from fact where [Total Expenses]>40 order by [Total Expenses];

---------------------------------------------------------------------------------------

-- 8. What is the average sales in area code 719?

select round(avg(Sales),2)[Average sales] from fact where [Area Code]=719;

-------------------------------------------------------------------------------------

-- 9. Find out the total profit generated by Colorado state.

select sum(f.Profit)as [Total Profit Colorado] from Location l

left join fact f on l.[Area Code]=f.[Area Code]

where l.State='Colorado';

------------------------------------------------------------------------------------

-- 10. Display the average inventory for each product ID

select ProductId,round(avg(Inventory),2) [average inventory] from fact group by ProductId order by ProductId;

-------------------------------------------------------------------------------------

-- 11.Display state in a sequential order in a LocationTable

select distinct state from Location order by state;

--------------------------------------------------------------------------------------

-- 12. Display the average budget of the Product where the average budget margin should be greater than 100

with cte as (

select distinct p.ProductId,Product,round(avg(f.[Budget Margin]) over(partition by p.productid),2) [average budget]

from Product p

left join fact f on p.ProductId=f.ProductId)

select * from cte where [average budget]>100 order by ProductId;

---------------------------------------------------------------------------------------

-- 13. What is the total sales done on date 2010-01-01?

select convert(nvarchar(15),date,23)[Date],sum(Sales) [total sales] from fact

where date='2010-01-01'

group by Date ;

----------------------------------------------------------------------------------------

-- 14.  Display the average total expense of each product ID on an individual date.

select distinct ProductId,

convert(nvarchar(15),date,23)[Date],

avg([Total Expenses])over(partition by productid,date)[avg total expense] 

from fact;

----------------------------------------------------------------------------------------

--15.  Display the table with the following attributes such as date, productID, product_type, product, sales, profit, state, area_code.

select f.Date,f.ProductId,p.[Product Type],p.Product,f.Sales,f.Profit,l.State,l.[Area Code]

from Product p

left join fact f on p.ProductId=f.ProductId

left join Location l on f.[Area Code]=l.[Area Code];

------------------------------------------------------------------------------------------

--16. Display the rank without any gap to show the sales wise rank

with cte as(

select f.ProductId,p.Product,sum(f.Sales)[total sales]

from fact f

left join Product p on f.ProductId=p.ProductId

group by f.Productid,p.Product)

select *,DENSE_RANK()over(order by [total sales]desc)[sales wise rank] from cte;

-------------------------------------------------------------------------------------------

-- 17.  Find the state wise profit and sales

Select l.State,sum(f.Profit)[total profit],sum(f.Sales)[total sales] from Location l

left join fact f on l.[Area Code]=f.[Area Code]

group by l.State

order by state

------------------------------------------------------------------------------------------

-- 18.Find the state wise profit and sales along with the productname

Select l.State,p.product,sum(f.Profit)[total profit],sum(f.Sales)[total sales] from Location l

left join fact f on l.[Area Code]=f.[Area Code]

left join Product p on f.ProductId=p.ProductId

group by l.State,p.Product

order by state,p.Product

-------------------------------------------------------------------------------------------

-- 19. If there is an increase in sales of 5%, calculate the increased sales.

select *,Sales,Sales*1.05[increased sales] from fact

--------------------------------------------------------------------------------------------

--20. Find the maximum profit along with the product ID and producttype.

select p.ProductId,p.[Product Type],f.Profit from Product p

left join fact f on p.ProductId=f.ProductId

where f.Profit=(select max(Profit)from fact)

---------------------------------------------------------------------------------------------

-- 21. Create a stored procedure to fetch the result according to the product type from Product Table.

create procedure sp_fetch_product_by_type(@input varchar(50))

as

begin

select * from Product

where [Product Type]=@input

end;

exec sp_fetch_product_by_type @input='coffee'

-----------------------------------------------------------------------------------------------

-- 22. Write a query by creating a condition in which if the total expenses is less than 60 then it is a profit or else loss

select *,

case

when [Total Expenses]<60 then 'Profit'

else 'Loss'

end [profit/loss]

from fact

-----------------------------------------------------------------------------------------------

-- 23.Give the total weekly sales value with the date and product ID details. Use roll-up to pull the data in hierarchical order.

select datepart(year,date)[year],DATEPART(WEEK,date)[week],SUM(Sales)[total sales],ProductId from fact

GROUP BY 

rollup(datepart(year,date),DATEPART(WEEK,DATE),ProductId)

order by datepart(year,date),DATEPART(WEEK,DATE),ProductId

-----------------------------------------------------------------------------------------------

-- 24. Apply union and intersection operator on the tables which consist of attribute area code.

select [Area Code] from fact

union

select [Area Code] from Location


select [Area Code] from fact

intersect

select [Area Code] from Location

------------------------------------------------------------------------------------------------

--25. Create a user-defined function for the product table to fetch aparticular product type based upon the user s preference.

create function fn_by_product_type(@producttype nvarchar(50))

returns table

as

return

(select * from Product

where [Product Type]=@producttype)


Select * from fn_by_product_type('Coffee')

--------------------------------------------------------------------------------------------------

--26. Change the product type from coffee to tea where product ID is 1and undo it.

begin transaction;


update Product

set [Product Type]='tea'

where ProductId=1;


select * from Product


rollback;


select * from Product

-----------------------------------------------------------------------------------------

-- 27.Display the date, product ID and sales where total expensesare between 100 to 200.

select date,ProductId,Sales,[Total Expenses] from fact

where [Total Expenses] between 100 and 200;

-----------------------------------------------------------------------------------------

-- 28. Delete the records in the Product Table for regulartype

begin transaction;


delete from Product

where Type='Regular';


select * from Product;


rollback;


select * from Product;

---------------------------------------------------------------------------------------------

-- 29. Display the ASCII value of the fifth character from the column Product.

select Product,SUBSTRING(product,5,1)[5th character],ASCII(substring(product,5,1))[ascii of 5th character] from Product

----------------------------------------------------------------------------------------------



Comments

Post a Comment

Popular posts from this blog

Assignment module 1&2

-
 create database assignment1; use assignment1; /*Problem Statement:  ABC Fashion is a leading retailer with a vast customer base and a team of dedicated sales  representatives. They have a Sales Order Processing System that helps manage customer  orders and interactions.*/  -- Salesman table creation  CREATE TABLE Salesman (  SalesmanId INT,  Name VARCHAR(255),  Commission DECIMAL(10, 2),  City VARCHAR(255),  Age INT  );  --Salesman table record insertion  INSERT INTO Salesman (SalesmanId, Name, Commission, City, Age)  VALUES  (101, 'Joe', 50, 'California', 17),  (102, 'Simon', 75, 'Texas', 25),  (103, 'Jessie', 105, 'Florida', 35),  (104, 'Danny', 100, 'Texas', 22),  (105, 'Lia', 65, 'New Jersey', 30);  -- Customer table creation  CREATE TABLE Customer (  SalesmanId INT,  CustomerId INT,  CustomerName VARCHAR(255),  PurchaseAmount INT,  );  -- Cust...
Read more

Assignment Module 3&4

-
create database assignment2 use assignment2 select * from jomato -------------------------------------------------------------------------------------------------------- /* Dataset: Jomato  About the dataset:  You work for a data analytics company, and your client is a food delivery platform similar to  Jomato. They have provided you with a dataset containing information about various  restaurants in a city. Your task is to analyze this dataset using SQL queries to extract valuable  insights and generate reports for your client. */ -----------------------------------------------------------------------------------------------------   --Tasks to be performed:  -- 1.Create a user-defined functions to stuff the Chicken into ‘Quick Bites’. Eg: ‘Quick Chicken Bites’  create function fn_modify_restaurant_type(  @oldtype nvarchar(100),  @newtype nvarchar(100)  )  returns table  as  return ( select Orderid,  ...
Read more

SQL_chapter1_assignment

-
 --1.Find out the selling cost AVG for packages developed in Pascal. Select AVG(SCOST) as [Average selling cost] From SOFTWARE Where DEVELOPIN='PASCAL'; --2.Display Names, Ages of all Programmers. select pname as Names, DATEDIFF(YY,[DOB],GETDATE()) as [Age] from programmer; --3.Display the Names of those who have done the DAP Course. Select PNAME as Names, COURSE from STUDIES where COURSE = 'DAP'; --4.Display the Names and Date of Births of all Programmers Born in January. Select PNAME as Names,DOB, DATENAME(MM, DOB) as [Month] from PROGRAMMER where DATENAME(MM, DOB) = 'January' --5.Display the Details of the Software Developed by Ramesh select * from SOFTWARE where PNAME='Ramesh' --6. Display the Details of Packages for which Development Cost have been recovered. select * , (SCOST*SOLD) as [Total Sale] from SOFTWARE  where (SCOST*SOLD) > DCOST --7. Display the details of the Programmers Knowing C select pname from PROGRAMMER where PROF1='C' o...
Read more