Home » SQL & PL/SQL » SQL & PL/SQL » about sQL error....
about sQL error.... [message #197465] Wed, 11 October 2006 06:03
hiip
Messages: 78
Registered: October 2006
Member
hi, can someone help me on this please

list the names of staff members who do not work on campaigns with Tv advertisement..

this is my code but when i innput the sql to compile it, that is not wat i want .. can someone correct it for me ....


select staffname
from staff, staffcampaign, advert
where staff.staffno = staffcampaign.staffno
and staffcampaign.title = advert.campaigntitle
and advert.type <> 'Tv'
and staffcampaign.title NOT IN
(select campaigntitle
from advert
where type = 'Tv')



select campaigntitle
from advert
where type <> 'Tv'



this is THE DDL for creating table ..


CREATE TABLE Staff
(StaffNo NUMBER (5),
StaffName VARCHAR2 (30) NOT NULL,
lineManagerStaffNo NUMBER (5),
CONSTRAINT staff_staffno_pk PRIMARY KEY(Staffno),
CONSTRAINT staff_linemanagerstaffno_fk FOREIGN KEY(linemanagerstaffno)REFERENCES staff(staffno));

CREATE TABLE SalaryGrade
(Grade NUMBER (5),
Salary FLOAT (8),
CONSTRAINT salarygrade_grade_pk PRIMARY KEY(grade));

CREATE TABLE Client
(CompanyName VARCHAR2 (30),
CompanyAddress VARCHAR2 (30),
ContactName VARCHAR2 (30),
ContactEmail VARCHAR2 (35),
StaffNo NUMBER (5),
CONSTRAINT client_companyname_pk PRIMARY KEY(companyname),
CONSTRAINT client_staffno_fk FOREIGN KEY(staffno)REFERENCES staff(staffno));

CREATE TABLE StaffOnGrade
(StaffNo NUMBER (5),
Grade NUMBER (5),
StartDate DATE,
FinishDate DATE,
CONSTRAINT staffongrade_staffno_grade_pk PRIMARY KEY(staffno, grade),
CONSTRAINT staffongrade_staffno_fk FOREIGN KEY(staffno) REFERENCEs staff(staffno),
CONSTRAINT staffongrade_grade_fk FOREIGN KEY(grade) REFERENCES salarygrade(grade));

CREATE TABLE Campaign
(Title VARCHAR2 (30),
CampaignFinishDate DATE,
EstimatedCost FLOAT (4),
ActualCost FLOAT (4),
DatePaid DATE,
CompanyName VARCHAR2 (30),
ManagerStaffNo NUMBER(5),
CONSTRAINT campaign_title_pk PRIMARY KEY(title),
CONSTRAINT campaign_companyname_fk FOREIGN KEY(companyname)REFERENCES client(companyname),
CONSTRAINT campaign_ManagerStaffNo_fk FOREIGN KEY(ManagerStaffNo)REFERENCES staff(staffNo));

CREATE TABLE StaffCampaign
(StaffNo NUMBER (5),
Title VARCHAR2 (30),
StaffCamDate DATE,
Hour FLOAT,
CONSTRAINT staffcampaign_staffno_title_pk PRIMARY KEY(staffno, title),
CONSTRAINT staffcampaign_staffno_fk FOREIGN KEY(staffno) REFERENCES staff(staffno),
CONSTRAINT staffcampaign_title_fk FOREIGN KEY(title) REFERENCES campaign(title));

CREATE TABLE Advert
(AdTitle VARCHAR2 (10),
Type VARCHAR2 (8),
TargetDate DATE,
CompletedDate DATE,
EstimatedCost FLOAT (4),
ActualCost FLOAT (4),
CampaignTitle VARCHAR2 (30),
CONSTRAINT advert_adtitle_pk PRIMARY KEY(adtitle),
CONSTRAINT advert_campaigntitle_fk FOREIGN KEY(campaigntitle) REFERENCES campaign(title));


[Updated on: Wed, 11 October 2006 06:55]

Report message to a moderator

Previous Topic: Need help for a query.
Next Topic: query giving duplicate result
Goto Forum:
  


Current Time: Fri Dec 09 01:58:56 CST 2016

Total time taken to generate the page: 0.13449 seconds