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
Chapter12: Subqueries And Merge Statements
Section: Chapter Questions
Problem 19MC
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](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F3ed82f9e-237a-4a6c-a695-7db3dc4090e0%2Fe2b76b45-1181-4e25-b1d7-35f889f69ae8%2F3ocq92_processed.png&w=3840&q=75)
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
![](/static/compass_v2/shared-icons/check-mark.png)
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 4 steps
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
Knowledge Booster
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.Recommended textbooks for you
![Oracle 12c: SQL](https://www.bartleby.com/isbn_cover_images/9781305251038/9781305251038_smallCoverImage.gif)
![Database Systems: Design, Implementation, & Manag…](https://www.bartleby.com/isbn_cover_images/9781285196145/9781285196145_smallCoverImage.gif)
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
![Programming with Microsoft Visual Basic 2017](https://www.bartleby.com/isbn_cover_images/9781337102124/9781337102124_smallCoverImage.gif)
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:
9781337102124
Author:
Diane Zak
Publisher:
Cengage Learning
![Oracle 12c: SQL](https://www.bartleby.com/isbn_cover_images/9781305251038/9781305251038_smallCoverImage.gif)
![Database Systems: Design, Implementation, & Manag…](https://www.bartleby.com/isbn_cover_images/9781285196145/9781285196145_smallCoverImage.gif)
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
![Programming with Microsoft Visual Basic 2017](https://www.bartleby.com/isbn_cover_images/9781337102124/9781337102124_smallCoverImage.gif)
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:
9781337102124
Author:
Diane Zak
Publisher:
Cengage Learning
![A Guide to SQL](https://www.bartleby.com/isbn_cover_images/9781111527273/9781111527273_smallCoverImage.gif)
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr