createdb.sql

CREATE TABLE CUSTOMER (
	ID SERIAL PRIMARY KEY,
	GENDER VARCHAR(10),
	PHONENUMBER VARCHAR(25),
	FIRSTNAME VARCHAR(100),
	LASTNAME VARCHAR(100)
)


CREATE TABLE BRANDS(
	ID SERIAL PRIMARY KEY,
	NAME VARCHAR(30)
)


CREATE TABLE TYPE(
	ID SERIAL PRIMARY KEY,
	NAME VARCHAR(30)
)


CREATE TABLE PRODUCTS(
	ID SERIAL PRIMARY KEY,
	TYPEID SERIAL,
	AMOUNT DECIMAL,
	NAME VARCHAR(40),
	BRANDID SERIAL,
	EXPIRATIONDATE DATE,
	CONSTRAINT fk_product_type FOREIGN KEY(TYPEID) REFERENCES TYPE(ID),
	CONSTRAINT fk_product_brand FOREIGN KEY(BRANDID) REFERENCES BRANDS(ID)
)

CREATE TABLE PURCHASES(
ID SERIAL PRIMARY KEY,
	ADRESS VARCHAR(100),
	CUSTOMERID SERIAL,
	CREDITCARD VARCHAR(20),
	TOTALAMOUNT DECIMAL,
	PURCHASEDATE DATE,
	CONSTRAINT fk_purchases_customerID FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMER(ID)
)

CREATE TABLE PURCHASEPRODUCT(
	ID SERIAL PRIMARY KEY,
	PRODUCTID SERIAL,
	PURCHASEID SERIAL,
	CONSTRAINT fk_purchaseproduct_product FOREIGN KEY(PRODUCTID) REFERENCES PRODUCTS(ID),
	CONSTRAINT fk_purchaseproduct_purchase FOREIGN KEY(PURCHASEID) REFERENCES PURCHASES(ID)
)


SELECT B.name, count(*) TOTALSALES FROM PRODUCTS PR
LEFT JOIN BRANDS B ON B.ID = PR.BRANDID
LEFT JOIN PURCHASEPRODUCT PP ON PP.PRODUCTID = PR.ID
WHERE B.NAME = 'KREATIN' 
GROUP BY B.NAME
ORDER BY TOTALSALES DESC
LIMIT 1


SELECT SUM(PUR.TOTALAMOUNT) FROM PURCHASEPRODUCT PP
LEFT JOIN PRODUCTS PR ON PR.ID = PP.PRODUCTID
LEFT JOIN PURCHASES PUR ON PUR.ID = PP.PURCHASEID
WHERE PR.BRANDID = 4 AND PUR.PURCHASEDATE BETWEEN '2015/01/01' AND '2020-12-31'


SELECT CR.GENDER, COUNT(*) TOTALSALES FROM PURCHASEPRODUCT PP
LEFT JOIN PRODUCTS PR ON PR.ID = PP.PRODUCTID
LEFT JOIN PURCHASES PUR ON PUR.ID = PP.PURCHASEID
LEFT JOIN CUSTOMER CR ON CR.ID = PUR.CUSTOMERID
WHERE PR.NAME = 'L KAMITIN' AND PUR.PURCHASEDATE BETWEEN '2015/01/01' AND '2020/12/31'
GROUP BY CR.GENDER
HAVING count(*)>4






CREATE TABLE EMPLOYEE(

	ID SERIAL PRIMARY KEY,
	TITLE VARCHAR(12),
	PERSONID SERIAL,
	CONSTRAINT fk_person_id FOREIGN KEY(PERSONID) REFERENCES PERSON(ID)
	
	
)


CREATE TABLE SALARY(

	ID SERIAL PRIMARY KEY,
	AMOUNT DECIMAL,
	MONTH VARCHAR(6),
	EMPLOYEEID SERIAL,
	CONSTRAINT fk_employee_ID FOREIGN KEY(EMPLOYEEID) REFERENCES EMPLOYEE(ID)
	

)
CREATE TABLE CAMPAIGN(

	ID SERIAL PRIMARY KEY,
	PRODUCTID SERIAL,
	RATE DECIMAL,
	NAME VARCHAR(15),
	DURATION DECIMAL,
	CONSTRAINT fk_product_ID FOREIGN KEY(PRODUCTID) REFERENCES PRODUCTS(ID)
)

CREATE TABLE PRODUCTNEWS(

	ID SERIAL PRIMARY KEY,
	CONTENT VARCHAR(5),
	ISPUBLISHED BOOLEAN ,
	EMPLOYEEID SERIAL,
	PRODUCTID SERIAL,
	CONSTRAINT fk_employee_ID FOREIGN KEY(EMPLOYEEID) REFERENCES EMPLOYEE(ID),
	CONSTRAINT fk_product_ID FOREIGN KEY(PRODUCTID) REFERENCES PRODUCTS(ID)
)

insert into brands 
("name") values ('NESTLE')

insert into "type" 
("name") values ('POWDER')

create view vw_salesReport as
select pr."name" as Name, b."name" as BrandName, t."name" as TypeName,
pr.amount as Amount, pe.firstname as CustomerName
from purchaseproduct pp 
left join products pr on pr.id = pp.productid 
left join purchases pu on pu.id = pp.purchaseid
left join brands b on b.id = pr.brandid 
left join "type" t on t.id = pr.typeid 
left join customer c on c.id = pu.customerid 
left join person pe on pe.id = c.personid 

ALTER TABLE PRODUCTNEWS
ALTER COLUMN CONTENT TYPE  VARCHAR(50)