theUnderDog.ca

• Home
• Tutorials
• Math
• Computer Science
• Database
• Links
• About

Oracle Data Insertion

Sample Oracle queries

Note: some queries are not completely correct!

Note: due to < and > tags, the relational calculus is not displaying correctly. I'll make those changes later.

Assignment 2

*** Note ***
Completed
Feb 18/09
***

Key: 
P = project
S = select
E = 'is in'
NE = 'not in'
JOIN = join
EXISTS = 'there exists'
FORALL = 'for all'
-> = 'implies'


Part I
1) List all the information about each teacher. 

select *
from teachers;

Relational Algebra:
Teachers

Relational Calculus:
{<i,n,a,e,p>: E Teachers}

2) List the name of each teacher who can teach either the subject with the subjectID "Math 10", or the subject with the subjectID "Science 10" or both. 

SELECT DISTINCT tname
FROM teachers T, 
qualifications Q
WHERE T.teacherID = Q.teacherID and
	(Q.subjectID = 'Math 10' or Q.subjectID = 'Science 10');

Relational Algebra:
P(tname)((S(subjectID = 'Math 10')Qualifications)
	JOIN (S(subjectID = 'Science 10')Qualifications) 
	JOIN Teachers)

Relational Calculus:
{: EXISTS t ((( E Qualifications) 
	OR ( E Qualifications))
	AND ( E Teachers))}	


3) List the name of each school who offers the subject with the subjectID "Math 10", but not the subject with the subjectID "Science 10". 

SELECT S.sname
FROM schools S,
offerings O
WHERE S.schoolID = O.schoolID
AND O.subjectID = 'Math 10'
AND O.schoolID NOT IN
	(SELECT O2.schoolID
	FROM offerings O2
	where O2.subjectID = 'Science 10');

Relational Algebra:
P(sname)((((S(subjectID = 'Math 10')Offerings)
	JOIN (Schools))
	- ((S(subject = 'Science 10')Offerings))
	JOIN (Schools)))

Relational Calculus:
{: EXISTS s ((( E Offerings)
	AND ( NE Offerings))
	AND ( E Schools))}

4) For each teacher, list the teacher's name and the subjectID of each subject he/she is qualified to teach. 

SELECT T.tname, Q.subjectID
FROM qualifications Q,
teachers T
WHERE Q.teacherID = T.teacherID;

Relational Algebra:
P(tname,subjectID) (Qualifications JOIN Teachers)

Relational Calculus:
{: EXISTS i (( E Teachers) 
	AND ( E Qualifications))}

5) For a school named "Denbridge Secondary School", list the teacherID and the name of each teacher who had at least one assignment in this school in the month of January 2008. The result should not show any duplicates. 

SELECT DISTINCT T.tname, T.teacherID
FROM assignments A,
teachers T,
schools S
WHERE A.teacherID = T.teacherID 
and S.sname = 'Denbridge Secondary School'
and S.schoolID = A.schoolID
and assignmentDate >= to_date('2008-01-01','yyyy-mm-dd')
and assignmentDate <= to_date('2008-01-31', 'yyyy-mm-dd');

Relational Algebra:
P(teacherID, tname) ((S(sname = 'Denbridge Secondary School)Schools)
	JOIN (S(assignmentDate >= 2008-01-01)Assignments)
	JOIN (S(assignmentDate <= 2008-01-31)Assignments)
	JOIN Teachers)

Relational Calculus:
{: EXISTS(s, date) (( E Schools)
	AND (<_,i,s,_,date,_> E Assignments)
	AND (date >= 2008-01-01)
	AND (date <= 2008-01-31)
	AND ( E Teachers))}

6) List the subjectID of each subject a school named "Denbridge Secondary School" does not offer. 

SELECT DISTINCT F.subjectID
FROM offerings F
WHERE F.subjectID NOT IN
	(SELECT F2.subjectID
	FROM offerings F2,
	schools S2
	WHERE S2.sname = 'Denbridge Secondary School'
	and S2.schoolID = F2.schoolID);

Relational Algebra:
P(subjectID) ((Subjects)
	- (S(subjectID = 'Denbridge Secondary School')Subjects))

Relational Calculus:
{<\s>: EXISTS (i) (( E Offerings)
	AND () NE Teachers))}

7) List the teacherID and the name of each teacher who, so far, has never had an assignment in the school named "Denbridge Secondary School". 

