Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
1. Write a SQL statement to insert the following record in the table LGEMPLOYEE emp_email J_HERNANDE Z@LGCOMPA NY.COM emp_phone 810-7718 emp_num 85600 emp_fname JENNIFER emp_Iname HERNANDEZ emp_hiredate 2019-12-1 2. Write a query to change the email in the record you just inserted to J.HERNANDEZ@LGCOMPANY.COM 3. Write a query to delete the brand "HOME COMFORT" from the table Igbrand emp_title emp_comm SPECIALIST NULL dept_num 250

1. Write a SQL statement to insert the following record in the table LGEMPLOYEE emp_email J_HERNANDE Z@LGCOMPA NY.COM emp_phone 810-7718 emp_num 85600 emp_fname JENNIFER emp_Iname HERNANDEZ emp_hiredate 2019-12-1 2. Write a query to change the email in the record you just inserted to J.HERNANDEZ@LGCOMPANY.COM 3. Write a query to delete the brand "HOME COMFORT" from the table Igbrand emp_title emp_comm SPECIALIST NULL dept_num 250

Oracle 12c: SQL
Oracle 12c: SQL
3rd Edition
ISBN:9781305251038
Author:Joan Casteel
Publisher:Joan Casteel
Chapter12: Subqueries And Merge Statements
Section: Chapter Questions
Problem 19MC
See similar textbooks
icon
Related questions
Question

Please see image for information SQL Code: https://gist.githubusercontent.com/GistMasterPro/89cad4b0f93db12893c238b40d1d1d94/raw/707e8a95b879a58e4eafe21672bae0e1358975eb/sqlcode.sql

1. Write a SQL statement to insert the following record in the table LGEMPLOYEE emp_email J_HERNANDE Z@LGCOMPA NY.COM emp_phone 810-7718 emp_num 85600 emp_fname JENNIFER emp_Iname HERNANDEZ prod_sku 2366-EFV 8841-JFP 5437-WBO 5602-QBF 7258-HCV 5161-GIZ 4784-SLU emp_hiredate 2019-12-1 2. Write a query to change the email in the record you just inserted to J.HERNANDEZ@LGCOMPANY.COM 3. Write a query to delete the brand "HOME COMFORT" from the table Igbrand 4. Create a view that displays all records from the table Igsalary_history from 2013 or later. Name the view recent_salaries Varnish, Interior, Polyurethane, Oil Modified, Gloss Varnish, Interior, Polyurethane, Oil Modified, Satin Fire Retardant Sealer, Alkyd, Interior (ULC Approved) Polyurethane, Two-Component, Pigmented, Gloss (MPI Gloss Level 6) Primer, Alkyd, Anti-Corrosive for Metal Lacquer, Sanding Sealer, Clear Lacquer, Clear, Flat emp title SPECIALIST NULL 32 15 14 37 15 5. Write the view prod_to_order that displays the products that need to be reordered. Display the product SKU, description and a calculated field named "q_to_order" from the table Igproduct. List only the products that need to be ordered (where the minimum quantity is greater than the quantity on hand). The calculated field will show the difference between the minimum quantity and the quantity on hand plus 10 - the quantity that needs to be ordered. For example, if prod_min=7, prod_qoh=5, then prod_to_order = 10 + (7-5) = 12 prod_descript q_to_order 23 11 emp_comm dept_num 250 6. Assume that you refilled the products from step 5. Write a query that updates the products where the minimum quantity is greater than the quantity on hand using the same formula: the new quantity on hand is the difference between the minimal quantity and the current quantity plus 10. For example, is prod_qoh = 3 and prod_min=25, the query should record 25-3+10= 32 as the new quantity on hand. Note: use the formula to update all records at once (do not write a separate query to update each record). Apply the changes only to the product where the quantity on hand is lower than the minimum required
Transcribed Image Text:1. Write a SQL statement to insert the following record in the table LGEMPLOYEE emp_email J_HERNANDE Z@LGCOMPA NY.COM emp_phone 810-7718 emp_num 85600 emp_fname JENNIFER emp_Iname HERNANDEZ prod_sku 2366-EFV 8841-JFP 5437-WBO 5602-QBF 7258-HCV 5161-GIZ 4784-SLU emp_hiredate 2019-12-1 2. Write a query to change the email in the record you just inserted to J.HERNANDEZ@LGCOMPANY.COM 3. Write a query to delete the brand "HOME COMFORT" from the table Igbrand 4. Create a view that displays all records from the table Igsalary_history from 2013 or later. Name the view recent_salaries Varnish, Interior, Polyurethane, Oil Modified, Gloss Varnish, Interior, Polyurethane, Oil Modified, Satin Fire Retardant Sealer, Alkyd, Interior (ULC Approved) Polyurethane, Two-Component, Pigmented, Gloss (MPI Gloss Level 6) Primer, Alkyd, Anti-Corrosive for Metal Lacquer, Sanding Sealer, Clear Lacquer, Clear, Flat emp title SPECIALIST NULL 32 15 14 37 15 5. Write the view prod_to_order that displays the products that need to be reordered. Display the product SKU, description and a calculated field named "q_to_order" from the table Igproduct. List only the products that need to be ordered (where the minimum quantity is greater than the quantity on hand). The calculated field will show the difference between the minimum quantity and the quantity on hand plus 10 - the quantity that needs to be ordered. For example, if prod_min=7, prod_qoh=5, then prod_to_order = 10 + (7-5) = 12 prod_descript q_to_order 23 11 emp_comm dept_num 250 6. Assume that you refilled the products from step 5. Write a query that updates the products where the minimum quantity is greater than the quantity on hand using the same formula: the new quantity on hand is the difference between the minimal quantity and the current quantity plus 10. For example, is prod_qoh = 3 and prod_min=25, the query should record 25-3+10= 32 as the new quantity on hand. Note: use the formula to update all records at once (do not write a separate query to update each record). Apply the changes only to the product where the quantity on hand is lower than the minimum required
Expert Solution
steps

Step by step

Solved in 4 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
SQL Query
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Oracle 12c: SQL
Oracle 12c: SQL
Computer Science
ISBN:
9781305251038
Author:
Joan Casteel
Publisher:
Cengage Learning
SEE MORE TEXTBOOKS