POS 410 Week 3 Lab Two

SQL Lab 2

Due Week 3 Day 7

Post a Microsoft Word document including these statements and named “Lastname – Lab 2.doc”. (change Lastname to your last name)

Using the database and tables from Lab One, do the following:

• Insert ten more records into each table.  Verify your inserts by selecting all rows from each table.

• Write SQL queries using BETWEEN, LIKE and UNION as follows:

1. Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use salaries to restrict the data.)

2. 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. 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.)

4. Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use zip codes to restrict data.)

5. Write a SQL query that uses UNION of the two tables using at least one column from each table.

• Write queries using the SQL GROUP statement to produce the requested reports as described below.  In general, to make grouping meaningful, a function such as COUNT or SUM is used.  In the below tasks, COUNT(*) is the desired function to include in your SELECT statement along with the requested fields.

6. Display total number of employees for each job title.  Columns should include Job_Title and ‘Total Employees’. (Hint: use the “AS clause” to rename a column in the result set.)

7. Display total number of employees for each salary.  Columns should include Salary and ‘Total Employees’.

8. Display total number of employees for each salary within each job title.  Columns should include Job Title, Salary, and ‘Total Employees’.

9. Display total number of employees for each salary grouped by exempt status.  Columns should include Exempt, Salary, and ‘Total Employees’.

• Include all SQL statements (i.e create, insert, etc) used to complete each task of your assignment. Do not include screenshots, rather include the SQL scripts in the Word document. Label each script with the number of the query and use the order in which each query is listed in the assignment.