OraFAQ Forum: SQL & PL/SQL » SQL PROBLEM !!! PLEASE HELP

Home » SQL & PL/SQL » SQL & PL/SQL » SQL PROBLEM !!! PLEASE HELP

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL PROBLEM !!! PLEASE HELP [message #197016]

Mon, 09 October 2006 09:59

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

Report message to a moderator

Re: SQL PROBLEM !!! PLEASE HELP [message #197020 is a reply to message #197016]

Mon, 09 October 2006 10:32

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.

Report message to a moderator

Re: SQL PROBLEM !!! PLEASE HELP [message #197036 is a reply to message #197016]

Mon, 09 October 2006 12:07

joy_division
Messages: 4963
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

hiip
Messages: 78
Registered: October 2006

Member

plsql11 wrote on Mon, 09 October 2006 10:32

i am sorry, i am not quite understand your query....

Report message to a moderator

Re: SQL PROBLEM !!! PLEASE HELP [message #197124 is a reply to message #197016]

Tue, 10 October 2006 03:04

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

Report message to a moderator

Re: SQL PROBLEM !!! PLEASE HELP [message #197193 is a reply to message #197016]

Tue, 10 October 2006 08:09

hiip
Messages: 78
Registered: October 2006

Member

come on guyz.. please help

Report message to a moderator

Re: SQL PROBLEM !!! PLEASE HELP [message #197195 is a reply to message #197193]

Tue, 10 October 2006 08:14

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

Report message to a moderator

Re: SQL PROBLEM !!! PLEASE HELP [message #197212 is a reply to message #197195]

Tue, 10 October 2006 08:58

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

Report message to a moderator

Re: SQL PROBLEM !!! PLEASE HELP [message #197214 is a reply to message #197195]

Tue, 10 October 2006 09:15

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.

Report message to a moderator

Re: SQL PROBLEM !!! PLEASE HELP [message #197225 is a reply to message #197214]

Tue, 10 October 2006 10:36

hiip
Messages: 78
Registered: October 2006

Member

thank you very much... really appreciate tat help..

Report message to a moderator

Previous Topic:	Time difference report using date range
Next Topic:	Timestamp column as primary key.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:37:04 CDT 2024