OPIM 5272

Fall 2014, University of Connecticut

Practice Exam
Problems 1-20 requires you to compose an SQL query which will report a given output. The queries
that you design should not work only for the data that exists in the table - the design of the query
should be independent of the data that is in the table, but work according to how the database is
set up.
For the following 3 questions, you are required to design a data model and draw it in Visio.
According to the conventions discussed in class, you must indicate (when applicable) strong /
weak (must be indicated if the entity can only exists because of another entity, not just due to
a one-to-many relationship) / associative entities, primary keys, foreign keys, required / optional
attributes, simple / composite attributes, NO multi-valued attributes, regular / derived attributes
(if an attribute can be derived from other attributes it must be drawn as such), weak / strong
relationships, subtype / supertype entities and discriminators, attribute types, etc. Each entity
/ relationship / attribute should have a name indicating what it represents and must be easily
understood by the name that you assign, except to associative entities when this relationship is
easy to understand. Note that associative entities only arise from many-to-many relationships, and
from ternary (or higher degree relationships). If the problem states that you are to only include the
entities listed but can in addition include associative entities, recall that associative entities may
arise only from many-to-many relationships or from higher-degree relationships.
This practice exam is longer than you should expect the actual final exam to be. Good luck.

Problem 1
Write an SQL query that will display the job ID and the year (in four digit format) that the job
ended for each job listed in the job history table where the start date of the job was before and
not including 1990.

Problem 2
Write an SQL query that will display the job ID, start date, and number of days between my birth
date (May 19, 1983) and the start date for each job listed in the job history table.

Problem 3
Write an SQL query that outputs the last name, department ID, and commission percentage of each
employee in the employees table whose commission percentage is not NULL. Order the outputs in
increase department ID order, with those instances with a NULL department ID appearing first.

Problem 4
Write an SQL query that outputs the last name and department ID of each employee in the
employees table whose salary is greater than or equal to $7,000.00. Include only those employees
that have a non-NULL department ID.

Problem 5
Write an SQL query that will create a table named copy employees in your database that contains
all of the data in the employees table for those employees whose department ID is not NULL.

Problem 6
For the table created in Problem 5, write a query or set of queries that will set the department ID
to 200 for each instance in the table that has department ID 60.

Problem 7
For the table created in Problem 5, set the primary key attribute to be the same primary key
attribute as the table that this table was based off of. Call this constraint copy PK (Note: if you
have NULL values for the primary key attribute because of the queries composed in the previous
problems, you can drop the table and run your answer to Problem 5 again to recreate the table).

Problem 8
For the table created in Problem 5, pick one of the attributes that is a foreign key to another table
in the database. Write a query that sets the foreign key constraint - call this constraints copy fk.

Problem 9
Write an SQL query that outputs one row for each location in the locations table, and reports the
postal code, city, and country name of the country that it is in. The output should have exactly one
row for each location, even if the country ID in the locations table is NULL, whereupon a NULL
value should be reported for the country name. Note that even if the country ID is specified for
each instance, your query should work if there were instances that had a NULL country ID.

Problem 10
Write an SQL query that outputs one row for each location in the locations table that has a
non-NULL country ID, and reports the postal code, city, and country name of the country that
it is in. Also, the output should include a row for each country that is not associated with any
location in the locations table, using NULL as the postal code and city. Note that even if the
country ID is specified for each instance, your query should work if there were instances that had
a NULL country ID.

Problem 11
Write an SQL query that outputs the last name and previous job title for each job listed in the
job history table for which there is an employee in the employees table that is still currently
employed. Order the output by job title.

Problem 12
Write an SQL query that, for each job in the job history table, outputs the end date of the job,
the job title, the employees last name that had the job, and the name of the department in which
the job was housed for every job in the job history table regardless of whether or not an employee
is currently listed in the employee table.

Problem 13
Write an SQL query that outputs the last name and salary of each employee in the employees table
that has a salary greater than the salary of the employee with employee ID 124 (You cannot simply
look up the salary and put that in the query - the query must find the salary of the employee with
employee ID 124 and use the result of that to run the query).

Problem 14
Write an SQL query that outputs the last name and salary of each employee in the employees
table that has a salary greater than the salary of an employee which is inputted via a substitution
variable and specified by an employee ID.

Problem 15
Write an SQL query that outputs the department IDs of the departments in the employees table
whose employees have an average salary that is greater than or equal to the average salary of the
employees in any other department. For example, suppose there were two department IDs, d1 and
d2. Suppose that the average salary of all employees with department ID d1 is $10,000.00, and that
the average salary of all employees with department ID d2 is $9,000.00. The query should output
d1.
Problem 16
Write an SQL query that outputs the number of full years that have elapsed since a date which
will be specified by a substitution variable in Oracles stadard DD-MON-RR format.

Problem 17
Write an SQL query that outputs one column for each employee in the employees table. The
output should report the last name, in possessive form, and describe the employees commission
percentage. The output should be exactly of the following form, which depends on whether or
not an employee has a NULL commission percentage or not. If, for example, I was an employee,
and I had no commission percentage, the output should read Bergmans commission percentage
is not specified., and if I had a commission percentage of say .15, the output should read
Bergmans commission percentage is .15..

Problem 18
Write an SQL query that will display the last name and country code of each employee in the
employees table that has an international phone number. Note that a phone number is either
domestic or international. If it is domestic it contains 10 numbers and 2 periods (with the first 3
numbers being the area code), and if it is international it contains 15 numbers and 3 periods (with
the fourth and fifth numbers being the country code).

