CIS250 Unit 2 Assignment 2 Directions Perform the tasks below by using the corre
ID: 3624616 • Letter: C
Question
CIS250 Unit 2 Assignment 2Directions
Perform the tasks below by using the correct SQL statements. 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 the assignment document. Upload this Word document for grading.
Scenario:
The suppliers want access to our database to enter their own prices. Although they are worried that other suppliers can see their prices, we have assured them that this is not possible.
2. Use the data dictionary to get the SELECT statement for the view you just created. Write that SELECT statement below (DO NOT Test this statement in Oracle):
5. Attempt to insert the following two records into the view. Were you successful, why or why not?
Supplier_id product_code menu_item description price price_increase
ASP GG 22 GRILLED GARDEN 6 7
CBC GS 23 GRILLED SALAD 6 7
6. What is a preventative delete?
7. Create a new table using the following column structure from the l_foods table: supplier_id, product_code, menu_item, description and price. Call the table l_foods_lt5. Populate the l_foods_lt5 table to include all the foods that cost less than $5.00.
11. List the employees hired before 2002. Show the employee_id, first_name, last_name, and hire_date columns. Bring the last_name and first_name back in last_name, first_name format. Call this column full name. Sort the rows by employer_id.
12. From the l_foods table, list the description and price values of the following foods: hamburger, french fries, and soda. Sort the rows by description.
13. From the l_foods table, list the foods that have “fresh” in their descriptions. Sort these rows in ascending order.
14. List the employees who have a manager. Show the employee_id , first_name, and last_name. Sort the rows by employee_id.
Explanation / Answer
7)
CREATE table l_foods_lt5 AS (SELECT supplier_id, product_code, menu_item, description, price FROM l_foods WHERE price < $5.00)
11)
SELECT employee_id, hire_date, CONCAT(first_name,last_name) AS fullname FROM Employees
WHERE hire_date < ’01-Jan-02’ORDERBY employee_id;
12)
SELECT description, price FROM l_foods WHERE description = ‘hamburger’ OR description = ‘french fries’ OR description = ‘soda’ ORDERBY description;
13)
SELECT * FROM l_foods WHERE description LIKE’%fresh%’ ORDERBY description ASC;