USING TABLE RELATIONSHIPS IN SQL SELECT STATEMENTS :-
1.List the course numbers of courses Robert Roberts has been on :-
‘Robert’
‘Roberts’
employee
empno
empcourse
courseno’s
SELECT courseno
FROM employee, empcourse
WHERE surname = 'Roberts'
AND forenames = 'Robert'
AND employee.empno = empcourse.empno; --link the tables
2.List the name and date of courses Robert Roberts has been on :-
‘Robert’
‘Roberts’
employee
empno
empcourse
courseno
cname
cdate
course
SELECT cname, cdate
FROM employee, empcourse, course
WHERE surname = 'Roberts'
AND forenames = 'Robert'
AND employee.empno = empcourse.empno --link the tables
AND empcourse.courseno = course.courseno; --link the tables
3.(tutorial 2/10)List all the names and current positions of employees who have been on any of the courses that Robert Roberts has been on :-
Courses for rest
‘Robert’
‘Roberts’
employee rest
employee RR
surname
forenames
RR.empno
rest.empno
courseno’s
empcourse restcourse
empcourse RRcourse
rest.empno
jobhistory
position
USING SELF JOINS
SELECT DISTINCT rest.surname, rest.forenames, position
FROM employee rest, employee RR,
empcourse restcourse, empcourse RRcourse,
jobhistory
WHERE RRcourse.empno = RR.empno -- Robert's courses
AND RR.surname = 'Roberts'
AND RR.forenames = 'Robert'
AND restcourse.courseno = RRcourse.courseno -- others on his courses
AND rest.empno = restcourse.empno
AND rest.empno != RR.empno -- excluding Robert
AND rest.empno = jobhistory.empno -- current positions
AND enddate is NULL;
USING A SUBENQUIRY
SELECT DISTINCT rest.surname, rest.forenames, position
FROM employee rest, empcourse restcourse, jobhistory
WHERE
restcourse.courseno IN -- Robert's course numbers
(SELECT RRcourse.courseno
FROM employee RR, empcourse RRcourse
WHERE RRcourse.empno = RR.empno -- Robert's courses
AND RR.surname = 'Roberts'
AND RR.forenames = 'Robert')
AND rest.empno = restcourse.empno -- others on Robert's courses
AND NOT(rest.surname = 'Roberts' -- excluding Robert
AND rest.forenames = 'Robert')
AND rest.empno = jobhistory.empno -- current positions
AND enddate is NULL;
3.(tutorial 2/10 - again )List all the names and current positions of employees who have been on any of the courses that Robert Roberts has been on.
This solution uses a VIEW to extract the courses which Robert Roberts has been on, and also to make a note of his employee number. This view is then used like any other table, to support the main select enquiry. Since this view is only required temporarily, it is advisable to “tidy up” before and after this enquiry has been completed:-
Create Temporary View
Courses for Robert
Courses for rest
‘Robert’
‘Roberts’
employee rest
employee
surname
forenames
empno
rest.empno
empcourse restcourse
empcourse
rest.empno
courseno, empno
jobhistory
position
RobertsCourses VIEW
DROP VIEW robertsCourses; -- Clean Start
CREATE VIEW robertsCourses AS -- Courses Robert Roberts has (SELECT courseno, employee.empno -- been on, and his empno
FROM employee, empcourse
WHERE empcourse.empno = employee.empno
AND surname = 'Roberts'
AND forenames = 'Robert'
);
SELECT DISTINCT surname, forenames, position -- Main Enquiry
FROM employee, empcourse, jobhistory,
robertsCourses
WHERE empcourse.courseno = robertsCourses.courseno
AND empcourse.empno = employee.empno
AND empcourse.empno = jobhistory.empno
AND employee.empno != robertsCourses.empno
AND enddate is NULL;
DROP VIEW robertsCourses; -- Drop the temporary view
Dostları ilə paylaş: |