CREATE TABLE customer
(
CUSTID INTEGER PRIMARY KEY,
CUSTName VARCHAR(25),
CUSTGENDER CHAR(1),
CUSTBIRTHYEAR NUMERIC,
CUSTREGION VARCHAR(15));
INSERT INTO customer VALUES( 1, 'Salam','M',1998,'Muscat');
INSERT INTO customer VALUES( 2, 'Ali','M',1983 ,'Musandam');
INSERT INTO customer VALUES( 3, 'Musalum','M', 1983 ,'Dhofar');
INSERT INTO customer VALUES( 4, 'Ahmed','M', 1998,'AlDhakhlya');
INSERT INTO customer VALUES( 5, 'Ibrahim','M',1998, 'AlDhahira');
INSERT INTO customer VALUES( 6, 'Yahya','M', 1998,'AlBatinah');
INSERT INTO customer VALUES( 7, 'Salma','F', 1983 ,'Muscat');
INSERT INTO customer VALUES( 8, 'Aida','F', 1983 ,'Musandam');
INSERT INTO customer VALUES( 9, 'Adil','M', 1983 ,'Dhofar');
INSERT INTO customer VALUES( 10,'Badria','F',2000, 'AlDhakhlya');
INSERT INTO customer VALUES( 11,'Shahad','F', 2000,'AlDhahira');
INSERT INTO customer VALUES( 12,'Lamya','F', 2000,'AlBatinah');
INSERT INTO customer VALUES( 13,'Mohad','M', 2000,'AlSharqia');
INSERT INTO customer VALUES( 14,'Mustafa','M' ,1979,'AlWasta');
INSERT INTO customer VALUES( 15,'Sumaya','F',1979, 'AlBatinah');
INSERT INTO customer VALUES( 16,'Lama','F' ,1979,'AlSharqia');
CREATE TABLE Promotion
(
PROMOID INTEGER PRIMARY KEY,
PROMONAME VARCHAR(25),
PROMOCATEGORY VARCHAR(25));
INSERT INTO Promotion VALUES(11, 'loyal customer discount', 'internet');
INSERT INTO Promotion VALUES(22, 'online discount', 'internet');
INSERT INTO Promotion VALUES(33, 'ad magazine', 'magazine');
INSERT INTO Promotion VALUES(44, 'coupon magazine', 'magazine');
INSERT INTO Promotion VALUES(55, 'TV commercial', 'TV');
INSERT INTO Promotion VALUES(66, 'TV program sponsorship', 'TV');
INSERT INTO Promotion VALUES(77, 'general flyer', 'flyer');
INSERT INTO Promotion VALUES(88, 'hospital flyer', 'flyer');
CREATE TABLE product
(
PRODID NUMBER(6) PRIMARY KEY,
prodName VARCHAR2(50) ,
prodClass VARCHAR2(20));
INSERT INTO product VALUES (111, 'Pitching Machine and Batting Cage Combo',
'Baseball' );
INSERT INTO product VALUES(222, 'Speed Trainer Bats and Training Program',
'Baseball' ) ;
INSERT INTO product VALUES (333, 'Plastic Cricket Bat', 'Cricket' );
INSERT INTO product VALUES (444, 'English Willow Cricket Bat', 'Cricket'
);
INSERT INTO product VALUES (555, 'Soccer Ball', 'Football' );
INSERT INTO product VALUES (666, 'Gear Bag','Football' );
INSERT INTO product VALUES (777, 'Tennis Strings Natural Gut', 'Tennis' );
INSERT INTO product VALUES (888, 'Tennis Strings Synthetic Gut',
'Tennis' );
CREATE TABLE channel
(
CHANNELID NUMBER PRIMARY KEY,
channelDesc VARCHAR2(20) ,
channelClass VARCHAR2(20) );
INSERT INTO channel VALUES
( 10, 'Direct Sales', 'Direct');
INSERT INTO channel VALUES
( 20, 'Tele Sales', 'Direct');
INSERT INTO channel VALUES
( 30, 'Catalog', 'Indirect');
INSERT INTO channel VALUES
( 40, 'Internet', 'Indirect');
INSERT INTO channel VALUES
( 50, 'Partners', 'Others');
CREATE TABLE costs
(
COSTID INTEGER PRIMARY KEY,
PRODID NUMBER(6),
CostDate DATE,
PROMOID INTEGER ,
CHANNELID NUMBER ,
UNITCOST NUMBER(5,2),
UNITPRICE NUMBER(5,2));
INSERT INTO COSTS VALUES (1, 111, '10-JAN-2019', 11, 10, 41.07,
120.31);
INSERT INTO COSTS VALUES (2,111, '19-FEB-2020', 22, 20, 88.45,
110.99);
INSERT INTO COSTS VALUES (3,111, '10-JAN-2020', 33, 30, 63.64,
80.99);
INSERT INTO COSTS VALUES (4,222, '19-FEB-2019', 44, 40, 75.1, 100.99);
INSERT INTO COSTS VALUES (5,222, '19-FEB-2019', 55, 50, 63.64,
90.52);
INSERT INTO COSTS VALUES (6,222, '19-FEB-2021', 66, 10, 63.64,
90.52);
INSERT INTO COSTS VALUES (7,222, '10-JAN-2019', 77, 20, 86.64,
117.23);
INSERT INTO COSTS VALUES (8,222, '10-JAN-2021', 88, 30, 90.18,
117.23);
INSERT INTO COSTS VALUES (9,333, '10-MAR-2021', 11, 40, 46.71,
79.99);
INSERT INTO COSTS VALUES (10,333, '10-MAR-2022', 22, 50, 87.22,
101.99);
INSERT INTO COSTS VALUES (11,333, '19-FEB-2023', 33, 10, 84.71,
100.99);
INSERT INTO COSTS VALUES (12,333, '10-MAR-2023', 44, 20, 46.71,
66.99);
INSERT INTO COSTS VALUES (13,444, '10-MAR-2021', 55, 30, 50.18,
60.99);
INSERT INTO COSTS VALUES (14,444, '10-MAR-2021', 44, 40, 53.64,
77.99);
INSERT INTO COSTS VALUES (15,555, '10-MAR-2021', 55, 50, 53.8, 90.49);
INSERT INTO COSTS VALUES (16,555, '10-MAR-2020', 88, 10, 65.41,
88.49);
INSERT INTO COSTS VALUES (17,555, '19-FEB-2019', 11, 20, 34.8, 55.49);
INSERT INTO COSTS VALUES (18,666, '19-FEB-2023', 22, 30, 54.41,
83.49);
INSERT INTO COSTS VALUES (19,777, '10-JAN-2023', 33, 40, 57.04,
95.99);
INSERT INTO COSTS VALUES (20,777, '10-JAN-2023', 44, 50, 47.79,
55.99);
INSERT INTO COSTS VALUES (21,777, '10-MAR-2023', 11, 10, 77.79,
100.99);
INSERT INTO COSTS VALUES (22,888, '10-MAR-2021', 22, 20, 90.73,
155.83);
INSERT INTO COSTS VALUES (23,888, '10-MAR-2022', 33, 30, 133.3,
163.79);
INSERT INTO COSTS VALUES (24,888, '10-MAR-2022', 44, 40, 115.39,
155.83);
CREATE TABLE sales
(
SALESID INTEGER PRIMARY KEY,
PRODID NUMBER(6),
SaleDate DATE,
CUSTID INTEGER ,
CHANNELID NUMBER ,
SaleQuantity INTEGER,
SalePrice NUMBER(6,2));
INSERT INTO sales VALUES (1, 111, '10-JAN-2019', 1, 10, 41.07,
120.31);
INSERT INTO sales VALUES (2,111, '19-FEB-2018', 2, 20, 88.45,
110.99);
INSERT INTO sales VALUES (3,111, '10-JAN-2018', 3, 30, 63.64,
80.99);
INSERT INTO sales VALUES (4,222, '19-FEB-2019', 4, 40, 75.1, 100.99);
INSERT INTO sales VALUES (5,222, '19-FEB-2019', 5, 50, 63.64,
90.52);
INSERT INTO sales VALUES (6,222, '19-FEB-2017', 6, 10, 63.64,
90.52);
INSERT INTO sales VALUES (7,222, '10-JAN-2019', 7, 20, 86.64,
117.23);
INSERT INTO sales VALUES (8,222, '10-JAN-2017', 8, 30, 90.18,
117.23);
INSERT INTO sales VALUES (9,333, '10-MAR-2017', 9, 40, 46.71,
79.99);
INSERT INTO sales VALUES (10,333, '10-MAR-2020', 10, 50, 87.22,
101.99);
INSERT INTO sales VALUES (11,333, '19-FEB-2020', 11, 10, 84.71,
100.99);
INSERT INTO sales VALUES (12,333, '10-MAR-2020', 12, 20, 46.71,
66.99);
INSERT INTO sales VALUES (13,444, '10-MAR-2017', 13, 30, 50.18,
60.99);
INSERT INTO sales VALUES (14,444, '10-MAR-2017', 14, 40, 53.64,
77.99);
INSERT INTO sales VALUES (15,555, '10-MAR-2017', 1, 50, 53.8, 90.49);
INSERT INTO sales VALUES (16,555, '10-MAR-2018', 1, 10, 65.41,
88.49);
INSERT INTO sales VALUES (17,555, '19-FEB-2019', 2, 20, 34.8, 55.49);
INSERT INTO sales VALUES (18,666, '19-FEB-2017', 3, 30, 54.41,
83.49);
INSERT INTO sales VALUES (19,777, '10-JAN-2017', 3, 40, 57.04,
95.99);
INSERT INTO sales VALUES (20,777, '10-JAN-2017', 4, 50, 47.79,
550.99);
INSERT INTO sales VALUES (21,777, '10-MAR-2019', 5, 10, 77.79,
100.99);
INSERT INTO sales VALUES (22,888, '10-MAR-2020', 5, 20, 90.73,
155.83);
INSERT INTO sales VALUES (23,888, '10-MAR-2020', 6, 30, 133.3,
163.79);
INSERT INTO sales VALUES (24,888, '10-MAR-2020', 7, 40, 115.39,
155.83);
ALTER TABLE SALES ADD CONSTRAINT sales_customer_fk FOREIGN KEY (CUSTID)
REFERENCES customer (CUSTID);
ALTER TABLE SALES ADD CONSTRAINT sales_product_fk FOREIGN KEY (prodid)
REFERENCES product (prodid);
ALTER TABLE SALES ADD CONSTRAINT sales_channel_fk FOREIGN KEY (channelid)
REFERENCES channel (channelid);
ALTER TABLE costs ADD CONSTRAINT costs_promo_fk FOREIGN KEY (promoid)
REFERENCES promotion (promoid);
ALTER TABLE costs ADD CONSTRAINT costs_product_fk FOREIGN KEY (prodid)
REFERENCES product (prodid);
ALTER TABLE costs ADD CONSTRAINT costs_channel_fk FOREIGN KEY (channelid)
REFERENCES channel (channelid);
