Bart De Lepeleer 3e83930412 Merge branch 'master' of https://git.fullstacksyntra.be/bdelepeleer/How_to_code_summary_queries into master | 2 years ago | |
---|---|---|
README.md | 2 years ago | |
demo-queries-from slides-1.sql | 2 years ago | |
demo-queries-from slides-2.sql | 2 years ago | |
demo-queries-from slides-3.sql | 2 years ago | |
demo-queries-from slides-4.sql | 2 years ago | |
demo-queries-from slides-5.sql | 2 years ago | |
demo-queries-from slides-6.sql | 2 years ago | |
demo-queries-from slides-7.sql | 2 years ago | |
exercise-01.sql | 2 years ago | |
exercise-02.sql | 2 years ago | |
exercise-03.sql | 2 years ago | |
exercise-04.sql | 2 years ago | |
exercise-05.sql | 2 years ago | |
exercise-06.sql | 2 years ago | |
exercise-07.sql | 2 years ago |
Write a SELECT statement that returns one row for each vendor in the Invoices table that contains these columns:
This should return 34 rows.
Write a SELECT statement that returns one row for each vendor that contains these columns:
Sort the result set in descending sequence by the payment total sum for each vendor.
Write a SELECT statement that returns one row for each vendor that contains three columns:
Sort the result set so the vendor with the most invoices appears first.
Write a SELECT statement that returns one row for each general ledger account number that contains three columns:
Return only those rows where the count of line items is greater than 1. This should return 10 rows.
Group the result set by the account_description column.
Sort the result set in descending sequence by the sum of the line item amounts.
Modify the solution to exercise 4 so it returns only invoices dated in the second quarter of 2018 (April 1, 2018 to June 30, 2018).
This should still return 10 rows but with some different line item counts for each vendor.
Hint: Join to the Invoices table to code a search condition based on invoice_date.
Write a SELECT statement that answers this question:
What is the total amount invoiced for each general ledger account number?
Return these columns:
Use the WITH ROLLUP operator to include a row that gives the grand total.
This should return 22 rows.
Write a SELECT statement that answers this question: Which vendors are being paid from more than one account?
Return these columns:
This should return 2 rows.