Query Formulation Problems

Assignment 4

Assignment 4 provides experience with formulation of advanced matching problems involving the outer join operator, difference operator, and nested queries in Chapter 9. Gaining skills with advanced query formulation will deepen your understanding of query formulation and provide an edge in difficult work assignments. Assignment 4 also contains problems involving the CREATE VIEW statement and SELECT statements that use views from Chapter 10.

Don't use plagiarized sources. Get Your Custom Essay on
Query Formulation Problems
Just from $13/Page
Order Essay

To facilitate grading, please number the SQL statements and format them neatly. Show the SELECT statement for each problem. Each SELECT statement should be in text format so that it can be copied to the Query Editor. Generate a screen snapshot for each statement to demonstrate execution. The snapshot should show some or all rows. Name your file as “LastNameFirstNameA4.docx” where LastName and FirstName are your last and first name, respectively.

Query Formulation Problems

  1. List the pending expense reports submitted in October 2020 without any related expense items. The result should contain the expense report number, submitting user name (first and last), expense report description, and submitted date. Remember that PostgreSQL is case sensitive on text comparisons. (Hint: see textbook Chapter 9.2.2 for similar problems.) (10 points)
  2. For expense reports submitting in August 2020, list the expense report number, submitted date, status date, submitting user name (first and last), organization name of the submitting user, and approving user name (first and last). Include a row in the result even if there is no approving user for the expense report. (10 points)
  3. List the expense item number, expense item description, expense date, expense approved amount, asset number, asset description, expense category number, expense category name, and submitted date of the expense report. The rows in the result should meet the following conditions: (1) the expense date and submit date are in the same month and year (2) the expense report has “Approved” status. Include a row in the result even if there is no asset for the expense item. You can use the Date_Part PostgreSQL function to extract the year and month of date and timestamp columns. (10 points)
  4. List the name of organizations without users submitting a pending expense report in September 2020. (10 points)
  5. List the name and limit of expense categories not used in an expense item with a submitted date in September 2020. (10 points)
  6. List the organization name, count of expense reports, and average expense report amount for expense reports with a submitted date in September 2020. The amount of an expense report is the sum of the expense amounts for items in the expense report. The average expense report amount is the average of the expense report amounts for an organization. (Hint: see Section 9.2.4 in Chapter 9 and related problems about nested aggregate functions in class notes). (10 points)
  7. List the expense report number, description, submitted date, submitted user name (first and last name) of expense reports meeting the following conditions: (1) approved status and (2) contains at least one expense item in all transportation expense categories (Local Transportation and Airfare). Remember that PostgreSQL is case sensitive on text comparisons. Your SQL statement should have conditions involving ECName not ECNo. For this problem, you should read textbook Section 9.3. (15 points)
  8. Write a CREATE VIEW statement for the following data requirements. Use Problem8View for the view name. List the expense report number, submitting user first name, submitting user last name, expense item number, expense date, expense amount, expense category name, and expense category limit for expense items with expense date in September 2020. (5 points)
  9. Write a CREATE VIEW statement for the following data requirements. Use Problem9View for the view name. For expense reports with a submitted date in 2020, summarize expense reports by organization name of the submitting user and status of the expense report. The result should contain the organization name of the submitting user, status of the expense report, sum of expense amounts, and sum of approved expense amounts. Only include combinations of organization name and month in the result with sum of expense amounts greater than $500. Rename the computed columns with meaningful names. Use the cross product or join operator style. (5 points)
  10. Write a SELECT statement that uses the view in problem 8 (Problem8View). The SELECT statement should retrieve the expense report number, user last name, expense date, and expense amount for expense amounts greater than 50. Do not use base tables in the SELECT statement. (5 points)
  11. Write a SELECT statement that uses the view in problem 9 (Problem9View). The SELECT statement should retrieve the organization name and expense report status for rows with the sum of approved amounts greater than 100. Do not use base tables in the SELECT statement. (5 points)
  12. Modify the query in problem 10 so that it does not use Problem8View. Follow the query modification steps in Chapter 10 to modify the query in problem 10. The rewritten query should reference only base tables. Simplify the rewritten query to remove extra tables if any. (5 points)

Academic Heros
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

Our writers are encouraged to read and research widely to have rich information before writing clients’ papers. Therefore, be it high school or PhD level paper, it will always be a well-researched work handled by experts.

Professional and Experienced Academic Writers

For one to become part of our team, thorough interview and vetting is undertaken to make sure their academic level and experience are beyond reproach, hence enabling us give our clients top quality work.

Free Unlimited Revisions

Once you have received your paper and feel that some issues have been missed, just request for revision and it will be done. In addition, you can present your work to the tutor and he/she asks for improvement/changes, we are always ready to assist.

Prompt Delivery and 100% Money-Back-Guarantee

All our papers are sent to the clients before the deadline to allow them time to review the work before presenting to the tutor. If for some reason we feel our writers cannot meet the deadline, we will contact you to ask for more time. If this is not possible, then the paid amount will be refunded.

Original & Confidential

Our writers have been trained to ensure work produced is free of plagiarism. Software to check originality are also applied. Our clients’ information is highly guarded from third parties to ensure confidentiality is maintained.

24/7 Customer Support

Our support team is available 24 hours, 7 days a week. You can reach the team via live chat, email or phone call. You can always get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

You have had a hectic day, and still need to complete your assignment, yet it is late at night. No need to panic. Place your order with us, retire to bed, and once you wake up, the paper will be ready.

Essays

Essay Writing Service

It does not matter the urgency of your paper, or the academic level, our team is ready to help you 24/7. Just contact us and all your academic needs will be sorted.

Admissions

Admission Essays & Business Writing Help

A student is often required to write an admission letter requesting to be admitted in a certain institution. For you to be gain that admission in your dream institution, you must write a convincing letter. You can depend on our team for the best admission letters.

Reviews

Editing Support

Academic writing is not just about getting information and throwing it all over. Our team will ensure you have a polished paper that is coherent and has a good flow of information. We also ensure the paper follows the correct formatting styles like APA, Harvard, MLA, Chicago/Turabian.

Reviews

Revision Support

If our writers write a paper but you are not satisfied in one way or another, you can always ask for revision. This is totally free. Our writers and editors will revise your paper untill you are be totally satisfied. We as well offer revision for papers not done by our writers at a small fee.