Week 3 Individual Assignment: Add Data, JOINS and Queries

Read and follow the instructions carefully and completely for submission of the Week 3 Individual Assignment.

REQUIRED: Prepare ALL Code per the Coding Standards document. Refer to the attached handout Coding Standards for POS 410 - before proceeding with this assignment.

Use the database and tables from last week's assignment. You will be writing SQL to ADD more records to the tables created in Week 2 assignment and SQL queries to display information from these tables.

REQUIRED: ALL code must be commented. See Learning Activity Tutorial on Creating a Table for writing comments for each code set.

------------------------------------------------------------------------------------------------------------------

Assignment Request: ENTER NEW RECORDS into the Employee table for workers identified in the Week 3 Data Sheet Kudler Fine Foods attached - DATA: Required Inserts.

REQUIRED: Create the Code, execute the code without errors and take a screen shot of the code and message showing the code in the current screen and message "X rows affected." Copy the code to the NotePad file created for this week, paste the screen shot in the Word Document.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: ENTER MORE NEW RECORDS: You have just been hired by Kudler Fine Foods as the new Chief Technical Officer. There will be Two (2) additional inserts provided in the attached Data Sheet.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing the code in the current screen and message "X rows affected."

*****NOTE: IF YOU MADE CHANGES TO YOUR WEEK 1 AND/OR 2 CREATE TABLE CODE, MAKE SURE TO SUBMIT THE CORRECTED CODE WITH THE WEEK 3 ASSIGNMENT SO THAT I HAVE THE MOST CURRENT CODE THAT WILL EXECUTE WITHOUT ERRORS!!!!******

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Check results by SELECTing all columns from both tables. Use this code sample to help write the code:

SELECT *

FROM TableName

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing the code in the current screen and results of the table(s) (2 screen shots)

-----------------------------------------------------------------------------------------------------------------

JOINS, Between, Like, and Union (Join your two tables to create these queries):

NOTE: One SQL Query contains only one SELECT statement and only one FROM statement and may use other statements and clauses to complete the query. See sample queries in handouts and extras. You will select the values for each query.

REQUIRED: Use Each Assignment Request Below As The "Comment" For Each Query. Copy and paste the Assignment request between 2 multi-line comment indicators. A multi-line comment indicator is a slash and an asterisk - the comments - and then an asterisk and slash: /*comments*/: Example: /* Assignment Request: Write one SQL query that Selects all the columns in both tables and JOINs the two tables in the database you created last week and uses BETWEEN to filter record selection. Use Date of Birth to filter data.*/

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

REQUIRED: ALL queries must return results that fit the query request. When 3 tables are joined, then all the data must be displayed for all three tables. The number of records will differ depending on the type of join used.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one SQL Query to JOINall three tables and select all the data from the tables.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

----------------------------------------------------------------------------------------------

Assignment Request: Write one SQL query that Selects all the columns in all three tables and JOINs the three tables in the database you created and uses BETWEEN to filter record selection. Use Date of Birth to filter data.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one SQL query that Selects all the columns in all three tables and JOINs the three tables in the database you created and uses BETWEEN to filter record selection. Use No. of Exemptions to filter data.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one SQL query that Selects all the columns in all three tables and JOINs the three tables in the database you created and uses LIKE to restrict record selection (a wild card is required to receive full credit). Use Job Title to filter data.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one SQL query that Selects all the columns in all three tables and JOINs the three tables in the database you created and uses LIKE to filter record selection (a wild card is require to receive full credit). Use First Name to filter data.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one SQL query that Selects all the columns in all three tables and JOINs the three tables in the database you created and uses an expression (>, <, >=, <=) to restrict record selection. Use Date of Hire to filter data. Add an ORDER BY clause and order the results by the Last Name.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one SQL query that uses the UNION JOIN of the two of the tables. (Note: Output should have at least 2 columns from each table to receive full credit)

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

GROUP BY: Use the updated database to write the following separate queries, using the SQL statement "GROUP BY" without aggregate functions: JOIN tables where needed.

REQUIRED: Use Each Assignment Request Below As The "Comment" For Each Query. Copy and paste the Assignment request between 2 multi-line comment indicators. A multi-line comment indicator is a slash and an asterisk - the comments - and then an asterisk and slash: /*comments*/: Example: /* Assignment Request: Write one SQL query that Selects all the columns in both tables and JOINs the two tables in the database you created last week and uses BETWEEN to filter record selection. Use Date of Birth to filter data.*/

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one (1) SQL query to display employees' last names and the Job Title and GROUP Job Title

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one SQL query to display employees' last names, Date of Hire and Age and GROUP BY Age and Date of Birth.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one SQL query to display employees' last names, their Job Classification and their No. of Exemptions and GROUP BY ages in their Job Classification.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

Assignment Request: Write one SQL query to display employees' last names, Date of Hire and their job classification and GROUP BY Date of Hire in Job classification, grouped into Marital Status.

REQUIRED: Create the Code, execute the code without errors and take a screen shot showing each query code set in the current screen and results of the query. Copy the Code to your NotePad file created for this week. Paste the screen shot into the Word document you created for this week.

-----------------------------------------------------------------------------------------------------------------

REQUIRED: Write a 1-page paper (500 words minimum, but not more than 1,000) discussing what challenges you encountered as you joined your first tables and wrote your first queries, and what resources in class or outside of class that were most helpful to you?

-----------------------------------------------------------------------------------------------------------------

DUE MONDAY! WHAT TO SUBMIT:

Review the Grading Rubric before you upload and submit the Assignment.

Note: There will be two (3) Attachments for this assignment (Please DO NOT ZIP files)

Submit only One (1) Notepad attachment with "all" code and One (1) Word document with all screenshots, and One (1) 1-page retrospective paper.

NOTE: submitting only 1 record in your INSERT statement for the table will result in loss of points and request to resubmit.

Notepad file includes code in this order:

• All commented SQL Code for additional 12 Inserts properly formatted per the Coding Standards document
• All commented SQL Code for query joining the three tables properly formatted per the Coding Standards document
• All commented SQL Code for 6 separate queries properly formatted per the Coding Standards document
• All commented SQL Code for 4 separate Group By statements properly formatted per the Coding Standards document

*****NOTE: IF YOU MADE CHANGES TO YOUR WEEK 1 AND/OR 2 CREATE TABLE CODE, MAKE SURE TO SUBMIT THE CORRECTED CODE WITH THE WEEK 3 ASSIGNMENT SO THAT I HAVE THE MOST CURRENT CODE THAT WILL EXECUTE WITHOUT ERRORS!!!!******

Word Document includes screen shots of the executed code (without errors) in this order:

• Screen shot of the execution of the code to insert the records into the Job Title table
• Screen shot of the execution of the code to insert the records into the Employee table
• Screen shot of the code and table results from selecting the Store table
• Screen shot of the code and table results from selecting the Job Title table

• Screen shot of the code and table results from selecting the Employee table
• One (1) screen shot each of each Query with query code and results output
• One (1) screen shot each of each Query with GROUP BY code and results output
• NOTE: There will be a minimum of 14 screen shots required for this assignment

Note: There will be two (3) Attachments on the Assignments page (Notepad file with Code, Document with Screenshots, 1 page retrospective/challenges paper)