CIS250 Unit 1 Assignment 2 Directions You must create the statements in Oracle b
ID: 3624615 • Letter: C
Question
CIS250 Unit 1 Assignment 2Directions
You must 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 document. Upload this Word document for grading.
.
4. Write the command to list all the employees hired in 2003 and 2004. Sort the rows by the hire_date column in descending order. Show columns last_name, hire_date, phone_number, and dept_code in your results. Also, change the name of dept_code column to department_code.
?
5. List all the distinct employee_id values in the l_lunches table. Sort these ids in ascending order.
6. List all distinct combinations of supplier_id and product_code from the l_lunch_items table. Exclude the data for lunch 17. Sort the result on both columns in ascending order.
7. Using the l_foods table, place the columns in this order: menu_item, description, price_increase, price, supplier_id, product_code. Sort the rows by product_code in ascending order and supplier_id in descending order.
8. From the l_foods table, get the price of a grilled steak. Show price and description in your results.
10. List the foods costing less than $2.00 that don’t have a price increase scheduled. Show the description and price of each food. Sort the rows by the description of the food.
11. List the all the foods supplied by all the suppliers except ASP and JRV.
12. List all the employees who have a credit limit of more than Jim Kern. (Hint: You might need two select statements for this)
Explanation / Answer
4) SELECT last_name, hire_date, phone_number, dept_code FROM employees WHERE hire_date BETWEEN 01-jan-03 AND 01-jan-2004 ORDERBY hire_date desc AND ALTER TABLE department RENAME COLUMN dept_code to department_code; 5) SELECT DISTINCT employee_id FROM l_lunches ORDERBY employee_id ASC; 8) SELECT price, description FROM l_foods WHERE description = ‘grilled steak’; 11) SELECT * FROM suppliers EXCEPT SELECT * FROM suppliers where supplier_id = ‘ASP’ OR supplier_id =’JRV’;