SELECT DISTINCT T.teacherID, T.tname
FROM teachers T,
assignments A
WHERE A.teacherID = T.teacherID
and A.teacherID NOT IN
	(SELECT A2.teacherID
	FROM assignments A2,
	schools S2
	WHERE S2.sname = 'Denbridge Secondary School'
	and S2.schoolID = A2.schoolID);

Relational Algebra:
P(tname, teacherID) (Teachers) 
	- (P(tname, teacherID) (teachers, assignments, schools(sname = 'Denbridge Secondary School')

Relational Calculus:
{: FORALL (s) (( E Teachers)
	AND (<_,i,s,_,_,_> E Assignments)
	-> ( NE Schools))}

8) For each assignment done for the subject with the subjectID "Science 10", list the name of the teacher, the name of the school, the date the assignment happened and the number of hours the assignment lasted. 

SELECT T.tname, S.sname, A.assignmentDate, A.hours
FROM assignments A,
teachers T,
schools S
WHERE A.subjectID = 'Science 10'
and A.teacherID = T.teacherID
and A.schoolID = S.schoolID;

Relational Algebra:
P(tname,sname,assignmentDate,hours) ((Teachers)
	JOIN (Schools)
	JOIN (S(subjectID = 'Science 10')Assignments))

Relational Calculus:
{: EXISTS(i,c) (( E Teachers)
	AND ( E Schools)
	AND (<_,i,c,'Science 10',d,h> E Assignments))}


Part II

1) List the highest hourly wage offered by any school for any subject.

SELECT max(hourlyWage)
FROM Offerings;

2) A school named "Denbridge Secondary School" wants to find someone to teach a subject with the subjectID "Math 11". List the name, phone number and email of each teacher who is qualified to teach this subject, and the preference of this teacher gives to this school. Order the display according to the preference descending. 

SELECT DISTINCT T.tname, T.phoneNo, T.email, P.preference
FROM Teachers T,
Preferences P,
Schools S
WHERE T.teacherID = P.teacherID 
AND S.sname = 'Denbridge Secondary School'
AND S.schoolID = P.schoolID
AND T.teacherID IN
	(SELECT Q2.teacherID
	FROM Qualifications Q2
	WHERE Q2.subjectID = 'Math 11')
order by P.preference desc;

3) For each teacher, list his/her name, and the number of subjects he/she is qualified to teach. If a teacher is not qualified to teach any subject yet, his/her name should still be on the list and the number of subjects should be shown as 0. 


SELECT T.tname, NVL(SubQ.mycount, 0) AS NumSubjects
FROM Teachers T
LEFT JOIN
	(SELECT T2.teacherID, count(Q2.teacherID) AS MYCOUNT
	FROM Qualifications Q2,
	Teachers T2
	WHERE T2.teacherID = Q2.teacherID
	GROUP BY T2.teacherID) SubQ
ON T.teacherID = SubQ.teacherID;

4) List the name of each school that offers ALL the subjects. 

SELECT DISTINCT S.sname
FROM Schools S
WHERE S.schoolID IN
(SELECT O2.schoolID
FROM Offerings O2
GROUP BY O2.schoolID
HAVING count(*) >= 
	(SELECT count(*) as MyCount
	FROM Subjects S2
	)
);

5) For each teacher, count the total number of hours he/she teached in January 2008. 

SELECT T.teacherID, T.tname, sum(A.hours)
FROM Teachers T,
Assignments A
WHERE T.teacherId = A.teacherId
GROUP BY T.teacherID, T.tname;

6) List the name of the school that offers the most subjects. 

SELECT S.sname
FROM Schools S
WHERE S.schoolID IN
	(SELECT O.schoolID
	FROM Offerings O
	GROUP BY O.schoolID
	HAVING count(*) = (
		SELECT max(count(O1.subjectID))
		FROM Offerings O1
		GROUP BY O1.schoolID)
	);

7) List the name of each teacher who teached more than 300 hours in the year 2008. Order the display according to the teacher's total working hours in 2008 descending. 

SELECT T.tname
FROM Teachers T
WHERE T.teacherID IN
	(SELECT A2.teacherID
	FROM Assignments A2
	GROUP BY A2.teacherID
	having sum(A2.hours) > 300);

created and © by Jer K
theunderdog.ca