Program To Solve Deliverable Queries Assignment Solution.

Instructions

Objective

Write a program to solve deliverable queries in sql.

Requirements and Specifications

Skills (S_EmpID, SkillName, Skill_Descr)

From here on, each normalized relation will be referred to as TABLE.

This submission will be a SINGLE DOCUMENT.

Your deliverable MUST be organized in this specific order:

Map ALL tables with respective columns (Refer Appendix, Table 1)
For each column in respective Table, determine the datatype, size, decimal positions, domain, and nullable (or not) (Refer Appendix, Table 2)

Incorporate at least 2 constraints (using DOMAIN) for those tables with more than 4 columns

CREATE tables using information from step#2. (Refer Appendix, Table 3)

Use CHECK CONSTRAINT for primary key(s) in your CREATE statement as applicable.

INSERT 8 Records in each Table (Refer Appendix, Table 4)

APPENDIX

Table 1: Mapping Table(s) from Deliverable#2

Table Name	Columns (excluding PK and FK)	Primary Key	Foreign Key
Customer	Cus_LName Cus_FName Cus_Initial Cus_Areacode Cus_Phone Cus_Balance

Table 2: Column Datatypes and other information
CUSTOMER Column Datatype Size Decimal Positions Domain Nullable? (Yes or No) CUS_NUM Numeric 8 0 > 0 NO CUS_LNAME Character 1 - 35 NO CUS_FNAME Character 1 - 30 NO CUS_INITIAL Character 1 YES CUS_AREACODE Numeric 3 0 ≥ 100 & ≤ 999 CUS_PHONE Numeric 7 0 ≥ 1010000 CUS_BALANCE Numeric 7 2 ≥ 0.00

Table 3: CREATE table and Screenshot

Table Name	Screenshot

Table 4: INSERT INTO and Screenshot

Action	Result

Source Code

create table CUSTOMER
(
    CUS_NUM number(8, 0) not null primary key,
    CUS_LNAME varchar2(35) not null,
    CUS_FNAME varchar2(30) not null,
    CUS_INITIAL varchar2(1),
    CUS_AREACODE number(3, 0),
    CUS_PHONE number(7,0),
    CUS_BALANCE number(7, 2),
 constraint cusnum_cnstr check (CUS_NUM > 0),
    constraint areacode_cnstr check (CUS_AREACODE >= 100 and CUS_AREACODE <= 999),
    constraint phone_cnstr check (CUS_PHONE >=1010000),
    constraint balance_cnstr check (CUS_BALANCE >= 0.00)
);
create table INVOICE
(
    INV_NUMBER number(8,0) primary key,
    CUS_NUM number(8,0),
 constraint inv_number_cnstr check (INV_NUMBER > 0),
    constraint CUS_NUM_REF foreign key (CUS_NUM) references CUSTOMER(CUS_NUM)
);
create table VENDOR
(
    V_CODE number(8,0) not null primary key,
    V_NAME varchar(20) not null,
    V_CONTACT varchar(35) not null,
    V_AREACODE number(3,0),
    V_PHONE number(7,0),
    V_STATE varchar(35),
    V_ORDER varchar(10),
    constraint vcode_cnstr check (V_CODE > 0),
    constraint v_areacode_cnstr check (V_AREACODE >= 100 and V_AREACODE <= 999),
    constraint v_phone_cnstr check (V_PHONE >=1010000),
    constraint vorder_cnstr check (V_ORDER = 'OPEN' or V_ORDER = 'PENDING' or V_ORDER = 'COMPLETE')
);
create table PRODUCT
(
    P_CODE number(8,0) not null primary key,
    P_DESCRIPT varchar2(40),
    P_INDATE date not null,
    P_ONHAND number(1,0),
    P_MIN number(8,0),
    P_PRICE number(8,2),
    P_DISCOUNT number(3,2),
    V_CODE number(8,0) not null,
    constraint pcode_cnstr check (P_CODE > 0),
    constraint pmin_cnstr check(P_MIN >= 0),
    constraint ponhand_cnstr check(P_ONHAND = 0 OR P_ONHAND = 1),
    constraint pprice_cnstr check (P_PRICE >= 0.00),
    constraint pdiscount_cnstr check (P_DISCOUNT >= 0.00),
    constraint vcode_ref_cnstr foreign key (V_CODE) references VENDOR(V_CODE)
)
create table LINE
(
    INV_NUMBER number(8,0) not null,
    LINE_NUMBER number(8,0) not null,
    P_CODE number(8,0) not null,
    LINE_UNITS number(8,0),
    LINE_PRICE number(8,2),
    constraint l_invnumber_ref foreign key (INV_NUMBER) references INVOICE(INV_NUMBER),
    constraint l_linenumber_cnstr check (LINE_NUMBER > 0),
    constraint l_pcode_ref foreign key (P_CODE) references PRODUCT(P_CODE),
    constraint l_lineunits_cnstr check (LINE_UNITS > 0),
    constraint l_lineprice_cnstr check (LINE_PRICE > 0.00),
    constraint l_PKS primary key(INV_NUMBER, LINE_NUMBER)
)
INSERT ALL
    INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(1, 'Wick', 'John', 'J', 111, 1234567, 0.73)
    INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(2, 'King', 'Martin', 'M', 652, 1254785, 999.99)
    INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(3, 'Hill', 'Mariah', 'H', 123, 4521452, 0.00)
    INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(4, 'Smith', 'Carl', 'C', 563, 1253658, 1256.31)
    INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(5, 'Shakespeare', 'William', 'S', 741, 1010001, 16556.25)
    INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(6, 'Smith', 'William', 'S', 963, 5214474, 0.00)
    INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(7, 'Carter', 'Monica', 'C', 852, 1020114, 63.52)
    INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(8, 'Cox', 'Charlie', 'C', 888, 1020205, 74.10)
