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
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
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-
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 1 images
