UNIVERSIDAD DE LA SALLE

FACULTAD DE INGENIERA

ASIGNATURA: SISTEMAS INFORMACIN INDUSTRIAL

PREPARADO POR: GUILLERMO CAMACHO

EJERCICIOS MODELO DE DATOS RELACIONAL Y SQL

Para resolver esta lista de ejercicios tenga en cuenta

1) Las listas deben ser desarrolladas y entregadas en grupos de 2 personas. No se evaluarn listas

individuales 2) En cada uno de las preguntas que requieran consultas debe especificar:

a) Cdigo SQL utilizado. b) Pantallazo mostrando el resultado generado por Access y/o SQLServer (minimice el nmero de

pginas!) 3) Las preguntas que no requieran consultas SQL deben ser desarrolladas basndose en imgenes y texto

descriptivo (minimice el nmero de pginas!) 4) El taller es dividido en 2 partes: (a) consultas bsicas en base de datos COMPANY y (b) consultas

intermedias en base de datos MOVIES.

La fecha de entrega de esta lista es 18 de septiembre de 2015, 11.55pm. El medio de

entrega ser foro de Moodle.

Lo que debe entregar: 1) Archivo debidamente marcado, formato .docx y con la respuesta a cada una de las preguntas 2) Archivos myCOMPANY, myMOVIES con datos, tablas y consultas implementadas. Estos archivos sern

entregados en formatos de Acess y SQLServer.

PARTE 1 - consultas bsicas Este material est basado en los talleres del curso Database Systems ofertado en Cleveland State

University durante el otoo del 2007 y dirigido por el Dr. V. Matos

In this homework you will write and test a number of SQL queries.

The database schema consists of the following tables EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno) KEY: ssn

DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate) KEY: dnumber.

PROJECT (pname, pnumber, plocation, dnum) KEY: pnumber.

WORKS_ON (essn, pno, hours) KEY: (essn, pno)

DEPENDENT (essn, dependent-name, sex, bdate, relationship) KEY: (essn, dependent-name)

I Identify the procedures to define data and constraints with the GUI (graphical user

interface) of (a) access, (b) SQLServer 1. Explain how to add a NOT NULL constraint with the GUI. 2. Explain how to configure the DEFAULT property of an attribute. 3. Explain how to implement the CHECK command defined in SQL. 4. How do you configure a foreign and a primary key from the GUI? 5. How do you add the property of UNIQUE to an attribute using the GUI?.

II To resolve this section, download the COMPANY database from the moodle (use the link

in topic 5 with name COMPANY database). You must resolve this section with Access and

SQLServer

Add New Data to the COMPANY database. Remember to use SQL commands

1. Create a new DEPARTMENT record. Its number should be 7 and the name is Enjoying Life. The managers SSN is 123456789 and his starting date is September 15, 2005.

2. Add yourself to the EMPLOYEE table. Assign yourself to department 7 and make 987654321 be your supervisor. Fill the rest of the fields (use the SSN 111-22-3333).

3. Create a new PROJECT record. The project number should be 7 and its name is Paper planes. The location is Bogot and the department in charge is 7.

4. Modify the WORKS_ON table. Add yourself to project 7 with a weekly assignment of 3 hours.

5. Add your (real or fictional) spouse and two children to the DEPENDENT table.

III Use the COMPANY database populated in the previous step. Use SQL to formulate the

following queries (note: some of them may produce empty results). You must resolve this

section with Access and SQLServer

Q01. Get the last name of the married female managers.

Q02. Get the last name of married employees who have at least one daughter and one son.

Q03. Get the last name of married employees who have no children.

Q04. Get the last name of married employees who only have daughters.

Q05. Get the last name and salary of each employee as well as the name and salary of their

corresponding (direct) supervisor. Q06. Get the last name of employees who work on two or more projects.

Q07. Retrieve the names of all employees in department 5 who work more than 10 hours per week on

the PRODUCTX project Q08. Find the name of all employees who are directly supervised by Franklin Wong.

Q09. Figure 4.5 presents examples of the operations UNION, EXCEPT AND INTERSECT. Explain the way in which each operation works. Your answer must be a descriptive text

The SQL standard defines the command LIKE to evaluate substring pattern matching. This command is complemented

with the operators in Table 1.

COMMAND DESCRIPTION

% Replace an arbitrary number of zero or

more characters

_ Replace a single character

\ Command used to precede special

characters as: %, _,

a. The operators in Table 1 are not implemented in Microsoft Access. Find the correspond operators for the functions in

Table 1

b. Probe the Access operators with the next queries:

Q10. Find all employees who were born during the 1950s

Q11. Retrieve all employees whose address is in Houston, Texas

