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.

1.      In a file oriented system, application programs directly access data files.  How do Database Management Systems differ?

 

 

 

 

 

 

 

 

 

  1. 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:

 

  1. 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

 

  1. SQL:   The  sid, last name, and gpa of all students.

 

 

 

 

 

  1. SQL:   The sid, last name, and gpa of all students with 3.5 or higher GPA.

 

 

 

 

 

 

 

  1. 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.

 

 

 

 

 

 

 

 

 

  1. SQL:   The average salary of all faculty.

 

 

 

 

 

 

  1. 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.

 

 

 

 

 

 

 

 

  1. SQL:   A list of departments that have ten or more faculty.

 

 

 

 

 

 

 

 

 

  1. 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.

 

 

 

 

 

 

 

 

 

 

  1. SQL:   The sid, and last name of any student who is an INSY major.

 

 

 

 

 

 

 

 

 

 

 

  1. 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.

 

 

 

 

 

 

 

 

 

  1. SQL:   The sid and name of any student that does not have a declared major.

 

 

 

 

 

 

 

 

  1. SQL:    The sid of any student who is double majoring in CPSC and INSY.

 

 

 

 

 

 

 

 

 

 

 

  1. SQL:   The sid of any student who is an INSY major but not a CPSC major.

 

 

 

 

 

 

 

 

 

 

  1. SQL:   The sid of any student who is majoring in INSY and has a 3.5 or higher GPA.

 

 

 

 

 

 

 

 

 

 

  1. SQL:   The sid, last name, and number of majors for any student who has more than 2 majors.

 

 

 

 

 

 

 

 

 

 

 

  1. 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.

 

 

 

 

 

 

 

 

  1. 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.

 

 

 

 

 

 

 

 

  1. 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