-- 1 select vendor_name as company, vendor_contact_first_name as first_name, ven
ID: 3605109 • Letter: #
Question
-- 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
Explanation / Answer
3) select A_currency_with_all_decimals, A_currency_to_the_dollar, A_currency_to_the_penny, A_currency_to_the_dime From vendors where A_currency_with_all_decimals = '2567.8876'and A_currency_to_the_dollar ='2568' and A_currency_to_the_penny ='2567.9' and as A_currency_to_the_dime ='2567.89' ;
6)select v1.vendor_name , v1.vendor_zip_code
from vendors as v1, vendors as v2
order by v1.vendor_zip_code having v1.vendor_zip_code = v2.vendor_zip_code ;
9)select vendor_name, avg(invoice_total) as average_invoice_total
from vendors join invoice where vendors.vendor_id = invoices.vendor_id
and average_invoice_total > 1000
order by avg(invoice_total) desc ;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.