SQL Help 4: SQL Help | Computer Science

SQL Help Lesson4SQLStatementsDirections.docx The following are required for this lesson: Read Murach Chapter 4: How to retrieve data from two or more tables. Read and study the material presented in the online lesson. Exercise 1: Write your SQL statement in a file named CIS276DA_Lesson4Exercise1_MEID.sql. Run the script and export the results to CIS276DA_Lesson4Exercise1_MEID.csv. Exercise 2: Write your SQL statement in a file named CIS276DA_Lesson4Exercise2_MEID.sql. Run the script and export the results to CIS276DA_Lesson4Exercise2_MEID.csv. Exercise 3: Write your SQL statement in a file named CIS276DA_Lesson4Exercise3_MEID.sql. Run the script and export the results to CIS276DA_Lesson4Exercise3_MEID.csv. Exercise 4: Write your SQL statement in a file named CIS276DA_Lesson4Exercise4_MEID.sql. Run the script and export the results to CIS276DA_Lesson4Exercise4_MEID.csv. Exercise 5: Write your SQL statement in a file named CIS276DA_Lesson4Exercise5_MEID.sql. Run the script and export the results to CIS276DA_Lesson4Exercise5_MEID.csv. Exercise 6: Write your SQL statement in a file named CIS276DA_Lesson4Exercise6_MEID.sql. Run the script and export the results to CIS276DA_Lesson4Exercise6_MEID.csv. Exercise 7: Write your SQL statement in a file named CIS276DA_Lesson4Exercise7_MEID.sql. Run the script and export the results to CIS276DA_Lesson4Exercise7_MEID.csv. Submission Instructions Place all your SQL files (7) and CSV files (7) in a folder labeled "Lesson 4". Replace "MEID" in the file names with your actual MEID. Zip the entire "Lesson 4" folder as described in the Syllabus. Submit the zipped assignment folder using the link below. Lesson4SQLQuestions.docx 1. Write a SELECT statement that returns all columns from the Vendors table inner-joined with all columns from the Invoices table. This should return 114 rows. Hint: You can use an asterisk (*) to select the columns from both tables. 2. Write a SELECT statement that returns these four columns: vendor_name invoice_number invoice_date balance_due The vendor_name column from the Vendors table The invoice_number column from the Invoices table The invoice_date column from the Invoices table The invoice_total column minus the payment_total and credit_total columns from the Invoices table Use these aliases for the tables: v for Vendors and i for Invoices. Return one row for each invoice with a non-zero balance. This should return 11 rows. Sort the result set by vendor_name in ascending order. 3. Write a SELECT statement that returns these three columns: vendor_name default_account description The vendor_name column from the Vendors table The default_account_number column from the Vendors table The account_description column from the General_Ledger_Accounts table Return one row for each vendor. This should return 122 rows. Sort the result set by account_description and then by vendor_name. 4. Write a SELECT statement that returns these five columns: vendor_name invoice_date invoice_number li_sequence li_amount The vendor_name column from the Vendors table The invoice_date column from the Invoices table The invoice_number column from the Invoices table The invoice_sequence column from the Invoice_Line_Items table The line_item_amount column from the Invoice_Line_Items table 5. Write a SELECT statement that returns three columns: vendor_id vendor_name The vendor_id column from the Vendors table The vendor_name column from the Vendors table contact_name A concatenation of the vendor_contact_first_name and vendor_contact_last_name columns with a space between Return one row for each vendor whose contact has the same last name as another vendor’s contact. This should return 2 rows. Hint: Use a self-join to check that the vendor_id columns aren’t equal but the vendor_contact_last_name columns are equal. Sort the result set by vendor_contact_last_name. 6. Write a SELECT statement that returns these three columns: account_number account_description invoice_id The account_number column from the General_Ledger_Accounts table The account_description column from the General_Ledger_Accounts table The invoice_id column from the Invoice_Line_Items table Return one row for each account number that has never been used. This should return 54 rows. Hint: Use an outer join and only return rows where the invoice_id column contains a null value. Remove the invoice_id column from the SELECT clause. Sort the final result set by the account_number column. 7. Use the UNION operator to generate a result set consisting of two columns from the Vendors table: vendor_name and vendor_state. If the vendor is in California, the vendor state_value should be “CA”; otherwise, the vendor_state value should be “Outside CA.” Sort the final result set by vendor_name.