Reference no: EM13315029
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.
Complete the following by creating the appropriate SQL statements:
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 his own data in the database.
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):
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?
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 in view_l_foods_ASP?
Attempt to insert the following two records into the view. Were you successful, why or why not?
Supplier_idproduct_codemenu_item description price price_increase
ASP GG 22 GRILLED GARDEN 6 7
CBC GS 23 GRILLED SALAD 6 7
What is a preventative delete?
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.
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.
Remove all the entries in the your l_foods_lt5 table, but not the table itself.
Delete the l_foods_lt5 table itself.
Review Questions
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
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.
From the l_foods table, list the foods that have "fresh" in their descriptions. Sort these rows in ascending order.
List the employees who have a manager. Show the employee_id ,first_name, and last_name. Sort the rows by employee_id.
Attachment:- Assignment1.docx
What is the moment of inertia of the wheel
: A string is wrapped tightly around the circumference of a wheel with a mass of 1.23 kg and a radius of 0.324 m. What is the moment of inertia of the wheel
|
What is the greatest potential difference across capacitor
: An LC circuit consists of a 20 mh inductor and a 0.5 if capacitor.maximum instantaneous current is 0.1 A
|
Calculate the inductive reactance
: In a purely iductive AC circuit L=25.0mH and the rms voltage is 150V. Calculate the inductive reactance, max and rms current in the circuit
|
Price-discrimination monopolistic
: Suppose that a price-discrimination monopolistic has segregated its marker into two group of buyers. The first group is described by the demand and revenue data that you developed for developed for problem 1
|
Perform the tasks below by using the correct sql statements
: 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.
|
What is the formula for impedence for this circuit
: resistance R, capacitance C, and inductance L are connected in series, what is the formula for impedence for this circuit
|
Determine the magnitude of the resultant of the two forces
: Obstruction due to underground electric lines requires that the x coordinate location of point C must be three feet as shown in the figure, as opposed to 1.5 ft for point B. Determine the magnitude of the resultant of the two forces
|
Determine the volume of water in gallons added to the tank
: a water main is used to refill an elevated storage tank during the night. a 100 ft long, 12 in steel pipe (ks=.06 mm) connects to the booster pump that provides a pressure of 75 psig at the pump outlet
|
Find the electric field due to this rod
: A rod of length L carries a uniform linear charge density of 5 nC/m. find the electric field due to this rod at a distance of 20cm
|