Name ________________________________________________________________
INSY 483
Summer 2001
Test 1
Use the tables at the end of this test as the basis for answering any queries requested. As was the case with homework, I will give full credit for any reasonably optimized query but will deduct points for queries that are clearly more complicated than necessary.
Make sure that your test is not missing any pages. You should have seven pages total as follows:
Cover page 1 page
Questions 5 pages
Sample Tables 1 page
----------
Total 7 pages
The Radford University honor code should be observed at all times. You should:
â receive no aid from other students, books, or notes.
â give no aid to other students.
â report any suspected cheating to the instructor or proctor.
In a file oriented system, application programs directly access data files. How do Database Management Systems differ?
Oracle is a client/server database. This implies the following with regard to an interactive SELECT statement (pick one).
Data is passed from the server to a client running SQLplus where the SELECT query against that data can be executed. The results of the query are then displayed on the screen.
Data is selected from the client but passed to the server for processing of the SELECT command. The results are then sent back to the client machine where they are displayed on the screen.
The server SELECTS the rows indicated by any WHERE clause and passes them to the client where any search conditions based on the HAVING clause are then executed. The results are then displayed on the screen.
The SELECT command is sent from the client to the server. The command is executed on the server and the results are sent back to the client. The client than displays the results on the screen.
Use the tables at the end of this test to answer the following questions:
Which of the following are foreign keys? Select all that apply.
q Students.sid
q Faculty.sid
q DeclaredMajors.sid
q Students.fid
q Faculty.fid
q DeclaredMajors.advisorfid
SQL: The sid, last name, and gpa of all students.
SQL: The sid, last name, and gpa of all students with 3.5 or higher GPA.
SQL: The fid, last name, first name and salary of all faculty that have a salary less than $50,000 and who are also in either the INSY or CPSC department.
SQL: The average salary of all faculty.
SQL: A list of departments and the average salary of each department in order from the department with the highest average salary to the department with the lowest average salary.
SQL: A list of departments that have ten or more faculty.
SQL: A single alphabetized list of students and faculty. The list should include last name, first name, and phone number and be alphabetized by last name and within last name by first name.
SQL: The sid, and last name of any student who is an INSY major.
SQL: The sid, last name, first name, and major of all students. Students with more than one major may appear in the list more than once.
SQL: The sid and name of any student that does not have a declared major.
SQL: The sid of any student who is double majoring in CPSC and INSY.
SQL: The sid of any student who is an INSY major but not a CPSC major.
SQL: The sid of any student who is majoring in INSY and has a 3.5 or higher GPA.
SQL: The sid, last name, and number of majors for any student who has more than 2 majors.
SQL: The student with SID=123456 is majoring in INSY and has decided to also major in CPSC. Give the SQL statement necessary to add this data to the database.
SQL: All faculty in the INSY department have been granted a $2,500 raise. Give the SQL statement necessary to change the salary of these faculty.
SQL: The student with SID=111111 is majoring in both INSY and MGNT. They have decided to drop their INSY major. Give the SQL necessary to make this change to the database.
SQL: Bonus question: Use SQL to create a list of the lastname, firstname, and phone number of all students who are currently being advised by the faculty who has the last name “Jones” and the first name “Fred”.
Students {sid, lastname, firstname, phone, gpa}
Faculty{fid, lastname, firstname, phone, salary, dept}
DeclaredMajors {sid, major, advisorfid }
The first few rows of each table is shown below
Students
Sid
|
Lastname |
Firstname |
Phone |
GPA |
|
123456 |
Doe |
Jane |
555-1111 |
3.6 |
|
111111 |
Smith |
Steve |
555-5432 |
3.1 |
|
222222 |
Davis |
Sarah |
555-7676 |
2.7 |
|
Etc… |
|
|
|
|
Faculty
Fid
|
Lastname |
Firstname |
Phone |
Salary |
Dept |
|
987654 |
Parker |
Peter |
555-8181 |
49000 |
INSY |
|
777777 |
Date |
CJ |
555-4444 |
81000 |
INSY |
|
654321 |
Jones |
Fred |
555-9999 |
56000 |
CPSC |
|
222222 |
Bures |
Allen |
555-4554 |
71000 |
MGMK |
|
Etc… |
|
|
|
|
|
DeclaredMajors
|
Sid |
Major |
AdvisorFid |
|
123456 |
INSY |
987654 |
|
111111 |
INSY |
777777 |
|
111111 |
MGNT |
222222 |
|
237654 |
CPSC |
654321 |
|
877665 |
DBMS |
987654 |
|
Etc |
|
|