Help Needed !!!!!!!!!!!!!!

Question

Post reply

Help Needed !!!!!!!!!!!!!!

Congo

Newbie

Points: 7
More actions
January 1, 2007 at 4:00 pm

#116744

Hello, and thank you for reading this post. I’m a Newbie to Database administration, and I’ve been working on this project for the last couple of weeks, with no positive results, as I don’t know how to approach or solve both problems.Could you kindly help? The project consist of two scenarios, each scenario would result in an SQL script. The actual Access database file, for both Scenarios, can be found in following URL:
http://www.sendmefiles.com/KTCShippingApp_mdb22

Scenario 1:
Your co-worker Anne, who is just learning SQL, is preparing some reports and sends you a query on which she's working. She needs help getting it to work. Her problem is that she wants to generate a list of all customers who were entered into the Overdue Accounts table but presently have paid their bill in full. Unfortunately, her query returns all records for any customer who has any account in the list of overdue accounts. She only wants it to return records when the customer has paid the bill in full. Her second issue is she wants to look up the customer's name from the Customer table instead of just displaying a customer id and an order id. She can't figure out how to do it yet.
Anne's Query (returns all overdue account records for any customer who has even one line that is paid off)
use KTC
SELECT a.CustomerID, a.OrderID
FROM Orders a
WHERE a.CustomerID in
( SELECT b.CustomerID
FROM [Overdue Accounts] b
WHERE b.PaidInFull = 1
)
You think that a correlated sub-query is the solution to her problem. Revise her query to be a correlated sub-query that performs in the manner that she wants. (Hint: Using the test data you were provided with, this should only return one record.) Prepare a response to Anne that shows your correlated sub-query and that explains what a correlated sub-query is.
The second part of her 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. (Hint: What column do the Overdue Accounts table and the Customers table have in common?)
Finally, because you're not sure if a correlated query is the best solution, rewrite the query a third time but instead of a correlated sub-query, use table joins. Add this query to your response as well.

Scenario 2:
Your supervisor was not present at the last meeting but knows that you discussed issues of efficiency. She asks you to select the query you prepared for Anne that you found to be most efficient and that lists all three pieces of required information. She asks you to help Anne by embedding the query in a script that creates a view in the KTC database. She also asks you to explain to Anne one or two advantages of using a defined view in the database versus external SQL commands. Your supervisor also tells you to make sure when Anne cuts and pastes the script into Query analyzer, it will execute without any errors.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Is this a homework assignment, or a test or some sort? It certainly looks like it.

What specifically are you having problems with?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

You say you've been working on it for "weeks"... let's see what you've tried.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 3

Hi ,

Use this query for Scnerio 1 :

Select C.CustomerID ,

C.CompanyName ,

C.ContactFirstName ,

C.ContactLastName ,

O.OrderID ,

O.InvoiceTotal

From Customers C

Join [Overdue Accounts] O

On C.CustomerID=O.CustomerID

Where O.PaidInFull=1

Regards ,

Amit Gupta..

M.V.P.

/* Problem Makes Man Perfect */

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 4

Please show us your work and what you are having issues with. This does indeed sound like homework.

To other posters, please don't post answers to people's homework. This is a forum for helping people learn.