Count the number of foods supplied by each supplier. List all the suppliers. Show the supplier_id, supplier_name, and number of foods from each supplier. Sort the rows on the supplier_id.

6. Count the number of employees who work in each department. List all the departments. Show the department code, department name, and the number of employees. Sort on the dept_code.

7. Count the number of servings of each food in all the lunches in the l_lunch_items table. Show all of the foods. Return supplier id, product code, description, and the number of servings in all of the lunches.

10. For each lunch date, list the total quantity of each food needed for that lunch. List the columns: lunch_date, food_description, full name of the supplier, total quantity, and total price.

11. Find the total amount spent on lunches by each employee. Only show the employees who spent more than their credit limit. Show first_name,last_name, credit_limit, and total_price_spent in your results. Order your answer by total_price_spent in descending order.

home / homework help / questions and answers / engineering / computer science / note: tables included, and answers (1, 2, 3, 4, …

EMPLOYEE_ID FIRST_NAME LAST_NAME DEPT_CODE HIRE_DATE CREDIT_LIMIT PHONE_NUMBER MANAGER_ID 201 SUSAN BROWN EXE 01-JUN-98 30 3484 – 202 JIM KERN SAL 16-AUG-99 25 8722 201 203 MARTHA WOODS SHP 02-FEB-09 25 7591 201 204 ELLEN OWENS SAL 01-JUL-08 15 6830 202 205 HENRY PERKINS SAL 01-MAR-06 25 5286 202 206 CAROL ROSE ACT – – – – 207 DAN SMITH SHP 01-DEC-08 25 2259 203 208 FRED CAMPBELL SHP 01-APR-08 25 1752 203 209 PAULA JACOBS MKT 17-MAR-99 15 3357 201 210 NANCY HOFFMAN SAL 16-FEB-07 25 2974 203

SUPPLIER_ID PRODUCT_CODE MENU_ITEM DESCRIPTION PRICE PRICE_INCREASE ASP FS 1 FRESH SALAD 2 .25 ASP SP 2 SOUP OF THE DAY 1.5 – ASP SW 3 SANDWICH 3.5 .4 CBC GS 4 GRILLED STEAK 6 .7 CBC SW 5 HAMBURGER 2.5 .3 FRV BR 6 BROCCOLI 1 .05 FRV FF 7 FRENCH FRIES 1.5 – JBR AS 8 SODA 1.25 .25 JBR VR 9 COFFEE .85 .15 VSB AS 10 DESSERT 3 .5

LUNCH_ID LUNCH_DATE EMPLOYEE_ID DATE_ENTERED 1 16-NOV-11 201 13-OCT-11 2 16-NOV-11 207 13-OCT-11 3 16-NOV-11 203 13-OCT-11 4 16-NOV-11 204 13-OCT-11 6 16-NOV-11 202 13-OCT-11 7 16-NOV-11 210 13-OCT-11 8 25-NOV-11 201 14-OCT-11 9 25-NOV-11 208 14-OCT-11 12 25-NOV-11 204 14-OCT-11 13 25-NOV-11 207 18-OCT-11

SUPPLIER_ID SUPPLIER_NAME ARR ALICE & RAY’S RESTAURANT ASP A SOUP PLACE CBC CERTIFIED BEEF COMPANY FRV FRANK REED’S VEGETABLES FSN FRANK & SONS JBR JUST BEVERAGES JPS JIM PARKER’S SHOP VSB VIRGINIA STREET BAKERY

DEPT_CODE DEPARTMENT_NAME ACT ACCOUNTING EXE EXECUTIVE MKT MARKETING PER PERSONNEL SAL SALES SHP SHIPPING