DATABASE MariaDB
mariadb on podman
podman pull mariadb
podman run -p 127.0.0.1:3306:3306 --name some-mariadb -e MARIADB_ROOT_PASSWORD=redhat -d mariadb
podman cp Project.sql c3c6c76bd38a:/opt/
podman exec -it c3c6c76bd38a /bin/bash
Create DB and Tables
```- mysql -u root -p redhat
- CREATE DATABASE Project;
- show databases;
- use Project;
- show tables;
- ^C
- mysql -u root -p Project <
ProductMaster Table
- DROP TABLE IF EXISTS `ProductMaster`;
- CREATE TABLE `ProductMaster` (
`PRODUCTCODE` varchar(100) DEFAULT NULL,
`PRODUCTTYPE` varchar(100) DEFAULT NULL,
`DEALERPRICE` double(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Transaction Table
- DROP TABLE IF EXISTS `Transaction`;
- CREATE TABLE `Transaction` (
`PARTNERCODE` varchar(100) DEFAULT NULL,
`PRODUCTCODE` varchar(100) DEFAULT NULL,
`SELLOUTDATE` date DEFAULT NULL,
`SERIALNUM` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
PartnerMaster DB
- DROP TABLE IF EXISTS `PartnerMaster`;
- CREATE TABLE `PartnerMaster` (
`PARTNERCODE` varchar(100) DEFAULT NULL,
`ATTRIBUTE` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
TransactionNew DB
- CREATE TABLE `TransactionNew` (
`PARTNERCODE` varchar(100) DEFAULT NULL,
`PRODUCTCODE` varchar(100) DEFAULT NULL,
`SELLOUTDATE` date DEFAULT NULL,
`SERIALNUM` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
TRIGGER DB
- DROP TABLE IF EXISTS `TransactionTrigger`;
- CREATE TABLE TransactionTrigger ( DATE date NOT NULL, PARTNERCODE varchar(100) DEFAULT NULL, PRODUCTCODE varchar(100) DEFAULT NULL, REGION varchar(10) DEFAULT NULL, PRODUCTTYPE varchar(100) DEFAULT NULL, TOTALSALESCOUNT int DEFAULT NULL , TOTALSALESVALUE double(10,2) DEFAULT NULL, SELLERCODE varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
RIGGER
DELIMITER //
CREATE TRIGGER TriggerData
AFTER INSERT ON NewTransaction
FOR EACH ROW
BEGIN
IF (( select count(*) from TransactionTrigger where TransactionTrigger.PARTNERCODE = NEW.BUYERCODE and TransactionTrigger.PRODUCTCODE= NEW.PRODUCTCODE and TransactionTrigger.DATE = New.INVOICEDATE ) >= 1) THEN
UPDATE TransactionTrigger SET TransactionTrigger.TOTALSALESCOUNT = TOTALSALESCOUNT+1 , TransactionTrigger.TOTALSALESVALUE = TOTALSALESVALUE+(select ProductMaster.DEALERPRICE from ProductMaster where ProductMaster.PRODUCTCODE = NEW.PRODUCTCODE) where TransactionTrigger.PARTNERCODE = NEW.BUYERCODE and TransactionTrigger.PRODUCTCODE= NEW.PRODUCTCODE and TransactionTrigger.DATE = New.INVOICEDATE ;
ELSE
INSERT INTO TransactionTrigger (DATE,PARTNERCODE,PRODUCTCODE,REGION,PRODUCTTYPE,TOTALSALESCOUNT,TOTALSALESVALUE,SELLERCODE) VALUES(New.INVOICEDATE,NEW.BUYERCODE,NEW.PRODUCTCODE,(select ATTRIBUTE from PartnerMaster where PartnerMaster.PARTNERCODE = NEW.BUYERCODE),(select PRODUCTTYPE from ProductMaster where ProductMaster.PRODUCTCODE = NEW.PRODUCTCODE ),1,(select DEALERPRICE from ProductMaster where ProductMaster.PRODUCTCODE = NEW.PRODUCTCODE),NEW.SELLERCODE);
END IF;
END; //
DELIMITER ;
INSERT QUERY
ProductMaster
- LOCK TABLES `ProductMaster` WRITE;
- INSERT INTO `ProductMaster` VALUES ('PRODUCT1','FeaturePhone',10000.00),('PRODUCT2','FeaturePhone',8000.00),('PRODUCT3','FeaturePhone',14000.00),('PRODUCT4','FeaturePhone',15000.00),('PRODUCT5','SmartPhone',34000.00),('PRODUCT6','SmartPhone',19890.00),('PRODUCT7','SmartPhone',18000.00),('PRODUCT8','Tab',43000.00),('PRODUCT9','Gear',19000.00),('PRODUCT10','SmartPhone',40000.00);
- UNLOCK TABLES;
- select * from ProductMaster;
PartnerMaster
- LOCK TABLES `PartnerMaster` WRITE;
- INSERT INTO `PartnerMaster` VALUES ('PTNR1','DCM'),('PTNR2','DCM'),('PTNR3','DCM'),('PTNR4','DCM'),('PTNR5','RCM'),('PTNR6','RCM'),('PTNR7','DCM'),('PTNR8','DCM'),('PTNR9','RCM'),('PTNR10','RCM');
- UNLOCK TABLES;
- select * from PartnerMaster;
Transaction
- LOCK TABLES `Transaction` WRITE;
- INSERT INTO `Transaction` VALUES ('PTNR1','PRODUCT1','2021-08-03','8968996183342'),('PTNR1','PRODUCT1','2021-08-01','7398488714748'),('PTNR1','PRODUCT2','2021-08-03','2793702555065'),('PTNR1','PRODUCT2','2021-08-03','6902577934349'),('PTNR2','PRODUCT1','2021-08-01','4682612894681'),('PTNR2','PRODUCT2','2021-08-03','7534173072645'),('PTNR2','PRODUCT3','2021-08-03','7966740771872'),('PTNR3','PRODUCT4','2021-08-01','3076478564384'),('PTNR3','PRODUCT5','2021-08-02','6604277462362'),('PTNR3','PRODUCT5','2021-08-02','5028882823446'),('PTNR3','PRODUCT5','2021-08-02','5894331636600'),('PTNR3','PRODUCT6','2021-08-02','8831677321362'),('PTNR3','PRODUCT7','2021-08-04','3288616778824'),('PTNR4','PRODUCT3','2021-08-03','4741137291482'),('PTNR4','PRODUCT3','2021-08-03','2620967442984'),('PTNR4','PRODUCT3','2021-08-03','2884586445256'),('PTNR4','PRODUCT3','2021-08-03','1885666783473'),('PTNR4','PRODUCT3','2021-08-03','6815974675148'),('PTNR4','PRODUCT3','2021-08-03','7504012514273');
- UNLOCK TABLES;
- select * from Transaction;
TransactionNew
- INSERT INTO NewTransaction VALUES ('X0001556793','2021-08-03','SELLER1','PTNR1','PRODUCT1','5268900477264'),('X0001556793','2021-08-03','SELLER1','PTNR1','PRODUCT1','5204492336127'),('X0001556793','2021-08-03','SELLER1','PTNR1','PRODUCT1','5229670892965'),('X0001556793','2021-08-03','SELLER1','PTNR1','PRODUCT3','7540726227419'),('X0001556793','2021-08-03','SELLER1','PTNR1','PRODUCT3','6749974594624'),('X0001556793','2021-08-03','SELLER1','PTNR1','PRODUCT4','7156476816032'),('X0001556793','2021-08-03','SELLER1','PTNR1','PRODUCT5','1563906592782'),('X0001552343','2021-08-02','SELLER1','PTNR2','PRODUCT5','5041799248399'),('X0001552343','2021-08-02','SELLER1','PTNR2','PRODUCT5','2400024135966'),('X0001552343','2021-08-02','SELLER1','PTNR2','PRODUCT5','7303831669791'),('X0001552343','2021-08-02','SELLER1','PTNR2','PRODUCT5','2126550588264'),('X0004342343','2021-08-01','SELLER2','PTNR3','PRODUCT2','4897940206810'),('X0004342343','2021-08-01','SELLER2','PTNR3','PRODUCT2','3842796792574');
- INSERT INTO NewTransaction (BUYERCODE,PRODUCTCODE,INVOICEDATE,SERIALNUM) VALUES ('PTNR1','PRODUCT1','2021-07-03','2901039724308'),('PTNR1','PRODUCT1','2021-07-01','9021464526705'),('PTNR1','PRODUCT3','2021-07-01','7947772404972'),('PTNR1','PRODUCT3','2021-07-01','8452451667128'),('PTNR2','PRODUCT3','2021-07-01','9661268121911'),('PTNR2','PRODUCT2','2021-07-02','5354578802528'),('PTNR2','PRODUCT3','2021-07-04','1780845912976'),('PTNR3','PRODUCT4','2021-07-02','1819284341382'),('PTNR3','PRODUCT5','2021-07-02','6073112538940'),('PTNR3','PRODUCT1','2021-07-04','2384024990524'),('PTNR3','PRODUCT1','2021-07-01','8164495964016'),('PTNR3','PRODUCT4','2021-07-04','2393455104679'),('PTNR3','PRODUCT4','2021-07-03','3230036913264'),('PTNR4','PRODUCT8','2021-07-03','4093779269622'),('PTNR4','PRODUCT8','2021-07-03','5798429185135');
- select * from NewTransaction;
TRIGGER DATA
- select * from TransactionTrigger;
- select * from TransactionTrigger where PARTNERCODE='PTNR4' and PRODUCTTYPE in ('Smartphone','Tab','Gear') and MONTH = (MonthName('31-07-21'));
New DB Queries
- select SUM(TOTALSALESCOUNT) as total_sales from TransactionTrigger where PARTNERCODE='PTNR6' and PRODUCTTYPE in ('Smartphone','Tab','Gear') and DATE BETWEEN '2021-08-01' AND '2021-08-31' ;
- select SUM(TOTALSALESVALUE) as total_sales from TransactionTrigger where PARTNERCODE='PTNR6' and PRODUCTTYPE in ('Smartphone','Tab','Gear') and DATE BETWEEN (DATE_SUB('2021-08-01', INTERVAL 1 MONTH)) AND (DATE_SUB('2021-08-31', INTERVAL 1 MONTH));
- select SUM(TOTALSALESVALUE) as total_sales from TransactionTrigger where PARTNERCODE='PTNR6' and PRODUCTTYPE in ('Smartphone','Tab','Gear') and DATE BETWEEN '2021-08-01' AND '2021-08-31';
- select PRODUCTCODE from ProductMaster where DEALERPRICE >= 20000 and PRODUCTTYPE in ('Smartphone','Tab','Gear') ;
- select sum(TOTALSALESVALUE) as ValueExcMinPP from TransactionTrigger where PARTNERCODE = 'PTNR6' and PRODUCTCODE in ('Smartphone','Tab','Gear') and DATE BETWEEN '2021-08-01' AND '2021-08-31';
EXTRA Queries
- mysql -u root -p Project < Project.sql
- DROP TABLE TransactionNew ;
- DROP TRIGGER TriggerData;
- Truncate table TransactionTrigger;
OLD DB Queries Used
- countQuery = select count(*) from Transaction a left join ProductMaster b on a.PRODUCTCODE = b.PRODUCTCODE where a.SELLOUTDATE >='StartDate' and a.SELLOUTDATE <='EndDate' and a.PARTNERCODE='PTNRCODE' and b.PRODUCTTYPE='PRODTYPE';
- select TOTALSALESCOUNT,MONTH from TransactionTrigger where PARTNERCODE='PTNR7' and PRODUCTTYPE='FeaturePhone' and MONTH = (MonthName('01-07-21'));
countQuery = select SUM(TOTALSALESCOUNT) as total_sales from TransactionTrigger where PARTNERCODE='PTNRCODE' and PRODUCTTYPE in PRODTYPE and MONTH = (EXTRACT(YEAR_MONTH FROM 'Date'));
- PreviousValueQuery = select SUM(TOTALSALESVALUE) as total_sales from TransactionTrigger where PARTNERCODE='PTNRCODE' and PRODUCTTYPE in PRODTYPE and MONTH = (EXTRACT(YEAR_MONTH FROM (DATE_SUB('Date', INTERVAL 1 MONTH))));
- CurrentValueQuery = select SUM(TOTALSALESVALUE) as total_sales from TransactionTrigger where PARTNERCODE='PTNRCODE' and PRODUCTTYPE in PRODTYPE and MONTH = (EXTRACT(YEAR_MONTH FROM 'Date'));
- MinPriceQuery = select PRODUCTCODE from ProductMaster where DEALERPRICE >= MINPRICE and PRODUCTTYPE in PRODTYPE ;
- SalesvalueExcMinpp = select sum(TOTALSALESVALUE) as ValueExcMinPP from TransactionTrigger where PARTNERCODE = 'PTNRCODE' and PRODUCTCODE in MAXPTYPE and MONTH = (EXTRACT(YEAR_MONTH FROM 'Date'));
Test DB
- select * from Transaction;
- create table Test as select * from Transaction;
- alter table Test add column REGION varchar(10) ;
- update Test set REGION='DEL' where PARTNERCODE in ('PTNR6','PTNR3');
- update Test set REGION='MUM' where PARTNERCODE in ('PTNR7','PTNR2');
- update Test set REGION='HYD' where PARTNERCODE in ('PTNR4','PTNR9');
- update Test set REGION='LKO' where PARTNERCODE in ('PTNR5','PTNR1');
- update Test set REGION='BLR' where PARTNERCODE in ('PTNR8','PTNR10');
- select * from Test;