PIRC : independent research and advisory consultancy

PIRC is the UKs' leading independent research and advisory consultancy providing services to institutional investors on corporate governance and corporate social responsibility. Since 1986, it has been the pioneer and champion of good corporate governance within the UK.
Authoritative resource
PIRC has a wide spectrum of clients ranging from pension funds, faith-based investors, trade unions to banks and asset managers. Its Corporate Governance Service is an authoritative and vital resource for active investors, whilst its widely-read Shareholder Voting Guidelines provide a market-wide benchmark for investors and forms part of the movement for corporate governance reform and long-term wealth creation strategies for responsible investors.

For more information about PIRC or to arrange a meeting, contact Janice Hayward, or call us on 0207 247 2323.

The below is a simple(ish) test for applications for one of our Intern positions.

The task:

Please write a SQL statement (or series of statements) that will get me the IDs of the student(s) who have more than one course taught by the same professor. As a hint, it is fred taking 2 courses taught by scooby. (I do not want the answer - I want to see how you get to the answer) The SQL has been aimed at MySQL but should be easily convertible to most other RDBMSes.

Get the SQL right, we will interview you.

If you pass the interview, then you may be offered a paid internship at PIRC's IT dept.

To get an interview, please email the completed SQL statement to jobs@pirc.co.uk with "Intern SQL" in the subject line. Include your name, college or university and contact details (phone number preferably)


CREATE DATABASE dbpirctest;
USE dbpirctest;

Create table tblstudent(
Studentid int primary key auto_increment,
Studentname varchar(64)
);

INSERT INTO tblstudent(studentname) VALUES ('fred');
INSERT INTO tblstudent(studentname) VALUES ('barney');
INSERT INTO tblstudent(studentname) VALUES ('wilma');

Create table tblprofessor(
Profid int primary key auto_increment,
Profname varchar(64)
);

INSERT INTO tblprofessor(profname) VALUES ('scooby');
INSERT INTO tblprofessor(profname) VALUES ('shaggy');
INSERT INTO tblprofessor(profname) VALUES ('scrappy');

Create table tblcourse(
courseID  int primary key auto_increment,
coursename varchar(64)
);

INSERT INTO tblcourse(coursename) VALUES ('English');
INSERT INTO tblcourse(coursename) VALUES ('Maths');
INSERT INTO tblcourse(coursename) VALUES ('MediaStudies');



Create table tblcourseprofessor(
Courseid int references tblcourse(courseiD),
Profid int references tblprofessor(profid)
);

INSERT INTO tblcourseprofessor(courseID, ProfID) VALUES (1,1);
INSERT INTO tblcourseprofessor(courseID, ProfID) VALUES (2,2);
INSERT INTO tblcourseprofessor(courseID, ProfID) VALUES (3,1);

Create table tblcoursestudent(
Courseid int references tblcourse(courseiD),
studentid int references tblstudent(studentid)
);

INSERT INTO tblcoursestudent(courseID, studentID) VALUES (1,1);
INSERT INTO tblcoursestudent(courseID, studentID) VALUES (1,2);
INSERT INTO tblcoursestudent(courseID, studentID) VALUES (1,3);


INSERT INTO tblcoursestudent(courseID, studentID) VALUES (2,1);
INSERT INTO tblcoursestudent(courseID, studentID) VALUES (2,2);


INSERT INTO tblcoursestudent(courseID, studentID) VALUES (3,1);
INSERT INTO tblcoursestudent(courseID, studentID) VALUES (3,2);