In this blog post I will be teaching you how to create Sample Oracle Database Tables for PLSQL Queries Practice. You can use this Sample Database tables with Data for practicing PLSQL Queries or even practicing SQL Queries.

I created a Sample Database for an Currency Exchange Company whose main business is to exchange money for its registered customers. Its a perfect scenario for all you Database Programmers to get your hands on this type of Organizational Database for practicing SQL queries and strengthen you command on SQL.

Lets Identify the Users for Creating Sample Oracle Database Tables for PLSQL Queries Practice

Customers: 
Customers use the system to fill their registration information. Due to security restrictions, customers cannot modify or delete their information once it is entered in the system.

Front Desk Staff:
Front desk staffs use the system to register or verify customer’s profile. They have a permission to modify customer’s information and most importantly they will enter the transactions in the system.

Supervisor:
Supervisors will have full access to data stored in the database to solve business issues and assist front desk staff to overcome them. Moreover, they use the system to generate report for the management and accountants.

Manager/ Administrator:
Management/ Administrator uses the system to add or modify information for each employee and to grant their access level to the system. He also uses the system to solve any unanticipated issues and to perform data mining and creating backup of the system in regular basis.

ERD DIAGRAM

Sample Oracle Database Tables for PLSQL Queries Practice

SQL CODE TO CREATE SAMPLE DATABASE TABLES

CREATE TABLE CUSTOMERS(
CUSTOMERID NUMBER(12),FIRSTNAME VARCHAR2(30) NOT NULL,

LASTNAME VARCHAR2(30) NOT NULL,

DATEOFBIRTH DATE NOT NULL,

GENDER CHAR NOT NULL,

NATIONALITY CHAR(2) NOT NULL,

RESIDENCYSTATUS CHAR NOT NULL,

ID_NUMBER NUMBER(20) NOT NULL,

REGISTRATIONDATE DATE NOT NULL,

VERIFICATIONSTATUS CHAR NOT NULL);

Create Sample Customer Table

CREATE TABLE TRANSACTIONS (

TRANSACTIONID NUMBER(24),

CUSTOMERID NUMBER(12) NOT NULL,

EMPLOYEEID NUMBER(7) NOT NULL,

“DATE” DATE NOT NULL,

EXCHANGERATE NUMBER(6,2) NOT NULL,

MONEYAMOUNT NUMBER(10,2) NOT NULL,

CURRENCYTYPE CHAR(3) NOT NULL,

PAYMENTMETHOD CHAR NOT NULL,

DELIVERYMETHOD CHAR NOT NULL);

Create Sample Transaction Table

CREATE TABLE EMPLOYEES (

EMPLOYEEID NUMBER(7),

FIRSTNAME VARCHAR2(30) NOT NULL,

LASTNAME VARCHAR2(30) NOT NULL,

DATEOFBIRTH DATE NOT NULL,

SIN# NUMBER(9) NOT NULL,

MONTHLYSALARY NUMBER(6,2) NOT NULL,

YEARLYBONUS NUMBER(7,2),

POSITION VARCHAR2(30) NOT NULL,

EMPLOYEMENTDATE DATE NOT NULL

MNG NUMBER(7) );

Create Sample Employee Table

