Database
Management
The
Professor Kirs
SQL: Sample Problems and Exercises
NOTE: It does you absolutely no good to
look at the answer before you attempt to answer the Questions.
Match the Terms on the Left with the Definitions on the
Right
|
___ AVG |
01. |
A command which is used to allow only
certain values to be entered |
|
___ ALTER |
02. |
The data type which indicates that symbols
are to be stored |
|
___ AUTHORIZATION |
03. |
The data type which is used to store months, days, years, etc. |
|
___ BETWEEN |
04. |
The data type that stores whole numbers |
|
___ CHAR |
05. |
The command to delete a table or view |
|
___ CHECK |
06. |
This is used to indicate what table is to be used in a join |
|
___ COMMIT |
07. |
The command used
to change a field’s values |
|
___ COUNT |
08. |
The function that
returns the smallest value in a series |
|
___ CREATE |
09. |
The command to link attribute values with values in a different table |
|
___ DATE |
10. |
The command to indicate the owner of the database |
|
___ DECIMAL |
11. |
A specifier which extracts
non-duplicate values |
|
___ DEFAULT |
12. |
The command used to build a table or view |
|
___ DISTINCT |
13. |
The security option that allows users to enter new records |
|
___ DROP |
14. |
The command to sort the output in some fashion |
|
___ EXISTS |
15. |
An SQL Program |
|
___ FOREIGN KEY |
16. |
The data type that allows for small whole numbers |
|
___ GRANT ALL |
17. |
A virtual table containing selected rows and columns |
|
___ GRANT INSERT |
18. |
A command that
checks if no values have been placed in a field |
|
___ GRANT SELECT |
19. |
The command which indicates a range of values |
|
___ GRANT UPDATE |
20. |
A function that returns the no. of items meeting specified
requirements |
|
___ INTEGER |
21. |
The command that checks to see if certain attribute values are
present |
|
___ IS NULL |
22. |
The security option that allows users to edit existing records |
|
___ MAX |
23. |
This is used to
indicate what the unique identifying field is |
|
___ MIN |
24 |
A function that
calculates the average value of a series |
|
___ NOT NULL |
25. |
The conceptual
organization of the entire database |
|
___ ORDER BY |
26. |
The command that indicates that fields should have non-duplicate
values |
|
___ PRIMARY KEY |
27. |
The conceptual organization of a database as seen by the user |
|
___ REFERENCES |
28. |
The command used to indicate which fields are to be considered |
|
___ ROLLBACK |
29. |
The command which indicates that a value must be placed in a field |
|
___ Schema |
30. |
The function that returns the total of value in a range |
|
___ Script |
31. |
The command to
change a table’s properties |
|
___ SELECT |
32. |
A command which places predefined values in specified fields |
|
___ SET |
33. |
The command which indicates what table is to be edited |
|
___ SMALLINT |
34. |
The security
option which allows complete access |
|
___ Subschema |
35. |
The datatype that allows for strings of a variable length |
|
___ SUM |
36. |
This command is used to negate changes made |
|
___ UNIQUE |
37. |
The function that returns the largest value in a series |
|
___ UPDATE |
38. |
The command which
makes changes to tables permanent |
|
___ VARCHAR |
39. |
The security option that allows users to only view the data |
|
___ VIEW |
40. |
The data type which is used to store real numbers |
The following are short Answer questions are not necessarily
intended as having the best solutions, but for instructional purposes. Please
note also that some questions and Answers do not necessarily transfer well into
HTML Format.
1. List
AT LEAST three reasons why SQL is considered the ‘standard’ for databases.
2. List AT LEAST Three different Data Types in
SQL, Explain What each is, when to use each, and how each is defined is SQL.
3. Explain What a Schema and a
subschema are.
4. List the major components of a
schema and explain what each means.
5. We listed 7 basic guidelines for when creating a table (e.g., Choose Numeric data types ONLY if calculations are to be performed on the field
). List 4 Other ones.
6. Show the CREATE command
necessary for the physician Table:
PHYSICIAN(physid,
name, address, age)
Where the primary key is
physid, name must not be blank, address is the composite key consisting of
street, city, state (assume that, by default, all physicians live in Texas
(TX)), and zipcode. Assume that no twp physicians can have the same
specialty. Age is the physician’s
present age, in whole numbers, as of their last birthday (to avoid the
possibility of entry error, let’s assume that a physician must be between the
ages of 21 and 90 (Doogie Howser not withstanding)).
7. In conjunction with the above
question, add the Patient table:
PATIENT(patid, name, address, balance_owed, physid)
Where patid is the primary
key. As above, name should be entered. Address is a composite attribute, the
same as the physician’s address, and this time, not only can we assume that
patients live in
8. We know that patients suffer
illnesses. A patient can suffer many illnesses, and an illness can be suffered
by many patients. The illness table need only contain the attributes illcode
(the primary key) and illname (the name given to the illness, which, again,
should not be left blank). Now, of course, we know that a patient CAN have the
same illness at two different points in time. Draw the section of the ERD which
illustrates this situation and then show the SQL commands necessary to create
the tables.
9. Assume that all of the above
tables are in place. Dr. J. Kervorkian (physid ‘555443333’, who lives at 203 N.
Cooper St., Arlington, TX, 76011, and whose specialty is Surgery (NOTE: THE
SAME AS DR. SMITH’S), and who is 45 Years old) is going to take over all of Dr.
M. Smith’s (physid ‘123456789’) patients. Her name will then be removed from
the physician table. Show all of the necessary SQL commands (be careful of the
order of commands issued).
10. Let’s assume that when you
created the patient table, you only allocated 15 characters to the patient’s
name. You now find that you need to allocate 20 characters. Show the SQL
necessary to make the change.
11. Doogie Howser has appeared. So
has a Tibetan Monk, who is also a physician and is 122 years old. You need to
change the restrictions on the physician table. A physician’s still must be 16
or greater (State law), but given increases in longevity, can be anywhere up to
200 years old (A soon to be passed State law). Show the commands necessary.
12.
Given the following ERD and
corresponding Tables, answer all of the questions that follow:
12.a. Write the script that would create the tables.
12.b. Get a
list of all the faculty members (listed by department name and professor name),
ordered by department.
12.c. Get a
list of all of the courses that a department offers (listed by course name and
department name), ordered by course name.
12.d. For
the above question, unless you added a special command, some courses (notably
database) appeared more than once on the list. Correct this situation.
12.e. Get
a list of all of the courses that each professor has taught, listed by
professor name and course name, ordered by professor name.
12.f. Get a list of classes, list by class ID and
enrollment, in decreasing order of class enrollment, where enrollment is
greater than 54.
12.g. Get a list of all classes and the department
which offered them, listed by department name, professor name, course name, and
class name, where enrollment has been greater than 54, ordered by department
name, professor name, course name, class ID, and enrollment.
12.h. There are TWO illegal records (NOTE:
If there are 2 duplicates, only one of them is illegal). Which are they and
why.
12.00 Consider the following Situation:
Each
season, the El Paso Ballet Company puts on a number of ballets. Each ballet has
a Ballet_Name (Primary Key), Composer, and Choreographer. Each ballet has many
roles. Some roles may appear in many ballets (a common example is that “Girl
#1” is a role in many ballets). Each role may be played by any number of
Dancers (although a role can only be danced by one dancer at a time). Many
dancers may portray many roles, and the same dancer may portray many roles.
Because a dancer may dance the same role many times, we need to keep track of
the date the dancer portrayed the role. Since there is no rule about two
dancers having the same name, dancers are identified by a unique Dancer_Number
(Primary key), as well as their names, of course.
After much
consideration, the DB designer has come up with what appears to be a simple
solution. She has determined that a role is simply an associative relationship
between a ballet and the individual dancer.
To further
illustrate the situation, here is some of the data which might be found (across
all three tables) for the ballet “Romeo and Juliet”:
|
Ballet_Name |
Composer |
Choreo-grapher |
Dancer_Number |
Dancer_Name |
RoleName |
DanceDate |
|
Romeo
& Juliet |
Prokofiev |
McMillan |
1 |
Nureyev |
Romeo |
|
|
Romeo
& Juliet |
Prokofiev |
McMillan |
2 |
Makarova |
Juliet |
|
|
Romeo
& Juliet |
Prokofiev |
McMillan |
3 |
Farrell |
Juliet |
|
|
Romeo
& Juliet |
Prokofiev |
McMillan |
4 |
Baryshnikov |
Romeo |
|
a. Develop the ERD to show this
situation.
b. Write the SQL Commands to Create the
Tables
c. Populate the tables with the data
above
d. Write the SQL
Command which would produce the following Output:
Dancer Date Danced
-------------------------------------------------
-----------------
Makarova
Farrell
e. The
DB Designer in the previous program just found out that she had made a bad
assumption. She assumed that each ballet had a unique name, single composer,
and single composer. As it turns out, a ballet such as “Romeo and Juliet” has
two different composers (Prokofiev and Tchaikovsky), and a variety of
composers. Some of the different composer/choreography combinations she found
were:
|
Composer |
Choreographer |
|
Prokofiev |
McMillan |
|
Prokofiev |
Ashton |
|
Prokofiev |
Cranko |
|
Tchaikovsky |
Ashton |
|
Tchaikovsky |
Lavrovsky |
Nonetheless, she feels that it is a reasonably easy fix. The
concatenated key in table ballet as follows:
ballet (ballet_name, composer,
choreographer)
She feels that this should be sufficient to uniquely
identify any ballet. Since these are already fields in the table all she has to
do is change the primary key to correspond to this situation.
Show how the new ERD would look.
f. Write
the commands needed to recreate the tables based on the new ERD (don’t forget
to drop the old tables before recreating them).
g. What happens? Why?
h. How could the problem be solved?
i. Rewrite
the ERD to reflect this situation
j. Rewrite the table
creation commands.
13. Given the following ERD:
13a. List
the order in which the tables must be created and explain why.
13b. Write the SQL
command necessary to create table CLASS.
13c. Get a list of all COURSES
(by name) Professor Hernandez (ProfID ‘456789012’) has taught and the semester
in which they were taught.
13d. Get
a list of all the classIDs (ordered by semester) the Information Systems
Department (DeptID ‘INSY’) has offered.
13e. Get
a list of students (by name) who have had Professor Johnson (‘Johnson, J.C.’).
If a student took more than one class with Professor Johnson, list them only
once.
13f. Get
a list of students (by name) who took the course ‘Database Management’ with
Professor Chan (‘Chan, C.’) in the Spring, 2001 semester (‘2001, S1 - Spring’).
13g. Get
a list of all students (by name) , the name of course they took, who taught it
(by Professor Name), and when they took it, who have taken courses from the
Management Department (‘Management’) AFTER the Fall, 2000 Semester (‘2000, S3 -
Fall’) ordered by the semester in which they took the course.
13h. Get
a list of all students, the name of course they took, who taught it, and when
they took it, who have taken courses from the Management Department
(‘Management’) AFTER the Fall, 2000 Semester (‘2000, S3 - Fall’) ordered by the
semester in which they took the course.
14. Given the following ERD,
write the necessary SQL commands to answer the questions that follow.
Additional
Notes:
1. Visiting Team and Home Team are foreign keys
which refer to the primary key Team_ID in the table Hockey_Team.
2. HT_Score refers the to Home Team’s Score.
VT_Score refers to the visiting team’s score.
3. StartDate refers to when a player started
playing for a team. If a player is still playing with a team, the field EndDate
contains the value NULL
14.0. Write the SQL Script to create the tables.
14.1. Given the following data, write the commands to insert the data
into the tables.
|
Team ID |
Team Name |
|
1234 |
|
|
2345 |
|
|
3456 |
|
|
4567 |
NY Rangers |
|
5678 |
|
|
GameID |
DatePlayed |
HomeTeam |
VisitingTeam |
HTScore |
VTScore |
|
0001 |
|
|
|
4 |
3 |
|
0002 |
|
NY Rangers |
|
3 |
5 |
|
0003 |
|
|
|
2 |
2 |
|
0004 |
|
|
|
5 |
1 |
|
0005 |
|
NY Rangers |
|
4 |
0 |
|
0006 |
|
|
|
5 |
2 |
|
0007 |
|
|
NY Rangers |
4 |
4 |
|
0008 |
|
|
|
2 |
1 |
|
0009 |
|
|
|
2 |
6 |
|
0010 |
|
|
|
0 |
3 |
|
0011 |
|
|
NY Rangers |
4 |
2 |
|
0012 |
|
|
|
2 |
6 |
|
0013 |
|
|
NY Rangers |
1 |
1 |
|
0014 |
|
|
|
4 |
0 |
|
0015 |
|
NY Rangers |
|
2 |
5 |
|
0016 |
|
|
|
3 |
3 |
|
0017 |
|
|
|
2 |
6 |
|
0018 |
|
|
NY Rangers |
1 |
1 |
|
PlayerID |
Player Name |
Position |
Team |
Start Date |
End Date |
|
98765 |
Hull, Bret |
Center |
|
|
|
|
87654 |
Jagr, J. |
Wing |
|
|
|
|
76543 |
Belfours, E. |
Goalie |
|
|
|
|
65432 |
Orr, B. |
Defense |
|
|
|
|
65432 |
Orr, B. |
Defense |
|
|
|
|
54321 |
Borque, R. |
Wing |
|
|
|
|
43210 |
Bathgate, A. |
Wing |
|
|
|
|
32109 |
Hasek, D. |
Goalie |
|
|
|
|
21098 |
Perot, G. |
Center |
|
|
|
|
10987 |
Martin, R. |
Wing |
|
|
|
|
10987 |
Martin, R. |
Wing |
|
|
|
|
09876 |
Robert, R. |
Wing |
|
|
|
|
99887 |
Horton, T. |
Defense |
|
|
|
|
88776 |
Schoenfeld, J. |
Defense |
|
|
|
|
77665 |
LaFleur, G. |
Wing |
Montreal |
01/05/97 |
01/01/01 |
|
77665 |
LaFleur, G. |
Wing |
NY Rangers |
01/01/01 |
|
|
66554 |
Plante, J. |
Goalie |
Montreal |
10/17/84 |
|
|
55443 |
Richard, M. |
Center |
Montreal |
03/22/01 |
|
|
44332 |
LaPointe, G. |
Wing |
Montreal |
12/04/95 |
|
|
33221 |
Robinson, L. |
Defense |
Montreal |
06/12/00 |
|
|
22110 |
Strudland, B. |
Wing |
Montreal |
08/15/93 |
02/11/01 |
|
22110 |
Strudland, B. |
Wing |
Buffalo |
02/11/01 |
|
|
11009 |
Gretzky, W. |
Center |
NY Rangers |
08/12/91 |
|
|
00998 |
Park, B. |
Defense |
NY Rangers |
02/11/01 |
|
|
98876 |
LeMieux, M. |
Wing |
NY Rangers |
11/16/97 |
|
|
87765 |
Esposito, P. |
Wing |
NY Rangers |
04/01/01 |
|
|
76654 |
Ruutu, C. |
Wing |
NY Rangers |
02/12/90 |
01/01/01 |
|
76654 |
Ruutu, C. |
Wing |
Montreal |
01/01/01 |
|
|
65543 |
Parent, B. |
Goalie |
NY Rangers |
04/17/88 |
11/12/00 |
|
54432 |
Esposito, T. |
Goalie |
Chicago |
11/12/00 |
|
|
43321 |
Hull, Bobby |
Wing |
Chicago |
06/14/83 |
12/04/00 |
|
43321 |
Hull, Bobby |
Wing |
Dallas |
12/04/00 |
|
|
32210 |
Savard, D. |
Center |
Chicago |
09/20/97 |
|
|
21109 |
Howe, G. |
Wing |
Chicago |
11/06/87 |
|
|
10098 |
Lindsey, T. |
Defense |
Chicago |
12/01/00 |
|
14.a. Get a list of the dates (ordered by the date
of the game) that
14.b. Get a list of all of the teams (ordered by
team name), and the dates played, that
14.b2. Get A list of all of the Players (ordered by
Player_Name) who have ever played for the Buffalo Sabres (Team Name = ‘
14.c. Get A list of all of the Players (ordered by
Player_Name) who played for the Buffalo Sabres (Team Name = ‘
14c2. Get a list (ordered by date played) of all
games that ended in a tie.
14.d. Get a list of all of the team names and the
dates (ordered by date) that
14d2. Get a list of all players who have played in
tie games (ordered by player name). Do NOT duplicate player names.
14.e. Get a list of all the players (ordered by
Player_Name), who have played against
14.f. Get a list of all of the teams (and the
date of the game) that beat
14.g. Get a list of all the players from the
Buffalo Sabres (Team Name = ‘Buffalo’) who played against Dallas in winning
games at home (in Buffalo) in the months of either January or March of 2001
(Note that both January and March have 31 days: i.e., 01/01/01 to 01/31/01 and
03/01/01 to 03/31/01). Notice also that some players who played with
NOTE: The following questions
(although not necessarily difficult) might be beyond the knowledge given in the
introductory course). Try Them!!
100. Consider the following ERD:
**
The notation used has been altered to save space
102. In what order must the tables be created?
104. Show the SQL commands necessary to create
table Building. Show all Primary and Foreign Keys. Assume that no two buildings
can have the same name, and must be included in the record. Also assume that
most of the most of the classes are held in the ‘
105. Enter the following data into table Building:
|
BuildID |
BuildName |
|
10 |
COBA |
|
11 |
UGLC |
|
12 |
|
107. Show the SQL commands necessary to create
table Classrooms. Show all Primary and Foreign Keys. Assume that no fields can
be left empty. Make sure capacity is greater than zero (0).
108. Ah!! What a moron!! I
forgot to tell you to make sure that two (or more) room numbers are assigned to
the same building. Add the constraint (without dropping the table) to do this.
109. Enter the following data
into table Classrooms
|
Classroom |
Building |
RoomNum |
Capacity |
|
1 |
10 |
301 |
40 |
|
2 |
10 |
302 |
35 |
|
3 |
10 |
303 |
35 |
|
4 |
10 |
321 |
20 |
|
5 |
10 |
320 |
30 |
|
6 |
11 |
306 |
50 |
|
7 |
11 |
307 |
45 |
|
8 |
11 |
308 |
30 |
112. Show the SQL commands necessary to create
table Semester. Show all Primary and Foreign Keys. Remember that this is simply
unique information about a semester. Each semester should have a unique name
(e.g., Fall 2003), and unique starting and ending times (e.g., two semesters
can not start on,
114. Enter the following data into table Semester
|
SemID |
Semname |
Semstart |
Semend |
|
100 |
Fall 2002 |
|
|
|
102 |
Spring 2003 |
|
|
|
103 |
Summer I 2003 |
|
|
116. The way we laid things out has caused us a
few problems. Remember the relationship between department and Instructor?
Which one
gets created first?
The answer is Either one, BUT we can’t add the foreign
key (for at least one of them) until they are both created. Here’s how we can
do it:
1.
Create Table Department (without the foreign key reference)
2.
Create Table Instructor (with the foreign key reference)
3.
Alter table Department by adding the foreign key reference
Just a few
additional considerations:
q
Table Department should not accept any null values, and
department name should be unique
q
Table Instructor should also not accept any null values
118. Enter the following data into table
Department
|
DeptID |
DeptName |
DeptChair |
|
100 |
IDS |
345678901 |
|
101 |
Accounting |
567890123 |
|
102 |
Finance |
678901234 |
120. Enter the following data into table Instructor
|
InstID |
InstName |
Dept |
|
123456789 |
Kirs |
100 |
|
234567890 |
Joseph |
100 |
|
345678901 |
Udo |
100 |
|
456789012 |
Bagchi |
100 |
|
567890123 |
Eason |
101 |
|
678901234 |
Braun |
102 |
122. Create tabe Course. There should be no null values
allowed and Course Name shoule be unique.
124. Enter the following data into table Course
|
CourseID |
CourseName |
Depart |
|
100 |
Database |
100 |
|
101 |
Data Structures |
100 |
|
102 |
Systems Analysis |
100 |
|
103 |
Data Comm. |
100 |
|
104 |
C++ |
100 |
|
105 |
Accounting |
101 |
126 Create table Course. The constraints need a
little explanation:
q
Classes can only meet on certain days:
o
MWF, MW and TR
q
Depending on what day they meet, the starting times are
different:
o
MWF classes can start at
o
MW classes can start at
o
TR classes can start at
q
The cap must be greater than zero (0)
q
No two classes can be scheduled in the same room at the same
time
q
An instructor can not teach two classes at the same time
Write the SQL Commands needed
128. Enter the following data into table Class
|
ClassID |
CourseID |
InstID |
Semester |
Days |
Times |
Room |
Cap |
|
5000 |
100 |
123456789 |
100 |
MWF |
|
1 |
40 |
|
5001 |
102 |
345678901 |
100 |
TR |
|
2 |
50 |
|
5002 |
100 |
123456789 |
100 |
TR |
|
1 |
30 |
|
5003 |
101 |
123456789 |
100 |
TR |
|
3 |
35 |
|
5004 |
100 |
123456789 |
102 |
MW |
|
2 |
25 |
|
5005 |
102 |
456789012 |
102 |
TR |
|
4 |
40 |
|
5006 |
103 |
234567890 |
102 |
TR |
|
5 |
50 |
130. Create table Major. Very simple: No null
values, unique Major Name
132. Enter the following data into table Major
|
Major ID |
MajorName |
Dept |
|
100 |
CIS |
100 |
|
101 |
POM |
100 |
|
102 |
Accounting |
101 |
134. Create table Student. Also very simple: Major
can be undeclared
136. Enter the following data into table Student
|
StudentID |
StudentName |
Major |
|
987654321 |
Simpson,
Homer |
100 |
|
876543210 |
Cheney,
Dick |
101 |
|
765432109 |
Lopez,
Jennifer |
100 |
|
654321098 |
Williams,
Venus |
100 |
|
543210987 |
De La
Hoya, Oscar |
102 |
|
432109876 |
Bush, |
101 |
|
321098765 |
Seinfeld,
Jerry |
NULL |
|
210987654 |
Ming, |
100 |
|
109876543 |
Abul-Jabar,
Kareem |
102 |
|
098765432 |
Enem,
Em |
100 |
|
001122334 |
De
Lion, Dan |
100 |
|
112233445 |
Kafka,
Franz |
101 |
|
667788990 |
Galt,
John |
102 |
|
223344556 |
Powell,
Colin |
102 |
|
334455667 |
Fox,
Vincente |
100 |
|
445566778 |
|
101 |
|
556677889 |
Toledo,
Alejandro |
100 |
138. Create table Enrollment. Some considerations:
q
Grades can be NULL, A, B, C, D, F, I, W
q
A student can not be enrolled in the same class twice
140. I have inserted 48 records into table
Enrollment. I won’t ask you to enter them. To see the entries, click HERE. To download a Zipped ASCII File of ALL the data
inserted (for all the tables), click HERE.
170. Suppose that we wanted to drop all of the
tables. In what order must we do it (CAUTION:
REMEMBER THAT DEPARTMENT AND INSTRUCTOR BOTH REFER TO EACH OTHER)
174. Get a list of all instructors (by name) and
the department (by name) that they belong to ordered by department and within
department, by instructor name) (NOTE: We know there are only six Instructors)
178. Get a list of all Buildings and the classrooms in them (and their
capacities) that have a capacity of 40 or more, listed alphabetically by
building (NOTE: There are three)
182. Get a list of all the unique courses that each instructor has
taught (listed by instructor and course name) (NOTE: There should be 5 lines of
output)
186. Get a list of all the courses (as “Course Name”, not coursename)
that the IDS department offered in “Spring 2003” (NOTE: There should be three)
190. How many students have been in each of the classes offered? (NOTE: We know that there have only been 7
classes)
194. Get a list of all CIS majors (by name, alphabetically) who took
‘Database’ in ‘Spring 2003’ with ‘Kirs’. (NOTE: There are 4)
198. How many of these student received an ‘A’? (NOTE: There was only one)
202. Get a list of all of the courses offered (by name), the
department that offered it (by DeptName) the semester they were offered (by
SemName), who taught it (by InstName), where it was offered (by BuildName and
RoomNumber), what the room capacity was, and what the department Cap was (while
you’re at it, clean up the labels a bit) (Remember, there were only seven classes
offered)
206. Write a VIEW to link all the tables together (so we won’t have to
go through this again)
210. Use your view get a list of all ‘CIS’ majors (alphabetically)
214. Use your view get a list get a class transcript for ‘Simpson, Homer’
218. Use your view get a list get a list of all classes (by
coursename) that have been taught in ‘COBA’ room ‘301’, when they were taught
(by semester name) and who the instructor was for the course