HJÆLP til Update Statement
Kan ikke finde ud af hvorfor følgende Update statement ikke virker..Har prøvet følgende men det giver fejlen:
Invalid use of group function
UPDATE faculty AS f
SET f.salary = f.salary * 1.05
WHERE COUNT(instructor) = (SELECT
COUNT(instructor)
FROM courseoffering AS c, course AS co, faculty AS f
WHERE c.courseid = co.courseid
AND f.name = c.instructor
AND c.semester ='SPRING'
AND co.department = 'Computer Science'
GROUP BY instructor
HAVING COUNT(instructor) > 1)
Jeg skal opdatere en instruktørs løn med 5 %, hvis han er instruktør på mere end 1 kursus i forårs semestret og er tilhørende Computer Science departementet. Hjælp...
CREATE TABLE student (
studentid INT (20) PRIMARY KEY,
name VARCHAR(100) NOT NULL,
class VARCHAR (50) NOT NULL,
major VARCHAR(100)NOT NULL
) TYPE=InnoDB;
INSERT INTO student (studentid, name, class, major)
VALUES ('999872543','Joe Smith','Senior','Computer Science'),
('995389332','Jane Doe','Junior','Computer Science'),
('999872552','Bo Hansen','Junior','Computer Science'),
('991236459','Hans Ib','Senior','Computer Science'),
('991236460','Lise Hansen','Junior','E Business'),
('991236461','Karen Jeppesen','Senior','E Business'),
('991236462','Hans Sørensen','Senior','E Business'),
('991236463','Lasse Høeg','Junior','E Business'),
('991236464','IB BO Hansen','Senior','Computer Science'),
('991236465','Søs Larsen','Junior','Database Design'),
('991236466','Lars Larsen','Senior','Database Design'),
('991236467','Uffe Hansen','Junior','Database Design'),
('991236468','Stefan Sørensen','Senior','Database Design'),
('991236469','Christian Høeg','Senior','Communication'),
('991236470','Lulu Hansen','Senior','Communication'),
('991236471','Bo Hansen Larsen', 'Senior','Communication');
CREATE TABLE course (
courseid VARCHAR (4) PRIMARY KEY,
coursename VARCHAR (100),
department VARCHAR (50)
) TYPE=InnoDB;
INSERT INTO course
VALUES ('ITDD', 'Introduction to Database Design','Computer Science'),
('DAMI', 'Data Mining','Computer Science'),
('ITPM', 'IT Project Management','E Business'),
('BUCA', 'Business Cases','E Business'),
('IP', 'Introductory Programming','Software Engineering'),
('OOP', 'Object Oriented Programming ','Software Engineering'),
('OS', 'Operating Systems','Communication'),
('PW', 'Project Workshop','Software Engineering');
CREATE TABLE courseoffering (
courseid VARCHAR (4),
semester VARCHAR(50),
year VARCHAR (4),
instructor VARCHAR (50),
FOREIGN KEY (courseid) REFERENCES course(courseid)
ON DELETE CASCADE
ON UPDATE CASCADE
) TYPE=InnoDB;
INSERT INTO courseoffering
VALUES ('ITDD', 'Spring','2010','John Smithson'),
('DAMI', 'Spring','2010','James Jensen'),
('ITDD', 'Spring','2010','James Jensen'),
('ITPM', 'Fall','2010','Ole Isakson'),
('BUCA', 'Fall','2010','Lars Larsen'),
('IP', 'Spring','2010','Kurt Nielsen'),
('OOP', 'Spring','2010','Karl Smith'),
('OS', 'Spring','2010','Lars Hansen'),
('IP', 'Spring','2010','John Smithson');
CREATE TABLE faculty (
name VARCHAR (50) PRIMARY KEY,
office VARCHAR(50),
salary VARCHAR(10)
) TYPE=InnoDB;
INSERT INTO faculty
VALUES ('John Smithson', '4D44','220000'),
('James Jensen', '4D45','250000'),
('Ole Isakson', '3D44','300000'),
('Lars Larsen', '4D100','300000'),
('Kurt Nielsen', '4C44','250000'),
('Karl Smith', '4B44','220000'),
('Lars Hansen', '4D30','250000');
CREATE TABLE gradereport (
studentid INT (20),
courseid VARCHAR (4),
semester VARCHAR(50),
year VARCHAR (4),
grade INT (2),
PRIMARY KEY (studentid, courseid)
) TYPE=InnoDB;
INSERT INTO gradereport
VALUES ('999872543','ITDD','Spring','2009','5'),
('995389332','ITDD','Fall','2008','12'),
('999872543','OS','Spring','2007','7'),
('999872543','PW','Fall','2008','10'),
('991236460','OS','Spring','2009','12'),
('991236460','PW','Spring','2009','12'),
('991236461','ITDD','Fall','2009','4'),
('991236462','OS','Spring','2007','7'),
('991236462','IP','Spring','2007','7'),
('991236463','ITDD','Spring','2009','7'),
('991236464','IP','Fall','2007','10'),
('991236464','DAMI','Fall','2007','10'),
('991236465','IP','Fall','2007','2'),
('991236466','BUCA','Fall','2009','7'),
('991236467','BUCA','Spring','2008','7'),
('991236467','OOP','Spring','2008','7'),
('991236468','OOP','Fall','2009','10'),
('991236469','OOP','Spring','2008','10'),
('991236470','ITPM','Fall','2007','12'),
('991236470','DAMI','Fall','2007','12'),
('991236471','DAMI', 'Spring','2009','12'),
('991236471','OS', 'Spring','2009','12');
CREATE TABLE prerequisite (
courseid VARCHAR (4),
prerequisiteid VARCHAR (4),
PRIMARY KEY (prerequisiteid, courseid)
) TYPE=InnoDB;
INSERT INTO prerequisite
VALUES ('ITDD','OS'),
('OOP','PW'),
('DAMI','ITDD');