13 Aug Assignment

ITCO231 – Unit 4 Submission Assignment

Your name: Click here to enter text.

Overview

There are two parts to this assignment.

In Part 1, you will be take your Unit 2 design and create it in SQL Server. Be sure to correct the issues I pointed out in the Unit 2 grading feedback.

In Part 2, you will write SQL that retrieves data from the AdventureWorksLT database you created in Unit 2. I’ve included screen shots that show what the query results should look like. Your goal is to write SQL statement that produce the same results.

Instructions

1. Watch the mini-lesson: “Unit 4 – What to Submit”

2. Save this file to a place where you can edit it and include your last name in the file name. For example: LoCoco-ITCO231-Unit-04-Submission-Assignment.

3. Add your full name to the “Your Name” field above.

4. Do not delete any other text or images that came with this template. Including these instructions.

5. In this document you will paste the following in the appropriate input boxes:

· SQL text (highlight the SQL in your Management Studio, copy and paste in the designed boxes in t his document)

· SQL screen shots

· Database Diagram screen shot

· Script for the entire databse.

6. Be sure your:

· screen shots are readable at up to 120% zoom.

· Your entire SQL statements are visible. In other words, I should be able to read your entire SQL statement. This means that in Management Studio, you may have to type your SQL statements on muliple lines.

7. For the AdventureWorksLT queries, I included model results. Your results should match. If your results do not match, then your query is incorrect.

8. When complete, ZIP this file along with the other files the assignment specifies.

Part 1

The steps listed below, come directly from the assignment page.

Step 6 – Generate and save the [SQL Server] database diagram to ensure that your database matches your [revised unit 2 ERD] design.

Paste your database diagram here.

Step 8 – Write Select statements that will display all of the data from each of your tables.

Paste your SELECT SQL and results as screen shots here. Crop your images so just the SQL statement and the results are shown (no need to see all the columns in the results). You will have one screen shot per table.

For Example:

Step 9 – Write a Select statement that will use a WHERE clause.

Paste your SELECT SQL and results as screen shots here. Crop your images so just the SQL statement and the results are shown (no need to see all the columns in the results). Your WHERE clause must actually retrieve fewer records than the same SELECT SQL without the WHERE clause.

Step 11 – Generate your database script file [for the entire database]

REQUIREMENT: Follow the instructions I’ve demonstrated and provided in week 2: “Generate Scripts for Entire Database.pdf”

SUGGESTION: Be sure your script contains your create tables, relationships and your sample data. I suggest that you read: “Database-Script-How-to-Read-It.pdf”

Paste the script for the entire database here.

Part 2

Using the AdventureWorks database, write the following queries:

Query# 2.1. Display the ProductID, Name, StandardCost, and ListPrice for all rows in the Product table. Use column aliases of “ID”, “Name”, “Cost”, and “List Price”.

Paste your SQL here.

Paste a screen shot of your results here. Be sure I can see the number of records returned in the lower right hand corner of the screen shot.

Your screen shot should look similar to this:

Query# 2.2. Display the CustomerID, full concatenated customer name (with spaces between the names), and Company Name for all customers in the Customer table. Add a column alias to the full customer name column.

Paste your SQL here.

Paste a screen shot of your results here. Be sure I can see the number of records returned.

Your screen shot should look similar to this:

Query# 2.3. Display the first and last names of all customers whose company is Friendly Bike Shop sorted by the customers’ last name.

Paste your SQL here.

Paste a screen shot of your results here. Be sure I can see the number of records returned.

Your screen shot should look similar to this:

View # 2.4. Create a view that will display the unique Cities, along with the StateProvince and CountryRegion columns from the Address table. Name the view unique_city_vu.

Step 1

Paste your SQL to create the VIEW here.

Step 2

Write a SELECT statement to retrieve data from your view and copy/paste a screen shot of the SELECT statement and the results in here.