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...
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)