PARTE 2 - consultas intermedias Este material est basado en los talleres del curso Database Systems ofertado en Stanford University de

forma permanente a travs de sus cursos MOOCs y dirigido por la Dr. Jennifer Widom

You've started a new movie-rating website, and you've been collecting data on reviewers'

ratings of various movies. There's not much data yet, but you can still try out some

interesting queries. You must resolve this section with Access and SQLServer

Movie ( mID, title, year, director )

Description: There is a movie with ID number mID, a title, a release year, and a director.

Reviewer ( rID, name )

Description: The reviewer with ID number rID has a certain name.

Rating ( rID, mID, stars, ratingDate )

Description: The reviewer rID gave the movie mID a number of stars rating (1-5) on a

certain ratingDate.

Instructions:

1. You must define data using the GUI of Access and SQLServer

2. You are to write each of the following queries using SQL.

Important Notes: Your queries are executed using Microsoft Access so you must conform to the SQL constructs

supported by Access. Your queries must be executed on SQLServer too.

Unless a specific result ordering is asked for, you can return the result rows in any order.

For each query present:

o SQL code (copy-paste to a MS-Word file minimize the number of pages!),

o Screen-shots showing the output (if any).

You must present the final file with queries an tables implemented: myMovies.accdb

Q1. Find the titles of all movies directed by Steven Spielberg.

Q2. Find all years that have a movie that received a rating of 4 or 5, and sort them in

increasing order

Q3. Find the titles of all movies that have no ratings.

Q4. Some reviewers didn't provide a date with their rating. Find the names of all reviewers

who have ratings with a NULL value for the date.

Q5. Write a query to return the ratings data in a more readable format: reviewer name,

movie title, stars, and ratingDate. Also, sort the data, first by reviewer name, then by movie

title, and lastly by number of stars.

Q6. For all cases where the same reviewer rated the same movie twice and gave it a higher

rating the second time, return the reviewer's name and the title of the movie.

Q7. For each movie that has at least one rating, find the highest number of stars that movie

received. Return the movie title and number of stars. Sort by movie title.

Q8. For each movie, return the title and the 'rating spread', that is, the difference between

highest and lowest ratings given to that movie. Sort by rating spread from highest to lowest,

then by movie title

Q9. Find the difference between the average rating of movies released before 1980 and the

average rating of movies released after 1980. (Make sure to calculate the average rating for

each movie, then the average of those averages for movies before 1980 and movies after.

Don't just calculate the overall average rating before and after 1980.)

Q10. Find the names of all reviewers who rated Gone with the Wind.

Q11. For any rating where the reviewer is the same as the director of the movie, return the

reviewer name, movie title, and number of stars.

Q12. Return all reviewer names and movie names together in a single list, alphabetized.

(Sorting by the first name of the reviewer and first word in the title is fine; no need for

special processing on last names or removing "The".)

Q13. Find the titles of all movies not reviewed by Chris Jackson.

Q14. For all pairs of reviewers such that both reviewers gave a rating to the same movie,

return the names of both reviewers. Eliminate duplicates, don't pair reviewers with

themselves, and include each pair only once. For each pair, return the names in the pair in

alphabetical order.

Q15. For each rating that is the lowest (fewest stars) currently in the database, return the

reviewer name, movie title, and number of stars.

Q16. List movie titles and average ratings, from highest-rated to lowest-rated. If two or

more movies have the same average rating, list them in alphabetical order.

Q17. Find the names of all reviewers who have contributed three or more ratings. (As an

extra challenge, try writing the query without HAVING or without COUNT.)

Q18. Some directors directed more than one movie. For all such directors, return the titles

of all movies directed by them, along with the director name. Sort by director name, then

movie title. (As an extra challenge, try writing the query both with and without COUNT.)

Q19. Find the movie(s) with the highest average rating. Return the movie title(s) and

average rating. (you might think of it as finding the highest average rating and then

choosing the movie(s) with that average rating.)

Q20. Find the movie(s) with the lowest average rating. Return the movie title(s) and

average rating. (you might think of it as finding the highest average rating and then

choosing the movie(s) with that average rating.)

Q21. For each director, return the director's name together with the title(s) of the movie(s)

they directed that received the highest rating among all of their movies, and the value of

that rating. Ignore movies whose director is NULL.

Q22. Add you and your homework mate to your database, with an rID of 209, 210,

respectively

Q23. Insert 5-star ratings by James Cameron for all movies in the database. Leave the

review date as NULL.

Q24. For all movies that have an average rating of 4 stars or higher, add 25 to the release

year. (Update the existing tuples; don't insert new tuples.)

Q25. Remove all ratings where the movie's year is before 1970 or after 2000, and the rating

is fewer than 4 stars.