Rem Creation Script for Oracle courses by ron tarr
rem made up of 2 parts NAME “demobld10g.sql” – Drops and then Creates SIX tables for DEMO schema
Rem needed for SQL Notes material in Version 11 onward
Rem This set is a SUBSET of tables and data in the Human Resources or HR schema
Copyright By PowCoder代写 加微信 powcoder
Rem Added reps from CMC to employees table in 20183
Rem Added other SQL from CMC covering ORDERS, PRODUCTS, ORDERLINES, CUSTOMERS
Rem Later added Rep numbers to customers
Rem This PART of script will drop all SEQUENCES and TABLES related to DEMO schema
Rem If this is the first time running, these DROPS will cause errors as no such sequence exists
Rem Used in 20212 20213 in both 211 and 311
Rem This script new for 2022-1
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 200
–SET TRIMSPOOL ON
–SET TAB OFF
SET PAGESIZE 200
SET ECHO OFF
NOTE: The first time through there will be errors for the beginning as no sequences or tables exist
To be able to drop
DROP SEQUENCE locations_seq;
DROP SEQUENCE departments_seq;
DROP SEQUENCE employees_seq;
DROP TABLE countries CASCADE CONSTRAINTS;
DROP TABLE locations CASCADE CONSTRAINTS;
DROP TABLE departments CASCADE CONSTRAINTS;
DROP TABLE employees CASCADE CONSTRAINTS;
DROP TABLE job_grades;
DROP TABLE job_history;
/* This should be the end of the errors */
Rem This PART of the script creates SIX tables, populates data, adds associated constraints
Rem and indexes for the DEMO user.
REM ********************************************************************
REM Create the COUNTRIES table to hold country information for customers
REM and company locations.
REM LOCATIONS have a foreign key to this table.
Prompt ****** Creating COUNTRIES table ….
CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, region_id NUMBER
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id)
ORGANIZATION INDEX;
REM ********************************************************************
REM Create the LOCATIONS table to hold address information for company departments.
REM DEPARTMENTS has a foreign key to this table.
Prompt ****** Creating LOCATIONS table ….
CREATE TABLE locations
( location_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city VARCHAR2(30)
CONSTRAINT loc_city_nn NOT NULL
, state_province VARCHAR2(25)
, country_id CHAR(2)
CREATE UNIQUE INDEX loc_id_pk
ON locations (location_id) ;
ALTER TABLE locations
ADD ( CONSTRAINT loc_id_pk
PRIMARY KEY (location_id)
Rem Useful for any subsequent addition of rows to locations table
Rem Starts with 3300
CREATE SEQUENCE locations_seq
START WITH 3300
INCREMENT BY 100
MAXVALUE 9900
REM ********************************************************************
REM Create the DEPARTMENTS table to hold company department information.
REM EMPLOYEES has a foreign key to this table.
Prompt ****** Creating DEPARTMENTS table ….
CREATE TABLE departments
( department_id NUMBER(4)
, department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL
, manager_id NUMBER(6)
, location_id NUMBER(4)
CREATE UNIQUE INDEX dept_id_pk
ON departments (department_id) ;
ALTER TABLE departments
ADD ( CONSTRAINT dept_id_pk
PRIMARY KEY (department_id)
Rem Useful for any subsequent addition of rows to departments table
Rem Starts with 280
CREATE SEQUENCE departments_seq
START WITH 280
INCREMENT BY 10
MAXVALUE 9990
REM ********************************************************************
REM Create the EMPLOYEES table to hold the employee personnel
REM information for the company.
REM EMPLOYEES has a self referencing foreign key to this table.
Prompt ****** Creating EMPLOYEES table ….
CREATE TABLE employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;
ALTER TABLE employees
ADD ( CONSTRAINT emp_emp_id_pk
PRIMARY KEY (employee_id)
, CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
, CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees
ALTER TABLE departments
ADD ( CONSTRAINT dept_mgr_fk
FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
Rem Useful for any subsequent addition of rows to employees table
Rem Starts with 207
CREATE SEQUENCE employees_seq
START WITH 207
INCREMENT BY 1
REM ********************************************************************
REM Create the JOB_GRADES table that will show different SALARY GRADES
REM depending on employee’s SALARY RANGE
Prompt ****** Creating JOB_GRADES table ….
CREATE TABLE job_grades (
grade CHAR(1),
lowest_sal NUMBER(8,2) NOT NULL,
highest_sal NUMBER(8,2) NOT NULL
ALTER TABLE job_grades
ADD CONSTRAINT jobgrades_grade_pk PRIMARY KEY (grade);
rem This PART of script will populate script for the DEMO account
rem NOTES There is a circular foreign key reference between
rem EMPLOYEES and DEPARTMENTS. That is why we disable
rem the FK constraints here
SET VERIFY OFF
REM ***************************insert data into the COUNTRIES table
Prompt ****** Populating COUNTRIES table ….
INSERT INTO countries VALUES
INSERT INTO countries VALUES
INSERT INTO countries VALUES
, ‘United States of America’
INSERT INTO countries VALUES
, ‘Canada’
INSERT INTO countries VALUES
INSERT INTO countries VALUES
INSERT INTO countries VALUES
, ‘Australia’
INSERT INTO countries VALUES
, ‘Zimbabwe’
INSERT INTO countries VALUES
, ‘Singapore’
INSERT INTO countries VALUES
, ‘United Kingdom’
INSERT INTO countries VALUES
, ‘France’
INSERT INTO countries VALUES
, ‘Germany’
INSERT INTO countries VALUES
, ‘Zambia’
INSERT INTO countries VALUES
INSERT INTO countries VALUES
, ‘Brazil’
INSERT INTO countries VALUES
, ‘Switzerland’
INSERT INTO countries VALUES
, ‘Netherlands’
INSERT INTO countries VALUES
, ‘Mexico’
INSERT INTO countries VALUES
, ‘Kuwait’
INSERT INTO countries VALUES
, ‘Israel’
INSERT INTO countries VALUES
, ‘Denmark’
INSERT INTO countries VALUES
, ‘HongKong’
INSERT INTO countries VALUES
, ‘Nigeria’
INSERT INTO countries VALUES
, ‘Argentina’
INSERT INTO countries VALUES
, ‘Belgium’
REM ***************************insert data into the LOCATIONS table
Prompt ****** Populating LOCATIONS table ….
INSERT INTO locations VALUES
, ‘1297 Via Cola di Rie’
INSERT INTO locations VALUES
, ‘93091 Calle della Testa’
, ‘Venice’
INSERT INTO locations VALUES
, ‘2017 Shinjuku-ku’
, ‘Tokyo Prefecture’
INSERT INTO locations VALUES
, ‘9450 Kamiya-cho’
, ‘Hiroshima’
INSERT INTO locations VALUES
, ‘2014 Jabberwocky Rd’
, ‘Southlake’
INSERT INTO locations VALUES
, ‘2011 Interiors Blvd’
, ‘south San Francisco’
, ‘California’
INSERT INTO locations VALUES
, ‘2007 Zagora St’
, ‘South Brunswick’
INSERT INTO locations VALUES
, ‘2004 Charade Rd’
, ‘Seattle’
, ‘Washington’
INSERT INTO locations VALUES
, ‘147 Spadina Ave’
, ‘M5V 2L7’
, ‘Toronto’
, ‘Ontario’
INSERT INTO locations VALUES
, ‘6092 Boxwood St’
, ‘YSW 9T2’
, ‘Whitehorse’
INSERT INTO locations VALUES
, ’40-5-12 Laogianggen’
, ‘190518’
, ‘Beijing’
INSERT INTO locations VALUES
, ‘1298 Vileparle (E)’
, ‘490231’
, ‘Bombay’
, ‘Maharashtra’
INSERT INTO locations VALUES
, ’12-98 ‘
, ‘Sydney’
, ‘ Wales’
INSERT INTO locations VALUES
, ‘540198’
, ‘Singapore’
INSERT INTO locations VALUES
, ‘8204 ‘
, ‘London’
INSERT INTO locations VALUES
, ‘ , The Oxford Science Park’
, ‘OX9 9ZB’
, ‘Oxford’
, ‘Oxford’
INSERT INTO locations VALUES
, ‘9702 ‘
, ‘09629850293’
, ‘Stretford’
, ‘Manchester’
INSERT INTO locations VALUES
, ‘Schwanthalerstr. 7031’
, ‘Munich’
, ‘Bavaria’
INSERT INTO locations VALUES
, ‘ Caneca 1360 ‘
, ‘01307-002′
INSERT INTO locations VALUES
, ’20 Rue des Corps-Saints’
, ‘Geneva’
, ‘Geneve’
INSERT INTO locations VALUES
, ‘Murtenstrasse 921’
INSERT INTO locations VALUES
, ‘3029SK’
, ‘Utrecht’
, ‘Utrecht’
INSERT INTO locations VALUES
, ‘ 9991’
, ‘Mexico City’
, ‘Distrito Federal,’
REM ****************************insert data into the DEPARTMENTS table
Prompt ****** Populating DEPARTMENTS table ….
REM disable integrity constraint to EMPLOYEES to load data
ALTER TABLE departments
DISABLE CONSTRAINT dept_mgr_fk;
INSERT INTO departments VALUES
INSERT INTO departments VALUES
, ‘Marketing’
INSERT INTO departments VALUES
, ‘Shipping’
INSERT INTO departments VALUES
INSERT INTO departments VALUES
INSERT INTO departments VALUES
, ‘Executive’
INSERT INTO departments VALUES
, ‘Accounting’
INSERT INTO departments VALUES
, ‘Contracting’
INSERT INTO departments VALUES
, ‘Actuarial’
REM ***************************insert data into the EMPLOYEES table
Prompt ****** Populating EMPLOYEES table ….
INSERT INTO employees VALUES
, ‘Steven’
, ‘515.123.4567’
, TO_DATE(’17-JUN-1987′, ‘dd-MON-yyyy’)
INSERT INTO employees VALUES
, ‘Kochhar’
, ‘NKOCHHAR’
, ‘515.123.4568’
, TO_DATE(’21-SEP-1989′, ‘dd-MON-yyyy’)
INSERT INTO employees VALUES
, ‘LDEHAAN’
, ‘515.123.4569’
, TO_DATE(’13-JAN-1993′, ‘dd-MON-yyyy’)
INSERT INTO employees VALUES
, ‘Alexander’
, ‘Hunold’
, ‘AHUNOLD’
, ‘590.423.4567’
, TO_DATE(’03-JAN-1990′, ‘dd-MON-yyyy’)
, ‘IT_PROG’
INSERT INTO employees VALUES
, ‘BERNST’
, ‘590.423.4568’
, TO_DATE(’21-MAY-1991′, ‘dd-MON-yyyy’)
, ‘IT_PROG’
INSERT INTO employees VALUES
, ‘Lorentz’
, ‘DLORENTZ’
, ‘590.423.5567’
, TO_DATE(’07-FEB-1999′, ‘dd-MON-yyyy’)
, ‘IT_PROG’
INSERT INTO employees VALUES
, ‘Mourgos’
, ‘KMOURGOS’
, ‘650.123.5234’
, TO_DATE(’16-NOV-1999′, ‘dd-MON-yyyy’)
, ‘ST_MAN’
INSERT INTO employees VALUES
, ‘Trenna’
, ‘650.121.8009’
, TO_DATE(’17-OCT-1995′, ‘dd-MON-yyyy’)
, ‘ST_CLERK’
INSERT INTO employees VALUES
, ‘Curtis’
, ‘Davies’
, ‘CDAVIES’
, ‘650.121.2994’
, TO_DATE(’29-JAN-1997′, ‘dd-MON-yyyy’)
, ‘ST_CLERK’
INSERT INTO employees VALUES
, ‘Randall’
, ‘RMATOS’
, ‘650.121.2874’
, TO_DATE(’15-MAR-1998′, ‘dd-MON-yyyy’)
, ‘ST_CLERK’
INSERT INTO employees VALUES
, ‘Vargas’
, ‘PVARGAS’
, ‘650.121.2004’
, TO_DATE(’09-JUL-1998′, ‘dd-MON-yyyy’)
, ‘ST_CLERK’
INSERT INTO employees VALUES
, ‘Zlotkey’
, ‘EZLOTKEY’
, ‘011.44.1344.429018’
, TO_DATE(’29-JAN-2000′, ‘dd-MON-yyyy’)
, ‘SA_MAN’
INSERT INTO employees VALUES
, ‘011.44.1644.429267’
, TO_DATE(’11-MAY-1996′, ‘dd-MON-yyyy’)
, ‘SA_REP’
INSERT INTO employees VALUES
, ‘Jonathon’
, ‘Vargas’
, ‘JVARGAS’
, ‘011.44.1644.429265’
, TO_DATE(’24-MAR-1998′, ‘dd-MON-yyyy’)
, ‘SA_REP’
INSERT INTO employees VALUES
, ‘Kimberely’
, ‘Grants’
, ‘KGRANTS’
, ‘011.44.1644.429263’
, TO_DATE(’24-MAY-1999′, ‘dd-MON-yyyy’)
, ‘SA_REP’
INSERT INTO employees VALUES
, ‘Spence’
, ‘de Man’
, ‘SDEMAN’
, ‘011.44.1644.420172’
, TO_DATE(’08-MAY-2017′, ‘dd-MON-yyyy’)
, ‘SA_REP’
INSERT INTO employees VALUES
, ‘Jennifer’
, ‘Whalen’
, ‘JWHALEN’
, ‘515.123.4444’
, TO_DATE(’17-SEP-1987′, ‘dd-MON-yyyy’)
, ‘AD_ASST’
INSERT INTO employees VALUES
, ‘Michael’
, ‘Hartstein’
, ‘MHARTSTE’
, ‘515.123.5555’
, TO_DATE(’17-FEB-1996′, ‘dd-MON-yyyy’)
, ‘MK_MAN’
INSERT INTO employees VALUES
, ‘603.123.6666’
, TO_DATE(’17-AUG-1997′, ‘dd-MON-yyyy’)
, ‘MK_REP’
INSERT INTO employees VALUES
, ‘Shelley’
, ‘Higgins’
, ‘SHIGGINS’
, ‘515.123.8080’
, TO_DATE(’07-JUN-1994′, ‘dd-MON-yyyy’)
, ‘AC_MGR’
INSERT INTO employees VALUES
, ‘William’
, ‘WGIETZ’
, ‘515.123.8181’
, TO_DATE(’07-JUN-1994′, ‘dd-MON-yyyy’)
, ‘AC_ACCOUNT’
REM ********************************
REM ********************************
REM reps from CMC added
REM *******************************
REM add these sales reps 20183
REM ***************************************
INSERT INTO employees VALUES
, ‘Flerjian’
,’BFLERJIAN’
, ‘414.111.4567’
, TO_DATE(’11-JAN-2011′, ‘dd-MON-yyyy’)
INSERT INTO employees VALUES
,’GGROLIN’
, ‘429.311.4567’
, TO_DATE(’23-MAR-2013′, ‘dd-MON-yyyy’)
INSERT INTO EMPLOYEES VALUES
,’Smertal’
,’BSMERTAL’
, ‘404.311.4567’
, TO_DATE(’24-MAR-2014′, ‘dd-MON-yyyy’)
INSERT INTO EMPLOYEES VALUES
,’Mustain’
,’DMUSTAIN’
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com