USA: +1(669)289-8683 
Sign in
Chapter 8 Practice(all figures noted can be seen at the end of Chapter 8 in our textbook)
Create the ConstructCo database by running the SQL commands found in the attached
file.
What should you submit for this exercise?
The question number, the SQL command you used and the output you get
after running the SQL command and/or output verifying that you ran the
SQL command.
The Ch08_ConstructCo database stores data for a consulting company that tracks all
charges to projects. The charges are based on the hours each employee works on each
project. The structure and contents of the Ch08_ConstructCo database are shown in
Figure P8.1.
Figure P8.1The CH08_CONSTRUCTCO Database
Note that the ASSIGNMENT table in Figure P8.1 stores the JOB_CHG_HOUR values as an
attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the data. The
JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_HOUR change
will be reflected in the ASSIGNMENT table. Naturally, the employee primary job
assignment might also change, so the ASSIGN_JOB is also stored. Because those attributes
are required to maintain the historical accuracy of the data, they are not redundant.
Given the structure and contents of the Ch08_ConstructCo database shown in Figure P8.1,
use SQL commands to answer Problems 1-15
1. Write the SQL code that will create only the table structure for a table named EMP_1. This table will
be a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following
table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to
enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and
EMP_FNAME.
Table P8.1
Constructco Database Structure
ATTRIBUTE (FIELD) NAME
DATA DECLARATION
EMP_NUM
CHAR(3)
EMP_LNAME
VARCHAR(15)
EMP_FNAME
VARCHAR(15)
EMP_INITIAL
CHAR(1)
EMP_HIREDATE
DATE
JOB_CODE
CHAR(3)
2. Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the
table shown in Figure P8.2. Each row should be inserted individually, without using a subquery.
Insert the rows in the order that they are listed in the figure.
Figure P8.2The Contents of the EMP_1 Table
3. Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the
EMPLOYEE table into the EMP_1 table. Remember, your subquery should only retrieve the columns
needed for the EMP_1 table and only the employees shown in the figure.
4. Write the SQL code that will save the changes made to the EMP_1 table (if supported by your DBMS).
5. Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM)
is 107.
6. Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and
whose job code is 500. (Hint: Use logical operators to include all of the information given in this problem.
Remember, if you are using MySQL, you will have to first disable “safe mode.”)
7. Write the SQL code to create a copy of EMP_1, including all of its data, and naming the copy EMP_2.
8. Using the EMP_2 table, write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to
EMP_2. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute
characteristics are:
Note: If your SQL implementation requires it, you may use DECIMAL(4,2) or NUMERIC(4,2) rather than
NUMBER(4,2).
9. Using the EMP_2 table, write the SQL code to change the EMP_PCT value to 3.85 for the person whose
employee number (EMP_NUM) is 103.
10. Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 5.00 for the
people with employee numbers 101, 105, and 107.
11. Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 10.00 for all
employees who do not currently have a value for EMP_PCT.
12. Using the EMP_2 table, write the SQL command to add .15 to the EMP_PCT of the employee whose
name is Maria D. Alonzo. (Use the employee name in your command to determine the correct employee.)
13. Using a single command sequence with the EMP_2 table, write the SQL code that will change the
project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500.
14. Using a single command sequence with the EMP_2 table, write the SQL code that will change the
project number (PROJ_NUM) to 25 for all employees whose job classification (JOB_CODE) is 502 or
higher.
15. Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before
January 1, 1994, and whose job code is at least 501. When you finish Problems 7-15, the EMP_2 table will
contain the data shown in Figure P8.15.
Figure P8.15The EMP_2 Table Contents
Chapter 7 SQL Practice exercise
(all figures noted can be seen at the end of Chapter 7 in our textbook)
Create the ConstructCo database by running the SQL commands found in the attached
file.
What should you submit for this exercise?
The question number, the SQL command you used and the output you get
after running the SQL command
The Ch07_ConstructCo database stores data for a consulting company that tracks all charges
to projects. The charges are based on the hours each employee works on each project. The
structure and contents of the Ch07_ConstructCo database are shown in Figure P7.1.
Figure P7.1The CH07_CONSTRUCTCO Database
Note that the ASSIGNMENT table in Figure P7.1 stores the JOB_CHG_HOUR values as an
attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the data. The
JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_HOUR change
will be reflected in the ASSIGNMENT table. Naturally, the employee primary job assignment
might also change, so the ASSIGN_JOB is also stored. Because those attributes are required to
maintain the historical accuracy of the data, they are not redundant.
Given the structure and contents of the Ch07_ConstructCo database shown in Figure P7.1, use
SQL commands to answer the following problems.
1. Write the SQL code required to list the employee number, last name, first name, and middle initial of all
employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield
should be included in the listing. Sort the results by employee number. Assume case sensitivity.
2. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo database, write the SQL code
that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display
the attributes shown in the results presented in Figure P7.2, sorted by project value.
Figure P7.2The Query Results for Problem 2
3. Write the SQL code that will produce the same information that was shown in Problem 2, but sorted by the
employee’s last name.
4. Write the SQL code that will list only the distinct project numbers in the ASSIGNMENT table, sorted by
project number.
5. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query
should retrieve the assignment number, employee number, project number, the stored assignment charge
(ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR
by ASSIGN_HOURS). Sort the results by the assignment number.
6. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours
worked for each employee and the total charges stemming from those hours worked, sorted by employee
number. The results of running that query are shown in Figure P7.6.
Figure P7.6Total Hours and Charges by Employee
7. Write a query to produce the total number of hours and charges for each of the projects represented in the
ASSIGNMENT table, sorted by project number. The output is shown in Figure P7.7.
Figure P7.7Total Hours and Charges by Project
8. Write the SQL code to generate the total hours worked and the total charges made by all employees. The
results are shown in Figure P7.8.
Figure P7.8Total Hours and Charges, All Employees
The structure and contents of the Ch07_SaleCo database are shown in Figure P7.9. Use this
database to answer the following problems.
Figure P7.9The CH07_SALECO Database
9.
Write a query to count the number of invoices.
10.
Write a query to count the number of customers with a balance of more than $500.
11.
Generate a listing of all purchases made by the customers, using the output shown in Figure P7.11 as
your guide. Sort the results by customer code, invoice number, and product description.
Figure P7.11List of Customer Purchases
12.
Using the output shown in Figure P7.12 as your guide, generate a list of customer purchases, including
the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying
LINE_UNITS by LINE_PRICE. Sort the output by customer code, invoice number, and product description. Be
certain to use the column aliases as shown in the figure.
Figure P7.12Summary of Customer Purchases with Subtotals
13.
Write a query to display the customer code, balance, and total purchases for each customer. Total
purchase is calculated by summing the line subtotals (as calculated in Problem 12) for each customer. Sort the
results by customer code, and use aliases as shown in Figure P7.13.
Figure P7.13Customer Purchase Summary
14.
Modify the query in Problem 13 to include the number of individual product purchases made by each
customer. (In other words, if the customer’s invoice is based on three products, one per LINE_NUMBER, you
count three product purchases. Note that in the original invoice data, customer 10011 generated three invoices,
which contained a total of six lines, each representing a product purchase.) Your output values must match
those shown in Figure P7.14, sorted by customer code.
Figure P7.14Customer Total Purchase Amounts and Number of Purchases
15.
Use a query to compute the total of all purchases, the number of purchases, and the average purchase
amount made by each customer. Your output values must match those shown in Figure P7.15. Sort the results
by customer code.
Figure P7.15Average Purchase Amount by Customer
16.
Create a query to produce the total purchase per invoice, generating the results shown in Figure P7.16,
sorted by invoice number. The invoice total is the sum of the product purchases in the LINE that corresponds to
the INVOICE.
Figure P7.16Invoice Totals
17.
Use a query to show the invoices and invoice totals in Figure P7.17. Sort the results by customer code
and then by invoice number.
Figure P7.17Invoice Totals by Customer
18.
Write a query to produce the number of invoices and the total purchase amounts by customer, using the
output shown in Figure P7.18 as your guide. Note the results are sorted by customer code. (Compare this
summary to the results shown in Problem 17.)
Figure P7.18Number of Invoices and Total Purchase Amounts by Customer
19.
Write a query to generate the total number of invoices, the invoice total for all of the invoices, the
smallest of the customer purchase amounts, the largest of the customer purchase amounts, and the average of all
the customer purchase amounts. Your output must match Figure P7.19.
Figure P7.19Number of Invoices, Invoice Totals, Minimum, Maximum, and Average Sales
20.
List the balances of customers who have made purchases during the current invoice cycle—that is, for
the customers who appear in the INVOICE table. The results of this query are shown in Figure P7.20, sorted by
customer code.
Figure P7.20Balances for Customers Who Made Purchases
21.
Provide a summary of customer balance characteristics for customers who made purchases. Include the
minimum balance, maximum balance, and average balance, as shown in Figure P7.21.
Figure P7.21Balance Summary for Customers Who Made Purchases
/* Database Systems, Coronel/Morris */
/* Type of SQL : MySQL */
/* — */
CREATE TABLE JOB (
JOB_CODE VARCHAR(3),
JOB_DESCRIPTION VARCHAR(25),
JOB_CHG_HOUR DECIMAL(8,2),
JOB_LAST_UPDATE DATETIME,
PRIMARY KEY(JOB_CODE)
);
INSERT INTO JOB VALUES(‘500′,’Programmer’, ‘35.75’,’2017-11-20′);
INSERT INTO JOB VALUES(‘501′,’Systems Analyst’, ‘96.75’,’2017-11-20′);
INSERT INTO JOB VALUES(‘502′,’Database Designer’, ‘125’, ‘2018-3-24’);
INSERT INTO JOB VALUES(‘503′,’Electrical Engineer’, ‘84.5’, ‘2017-11-20’);
INSERT INTO JOB VALUES(‘504′,’Mechanical Engineer’, ‘67.9’, ‘2017-11-20’);
INSERT INTO JOB VALUES(‘505′,’Civil Engineer’, ‘55.78’,’2017-11-20′);
INSERT INTO JOB VALUES(‘506′,’Clerical Support’, ‘26.87’,’2017-11-20′);
INSERT INTO JOB VALUES(‘507′,’DSS Analyst’, ‘45.95’,’2017-11-20′);
INSERT INTO JOB VALUES(‘508′,’Applications Designer’,’48.1′, ‘2018-3-24’);
INSERT INTO JOB VALUES(‘509′,’Bio Technician’, ‘34.55’,’2017-11-20′);
INSERT INTO JOB VALUES(‘510′,’General Support’, ‘18.36’,’2017-11-20′);
/* — */
CREATE TABLE EMPLOYEE (
EMP_NUM VARCHAR(3),
EMP_LNAME VARCHAR(15),
EMP_FNAME VARCHAR(15),
EMP_INITIAL VARCHAR(1),
EMP_HIREDATE DATETIME,
JOB_CODE VARCHAR(3),
EMP_YEARS INT(3),
PRIMARY KEY(EMP_NUM),
FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)
);
INSERT INTO EMPLOYEE VALUES(‘101′,’News’,’John’,’G’,’2000-11-8′,’502′,’4′);
INSERT INTO EMPLOYEE VALUES(‘102′,’Senior’,’David’,’H’,’1989-7-12′,’501′,’15’);
INSERT INTO EMPLOYEE VALUES(‘103′,’Arbough’,’June’,’E’,’1996-12-1′,’503′,’8′);
INSERT INTO EMPLOYEE VALUES(‘104′,’Ramoras’,’Anne’,’K’,’1987-11-15′,’501′,’17’);
INSERT INTO EMPLOYEE VALUES(‘105′,’Johnson’,’Alice’,’K’,’1993-2-1′,’502′,’12’);
INSERT INTO EMPLOYEE VALUES(‘106′,’Smithfield’,’William’,NULL,’2004-6-22′,’500′,’0′);
INSERT INTO EMPLOYEE VALUES(‘107′,’Alonzo’,’Maria’,’D’,’1993-10-10′,’500′,’11’);
INSERT INTO EMPLOYEE VALUES(‘108′,’Washington’,’Ralph’,’B’,’1991-8-22′,’501′,’13’);
INSERT INTO EMPLOYEE VALUES(‘109′,’Smith’,’Larry’,’W’,’1997-7-18′,’501′,’7′);
INSERT INTO EMPLOYEE VALUES(‘110′,’Olenko’,’Gerald’,’A’,’1995-12-11′,’505′,’9′);
INSERT INTO EMPLOYEE VALUES(‘111′,’Wabash’,’Geoff’,’B’,’1991-4-4′,’506′,’14’);
INSERT INTO EMPLOYEE VALUES(‘112′,’Smithson’,’Darlene’,’M’,’1994-10-23′,’507′,’10’);
INSERT INTO EMPLOYEE VALUES(‘113′,’Joenbrood’,’Delbert’,’K’,’1996-11-15′,’508′,’8′);
INSERT INTO EMPLOYEE VALUES(‘114′,’Jones’,’Annelise’,NULL,’1993-8-20′,’508′,’11’);
INSERT INTO EMPLOYEE VALUES(‘115′,’Bawangi’,’Travis’,’B’,’1992-1-25′,’501′,’13’);
INSERT INTO EMPLOYEE VALUES(‘116′,’Pratt’,’Gerald’,’L’,’1997-3-5′,’510′,’8′);
INSERT INTO EMPLOYEE VALUES(‘117′,’Williamson’,’Angie’,’H’,’1996-6-19′,’509′,’8′);
INSERT INTO EMPLOYEE VALUES(‘118′,’Frommer’,’James’,’J’,’2005-1-4′,’510′,’0′);
/* — */
CREATE TABLE PROJECT (
PROJ_NUM VARCHAR(3),
PROJ_NAME VARCHAR(25),
PROJ_VALUE DECIMAL(10,2),
PROJ_BALANCE DECIMAL(10,2),
EMP_NUM VARCHAR(3),
PRIMARY KEY(PROJ_NUM),
FOREIGN KEY(EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
);
INSERT INTO PROJECT VALUES(’15’,’Evergreen’,’1453500′,’1002350′,’103′);
INSERT INTO PROJECT VALUES(’18’,’Amber Wave’,’3500500′,’2110346′,’108′);
INSERT INTO PROJECT VALUES(’22’,’Rolling Tide’,’805000′,’500345.2′,’102′);
INSERT INTO PROJECT VALUES(’25’,’Starflight’,’2650500′,’2309880′,’107′);
/* — */
CREATE TABLE ASSIGNMENT (
ASSIGN_NUM INT(5),
ASSIGN_DATE DATETIME,
PROJ_NUM VARCHAR(3),
EMP_NUM VARCHAR(3),
ASSIGN_JOB VARCHAR(3),
ASSIGN_CHG_HR DECIMAL(8,2),
ASSIGN_HOURS DECIMAL(8,2),
ASSIGN_CHARGE DECIMAL(8,2),
PRIMARY KEY (ASSIGN_NUM),
FOREIGN KEY (PROJ_NUM) REFERENCES PROJECT(PROJ_NUM),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
);
INSERT INTO ASSIGNMENT VALUES(‘1001′,’2018-3-22′,’18’,’103′,’503′,’84.5′,’3.5′,’295.75′);
INSERT INTO ASSIGNMENT VALUES(‘1002′,’2018-3-22′,’22’,’117′,’509′,’34.55′,’4.2′,’145.11′);
INSERT INTO ASSIGNMENT VALUES(‘1003′,’2018-3-22′,’18’,’117′,’509′,’34.55′,’2′,’69.10′);
INSERT INTO ASSIGNMENT VALUES(‘1004′,’2018-3-22′,’18’,’103′,’503′,’84.5′,’5.9′,’498.55′);
INSERT INTO ASSIGNMENT VALUES(‘1005′,’2018-3-22′,’25’,’108′,’501′,’96.75′,’2.2′,’212.85′);
INSERT INTO ASSIGNMENT VALUES(‘1006′,’2018-3-22′,’22’,’104′,’501′,’96.75′,’4.2′,’406.35′);
INSERT INTO ASSIGNMENT VALUES(‘1007′,’2018-3-22′,’25’,’113′,’508′,’50.75′,’3.8′,’192.85′);
INSERT INTO ASSIGNMENT VALUES(‘1008′,’2018-3-22′,’18’,’103′,’503′,’84.5′,’0.9′,’76.05′);
INSERT INTO ASSIGNMENT VALUES(‘1009′,’2018-3-23′,’15’,’115′,’501′,’96.75′,’5.6′,’541.80′);
INSERT INTO ASSIGNMENT VALUES(‘1010′,’2018-3-23′,’15’,’117′,’509′,’34.55′,’2.4′,’82.92′);
INSERT INTO ASSIGNMENT VALUES(‘1011′,’2018-3-23′,’25’,’105′,’502′,’105′,’4.3′,’451.5′);
INSERT INTO ASSIGNMENT VALUES(‘1012′,’2018-3-23′,’18’,’108′,’501′,’96.75′,’3.4′,’328.95′);
INSERT INTO ASSIGNMENT VALUES(‘1013′,’2018-3-23′,’25’,’115′,’501′,’96.75′,’2′,’193.5′);
INSERT INTO ASSIGNMENT VALUES(‘1014′,’2018-3-23′,’22’,’104′,’501′,’96.75′,’2.8′,’270.9′);
INSERT INTO ASSIGNMENT VALUES(‘1015′,’2018-3-23′,’15’,’103′,’503′,’84.5′,’6.1′,’515.45′);
INSERT INTO ASSIGNMENT VALUES(‘1016′,’2018-3-23′,’22’,’105′,’502′,’105′,’4.7′,’493.5′);
INSERT INTO ASSIGNMENT VALUES(‘1017′,’2018-3-23′,’18’,’117′,’509′,’34.55′,’3.8′,’131.29′);
INSERT INTO ASSIGNMENT VALUES(‘1018′,’2018-3-23′,’25’,’117′,’509′,’34.55′,’2.2′,’76.01′);
INSERT INTO ASSIGNMENT VALUES(‘1019′,’2018-3-24′,’25’,’104′,’501′,’110.5′,’4.9′,’541.45′);
INSERT INTO ASSIGNMENT VALUES(‘1020′,’2018-3-24′,’15’,’101′,’502′,’125′,’3.1′,’387.5′);
INSERT INTO ASSIGNMENT VALUES(‘1021′,’2018-3-24′,’22’,’108′,’501′,’110.5′,’2.7′,’298.35′);
INSERT INTO ASSIGNMENT VALUES(‘1022′,’2018-3-24′,’22’,’115′,’501′,’110.5′,’4.9′,’541.45′);
INSERT INTO ASSIGNMENT VALUES(‘1023′,’2018-3-24′,’22’,’105′,’502′,’125′,’3.5′,’437.5′);
INSERT INTO ASSIGNMENT VALUES(‘1024′,’2018-3-24′,’15’,’103′,’503′,’84.5′,’3.3′,’278.85′);
INSERT INTO ASSIGNMENT VALUES(‘1025′,’2018-3-24′,’18’,’117′,’509′,’34.55′,’4.2′,’145.11′);
/* Introduction to SQL */
/* Script file for MySQL DBMS */
/* This script file creates the following tables: */
/* VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE */
/* EMPLOYEE, EMP */
/* and loads the default data rows */
BEGIN;
DROP TABLE IF EXISTS LINE ;
DROP TABLE IF EXISTS INVOICE;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS PRODUCT;
DROP TABLE IF EXISTS VENDOR;
DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS EMP;
CREATE TABLE VENDOR (
V_CODE INTEGER,
V_NAME VARCHAR(35) NOT NULL,
V_CONTACT VARCHAR(15) NOT NULL,
V_AREACODE CHAR(3) NOT NULL,
V_PHONE CHAR(8) NOT NULL,
V_STATE CHAR(2) NOT NULL,
V_ORDER CHAR(1) NOT NULL,
PRIMARY KEY (V_CODE));
CREATE TABLE PRODUCT (
P_CODE VARCHAR(10) PRIMARY KEY,
P_DESCRIPT VARCHAR(35) NOT NULL,
P_INDATE DATETIME NOT NULL,
P_QOH INTEGER NOT NULL,
P_MIN INTEGER NOT NULL,
P_PRICE NUMERIC(8,2) NOT NULL,
P_DISCOUNT NUMERIC(4,2) NOT NULL,
V_CODE INTEGER,
CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE));
CREATE TABLE CUSTOMER (
CUS_CODE INTEGER PRIMARY KEY,
CUS_LNAME VARCHAR(15) NOT NULL,
CUS_FNAME VARCHAR(15) NOT NULL,
CUS_INITIAL CHAR(1),
CUS_AREACODE CHAR(3),
CUS_PHONE CHAR(8) NOT NULL,
CUS_BALANCE NUMERIC(9,2) DEFAULT 0.00,
CONSTRAINT CUS_UI1 UNIQUE(CUS_LNAME,CUS_FNAME, CUS_PHONE));
CREATE TABLE INVOICE (
INV_NUMBER INTEGER PRIMARY KEY,
CUS_CODE INTEGER NOT NULL,
INV_DATE DATETIME NOT NULL,
CONSTRAINT INVOICE_CUS_CODE_FK FOREIGN KEY (CUS_CODE) REFERENCES CUSTOMER(CUS_CODE));
CREATE TABLE LINE (
INV_NUMBER INTEGER NOT NULL,
LINE_NUMBER NUMERIC(2,0) NOT NULL,
P_CODE VARCHAR(10) NOT NULL,
LINE_UNITS NUMERIC(9,2) DEFAULT 0.00 NOT NULL,
LINE_PRICE NUMERIC(9,2) DEFAULT 0.00 NOT NULL,
PRIMARY KEY (INV_NUMBER,LINE_NUMBER),
FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE (INV_NUMBER) ON DELETE CASCADE,
FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE),
CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER, P_CODE));
CREATE TABLE EMPLOYEE (
EMP_NUM INTEGER PRIMARY KEY,
EMP_TITLE CHAR(10),
EMP_LNAME VARCHAR(15) NOT NULL,
EMP_FNAME VARCHAR(15) NOT NULL,
EMP_INITIAL CHAR(1),
EMP_DOB DATETIME,
EMP_HIRE_DATE DATETIME,
EMP_YEARS INTEGER,
EMP_AREACODE CHAR(3),
EMP_PHONE CHAR(8));
CREATE TABLE EMP (
EMP_NUM INTEGER PRIMARY KEY,
EMP_TITLE CHAR(10),
EMP_LNAME VARCHAR(15) NOT NULL,
EMP_FNAME VARCHAR(15) NOT NULL,
EMP_INITIAL CHAR(1),
EMP_DOB DATETIME,
EMP_HIRE_DATE DATETIME,
EMP_AREACODE CHAR(3),
EMP_PHONE CHAR(8),
EMP_MGR INTEGER);
/* Loading data rows */
/* VENDOR rows */
INSERT INTO VENDOR VALUES(21225,’Bryson, Inc.’ ,’Smithson’,’615′,’223-3234′,’TN’,’Y’);
INSERT INTO VENDOR VALUES(21226,’SuperLoo, Inc.’ ,’Flushing’,’904′,’215-8995′,’FL’,’N’);
INSERT INTO VENDOR VALUES(21231,’D&E Supply’ ,’Singh’ ,’615′,’228-3245′,’TN’,’Y’);
INSERT INTO VENDOR VALUES(21344,’Gomez Bros.’ ,’Ortega’ ,’615′,’889-2546′,’KY’,’N’);
INSERT INTO VENDOR VALUES(22567,’Dome Supply’ ,’Smith’ ,’901′,’678-1419′,’GA’,’N’);
INSERT INTO VENDOR VALUES(23119,’Randsets Ltd.’ ,’Anderson’,’901′,’678-3998′,’GA’,’Y’);
INSERT INTO VENDOR VALUES(24004,’Brackman Bros.’ ,’Browning’,’615′,’228-1410′,’TN’,’N’);
INSERT INTO VENDOR VALUES(24288,’ORDVA, Inc.’ ,’Hakford’ ,’615′,’898-1234′,’TN’,’Y’);
INSERT INTO VENDOR VALUES(25443,’B&K, Inc.’ ,’Smith’ ,’904′,’227-0093′,’FL’,’N’);
INSERT INTO VENDOR VALUES(25501,’Damal Supplies’ ,’Smythe’ ,’615′,’890-3529′,’TN’,’N’);
INSERT INTO VENDOR VALUES(25595,’Rubicon Systems’ ,’Orton’ ,’904′,’456-0092′,’FL’,’Y’);
/* PRODUCT rows */
INSERT INTO PRODUCT VALUES(’11QER/31′,’Power painter, 15 psi., 3-nozzle’ ,’2017-11-03′, 8, 5,109.99,0.00,25595);
INSERT INTO PRODUCT VALUES(’13-Q2/P2′,’7.25-in. pwr. saw blade’ ,’2017-12-13′, 32, 15, 14.99,0.05,21344);
INSERT INTO PRODUCT VALUES(’14-Q1/L3′,’9.00-in. pwr. saw blade’ ,’2017-11-13′, 18, 12, 17.49,0.00,21344);
INSERT INTO PRODUCT VALUES(‘1546-QQ2′,’Hrd. cloth, 1/4-in., 2×50′ ,’2018-01-15’, 15, 8, 39.95,0.00,23119);
INSERT INTO PRODUCT VALUES(‘1558-QW1′,’Hrd. cloth, 1/2-in., 3×50′ ,’2018-01-15’, 23, 5, 43.99,0.00,23119);
INSERT INTO PRODUCT VALUES(‘2232/QTY’,’B&D jigsaw, 12-in. blade’ ,’2017-12-30′, 8, 5,109.92,0.05,24288);
INSERT INTO PRODUCT VALUES(‘2232/QWE’,’B&D jigsaw, 8-in. blade’ ,’2017-12-24′, 6, 5, 99.87,0.05,24288);
INSERT INTO PRODUCT VALUES(‘2238/QPD’,’B&D cordless drill, 1/2-in.’ ,’2018-01-20′, 12, 5, 38.95,0.05,25595);
INSERT INTO PRODUCT VALUES(‘23109-HB’,’Claw hammer’ ,’2018-01-20′, 23, 10, 9.95,0.10,21225);
INSERT INTO PRODUCT VALUES(‘23114-AA’,’Sledge hammer, 12 lb.’ ,’2018-01-02′, 8, 5, 14.40,0.05,NULL);
INSERT INTO PRODUCT VALUES(‘54778-2T’,’Rat-tail file, 1/8-in. fine’ ,’2017-12-15′, 43, 20, 4.99,0.00,21344);
INSERT INTO PRODUCT VALUES(’89-WRE-Q’,’Hicut chain saw, 16 in.’ ,’2018-02-07′, 11, 5,256.99,0.05,24288);
INSERT INTO PRODUCT VALUES(‘PVC23DRT’,’PVC pipe, 3.5-in., 8-ft’ ,’2018-02-20′,188, 75, 5.87,0.00,NULL);
INSERT INTO PRODUCT VALUES(‘SM-18277′,’1.25-in. metal screw, 25′ ,’2018-03-01’,172, 75, 6.99,0.00,21225);
INSERT INTO PRODUCT VALUES(‘SW-23116′,’2.5-in. wd. screw, 50′ ,’2018-02-24’,237,100, 8.45,0.00,21231);
INSERT INTO PRODUCT VALUES(‘WR3/TT3′ ,’Steel matting, 4”x8”x1/6″, .5″ mesh’,’2018-01-17′, 18, 5,119.95,0.10,25595);
/* CUSTOMER rows */
INSERT INTO CUSTOMER VALUES(10010,’Ramas’ ,’Alfred’,’A’ ,’615′,’844-2573′,0);
INSERT INTO CUSTOMER VALUES(10011,’Dunne’ ,’Leona’ ,’K’ ,’713′,’894-1238′,0);
INSERT INTO CUSTOMER VALUES(10012,’Smith’ ,’Kathy’ ,’W’ ,’615′,’894-2285′,345.86);
INSERT INTO CUSTOMER VALUES(10013,’Olowski’ ,’Paul’ ,’F’ ,’615′,’894-2180′,536.75);
INSERT INTO CUSTOMER VALUES(10014,’Orlando’ ,’Myron’ ,NULL,’615′,’222-1672′,0);
INSERT INTO CUSTOMER VALUES(10015,’O”Brian’,’Amy’ ,’B’ ,’713′,’442-3381′,0);
INSERT INTO CUSTOMER VALUES(10016,’Brown’ ,’James’ ,’G’ ,’615′,’297-1228′,221.19);
INSERT INTO CUSTOMER VALUES(10017,’Williams’,’George’,NULL,’615′,’290-2556′,768.93);
INSERT INTO CUSTOMER VALUES(10018,’Farriss’ ,’Anne’ ,’G’ ,’713′,’382-7185′,216.55);
INSERT INTO CUSTOMER VALUES(10019,’Smith’ ,’Olette’,’K’ ,’615′,’297-3809′,0);
/* INVOICE rows */
INSERT INTO INVOICE VALUES(1001,10014,’2018-01-16′);
INSERT INTO INVOICE VALUES(1002,10011,’2018-01-16′);
INSERT INTO INVOICE VALUES(1003,10012,’2018-01-16′);
INSERT INTO INVOICE VALUES(1004,10011,’2018-01-17′);
INSERT INTO INVOICE VALUES(1005,10018,’2018-01-17′);
INSERT INTO INVOICE VALUES(1006,10014,’2018-01-17′);
INSERT INTO INVOICE VALUES(1007,10015,’2018-01-17′);
INSERT INTO INVOICE VALUES(1008,10011,’2018-01-17′);
/* LINE rows */
INSERT INTO LINE VALUES(1001,1,’13-Q2/P2′,1,14.99);
INSERT INTO LINE VALUES(1001,2,’23109-HB’,1,9.95);
INSERT INTO LINE VALUES(1002,1,’54778-2T’,2,4.99);
INSERT INTO LINE VALUES(1003,1,’2238/QPD’,1,38.95);
INSERT INTO LINE VALUES(1003,2,’1546-QQ2′,1,39.95);
INSERT INTO LINE VALUES(1003,3,’13-Q2/P2′,5,14.99);
INSERT INTO LINE VALUES(1004,1,’54778-2T’,3,4.99);
INSERT INTO LINE VALUES(1004,2,’23109-HB’,2,9.95);
INSERT INTO LINE VALUES(1005,1,’PVC23DRT’,12,5.87);
INSERT INTO LINE VALUES(1006,1,’SM-18277′,3,6.99);
INSERT INTO LINE VALUES(1006,2,’2232/QTY’,1,109.92);
INSERT INTO LINE VALUES(1006,3,’23109-HB’,1,9.95);
INSERT INTO LINE VALUES(1006,4,’89-WRE-Q’,1,256.99);
INSERT INTO LINE VALUES(1007,1,’13-Q2/P2′,2,14.99);
INSERT INTO LINE VALUES(1007,2,’54778-2T’,1,4.99);
INSERT INTO LINE VALUES(1008,1,’PVC23DRT’,5,5.87);
INSERT INTO LINE VALUES(1008,2,’WR3/TT3′,3,119.95);
INSERT INTO LINE VALUES(1008,3,’23109-HB’,1,9.95);
/* EMP rows */
INSERT INTO EMP VALUES(100,’Mr.’ ,’Kolmycz’ ,’George’ ,’D’ ,’1942-06-15′,’1985-03-15′,’615′,’324-5456′,NULL);
INSERT INTO EMP VALUES(101,’Ms.’ ,’Lewis’ ,’Rhonda’ ,’G’ ,’1965-03-19′,’1986-04-25′,’615′,’324-4472′,100);
INSERT INTO EMP VALUES(102,’Mr.’ ,’Vandam’ ,’Rhett’ ,NULL,’1958-11-14′,’1990-12-20′,’901′,’675-8993′,100);
INSERT INTO EMP VALUES(103,’Ms.’ ,’Jones’ ,’Anne’ ,’M’ ,’1974-10-16′,’1994-08-28′,’615′,’898-3456′,100);
INSERT INTO EMP VALUES(104,’Mr.’ ,’Lange’ ,’John’ ,’P’ ,’1971-11-08′,’1994-10-20′,’901′,’504-4430′,105);
INSERT INTO EMP VALUES(105,’Mr.’ ,’Williams’ ,’Robert’ ,’D’ ,’1975-03-14′,’1998-11-08′,’615′,’890-3220′,NULL);
INSERT INTO EMP VALUES(106,’Mrs.’,’Smith’ ,’Jeanine’,’K’ ,’1968-02-12′,’1989-01-05′,’615′,’324-7883′,105);
INSERT INTO EMP VALUES(107,’Mr.’ ,’Diante’ ,’Jorge’ ,’D’ ,’1974-08-21′,’1994-07-02′,’615′,’890-4567′,105);
INSERT INTO EMP VALUES(108,’Mr.’ ,’Wiesenbach’,’Paul’ ,’R’ ,’1966-02-14′,’1992-11-18′,’615′,’897-4358′,NULL);
INSERT INTO EMP VALUES(109,’Mr.’ ,’Smith’ ,’George’ ,’K’ ,’1961-06-18′,’1989-04-14′,’901′,’504-3339′,108);
INSERT INTO EMP VALUES(110,’Mrs.’,’Genkazi’ ,’Leighla’,’W’ ,’1970-05-19′,’1990-12-01′,’901′,’569-0093′,108);
INSERT INTO EMP VALUES(111,’Mr.’ ,’Washington’,’Rupert’ ,’E’ ,’1966-01-03′,’1993-06-21′,’615′,’890-4925′,105);
INSERT INTO EMP VALUES(112,’Mr.’ ,’Johnson’ ,’Edward’ ,’E’ ,’1961-05-14′,’1983-12-01′,’615′,’898-4387′,100);
INSERT INTO EMP VALUES(113,’Ms.’ ,’Smythe’ ,’Melanie’,’P’ ,’1970-09-15′,’1999-05-11′,’615′,’324-9006′,105);
INSERT INTO EMP VALUES(114,’Ms.’ ,’Brandon’ ,’Marie’ ,’G’ ,’1956-11-02′,’1979-11-15′,’901′,’882-0845′,108);
INSERT INTO EMP VALUES(115,’Mrs.’,’Saranda’ ,’Hermine’,’R’ ,’1972-07-25′,’1993-04-23′,’615′,’324-5505′,105);
INSERT INTO EMP VALUES(116,’Mr.’ ,’Smith’ ,’George’ ,’A’ ,’1965-11-08′,’1988-12-10′,’615′,’890-2984′,108);
/* EMPLOYEE rows */
INSERT INTO EMPLOYEE VALUES(100,’Mr.’ ,’Kolmycz’ ,’George’ ,’D’ ,’1942-06-15′,’1985-03-15′,18,’615′,’324-5456′);
INSERT INTO EMPLOYEE VALUES(101,’Ms.’ ,’Lewis’ ,’Rhonda’ ,’G’ ,’1965-03-19′,’1986-04-25′,16,’615′,’324-4472′);
INSERT INTO EMPLOYEE VALUES(102,’Mr.’ ,’Vandam’ ,’Rhett’ ,NULL,’1958-11-14′,’1990-12-20′,12,’901′,’675-8993′);
INSERT INTO EMPLOYEE VALUES(103,’Ms.’ ,’Jones’ ,’Anne’ ,’M’ ,’1974-10-16′,’1994-08-28′, 8,’615′,’898-3456′);
INSERT INTO EMPLOYEE VALUES(104,’Mr.’ ,’Lange’ ,’John’ ,’P’ ,’1971-11-08′,’1994-10-20′, 8,’901′,’504-4430′);
INSERT INTO EMPLOYEE VALUES(105,’Mr.’ ,’Williams’ ,’Robert’ ,’D’ ,’1975-03-14′,’1998-11-08′, 4,’615′,’890-3220′);
INSERT INTO EMPLOYEE VALUES(106,’Mrs.’,’Smith’ ,’Jeanine’,’K’ ,’1968-02-12′,’1989-01-05′,14,’615′,’324-7883′);
INSERT INTO EMPLOYEE VALUES(107,’Mr.’ ,’Diante’ ,’Jorge’ ,’D’ ,’1974-08-21′,’1994-07-02′, 8,’615′,’890-4567′);
INSERT INTO EMPLOYEE VALUES(108,’Mr.’ ,’Wiesenbach’,’Paul’ ,’R’ ,’1966-02-14′,’1992-11-18′,10,’615′,’897-4358′);
INSERT INTO EMPLOYEE VALUES(109,’Mr.’ ,’Smith’ ,’George’ ,’K’ ,’1961-06-18′,’1989-04-14′,13,’901′,’504-3339′);
INSERT INTO EMPLOYEE VALUES(110,’Mrs.’,’Genkazi’ ,’Leighla’,’W’ ,’1970-05-19′,’1990-12-01′,12,’901′,’569-0093′);
INSERT INTO EMPLOYEE VALUES(111,’Mr.’ ,’Washington’,’Rupert’ ,’E’ ,’1966-01-03′,’1993-06-21′, 9,’615′,’890-4925′);
INSERT INTO EMPLOYEE VALUES(112,’Mr.’ ,’Johnson’ ,’Edward’ ,’E’ ,’1961-05-14′,’1983-12-01′,19,’615′,’898-4387′);
INSERT INTO EMPLOYEE VALUES(113,’Ms.’ ,’Smythe’ ,’Melanie’,’P’ ,’1970-09-15′,’1999-05-11′, 3,’615′,’324-9006′);
INSERT INTO EMPLOYEE VALUES(114,’Ms.’ ,’Brandon’ ,’Marie’ ,’G’ ,’1956-11-02′,’1979-11-15′,23,’901′,’882-0845′);
INSERT INTO EMPLOYEE VALUES(115,’Mrs.’,’Saranda’ ,’Hermine’,’R’ ,’1972-07-25′,’1993-04-23′, 9,’615′,’324-5505′);
INSERT INTO EMPLOYEE VALUES(116,’Mr.’ ,’Smith’ ,’George’ ,’A’ ,’1965-11-08′,’1988-12-10′,14,’615′,’890-2984′);
COMMIT;
