Description of this paper

BIS345 Lab 4 - with screenshots - Guaranteed A+

Description

solution


Question

BIS-345: Data Analysis for Business;Lab 4 Part 1: Table Joins and functions;Overview;Using the Northwind database, you have been requested to select data for specific needs. Once selected, your data will be presented to management. You will have to determine which fields are the most appropriate to be selected. Do not include any fields that management does not need to see.;Below is a screenshot of the Northwind Database schema;;;Log into SQL Server and access the database engine. Once into SQL Server's database engine, select the Northwind database as usual.;Part A;Northwind Traders deals with a number of suppliers. The Products table includes the supplier ID for each product. Go through the Products table and provide a count of the number of products from each company. The output should show the name of the supplier and a count of the number of products it supplies.;As you prepare to create this query, decide which information needs to be displayed ? this will be listed in the SELECT clause. Review the tables to determine which tables to use ? these will be listed in the FROM clause. This problem will require an aggregate function ? which one? What fields will be in the GROUP BY? If more than one table is needed, how will these tables be joined?;;1. Using the query window, type the SELECT statement needed to produce the desired results. The SQL is given below and after you?ve typed your query, click on the Execute button to run the query and see the results.;;The first rows of your result set should look like this;;2. Open up the Lab 4 Student Answer Sheet located in Doc Sharing, and answer the questions related to this part of the lab.;3. You must provide copies of your SQL statement and/or results. You may be asked to take a screenshot or cut and paste the SQL into the Word document. Follow the instructions on your lab answer sheet.;(To take a screenshot, press CTRL-ALT-PRINTSCREEN. Nothing appears to happen on your screen, but this set of keystrokes places a picture of your screen on the clipboard. In Word, just put your mouse where you want the screenshot to go, and then right click and press Paste. The screenshot will appear in your Word document after a few seconds).;Part B;The ?Orders? and ?Order Details? tables contain the data relating to current invoices. Provide a list showing order ID, order date, and order total (don't forget to include the discount). Round the order total to two decimals, display the order date as mm/dd/yyyy (no timestamp).;As you prepare to create this query, decide which information needs to be displayed ? this will be listed in the SELECT clause. Review the tables to determine which tables to use ? these will be listed in the FROM clause. This problem will require a calculated field and an aggregate function. What fields will be in the GROUP BY? If more than one table is needed, how will these tables be joined?;1. Using the query window, type the SELECT statement needed to produce the desired results. The SQL is given below;;2. Press the Execute button. A partial result set is displayed below;;3. Answer the questions under Part B of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.;Part C;Change the previous query to include customer name and sort the list into customer name order.;This problem builds on the previous query.;1. Use the same query that is currently in the query window.;2. What changes are required?;a.;The customer name is in the Customers table. We are already joining Orders and Order Details. Which of these tables can we join Customers to? This table is joined to the Orders table on the CustomerID field.;b.;How will you order the output?;A partial list of results returned will be;;Try the statement on your own, if you have problems, check the solution at the end of this document.;3. Answer the questions under Part C of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.;Part D;Produce a list of employees and the name of the territory they have been assigned to. The list should be ordered on last name and then first name order.;This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.;1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document, or open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.;2. Build the statement step by step;a.;What tables will this information come from? Employee first and last name comes from Employees, the territory name comes from the Territories table.;b.;What is the joining field between these tables? There is no field in common between Employees and Territories. So how do we get the data? We will have to use a linking table. For instance, EmployeeTerritories allows us to join the Employees table to the Territories.;c.;Join Employees to EmployeeTerritories, and EmployeeTerritories to Territories.;A partial list of results returned will be;;3. Answer the questions under Part D of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.;Part E;Produce a list of all orders placed in December 1996. The list needs to show the order ID, order date, product name and the quantity ordered.;This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.;1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document, or you can open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.;2. Build the statement step by step;a.;Which tables will this information come from? Product name comes from Products, order ID and order date comes from Orders, and quantity ordered comes from Order Details.;b.;How will these tables be joined? Orders is joined to Order Details on OrderID, Order Details is joined to Products on ProductID.;c.;How will you test for orders in December 1996? The DatePart function allows you to extract specific parts of the date. This problem will require two DatePart functions ? one to extract the month and one to extract the year.;A partial list of results returned will be;;3. Answer the questions under Part E of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.;Part F;The company would like to reward its best customers. Provide a list of customers who currently have more than 10 orders with the company. Display the company name and a count of its orders. Display the list in descending order of the count.;This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.;1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document, or you can open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.;2. Build the statement step by step;a.;Which tables will this information come from? Customer name comes from Customers, the count of the number of orders comes from Orders.;b.;How will these tables be joined? Customers is joined to Orders on CustomerID.;c.;What aggregate function will you need?;d.;You also need a sort order. How do you sort in descending order?;A partial list of results returned will be;;3. Answer the questions under Part F of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.;Part G;Northwind Traders currently uses only three shipping companies, management would like to review the shipping choices made by customers for their orders. Produce a list showing customer name, order ID, and date, the amount charged for freight, and the name of the shipping company used. Display the freight amount as currency. Display just the date, not the date and time. The list should be in shipper name order and then in company order.;This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.;1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document, or you can open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.;2. Build the statement step by step;a.;What tables will this information come from? Customer name comes from Customers, Order ID, date, and freight amount comes from Orders, the shipping company name comes from Shippers.;b.;How will these tables be joined? Customers is joined to Orders on CustomerID and Orders is joined to Shippers but the joining field is named differently in each table ? it is ShipVia in Orders and ShipperID in Shippers.;c.;How will you format freight for currency?;d.;How will you format the date to display the date only?;e.;What are the sort fields?;A partial list of results returned will be;;3. Answer the questions under Part G of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.;Part H;Produce a list showing the customer name and the total amount of each order placed by a customer. Make the alias of the total amount of each order read ?Total Owed? or, if you wish, ?Total Amount.? Display the list in customer name order.;This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.;1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document, or you can open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.;2. Build the statement step by step;a.;What tables will this information come from? Customer name comes from Customers, the total of each order will be the result of an aggregate function and a calculation ? we will have to sum the extended line item total for each item on each order.;b.;How will these tables be joined? The Customers table is joined to Orders on CustomerID, to sum the extended line item amounts we will need the Order Details table. This table is not related directly to Customers, so we will need to go through the Orders table, Customers is joined to Orders and Orders is joined to Order Details.;c.;What aggregate function will you need?;d.;What field will you group on?;e.;What are the sort fields?;A partial list of results returned will be;;3. Answer the questions under Part H of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.;You have now completed Lab 4 Part 1. Well done! Submit your Word document to the Week 4 iLab Dropbox.;(Referenced solutions are provided below.);SQL Solutions, Problems 3 ? 8;Part C;;Part G;;BIS-345 iLab Problems;(These are the problems you just solved in word problem format. This represents how you might be presented with these types of problems in a professional environment.);1. Northwind Traders deals with a number of suppliers. The Products table includes the supplier ID for each product. Go through the Products table and provide a count of the number of products from each company. The output should show the name of the supplier and a count of the number of products it supplies.;2. The ?Orders? and ?Order Details? tables contain the data relating to current invoices. Provide a list showing order number, order date, and order total (don't forget to take discount into account). Round the order total to two decimals, display the order date as mm/dd/yyyy (no timestamp).;3. Change the previous query to include customer name and sort the list into customer name order.;4. Produce a list of employees and the name of the territory they have been assigned to. The list should be ordered on last name and then first name order.;5. Produce a list of all orders placed in December 1996. The list needs to show the order ID, order date, product name, and the quantity ordered.;6. The company would like to reward its best customers. Provide a list of customers who currently have more than 10 orders with the company. Display the company name and a count of its orders. Display the list in descending order of the count.;7. Northwind Traders currently uses only three shipping companies. Management would like to review the shipping choices made by customers for their orders. Produce a list showing customer name, order ID, and date, the amount charged for freight, and the name of the shipping company used. Display the freight amount as currency, display just the date, not the date and time. The list should be in shipper name order and then in customer name order.;8. Produce a list showing the total amount owed to us by our customers. The list should show the customer name and the amount owed. The amount owed will be the total amount of each order placed by a customer. Display the list in customer name order.;BIS345: Data Analysis for Business;Lab 4 Part 2: Creating Simple Reports;Overview;Using the AdventureWorks database, you have been requested to create a report. The report will introduce reporting features such as using groups and a calculated field. The wizard will be used to create the report.;The AdventureWorks database contains many tables, we will be using only some of those tables in the course. Below is an ER diagram showing the tables in the AdventureWorks database that we will be using;;You will log into the iLab Citrix Server, access SQL Server Business Intelligence Development Studio 2008 and create the reports using Report Server and the AdventureWorks database.;When SQL Server BI Studio opens, you will be prompted by a security warning. Click yes and place the checkmark on the do not ask me again.;;If this is the first time you have used this software, there will not be a list of previously opened projects. To create a new project, select File -> New Project.;The New Project dialog box will appear. Select Report Server Project, be sure to select a specific location to save your project file. Use your last name and the lab number in the project name;;;When the new project opens, it will look similar to the following.Look at the Solution Explorer ? it is showing two folders ? Shared Data Sources and Reports:;Now we are ready to create the report.;Creating the Report;A report has been requested that will show a full list of products offered by the company. Products are divided into categories and each category is further subdivided into subcategories. For example, the category Bikes consists of subcategories such as Mountain Bikes, Touring Bikes, etc.;Your report needs to list the products in each subcategory. Include the subcategory name, product name, and number along with the list price and standard cost. Add a calculated field to the report to calculate the difference between the list price and standard cost.;In the dataset, the subcategory name will be repeated for each product in that subcategory. On the report, the subcategory name should not be repeated.;The report must have an appropriate title, it must display the current date, and it should include page numbers. The finished report will look like this;;We will use the wizard to create this report.;Step 1;To add a new report, right click the Reports folder in the Solution Explorer and select Add New Report. The Report wizard will begin and step you through the process of creating this report.;Report Wizard Step 1 is a Welcome Window.;;Click Next to continue.;Step 2;Report Wizard Step 2 ? Select the Data Source. If this is a new project, you will create a new data source.;;Enter a meaningful name for the data source ? a suggestion is to use the same name as the project for shared data sources.;To create the connection string, click the Edit button. In the Connection Properties window, enter the server name provided to you by your instructor.;Ensure Windows authentication is selected.;When you have entered a valid server name, you will be able to access the bottom half of this window where you can see a drop down for ? Select or enter a database name ?. Enter the Server Name bis345sql2008. Click on the dropdown and select the BIS345_AdventureWorks database. If you do not get a list of database names in the Connect to a database section of the screen, then your server name is incorrect, you will need to re-enter theserver name bis345sql. Your screen should look like the following;;Click OK to return to the Report Wizard Select the Data Source screen, it will now look like this;;Make sure you check the ? Make this a shared data source ? check box in this step. It is more efficient to share a data source between multiple reports in a project.;Click Next.;Step 3;Report Wizard Step 3 ? Design the Query will appear.;In this window, you can type the SQL into the query string window or use the Query Builder option. We will use the Query Builder.;Select the Query Builder button to open the next window showing the generic query builder.;;This is the Query Designer;

Price : $32
SiteLock