Need help with database query problems!
1. A. Write a query that returns the vendor name and state of each vendor but for only those vendors who live in a city named 'Fairfield B. Repeat the same query but add the requirement that the vendor is not from state NJ 2. Write a query that returns the invoice_number and vendor id of all invoices. Now calculate the amount owed by subtracting the Payment_total from the Invoice_total. Name this new field Remaining_Total. 3. Write a query that returns the number of all items ever ordered (you need to add all the entries of the Order_Qty field). but only include the items with Id smaller than 6. 4. For each order, find the date of the order, but report the only the month and year in the form: Month - 20XX. Also report the number of days between order date and shipped date? 5. Find the price and the title of the item for those items whose artist contains the character: 'o! How can you include Capitalized 'O' as well? 6. A. Find the vendors whose phone is empty (NULL). Order the results by vendor state ascending and name descending B. Can you write a query to count the records that are NULL? C. Find the vendor name and phone of the vendors whose phone is empty but now substitute the empty phone with 'Not Available': 7. A. Write a query that returns the Last name, First name, state and city of each customer but for only those customers: whose first name starts with 'A' and whose city is alphabetically after New York OR are from California B. Try to re-write this without using parenthesis... 8. A. Write a query that returns the Last name, First name, state and city of each customer but for only those customers: whose first name starts with 'A' and whose city is alphabetically after New York OR are from California and whose city is alphabetically after New York B. Try to re-write this without using parenthesis... 9. Write a query that returns the Last name, First name, state and city of each customer but for only those customers: whose first name starts with 'A' and whose city is alphabetically after New AND are from California 10. Find the Invoice_Id and vendor id for those invoices whose Payment_total is larger than 600 and smaller than 700. Repeat the previous query but this time use the BETWEEN function. Do you see the same results? 11. Using the table VENDORS find vendor contact first and last name for those records whose Vendor-Contact_First_name starts with 'C! Report the results: 1. In ascending alphabetical order of contact first name and then ascending alphabetical order of contact last name 2. In ascending alphabetical order of contact first name and then descending alphabetical order of contact last name 3. In ascending alphabetical order of contact last name and then ascending alphabetical order of contact first name Compare the results of a and b and the results of a and c. 12. Report the full name of each customer using this format: Instead of: "Bob Squarepants" You should report "Bob-S." 13. A. Report the vendor_ids and a count of invoices for each vendor_id (use table invoices). B. Report the vendor_ids, the invoice month (as Invoice_Month) and a count of invoices for each vendor_id and for each month (use table invoices). Report the average invoice total and the complete payment total again per vendor_id and for eac month. Round them all to dollar C. Describe what you see for the vendor with id = 34. Do the results of queries A and B match? D. Now repeat the query of A but restrict your results to only those vendors that have more than 2 invoices. Is this requirement placed in the WHERE clause, the HAVING clause or both? E. Now repeat again the query of A but restrict your results to only those vendors with vendor id larger than 100. Is this requirement placed in the WHERE clause, the HAVING clause or both? F. Now repeat again the query of A but restrict your results to only those records with invoice total smaller than 100. Is this requirement placed in the WHERE clause, the HAVING clause or both? ORDERS I ORDER_ID 1 CUSTOMER_ID I ORDER_DATE I SHIPPED_DATE PATRON INVOICES - INVOICE_ID I VENDOR_ID - INVOICE_NUMBER 0 INVOICE_DATE INVOICE_TOTAL - PAYMENT_TOTAL O CREDIT_TOTAL TERMS_ID - INVOICE_DUE_DATE - PAYMENT_DATE CUSTOMERS CUSTOMER_ID - CUSTOMER_FIRST_NAME - CUSTOMER_LAST_NAME 1 CUSTOMER_ADDRESS - CUSTOMER_CITY 1 CUSTOMER_STATE O CUSTOMER_ZIP I CUSTOMER_PHONE - CUSTOMER_FAX ITEMS ORDER_DETAILS - ORDER_ID - 1 ITEM_ID 1 ORDER_QTY
