Assignment 1: Table Queries Create a SQL Server database for Kudler Fine Foods Virtual Organization.
Using the SQL CREATE statement, create the following tables using the fields listed below:
Note: Supply the SQL Server data types when creating the tables. In the Employee table, create an Employee ID field that will generate a unique number for each employee and designate the field as the Primary Key. In the Job Title table, use a listed fields as the table’s primary key or create an additional field to use as the primary key. The primary key from the Job Title table appears as the foreign key in the Employee table.
• Employee
Employee ID
Last name
First name
Address
City
State
Telephone area code
Telephone number
Employer Information Report (EEO-1) classification
Hire date
Salary
Gender
Age
Foreign key from Job Title table
• Job_title
EEO-1 classification
Job title
Job description
Exempt / non-exempt status
Using the SQL INSERT statement:
• Go to the Human Resources department in the Kudler Fine Foods Virtual Organization. Using information found in the Employee Files for the La Jolla and Encinitas stores, enter records into the employee table for the following employees:
Glenn Edelman
Eric McMullen
Raj Slentz
Erin Broun
Donald Carpenter
David Esquivez
Nancy Sharp
• Use the information from the Kudler Fine Foods Job Classifications and Job Descriptions to enter records into the Job Title table for the following job titles:
Accounting Clerk
Assistant Manager
Bagger
Cashier
Computer Support Specialist
Director of Finance & Accounting
Retail Assistant Bakery & Pastry
Retail Assistant Butchers and Seafood Specialists
Stocker
Check the results by selecting all of the columns from both tables. Non-Exempt employees at Kudler Fine Foods are paid an hour ly wage and are required to track their working hours.
Copy all SQL statements and tables (screen shots) into a Word document. Include a title page with your name, course and assignment # and submit the document to the Assignments link
Assignment 2: Data Changes and SQL Statements Use the database and tables from last week’s assignment to write SQL statements and enter records into the Employee table for workers identified in the employee files for the administrative offices and the Del Mar store. Check results by selecting all columns from both tables.
•
Use the database and tables from last week’s assignment to write SQL queries using Between, Like and Union. Write a SQL query that does the following:
• Joins two tables in the example database and uses BETWEEN to restrict record selection. Use salary to restrict the data.
• Joins two tables in the example database and uses BETWEEN to restrict record selection. Use hire dates to restrict the data.
• Joins two tables in the example database and uses LIKE to restrict record selection. Use telephone area codes to restrict data.
• Joins two tables in the example database and uses LIKE to restrict record selection. Use age to restrict data.
• Uses the UNION of the two tables to produce a third table.
Copy each query and its results (screen shots) in a Word document and post it to the Assignments link.
Use the updated database to write the following queries, using the SQL GROUP statement:
• Select employees’ last names and group them by EEO-1 Classification.
• Select employees’ last names and group them by salary.
• Select employees’ last names and group them by salary within their EEO-1 Classification.
• Select employees’ last names and group them by salary within job titles, grouped into exempt and non-exempt.
Copy the SQL statements and the results (screen shots) in a Word document and post it to the Assignments link.
Assignment 3: SQL Reports Use the database and tables from last week’s assignment to write queries using the SQL SELECT statement.
Note: Select all of the data from both of your tables before you perform the following.
• Increase all employees’ salaries with the selected EEO-1 classification by 10%.
• Increase all employees’ salaries by 5%.
• Choose an employee from the Employee table and delete that employee.
Copy the SQL statements and the results (screen shots) in a Word document and post it to the Assignments link.
Use the database and tables from last week’s assignment to write SQL statements that do the following:
• Calculate the average salary for all employees.
• Calculate the maximum salaries for exempt and non-exempt employees.
• Calculate the maximum salary for all employees.
• Calculate the minimum salaries for exempt and non-exempt employees.
• Calculate the minimum salary for all employees.
Copy the SQL statements and the results (screen shots) in a Word document and post it to the Assignments link.