Problem 19
Write an SQL query that will display two columns based only on the employees table. The first
column will display the department IDs for the employees listed in this table. The second column
should display the maximum salary of all employees with that corresponding department ID. Count
towards this maximum only those employees whose hire date is before January 1st, 1995. Display
an output only for those departments that have a maximum salary of over $10,000.00. Order the
outputs in decreasing order of department ID.

Problem 20
Write an SQL query which outputs two columns. The first column should output the last name
of each employee in the employees table that is the manager for some other employee, and the
second column should be the number of employees that this person manages.

Problem 21
For this problem, you are to create a data model from scratch. It should contain ONLY the entities
listed at the bottom of the problem. You CANNOT create any additional entities.
An art museum owns a large volume of paintings, and wants to create a database to track which
paintings it owns and where the paintings are and have been. Each painting has a 10-character
painting code, title, and a size, all of which must be stored; size is further composed of height, width,
and weight. We also must save and/or be able to calculate the date the painting was started, the
date it was completed, the number of years since it has been completed, and the number of years
it took to complete it.
A painting is painted by one and only one artist, but the artist for some paintings is unknown. An
artist is stored in the database only if the museum owns a painting composed by the artist. Each
artist has a 10-character artist ID, name, date of birth, which all must be recorded, and date of
death, if the artist has passed away.
At any point in time, a painting may either be loaned to a temporary art show, or on display in
the museum. Each show may have on display many paintings from our art gallery, or none at all.
We want to be able to record each show that a given piece of art has ever been loaned to. There
may be many such shows, or none at all. Each show has a 10-character show ID which is unique,
and a show location (you can consider this as a simple attribute with attribute type varchar(40) you dont have to make this a composite attribute) that must be saved. The art museum also must
record the date that each show started, and the date that each show ended (or will end), which is
known for each show. The art museum wants to save information about each show that they hear
about, whether or not they have loaned any paintings to the show.
ENTITIES
PAINTING: An entity where we store information about the paintings that the art museum owns.
ARTIST: An entity where we store information about the artists that paint the paintings that the
art museum owns.
SHOW: An entity where we store information about the shows that the art museum has loaned
paintings to.
SHOW PAINTING HISTORY: An associative entity that arises between painting and show, where
an instance of this entity exists if a given instances of the painting entity was loaned to an instances
of the show entity that has already ended.

Problem 22
For this problem, you are to create a data model from scratch. You must use the entities below,
and are free to create only associative entities, if they are necessary.
A management consulting firm would like to create a database to save information about the various
offices that their employees work in throughout the country. Offices are contained in buildings. Each
building has an address which must be stored (you can consider this as a simple attribute with
attribute type varchar(40) - you dont have to make this a composite attribute) and 10-character
building ID which is assigned by the company. Each office has a unit number which is unique within
the building that it resides in and may contain letters or numbers (but always less than or equal
to 5 characters). Each building that we are storing information about has at least one office which
the management consulting company uses. Note that offices in different buildings might have the
same office number.
The management consulting company is interested in saving information about its employees as
well. Each employee has a unique 10-character employee ID, and we have to save their names (you
can consider this as a simple attribute - no need to make first and last name attributes). Each
employee is assigned to multiple offices because of how much they travel, but each office is reserved
for exactly one employee. Employees may be classified as either process owners or managers. An
employee can be only one of these, but there are other types of employees as well.
The management consulting firm has many departments. Each department has a unique 10character department ID. For this exercise you are required to store department affiliations for
only those employees that are classified as either process owners or managers. Each manager is
assigned to one and only one department, but a department can have any number of managers, but
has at least one. Finally, process owners work within three of more departments, and a department
may have any number of process owners that work in it, but may have none.
ENTITIES
BUILDING: An entity where we store information about the buildings where the management
consulting firm has employees that work.
OFFICE: An entity where we store information about the offices where employees are assigned.
EMPLOYEE: An entity where we store information about each and every employee of the company.
PROCESS OWNER: An entity where we store information about the employees that are process
owners.
MANAGER: An entity where we store information about the employees that are department managers.
DEPARTMENT: An entity where we store information about the departments of the company.

Problem 23
For this problem, you are to complete the data model that I started for you in the Visio file
Problem23-ToComplete.vsdx, which is available in HuskyCT under Exam 1. Please complete this
diagram. You do not have to include attribute types. You must use the entities below, and are free
to create only associative entities, if they are necessary. You must include relationship lines, and
any missing attributes. Also, you must indicate associative entities, primary keys, foreign keys,
required / optional attributes, simple / composite attributes, regular / multi-valued attributes,
regular / derived attribute, etc., according to the conventions we discussed in class - the only part
of the data model that you need not include are attribute types.
A real estate firm wants to create a database to better its organization and information retrieval
systems. The real estate firm lists properties for sale. The firm has a number of sales offices in
several states. Each sales office has a unique sales office ID, together with an office number and
office location.
The firm has many employees, each with a unique employee number and each with a name that we
must save. Every employee is associated with a single sales office and may have a manager, which
we must indicate in the data model and save in the database.
The firm lists properties for sale. Attributes of property include property ID (identifier) and
location. Each unit of property must be listed with one and only one of the sales offices. A sales
office may have any number of properties listed or may have no properties listed.
Each unit of property has one or more owners. Attributes of owners are owner ID (identifier)
and owner name. An owner may own one or more units of property, and each property can have
multiple owners, in which case the percent ownership of each owner must be saved (where 100% in
entered if it is owned entirely by one owner). In addition, each property has a case manager, who
is in charge of selling that property.
ENTITIES
SALES OFFICE: An entity where we store information about the sales offices that the real estate
has.
EMPLOYEE: An entity where we store information about every employee of the firm.
PROPERTY: An entity where we store information about the properties that the real estate firm
lists.
OWNER: An entity where we store information about the employees that are department managers.