Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
A little SQL Exercise
Message
From
18/01/2009 12:49:50
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
Other
Title:
A little SQL Exercise
Miscellaneous
Thread ID:
01374800
Message ID:
01374800
Views:
57
I use this one in my SQL class from time to time....

Suppose you have three tables - a Job Construction Master and 2 child tables (one for hours worked, and one for material purchases).

At any one time, a job could have data in neither child table....or in both.....or in one but not the other.

So here are the tables...
CREATE TABLE JobMaster   (JobNumber int, JobDescription varchar(50))

CREATE TABLE JobHours  (JobNumber int, EmployeeID int, DateWorked DateTime, 
                        HoursWorked decimal(10,2))

CREATE TABLE JobMaterials  (JobNumber int, Notes varchar(50), 
                            purchaseamount decimal(14,2), 
                            PurchaseDate DateTime)
OK, here's some sample data...
insert into JobMaster values (1, 'Job A')
insert into JobMaster values (2, 'Job B')
insert into JobMaster values (3, 'Job C')

INSERT INTO JobHours values (1, 1, '01-01-08',8)
INSERT INTO JobHours values (1, 1, '01-02-08',8)
INSERT INTO JobHours values (1, 2, '01-01-08',7)
INSERT INTO JobHours values (1, 2, '01-02-08',7)


INSERT INTO JobHours values (2, 3, '01-01-08',7)
INSERT INTO JobHours values (2, 3, '01-02-08',7)
INSERT INTO JobHours values (2, 4, '01-01-08',6)
INSERT INTO JobHours values (2, 4, '01-02-08',6)


insert into JobMaterials values (1, 'bought tractor',1000,'1-1-08')
insert into JobMaterials values (1, 'bought cement',2000,'1-1-08')

insert into JobMaterials values (3, 'bought tractor',10000,'1-1-08')
insert into JobMaterials values (3, 'bought cement',20000,'1-1-08')
The goal: to write a query that returns a result set of one row per job, along with the summary of hours worked and material purchase amounts. So the result set might look something like this...
--                Tot Hrs Worked      total of Material purchases
--Job A      30                              3,000
--Job B      26                              null
--Job C      null                           30,000
Given the table structure above and sample data - how would you write the SQL query? (Assume there are indexes on Job Number in every table, I was too lazy to include the syntax for that)
Next
Reply
Map
View

Click here to load this message in the networking platform