CREATE TABLE CUSTOMERSFINANCIAL(

FINANCIALID NUMBER(14),

CUSTOMERID NUMBER (12),

BANKNAME VARCHAR2(30)NOT NULL,

BRANCHCODE VARCHAR2(30) NOT NULL,

ACCOUNTTYPE CHAR NOT NULL,

ACCOUNT# NUMBER(24) NOT NULL);

Create Sample CustomerFinancial Table

CREATE TABLE EMPLOYEESFINANCIAL(

FINANCIALID NUMBER(4),

EMPLOYEEID NUMBER(7),

BANKNAME VARCHAR2(30)NOT NULL,

BRANCHCODE VARCHAR2(30) NOT NULL,

ACCOUNTTYPE CHAR NOT NULL,

ACCOUNT# NUMBER(24) NOT NULL);

Create Sample EmployeeFinancial Table

CREATE TABLE CUSTOMERSCONTACT(

CONTACTID NUMBER(14),

CUSTOMERID NUMBER(12),

UNIT# NUMBER(7),

STREET# NUMBER(7) NOT NULL,

STREETNAME VARCHAR2(30) NOT NULL,

CITY VARCHAR2(30) NOT NULL,

PROVINCE VARCHAR2(30) NOT NULL,

COUNTRY VARCHAR2(30) NOT NULL,

POSTALCODE VARCHAR2(12),

PHONE# NUMBER(10) NOT NULL,

ALTERNATIVEPHONE# NUMBER(10),

EMAIL VARCHAR2(30));

Create Sample Customers Contact Table

CREATE TABLE EMPLOYEESCONTACT(

CONTACTID NUMBER(4),

EMPLOYEEID NUMBER(7),

UNIT# NUMBER(7),

STREET# NUMBER(7) NOT NULL,

STREETNAME VARCHAR2(30) NOT NULL,

CITY VARCHAR2(30) NOT NULL,

PROVINCE VARCHAR2(30) NOT NULL,

COUNTRY VARCHAR2(30) NOT NULL,

POSTALCODE VARCHAR2(12),

PHONE# NUMBER(10) NOT NULL,

ALTERNATIVEPHONE# NUMBER(10),

EMAIL VARCHAR2(30));

Create Sample Employees Contact Table

SQL CODE TO ADD CONSTRAINTS TO DATABASE TABLES

ALTER TABLE CUSTOMERS
ADD CONSTRAINT CUSTOMERS_CUSTOMERID_PK PRIMARY KEY(CUSTOMERID);
ALTER TABLE CUSTOMERSCONTACT
ADD CONSTRAINT CCONTACT_CONTACTID_PK PRIMARY KEY(CONTACTID);
ALTER TABLE EMPLOYEESCONTACT
 ADD CONSTRAINT ECONTACT_CONTACTID_PK PRIMARY KEY(CONTACTID);
ALTER TABLE CUSTOMERSFINANCIAL
    ADD CONSTRAINT CFINANCIAL_FINANCIALID_PK PRIMARY KEY(FINANCIALID;
ALTER TABLE EMPLOYEESFINANCIAL
    ADD CONSTRAINT EFINANCIAL_FINANCIALID_PK PRIMARY KEY(FINANCIALID);
ALTER TABLE TRANSACTIONS
     ADD CONSTRAINT TRANSACTIONS_TRANSACTIONID_PK PRIMARY KEY(TRANSACTIONID);
ALTER TABLE EMPLOYEES
   ADD CONSTRAINT EMPLOYEES_EMPLOYEEID_PK PRIMARY KEY(EMPLOYEEID);
ALTER TABLE CUSTOMERSCONTACT
 ADD CONSTRAINT CCONTACT_CUSTOMERID_FK FOREIGN KEY (CUSTOMERID)
 REFERENCES CUSTOMERS(CUSTOMERID);
ALTER TABLE EMPLOYEESCONTACT
  ADD CONSTRAINT ECONTACT_EMPLOYEESID_FK FOREIGN KEY (EMPLOYEEID)
 REFERENCES EMPLOYEES(EMPLOYEEID);
ALTER TABLE CUSTOMERSFINANCIAL
ADD CONSTRAINT CFINANCIAL_CUSTOMERID_FK FOREIGN KEY (CUSTOMERID)
REFERENCES CUSTOMERS(CUSTOMERID);
ALTER TABLE EMPLOYEESFINANCIAL
ADD CONSTRAINT EFINANCIAL_EMPLOYEESID_FK FOREIGN KEY (EMPLOYEEID)
REFERENCES EMPLOYEES(EMPLOYEEID);
ALTER TABLE TRANSACTIONS
ADD CONSTRAINT TRANSACTIONS_CUSTOMERID_FK FOREIGN KEY (CUSTOMERID)
REFERENCES CUSTOMERS(CUSTOMERID);
ALTER TABLE TRANSACTIONS
    ADD CONSTRAINT TRANSACTIONS_EMPLOYEEID_FK FOREIGN KEY (EMPLOYEEID)
    REFERENCES EMPLOYEES(EMPLOYEEID);
ALTER TABLE EMPLOYEES
    ADD CONSTRAINT EMPLOYEES_MNG_FK FOREIGN KEY (MNG)
    REFERENCES EMPLOYEES(EMPLOYEEID);
ALTER TABLE CUSTOMERS
ADD CONSTRAINT CUSTOMER_GENDER_CK
CHECK ( GENDER IN ('M', 'F', 'O'));
ALTER TABLE CUSTOMERS
  ADD CONSTRAINT CUSTOMER_RESIDENCY_CK
 CHECK ( RESIDENCYSTATUS IN ('V', 'R'));
ALTER TABLE CUSTOMERS
 ADD CONSTRAINT CUSTOMER_VERIFICATION_CK
CHECK ( VERIFICATIONSTATUS IN ('A', 'N', 'D'));
ALTER TABLE TRANSACTIONS
 ADD CONSTRAINT TRANSACTIONS_PAYMENT_CK
CHECK ( PAYMENTMETHOD IN ('C', 'D', 'Q', 'R', 'T', 'O'));
ALTER TABLE TRANSACTIONS
ADD CONSTRAINT TRANSACTIONS_DELIVERY_CK
CHECK ( DELIVERYMETHOD IN ('C', 'D', 'Q', 'O'));
ALTER TABLE CUSTOMERSFINANCIAL
ADD CONSTRAINT CFINANCIAL_ACCOUNTTYPE_CK
CHECK ( ACCOUNTTYPE IN ('S', 'C', 'R'));
ALTER TABLE CUSTOMERSFINANCIAL
ADD CONSTRAINT EFINANCIAL_ACCOUNTTYPE_CK
CHECK ( ACCOUNTTYPE IN ('S', 'C', 'R'));
ALTER TABLE CUSTOMERSCONTACT
    ADD CONSTRAINT CCONTACT_CUSTOMERID_UQ UNIQUE(CUSTOMERID);
ALTER TABLE EMPLOYEESCONTACT
    ADD CONSTRAINT ECONTACT_EMPLOYEEID_UQ UNIQUE(EMPLOYEEID);

SQL CODE TO ADD SAMPLE DATA TO DATABASE TABLES

INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS) 
VALUES (525000000001, 'JOHN', 'SMITH', '1957-10-01', 'M', 'US', 'R', 07985198754074, '1997-02-21', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS) 
VALUES (525000000002, 'WILL', 'SMITH', '1977-11-21', 'M', 'US', 'R', 07998199754083, '2017-02-21', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS) 
VALUES (525000000003, 'JACK', 'SPARROW', '1987-01-03', 'M', 'AU', 'V', 05485198754062, '2017-03-21', 'N');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (525000000004, 'HILLARY', 'CLINTON', '1966-04-09', 'F', 'CA', 'R', 090851987540732, '1999-05-30', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (525000000005, 'TONY', 'BLAIR', '1989-08-23', 'M', 'EN', 'V', 08585198754099, '2015-06-21', 'D');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (525000000006, 'LIONEL', 'MESSI', '1998-08-22', 'M', 'AR', 'V', 07685198754088, '2017-01-21', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (525000000007, 'NEYMAR', 'JUNIOR', '1998-09-17', 'M', 'BR', 'V', 07585198754045, '2017-01-21', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (525000000008, 'GWEN', 'STEFANI', '1979-07-14', 'F', 'CA', 'R', 09085198754584, '2011-07-13', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (525000000009, 'MARGARETH', 'BROOKS', '1987-08-16', 'F', 'CA', 'V', 09085198754874, '2013-05-17', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (788000000001, 'CAROLYN', 'DOE', '1987-04-04', 'F', 'CA', 'R', 09085198754555, '2016-02-25', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (788000000002, 'JOANNE', 'GELLAR', '1988-11-04', 'F', 'CA', 'R', 09098199754333, '2016-11-21', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (788000000003, 'JACK', 'BROWN', '1979-01-31', 'M', 'CA', 'R', 09085198754062, '2017-03-21', 'N');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (788000000004, 'BILL', 'CLINTON', '1956-04-09', 'M', 'CA', 'R', 090851987540832, '2016-05-30', 'A');
INSERT INTO CUSTOMERS (CUSTOMERID, FIRSTNAME, LASTNAME, DATEOFBIRTH, GENDER, NATIONALITY, RESIDENCYSTATUS, ID_NUMBER, REGISTRATIONDATE, VERIFICATIONSTATUS)
  VALUES (788000000005, 'PABLO', 'FREITAS', '1989-08-15', 'M', 'AR', 'V', 07685198754222, '2016-11-21', 'D');
INSERT INTO EMPLOYEES (EMPLOYEEID, FIRSTNAME, LASTNAME, DATEOFBIRTH, SIN#, MONTHLYSALARY, YEARLYBONUS, POSITION, EMPLOYEMENTDATE)
  VALUES (5259876, 'SARAH', 'FISHER', '1988-07-31', 987600555, 3000.00, NULL, 'FRONT DESK STAFF', '2016-05-23');
INSERT INTO EMPLOYEES (EMPLOYEEID, FIRSTNAME, LASTNAME, DATEOFBIRTH, SIN#, MONTHLYSALARY, YEARLYBONUS, POSITION, EMPLOYEMENTDATE)
  VALUES (5258765, 'REBECCA', 'SIMPSONS', '1990-08-22', 876500345, 3100.00, 1350.00, 'FRONT DESK STAFF', '2015-05-23');
INSERT INTO EMPLOYEES (EMPLOYEEID, FIRSTNAME, LASTNAME, DATEOFBIRTH, SIN#, MONTHLYSALARY, YEARLYBONUS, POSITION, EMPLOYEMENTDATE)
  VALUES (5257654, 'MICHELE', 'PFIFFER', '1978-03-28', 765400234, 4500.00, 2500.00, 'SUPERVISOR', '2011-08-29');
INSERT INTO EMPLOYEES (EMPLOYEEID, FIRSTNAME, LASTNAME, DATEOFBIRTH, SIN#, MONTHLYSALARY, YEARLYBONUS, POSITION, EMPLOYEMENTDATE)
  VALUES (5256543, 'MICHAEL', 'JORDAN', '1970-07-26', 654300123, 5500.00, 3700.00, 'MANAGER', '2000-09-24');
INSERT INTO EMPLOYEES (EMPLOYEEID, FIRSTNAME, LASTNAME, DATEOFBIRTH, SIN#, MONTHLYSALARY, YEARLYBONUS, POSITION, EMPLOYEMENTDATE)
  VALUES (7885432, 'SHIRLEY', 'VALENTINE', '1990-08-26', 543200345, 2900.00, 1100.00, 'FRONT DESK STAFF', '2016-05-23');
INSERT INTO EMPLOYEES (EMPLOYEEID, FIRSTNAME, LASTNAME, DATEOFBIRTH, SIN#, MONTHLYSALARY, YEARLYBONUS, POSITION, EMPLOYEMENTDATE)
  VALUES (7884321, 'SARAH', 'MCDONALDS', '1978-09-28', 432100234, 4200.00, 2450.00, 'SUPERVISOR', '2016-02-28');
INSERT INTO EMPLOYEES (EMPLOYEEID, FIRSTNAME, LASTNAME, DATEOFBIRTH, SIN#, MONTHLYSALARY, YEARLYBONUS, POSITION, EMPLOYEMENTDATE)
  VALUES (7881234, 'SOFIA', 'COPOLA', '1970-01-26', 123400123, 5150.00, 3680.00, 'MANAGER', '2016-01-01');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100001, 525000000001, 20211, 18, 'YONGE ST', 'TORONTO', 'ONTARIO', 'CANADA', 'A9A 9A9', 4164529876, 4164526789, 'JSMITH@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100002, 525000000002, 30001, 11, 'ELM ST', 'TORONTO', 'ONTARIO', 'CANADA', 'Z8Z 8Z8', 4168745412, 4168749658, 'WSMITH@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100003, 525000000003, 301, 248, 'SHEPPARD AV', 'TORONTO', 'ONTARIO', 'CANADA', 'M3M 8Z8', 4168745847, 4168745848, 'JSPARROW@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100004, 525000000004, 20123, 1111, 'YONGE ST', 'TORONTO', 'ONTARIO', 'CANADA', 'Z8Z 8C0', 4168744859, 4168744852, 'HCLINTON@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100005, 525000000005, 30005, 121, 'VICTORIA PARK AV', 'TORONTO', 'ONTARIO', 'CANADA', 'N9N 8Z8', 4168744563, 4168749652, 'TBLAIR@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100006, 525000000006, 30021, 421, 'WARDEN AV', 'TORONTO', 'ONTARIO', 'CANADA', 'V7V 8Z8', 4168745147, 4168749741, NULL);
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100007, 525000000007, 2201, 181, 'BLOOR ST', 'TORONTO', 'ONTARIO', 'CANADA', 'M8M 8Z8', 4168745852, 4168749258, 'NJUNIOR@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100008, 525000000008, 2110, 748, 'BLOOR ST', 'TORONTO', 'ONTARIO', 'CANADA', 'M8M 8Z8', 4168749632, 4168736958, 'GSTEFANI@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100009, 525000000009, 30110, 854, 'YONGE ST', 'TORONTO', 'ONTARIO', 'CANADA', 'Z8Z 8Z8', 4168754612, 4168456658, 'MBROOKS@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100010, 788000000001, 20001, 111, 'SPADINA AV', 'TORONTO', 'ONTARIO', 'CANADA', 'V7B 8Z8', 4168747892, 4168747538, NULL);
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100011, 788000000002, 22014, 411, 'COLEGE ST', 'TORONTO', 'ONTARIO', 'CANADA', 'C8Z V7B', 4168798712, 4168795158, 'JGELLAR@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100012, 788000000003, 30141, 115, 'DUNDAS ST', 'TORONTO', 'ONTARIO', 'CANADA', 'M9K 8Z8', 4168123412, 4168735758, NULL);
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100013, 788000000004, 11425, 181, 'YONGE ST', 'TORONTO', 'ONTARIO', 'CANADA', 'E8Z 0H7', 4168963412, 4168715958, 'BCLINTON@GMAIL.COM');
INSERT INTO CUSTOMERSCONTACT (CONTACTID, CUSTOMERID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (10001000100014, 788000000005, 11251, 1771, 'YONGE ST', 'TORONTO', 'ONTARIO', 'CANADA', 'M8J 8Z8', 4168741472, 4168742658, NULL);
INSERT INTO EMPLOYEESCONTACT (CONTACTID, EMPLOYEEID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (20001000100001, 5259876, 20222, 1147, 'YONGE ST', 'TORONTO', 'ONTARIO', 'CANADA', 'H7A 9A9', 4164521234, 4164524321, 'SFISHER@GMAIL.COM');
INSERT INTO EMPLOYEESCONTACT (CONTACTID, EMPLOYEEID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (20001000100002, 5258765, 223, 121, 'ELM ST', 'TORONTO', 'ONTARIO', 'CANADA', 'A8A 9A9', 4164521254, 4164512521, 'RSIMPSONS@GMAIL.COM');
INSERT INTO EMPLOYEESCONTACT (CONTACTID, EMPLOYEEID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (20001000100003, 5257654, 215, 365, 'BLOOR ST', 'TORONTO', 'ONTARIO', 'CANADA', 'J8J 9A9', 4164521951, 4164524452, 'MPFIEFFER@GMAIL.COM');
INSERT INTO EMPLOYEESCONTACT (CONTACTID, EMPLOYEEID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (20001000100004, 5256543, 2343, 1245, 'YONGE ST', 'TORONTO', 'ONTARIO', 'CANADA', 'A3A 9A9', 4164521458, 4164524258, 'MJORDAN@GMAIL.COM');
INSERT INTO EMPLOYEESCONTACT (CONTACTID, EMPLOYEEID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (20001000100005, 7885432, 20233, 258, 'ELM ST', 'TORONTO', 'ONTARIO', 'CANADA', 'M8M 9A9', 4164521234, 4168744875, 'SVALENTINE@GMAIL.COM');
INSERT INTO EMPLOYEESCONTACT (CONTACTID, EMPLOYEEID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (20001000100006, 7884321, 2514, 368, 'FINCH AV', 'TORONTO', 'ONTARIO', 'CANADA', 'Z5Z 9A9', 4164521684, 4164524486, 'SMCDONALDS@GMAIL.COM');
INSERT INTO EMPLOYEESCONTACT (CONTACTID, EMPLOYEEID, UNIT#, STREET#, STREETNAME, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE#, ALTERNATIVEPHONE#, EMAIL)
  VALUES (20001000100007, 7881234, 20282, 1147, 'FINCH AV', 'TORONTO', 'ONTARIO', 'CANADA', 'Z5Z 9A9', 4164521419, 4164524941, 'SCOPOLA@GMAIL.COM');
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010001, 525000000001, 'RBC', 'R999', 'S', 999565565000000000000111);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010002, 525000000001, 'RBC', 'R999', 'R', 999565565000000000000112);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010003, 525000000002, 'RBC', 'R999', 'C', 999565565000000000000222);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010004, 525000000003, 'TD', 'T2222', 'S', 444565565000000000000333);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010005, 525000000004, 'TD', 'T2222', 'S', 444565565000000000000444);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010006, 525000000004, 'RBC', 'R999', 'C', 999565565000000000000445);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010007, 525000000006, 'RBC', 'R999', 'S', 999565565000000000000555);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010008, 525000000007, 'RBC', 'R999', 'S', 999565565000000000000666);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010009, 525000000008, 'TD', 'T2222', 'S', 444565565000000000000777);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010010, 525000000009, 'RBC', 'R999', 'R', 999565565000000000000888);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010011, 525000000009, 'RBC', 'R999', 'C', 999565565000000000000889);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010012, 525000000005, 'RBC', 'R999', 'S', 999565565000000000000000);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010013, 788000000001, 'TD', 'T2222', 'S', 444565565000000000000111);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010014, 788000000001, 'TD', 'T2222', 'R', 444565565000000000000112);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010015, 788000000002, 'RBC', 'R999', 'S', 999565565000000000000122);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010016, 788000000003, 'RBC', 'R999', 'S', 999565565000000000000229);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010016, 788000000003, 'RBC', 'R999', 'C', 999565565000000000000230);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010016, 788000000003, 'RBC', 'R999', 'R', 999565565000000000000231);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010016, 788000000004, 'TD', 'T2222', 'S', 444565565000000000000558);
INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (10010010010016, 788000000005, 'TD', 'T2222', 'S', 999565565000000000000887);
INSERT INTO EMPLOYEESFINANCIAL (FINANCIALID, EMPLOYEEID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (1001, 5259876, 'RBC', 'R999', 'S', 999565565000000000001111);
INSERT INTO EMPLOYEESFINANCIAL (FINANCIALID, EMPLOYEEID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (1002, 5258765, 'RBC', 'R999', 'S', 999565565000000000001112);
INSERT INTO EMPLOYEESFINANCIAL (FINANCIALID, EMPLOYEEID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (1003, 5257654, 'TD', 'T2222', 'S', 444565565000000000001111);
INSERT INTO EMPLOYEESFINANCIAL (FINANCIALID, EMPLOYEEID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (1004, 5256543, 'TD', 'T2222', 'S', 444565565000000000001112);
INSERT INTO EMPLOYEESFINANCIAL (FINANCIALID, EMPLOYEEID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (1005, 7885432, 'TD', 'T2222', 'S', 444565565000000000001113);
INSERT INTO EMPLOYEESFINANCIAL (FINANCIALID, EMPLOYEEID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (1006, 7884321, 'RBC', 'R999', 'S', 999565565000000000001113);
INSERT INTO EMPLOYEESFINANCIAL (FINANCIALID, EMPLOYEEID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES (1007, 7881234, 'RBC', 'R999', 'S', 999565565000000000001114);
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 5250000000012016053101, 525000000001, 5259876, '2016-05-31', 11.25, 10000.00, 'USD', 'O', 'C');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 5250000000012016063001, 525000000001, 5257654, '2016-06-30', 10.25, 5000.00, 'USD', 'C', 'Q');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 5250000000022016053101, 525000000002, 5259876, '2016-05-31', 11.25, 1000.00, 'USD', 'C', 'D');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 5250000000042016053101, 525000000004, 5258765, '2016-05-31', 11.25, 7000.00, 'USD', 'D', 'Q');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 5250000000062016063001, 525000000006, 5259876, '2016-06-30', 5.37, 7000.00, 'CND', 'T', 'Q');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 5250000000072016063001, 525000000007, 5258765, '2016-06-30', 5.37, 4500.00, 'CND', 'R', 'C');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 5250000000082016063001, 525000000008, 5257654, '2016-06-30', 5.37, 9999.00, 'CND', 'D', 'O');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 5250000000092016063001, 525000000009, 5256543, '2016-06-30', 10.25, 9999.00, 'USD', 'O', 'O');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 7880000000012016053101, 788000000001, 7885432, '2016-05-31', 11.25, 4870.00, 'USD', 'D', 'Q');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 7880000000012016063001, 788000000001, 7885432, '2016-06-30', 10.25, 3000.00, 'USD', 'D', 'Q');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 7880000000012016063002, 788000000001, 7885432, '2016-06-30', 5.37, 3000.00, 'USD', 'O', 'C');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 7880000000022016053101, 788000000002, 7885432, '2016-05-31', 6.11, 5500.00, 'CND', 'D', 'Q');
INSERT INTO TRANSACTIONS (TRANSACTIONID, CUSTOMERID, EMPLOYEEID, "DATE", EXCHANGERATE, MONEYAMOUNT, CURRENCYTYPE, PAYMENTMETHOD, DELIVERYMETHOD)
  VALUES ( 7880000000022016053102, 788000000002, 7885432, '2016-05-31', 11.25, 5500.00, 'USD', 'D', 'Q');
INSERT INTO EMPLOYEES(MNG)
  VALUES(5256543)
  WHERE 'EMPLOYEEID' = 5259876;
UPDATE EMPLOYEES
  SET MNG = 5256543
  WHERE EMPLOYEEID = 5259876;
UPDATE EMPLOYEES
  SET MNG = 5256543
  WHERE EMPLOYEEID = 5258765;
UPDATE EMPLOYEES
  SET MNG = 5256543
  WHERE EMPLOYEEID = 5257654;
UPDATE EMPLOYEES
  SET MNG = 7881234
  WHERE EMPLOYEEID = 7885432;
UPDATE EMPLOYEES
  SET MNG = 7881234
  WHERE EMPLOYEEID = 7884321;

SQL QUERIES TO DISPLAY DATABASE TABLES

DISPLAY CUSTOMERS TABLE DATA
DISPLAY CUSTOMERSCONTACT TABLE DATA
DISPLAY CUSTOMERFINANCIAL TABLE DATA
DISPLAY EMPLOYEES TABLE DATA
DISPLAY EMPLOYEESCONTACT TABLE DATA
DISPLAY EMPLOYEESFINANCIAL TABLE DATA
DISPLAY TRANSACTION TABLE DATA
DISPLAYS THE CUSTOMER AND RESPECTIVE CUSTOMER’S BANK
DISPLAYS THE CUSTOMER AND HIS BANK
DISPLAYS PERSONAL INFORMATION OF AN EMPLOYEE
DISPLAYS A GROUP OF EMPLOYEES THAT BELONGS TO A MANAGER
DISPLAYS TRANSACTIONS MADE BY CUSTOMERS
DISPLAYS THE TRANSACTIONS MADE BY THE EMPLOYEE
CUSTOMERS WITH LASTNAME SMITH
CUSTOMERS THAT WERE NOT APPROVED

PLSQL STORED PROCEDURE

CREATE OR REPLACE PROCEDURE
add_new_customer_sp(c_id IN customers.customerid%type,
c_fname IN customers.firstname%type,
c_lname IN customers.lastname%type,
c_dob IN customers.dateofbirth%type,
c_gen IN customers.gender%type,
c_nat IN customers.nationality%type,
c_res IN customers.residencystatus%type,
c_idn IN customers.id_number%type,
c_regdate IN customers.registrationdate%type,
c_verstatus IN customers.verificationstatus%type)
IS
BEGIN
  INSERT INTO customers(customerid, firstname, lastname, dateofbirth, gender, nationality, residencystatus, id_number, registrationdate, verificationstatus) 
  VALUES (c_id, c_fname, c_lname, c_dob, c_gen, c_nat, c_res, c_idn, c_regdate, c_verstatus); 
  COMMIT;
END;
SET SERVEROUTPUT ON
BEGIN
add_new_customer_sp(788000000555, 'VISHAL', 'TEJI', '1988-10-01', 'M', 'CA', 'R', 07985198754786, '2017-08-06', 'A');
END;

SELECT * FROM CUSTOMERS;

Program to add new customer
CREATE OR REPLACE PROCEDURE 
add_new_employee_sp(e_id IN employees.employeeid%type,
e_fname IN employees.firstname%type,
e_lname IN employees.lastname%type,
e_dob IN employees.dateofbirth%type,
e_sin IN employees.sin#%type,
e_msalary IN employees.monthlysalary%type,
e_ybonus IN employees.yearlybonus%type,
e_pos IN employees.position%type,
e_empdate IN employees.employementdate%type,
e_mng IN employees.mng%type)
IS
BEGIN
  INSERT INTO EMPLOYEES (EMPLOYEEID, FIRSTNAME, LASTNAME, DATEOFBIRTH, SIN#, MONTHLYSALARY, YEARLYBONUS, POSITION, EMPLOYEMENTDATE, MNG)
  VALUES (e_id, e_fname, e_lname, e_dob, e_sin, e_msalary, e_ybonus, e_pos, e_empdate, e_mng);
  COMMIT;
END;
SET SERVEROUTPUT ON
BEGIN
add_new_employee_sp(5256786, 'JOVANA', 'ZAJAREDIC', '1980-07-26', 654300123, 5500.00, 3700.00, 'MANAGER', '2000-09-24', 5256543);
END;

SELECT * FROM EMPLOYEES;

program to add new employee

CREATE OR REPLACE PROCEDURE 
customer_fin_sp(fin_id IN customersfinancial.financialid%type,
c_id IN customersfinancial.customerid%type,
bk_name IN customersfinancial.bankname%type,
b_code IN customersfinancial.branchcode%type,
ac_type IN customersfinancial.accounttype%type,
ac_number IN customersfinancial.account#%type)
IS
BEGIN
  INSERT INTO CUSTOMERSFINANCIAL (FINANCIALID, CUSTOMERID, BANKNAME, BRANCHCODE, ACCOUNTTYPE, ACCOUNT#)
  VALUES(fin_id, c_id, bk_name, b_code, ac_type, ac_number );
  COMMIT;
END;

SET SERVEROUTPUT ON
BEGIN
customer_fin_sp(10010010010017, 788000000555, 'CIBC', 'C789', 'C', 999565565000000000000786);
END;

SELECT * FROM CUSTOMERSFINANCIAL;	

program to add new Customer Financial Info

CREATE OR REPLACE PROCEDURE
customer_fin_update_sp(fin_id IN customersfinancial.financialid%type,
c_id IN customersfinancial.customerid%type,
bk_name IN customersfinancial.bankname%type,
b_code IN customersfinancial.branchcode%type,
ac_type IN customersfinancial.accounttype%type,
ac_number IN customersfinancial.account#%type)
AS
BEGIN
UPDATE CUSTOMERSFINANCIAL
SET customerid = c_id, bankname = bk_name, branchcode = b_code, accounttype = ac_type, account# = ac_number
WHERE financialid = fin_id;
COMMIT;
END;

SET SERVEROUTPUT ON
BEGIN
customer_fin_update_sp(10010010010017, 788000000555, 'CIBC', 'C789', 'C', 999565565000000000000786);
END;

SELECT * FROM CUSTOMERSFINANCIAL;

Before Update Before UpdateAfter Update
CREATE OR REPLACE PROCEDURE
update_employeecontact_sp(con_id IN employeescontact.contactid%type,
employee_id IN employeescontact.employeeid%type,
unit_number IN employeescontact.unit#%type,
street_number IN employeescontact.street#%type,
street_name IN employeescontact.streetname%type,
city_name IN employeescontact.city%type,
province_name IN employeescontact.province%type,
country_name IN employeescontact.country%type,
postal_code IN employeescontact.postalcode%type,
phone_number IN employeescontact.phone#%type,
alter_phone IN employeescontact.alternativephone#%type,
email_addr IN employeescontact.email%type)
AS
BEGIN
UPDATE EMPLOYEESCONTACT
SET employeeid = employee_id, unit# = unit_number, street# = street_number, 
streetname = street_name, city = city_name,  province = province_name, 
country = country_name, postalcode = postal_code, phone# = phone_number, 
alternativephone# = alter_phone, email = email_addr
WHERE contactid = con_id;
COMMIT;
END;

SET SERVEROUTPUT ON
BEGIN
update_employeecontact_sp(20001000100001, 5259876, 20444, 1150, 'KING ST', 'TORONTO', 'ONTARIO', 'CANADA', 'H7B 9Z9', 4164521786, 4164524321, 'SFISHER@GMAIL.COM'); END;


SELECT * FROM EMPLOYEESCONTACT;

Update Employee Contact Info

SHARE

Facebook
Google+
Twitter
LinkedIn

LEAVE YOUR COMMENTS

ALSO READ

TAGS

http://techhowdy.com/wp-content/uploads/2018/05/Oracle-Database-Tables-for-PLSQL-Queries-Practice-1024x522.pnghttp://techhowdy.com/wp-content/uploads/2018/05/Oracle-Database-Tables-for-PLSQL-Queries-Practice-150x150.pngDemonDatabase Programmingazure sql database create table example with sample Data,create a database table using sql and php with sample Data,create a table in my sql database with sample Data,create a table in sql database using jdbc api with sample Data,create azure sql database table with sample Data,create database and table in sql server 2008 with sample Data,create database and table using ms sql server 2008 with sample Data,create database table in sql server 2008 with sample Data,create database table in sql server 2012 with sample Data,create database table in sql with sample Data,create database table sql server 2008 with sample Data,create database table sql server with sample Data,create html table from sql database with sample Data,create login table in sql database with sample Data,create new database table sql with sample Data,create pivot table from sql database with sample Data,create sql database and table with sample Data,create sql database table c# with sample Data,create sql database table from excel with sample Data,create sql database table with sample Data,create table from another database sql server with sample Data,create table in azure sql database with sample Data,create table in existing database sql with sample Data,create table in sql database using jdbc api with sample Data,create table into database sql server with sample Data,create table permission denied in database sql server 2008 r2 with sample Data,create table permission denied in database sql server 2012 with sample Data,create table permission denied in database sql server 2014 with sample Data,create table permission denied in database sql server with sample Data,create table template sql azure database with sample Data,create table template windows azure sql database with sample Data,create table windows azure sql database with sample Data,dynamically create sql database and table using c#.net with sample Data,grant create table on database sql server with sample Data,how to create sql database table with sample Data,how to create sql server 2008 database table with sample Data,how to create table in sql database using vb.net with sample Data,microsoft azure sql database create table with sample Data,ms sql create database and table with sample Data,ms sql create table database with sample Data,ms sql create table in database with sample Data,oracle database sql create table with sample Data,Oracle Database Tables for PLSQL Queries Practice,Oracle Database Tables Practice,sql compact database create table with sample Data,sql create database and table script with sample Data,sql create database create table with sample Data,sql create database tables script with sample Data,sql create database with table with sample Data,sql create table collate database default with sample Data,sql create table database name with sample Data,sql create table different database with sample Data,sql create table for database with sample Data,sql create table from another database with sample Data,sql create table in another database with sample Data,sql create table in specific database with sample Data,sql create table no database selected with sample Data,sql create table on another database with sample Data,sql create table on database with sample Data,sql create table on specific database with sample Data,sql create table permission denied in database 'master' with sample Data,sql create table permission denied in database with sample Data,sql create table specific database with sample Data,sql create table specify database with sample Data,sql create table to another database with sample Data,sql create table use database with sample Data,sql create table with database name with sample Data,sql create view from another database table with sample Data,sql database create table primary key with sample Data,sql database create table query with sample Data,sql database role create table with sample Data,sql server create database and table script with sample Data,sql server database create table permission with sample Data,t sql create table permission denied in database with sample Data,t-sql create database with tables with sample Data,t-sql create table in another database with sample Data,vb.net create sql database table programmatically with sample Data,web sql database create table with sample DataIn this blog post I will be teaching you how to create Sample Oracle Database Tables for PLSQL Queries Practice. You can use this Sample Database tables with Data for practicing PLSQL Queries or even practicing SQL Queries.I created a Sample Database for an Currency Exchange Company whose main business...Latest technology news