Examples for Query Processing

1
Examples for Query Processing
2
Queries in Relational Algebra

1. Find the titles of ITEC courses
2. Find the students (IDs and names) who have been
   registered for the course IT1010
3. Find the students who are enrolled in ITEC
   program courses
4. Find the students who have been enrolled in at
   least two courses

3
Contradictory Queries

• Find the students who are older than 47.
• SELECT S.sid, S.sname FROM Student S WHERE
  S.Y_of_B lt 1966
• Find the students who have been registered for
  both ITEC and BA courses
• SELECT S.sid, S.sname FROM Student S WHERE S.sid
  IN (SELECT T.sid FROM Transcript T, Course C
  WHERE T.cidC.cid AND C.PcodeITEC AND
  C.PcodeBA)

4
Incorrectly Formulated Query

• Find the students who have marks higher than 75
  and are younger than 25
• SELECT S.sid, S.sname
• FROM Student S, Transcript T
• WHERE S.Y_of_B gt 1988 AND T.Mark gt 75

5
View Resolution

• CREATE VIEW Young_Student AS
• SELECT FROM Student S WHERE S.Y_of_B gt 1984
• SELECT YS.sid, YS.sname
• FROM Young_Student YS, Transcript T
• WHERE YS.Y_of_B gt 1984 AND T.Mark gt 75 AND
  YS.sidT.sid

6
Simplification

• Consider the following table
• Employee (EID, Ename, Ocp)
• Find employee who are
• NOT(OcpManager) AND (OcpManager OR
  OcpClerk) AND (NOT (OcpClerk)) OR
  EnameSmith