If you could check statements in the sql and make correction I would be happy. I added the statements that you helped me with at the end. These statements are located in the middle.
(3.1) Using the database and tables from Week Two, write SQL statements and enter the records into the employee table for the workers identified in the Employee Files for the administrative offices and the Del Mar location.
(3.2) Check the results by selecting all of the columns from both of your tables. USE INSERT statements to enter the new records.
Using the database and tables from Week Two (including the new records just added), write SQL queries using BETWEEN, LIKE:
(3.3) Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use salary to restrict the data.)
(3.4) Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use hire dates to restrict the data.)
(3.5) Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use telephone area codes to restrict data.)
(3.6) Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use age to find all people in their 20's.)
(3.7) Write a SQL query that uses UNION of the two tables to produce a third table. Be creative.
Using the updated database, write the following queries using the SQL GROUP statement and appropriate aggregate statements (when needed):
(3.8) Write a SQL query that displays the average salary / hourly wage grouped by job_title.
(3.9) Write a SQL query that displays the number of people in each EE0-1 Classification and the average salary / hourly wage for each classification.
(3.10) Group employees by job classification: Select the employees' last names and group them by EEO-1 Classification
(3.11) Group employees by salary within their job classification: Select the employees' last names and group them by salary within their EEO-1 Classification.
(3.12) Select the employees' last names and group them by salary within job titles that are grouped into exempt and non-exempt.
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".
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.
select eeo_1_classification, salary, l_name from EMPLOYEE order by eeo_1_classification, salary DESC
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 ...
In this solution I demonstrate several SQL queries on a database. The queries demonstrate simple SELECT syntax as well as more complicated uses of WHERE and GROUP BY.