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. Check the results by selecting all of the columns from both of your tables
Using the database and tables in Week Two, write SQL queries using Between, Like and Union:
• 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.)
• 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.)
• Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use city to restrict data.). Include the employee last name, first name, city, state, salary and job title in the SELECT list.
• Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use job title to restrict data.). Include the employee last name, first name, city, state, salary and job title in the SELECT list.
• Write a SQL query that uses UNION of the two tables to produce a third table. The new table should be called Names_And_Jobs. The new table should include a single field which contains either the full name of each employee in a single field (First name Last Name e.g. Aron Kuppersmith) or a job title.
• Submit the queries in a text file or Word document as an attachment to your submission.
Write the following queries using the SQL GROUP BY clause:
• Group employees by job classification: Select the employees' average salary and group them by EEO-1 Classification.
• Group employees by job title. Select the employees' total (SUM) of the salaries and group them by job title.
• Group employees by salary within their job title. Select the count of employees' and group them by salary within their job title.
• Select the count of employees' and group them by salary within job titles that are grouped into exempt and non-exempt. For example:
Status Job Title Salary Count
Exempt Manager 70,000 3
Exempt Manager 75,000 2
Exempt Owner 90,000 1
NonExempt Busser 40,000 5
NonExempt Waiter 60,000 7
• The SQL statements should be copied into a Microsoft Word document (doc or docx) or a text file with the txt extension. You should submit one file that contains all of the statements.
This assignment is due by Day 7 (Monday of Week 3) to your Individual Forum. Copy all of your SQL statements to a single Word or text file and include as an attachment. Also, include a backup of your database in the form of a BAK file. You may zip (compress) this file if you would like. See my post about making backups in the main forum