use jarvis;
select * from Location;
select * from fact;
select * from product;
--1) Display no of states from location table
select count(distinct state) as no_of_states from location
--2) How many products are of regular type?
select count(type) as no_of_regular_products from product
where product.type = 'regular';
--3) how much money has been spend on marketing for product id 1
select sum(marketing) as total from fact
where ProductId = 1;
--4) What is the minimum sales of a product ?
select min(sales) as min_sales from fact;
--5) Display max Cost of Good Sold(COGS)
select max(cogs) as max_cogs from fact;
--6) Display the Details of the productid where product type is coffee
select productid from Product
where Product_Type = 'coffee';
--7) Display the details where total_expenses is greater than 40
select * from fact
where Total_Expenses > 40;
--8) What is the average sales in Area_Code 719 ?
select avg(sales) as avg_sales_of_areacode719 from fact
where Area_Code = 719;
--9) Find out the total profit generated by Colorado state
select
sum(profit) as total_profit
from fact
inner join location on fact.Area_Code = location.Area_Code
where location.state = 'colorado';
--10) Display the average inventory for each product id.
select productid, avg(inventory) as avg_inventory from fact
group by ProductId
order by ProductId asc;
--11) Display state in a sequential order in a location table
select * from location order by state desc;
--12) Display the average budget margin where average budget margin should be
greater than100
select productid, avg(budget_margin) as avg_budget_margin from fact
group by ProductId
having avg(budget_margin) > 100;
--13) What is the total sales done on date 2010-01-01
select sum(sales) as total_sales from fact
where date = '2010-01-01';
--14) Display the average total expense of each product id on individual date
select ProductId, date, avg(total_expenses) as avg_total_expenses from fact
group by date, ProductId;
/*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,
l.state,
l.area_code
from fact f inner join product p on f.productid = p.productid
inner join location l on f.area_code = l.area_code;
--16) Display the rank without any gap to show the Sales wise rank.
select sales, dense_rank() over (order by sales asc) as rank from fact;
--17) Find the State wise Profit and Sales.
select
l.state,
sum(profit) as profit,
sum(sales) as sales
from fact f inner join location l on f.Area_Code = l.Area_Code
group by l.state;
--19) Find the State wise Profit and Sales along with the Product Name
select
l.state,
sum(f.profit) as profit,
sum(f.sales) as sales,
p.product
from fact f inner join location l on f.area_code = l.area_code
inner join product p on p.productid = f.ProductId
group by l.state, p.product;
--19) If there is an increase in sales of 5%. Calculate the increased sales.
select productid, sales, (sales + sales*5/100) as increased_sale from fact
--20) Find the maximum profit along with the Product id and Product Type.
select
fact.productid,
max(profit) as max_profit,
product.Product_Type
from fact inner join product on fact.ProductId = Product.ProductId
group by fact.ProductId, Product_Type;
/*21) Create a Stored Procedure to fetch the result
according to the product type fromProduct.*/
CREATE PROCEDURE PTYPE @PROD_TYP VARCHAR(20) AS
SELECT * FROM Product
WHERE Product_Type = @PROD_TYP
EXEC PTYPE @PROD_TYP = 'COFFEE';
/*22) Write a query by creating a condition in which if the total expenses
is less than 60 thenit isaprofit or else loss*/
select total_expenses, iif(total_expenses > 60, 'profit', 'loss') as profitorloss
from fact;
/* 23) Give the total weekly sales value with the Date and productid details.
Use roll-up to pull the data in hierarchical order.*/
SELECT DATE, PRODUCTID, SUM(SALES) AS TOTAL_SALES FROM FACT
GROUP BY DATE, PRODUCTID WITH ROLLUP;
/*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 a
particular product typebasedupon the user’s preference.*/
CREATE FUNCTION PRO_TYPE(@PROD_TYP VARCHAR(20))
RETURNS TABLE
AS
RETURN
(
SELECT * FROM Product WHERE Product_Type = @PROD_TYP
)
SELECT * FROM DBO.PRO_TYPE('COFFEE');
/*26) Change the product type from coffee to tea
where product id is 1 and undo it.*/
begin tran
UPDATE Product
SET Product_Type = 'tea'
WHERE ProductId = 1;
ROLLBACK
SELECT * FROM PRODUCT;
/*27 Display the Date, productid and sales
where total expenses are between 100 to 200.*/
SELECT DATE, PRODUCTID, SALES FROM FACT
WHERE Total_Expenses BETWEEN 100 AND 200;
/*28) Delete the records in the product table for regular type.*/
BEGIN TRAN
DELETE FROM PRODUCT WHERE TYPE = 'REGULAR'
ROLLBACK;
/*29)Display the ASCII value of the fifth character from the column product.*/
SELECT ASCII(SUBSTRING(Product, 5, 1)) AS ASCII_VALUE FROM PRODUCT;
