Database|MySQL] Write SQL queries for the following use cases: 1)What are the to

Question

Database|MySQL] Write SQL queries for the following use cases:

1)What are the total sales of each store?

2)Which store location has the highest sales?

3)How many distinct customers does the X-Mart have in 2013?

4)Which gender of customers buys less in 2013?

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);

Explanation / Answer

Hi,

Please find below details

1)What are the total sales of each store?

select dm.storeid, sum(fa.salestotalcost) total
from dimstore dm, FACTPRODUCTSALES fa where dm.storeid = fa.storeid group by dm.storeid;

2)Which store location has the highest sales?

SELECT DM.STORELOCATION FROM dimstore DM, (
SELECT DM.STOREID, SUM(FA.QUANTITY) MAX
FROM DIMSTORE DM, FACTPRODUCTSALES FA WHERE DM.STOREID = FA.STOREID GROUP BY DM.STOREID ORDER BY MAX DESC) INN WHERE DM.STOREID = INN.STOREID
AND ROWNUM = 1;

3)How many distinct customers does the X-Mart have in 2013?

SELECT COUNT(DISTINCT DC.CUSTOMERID) CUSTOMER FROM FACTPRODUCTSALES FA, DIMSTORE DM, DIMCUSTOMER DC WHERE FA.STOREID = DM.STOREID
AND FA.CUSTOMERID = DC.CUSTOMERID AND DM.STORENAME = 'X-Mart' AND FA.SALESYEARKEY = '2013';

4)Which gender of customers buys less in 2013?

SELECT DM.GENDER FROM DIMCUSTOMER DM,(
SELECT CUSTOMERID, SUM(QUANTITY) QU FROM FACTPRODUCTSALES WHERE SALESYEARKEY = '2013' GROUP BY CUSTOMERID ORDER BY QU) INN
WHERE DM.CUSTOMERID = INN.CUSTOMERID AND ROWNUM = 1;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.