SQL FILL IN THE BLANK. Thank you in advance.

Question 1

Fill in the blanks in the SQL statement below that will list the vendor number, vendor name, vendor contact name for all vendors located in Iowa. Format all vendor contact names as last name, first name (example: Bucket, Charles) and name the column contact.

SELECT vendor_id, name, ____ (last_name, , , first_name) AS ______ FROM vendors, vendor_contacts WHERE _______ = vendor_contacts.contact_id AND vendor_state = IA;

Question 2

Fill in the blanks in the SQL statement below that will list all vendor names for vendors with a name that begins with a letter from A to L.

SELECT name FROM vendors WHERE ______ BETWEEN A AND _______ ;

Question 3

Fill in the blanks in the SQL statement below that will list the invoice_id, invoice_total and payment_total that were due with 25 days of the invoice date.

SELECT invoice_id, invoice_total , payment_total FROM invoices WHERE invoice_due_date <= _______ ;

Question 4

Fill in the blanks in the SQL statement below that will list the invoice number, vendor contact initials (first letter of first name and first letter of second name example: M.D.) and the invoice date for vendors that have not paid a payment on the invoice.

SELECT invoice_number, CONCAT( _______ , .,________ , . ) AS initials, invoice_date FROM vendor, vendor_contacts, invoices WHERE invoices.vendor_id = vendors.vendor_id AND vendors.contact_id = vendor_contacts. contact_id AND payment_total = 0.0;

Question 5

Fill in the blanks in the SQL statement below that will list the invoice number, vendor name and the invoice date for vendors that have not paid a payment on the invoice. Format the invoice date as DD-Mon-YYYY (example: 28-Mar-2017).

SELECT invoice_number, name, _______ FROM vendors, invoices WHERE invoices.vendor_id = vendors. vendor_id AND payment_total = 0.0;

Question 6

Fill in the blanks in the SQL statement below that will list the invoice_id, invoice_total and the invoice_date for invoices with amounts over $1000. Format the invoice date as DD/MM/YYYY (example: 3/28/2017). Round invoice amount to one decimal place.

SELECT invoice_id, ________ , DATE_FORMAT(invoice_date, _________ ) FROM invoices WHERE invoice_total > 1000;

Question 7

Fill in the blanks in the SQL statement below that will list the vendor name where the vendors address2 would be NULL.

SELECT name FROM vendors WHERE address2 ________;

Question 8

Fill in the blanks in the SQL statement below that will list the vendor contacts whose last name begins with the letter A, B, C or D.

SELECT first_name, last_name FROM vendor_contacts WHERE _______ IN (A, B, C, D);

Question 9

Fill in the blanks in the SQL statement below that will list the rows with an invoice_total that is greater than or equal to 500 and less than or equal to 1000. Sort in descending sequence by invoice total. Do not return duplicate rows. SELECT ________ * FROM invoices WHERE invoice_total BETWEEN 500 AND 1000 ORDER BY invoice_total DESC;

Question 10

Fill in the blanks in the SQL statement below that will list the invoice number, invoice total and credit which is the total sum of the payment_total and credit_total. Format the payment credit to 2 decimal places.

SELECT invoice_number, invoice_total, ________ AS credit FROM invoices;

Question 11

Fill in the blanks in the SQL statement below that will list the invoice_id, vendor name, invoice_due_date and an extended date of 20 days for all invoices with an invoice_total greater than 3000. Name the new field extended due date.

SELECT invoice_id, name, invoice_due_date, DATE_ADD(invoice_due_date, ____________) AS extended_due_date FROM invoices, vendors WHERE invoices.invoice_id = vendors.invoice_id AND invoice_total > 3000 ;

Question 12

Fill in the blanks in the SQL statement below that will list vendor name, sum of the invoice_total for that vendor. Include a grand total for all vendors at the end.

SELECT vendor_id, name, SUM( ________ ) FROM vendors, invoices WHERE vendors_invoice_id = invoices_invoice_id GROUP BY vendor_id ________ ;

Question 13

Fill in the blanks in the SQL statement below that will list invoice_number, vendor name, invoice_total. Use the alias i for invoices and the alias v for vendors.

SELECT invoice_number, name, invoice_total FROM _______ , ________ WHERE _________ .invoice_id = _________.invoice_id ;

Question 14

Fill in the blanks in the SQL statement below that will create a view named vendor_invoices which lists invoice_number, vendor name, invoice_date and invoice_total for all invoices.

CREATE VIEW ________ AS SELECT invoice_number, name, invoice_date, invoice_total FROM invoices , _________ WHERE _______ .invoice_id = ________.invoice_id ;

Question 15

Fill in the blanks in the SQL statement below that will create a view named summary which lists the vendor name along with the number of invoices for that vendor for all vendors.

CREATE VIEW summary _________ SELECT name, __________ FROM invoices , ________ WHERE invoice.invoice_id = _________.invoice_id ;

Question 16

Fill in the blanks in the SQL statement below to change the name in the vendors table a character field that is 60 characters in length. ____ vendors ______ name ______ ;