+
BIS 345 Week 4 iLab

BIS 345 Week 4 iLab

Rating:
Rating
(0)
Author: Minh Reed
Description:

http://www.devrygenius.com/product/bis-345-week-4-ilab

Using Lab 4 provide copies of your SQL Statements and/or results for each Section listed below
Part A Supplier and product list (10 points)
a.Paste your SQL Statement here:
b.Screen print your results and paste here:
Part B: Order information (10 points)
a.Paste your SQL Statement here:
b.Screen print your results and paste here:
Part C: Customer and order (10 points)
a.Paste your SQL Statement here:
a.Screen print your results and paste here:
Part D: Employee and territory list (10 points)
b.Paste your SQL Statement here:
c.Screen print your results and paste here:
Part E: Order and product (10 points)
a.Paste your SQL Statement here:
b.Screen print your results and paste here:
Part F: Best customer list (10 points)
a.Paste your SQL Statement here:
b.Screen print your results and paste here:
Part G: Join Shipper list (10 points)
a.Paste your SQL Statement here:
b.Screen print your results and paste here:
Part H: Account payable (10 points)
a.Paste your SQL Statement here:
a.Screen print your results and paste here
Part I: Create a dataset for reporting (10 points)
b.Paste your SQL Statement here:
c.Screen print your results and paste here:
Part J: Report (10 points)
a.Paste a screen print of the report view:
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.


(more)
See More
Try a College Course Free

Sophia’s self-paced online courses are a great way to save time and money as you earn credits eligible for transfer to over 2,000 colleges and universities.*

Begin Free Trial
No credit card required

25 Sophia partners guarantee credit transfer.

226 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 20 of Sophia’s online courses. More than 2,000 colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial