Factory Data

You must execute the code again to save those values in the database.

-- Factory database

--5 tables
--write 3-5 queries
--1x WITH
--1x SUBQUERY
--1x aggregate fn

.open standas.db

DROP TABLE CUSTOMER;
CREATE TABLE IF NOT EXISTS CUSTOMER ( 
	CUS_ID	INT PRIMARY KEY, 
	NAME TEXT, 
	TEL TEXT, 
	CH_ID INT
);

DROP TABLE QUOTATION;
CREATE TABLE IF NOT EXISTS QUOTATION ( 
	QN_ID	INT PRIMARY KEY, 
	TYPE TEXT,  
	QNPRICE REAL,
  CUS_ID INT
);

DROP TABLE STAFF;
CREATE TABLE IF NOT EXISTS STAFF ( 
	ST_ID	INT PRIMARY KEY, 
	NAME TEXT,  
	GENDER TEXT,
  POSITION TEXT,
  TAKECARE_CUS_ID INT
);

DROP TABLE INVOICE;
CREATE TABLE IF NOT EXISTS INVOICE ( 
	IV_ID	INT PRIMARY KEY, 
	IVPRICE REAL,  
	PAYDATE DATETIME,
  PAYMETHOD TEXT,
  QN_ID INT
);

DROP TABLE CHANNEL;
CREATE TABLE IF NOT EXISTS CHANNEL ( 
	CH_ID	INT PRIMARY KEY, 
	NAME TEXT
);

INSERT INTO CUSTOMER VALUES
(1,'MOMO','0990121236',3),
(2,'DJ','0958462389',1),
(3,'KP','0851326547',2),
(4,'JDAI','0539853216',2),
(5,'COCO','0896543215',1);

INSERT INTO QUOTATION VALUES
(66001,'RETORT',5500,1),
(66002,'FREEZE DRIED',4500,2),
(66003,'RD',1000,3),
(66004,'UHT',15000,4),
(66005,'DRYER',3500,5),
(66006,'RD',1000,4),
(66007,'RETORT',5500,1),
(66008,'RD',1000,5),
(66009,'UHT',15000,4),
(66010,'FDA',7500,1);

INSERT INTO STAFF VALUES
(1,'TLL','FEMALE','STORE',NULL),
(2,'DAO','FEMALE','MARKETING',1),
(3,'NAN','MALE','PRODUCTION',NULL),
(4,'BELL','FEMALE','PRODUCTION',NULL),
(5,'IMIM','FEMALE','MARKETING',5),
(6,'MUAY','FEMALE','MARKETING',4),
(7,'AE','FEMALE','PRODUCTION MANAGER',NULL),
(8,'YUI','FEMALE','MARKETING MANAGER',NULL),
(9,'FERN','FEMALE','QC',NULL),
(10,'FAH','FEMALE','QC SUPERVISOR',NULL);

INSERT INTO INVOICE VALUES
(66001,5885,'08-10-2021','TRANSFER',66001),
(66002,4815,'12-10-2021','TRANSFER',66002),
(66003,1070,'22-11-2021','CASH',66003),
(66004,16050,'28-12-2021','CASH',66004),
(66005,3745,'30-10-2021','TRANSFER',66005),
(66006,1070,'05-11-2021','TRANSFER',66006),
(66007,5885,'25-01-2022','CASH',66007),
(66008,1070,'05-04-2022','TRANSFER',66008),
(66009,16050,'30-10-2022','CASH',66009),
(66010,8025,'08-01-2023','TRANSFER',66010);

INSERT INTO CHANNEL values
(1,'WALKIN'),
(2,'FACEBOOK'),
(3,'WEBSITE');

.mode column
.header on

SELECT * FROM CUSTOMER;
SELECT * FROM QUOTATION;
SELECT * FROM STAFF;
SELECT * FROM INVOICE;
SELECT * FROM CHANNEL;

/* WITH */
.print \n'Query-Price Total of UHT Type'
WITH TYPE_UHT AS (
  SELECT * FROM QUOTATION
  WHERE TYPE = 'UHT'
)

SELECT T.TYPE AS TYPE, SUM(I.IVPRICE) AS TOTAL
FROM INVOICE AS I
JOIN TYPE_UHT AS T
ON T.QN_ID = I.QN_ID;

/* Sub-query */
.print \n'Query-Female Staff In Position Marketing & Production'

SELECT * FROM (
  SELECT * 
  FROM STAFF
  WHERE POSITION IN ('MARKETING','PRODUCTION')
)
WHERE GENDER = 'FEMALE';

/*Aggregate function*/
.print \n'Query-Count Split By Type'
SELECT
  Q.TYPE,
  COUNT(*) AS COUNT_TYPE
FROM QUOTATION AS Q, INVOICE AS I
WHERE Q.QN_ID = I.QN_ID
GROUP BY Q.TYPE;

.print \n'Query-Sum Price Split By Paymethod'
SELECT 
  PAYMETHOD,
  SUM(IVPRICE) AS SUM_PAY
FROM INVOICE
GROUP BY PAYMETHOD;

.print \n'Query-summary stat of Price Split By Type'
SELECT 
  Q.TYPE,
  COUNT(*) COUNT_TYPE,
  AVG(iVPRICE) AS AVG_P,
  SUM(IVPRICE) AS SUM_P,
  MIN(IVPRICE) AS MIN_P,
  MAX(IVPRICE) AS MAX_P
FROM QUOTATION AS Q
INNER JOIN INVOICE AS I ON Q.QN_ID = I.QN_ID
GROUP BY Q.TYPE
ORDER BY 4 DESC;

CUS_ID NAME TEL CH_ID
---------- ---------- ---------- ----------
1 MOMO 0990121236 3
2 DJ 0958462389 1
3 KP 0851326547 2
4 JDAI 0539853216 2
5 COCO 0896543215 1
QN_ID TYPE QNPRICE CUS_ID
---------- ---------- ---------- ----------
66001 RETORT 5500.0 1
66002 FREEZE DRI 4500.0 2
66003 RD 1000.0 3
66004 UHT 15000.0 4
66005 DRYER 3500.0 5
66006 RD 1000.0 4
66007 RETORT 5500.0 1
66008 RD 1000.0 5
66009 UHT 15000.0 4
66010 FDA 7500.0 1
ST_ID NAME GENDER POSITION TAKECARE_CUS_ID
---------- ---------- ---------- ---------- ---------------
1 TLL FEMALE STORE
2 DAO FEMALE MARKETING 1
3 NAN MALE PRODUCTION
4 BELL FEMALE PRODUCTION
5 IMIM FEMALE MARKETING 5
6 MUAY FEMALE MARKETING 4
7 AE FEMALE PRODUCTION
8 YUI FEMALE MARKETING
9 FERN FEMALE QC
10 FAH FEMALE QC SUPERVI
IV_ID IVPRICE PAYDATE PAYMETHOD QN_ID
---------- ---------- ---------- ---------- ----------
66001 5885.0 08-10-2021 TRANSFER 66001
66002 4815.0 12-10-2021 TRANSFER 66002
66003 1070.0 22-11-2021 CASH 66003
66004 16050.0 28-12-2021 CASH 66004
66005 3745.0 30-10-2021 TRANSFER 66005
66006 1070.0 05-11-2021 TRANSFER 66006
66007 5885.0 25-01-2022 CASH 66007
66008 1070.0 05-04-2022 TRANSFER 66008
66009 16050.0 30-10-2022 CASH 66009
66010 8025.0 08-01-2023 TRANSFER 66010
CH_ID NAME
---------- ----------
1 WALKIN
2 FACEBOOK
3 WEBSITE

'Query-Price Total of UHT Type'
TYPE TOTAL
---------- ----------
UHT 32100.0

'Query-Female Staff In Position Marketing & Production'
ST_ID NAME GENDER POSITION TAKECARE_CUS_ID
---------- ---------- ---------- ---------- ---------------
2 DAO FEMALE MARKETING 1
4 BELL FEMALE PRODUCTION
5 IMIM FEMALE MARKETING 5
6 MUAY FEMALE MARKETING 4

'Query-Count Split By Type'
TYPE COUNT_TYPE
---------- ----------
DRYER 1
FDA 1
FREEZE DRI 1
RD 3
RETORT 2
UHT 2

'Query-Sum Price Split By Paymethod'
PAYMETHOD SUM_PAY
---------- ----------
CASH 39055.0
TRANSFER 24610.0

'Query-summary stat of Price Split By Type'
TYPE COUNT_TYPE AVG_P SUM_P MIN_P MAX_P
---------- ---------- ---------- ---------- ---------- ----------
UHT 2 16050.0 32100.0 16050.0 16050.0
RETORT 2 5885.0 11770.0 5885.0 5885.0
FDA 1 8025.0 8025.0 8025.0 8025.0
FREEZE DRI 1 4815.0 4815.0 4815.0 4815.0
DRYER 1 3745.0 3745.0 3745.0 3745.0
RD 3 1070.0 3210.0 1070.0 1070.0