+1 (315) 557-6473 

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


GET FREE QUOTE
  1. Home
  2. Program to Implement Various Commands in Sql Assignment Solution.

Instructions

Objective
If you want to ace your SQL assignment, start by writing a program to implement various commands in the SQL language. Familiarize yourself with SQL syntax and its different commands such as SELECT, INSERT, UPDATE, DELETE, and JOIN, among others. Practice constructing queries to retrieve and manipulate data from databases effectively. Understanding the relationships between tables and using appropriate SQL clauses will significantly enhance your ability to handle complex database operations. With dedication and practice, you'll be well on your way to mastering SQL and acing your assignment.

Requirements and Specifications

program to implement various commands in sql

Source Code

CREATE TABLE CUSTOMERS

(

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');