Home » SQL & PL/SQL » SQL & PL/SQL » SQL PROBLEM !!! PLEASE HELP
SQL PROBLEM !!! PLEASE HELP [message #197016] Mon, 09 October 2006 09:59 Go to next message
hiip
Messages: 78
Registered: October 2006
Member
hi i just got a problem with this question, can someone help please

the question is:
For campaigns with more than three staff members working on them, list the campaign title and the number of staff members who had salary grade greater than 2

the relational data model is below..

Staff (StaffNo, StaffName, lineManagerStaffNo)
Primary Key: StaffNo
Foreign Key: lineManagerStaffNo refers to StaffNo in Staff

SalaryGrade(Grade, salary)
Primary Key: Grade

StaffOnGrade(StaffNo, Grade, StartDate, FinishDate)
Primary Key: StaffNo, Grade
Foreign Key: StaffNo refers to StaffNo in Staff
Grade refers to Grade in SalaryGrade

Client(CompanyName, CompanyAddress, ContactName, ContactEmail, StaffNo)
PrimaryKey: CompanyName
Foreign Key: StaffNo refers to StaffNo in Staff

Campaign(Title, CampaignFinishDate, EstimatedCost, ActualCost, DatePaid, CompanyName, ManagerStaffNo)
Primary Key: Title
Foreign Key: CompanyName refers to CompanyName in Client
ManagerStaffNo refers to StaffNo in Staff

StaffWorksOnCampaign(StaffNo, Title, Date, Hour)
Primary Key: StaffNo, Title
Foreign Key: StaffNo refers to StaffNo in Staff
Title refers to Title in Campaign

Advert(AdTitle, Type, TargetDate, CompletedDate, EstimatedCost, ActualCost, CampaignTitle)
Primary Key: AdTitle
Foreign Key: CampaignTitle refers to Title in Campaign

please help .. :'( Embarassed
Re: SQL PROBLEM !!! PLEASE HELP [message #197020 is a reply to message #197016] Mon, 09 October 2006 10:32 Go to previous messageGo to next message
plsql11
Messages: 7
Registered: December 2005
Junior Member
Hi ,

U can try the below query:

Select campaigntitle,(select count(emp) from staffongrade where staffno in(select managerstaffno from campaign cc where managerstaffno=cc.managerstaffno)) count
from campaign

Regards.
Re: SQL PROBLEM !!! PLEASE HELP [message #197036 is a reply to message #197016] Mon, 09 October 2006 12:07 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
That's an excellent retyping of your homework question. Perfect alignment, capitalization and spacing. Unless...maybe it was a cut and paste.

[Updated on: Mon, 09 October 2006 12:07]

Report message to a moderator

Re: SQL PROBLEM !!! PLEASE HELP [message #197120 is a reply to message #197020] Tue, 10 October 2006 02:36 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
plsql11 wrote on Mon, 09 October 2006 10:32

Hi ,

U can try the below query:

Select campaigntitle,(select count(emp) from staffongrade where staffno in(select managerstaffno from campaign cc where managerstaffno=cc.managerstaffno)) count
from campaign

Regards.



i am sorry, i am not quite understand your query....
Re: SQL PROBLEM !!! PLEASE HELP [message #197124 is a reply to message #197016] Tue, 10 October 2006 03:04 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
this is part of my sql....

sql for finding company which have more than three staff..

select title, count(staffno)
from staffcampaign
having count(staffno) > 3
group by title

and

sql finding number of staff members who had salary grade greater than 2
select sc.title, count(sc.staffno)
from staffcampaign sc, staffongrade sog
where sc.staffno = sog.staffno AND grade > 200
group by title

how can i use nested subqueires ? can someone help please..
Re: SQL PROBLEM !!! PLEASE HELP [message #197193 is a reply to message #197016] Tue, 10 October 2006 08:09 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
come on guyz.. please help
Re: SQL PROBLEM !!! PLEASE HELP [message #197195 is a reply to message #197193] Tue, 10 October 2006 08:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No Create Table statements, no sample data, and probably a homework assignment to boot.
I must be going soft in my old age.

Try this:
select sc.title, count(sc.staffno)
from staffcampaign sc, staffongrade sog
where sc.staffno = sog.staffno AND grade > 200
and title in (select title, count(staffno)
              from   staffcampaign
              having count(staffno) > 3
              group by title)
group by title
Re: SQL PROBLEM !!! PLEASE HELP [message #197212 is a reply to message #197195] Tue, 10 October 2006 08:58 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
thank you for reply.. really appreciate your reply
but the error comes out like this

and title in (select title, count(staffno)
*

ERROR at line 4:
ORA-00913: too many values

???

that is the same problem as my previous thought..
Re: SQL PROBLEM !!! PLEASE HELP [message #197214 is a reply to message #197195] Tue, 10 October 2006 09:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, the clue is in the error message really (schoolboy mistake on my part).

In the line
and title in (select title, count(staffno)
you need to change the section
select title, count(staffno)
to remove a column.
I leave exactly which column to remove as an excercise for the reader.
Re: SQL PROBLEM !!! PLEASE HELP [message #197225 is a reply to message #197214] Tue, 10 October 2006 10:36 Go to previous message
hiip
Messages: 78
Registered: October 2006
Member
thank you very much... really appreciate tat help..
Previous Topic: Time difference report using date range
Next Topic: Timestamp column as primary key.
Goto Forum:
  


Current Time: Sat Dec 20 20:49:59 CST 2014

Total time taken to generate the page: 0.08386 seconds