CSc 4710/6710
Spring 2001
Practice Problems for Exam3
---------------------------

Consider the following ODL schema for a database of students applying 
for summer internships. 

interface Student (extent Students, key ID) {
  attribute integer ID;
  attribute Struct{string first, string last} name;
  relationship Set applied
  inverse Internship::applicants;
}
interface Internship (extent Positions, key (company, city)) {
  attribute string company;
  attribute string city;
  relationship Set applicants
  inverse Student::applied;
} 

Write OQL queries for each of the following. 

(a) Find the ID's of all students whose last name is "Smith". 

(b) Find the ID's and last names of all students who have applied 
    to an internship at a company in Palo Alto. Do not repeat 
    (ID,last-name) pairs in the result, even if the student has 
    applied to many internships in Palo Alto. 

(c) If you used distinct in your answer for part (b), rewrite the 
    query so you don't need to use distinct. Conversely, if you 
    didn't use distinct in your answer for part (b), rewrite the query 
    so you do need to use distinct in order to guarantee that duplicates 
    are eliminated. 

(d) Find the names of all companies in Palo Alto such that at least 
    one student S (say) with ID between 25 and 50 has applied for an 
    internship at that company, and all internships student S has applied 
    for are in Palo Alto or San Jose. 

(e) Recall that the result of an OQL query or subquery is a set or a bag. 
    OQL allows two sets (bags) to be compared using =, where two sets (bags) 
    are equal if they contain exactly the same objects. Find all pairs of 
    student ID's such that the two students have applied to internships at 
    the exact same set of companies in Palo Alto. (The students may have 
    applied to different internships at companies in other cities.) 
    Return each pair of ID's exactly once, and order the final result based 
    on the last name of the first student in each pair.