For Queries 1-6 1.· What each query is requesting from the database. 2.· Include
ID: 3919655 • Letter: F
Question
For Queries 1-6
1.· What each query is requesting from the database.
2.· Include descriptions for what ALL of the following SQL keywords are doing in each query they are present in: SELECT, FROM, JOIN, WHERE, and ORDER BY
3.· Include a description of what is being aggregated and how, including the aggregate functions, the GROUP BY clause, and the HAVING clause.
QUERY 1
SELECT COUNT (*) AS number_of invoices,
SUM (invoice_total – payment_total – credit_total) AS total_due
FROM invoices
WHERE invoice_total – payment – credit_total > 0
QUERY 2
SELECT `After 1/1/2014` AS selection _date,
COUNT (*) AS number_of _invoices
ROUND (AVG (invoice_total), 2) AS avg_invoice_amt,
SUM (invice_total) AS total_invoice_amt
FROM invoices
WHERE invoice_date > `2014 -01-01`
QUERY 3
SELECT `After 1/1/2014` AS selection _date,
COUNT (*) AS number_of _invoices
MAX (invoice_total) AS highest_invoice-total,
MIN (invoice_total) AS lowest_invoice_total
FROM invoices
WHERE invoice_date > `2014-01-01`
QUERY 4
SELECT MIN (vendor_name) AS first_vendor
MAX (vendor_name) AS last_vendor
COUNT (vendor_name) AS number_of_vendors
FROM vendors
QUERY 5
SELECT COUNT (DISTINCT vendor_id) AS number _of _vendors,
COUNT (vendor_id) AS number_of_invoices,
ROUND (AVG (invoice_total), 2) AS avg_invoice_amt,
SUM (invoice_total) AS total_invoice_amt
FROM invoices
WHERE invoice_date > `2014-01-01`
QUERY 6
SELECT vendor_id, ROUND (AVG (invoice_total), 2) AS average_invoice_amount
FROM invoices
GROUP BY vendor_id
HAVING AVG (invoice__total) > 2000
ORDER BY average_invoice_amount DESC
Explanation / Answer
QUERY 1
SELECT COUNT (*) AS number_of invoices,
SUM (invoice_total – payment_total – credit_total) AS total_due
FROM invoices
WHERE invoice_total – payment – credit_total > 0;
Description: This query will result Number of invoices which due ammount is greater than 0
And sum of total due amount from invoices table.
due amount is calculated by invoice_total – payment – credit_total
In this count, SUM aggregation functions are used.
And where clause is used to filter for due amount > 0
-----------------------------------------------------------------------------------------------------
QUERY 2
SELECT `After 1/1/2014` AS selection _date,
COUNT (*) AS number_of _invoices
ROUND (AVG (invoice_total), 2) AS avg_invoice_amt,
SUM (invice_total) AS total_invoice_amt
FROM invoices
WHERE invoice_date > `2014 -01-01`;
Description: This query will result number of invoice generated after invoice date 2014 -01-01
And also return total invoice ammount using Sum Function
and Average invoice amount using AVG function after invoice date 2014 -01-01 by rounding this value;
In this AVG function is used to give average value of invoice amount
And sum function is used for total value od invoice amount.
And where clause is used to filter on invoice_date > `2014 -01-01`;
-------------------------------------------------------------------------------------------------------------
QUERY 3
SELECT `After 1/1/2014` AS selection _date,
COUNT (*) AS number_of _invoices
MAX (invoice_total) AS highest_invoice-total,
MIN (invoice_total) AS lowest_invoice_total
FROM invoices
WHERE invoice_date > `2014-01-01`
Description:- This query will result number of invoice generated after invoice date 2014 -01-01
And also return highest invoice ammount using MAX Function
and lowest invoice amount using MIN function after invoice date 2014 -01-01 by rounding this value;
In this MAX function is used to give highest value of invoice amount
And MIN function is used for total value od invoice amount.
And where clause is used to filter on invoice_date > `2014 -01-01`;
-------------------------------------------------------------------------------------------------------------------
QUERY 4
SELECT MIN (vendor_name) AS first_vendor
MAX (vendor_name) AS last_vendor
COUNT (vendor_name) AS number_of_vendors
FROM vendors
Description :- This query will result Fist vendor, last vendor and total number of vendor from vendors table
In this MIN function is used to find minimum value of vendor name
MAX is used to find maximum value of vandor name
and COUNT is used to get total number of vendor.
------------------------------------------------------------------------------------------------------------------
QUERY 5
SELECT COUNT (DISTINCT vendor_id) AS number _of _vendors,
COUNT (vendor_id) AS number_of_invoices,
ROUND (AVG (invoice_total), 2) AS avg_invoice_amt,
SUM (invoice_total) AS total_invoice_amt
FROM invoices
WHERE invoice_date > `2014-01-01`
Description: This query will result number of vendorsa
And number of invoice generated after invoice date 2014 -01-01
And also return total invoice ammount using Sum Function
and Average invoice amount using AVG function after invoice date 2014 -01-01 by rounding this value;
In this COUNT function is used to get no of vendors and number of invoices
And AVG function is used to give average value of invoice amount
And sum function is used for total value od invoice amount.
And where clause is used to filter on invoice_date > `2014 -01-01`;
-----------------------------------------------------------------------------------
QUERY 6
SELECT vendor_id, ROUND (AVG (invoice_total), 2) AS average_invoice_amount
FROM invoices
GROUP BY vendor_id
HAVING AVG (invoice__total) > 2000
ORDER BY average_invoice_amount DESC
Description: - This query will result Average Invoice amount for each Vendor and vender id where invoice__total > 2000
In the descending order od Average invoice amount from invoices table.
In this AVG function is used to get average of invoice total.
GROUP BY clause is used for grouping the rows on vendor_id
Having clause is used to put filter on rows where invoice total > 2000
And ORDER BY is user to maintain Descending order of avg invoice amount in result.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.