Loading

Database - CIS250 Unit 4 Assignment 2 Find the columns that make up the primary key of the l_lunch_items table

Availability: In stock

Regular Price: $55.00

Special Price: $33.00

Already Bought 3 times.

Database - CIS250 Unit 4 Assignment 2 Find the columns that make up the primary key of the l_lunch_items table

Double click on above image to view full picture

Zoom Out
Zoom In

More Views

Quick Overview

 


CIS250 Unit 4 Assignment 2


Directions


Perform the following tasks below by using the correct SQL statements.   Create the statements in Oracle by using the following steps (SQL > SQL commands > Enter Command).  Once your command is error-free, copy and paste your statements into this assignment document.  Upload this Word document for grading.


 


All questions should be answered with one SQL statement.  Use the new Join syntax.


 


 


 



  1.  Find the columns that make up the primary key of the l_lunch_items table.


             Sort the columns by position.  (Do this in one statement).


 


 



  1. List all the lunches that employees have signed up for. Show me first_name, last_name, lunch_date, and product_code of the items they ordered.  Do not include employees who don't have a manager.


Sort the rows by product_code.


 


 



  1. From the employees table, list the employee_id, last_name, and phone_number of every employee, along with the last_name and phone number of his manager. Sort the rows by employee_id. (Hint…tables can be joined to themselves…just give each table a different alias).


 



  1.  Similar to 3, but include rows for the people who do not have managers.


 



  1. 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.


 



  1. 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.


 



  1. 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.


 


 


 


 



  1. Show the columns that make up the foreign key of the l_lunch_items table.


 



  1. Find the total amount spent on lunches by each employee.  Show first_name,


             last_name, credit_limit, and total_price_spent in your results. Order your


             answer by total_price_spent in descending order.


 



  1. 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.


 



  1. 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.

Regular Price: $55.00

Special Price: $33.00

Details

 

CIS250 Unit 4 Assignment 2

Directions

Perform the following tasks below by using the correct SQL statements.   Create the statements in Oracle by using the following steps (SQL > SQL commands > Enter Command).  Once your command is error-free, copy and paste your statements into this assignment document.  Upload this Word document for grading.

 

All questions should be answered with one SQL statement.  Use the new Join syntax.

 

 

 

  1.  Find the columns that make up the primary key of the l_lunch_items table.

             Sort the columns by position.  (Do this in one statement).

 

 

  1. List all the lunches that employees have signed up for. Show me first_name, last_name, lunch_date, and product_code of the items they ordered.  Do not include employees who don't have a manager.

Sort the rows by product_code.

 

 

  1. From the employees table, list the employee_id, last_name, and phone_number of every employee, along with the last_name and phone number of his manager. Sort the rows by employee_id. (Hint…tables can be joined to themselves…just give each table a different alias).

 

  1.  Similar to 3, but include rows for the people who do not have managers.

 

  1. 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.

 

  1. 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.

 

  1. 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.

 

 

 

 

  1. Show the columns that make up the foreign key of the l_lunch_items table.

 

  1. Find the total amount spent on lunches by each employee.  Show first_name,

             last_name, credit_limit, and total_price_spent in your results. Order your

             answer by total_price_spent in descending order.

 

  1. 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.

 

  1. 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.

Additional Information

Programming Language Database & SQL

Product Tags

Use spaces to separate tags. Use single quotes (') for phrases.