bis345_w4_pt1_ilab_instructions

bis345_w4_pt1_ilab_instructions - 1 BIS-345: Data Analysis...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
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: 1
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2
Background image of page 2
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: 3
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
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.
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 13

bis345_w4_pt1_ilab_instructions - 1 BIS-345: Data Analysis...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online