+1 (315) 557-6473 

sql program to 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:
  1. Map ALL tables with respective columns (Refer Appendix, Table 1)
  2. For each column in respective Table, determine the datatype, size, decimal positions, domain, and nullable (or not) (Refer Appendix, Table 2)
    1. Incorporate at least 2 constraints (using DOMAIN) for those tables with more than 4 columns
  3. CREATE tables using information from step#2. (Refer Appendix, Table 3)
    1. Use CHECK CONSTRAINT for primary key(s) in your CREATE statement as applicable.
  4. INSERT 8 Records in each Table (Refer Appendix, Table 4)
APPENDIX
Table 1: Mapping Table(s) from Deliverable#2
Table NameColumns (excluding PK and FK)Primary KeyForeign Key
CustomerCus_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;