CS 2550 SQL Assignment #2
SQL SELECT Statement Functions

Perform SQL SELECT statements using Oracle Functions

Description

Write a series of Oracle SQL query statements using the Student database which consists of the schema defined in Appendix D of your text.

 

This assignment will be completed using Oracle. The CS Athena remote terminal server has both SQLDeveloper and SQL*Plus installed and available for student use.
Instructions for connecting to the CS Oracle Server through the remote terminal server can be found at
http://rbhilton.com/wsucs/cs2550/CS2550ConnectionInstructions.pdf

 

 

 

Complete the assignment by performing the following

  1. Connect to the WSU/CS Oracle Server, or any other available Oracle server.
    Instructions for connecting to terminal server and logging on to SQL*Developer will be posted.
    General instructions for using SQL Developer are contained in Lab 2.3 of your text.

  2. Create a notepad text file named CS2550SQL02.txt. Put your name and assignment information at the top of the file.
    Paste each of your SQL statements into the file, numbering each one.
    Make sure that each SQL statement is terminated with a semi-colon.

    It is essential that your submitted file is named as specified

  3. Complete the following Queries:
    1. Provide an alphabetical list of the full name and phone number of all students that work for 'Baxxon Corp.'
      (the full name should be displayed as one column with an alias of 'Student Name')
    2. Provide a list of student employers that are corporations (have "Corp." in their name).
      List each employer only once and arrange the the list alphabetical order.
    3. Provide an alphabetical list of students in area code 203. List student name in the format <last name (all upper case)>, <first initial>. ( Example, LOCKE, J. ) followed by the phone number.
    4. List the name and address of all students that don't have a phone number.
    5. Provide a list of zip codes for New York, NY.
    6. List the course number and location for all courses taught in building 'M'.
    7. Provide a list of cities, state abreviations and full state names from the zip code table for MA, CT and WV. (You'll need to use the CASE expression).
    8. Create a listing containing single column address (salutation, first name, last name, address, zip) as 'Instructor Address' for each instructor in zip code 10025.
    9. List the student id and final exam score for each student in section 87. List the scores from highest to lowest.
    10. List the student ID, final exam (FI) score and exam result ('PASS' or 'FAIL') for all students in section 103.
      A final score of 85 or higher is required to pass.


Upload the completed query file CS2550SQL02.txt using the Assignment Uploader prior to the due date