Part 03 Write a select statement that selects the second highest invoice_total.

Question

Part 03

Write a select statement that selects the second highest invoice_total.

Part 04

Now let’s create a view:

1. Create a view that holds information (name and phone numbet) for bad payers i.e. the payers that have a payment date that is later (larger) than the invoice due date or have a payment date that is null. Include also the invoice_id.

2. Use the view: write a select statement to find the bad payers whose name starts with ‘B’.

Part 05

In this part we create a small procedure:

1. Write a procedure that has as input the customer_id. Then the procedure should delete the corresponding customer from table customers.

2. Notice that customer_id is a foreign key on table orders. Therefore the customer cannot be deleted if he has placed orders. To delete a record from customers you should first delete the corresponding orders and to do that you need to first delete the corresponding order_details.

3. Now execute the procedure to delete customer with id 1. If you have solved part 01 a record will be inserted into Customer_Archives.

4. ROLLBACK; and all these changes will be undone!

Part 06

Use the metadata tables for users to count how many tables and how many indexes you have created in your database.

Part 03 Write a select statement that selects the second highest invoice total. Part 04 Now let's create a view 1. Create a view that holds information (name and phone numbet) for bad payers i.e. the payers that have a payment date that is later (larger) than the invoice due date or have a payment date that is null. Include also the invoice_id 2. Use the view: write a select statement to find the bad payers whose name starts with 'B Part 05 In this part we create a small procedure: 1.Write a procedure that has as input the customer id. Then the procedure should delete the corresponding customer from table customers 2. Notice that customer id is a foreign key on table orders. Therefore the customer cannot be deleted if he has placed orders. To delete a record from customers you should first delete the corresponding orders and to do that you need to first delete the corresponding order_details. 3. Now execute the procedure to delete customer with id 1. If you have solved part 01 a record will be inserted into Customer Archives. 4. ROLLBACK; and all these changes will be undone! Part 06 Use the metadata tables for users to count how many tables and how many indexes you have created in your database

Explanation / Answer

Solution:

The first question is done as per Chegg guidelines, please repost others.  

Explanation:

The first max of the invoice total is selected from the query and after that in the where condition a subquery is placed to extract the value of second highest invoice total.

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---