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.
| Author |
Topic |
|
sportsman667
Starting Member
4 Posts |
 Posted - 2007-04-15 : 18:35:36
|
Hello,I am having a few slight problems putting together some quieries based upon my tables I created. For some reason I am having trouble with two table joins and subquieries. Here's my CREATE table statements. Im just having problems coming up with the solution for these few queries. Would anyone be willing to help me out or guide me in the right direction? Heres my queries I am trying to do and my CREATE table statements:1.) Assuming that all are ongoing projects list the duration (in days) of each project. 2.) List all the technicians and all the details of the projects they manage. Include in your list technicians who may not have a project.3.)List the total value of all projects in each department.4.)List the total value of all projects by each technician.CREATE TABLE raghavan_Technicians (Techno CHAR(2) PRIMARY KEY, Tstartdate Date, DeptNo CHAR(2));INSERT INTO raghavan_Technicians VALUES(‘10’, ‘01-JAN-2006’, ‘S1’);INSERT INTO raghavan_Technicians VALUES (‘11’, ‘15-DEC-2004’, ‘S2’);INSERT INTO raghavan_Technicians VALUES (‘12’, ‘27-MAR-2003’, ‘S1’);INSERT INTO raghavan_Technicians VALUES (‘13’, ‘15-MAR-2005’, ‘S2’);INSERT INTO raghavan_Technicians VALUES (‘14’, ‘21-SEP-2004’, ‘S3’);INSERT INTO raghavan_Technicians VALUES (‘15’, ‘13-FEB-2005’, ‘S2’);INSERT INTO raghavan_Technicians VALUES (‘16’, ‘13-FEB-2007’, ‘S2’);CREATE TABLE raghavan_PROJECTS (Projno CHAR(3) PRIMARY KEY, TechNo CHAR(2) REFERENCES raghavan_Technicians (Techno), projcost NUMBER(15), Pstartdate Date);INSERT INTO raghavan_PROJECTS VALUES(‘100’, ‘10’, 254000, ‘02-FEB-2006’);INSERT INTO raghavan_PROJECTS VALUES(‘101’, ‘10’, 325000, ‘01-MAR-2006’);INSERT INTO raghavan_PROJECTS VALUES(‘102’, ‘11’, 128750, ‘23-FEB-2005’);INSERT INTO raghavan_PROJECTS VALUES(‘103’, ‘11’, 289000, ‘21-MAR-2005’);INSERT INTO raghavan_PROJECTS VALUES(‘104’, ‘12’, 122650, ‘04-FEB-2004’);INSERT INTO raghavan_PROJECTS VALUES(‘105’, ‘13’, 329000, ‘16-MAR-2005’);INSERT INTO raghavan_PROJECTS VALUES(‘106’, ‘14’, 375000, ‘12-NOV-2004’);INSERT INTO raghavan_PROJECTS VALUES(‘107’, ‘14’, 295000, ‘21-DEC-2005’);INSERT INTO raghavan_PROJECTS VALUES(‘108’, ‘15’, 325000, ‘23-OCT-2005’); |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-15 : 19:34:04
|
Do you have problem with the query ? Post your query here.
KH |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-15 : 21:35:14
|
| Homework?CODO ERGO SUM |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-16 : 01:15:48
|
1. generally you should use int to store integers, rather than char/varchar/etc. Here I am referring to your ProjNo and TechNo columns. I assume they will always contains digits since the column names imply "number". This is particularly important if you want to do arithmetic on the values in such columns, or want to sort them as numbers.2. replace ? with ' in your insert statements if you want them to work. 3. post the sql you have tried so far. nobody here will just give you the answer to what smells strongly of a homework problem. On the other hand, there are plenty of people here willing to help if you show some effort to learn on your part however. copy and paste of the homework question does not constitute effort. 
www.elsasoft.org |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-04-16 : 02:46:44
|
| [code]CREATE TABLE raghavan_Technicians (Techno CHAR(2) PRIMARY KEY, Tstartdate Date, DeptNo CHAR(2));[/code]Are you using SQL Server? MadhivananFailing to plan is Planning to fail |
|
|
|
pbguy
Constraint Violating Yak Guru
319 Posts |
Posted - 2007-04-16 : 07:48:13
|
| If ur using SQL server.... try the following-- preparing sample datadeclare @tt table (Techno varCHAR(2), Tstartdate Datetime, DeptNo varCHAR(2))INSERT INTO @tt VALUES('10', '01-JAN-2006', 'S1')INSERT INTO @tt VALUES ('11', '15-DEC-2004', 'S2')INSERT INTO @tt VALUES ('12', '27-MAR-2003', 'S1')INSERT INTO @tt VALUES ('13', '15-MAR-2005', 'S2')INSERT INTO @tt VALUES ('14', '21-SEP-2004', 'S3')INSERT INTO @tt VALUES ('15', '13-FEB-2005', 'S2')INSERT INTO @tt VALUES ('16', '13-FEB-2007', 'S2')declare @proj table (Projno varCHAR(3) PRIMARY KEY, TechNo varCHAR(2) , projcost int, Pstartdate Datetime)INSERT INTO @proj VALUES('100', '10', 254000, '02-FEB-2006')INSERT INTO @proj VALUES('101', '10', 325000, '01-MAR-2006')INSERT INTO @proj VALUES('102', '11', 128750, '23-FEB-2005')INSERT INTO @proj VALUES('103', '11', 289000, '21-MAR-2005')INSERT INTO @proj VALUES('104', '12', 122650, '04-FEB-2004')INSERT INTO @proj VALUES('105', '13', 329000, '16-MAR-2005')INSERT INTO @proj VALUES('106', '14', 375000, '12-NOV-2004')INSERT INTO @proj VALUES('107', '14', 295000, '21-DEC-2005')INSERT INTO @proj VALUES('108', '15', 325000, '23-OCT-2005')--Assuming that all are ongoing projects list the duration (in days) of each projectSelect projno, datediff(day, Pstartdate, getdate()) from @proj order by projno--List all the technicians and all the details of the projects they manage. Include in your list technicians who may not have a project.Select t.techno, t.deptno, case when p.projno is null then 'No project' else p.projno end as Projnum from @tt t left outer join @proj p on t.techno = p.techno--List the total value of all projects in each department.Select a.deptno, sum = coalesce(sum(a.projcost), 0) from(Select t.techno, t.deptno,p.projcost from @tt t left outer join @proj p on t.techno = p.techno) as a group by a.deptno--List the total value of all projects by each technicianSelect a.techno, sum = coalesce(sum(a.projcost), 0) from(Select t.techno, t.deptno,p.projcost from @tt t left outer join @proj p on t.techno = p.techno) as a group by a.techno |
|
|
|
|
|
|
|
|
query problems