Type up the scripts for the following questions as a script file or a document a

Question

Type up the scripts for the following questions as a script file or a document and upload it to ANGEL. 1. Write a CREATE VIEW statement that defines a view named InvoiceBasic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P. 2. Create a view named Top10Paidlnvoices that returns three columns for each vendor: VendorName, LastInvoice (the most recent invoice date), and SumOflnvoices (the sum of the InvoiceTotal column) Return only the 10 vendors with the largest SumOflnvoices and include only paid invoices 3. Create an updatable view named VendorAddress that returns the VendorID, both address columns, and the city, state, and zip code columns for each vendor. Then, write a SELECT query to examine the result set where VendorID=4. Next, write an UPDATE statement that changes the address so that the suite number (Ste 260) is Stored in VendorAddress2 rather than in VendorAddress! (Make sure to rerun your SELECT query in order to verify the change) 4. Create a view named AccountBy Vendor that returns the sum of InvoiceLineltemAmounts in the InvoiceLineItems table, grouped by VendorName and AccountDescription. 5. Modify the InvoiceBasic view treated in exercise I to sort the result set by VendorName.

Explanation / Answer

CREATE VIEW InvoiceBasic AS
SELECT VendorName,InvoiceNumber,InvoiceTotal
FROM Invoice ;--your table name here

select *from InvoiceBasic where VendorName like N% or VendorName like O% or vendorName like P% order by VendorName

sql>@InvoiceBasic

create view Top10PaidInvoices as
select VendorName,LastInvoice,sum(Invoices) as sumOfInvoices --write your attribute name.
from Invoice where rownum<=10 and sumOfInvoice=max(sumOfInvoices);

create view VendorAddress as
select VendorID,address1,address2,city,state,zip from Vendor--replace with your table names;

select *from VendorAddress where VendorID=4;
upadate VendorAddress set address2=ste260;
select address2 from VendorAddress;

create view AccountByVendor as
select sum(invoiceLineItemAmmounts),VendorName,AccountDescripter from InvoiceLineItems group by VendorName and AccountDescripter;

alter view InvoiceBasic as
select *from invoice order by VendorName

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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