Question

Directions
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 > EnterCommand). 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 thatthis is not possible.

Complete the following by creating the appropriate SQL statements:
1. Create a view so that supplier ‘ASP’ can only see his information from the l_foods table. Call this view_l_foods_ASP. Show columns supplier_id, product_code,menu_item, description, and price in your view. Make sure that this CREATE view statement can be run repeatedly. Also ensure that supplier ‘ASP’ can only change hisown data in the database.

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):


3. Fresh salad has a new price of $3.00. Update the data through the view_l_foods_ASP. Do you see the change in view_l_foods_ASP?

4. Select all the columns in the l_foods table which have a supplier_id of ‘ASP’ and a price of greater than $2.50. Do you see the price change that you entered inview_l_foods_ASP?



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 tablel_foods_lt5. Populate the l_foods_lt5 table to include all the foods that cost less than $5.00.

8. Supplier ASP has gone out of business. Delete all the products for supplier ASP from your l_foods_lt5 table. Make sure your changes save.

9. Remove all the entries in the your l_foods_lt5 table, but not the table itself.

10. Delete the l_foods_lt5 table itself.


Review Questions


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.
0 0
Add a comment Improve this question Transcribed image text
Request Professional Answer

Request Answer!

We need at least 10 more requests to produce the answer.

0 / 10 have requested this problem solution

The more requests, the faster the answer.

Request! (Login Required)


All students who have requested the answer will be notified once they are available.
Know the answer?
Add Answer to:
Assignment 2
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Similar Homework Help Questions
  • unit 1

    DirectionsYou 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 yourstatements into this document. Upload this Word document for grading.1. Write the SQL statement you would use to find out all the tables that you have access to.SELECT table_name FROM user_tables;2. Write the command to list all the columns and rows of the l_foods table.3. Write the command to list all the columns...

  • SQL Fundamentals

    Unit 2 Assignment 27. 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 tablel_foods_lt5. Populate the l_foods_lt5 table to include all the foods that cost less than $5.00.Unit 3 Assignment 13. Find the employee_id, first_name, last_name and number of days that everyone has been working with the company from the l_employees table. If someone doesn’t have ahire_date assume it was the first of this year.4. Create a new...

  • SQL Fundamentals 3rd ed. by John J. Patrick (repost)

    Note: Tables included, and answers (1, 2, 3, 4, 5, 6, 8,9,10, and 11) have been provided. I just need the remaining answered 7.Thanks for the helpUnit 4 Assignment 2DirectionsPerform the following tasks below by using the correct SQL statements. Create the statements in Oracle by using the following steps (SQL > SQL commands > EnterCommand). 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...

  • SQL Fundamentals 3rd ed. by John J. Patrick (repost request shirlee )

    Note: Questions 1,3,4,5,6,7 &8 have been answered. (need answer to 2) (request Edan, or Shirlee)Thanks tables included at the bottomUnit 5 Assignment 1DirectionsPerform 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.1. List all the employees in the emp table. If they work...

  • SQL Fundamentals 3rd ed. by John J. Patrick (repost)

    Note:I have included the table along with some answers (1,2,3,6,7,8,9,10,11,12,13, and 14) to some of the questions. I need answers to (4,5, and 15). Thanks remember tablesare included at the bottom of this post!Unit 4 Assignment 1Directions:Perform the following tasks below by using the correct SQL statements. Create the statements in Oracle by using the following steps (SQL > SQL commands > EnterCommand). Once your command is error-free, copy and paste your statements into this assignment document. Upload this Word...

  • SQL CIS250 unit 4 assignment 1

    Can someone help me please! This is the feedback I received from my instructor (in red letters) after attempting this assignment and need help in correcting thesestatements so they will run correctly. Thank you very much in advance!!!Unit 4 Assignment 1DirectionsPerform 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...

  • SQL CIS250 Unit 4 Assignment 1

    Here are the remaining questions that I need help with please. I am providing the table and data as well. Thanks a million for all the help!DirectionsPerform the following tasks below by using the correct SQL statements. Create the statements in Oracle by using the following steps (SQL > SQL commands > EnterCommand). 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...

  • rider_student Column Data Type Description student_id integer the primary key first_name varchar(25) student first name last_name...

    rider_student Column Data Type Description student_id integer the primary key first_name varchar(25) student first name last_name varchar(25) student last name major_id integer the ID of the student's major; a foreign key for the major_id in the rider_major table rider_major Column Data Type Description major_id integer the primary key major_name varchar(50) student first name major_description varchar(100) student last name Use the Tables above to answer the questions. Questions: 1. Write a SQL statement to add a student record to the rider_student...

  • 1.2) (5 Points) For each of the following statements, mark T (True) or F (False) SELECT...

    1.2) (5 Points) For each of the following statements, mark T (True) or F (False) SELECT DISTINCT is used if a user does not want to see duplicate rows in a query. T F TF Consider the following SQL statement: SELECT pred price * pred quant as total cost FROM Product; The output of this query is a table with two columns. You can create a View if you do not want users to see all the attributes of a...

  • SQL problem solving help; First 3 tables are finnished; Need help understanding on how to do...

    SQL problem solving help; First 3 tables are finnished; Need help understanding on how to do steps after. First 3 tables are after the first 2 images for reference if needed. //1// CREATE TABLE kr_customer ( Name VARCHAR(40) NOT NULL PRIMARY KEY, City VARCHAR(20), Status CHAR(1) ); //2// CREATE TABLE kr_salesperson ( Name VARCHAR(40) NOT NULL PRIMARY KEY, Age INT, Salary DECIMAL(8, 2) ); //3// CREATE TABLE kr_order ( Order_Number number(3) NOT NULL PRIMARY KEY, Customer_Name VARCHAR(40), Salesperson_Name VARCHAR(40), Amount...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT