X hits on this document

Word document

For the exam you should be able to - page 4 / 11





4 / 11

Q16: For each project, retrieve the project number, the project name, and the number of employees who work on that project.

Q17: For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.

Q18: Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10% raise.

Q19: Retrieve all employees whose address in Houston, TX....

Q20: Find all employees who were born during the 1950s.

Q21: For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.

3. You are given the following relational schema:



Write the following queries in relational algebra and calculus:

  1.find all the directors of movies where Tom Hanks starred together with Jodie Foster;

  2.find all the actors that starred in more than one movie of the same director;

  3.find all the actors that didn't appear in any movie between 1970 and 1980.

  4.find the actors that starred in all movies directed by Hitchcock;

  5.find the actors that starred in exactly the same movies as Jimmy Stewart;

  6.find the actors with the longest career span (the difference between the year of the latest and the year of the earliest movie of the actor).

4. Consider the following relational schema. An employee can work in more than one department; also, the pct_time field of the Works relations shows the percentage of time that a given employee works in a given department.

Emp(eid integer, ename string, age integer, salary real) WorksIn(eid integer, did integer, pct_time integer) Dept(did integer, dname string, budget real, manager_eid integer)

where bold column names indicate that the column if part of the table's primary key.

Write SQL92 integrity constraints (primary key, foreign key, or check constraints), as well as SQL99 triggers (as needed) to ensure that each of the following requirements are met. The enumerated items below should be considered independently, and you should show the appropriate table definitions per listed item to satisfy its requirements (note, only show the table definitions that are impacted by the listed item).


Employees must make a minimum salary of $1,000


Every manager must also be an employee. Show this using two differnt approaches ... the first using foreign keys, the other using only check options (no foreign key).


The total percentage of all appointments for an employee must be under 100%; an appointment relates to the percentage of time an employee is assigned to work in a department.

Document info
Document views49
Page views49
Page last viewedSat Jan 21 05:08:03 UTC 2017