ps9 1. Log on to your Oracle Apex account. Go to this web site: https://www.bria

Question

ps9

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.  

Explanation / Answer

Solution:

Please find the soltions (in bold & italics with underline) alongwith the corresponding questions:

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.

SELECT count(*) AS Count_Of_Each_State, state
FROM person_data
GROUP BY state

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.

SELECT count(*) AS Count_Of_Each_State, state
FROM person_data
WHERE state LIKE 'N%' OR state LIKE 'C%'
GROUP BY state

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

SELECT count(*) AS Count_Of_Each_State, state
FROM person_data
GROUP BY state
HAVING state LIKE 'N%' OR state LIKE 'C%'

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.

SELECT ( last_name || ',' || first_name ) AS name, company_name, city, state, email

FROM person_data

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.

SELECT ( id || ',' || first_name || ',' || last_name || ',' || company_name || ',' || address || ',' || city || ',' || county || ',' || state || ',' || zip || ',' || phone1 || ',' || phone2 || ',' || email || ',' || web )

FROM person_data

Copy and paste your SQL into your assignment.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.