Goal: Apply query If-Then-Else logic.

Using the class example database (SQLFUN), create the required SQL statements to produce the following results:

• Create 3 SQL If-Then-Else queries.
• Explain what the purpose of the query is.
• Provide the results of the query.

 

        **PLEASE INCLUDE ACTUAL CODES**

***BELOW IS THE SQLFUN CLASS EXAMPLE***

 

create database sqlfun;
commit;

use sqlfun;

CREATE TABLE IF NOT EXISTS L_EMPLOYEES
(EMPLOYEE_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(10),
LAST_NAME VARCHAR(20),
DEPT_CODE VARCHAR(3),
HIRE_DATE DATE,
CREDIT_LIMIT DECIMAL(4,2),
PHONE_NUMBER VARCHAR(4),
MANAGER_ID DECIMAL(3)
);

COMMIT;

ANALYZE TABLE L_EMPLOYEES;


CREATE TABLE IF NOT EXISTS L_FOODS
(SUPPLIER_ID VARCHAR(3),
PRODUCT_CODE VARCHAR(2),
MENU_ITEM INT(2),
DESCRIPTION VARCHAR(20),
PRICE DECIMAL(4,2),
PRICE_INCREASE DECIMAL(4,2),
PRIMARY KEY (SUPPLIER_ID, PRODUCT_CODE));

COMMIT;

ANALYZE TABLE L_FOODS;

-- CREATE THE L_DEPARTMENTS TABLE
CREATE TABLE IF NOT EXISTS L_DEPARTMENTS
(DEPT_CODE VARCHAR(3) PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(30));


COMMIT;

ANALYZE TABLE L_DEPARTMENTS;


CREATE TABLE IF NOT EXISTS L_LUNCHES
(LUNCH_ID INT(3) PRIMARY KEY,
LUNCH_DATE DATE,
EMPLOYEE_ID INT(3),
DATE_ENTERED DATE);

COMMIT;

ANALYZE TABLE L_LUNCHES;

CREATE TABLE IF NOT EXISTS L_LUNCH_ITEMS
(LUNCH_ID INT(3),
ITEM_NUMBER INT(2),
SUPPLIER_ID VARCHAR(3),
PRODUCT_CODE VARCHAR(2),
QUANTITY INT(1));

ALTER TABLE L_LUNCH_ITEMS
ADD PRIMARY KEY (LUNCH_ID, ITEM_NUMBER);


COMMIT;

ANALYZE TABLE L_LUNCH_ITEMS;

CREATE TABLE IF NOT EXISTS L_SUPPLIERS
(SUPPLIER_ID VARCHAR(3) PRIMARY KEY,
SUPPLIER_NAME VARCHAR(30));

COMMIT;

ANALYZE TABLE L_SUPPLIERS;

CREATE TABLE IF NOT EXISTS L_CONSTANTS
(BUSINESS_NAME VARCHAR(30),
BUSINESS_START_DATE DATE,
LUNCH_BUDGET DECIMAL(5,2),
OWNER_NAME VARCHAR(30));

COMMIT;

ANALYZE TABLE L_CONSTANTS;


ALTER TABLE L_EMPLOYEES
ADD CONSTRAINT FK_L_EMPLOYEES_DEPT_CODE
FOREIGN KEY (DEPT_CODE)
REFERENCES L_DEPARTMENTS (DEPT_CODE);


-- ALTER TABLE L_EMPLOYEES
-- ADD CONSTRAINT FK_L_EMPLOYEES_MANAGER_ID
-- FOREIGN KEY (MANAGER_ID)
-- REFERENCES L_EMPLOYEES (EMPLOYEE_ID); 


ALTER TABLE L_LUNCHES
ADD CONSTRAINT FK_L_LUNCHES_EMPLOYEES
FOREIGN KEY (EMPLOYEE_ID)
REFERENCES L_EMPLOYEES (EMPLOYEE_ID);


ALTER TABLE L_LUNCH_ITEMS
ADD CONSTRAINT FK_L_LUNCH_ITEMS_LUNCHES
FOREIGN KEY (LUNCH_ID)
REFERENCES L_LUNCHES (LUNCH_ID);


ALTER TABLE L_LUNCH_ITEMS
ADD CONSTRAINT FK_L_LUNCH_ITEMS_FOODS
FOREIGN KEY (SUPPLIER_ID, PRODUCT_CODE)
REFERENCES L_FOODS (SUPPLIER_ID, PRODUCT_CODE);


ALTER TABLE L_FOODS
ADD CONSTRAINT FK_L_FOODS_SUPPLIERS
FOREIGN KEY (SUPPLIER_ID)
REFERENCES L_SUPPLIERS (SUPPLIER_ID)
ON DELETE CASCADE;



-- Create NOT NULL constraints - Make a required field
ALTER TABLE L_EMPLOYEES
ADD CONSTRAINT NN_L_EMPLOYEES_FIRST_NAME
CHECK (FIRST_NAME IS NOT NULL);


