Using table relationships in sql select statements



Yüklə 59,5 Kb.
tarix16.08.2018
ölçüsü59,5 Kb.
#63139

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 Robert


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





Yüklə 59,5 Kb.

Dostları ilə paylaş:




Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©genderi.org 2024
rəhbərliyinə müraciət

    Ana səhifə