For class on 2/2/2012
Consider the following relational schema representing applicants for a faculty position:
candidate (cid, name, email, address, sex, nationality, rank)
application (cid, position, status)
decision (cid, infavour, opposed)
How would you pose each of the following queries/actions in SQL and in QBE?
1. What are the names of Canadians who applied?
a. What are the names of all applicants who are either Canadian or permanent residents?
b. What are the names of female Canadians who applied?
c. What are the names of female Canadians who applied for the "systems" position?
d. How many female Canadians applied for the "systems" position?
2. Give the names of candidates and the positions for each application having the status "under review".
3. How many people voted in favour of "John Smith"?
a. How many people voted in favour of each "systems" candidate?
b. How many people voted for each candidate with no more than 5 opposed?
c. Summarize how many people voted for and against candidates for each position.
d. On average, what was the difference in votes for and against candidates for each position?
4. Who applied for each position (giving name, rank, and email for each)?
5. Insert the decision that 20 voted in favour and 3 were opposed to the candidate with cid=215.
a. Insert the decision that 12 voted in favour and 17 were opposed to "John Smith."
6. Delete all information related to the applicant with email address notme@univ.ca.
7. Change the status of John Smith's application for the "systems" position to be "hold".
8. Use the Data Definition Language to define the application table, assuming that cid is an integer and the other two attributes are strings.