CIS 411w spring 2017 Problem Set 9 Due Date: 3/16/2017

1. Log on to your Oracle Apex account.

Go to this web site:

https://www.briandunning.com/sample-data/

and find this link for the free person data file:

Save this file to your Z drive or USB as person_data.csv.

2. In Oracle Apex, go to SQL Workshop Utilities Data Workshop

Select Spreadsheet Data

This is going to be a new table, and we want to upload a file rather than copy and paste. Navigate to find the file in your Z or USB drive and add to the text box for Optionally Enclosed by. The table name will be person_data.

For the primary key, allow the system to take care of that with a sequence.

3. Go to SQL Commands and create a new query that will show the state and count() from each state. GROUP BY state.

Copy and paste your SQL into your assignment.

Restrict the groups returned to just state codes that start with an N or a C. There are two ways to do this:

3a. Use a WHERE clause to filter the result set down to just the desired states and then group.

3b. Use a HAVING clause to restrict the groups that ultimately appear in the result set, without using a WHERE clause.

Copy and paste your SQL into your assignment for both 3a and 3b.

4. Create a SQL query that shows the last name, first name (put these together with a , in between them), the company_name, city, state and email address. In other words concatenate the values into one column named name, and then add the other columns as you would in a regular SQL select.

Copy and paste your SQL into your assignment.

5. Put this SQL in a SQL script. Modify the SQL so that it outputs the results with the values from each column separated by a ,. Here we are create a CSV (comma separated value) flat from the data that we have in the person_data table. you will have to modify the SQL query create in exercise 4, but it does give you a clue about how to format the result set.

Copy and paste your SQL into your assignment.