SQl oracle datebase Restrict the groups returned to just state codes that start

Question

SQl oracle datebase

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.

Explanation / Answer

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

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

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

FROM person_data;

5)
Ans:

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

FROM person_data;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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