Help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help!

Author

Topic

jaydstein
Starting Member

1 Post

Posted - 2010-06-18 : 02:14:10

I've been given the following data query problem. I'm new to SQL and really have no idea what I'm doing. Could you help me design queries to fulfill the requirements below? Although a simple answer would be appreciated, I am trying to learn this material, so any explanation you could give me would be most appreciated!

A project Database, which contains the following tables:
ProjectInformation
...ID (PK)
...OwnerID (FK)
...Name
...Description
...ContactName
...DueDate
...PostDate
...Status

Owner
...ID (PK)
...Name
...Department
...HomeURL
...dtCreated

OwnerAddress
...ID (PK)
...OwnerID (FK)
...Address1
...Address2
...City
...County
...State
...Zip

ProjectLocation
...ID (PK)
...ProjectID (FK)
...Address1
...Address2
...City
...County
...State
...Zip

Requirements:
1. SQL query project ID, Project Name, Owner Name, Owner State which Project State is “WA” or “CA” and Project post date is no later than 3/1/2010.

2.SQL query Owner State, # of Project with Project Due date is in 2010.

dattatreysindol
Starting Member

20 Posts

Posted - 2010-06-18 : 03:40:59

Hi There -

Here is the pseudo code for your requirements:

1st Requirement

SELECT
PrjInfo.ID AS ProjectId
, PrjInfo.Name AS ProjectName
, O.Name AS OwnerName
, OA.State AS OwnerState
FROM
ProjectInformation PrjInfo
INNER JOIN [Owner] O
ON PrjInfo.OwnerId = O.OwnerId
INNER JOIN OwnerAddress OA
ON O.OwnerId = OA.OwnerId
INNER JOIN ProjectLocation PrjLoc
PrjInfo.Id = PrjLoc.ProjectId
WHERE
PrjLoc.[State] IN ('WA','CA')
AND PrjInfo.PostDate <= CAST('2010-01-03' AS DATETIME)

2nd Requirement

SELECT
OA.[State] AS OwnerState
, PrjInfo.Name AS ProjectName
, COUNT(*)
FROM
ProjectInformation PrjInfo
INNER JOIN [Owner] O
ON PrjInfo.OwnerId = O.OwnerId
INNER JOIN OwnerAddress OA
ON O.OwnerId = OA.OwnerId
WHERE
YEAR(PrjInfo.DueDate) = 2010
GROUP BY
OA.[State], PrjInfo.Name

Hope that helps!

Dattatrey Sindol
http://mytechnobook.blogspot.com/

This information is provided "AS IS" with no warranties, and confers no rights.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources