Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE order_item CASCADE CONSTRAINTS;
- DROP TABLE product CASCADE CONSTRAINTS;
- DROP TABLE product_category_link CASCADE CONSTRAINTS;
- DROP TABLE product_category CASCADE CONSTRAINTS;
- DROP TABLE delivery CASCADE CONSTRAINTS;
- DROP TABLE payment_link CASCADE CONSTRAINTS;
- DROP TABLE order_table CASCADE CONSTRAINTS;
- DROP TABLE payment CASCADE CONSTRAINTS;
- DROP TABLE commercial CASCADE CONSTRAINTS;
- DROP TABLE customer CASCADE CONSTRAINTS;
- CREATE TABLE customer (
- customer_ID VARCHAR(8),
- customer_name VARCHAR(30) CONSTRAINT customer_name_nn not null,
- email VARCHAR(60) CONSTRAINT customer_email_constraint check (email like '%_@_%._%'),
- phone_number NUMBER(11),
- house_number VARCHAR(5) CONSTRAINT customer_houseno_nn not null,
- postcode VARCHAR(7)CONSTRAINT customer_postcode_nn not null,
- CONSTRAINT customer_PK PRIMARY KEY (customer_ID));
- CREATE TABLE commercial (
- company_registration_number VARCHAR(8),
- company_name VARCHAR(30)CONSTRAINT company_name_nn not null,
- authentication_code VARCHAR(6)CONSTRAINT company_auth_nn not null,
- customer_ID VARCHAR(8),
- CONSTRAINT company_PK PRIMARY KEY (company_registration_number),
- CONSTRAINT customer_FK_CT FOREIGN KEY (customer_ID) REFERENCES customer(customer_ID));
- CREATE TABLE payment (
- payment_ID VARCHAR(8),
- payment_method VARCHAR(25) CONSTRAINT payment_method_nn not null,
- date_of_payment DATE CONSTRAINT payment_date_nn not null,
- CONSTRAINT payment_PK PRIMARY KEY (payment_ID));
- CREATE TABLE order_table (
- order_ID VARCHAR(8),
- customer_ID VARCHAR(8),
- order_date DATE CONSTRAINT order_table_date_nn not null,
- CONSTRAINT order_PK PRIMARY KEY (order_ID),
- CONSTRAINT customer_FK_OT FOREIGN KEY (customer_ID) REFERENCES customer(customer_ID));
- CREATE TABLE payment_link (
- payment_link_ID VARCHAR(8),
- order_ID VARCHAR(8),
- to_pay NUMBER(19,4) CONSTRAINT to_pay_nn not null,
- CONSTRAINT payment_link_CK PRIMARY KEY (payment_link_ID, order_ID),
- CONSTRAINT payment_link_FK1 FOREIGN KEY (payment_link_ID) REFERENCES payment(payment_ID),
- CONSTRAINT payment_link_FK2 FOREIGN KEY (order_ID) REFERENCES order_table(order_ID));
- CREATE TABLE delivery (
- delivery_ID VARCHAR(8),
- postcode VARCHAR(7) CONSTRAINT delivery_postcode_nn not null,
- house_number VARCHAR(5) CONSTRAINT delivery_houseno_nn not null,
- delivered CHAR(1) CONSTRAINT delivery_delivered_nn not null,
- delivery_timestamp DATE,
- CONSTRAINT delivery_PK PRIMARY KEY (delivery_ID));
- CREATE TABLE product (
- product_ID VARCHAR(8),
- product_name VARCHAR(30) CONSTRAINT product_name_nn not null,
- product_description VARCHAR(150) CONSTRAINT product_desc_nn not null,
- product_price NUMBER(19,4) CONSTRAINT payment_price_nn not null,
- CONSTRAINT product_PK PRIMARY KEY (product_ID));
- CREATE TABLE product_category (
- product_category_ID VARCHAR(8),
- product_category VARCHAR(20),
- CONSTRAINT product_category_PK PRIMARY KEY (product_category_ID));
- CREATE TABLE product_category_link (
- product_ID VARCHAR(8),
- product_category_ID VARCHAR(8),
- CONSTRAINT product_CK PRIMARY KEY (product_ID, product_category_ID),
- CONSTRAINT product_link_FK1 FOREIGN KEY (product_ID) REFERENCES product(product_ID),
- CONSTRAINT product_link_FK2 FOREIGN KEY (product_category_ID) REFERENCES product_category(product_category_ID));
- CREATE TABLE order_item (
- order_item_ID VARCHAR(8),
- product_ID VARCHAR(8),
- delivery_ID VARCHAR(8),
- order_ID VARCHAR(8),
- CONSTRAINT order_item_PK PRIMARY KEY (order_item_ID),
- CONSTRAINT product_FK_OIT FOREIGN KEY (product_ID) REFERENCES product(product_ID),
- CONSTRAINT delivery_FK_OIT FOREIGN KEY (delivery_ID) REFERENCES delivery(delivery_ID),
- CONSTRAINT order_FK_OIT FOREIGN KEY (order_ID) REFERENCES order_table(order_ID));
- --customer
- INSERT INTO customer VALUES ('01', 'Sean Sparshott', 'sean.sparshott@yahoo.com', '07540700362', '3', 'so456ha');
- INSERT INTO customer VALUES ('02', 'John Smith', 'john.smith@yahoo.com', '07540710232', '56', 'sh736sa');
- INSERT INTO customer VALUES ('03', 'Kang Kong', 'kang.kong@yahoo.com', '07540756342', '33', 'bg467hg');
- INSERT INTO customer VALUES ('04', 'Chaswinder Singh', 'chaz.singh@yahoo.com', '07574709462', '67b', 'sh768ga');
- INSERT INTO customer VALUES ('05', 'Martin Smith', 'm.smith@yahoo.com', '07785433453', '67', 'jg655hg');
- INSERT INTO customer VALUES ('06', 'Sam Shaw', 'sam.shaw@yahoo.com', '07235463464', '22', 'ji877aj');
- INSERT INTO customer VALUES ('07', 'Max Anderson', 'max.anderson@yahoo.com', '07465678996', '108', 'kl344hj');
- INSERT INTO customer VALUES ('08', 'James Scott', 'james.scott@yahoo.com', '07357899044', '99', 'gf835lk');
- INSERT INTO customer VALUES ('09', 'Lia Tootsie', 'lia.tootsie@yahoo.com', '07345632690', '345', 'lk835gh');
- INSERT INTO customer VALUES ('10', 'Sean Sparshott', 'sean.sparshott@yahoo.com', '07964365846', '97c', 'fi635li');
- --commercial
- INSERT INTO commercial VALUES ('82746378', 'Master Builders', 'A28734', '01');
- INSERT INTO commercial VALUES ('88739475', 'Builders Unite', 'A35983', '03');
- INSERT INTO commercial VALUES ('90832544', 'Bricks were laid', 'A96833', '04');
- INSERT INTO commercial VALUES ('82340987', 'I know a guy', 'A12389', '05');
- INSERT INTO commercial VALUES ('82732487', 'Screwen nails', 'A43573', '07');
- INSERT INTO commercial VALUES ('81239853', 'Building Company', 'A87365', '08');
- INSERT INTO commercial VALUES ('86439083', 'Your local builders', 'A99893', '09');
- --payment
- INSERT INTO payment VALUES ('01', 'visa', TO_DATE('15/SEP/2015', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('02', 'visa', TO_DATE('16/SEP/2015', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('03', 'paypal', TO_DATE('20/OCT/2015', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('04', 'visa', TO_DATE('22/OCT/2015', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('05', 'paypal', TO_DATE('12/DEC/2015', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('06', 'visa', TO_DATE('18/JAN/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('07', 'visa', TO_DATE('06/MAR/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('08', 'paypal', TO_DATE('03/MAR/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('09', 'payment on collection', TO_DATE('19/JUN/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('10', 'paypal', TO_DATE('11/JUL/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('11', 'paypal', TO_DATE('14/JUL/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('12', 'visa', TO_DATE('22/AUG/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('13', 'payment on collection', TO_DATE('23/AUG/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('14', 'payment on collection', TO_DATE('28/AUG/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('15', 'visa', TO_DATE('09/SEP/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('16', 'visa', TO_DATE('11/SEP/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('17', 'paypal', TO_DATE('17/SEP/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('18', 'paypal', TO_DATE('17/OCT/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('19', 'visa', TO_DATE('11/DEC/2016', 'dd/MON/yyyy'));
- INSERT INTO payment VALUES ('20', 'payment on collection', TO_DATE('11/DEC/2016', 'dd/MON/yyyy'));
- --order_table
- INSERT INTO order_table VALUES ('01', '01', '18/SEP/2015');
- INSERT INTO order_table VALUES ('02', '02', '19/SEP/2015');
- INSERT INTO order_table VALUES ('03', '03', '25/OCT/2015');
- INSERT INTO order_table VALUES ('04', '04', '25/OCT/2015');
- INSERT INTO order_table VALUES ('05', '05', '17/DEC/2015');
- INSERT INTO order_table VALUES ('06', '06', '21/JAN/2016');
- INSERT INTO order_table VALUES ('07', '07', '07/MAR/2016');
- INSERT INTO order_table VALUES ('08', '08', '05/MAR/2016');
- INSERT INTO order_table VALUES ('09', '09', '20/JUN/2016');
- INSERT INTO order_table VALUES ('10', '03', '25/OCT/2015');
- INSERT INTO order_table VALUES ('11', '01', '18/SEP/2015');
- INSERT INTO order_table VALUES ('12', '07', '07/MAR/2016');
- INSERT INTO order_table VALUES ('13', '07', '07/MAR/2016');
- INSERT INTO order_table VALUES ('14', '05', '17/MAR/2015');
- INSERT INTO order_table VALUES ('15', '10', '16/AUG/2016');
- --payment_link
- INSERT INTO payment_link VALUES ('01', '01', '23.46');
- INSERT INTO payment_link VALUES ('02', '12', '233.26');
- INSERT INTO payment_link VALUES ('03', '07', '47.46');
- INSERT INTO payment_link VALUES ('04', '15', '9.15');
- INSERT INTO payment_link VALUES ('05', '05', '123.45');
- INSERT INTO payment_link VALUES ('06', '05', '543.21');
- INSERT INTO payment_link VALUES ('07', '07', '11.11');
- INSERT INTO payment_link VALUES ('08', '14', '21.34');
- INSERT INTO payment_link VALUES ('09', '09', '258.34');
- INSERT INTO payment_link VALUES ('10', '02', '982.12');
- INSERT INTO payment_link VALUES ('11', '02', '100011.34');
- INSERT INTO payment_link VALUES ('12', '12', '1004.23');
- INSERT INTO payment_link VALUES ('13', '07', '210.34');
- INSERT INTO payment_link VALUES ('14', '13', '9833.78');
- INSERT INTO payment_link VALUES ('15', '05', '212.44');
- INSERT INTO payment_link VALUES ('16', '07', '21.34');
- INSERT INTO payment_link VALUES ('17', '01', '56.84');
- INSERT INTO payment_link VALUES ('18', '14', '1.23');
- INSERT INTO payment_link VALUES ('19', '11', '9820.34');
- INSERT INTO payment_link VALUES ('20', '03', '55551.65');
- --delivery
- INSERT INTO delivery VALUES('01','so456ha','3','Y',TO_DATE('16/SEP/2015 11:30:32', 'dd/MON/yyyy hh24:mi:ss'));
- INSERT INTO delivery VALUES('02','sh736sa','56','N',NULL);
- INSERT INTO delivery VALUES('03','bg467hg','33','Y',TO_DATE('23/OCT/2015 14:25:32', 'dd/MON/yyyy hh24:mi:ss'));
- INSERT INTO delivery VALUES('04','sh768ga','67b','N',NULL);
- INSERT INTO delivery VALUES('05','jg655hg','67','Y',TO_DATE('07/MAR/2016 15:12:32', 'dd/MON/yyyy hh24:mi:ss'));
- INSERT INTO delivery VALUES('06','ji877aj','22','N',NULL);
- INSERT INTO delivery VALUES('07','kl344hj','108','Y',TO_DATE('13/JUL/2016 15:35:32', 'dd/MON/yyyy hh24:mi:ss'));
- INSERT INTO delivery VALUES('08','gf835lk','99','N',NULL);
- INSERT INTO delivery VALUES('09','lk835gh','345','Y',TO_DATE('20/JUL/2016 17:09:32', 'dd/MON/yyyy hh24:mi:ss'));
- INSERT INTO delivery VALUES('10','fi635li','97c','N',NULL);
- INSERT INTO delivery VALUES('11','gf835lk','99','Y',TO_DATE('22/AUG/2016 18:56:32', 'dd/MON/yyyy hh24:mi:ss'));
- INSERT INTO delivery VALUES('12','so456ha','3','N',NULL);
- INSERT INTO delivery VALUES('13','jg655hg','67','Y',TO_DATE('17/OCT/2016 19:22:32', 'dd/MON/yyyy hh24:mi:ss'));
- --product
- INSERT INTO product VALUES ('01', 'Blue Paint', 'Paint that is blue for your walls', '22.50');
- INSERT INTO product VALUES ('02', 'Batteries', 'Pack of 4x AAA Batteries', '8.99');
- INSERT INTO product VALUES ('03', 'Wood Nails', 'Box of 200 flat headed wood nails', '20.25');
- INSERT INTO product VALUES ('04', 'Cement Mixture', 'A packet of cement mixture', '75.00');
- INSERT INTO product VALUES ('05', 'Hammer Set', 'A set of your everyday building hammers', '180.00');
- INSERT INTO product VALUES ('06', 'Electric Drill Set', 'A set of your everyday building electric drills with bits', '220.99');
- INSERT INTO product VALUES ('07', 'Crane', 'A lifting vehicle that is suitable for building structures', '110000.20');
- INSERT INTO product VALUES ('08', 'Digger', 'A digging vehicale for all your big digging needs', '75000.99');
- --product_catagory
- INSERT INTO product_category VALUES ('01', 'Household materials');
- INSERT INTO product_category VALUES ('02', 'Buidling Materials');
- INSERT INTO product_category VALUES ('03', 'Building Tools');
- INSERT INTO product_category VALUES ('04', 'Vehicles');
- --product_category_link
- INSERT INTO product_category_link VALUES ('01', '01');
- INSERT INTO product_category_link VALUES ('02', '01');
- INSERT INTO product_category_link VALUES ('03', '02');
- INSERT INTO product_category_link VALUES ('04', '02');
- INSERT INTO product_category_link VALUES ('05', '03');
- INSERT INTO product_category_link VALUES ('06', '03');
- INSERT INTO product_category_link VALUES ('07', '04');
- INSERT INTO product_category_link VALUES ('08', '04');
- --order_item, some items may not add up to total value in payment link, this is due to items not being fully paid for. E.g. a product being paid on finance.
- INSERT INTO order_item VALUES ('01', '01', '01', '01');
- INSERT INTO order_item VALUES ('02', '01', '01', '11');
- INSERT INTO order_item VALUES ('03', '02', '02', '07');
- INSERT INTO order_item VALUES ('04', '07', '09', '02');
- INSERT INTO order_item VALUES ('06', '08', '13', '03');
- INSERT INTO order_item VALUES ('07', '03', '11', '07');
- INSERT INTO order_item VALUES ('08', '03', '11', '15');
- INSERT INTO order_item VALUES ('09', '04', '11', '12');
- INSERT INTO order_item VALUES ('10', '04', '11', '07');
- INSERT INTO order_item VALUES ('11', '05', '03', '12');
- INSERT INTO order_item VALUES ('12', '05', '04', '01');
- INSERT INTO order_item VALUES ('13', '05', '04', '11');
- INSERT INTO order_item VALUES ('14', '06', '03', '12');
- INSERT INTO order_item VALUES ('15', '04', '05', '08');
- INSERT INTO order_item VALUES ('16', '06', '06', '09');
- INSERT INTO order_item VALUES ('17', '04', '06', '12');
- INSERT INTO order_item VALUES ('18', '06', '07', '14');
- INSERT INTO order_item VALUES ('19', '04', '08', '15');
- INSERT INTO order_item VALUES ('20', '05', '10', '13');
- INSERT INTO order_item VALUES ('21', '03', '12', '13');
- INSERT INTO order_item VALUES ('22', '04', '12', '11');
- SELECT *
- FROM customer order by customer_ID;
- SELECT * FROM customer, commercial
- where customer.customer_id = commercial.customer_id
- order by customer_name;
- SELECT * FROM customer, commercial, order_table
- where customer.customer_id = commercial.customer_id and customer.customer_id = order_table.customer_id
- order by customer.customer_id;
- SELECT customer.customer_id,
- customer.customer_name,
- delivery.delivery_id,
- delivery.postcode,
- delivery.house_number,
- delivery.delivered,
- delivery.delivery_timestamp
- FROM customer, order_table, order_item, delivery
- where customer.customer_id = order_table.customer_id and
- order_table.order_id = order_item.order_id and
- order_item.delivery_id = delivery.delivery_id and
- customer.customer_id = 1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement