Create a Program to Implement Various Commands in Sql Assignment Solution.

(

Apple_id CHAR(4) NOT NULL,

App_Fst_nme VARCHAR(30),

app_email VARCHAR(30),

app_address VARCHAR(30),

app_city VARCHAR(30),

app_state VARCHAR(30),

app_zip VARCHAR(30),

app_region VARCHAR(30)

);

ALTER TABLE customers ADD CONSTRAINT CUSTOMER_CST_NUM_PK PRIMARY KEY (Apple_id);

CREATE TABLE orders

(

Order_num CHAR(4) NOT NULL,

apple_id CHAR(4),

dec_number NUMBER(4,2),

order_date DATE,

ship_date DATE,

ship_street VARCHAR(30),

ship_city VARCHAR(30),

ship_state VARCHAR(30),

ship_zip VARCHAR(30),

ship_cost NUMBER(4,2),

FOREIGN KEY (apple_id) REFERENCES customers(Apple_id)

);

ALTER TABLE orders ADD CONSTRAINT ORDER_CST_NUM_PK PRIMARY KEY (Order_num);

CREATE TABLE item_code

(

item_code CHAR(4) NOT NULL,

item_type CHAR(4),

serial_num VARCHAR(30),

color VARCHAR(30),

item_cost NUMBER(4,2),

part_num CHAR(4)

);

ALTER TABLE item_code ADD CONSTRAINT ITEMCODE_CST_NUM_PK PRIMARY KEY (item_code);

CREATE TABLE plan_type_code

(

Plan_Type_Code CHAR(4) NOT NULL,

plan_type_description VARCHAR(30),

plan_nme VARCHAR(30),

start_date DATE,

end_date DATE

);

ALTER TABLE plan_type_code ADD CONSTRAINT PLAN_CST_NUM_PK PRIMARY KEY (Plan_Type_Code);

CREATE TABLE order_items

(

order_num CHAR(4) NOT NULL,

item_code CHAR(4),

plan_code CHAR(4),

color VARCHAR(30),

Qty NUMBER(4,2) DEFAULT 0,

paid_each VARCHAR(30),

FOREIGN KEY (order_num) REFERENCES orders(Order_num),

FOREIGN KEY (item_code) REFERENCES item_code(item_code),

FOREIGN KEY (plan_code) REFERENCES plan_type_code(Plan_Type_Code)

);

CREATE TABLE plan_type

(

app_id CHAR(4) NOT NULL,

plan_type_code CHAR(4),

plan_type_description VARCHAR(30),

plan_nme VARCHAR(30),

plan_type_service VARCHAR(30),

plan_cost NUMBER(4,2),

FOREIGN KEY (plan_type_code) REFERENCES plan_type_code(Plan_Type_Code)

);

ALTER TABLE plan_type ADD CONSTRAINT APPID_CST_NUM_PK PRIMARY KEY (app_id);

CREATE TABLE plan_type_cost

(

plan_cost NUMBER(4,2),

plan_type_code CHAR(4),

plan_type_description VARCHAR(30),

plan_nme VARCHAR(30),

plan_type_service VARCHAR(30),

FOREIGN KEY (plan_type_code) REFERENCES plan_type(plan_type_code);

);

CREATE TABLE item_type

(

app_id CHAR(4) NOT NULL,

item_code CHAR(4),

item_nme VARCHAR(30),

serial_num VARCHAR(30),

color VARCHAR(30),

item_cost NUMBER(4,2),

part_num CHAR(4),

FOREIGN KEY (item_code) REFERENCES item_code(item_code)

);

ALTER TABLE item_type ADD CONSTRAINT APPID2_CST_NUM_PK PRIMARY KEY (app_id);

DATA INSERTION

INSERT INTO customers (Apple_id, App_Lst_nme, App_Fst_nme, app_email, app_address, app_city, app_state, app_zip, app_region) VALUES ('1', 'robert', 'jackson', 'robert@gmail.com', 'St 9', 'Los Angeles', 'California', '11111', 'USA');

INSERT INTO customers (Apple_id, App_Lst_nme, App_Fst_nme, app_email, app_address, app_city, app_state, app_zip, app_region) VALUES ('2', 'Wale', 'hen', 'wale@gmail.com', 'St 6', 'Houston', 'Texas', '112233', 'USA');

INSERT INTO customers (Apple_id, App_Lst_nme, App_Fst_nme, app_email, app_address, app_city, app_state, app_zip, app_region) VALUES ('4', 'Jack', 'Perry', 'Jacky@gmail.com', 'ST12', 'Dallas', 'Texas', '112233', 'USA');

INSERT INTO item_code (item_code, item_type, serial_num, color, qty, item_cost, part_num) VALUES ('100', 'IPad', 'SQ789', 'Black', '20', '900', '753');

INSERT INTO item_code (item_code, item_type, serial_num, color, qty, item_cost, part_num) VALUES ('101', 'IMac', 'ST159', 'White', '5', '1500', '654');

INSERT INTO item_code (item_code, item_type, serial_num, color, qty, item_cost, part_num) VALUES ('102', 'IPhone', 'SA982', 'Space grey', '60', '799', '934');

INSERT INTO item_code (item_code, item_type, serial_num, color, qty, item_cost, part_num) VALUES ('104', 'ACC', 'SM561', 'Black', '2', '1800', '364');

INSERT INTO plan_type_code (Plan_Type_code, plan_type_description, plan_nme) VALUES ('PTC110', 'Plan Description for plan 110', 'SQT');

INSERT INTO plan_type_code (Plan_Type_code, plan_type_description, plan_nme) VALUES ('PTC111', 'Plan Description for plan 111', 'SPY');

INSERT INTO plan_type_code (Plan_Type_code, plan_type_description, plan_nme) VALUES ('PTC112', 'Plan Description for plan 112', 'SBM');

INSERT INTO orders (Order_num, apple_id, dec_num, order_date, ship_date, ship_street, ship_city, ship_state, ship_zip, ship_cost) VALUES ('1', '1', '86', current_date(), current_date(), 'ST9', 'Los Angeles', 'Califonia', '11422', '1000');

INSERT INTO orders (Order_num, apple_id, dec_num, order_date, ship_date, ship_street, ship_city, ship_state, ship_zip, ship_cost) VALUES ('2', '1', '87', current_date(), current_date(), 'ST9', 'Los Angeles', 'Califonia', '11422', '800');

INSERT INTO orders (Order_num, apple_id, dec_num, order_date, ship_date, ship_street, ship_city, ship_state, ship_zip, ship_cost) VALUES ('3', '1', '88', current_date(), current_date(), 'ST9', 'Los Angeles', 'Califonia', '11422', '1200');

INSERT INTO orders (Order_num, apple_id, dec_num, order_date, ship_date, ship_street, ship_city, ship_state, ship_zip, ship_cost) VALUES ('4', '1', '89', current_date(), current_date(), 'ST9', 'Los Angeles', 'Califonia', '11422', '100');

INSERT INTO orders (Order_num, apple_id, dec_num, order_date, ship_date, ship_street, ship_city, ship_state, ship_zip, ship_cost) VALUES ('5', '1', '90', current_date(), current_date(), 'ST9', 'Los Angeles', 'Califonia', '11422', '850');

INSERT INTO orders (Order_num, apple_id, dec_num, order_date, ship_date, ship_street, ship_city, ship_state, ship_zip, ship_cost) VALUES ('6', '1', '91', current_date(), current_date(), 'ST9', 'Los Angeles', 'Califonia', '11422', '50');

INSERT INTO orders (Order_num, apple_id, dec_num, order_date, ship_date, ship_street, ship_city, ship_state, ship_zip, ship_cost) VALUES ('7', '2', '92', current_date(), current_date(), 'ST9', 'Houston', 'Texas', '112233', '160');

INSERT INTO orders (Order_num, apple_id, dec_num, order_date, ship_date, ship_street, ship_city, ship_state, ship_zip, ship_cost) VALUES ('8', '2', '93', current_date(), current_date(), 'ST9', 'Houston', 'Texas', '112233', '1020');

INSERT INTO order_items(order_num, item_code, plan_code, color, Qty, paid_each) values('1','100','PTC110','Black','1','1200');

INSERT INTO order_items(order_num, item_code, plan_code, color, Qty, paid_each) values('2','101','PTC110','white','1','1000');

INSERT INTO order_items(order_num, item_code, plan_code, color, Qty, paid_each) values('3','102','PTC110','Black','2','1800');

INSERT INTO order_items(order_num, item_code, plan_code, color, Qty, paid_each) values('4','104','PTC110','white','1','1300');

INSERT INTO order_items(order_num, item_code, plan_code, color, Qty, paid_each) values('5','100','PTC111','space grey','1','800');

INSERT INTO order_items(order_num, item_code, plan_code, color, Qty, paid_each) values('6','102','PTC112','space grey','3','300');

INSERT INTO order_items(order_num, item_code, plan_code, color, Qty, paid_each) values('7','104','PTC112','Black','1','600');

INSERT INTO order_items(order_num, item_code, plan_code, color, Qty, paid_each) values('8','102','PTC111','Black','1','1300');

INSERT INTO apple_watch (item_code, item_nme, serial_num, plan_type_code, plan_type_description, plan_service, plan_cost, qty, color) VALUES ('1003', 'Iwatch', '567', 'PTC110', 'Plan Description for plan 110', 'SQT', '250', '6', 'Black');

INSERT INTO apple_music (item_code, item_nme, serial_num, plan_type_code, plan_type_description, plan_nme, plan_service, plan_cost, qty, color) VALUES ('106', 'IMusic', '564', 'PTC111', 'Plan Description for plan 111', 'SPY', 'Service 1', '300', '3', 'White');

insert into imac (item_code, item_nme, serial_num, color, qty, item_cost, part_num) values('102','IMac','734','Black','4','1300','167');

insert into ipad (item_code, item_nme, serial_num, color, qty, item_cost, part_num) values('101','IPad','354','Black','3','800','463');

insert into iphone (item_code, item_nme, serial_num, color, qty, item_cost, part_num) values('100','IPhone','571','Space Grey','9','1200','617');

insert into item_type (app_id, item_code, item_nme, serial_num, color, qty, item_cost, part_num) values('1', '100','IPhone','945','Space Grey','20','750','648');

insert into imac (item_code, item_nme, serial_num, color, qty, item_cost, part_num) values('102','IMac','734','Black','4','1300','167');

insert into ipad (item_code, item_nme, serial_num, color, qty, item_cost, part_num) values('101','IPad','354','Black','3','800','463');

insert into iphone (item_code, item_nme, serial_num, color, qty, item_cost, part_num) values('100','IPhone','571','Space Grey','9','1200','617');

insert into item_type (app_id, item_code, item_nme, serial_num, color, qty, item_cost, part_num) values('1', '100','IPhone','945','Space Grey','20','750','648');

INSERT INTO plan_type (app_id, plan_type_code, plan_type_description, plan_nme, plan_type_service, plan_cost) VALUES ('1', 'PTC110', 'description', 'plan 1', 'service 1', '160');

insert into plan_type_cost(plan_cost, plan_type_code, plan_type_description, plan_nme, plan_type_service) values('230','PTC110','description','Plan 1','Service 1');