
USE ap;

-- 1 Select all fields and all tuples from the invoices table

SELECT * FROM invoices;


-- 2 Create a calculated  payment total for the invoices table
SELECT invoice_id AS inv_id , vendor_id, invoice_total + credit_total paym_total FROM invoices;


-- 3 Return the vendor name and vendor contact name as one field (use concat)
SELECT concat(vendor_name, ", ", vendor_contact_first_name, " ", vendor_contact_last_name) from vendors;

SELECT concat_ws(" ", vendor_name, vendor_contact_first_name, vendor_contact_last_name) from vendors;

-- 4 Return the vendor names that start with P (use LIKE)

SELECT vendor_name FROM VENDORS WHERE vendor_name LIKE 'P%';

-- 5 Return the invoice data for invoices_id from 10 to 17;

SELECT * FROM invoices WHERE invoice_id BETWEEN 10 AND 17;
-- 6 Write a query that uses AND in the WHERE clause 

SELECT * FROM invoices WHERE monthname(invoice_due_date) = 'May' AND year(invoice_due_date) = 2014;

-- 7 WRITE a query that uses OR in the WHERE clause
SELECT * FROM invoices WHERE monthname(invoice_due_date) = 'May' OR monthname(invoice_due_date) = 'June' ;

-- 8 Order the results of invoice 10 through 17 by invoice_total
SELECT * FROM invoices WHERE invoice_id BETWEEN 10 AND 17 ORDER BY invoice_total;

-- 9 Order the results of invoice 10 through 17 by invoice_total in descending order and credit_total in ascending order 

SELECT * FROM invoices WHERE invoice_id BETWEEN 10 AND 17 ORDER BY invoice_total DESC, credit_total;

-- Count the number of vendors  assign the result to a variable names num_vendors (AGGREGATION )
SELECT COUNT(*) AS num_vendors FROM vendors;

-- Count the number of invoices per vendor 
SELECT vendor_id, COUNT(*) from invoices GROUP BY vendor_id ORDER BY COUNT(*) DESC;

-- Calculate the total payment by vendor, return the vendor name and the total
SELECT vendor_id, sum(payment_total) AS vendor_pay FROM invoices GROUP BY vendor_id ORDER BY sum(payment_total) DESC;
-- Calculate the total payment by vendor, return the vendor id  and the total payment for vendor for vendors who paid more than $100
SELECT vendor_id, sum(payment_total) AS vendor_pay FROM invoices GROUP BY vendor_id HAVING sum(payment_total) > 100;
-- Select a subset of tuples from the invoices table using a WHERE clause 

SELECT * from invoices where invoice_id = 11 OR invoice_id = 12; 