For Queries 7-13 1.· What each query is requesting from the database. 2.· Includ
ID: 3919658 • Letter: F
Question
For Queries 7-13
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 7
SELECT vendor_id, COUNT (*) AS invoice_qty,
FROM invoices
GROUP BY vendor_id
QUERY 8
SELECT vendor_state, vendor_city,
COUNT (*) AS invoice_qty,
ROUND (AVG (invoice_total), 2) AS invoice_avg
FROM invoices JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
GROUP BY vendor_stated, vendor_city
QUERY 9
SELECT vendor_state, vendor_city,
COUNT (*) AS invoice_qty,
ROUND (AVG (invoice_total), 2) AS invoice_avg
FROM invoices JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
GROUP BY vendor_stated, vendor_city
HAVING COUNT (*) >= 2
QUERY 10
SELECT vendor_state, vendor_city,
COUNT (*) AS invoice_qty,
ROUND (AVG (invoice_total), 2) AS invoice_avg
FROM invoices JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
GROUP BY vendor_state, vendor_city
HAVING AVG (invoice_total) > 500
ORDER BY invoice_qty DESC
QUERY 11
SELECT vendor_state, vendor_city,
COUNT (*) AS invoice_qty,
ROUND (AVG (invoice_total), 2) AS invoice_avg
FROM invoices JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
WHERE invoice_total > 500
GROUP BY vendor_name
ORDER BY invoice_qty DESC
QUERY 12
SELECT
Invoice date,
COUNT (*) AS invoice_qty,
SUM (invoice_total) AS invoice_sum
FROM invoices
GROUP BY vendor_date
HAVING invoice_date BETWEEN `2014-05-01` AND `2014-05-31`
AND COUNT (*) > 1
AND SUM (invoice_total) >100
ORDER BY invoice_date DESC
QUERY 13
SELECT
Invoice date,
COUNT (*) AS invoice_qty,
SUM (invoice_total) AS invoice_sum
FROM invoices
WHERE invoice_date BETWEEN `2014-05-01` AND `2014-05-31`
GROUP BY invoice_date
HAVING COUNT (*) > 1
AND SUM (invoice_total) >100
ORDER BY invoice_date DESC
Explanation / Answer
QUERY 7 - SELECT vendor_id, COUNT (*) AS invoice_qty FROM invoices GROUP BY vendor_id
ANS - In this Query we are displaying the Vendor_ID and Count of all the records which belongs to that particular Vendor renaming the column as alias 'invoice_qty' from invoices Table and since we are using the Aggregate function COUNT(*) we need to GROUP by the records based on Vendor_ID. So, we will get finally VENDOR_ID and the count of records for each vendor id from Invoices Table.
QUERY 8 - SELECT vendor_state, vendor_city, COUNT (*) AS invoice_qty, ROUND (AVG (invoice_total), 2) AS invoice_avg FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id GROUP BY vendor_stated, vendor_city
Ans - In this Query we are displaying the Vendor_State, Vendor_City, Count of no of records from Invoices Table with alias column 'invoice_qty', Rounding off the Average of Invoice_Total from Invoices Table with alias column invoice_avg from Invoices and Vendor tables based on matching Vendor_Id from both the Tables and the grouping by Vendor_State and Vendoe_City since we are using Aggregate Functions like COUNT() and AVG().
QUERY 9 - SELECT vendor_state, vendor_city, COUNT (*) AS invoice_qty, ROUND (AVG (invoice_total), 2) AS invoice_avg FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id GROUP BY vendor_stated, vendor_city HAVING COUNT (*) >= 2
Ans - This is similiar to Q8 Query except here we will evaluate the HAVING Condition ie COUNT(*) >= 2 and we will eliminate the other records where COUNT(*) < 2. So for any record after performing COUNT(*) if the count is < 2, we will ignore such records and take only those for whicg COUNT() >= 2. Rest of the Query remains same as Q8
QUERY 10 - SELECT vendor_state, vendor_city, COUNT (*) AS invoice_qty, ROUND (AVG (invoice_total), 2) AS invoice_avg FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id GROUP BY vendor_state, vendor_city HAVING AVG (invoice_total) > 500 ORDER BY invoice_qty DESC
ANS - In this Query we are displaying Vendor_State, Vendor_City, Count of number of records from Invoices Table, Rounding off the Average() of Invoice_Total from both Invoices Table and Vendor Table that match common Vendor_Id in both the Tables but we again filter the result set so obtained to check if AVG() of Invoice_Total is greater than 500 and for these new Record set we are displaying the records in descending order of Invoice_Qty as its given ORDER BY invoice_qty DESC. So after performing the JOIN operation we will filter those records for which AVG() evaluates to > 500 and display the final Result set in Descending Order of invoice_qty.
Please let me know in case of any clarifications required. Thanks!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.