IT$350$M$1-1$: Apply fundamental SQL programming concepts.

Purpose

You will install Microsoft SQL Server Express and SQL Server Management Studio $($SSMS$)$ if you do not already have them installed. This module provides an overview of SQL$,$ and reviews IT$234$ content. You will work with fundamental SQL statements to manage and work with data in the sample database.

Assessment Instructions

It is especially important that you watch the Module $1$ videos associated with SQL prior to completing the assessment. Navigate to the Academic Tools area of this Module and select Library then Required Readings to access your texts and videos. You will generate a Microsoft Word report from the completed work.

You will need to install and use Microsoft SQL Server Express and Microsoft SQL Server Management Studio $($SSMS$)$ for this course. You can download the latest versions of these free software products here:

Microsoft SQL Server Express

Microsoft SSMS

The sample database for this module is called BikeStores. The database creation script, installation instructions, and database diagram can be downloaded below:

Create BikeStores Database Script

Instructions for Establishing the BikeStores Database

Bikestores Database Design Diagram

Use the BikeStores database design diagram to formulate an SQL solution for each of the assessment problems. A single SQL statement must be used to generate each problem result.

You will generate a Microsoft Word report from the completed work. Please copy the numbered problem statement into your Microsoft$$ Word$$ report.

Below that, enter the SQL text you used to solve the problem.

Below that, enter a screenshot of the execution of the SQL showing the code and the result set.

Start each problem on a new page.

Problem $1$: Display the bike names and list prices for bikes costing $$5000$ or more. Order the results in descending order based on list price.

Expected Output:

Image displaying bike names and list prices for bikes costing $$5000$ or more.

Problem $2$: Present a list of the bikes made by a company called $$Haro$$ in alphabetical order.

Expected Output:

Image displays a list of the bikes made by a company called "Haro" in alphabetical order.

Problem $3$: Select customer records for customers residing in the $14450$ zip code.

Expected Output:

Image displays the customer records for customers residing in the $14450$ zip code.

Problem $4$: List the first and last names of staff members whose manager is Mireya Copeland.

Expected Output:

Image displays the first and last names of staff members whose manager is Mireya Copeland.

Problem $5$: The company is now selling bikes from a company called GT Bicycles. Enter a new record into the database to reflect this change.

Expected Output:

Image displays the new record for the $"$GT Bicycles" company.

Problem $6$: Update the amount of stock for the bike with product$\_$id $=198$ at the store with store$\_$id $=2.$ The new stock amount should be $20.$

Expected Output:

Image displays the updated stock amount for product$\_$id $=198$ at store$\_$id $=2.$

Problem $7$: Show bike names and the total sales amounts for transactions involving bikes costing $$10000$ or more. The total sales amount is the product of the bike order item list price, quantity sold, and sale discount.

Expected Output:

Image displays the bike names and the total sales amounts for transactions involving bikes costing $$10000$ or more.

Problem $8$: Provide a distinct list of the states in alphabetical order where the company$$s customers reside.

Expected Output:

Image displays a distinct list of the states in alphabetical order where the company's customers reside.

Problem $9$: Display the full names of all staff personnel in First$\_$Name, $,$ Last$\_$Name format. Use the built$-$in CONCAT$()$ function for this query.

Expected Output:

Image displays the full names of all staff personnel in First$\_$Name, $,$ Last$\_$Name format.

Problem $10$: Show the bikes that have zero stock at the Texas $($TX$)$ store.

Expected Output:

Image displays the bikes that have zero stock at the Texas $($TX$)$ store.