-- 1

select vendor_name as company, vendor_contact_first_name as first_name, vendor_state

from vendors

where vendor_state = 'CA' and vendor_contact_first_name < 'B'

order by vendor_name ;

-- 2

select vendor_name as company,concat(vendor_contact_first_name, ' ', vendor_contact_last_name) as name, concat(vendor_address1, ' ', vendor_city, '.', ' ', vendor_state, ' ', vendor_zip_code) as Address

from vendors

order by vendor_name

limit 10;

-- 3

select '2567.8876' as A_currency_with_all_decimals, '2568' as A_currency_to_the_dollar, '2567.9' as A_currency_to_the_penny, '2567.89' as A_currency_to_the_dime ;

-- 4

select vendor_state as State

from vendors

where vendor_state between 'E' and 'P'

group by vendor_state ;

-- 5

select invoice_number, line_item_description, line_item_amount

from invoices join invoice_line_items

on invoices.invoice_id = invoice_line_items.invoice_id

where invoice_date > '2014-07-23'

order by invoice_date desc ;

-- 6

select v1.vendor_name , v1.vendor_zip_code

from vendors v1, vendors v2

where v1.vendor_zip_code = v2.vendor_zip_code

order by vendor_zip_code ;

-- 7

select vendor_name, invoice_number, invoice_date, line_item_amount, account_description

from vendors join invoices

on vendors.vendor_id = invoices.vendor_id

join invoice_line_items

on invoices.invoice_id = invoice_line_items.invoice_id

join general_ledger_accounts

on invoice_line_items.account_number = general_ledger_accounts.account_number

where (invoice_total - credit_total - payment_total ) > 0

order by vendor_name desc ;

-- 8

select count(invoice_total) as 'Number_of_invoices',

min(invoice_total) as 'Lowest_invoice_total',

max(invoice_total) as 'Highest_invoice_total',

avg(invoice_total) as 'average_invoice_total'

from invoices ;

-- 9

select vendor_name, avg(invoice_total) as average_invoice_total

from vendors join invoices

on vendors.vendor_id = invoices.vendor_id

where average_invoice_total > 1000

order by avg(invoice_total) desc ;

--

select vendor_name -- avg(invoice_total) as average

from vendors

where exists

(select avg(invoice_total)

from invoices

where avg(invoice_total) > 1000 )

-- where exists

-- (select invoice_total

-- from invoices

-- where invoice_total > 1000)

order by invoice_total ;

-- 10

select count(distinct vendor_id) as number_of_vendors_who_submitted_invoice

from vendors

where exists

(select *

from invoices

where vendor_id = vendors.vendor_id)

Please Correct numbers - 3, 6 ,9