Đang Thực Hiện

126068 MS SQL Correlated Subquery

Your co-worker Terri, who is just learning Microsoft SQL, is preparing a few different reports and sends you a query she has been working on. She needs assistance getting it to properly work. The issue is that she wants to generate a list of all customers who were entered into the Overdue_Accounts table but have presently paid their bill in full. Unfortunately, the query currently returns all records for any customer who has any account in the list of overdue accounts. Terri only wants it to return records when the customer has paid the bill in full. The second issue is that Terri wants to look up the customer's name from the Customer table instead of just displaying a orderID and customerID. She cannot figure out how to do it as of yet.

Terris Query currently returns all overdue account records for any customer who has even one line that has been paid off

use ShipApp

SELECT [url removed, login to view], [url removed, login to view]

FROM Orders a

WHERE [url removed, login to view] in

( SELECT [url removed, login to view]

FROM [Overdue_Accounts] b

WHERE [url removed, login to view] = 1

)

You begin to think that a correlated sub-query is the answer to her problem. Revise Terris query to be a correlated sub-query that performs in the manner that she is wanting. (Here's a Hint: Using the test data you have been provided with, it should only return one record.)

The second part of Terris request is a little more difficult. You will need to join the Customers table to get the CompanyName field from it. Add a second query to your response that accesses the CompanyName using a join command but that still uses a correlated sub-query.

Finally, because you are not entirely sure if a correlated subquery is the best solution, rewrite the query a third and final time but instead of a correlated sub-query, use table joins.

Kỹ năng: Bất kì công việc gì, SQL

Xem thêm: worker record, on line assistance, one line assistance, need of preparing final accounts, customer table, Sql reports, sql microsoft, generate bill, sql final, sql different, learning solution, sql assistance, rewrite sql, sql select select, sql select, little sql, sql problem, joins, test sql command, reports sql, rewrite part time, sql query field, sql part time, time table generate, generate time table

Về Bên Thuê:
( 1 nhận xét )

Mã Dự Án: #1872235