Simple Queries Use the Unit 3 Assignment Template, linked in the Resources, to document and submit your assignment. Your completed assignment will be graded using the following criteria: Design simple...

Simple Queries

Use the Unit 3 Assignment Template, linked in the Resources, to document and submit your assignment. Your completed assignment will be graded using the following criteria:

• Design simple queries that meet the organization's needs.
• Design five additional queries that use single-row functions from each category.
• Demonstrate the ability to use scripts in SQL statements and generate successful results. You will achieve this by providing screenshots of your reports.
• Refer to the Simple Queries scoring guide to ensure that you meet the grading criteria for this assignment.

Assignment Overview

For this assignment, you will create at least 10 simple queries that support SmartHomes that have the data directly correlating. You will choose to use Microsoft SQL Server Management Studio or the SQL Command Line to build your simple queries. In some ways, it will help you double-check that you have all the data elements matching up with one another between the tables.

SmartHomes has asked you to generate the SQL SELECT statements for a few reports it would like to include on the administrative side of its website. Before you can do this, you must populate the database with some dummy data. Add the data using the SQL Developer table view. There should be at least five rows in each table with 10 orders. Each order should have a mix of 1–3 products being ordered. The picture in the product should be a URL, such ashttp://www.capella.edu/images/product1.jpg, and be unique for each product.

These queries will be used to help build the reports directed to upper management.

In preparation for this assignment, it is important to understand the SELECT statement as well as the ways to filter data. Also, you should be familiar with SQL programming language and single-row functions. Use the suggested resources (linked in the Resources) or other research to learn more about these topics.

• Sampson, A. (2017).Filter and modify data in SQL Server 2016[Tutorial]. Skillsoft Ireland.
  • Sorting and Filtering SQL Server 2016 Data (39 minutes).
  • Unknown Values (14 minutes).
• Sampson, A. (2017).Using functions in SQL Server 2016[Tutorial]. Skillsoft Ireland.
  • 1 hour, 36 minutes.
• Sampson, A. (2018).Querying and manipulating data[Tutorial]. Skillsoft Ireland.
  • 2 hours, 14 minutes.

Assignment Instructions

For this assignment, complete the following SQL statements:

1. A list of all customers ordered by last name, first name with the first name, and last name concatenated together with a space in between.
2. A list of all orders with the date ordered output as MONTH DD, YYYY.
3. A list of all products that will initialize the first letter of each word on the product name.
4. A list of all orders that have been ordered in the current month.
5. A list of products with their prices rounded to the nearest dollar.
6. A list of the total price of each line item based on the price and quantity.
7. A list of all customers with the first name that starts with a specific letter (your choice).
8. A list of products with the cost more than $10.
9. A list of all customers with only the first four characters of their last name.
10. A list of products with the picture path removing the full path and only including the filename.

Export the database including the data and your SELECT statements. Submit these along with screenshots of their execution in a Word document in a report format using the template file.