Use LargeCO tables to complete this assignment.
Write a stored procedure that adds a new employee to lgemployee table. Pass the field values to the procedure as parameters.
Use parameters and a DECLARE statement to display employees that were hired in every decade beginning 1970. The program should return the statements similar to this:
12 employees were hired between 1970 and 1980
19 employees were hired between 1980 and 1990
etc.
LageCo Table Code:
CREATE TABLE lgbrand (
brand_id numeric(4,0) NULL,
brand_name VARCHAR(100) NULL,
brand_type VARCHAR(20) NULL
);
CREATE TABLE lgproduct (
prod_sku VARCHAR(15) NOT NULL,
prod_descript VARCHAR(255) NULL,
prod_type VARCHAR(255) NULL,
prod_base VARCHAR(255) NULL,
prod_category VARCHAR(255) NULL,
prod_price NUMERIC(10,2) NULL,
prod_qoh NUMERIC(10,0) NULL,
prod_min NUMERIC(10,0) NULL,
brand_id NUMERIC(4,0) NULL
);
CREATE TABLE lgvendor (
vend_id NUMERIC(6,0) NOT NULL,
vend_name VARCHAR(255) NULL,
vend_street VARCHAR(50) NULL,
vend_city VARCHAR(50) NULL,
vend_state VARCHAR(2) NULL,
vend_zip VARCHAR(5) NULL
);
CREATE TABLE lgsupplies (
prod_sku VARCHAR(15) NOT NULL,
vend_id NUMERIC(5,0) NOT NULL
);
CREATE TABLE lgdepartment (
dept_num NUMERIC(5,0) NOT NULL,
dept_name VARCHAR(50) NOT NULL,
dept_mail_box VARCHAR(3) NULL,
dept_phone VARCHAR(9) NULL,
emp_num NUMERIC(6,0) NULL
);
CREATE TABLE lgemployee (
emp_num NUMERIC(6,0) NOT NULL,
emp_fname VARCHAR(20) NULL,
emp_lname VARCHAR(25) NOT NULL,
emp_email VARCHAR(25) NOT NULL,
emp_phone VARCHAR(20) NULL,
emp_hiredate DATE NOT NULL,
emp_title VARCHAR(45) NOT NULL,
emp_comm NUMERIC(2,2) NULL,
dept_num NUMERIC(5,0) NULL
);
CREATE TABLE lgsalary_history (
emp_num NUMERIC(6,0) NULL,
sal_from DATE NULL,
sal_end DATE NULL,
sal_amount NUMERIC(10,2) NULL
);
CREATE TABLE lgcustomer (
cust_code NUMERIC(38,0) NOT NULL,
cust_fname VARCHAR(20) NOT NULL,
cust_lname VARCHAR(20) NOT NULL,
cust_street VARCHAR(70) NULL,
cust_city VARCHAR(50) NULL,
cust_state CHAR(2) NULL,
cust_zip CHAR(5) NULL,
cust_balance NUMERIC(8,2) NULL
);
CREATE TABLE lginvoice (
inv_num NUMERIC(38,0) NOT NULL,
inv_date DATE NULL,
cust_code NUMERIC NULL,
inv_total NUMERIC(11,2) NULL,
employee_id NUMERIC NULL
);
CREATE TABLE lgline (
inv_num NUMERIC NOT NULL,
line_num NUMERIC NOT NULL,
prod_sku VARCHAR(15) NULL,
line_qty NUMERIC NULL,
line_price NUMERIC(8,2) NULL
);
--Insert data
--LGBRAND
INSERT INTO LGBRAND VALUES (23, 'FORESTERS BEST', 'VALUE');
INSERT INTO LGBRAND VALUES (33, 'BINDER PRIME', 'PREMIUM');
INSERT INTO LGBRAND VALUES (25, 'STUTTENFURST', 'CONTRACTOR');
INSERT INTO LGBRAND VALUES (35, 'LE MODE', 'PREMIUM');
INSERT INTO LGBRAND VALUES (27, 'HOME COMFORT', 'CONTRACTOR');
INSERT INTO LGBRAND VALUES (28, 'OLDE TYME QUALITY', 'CONTRACTOR');
INSERT INTO LGBRAND VALUES (29, 'BUSTERS', 'VALUE');
INSERT INTO LGBRAND VALUES (30, 'LONG HAUL', 'CONTRACTOR');
INSERT INTO LGBRAND VALUES (31, 'VALU-MATTE', 'VALUE');
INSERT INTO LGBRAND VALUES (24, 'REGAL HOME', 'VALUE');
INSERT INTO LGBRAND VALUES (26, 'HOMESTEADER FINEST', 'PREMIUM');
INSERT INTO LGBRAND VALUES (32, 'YOUR HOME HELPER', 'VALUE');
INSERT INTO LGBRAND VALUES (34, 'PRIME OF LIFE', 'VALUE');
--LGPRODUCT
INSERT INTO LGPRODUCT VALUES ('2366-EFV', 'Varnish, Interior, Polyurethane, Oil Modified, Gloss', 'Interior', 'Solvent', 'Top Coat', 6.59, 3, 25, 30);
INSERT INTO LGPRODUCT VALUES ('8841-JFP', 'Varnish, Interior, Polyurethane, Oil Modified, Satin', 'Interior', 'Solvent', 'Top Coat', 6.59, 30, 35, 25);
INSERT INTO LGPRODUCT VALUES ('7231-UES', 'Stain, Interior, for Concrete Floors', 'Interior', 'Solvent', 'Top Coat', 23.99, 50, 10, 30);
INSERT INTO LGPRODUCT VALUES ('8726-ZNM', 'Floor Paint, Alkyd, Low Gloss ', 'Exterior', 'Solvent', 'Top Coat', 21.99, 107, 15, 29);
INSERT INTO LGPRODUCT VALUES ('8543-RJN', 'Floor Paint, Alkyd, Low Gloss ', 'Interior', 'Solvent', 'Top Coat', 21.99, 10, 5, 31);
INSERT INTO LGPRODUCT VALUES ('3754-MAK', 'Floor Paint, Latex, Low Gloss ', 'Exterior', 'Water', 'Top Coat', 21.99, 70, 10, 33);
INSERT INTO LGPRODUCT VALUES ('1504-LVK', 'Floor Paint, Latex, Low Gloss ', 'Interior', 'Water', 'Top Coat', 21.99, 63, 25, 33);
INSERT INTO LGPRODUCT VALUES ('2200-DAI', 'Primer Sealer, Low Permeability, Latex, Interior', 'Interior', 'Water', 'Primer', 7.59, 66, 5, 31);
INSERT INTO LGPRODUCT VALUES ('6491-XKF', 'Fire Retardant Coating, Interior, Clear (ULC Approved)', 'Interior', 'Solvent', 'Top Coat', 32.99, 27, 25, 29);
INSERT INTO LGPRODUCT VALUES ('5541-HST', 'Fire Retardant Coating, Interior, Clear (ULC Approved)', 'Interior', 'Water', 'Top Coat', 32.99, 31, 15, 35);
INSERT INTO LGPRODUCT VALUES ('5508-OUB', 'Fire Retardant Coating, Alkyd, Interior, Flat (ULC Approved)', 'Interior', 'Solvent', 'Top Coat', 35.99, 56, 35, 29);
INSERT INTO LGPRODUCT VALUES ('1203-AIS', 'Fire Retardant Coating, Latex, Interior, Flat (ULC Approved)', 'Interior', 'Water', 'Top Coat', 35.99, 49, 25, 35);
INSERT INTO LGPRODUCT VALUES ('5437-WBO', 'Fire Retardant Sealer, Alkyd, Interior (ULC Approved)', 'Interior', 'Solvent', 'Top Coat', 28.99, 26, 30, 35);