SELECT * FROM DUAL;
INSERT ALL
    INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (1, 'Medical Stuff', 'msupplies@gmail.com', 152, 1234567, 'Florida', 'OPEN')
    INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (2, 'Sound Stuff', 'awesomesound@gmail.com', 133, 6593157, 'New York', 'PENDING')
    INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (3, 'Supermarket', 'Ms. Alex', 256, 6958214, 'Michigan', 'OPEN')
    INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (4, 'Amazon', 'amazon@amazon.com', 111, 5865321, 'DC', 'COMPLETE')
    INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (5, 'Ebay', 'ebay@gmail.com', 853, 9659658, 'Florida', 'OPEN')
    INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (6, 'Walmar', 'walmart@gmail.com', 321, 9632541, 'Miami', 'PENDING')
    INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (7, 'Dunder Mifflin', 'dmifflin@gmail.com', 777, 6353655, 'Kansas', 'OPEN')
    INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (8, 'PC Stuff', 'mpcsuppliess@gmail.com', 152, 6549185, 'New York', 'OPEN')
SELECT * FROM DUAL;
INSERT ALL
    INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (1, 'KN95 masks', TO_DATE('11/11/2021', 'dd/MM/yyyy'), 0, 150, 5.99, 6.00, 1)
    INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (2, 'Microphone', TO_DATE('16/11/2021', 'dd/MM/yyyy'), 1, 1, 65.99, 6.00, 2)
    INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (3, 'Vegetables', TO_DATE('12/11/2021', 'dd/MM/yyyy'), 0, 5, 12.49, 0.00, 3)
    INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (4, 'Pillows', TO_DATE('05/09/2021', 'dd/MM/yyyy'), 1, 2, 37.49, 0.00, 4)
    INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (5, 'Pokemon cards', TO_DATE('16/11/2021', 'dd/MM/yyyy'), 1, 10, 136.49, 5.00, 5)
    INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (6, 'Sewing Machine', TO_DATE('05/10/2021', 'dd/MM/yyyy'), 0, 1, 217.00, 0.00, 6)
    INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (7, 'Paper', TO_DATE('15/11/2021', 'dd/MM/yyyy'), 1, 20, 130.00, 2.00, 7)
    INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (8, 'Nvidia GPU', TO_DATE('10/11/2021', 'dd/MM/yyyy'), 1, 1, 569.99, 5.00, 8)
SELECT * FROM DUAL;

sql program to to-solve-deliverable-queries assignment solution.

Instructions

Requirements and Specifications