CREATE TABLE dimcustomer ( customerid int, customeraltid varchar(10) not null, c
ID: 3738931 • Letter: C
Question
CREATE TABLE dimcustomer
(
customerid int,
customeraltid varchar(10) not null,
customername varchar(50),
gender varchar(20),
PRIMARY KEY (customerid)
);
INSERT INTO dimcustomer(customerid,customeraltid,customername,gender) VALUES
(1,'IMI-001','Henry Ford','M'),
(2,'IMI-002','Bill Gates','M'),
(3,'IMI-003','Muskan Shaikh','F'),
(4,'IMI-004','Richard Thrubin','M'),
(5,'IMI-005','Emma Wattson','F');
CREATE TABLE dimproduct
(
productkey int,
productaltkey varchar(10) NOT NULL,
productname varchar(100),
productactualcost DECIMAL(10,2),
productsalescost DECIMAL(10,2),
PRIMARY KEY(productkey)
);
INSERT INTO dimproduct(productkey,productaltkey,productname,productactualcost, productsalescost) VALUES
(1,'ITM-001','Wheat Floor 1kg',5.50,6.50),
(2,'ITM-002','Rice Grains 1kg',22.50,24),
(3,'ITM-003','SunFlower Oil 1 ltr',42,43.5),
(4,'ITM-004','Nirma Soap',18,20),
(5,'ITM-005','Arial Washing Powder 1kg',135,139);
CREATE TABLE dimstore
(
storeid int,
storealtid varchar(10) NOT NULL,
storename varchar(100),
storelocation varchar(100),
city varchar(100),
state varchar(100),
country varchar(100),
PRIMARY KEY(storeid)
);
INSERT INTO dimstore(storeid, storealtid,storename,storelocation,city,state,country) VALUES
(1,'LOC-A1','X-Mart','S.P. RingRoad','Ahmedabad','Guj','India'),
(2,'LOC-A2','X-Mart','Maninagar','Ahmedabad','Guj','India'),
(3,'LOC-A3','X-Mart','Sivranjani','Ahmedabad','Guj','India');
CREATE TABLE dimsalesperson
(
salespersonid int,
salespersonaltid varchar(10) NOT NULL,
salespersonname varchar(100),
storeid int,
city varchar(100),
state varchar(100),
country varchar(100),
PRIMARY KEY(salespersonid)
);
INSERT INTO dimsalesperson(salespersonid, salespersonaltid,salespersonname,storeid,city,state,country ) VALUES
(1,'SP-DMSPR1','Ashish',1,'Ahmedabad','Guj','India'),
(2,'SP-DMSPR2','Ketan',1,'Ahmedabad','Guj','India'),
(3,'SP-DMNGR1','Srinivas',2,'Ahmedabad','Guj','India'),
(4,'SP-DMNGR2','Saad',2,'Ahmedabad','Guj','India'),
(5,'SP-DMSVR1','Jasmin',3,'Ahmedabad','Guj','India'),
(6,'SP-DMSVR2','Jacob',3,'Ahmedabad','Guj','India');
CREATE TABLE factproductsales
(
transactionid BIGINT AUTO_INCREMENT,
salesinvoicenumber int NOT NULL,
salesdatekey INT,
salesyearkey INT,
salesmonthkey INT,
salesdayKey INT,
storeid INT NOT NULL,
customerid INT NOT NULL,
productid INT NOT NULL,
salespersonid INT NOT NULL,
quantity float,
salestotalcost DECIMAL(10,2),
productactualcost DECIMAL(10,2),
PRIMARY KEY(transactionid)
);
AlTER TABLE factproductsales
ADD FOREIGN KEY (storeid) REFERENCES dimstore(storeid);
AlTER TABLE factproductsales
ADD FOREIGN KEY (customerid) REFERENCES dimcustomer(customerid);
AlTER TABLE factproductsales
ADD FOREIGN KEY (productid) REFERENCES dimproduct(productkey);
AlTER TABLE factproductsales
ADD FOREIGN KEY (salespersonid) REFERENCES dimsalesperson(salespersonid);
INSERT INTO factproductsales(salesinvoicenumber,salesdatekey,salesyearkey,salesmonthkey,salesdaykey,storeid,customerid,productid,salespersonid,quantity,productactualcost,salestotalcost) VALUES
(1,20130101,2013,01,01,1,1,1,1,2,11,13),
(1,20130101,2013,01,01,1,1,2,1,1,22.50,24),
(1,20130101,2013,01,01,1,1,3,1,1,42,43.5),
(2,20130101,2013,01,01,1,2,3,1,1,42,43.5),
(2,20130101,2013,01,01,1,2,4,1,3,54,60),
(3,20130101,2013,01,01,1,3,2,2,2,11,13),
(3,20130101,2013,01,01,1,3,3,2,1,42,43.5),
(3,20130101,2013,01,01,1,3,4,2,3,54,60),
(3,20130101,2013,01,01,1,3,5,2,1,135,139);
INSERT INTO factproductsales(salesinvoicenumber,salesdatekey,salesyearkey,salesmonthkey,salesdaykey,storeid,customerid,productid,salespersonid,quantity,productactualcost,salestotalcost) VALUES
(4,20130102,2013,01,02,1,1,1,1,2,11,13),
(4,20130102,2013,01,02,1,1,2,1,1,22.50,24),
(5,20130102,2013,01,02,1,2,3,1,1,42,43.5),
(5,20130102,2013,01,02,1,2,4,1,3,54,60),
(6,20130102,2013,01,02,1,3,2,2,2,11,13),
(6,20130102,2013,01,02,1,3,5,2,1,135,139),
(7,20130102,2013,01,02,2,1,4,3,3,54,60),
(7,20130102,2013,01,02,2,1,5,3,1,135,139);
INSERT INTO factproductsales(salesinvoicenumber,salesdatekey,salesyearkey,salesmonthkey,salesdaykey,storeid,customerid,productid,salespersonid,quantity,productactualcost,salestotalcost) VALUES
(8,20130103,2013,01,03,1,1,3,1,2,84,87),
(8,20130103,2013,01,03,1,1,4,1,3,54,60),
(9,20130103,2013,01,03,1,2,1,1,1,5.5,6.5),
(9,20130103,2013,01,03,1,2,2,1,1,22.50,24),
(10,20130103,2013,01,03,1,3,1,2,2,11,13),
(10,20130103,2013,01,03,1,3,4,2,3,54,60),
(11,20130103,2013,01,03,2,1,2,3,1,5.5,6.5),
(11,20130103,2013,01,03,2,1,3,3,1,42,43.5);
Write SQL queries for the following use cases:
a) What is the total profit of the product id 4?
b) What is the profit and name of the product id 4 in Jan 2 2013?
c) What is each product's total sales in Jan 1 2013?
d) Which product (id and name) has the highest sales in Jan 1 2013?
Explanation / Answer
a) select sum(costsalestotalcost-productactual) as Profit from factproductsales where productid=4;
Above query gives the profit of product 4.
b) select sum(costsalestotalcost-productactual) as Profit, productname from factproductsales f,product p where f.productid=p.productkey and to_char(salesdate,'dd-mon-yyyy')='02-jan-2013' and f.productid=4;
Above query gives each product name and profit of the given date.
c) select productid,sum(quantity),sum(saletotalcost) from factproductsales where to-char(salesdate,'dd-mon-yyyy')='01-jan-2013' group by productid;
Above query gives the total sales of each product in the given date.
d) select productid,productname,max(sum(saletotalcost) from factproductsales f,product p where f.productid=p.productkey and to-char(salesdate,'dd-mon-yyyy')='01-jan-2013' group by productid;
Above query retrieves the highest sale product information of the given date.