Hint: If any string value you need to specify has an apostrophe in it, put in tw

Question

Hint: If any string value you need to specify has an apostrophe in it, put in two apostrophes where the one would be. This is because apostrophes are how strings are started and finished and the computer needs to be told that the apostrophe is not the end of the string, but part of the string. For example, in the first question, if your last name is O 'Malley, the value in your statement would be communicated as: 'O ' 'Malley '

Hint: If for any of the following questions you are instructed to provide no value, then you can either leave the column out of the statement or explicitly state NULL for that column’s value. Noting that NULL never goes inside apostrophes.

1.)            Write a single, simple INSERT statement for the employees table that:

·       Adds one row with an employee_id of 10, a department_number of 5, your last name, your first name, and no manager_id

2.)            Write a single, simple INSERT statement for the invoices table that:

·       Adds one row with a vendor_id of 123, an invoice_number of 5150_2112, an invoice_date of today’s date, an invoice total of 555.66, a payment_total of 0.0, a credit_total of 0.0, a terms_id of 3, an invoice_due_date of 30 days after today’s date, and no payment_date.

·       Note that this table also has an invoice_id column, but you don’t want to specify a value for that column. It will be automatically generated for you.

3.)            Write a single, simple INSERT statement for the items table that:

·       Adds one row with an item_id of 11, a title of 'Balance', an artist of 'Van Halen', and a unit_price of 9.99.

4.)            Write a single, simple INSERT statement for the order_details table that:

·       Adds one row with an order_id of 829, an item_id of 11, and an order_qty of 5.

For the remaining questions, you will always need a WHERE clause. You can create any WHERE clause you want that achieves the stated objective. However, I would suggest paying attention to each table’s main ‘id’ column and use that in your WHERE clause. For example, the WHERE clause to change or remove a record from the employee table with an employee_id of 13 would be:

WHERE employee_id = 13

5.)            Write a single, simple UPDATE statement for the employees table that:

·       For the row you added in question 1, change the department_number to 2.

6.)            Write a single, simple UPDATE statement for the invoices table that:

·       For the row you added in question 2, change the payment_total to 555.66 and the payment_date to today’s date.

7.)            Write a single, simple UPDATE statement for the items table that:

·       For the row you added in question 3, change the unit_price of to 8.99. (It’s a great album and it’s on sale!)

Hint: For question 8, you will need a WHERE clause with two conditions. order_id 829 has several items on it and we only want to change the row for item_id 11.

8.)            Write a single, simple UPDATE statement for the order_details table that:

·       Since the album is on sale, why don’t we order 2 more. For the row you added in question 4, change the order_qty to 7.

9.)            Write a single, simple DELETE statement for the employees table that:

·       Removes the row you added in question 1.

10.)        Write a single, simple DELETE statement for the invoices table that:

·       Removes the row you added in question 2.

11.)        Write a single, simple DELETE statement for the items table that:

·       Removes the row you added in question 3.

Hint: For question 12, you will need a WHERE clause with two conditions. order_id 829 has several items on it and we only want to remove the row for item_id 11.

12.)        Write a single, simple DELETE statement for the order_details table that:

·       Removes the row you added in question 4.

Explanation / Answer

Hi,

Please note the required scripts below (I'm being asked to answer first 4 queries only by Chegg - happy to answer rest in another question if asked) :

1. INSERT employees (employee_id,department_number, last name, first name, manager_id)  
VALUES (10, 5 , 'Expert' , 'Chegg', NULL)
GO  

2. INSERT invoices (vendor_id ,invoice_number, invoice_date , invoice total, payment_total ,credit_total ,terms_id, invoice_due_date ,payment_date)
VALUES (123, '5150_2112' , SELECT CONVERT(varchar, getdate(), 23) , 555.66, 0.0, 0.0, 3 , SELECT CONVERT(varchar, getdate() + 30, 23), NULL)

GO  

3. INSERT items (item_id ,title , artist, unit_price )
VALUES (11, 'Balance' , 'Van Halen' , 9.99)
GO  

4. INSERT order_details (order_id ,item_id , order_qty )
VALUES (829, 11, 5)
GO  

5. UPDATE employees

SET department_number = 2
Where employee_id = 10 and department_number = 5

6. UPDATE invoices
SET payment_total = 555.66, payment_date = SELECT CONVERT(varchar, getdate(), 23)
Where vendor_id = 123 and invoice_number = '5150_2112'

7. UPDATE items
SET unit_price = 8.99
WHERE item_id = 11 and title = 'Balance'

8. UPDATE order_details
SET order_qty = 7
WHERE order_id = 829 and item_id = 11

9. DELETE FROM employees
Where employee_id = 10 and department_number = 2

10. DELETE FROM invoices
WHERE vendor_id = 123 and invoice_number = '5150_2112'

11. DELETE FROM items
WHERE item_id = 11 and title = 'Balance'

12. DELETE FROM order_details
WHERE order_id = 829 and item_id = 11

Happy Learning! :)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.