Scribd Logo
Upload

Welcome to Scribd!

  • 2K views

    Case Study 1 Solution

    Uploaded by

    Vishnu Sharma
    The document contains 29 SQL queries that analyze data from location, product, and fact tables. The queries include finding the number of states and regular products, total marketing spend for a product, minimum/maximum sales and costs, average sales and expenses by area code and date, total profit by state, and hierarchical sales totals with rollup. Joins are performed between the tables and aggregate functions like count, sum, avg, and rank are used. Stored procedures, user-defined functions, transactions with commit and rollback are also demonstrated.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Case Study 1 Solution

    Uploaded by

    Vishnu Sharma
    2K views4 pages
    The document contains 29 SQL queries that analyze data from location, product, and fact tables. The queries include finding the number of states and regular products, total marketing spend for a product, minimum/maximum sales and costs, average sales and expenses by area code and date, total profit by state, and hierarchical sales totals with rollup. Joins are performed between the tables and aggregate functions like count, sum, avg, and rank are used. Stored procedures, user-defined functions, transactions with commit and rollback are also demonstrated.

    Original Title

    case study 1 solution

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains 29 SQL queries that analyze data from location, product, and fact tables. The queries include finding the number of states and regular products, total marketing spend for a product, minimum/maximum sales and costs, average sales and expenses by area code and date, total profit by state, and hierarchical sales totals with rollup. Joins are performed between the tables and aggregate functions like count, sum, avg, and rank are used. Stored procedures, user-defined functions, transactions with commit and rollback are also demonstrated.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    2K views4 pages

    Case Study 1 Solution

    Uploaded by

    Vishnu Sharma
    The document contains 29 SQL queries that analyze data from location, product, and fact tables. The queries include finding the number of states and regular products, total marketing spend for a product, minimum/maximum sales and costs, average sales and expenses by area code and date, total profit by state, and hierarchical sales totals with rollup. Joins are performed between the tables and aggregate functions like count, sum, avg, and rank are used. Stored procedures, user-defined functions, transactions with commit and rollback are also demonstrated.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    You are on page 1of 4

    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;

    You might also like