Answered step by step
Verified Expert Solution
Question
00
1 Approved Answer
Learning Activity 6 : Insert, update, and delete data Setup: Use create _ ap . sql to create database 1 . Write SELECT INTO statements
Learning Activity : Insert, update, and delete data Setup: Use createapsql to create database Write SELECT INTO statements to create two test tables named VendorCopy and InvoiceCopy that are complete copies of the Vendors and Invoices tables. If VendorCopy and InvoiceCopy already exist, first code two DROP TABLE statements to delete them. Write an INSERT statement that adds a row to the InvoiceCopy table with the following values: VendorID: InvoiceTotal: $ TermsID: InvoiceNumber: AX PaymentTotal: $ InvoiceDueDate: InvoiceDate: CreditTotal: $ PaymentDate: null Write an UPDATE statement that modifies the VendorCopy table. Change the default account number to for each vendor that has a default account number of Write an UPDATE statement that modifies the InvoiceCopy table. Change the PaymentDate to todays date and the PaymentTotal to the balance due for each invoice with a balance due. Set todays date with a literal date string, or use the GETDATE function. Write a DELETE statement that deletes all vendors in the state of Minnesota from the VendorCopy table. Write a DELETE statement for the VendorCopy table. Delete the vendors that are located in states from which no vendor has ever sent an invoice. Hint: Use a subquery coded with SELECT DISTINCT VendorState introduced with the NOT IN operator.
Learning Activity : Insert, update, and delete data
Setup:
Use createapsql to create database
Write SELECT INTO statements to create two test tables named VendorCopy
and InvoiceCopy that are complete copies of the Vendors and Invoices
tables. If VendorCopy and InvoiceCopy already exist, first code two DROP
TABLE statements to delete them.
Write an INSERT statement that adds a row to the InvoiceCopy table with the
following values:
VendorID:
InvoiceTotal: $
TermsID:
InvoiceNumber: AX
PaymentTotal: $
InvoiceDueDate:
InvoiceDate:
CreditTotal: $
PaymentDate: null
Write an UPDATE statement that modifies the VendorCopy table. Change the
default account number to for each vendor that has a default account
number of
Write an UPDATE statement that modifies the InvoiceCopy table. Change the
PaymentDate to todays date and the PaymentTotal to the balance due for
each invoice with a balance due. Set todays date with a literal date string, or
use the GETDATE function.
Write a DELETE statement that deletes all vendors in the state of Minnesota
from the VendorCopy table.
Write a DELETE statement for the VendorCopy table. Delete the vendors that
are located in states from which no vendor has ever sent an invoice.
Hint: Use a subquery coded with SELECT DISTINCT VendorState introduced
with the NOT IN operator.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started