For Queries 11-13, please describe: a. · What each query is requesting from the
ID: 3919706 • Letter: F
Question
For Queries 11-13, please describe:
a. · What each query is requesting from the database.
·b. 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
·c. Include a description of what is being aggregated and how, including the aggregate functions, the GROUP BY clause, and the HAVING clause.
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 11:
a. query is requseting from the database the following information
1. states of each vendor
2. cities of each vendor
3. quantity of invoices from each vendor
4. average of the totals of the invoices, rounded upto 2 decimal places,
generated from each vendor
5. all the data must be retrieved provided invoice_total must be greater than 500
and then sorted in descending order in accordance to the invoice_qty retrieved.
b. SELECT- The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
FROM- The SQL FROM clause is used to list the tables and any joins required for the SQL statement.
JOIN- A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
WHERE- The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
ORDER BY- The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
c. GROUP BY- The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
HAVING- The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
Query 12:
a. query is requesting from database the following:
1. date of each invoice
2. quantity of invoices for each vendor date
3. total of the invoices for each vendor date
4. data will be grouped as per vendor_date for the dates between '2014-05-01' and '2014-05-31'
5. quantity must be greater one and sum must be greater than 100.
the data must be sorted in descending order in accordance to the invoice_date.
b. SELECT- The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
FROM- The SQL FROM clause is used to list the tables and any joins required for the SQL statement.
JOIN- A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
WHERE- The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
ORDER BY- The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
c. GROUP BY- The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
HAVING- The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
Query 13:
a. query is requesting from database the following:
1. date of each invoice
2. quantity of invoices for each vendor date
3. total of the invoices for each vendor date
4. for the dates between '2014-05-01' and '2014-05-31' is retrieved and then grouped in accordance to the invoice_date
5. quantity must be greater one and sum must be greater than 100.
the data must be sorted in descending order in accordance to the invoice_date.
b. SELECT- The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
FROM- The SQL FROM clause is used to list the tables and any joins required for the SQL statement.
JOIN- A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
WHERE- The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
ORDER BY- The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
c. GROUP BY- The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
HAVING- The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.