Structured Query Language

Discussion

Data in a table is summarized using column functions, which examine all of the data in a column. An example would be to count the number of distinct values in a column. Joins combine data from two or more different tables. The data that is returns is dependent upon the relationships that exist.

Please respond to all of the following prompts:

• Discuss the possibility of mixing summarized data with non summarized data?
• Discuss how you could accomplish this. Give examples to illustrate your point.
• What is inner join and what is outer join? Give some potential real world applications of joins. (Show some SQL join code.)

 

Lab Activity

Goal: Use the MIN function.

Using the class example database (SQLFUN):

·  Find the price of the least expensive food in the L_foods table.

·  Find all the foods with that minimum price.

 

Goal: Find rows that are not matched. (They are dropped from an inner join.)

Using the class example database (SQLFUN):

• Are there any employees who are not attending any of the lunches? If so, who are they?
• List the Employee ID, first name, and last name of these employees.
• Sort them on the employee_id.

 

 

create database sqlfun;commit;use sqlfun;CREATE TABLE IF NOT EXISTS L_EMPLOYEES(EMPLOYEE_IDINT NOT NULL PRIMARY KEY AUTO_INCREMENT,FIRST_NAMEVARCHAR(10),LAST_NAMEVARCHAR(20),DEPT_CODEVARCHAR(3),HIRE_DATEDATE,CREDIT_LIMITDECIMAL(4,2),PHONE_NUMBERVARCHAR(4),MANAGER_IDDECIMAL(3));COMMIT;ANALYZE TABLE L_EMPLOYEES;CREATE TABLE IF NOT EXISTS L_FOODS(SUPPLIER_IDVARCHAR(3),PRODUCT_CODEVARCHAR(2),MENU_ITEMINT(2),DESCRIPTIONVARCHAR(20),PRICEDECIMAL(4,2),PRICE_INCREASEDECIMAL(4,2),PRIMARY KEY (SUPPLIER_ID, PRODUCT_CODE));COMMIT;ANALYZE TABLE L_FOODS;-- CREATE THE L_DEPARTMENTS TABLECREATE TABLE IF NOT EXISTS L_DEPARTMENTS(DEPT_CODEVARCHAR(3) PRIMARY KEY,DEPARTMENT_NAMEVARCHAR(30));COMMIT;ANALYZE TABLE L_DEPARTMENTS;CREATE TABLE IF NOT EXISTS L_LUNCHES(LUNCH_IDINT(3) PRIMARY KEY,LUNCH_DATEDATE,EMPLOYEE_IDINT(3),DATE_ENTEREDDATE);COMMIT;ANALYZE TABLE L_LUNCHES;CREATE TABLE IF NOT EXISTS L_LUNCH_ITEMS(LUNCH_IDINT(3),ITEM_NUMBERINT(2),SUPPLIER_IDVARCHAR(3),PRODUCT_CODEVARCHAR(2),QUANTITYINT(1));ALTER TABLE L_LUNCH_ITEMSADD PRIMARY KEY (LUNCH_ID, ITEM_NUMBER);