H

Complete exercise # 3 through 7. I have question 1,2,3 and 8 complete. see attached

pic 3 should help. SQL 2017 AP DATABASE FILE

Chapter 4 How to retrieve data from two or more tables Generate the same result set described in exercise 2, but use the implicit join syntax. ,. Write a SELECT statement that returns five columns from three tables, all using column aliases: Vendor Date Number VendorName column InvoiceDate column InvoiceNumber column InvoiceSequence column Lineltem InvoiceLineltemAmount column Assign the following correlation names to the tables: v Vendors table i Invoices table li InvoiceLineltems table Sort the final result set by Vendor, Date, Number, and #. Write a SELECT statement that returns three columns: 6. VendorID VendorName Name From the Vendors table From the Vendors table A concatenation of VendorContactFName and VendorContactLName, with a space in between The result set should have one row for each vendor whose contact has the same first name as another vendor's contact. Sort the final result set by Name. Hint: Use a self-join. Write a SELECT statement that returns two columns from the GLAccounts table: AccountNo and AccountDescription. The result set should have one row for each account number that has never been used. Sort the final result set by AccountNo 7. Hint: Use an outer join to the InvoiceLineltems table. Use the UNION operator to generate a result set consisting of two columns from the Vendors table: VendorName and VendorState. If the vendor is in California, the VendorState value should be "CA"; otherwise, the VendorState value should be "Outside CA." Sort the final result set by VendorName. 8. select from invoices i, vendors where i.VendorID v.VendorlD -Implicit example select vendorname, invoicenumber, invoicedate, InvoiceTotal - (PaymentTotal+ as Balance from vendors v, invoices i where v.vendorid i.VendorlD and invoiceTotal-(PaymentTotal + CreditTotal) > 0 order by VendorName -explicit example select vendorname, invoicenumber, invoicedate, InvoiceTotal - (PaymentTotal + CreditTotal) as Balance from vendors join invoices on vendors.vendorid - invoices.VendorltD where InvoiceTotal - (PaymentTotal +.CreditTotal)>0 order by VendorName CreditTotal) select vendorname, vendorstate from vendors where vendors tate 'CA' union select vendorname, vendorstate from Vendors where vendorstate 'CA vsEE9F.sql-local.-54MTTSNJohnh (51)). calhost',SQLEXPRESS (SQL Server 140, 1000 Results g Messages InvoicelD VendoriD Invoice Number InvoiceDate voice Total Payment Total Credit Total TemalD InvoiceDueDate Paymert Date 547479217 2016-02-07 00:00.00 116 00 11600 00O e InvoicelD (PK, int, not n e- VendorlD (FK, int, not n 111-92R-100 2016-03-06 0000:00 39.77 111.92R-100 2016-03-15 00-0000 327 111-92R-100 2015-12-30 00:0000 16.33 CreditTotal (money, no 111-92R-100 2016-02-09 00 0000 4621 111-92R-100 2016-02-01 0000 00 19 67 124 InvoiceDueDate (srmalic 96 7271001 2015-12800000066200 6620000 21-4923721 2016-01-13 00.0000 95 Query executed successfully 16 10 13 :00 00 00 00 00 00 00 :00 :00 00 :00 00 00 00 00 00 00 00 00 00 30 02 01 09 17 12 24 54 00 00 00 92 201 201 20 201 201 20 20 52 46 50 3 15 43 89 110 69 68 95 98 47 27 87 82 83 91 13 49 53 44 12 23 16 val 123456789 10 1 2 3 4 5 6 7 8 9 20 21 22 23 24 25 26 27 28 nvoice Total Payment Total Credt Total TemaID IvceDueDa 1 Blue Cross 2 Cardinal Business Media, Inc 134116 3 Data Reproductions Corp 39104 547480102 2016-04-01 00 00 00 224 00 2016-03-28 00.00:00 90 36 2016-03-10 00 00:00 85 31 4 Federal Express Corporation 963253264 2016-03-18 00:0000 5225 5 Federal Express Corporation 263253268 2016-03-21 00:0000 59.97 6 Federal Express Corporation 263253270 2016-03-22 0000:00 67 92 7 Federal Express Corporation 263253273 2016-03-22 0000:00 3075 8 Ford Motor Credt Company 9982771 9 Ingram 2016-03-24 00:00:00 503.20 1361833 2016-03-21 00-0000 579 42 Malloy Lithographing Inc 0-2436 2016-03-31 00.00:00 10976 2016-03-23 0000:00 19351 11 Malloy Lithographing IncP-0608 1Blue Cross 2 Cardinal Business Media, Inc. 134116 3 Data Reproductions Corp 4 Federal Express Corporation 963253264 2016-03-18 00:0000 5225 5 Federal Express Corporation 263253268 2016-03-21 0000:00 5997 $ Federal Express Corporation 2632532702016-03-22 000000 72 7 Federal Express Corporation 263253273 2016-03-22 00:00:00 3075 8 Ford Motor Credit Company 9982771 9 Ingram 10 Malloy Lithographing Inc 0-2436 11 Malloy Lithographing Inc P-0608 1 Blue Cross 547480102 2016-04-01 00:00:00 224.00 2016-03-28 00:0000 90 36 2016-03-10 00:00:00 85 31 39104 2016-03-24 00 0000 503.20 1361833 2016-03-21 000000 579 42 2016-03-31 00 00 00 10976 2016-03-23 00:00:00 19351 1 Abbey Office Funishings CA 2 American Booksellers Assoc NY Debug Tools Window Help Execute Debug EE dbo.lnvoices Columns 100 % InvoicelD (PK, int, not null) InvoicelD Ve VendorlD (FK, int, not null) invoiceNumber (varchar(50), not null) invoiceDate (smalldatetime, not null) invoiceTotal (money, not null) PaymentTotal (money, not null) CreditTotal (money, not null) 1 Blue Cross 2 Cardinal Busines 3 Data Reproduct 4 Federal Express 5 Federal Express 6 Federal Express TermsID (FK, int, not null) invoiceDueDate (smalldatetime, not null) PaymentDate (smalldatetime, null) Keys vendomame 1 Blue Cross 2 Cardinal Busines 3 Data Reproducti 4 Federal Express 5 Federal Express 6 Federal Express 7 Federal ExpressC Constraints Triggers til Indexes 1Statistics dbo.Orderlineltems dbo.Orders dboProducts dboShippers dbo.Terms dboTracking 8 Ford Motor Credt 9 Ingram 10 Malloy Lthograph 11 Malloy Lthograph dbo.TrackingTable E dboVendors Columns o VendorlD (PK, int, not null) B VendorName (varchar(50), not null VendorAddress1 (varchar(50), null) vendorAddress2 (varchar(50), null) VendorCity (varchar(50), not nul) VendorState (char(2), not null) VendorZipCode (varchar(20), not null) vendorphone (varchar(50), null) 1 Abbey Office Fun Query