Sql queries using SELECT, LIKE, GROUP BY

Solution Preview

---

I looked over the SQL statements in the provide file. Answers 3.1 - 3.7 were already done in the file. I marked those in the file. Answers 3.8 - 3.12 were not there so I added them.

For question 3.8 you are supposed to find the average salary for each job title. This is done with a "group by" clause on the select. The "group by" clause aggregates rows into a single row. This is useful for tasks such as counting or averaging.

The select that does this is as follows:

select job_title, avg(salary) from EMPLOYEE join JOB_TITLE on EMPLOYEE.JOB_TITLE_id = JOB_TITLE.JOB_TITLE_id group by job_title

First we are selecting the job_title and the average salary. We could select just the average salary but the we wouldn't know what job that average was related to. Notice that there is no other where clause in the statement. All of the rows from the joined tables will be selected. The "group by" clause directs SQL to group all of the rows with the same job_title together and then run the aggregate function average on that subset of rows. So we end up with a table that shows job_title and the average salary for that job.

Problem 3.9 is very similar to 3.8 but we use the "count()" aggregate function instead:

select eeo_1_classification, count(salary), avg(salary) from EMPLOYEE group by eeo_1_classification

The last three problems don't use the group by clause. The problems state to select all last names in the tables and then have the data grouped by certain fields. If the "group by" clause is used then we can't have all of the last names since some rows of data will get aggregated together. Instead, in order to group the rows together in the output we will use "order by".

This query:

select eeo_1_classification,l_name from EMPLOYEE order by eeo_1_classification

selects all of the last names in the EMPLOYEE table and orders the results by the classification field. So all of the data is "grouped" together.

We use the same reasoning for the next two problems but with multiple order fields.

// 3.11
select eeo_1_classification, salary, l_name from EMPLOYEE order by eeo_1_classification, salary DESC

// 3.12
select JOB_TITLE.eeo_1_classification, exempt, salary, l_name from EMPLOYEE join JOB_TITLE on EMPLOYEE.JOB_TITLE_id = JOB_TITLE.JOB_TITLE_id order by exempt, JOB_TITLE.eeo_1_classification, salary DESC

These queries show the power of using "group by" and "order by" to get more advanced query results.

The entire sql file, with these queries added, is provided below.

-------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table ...