my tables are as below.
CREATE TABLE PRODUCT
(
PROD_ID VARCHAR(5) NOT NULL,
PROD_NAME VARCHAR(20) NOT NULL,
PROD_DESC VARCHAR(20) NOT NULL,
PROD_PRICE DECIMAL NOT NULL,
PROD_CAT INT NOT NULL,
PRIMARY KEY(PROD_ID),
FOREIGN KEY(PROD_CAT) REFERENCES CATEGORY,
);
CREATE TABLE SALES
(
SALES_ID INT NOT NULL,
SALES_DATE DATE NOT NULL,
CUST_ID VARCHAR(5) NOT NULL,
EMP_ID VARCHAR(6) NOT NULL,
PRIMARY KEY(SALES_ID),
FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER,
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE,
);
CREATE TABLE SALES_DETAIL
(
SALES_ID INT NOT NULL,
PROD_ID VARCHAR(5) NOT NULL,
QUANTITY INT NOT NULL,
PRIMARY KEY (SALES_ID, PROD_ID),
);
if some of the product have discount for a duration of some time, should i create a product table?
CREATE TABLE DISCOUNT
(
DISCOUNT_ID INT NOT NULL,
PROD_ID VARCHAR(5) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
);
This is my sql queries to count total sales without the discount table. if the discount table is created, how am i going to make changes to the query to count the total after deducted the discount?
select month(s.sales_date), s.cust_id, sum((p.prod_price * sd.quantity)) as Total
from product p, sales_detail sd, sales s, category c
where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.category_id=p.prod_cat and year(s.sales_date) = \'1999\'
group by month(s.sales_date), s.cust_id;