ALTER TABLE L_EMPLOYEES
ADD CONSTRAINT NN_L_EMPLOYEES_LAST_NAME
CHECK (LAST_NAME IS NOT NULL);


ALTER TABLE L_LUNCHES
ADD CONSTRAINT NN_L_LUNCHES_EMPLOYEE_ID
CHECK (EMPLOYEE_ID IS NOT NULL);


-- Create UNIQUENESS constraints
ALTER TABLE L_EMPLOYEES
ADD CONSTRAINT UNIQUE_L_EMPLOYEES_FULL_NAME
UNIQUE (FIRST_NAME, LAST_NAME);


ALTER TABLE L_EMPLOYEES
ADD CONSTRAINT UNIQUE_L_EMPPLOYEES_PHONE_NUM
UNIQUE (PHONE_NUMBER);


-- Create CHECK constraints
ALTER TABLE L_FOODS
ADD CONSTRAINT CHECK_L_FOODS_PRICE_MAX_PRICE
CHECK (PRICE 

-- ***************************************************
-- END OF LOADING THE LUNCHES DATABASE
-- ***************************************************

-- insert data into tables; the order matters due to foreign key constraints.

INSERT INTO L_DEPARTMENTS VALUES ('ACT', 'ACCOUNTING');
INSERT INTO L_DEPARTMENTS VALUES ('EXE', 'EXECUTIVE');
INSERT INTO L_DEPARTMENTS VALUES ('MKT', 'MARKETING');
INSERT INTO L_DEPARTMENTS VALUES ('PER', 'PERSONNEL');
INSERT INTO L_DEPARTMENTS VALUES ('SAL', 'SALES');
INSERT INTO L_DEPARTMENTS VALUES ('SHP', 'SHIPPING');
COMMIT;


INSERT INTO L_EMPLOYEES VALUES
(201, 'SUSAN', 'BROWN', 'EXE', '1998-06-01', 30, '3484', NULL);
INSERT INTO L_EMPLOYEES VALUES
(202, 'JIM', 'KERN', 'SAL', '1999-08-16', 25, '8722', 201);
INSERT INTO L_EMPLOYEES VALUES
(203, 'MARTHA', 'WOODS', 'SHP', '2009-02-02', 25, '7591', 201);
INSERT INTO L_EMPLOYEES VALUES
(204, 'ELLEN', 'OWENS', 'SAL', '2008-07-01', 15, '6830', 202);
INSERT INTO L_EMPLOYEES VALUES
(205, 'HENRY', 'PERKINS', 'SAL', '2006-03-01', 25, '5286', 202);
INSERT INTO L_EMPLOYEES VALUES
(206, 'CAROL', 'ROSE', 'ACT', NULL, NULL, NULL, NULL);
INSERT INTO L_EMPLOYEES VALUES
(207, 'DAN', 'SMITH', 'SHP', '2008-12-01', 25, '2259', 203);
INSERT INTO L_EMPLOYEES VALUES
(208, 'FRED', 'CAMPBELL', 'SHP', '2008-04-01', 25, '1752', 203);
INSERT INTO L_EMPLOYEES VALUES
(209, 'PAULA', 'JACOBS', 'MKT', '1999-03-17', 15, '3357', 201);
INSERT INTO L_EMPLOYEES VALUES
(210, 'NANCY', 'HOFFMAN', 'SAL', '2007-02-16', 25, '2974', 203);
COMMIT;




INSERT INTO L_SUPPLIERS VALUES ('ARR', 'ALICE & RAY''S RESTAURANT');
INSERT INTO L_SUPPLIERS VALUES ('ASP', 'A SOUP PLACE');
INSERT INTO L_SUPPLIERS VALUES ('CBC', 'CERTIFIED BEEF COMPANY');
INSERT INTO L_SUPPLIERS VALUES ('FRV', 'FRANK REED''S VEGETABLES');
INSERT INTO L_SUPPLIERS VALUES ('FSN', 'FRANK & SONS');
INSERT INTO L_SUPPLIERS VALUES ('JBR', 'JUST BEVERAGES');
INSERT INTO L_SUPPLIERS VALUES ('JPS', 'JIM PARKER''S SHOP');
INSERT INTO L_SUPPLIERS VALUES ('VSB', 'VIRGINIA STREET BAKERY');
COMMIT;

INSERT INTO L_FOODS VALUES ('ASP', 'FS', 1, 'FRESH SALAD', 2, 0.25);
INSERT INTO L_FOODS VALUES ('ASP', 'SP', 2, 'SOUP OF THE DAY', 1.5, NULL);
INSERT INTO L_FOODS VALUES ('ASP', 'SW', 3, 'SANDWICH', 3.5, 0.4);
INSERT INTO L_FOODS VALUES ('CBC', 'GS', 4, 'GRILLED STEAK', 6, 0.7);
INSERT INTO L_FOODS VALUES ('CBC', 'SW', 5, 'HAMBURGER', 2.5, 0.3);
INSERT INTO L_FOODS VALUES ('FRV', 'BR', 6, 'BROCCOLI', 1, 0.05);
INSERT INTO L_FOODS VALUES ('FRV', 'FF', 7, 'FRENCH FRIES', 1.5, NULL);
INSERT INTO L_FOODS VALUES ('JBR', 'AS', 8, 'SODA', 1.25, 0.25);
INSERT INTO L_FOODS VALUES ('JBR', 'VR', 9, 'COFFEE', 0.85, 0.15);
INSERT INTO L_FOODS VALUES ('VSB', 'AS', 10, 'DESSERT', 3, 0.5);
COMMIT;

INSERT INTO L_LUNCHES VALUES (1, '2011-11-16', 201, STR_TO_DATE('2011-10-13 10:35:24', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (2, '2011-11-1', 207, STR_TO_DATE('2011-10-13 10:35:39', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (3, '2011-11-1', 203, STR_TO_DATE('2011-10-13 10:35:45', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (4, '2011-11-1', 204, STR_TO_DATE('2011-10-13 10:35:58', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (6, '2011-11-1', 202, STR_TO_DATE('2011-10-13 10:36:41', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (7, '2011-11-1', 210, STR_TO_DATE('2011-10-13 10:38:52', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (8, '2011-11-1', 201, STR_TO_DATE('2011-10-14 11:15:37', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (9, '2011-11-1', 208, STR_TO_DATE('2011-10-14 14:23:36', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (12, '2011-11-25', 204, STR_TO_DATE('2011-10-14 15:02:53', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (13, '2011-11-25', 207, STR_TO_DATE('2011-10-18 08:42:11', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (15, '2011-11-25', 205, STR_TO_DATE('2011-10-21 16:23:50', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (16, '2011-12-05', 201, STR_TO_DATE('2011-10-21 16:23:59', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (17, '2011-12-05', 210, STR_TO_DATE('2011-10-21 16:35:26', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (20, '2011-12-05', 205, STR_TO_DATE('2011-10-24 09:55:27', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (21, '2011-12-05', 203, STR_TO_DATE('2011-10-24 11:43:13', '%Y-%m-%d %H:%i:%s'));
INSERT INTO L_LUNCHES VALUES (22, '2011-12-05', 208, STR_TO_DATE('2011-10-24 14:37:32', '%Y-%m-%d %H:%i:%s'));
COMMIT;

INSERT INTO L_LUNCH_ITEMS VALUES (1, 1, 'ASP', 'FS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (1, 2, 'ASP', 'SW', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (1, 3, 'JBR', 'VR', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (2, 1, 'ASP', 'SW', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (2, 2, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (2, 3, 'JBR', 'VR', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (2, 4, 'VSB', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (3, 1, 'ASP', 'FS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (3, 2, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (3, 3, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (3, 4, 'JBR', 'VR', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (3, 5, 'JBR', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (4, 1, 'ASP', 'SP', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (4, 2, 'CBC', 'SW', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (4, 3, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (4, 4, 'JBR', 'AS', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (6, 1, 'ASP', 'SP', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (6, 2, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (6, 3, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (6, 4, 'JBR', 'VR', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (6, 5, 'VSB', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (7, 1, 'ASP', 'FS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (7, 2, 'ASP', 'SP', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (7, 3, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (7, 4, 'JBR', 'VR', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (7, 5, 'VSB', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (8, 1, 'ASP', 'FS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (8, 2, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (8, 3, 'JBR', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (9, 1, 'ASP', 'FS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (9, 2, 'ASP', 'SP', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (9, 3, 'CBC', 'SW', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (9, 4, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (9, 5, 'JBR', 'VR', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (9, 6, 'JBR', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (12, 1, 'ASP', 'FS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (12, 2, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (12, 3, 'JBR', 'VR', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (12, 4, 'VSB', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (13, 1, 'ASP', 'SP', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (13, 2, 'ASP', 'SW', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (13, 3, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (13, 4, 'JBR', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (15, 1, 'ASP', 'SP', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (15, 2, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (15, 3, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (15, 4, 'JBR', 'AS', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (16, 1, 'ASP', 'FS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (16, 2, 'ASP', 'SW', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (16, 3, 'CBC', 'SW', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (16, 4, 'JBR', 'VR', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (16, 5, 'JBR', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (17, 1, 'ASP', 'SP', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (17, 2, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (17, 3, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (17, 4, 'JBR', 'VR', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (17, 5, 'VSB', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (20, 1, 'ASP', 'FS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (20, 2, 'ASP', 'SP', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (20, 3, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (20, 4, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (20, 5, 'JBR', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (21, 1, 'ASP', 'SP', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (21, 2, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (21, 3, 'JBR', 'VR', 2);
INSERT INTO L_LUNCH_ITEMS VALUES (21, 4, 'VSB', 'AS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (22, 1, 'ASP', 'FS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (22, 2, 'CBC', 'GS', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (22, 3, 'FRV', 'FF', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (22, 4, 'JBR', 'VR', 1);
INSERT INTO L_LUNCH_ITEMS VALUES (22, 5, 'JBR', 'AS', 1);
COMMIT;