SQL Class Examples (we will go over these in class):



HOA#2 - Part 2:Manipulating & Importing DataMIS 420: Business IntelligenceSQL Class Examples (we will go over these in class): USE [AdventureWorks2012]SELECT * FROM [Sales].[SalesOrderHeader]USE [AdventureWorks2012]SELECT * FROM [Sales].[SalesOrderHeader]WHERE [OrderDate] BETWEEN '1/1/2007' AND '1/31/2007'SELECT * FROM [Sales].[SalesOrderHeader]WHERE [OrderDate] BETWEEN '1/1/2007' AND '1/31/2007'AND [SalesOrderID] = 48730USE [AdventureWorks2012]SELECT [SalesOrderID],[CustomerID], [TerritoryID], STR([SubTotal]) AS [Sub Total], [TaxAmt], + '$' + CONVERT(varchar(12), [TotalDue], 1) AS [Total $ Due]FROM Sales.SalesOrderHeaderWHERE [OrderDate] BETWEEN '1/1/2007' AND '1/31/2007'ORDER BY [TotalDue] DESCUSE [AdventureWorks2012]SELECT T.[Name], [SalesOrderID],[CustomerID], S.[TerritoryID],[SubTotal],[TaxAmt],[TotalDue]FROM Sales.SalesOrderHeader AS SINNER JOIN [Sales].[SalesTerritory] AS T ON T.TerritoryID = S.TerritoryIDWHERE [OrderDate] BETWEEN '1/1/2007' AND '1/31/2007'ORDER BY T.[Name]USE [AdventureWorks2012]SELECT T.[Name], [SalesOrderID],[CustomerID], S.[TerritoryID],[SubTotal],[TaxAmt],[TotalDue]INTO [Your database].[Your SQL Name].[ClassExample]FROM Sales.SalesOrderHeader AS SINNER JOIN [Sales].[SalesTerritory] AS T ON T.TerritoryID = S.TerritoryIDWHERE [OrderDate] BETWEEN '1/1/2007' AND '1/31/2007'ORDER BY T.[Name]TUTORIALPart 1: Log-In and Create New QueryLog-in to SQL Server Management Studio Server Type: Database Engine Server Name: CB-OT-DEVST03.AD.WSU.EDUAuthentication: SQL Server AuthenticationLogin: Your SQL_Username (see course website)Password: !@#qWeYOURSTUDENTID# (replace the bold with your ID #)Expand the Databases Folder, Right click AdventureWorks2012, and Select “New Query”Part 2: Importing One Table into Your Own SQL Server DatabaseWe will not use SQL code to load some structured data into your own databases so that you can import it into a data analysis tool (e.g., MS Excel) and begin analyzing and manipulating the data. Good data analysts know both the back-end (SQL) and the front-end (visualization) aspects of business intelligence. We will now dive into the back-end of the data and manipulate data so that it can be used for analysis in a front-end tool. We will begin with a simple example.Let’s start with importing some countries into our own database. In your query window, type USE??[AdventureWorks2012]; This tells SQL Server that you want to use the database called ‘AdventureWorks2012’, in which many countries are stored. Next, type SELECT?[CountryRegionCode],?[Name] This line of SQL code tells the database that we want to select the data that is in the tables called “CountryRegionCode” and “Name.” An example of a Country Region Code is “US”, and the Name being the “United States.”Now we must tell SQL Server that we want to put the data into our own database. To do this, on the next line, please type INTO?[YOUR DATABASE].[SQLUser Name].[Countries] Now we must change the [YOUR DATABASE].[ SQLUser Name] to something SQL recognized. In place of [YOUR DATABASE] please type the database name given to you. You can find your database under the list of databases in the Object Explorer window. For example, my database is named CC43christophercaliff. So in place of [YOUR DATABASE] I would type [CC43christophercaliff]. In place of [SQLUser Name], you need to type in your SQL username (i.e., the one with which you logged-in). For example, mine is ‘christopher.califf.’ So, my code would now look like this: [CC43christophercaliff].[christophercaliff].[Countries]The [Countries] portion of the code is simply what you want to call the table. Since we are importing countries, let’s just call this one Countries. Your code should now look like this:USE??[AdventureWorks2012];?SELECT?[CountryRegionCode],?[Name]INTO?[YOUR DATABASE].[ SQLUser Name].[Countries]Now we need to tell SQL server from which tables to get the country data. In the next line of code, type FROM?[Person].[CountryRegion] This tells SQL Server that we want to get the data from the table called “Person.Country” which is stored in the AdventureWorks2012 database. Now, your overall code should look like this. Remember, this code is using the database called AdventureWorks2012, selecting the information we want to import, telling SQL Server into which database we want to move the data, and telling it from which tables we want to get the data.USE??[AdventureWorks2012];SELECT?[CountryRegionCode],?[Name]INTO?[YOUR DATABASE].[ SQL User Name].[Countries]FROM?[Person].[CountryRegion]To run the above SQL query, select the “Execute” button. 3457575762000You should see the message “238 row(s) affected” Now, expand your own database, expand the Tables folder, and you should see a table called “[Your SQL User Name].[Countries].” If you do not, please right click on your database and click “Refresh.” Mine looks like the picture to the right: Now let’s see the country data. Right click on the Countries table and select “Select Top 1000 Rows.” In the Results pane, you should see the CountryRegionCode, and the Name of each country. Feel free to scroll to the bottom. There should be 238 rows. Part 3: Importing Multiple Tables into Your Own SQL Server DatabaseNow that we have imported Countries, let’s import multiple tables at a time, rather than doing it over and over (the beauty of computers, eh?) First, we need to decide what data we should analyze. Let’s do some analysis on sales order data by months in 2006. To do this, let’s get Order ID, Order Quantity, Product ID, Line Total, Order Date, Product Name, Product Color, Product Size, and Product Subcategory Name. To start, please type:USE AdventureWorks2012Now we need to select the data, just as we did before. To do this, please type the following code: SELECT O.[SalesOrderID], O.[SalesOrderDetailID], O.[OrderQty], O.[ProductID], O.[LineTotal], H.[OrderDate], P.[Name] as [ProductName], P.[Color], P.[Size], S.[Name] as [Category]Note: We will break down the meaning of this in class. For now, note that the “O” “H” “P” and “S” are simply shorter labels of the database tables. Now we need to tell SQL Server where this data will go (just as we did above). Please note that we called this table the “2006SalesData” table. Please type: INTO [Your Database].[Your Schema].[2006SalesData]Lets tell SQL Server where to get the data from. Here we need to get data from a few different tables. But, we need to join the tables so that we can have all the data in one place. So, we need to do some INNER JOINS (this is how to join the tables together). To join the tables, there must be a match between columns on both tables. You will learn more about these in your database class, but for now, let’s walk thorugh this step-by-step. Let’s start with the Sales.SalesOrder detail. We need to get give values from this table: SalesOrderId, SalesOrderDetailID, OrderQty, ProductID, and LineTotal. So, we need to tell SQL Server where to get the data from, so please type: FROM [AdventureWorks2012].[Sales].[SalesOrderDetail] as ONote: The “as O” is simply re-naming this long table name to “O”. That way, in the SELECT statement above, we can get the values we want from the Sales.SalesOrderDetail table simply by typing O.[value]. For example, we want the Sales Order ID, we just need to type O.[SalesOrderID] rather than [AdventureWorks2012].[Sales].[SalesOrderDetail.[SalesOrderId]. Now we need to get some data from the SalesOrderHeader table. Specifically from this table, we need the OrderDate. Remember, we want to be able to view all the data in a single table so it makes it easier for analysis. Since the SalesOrderHeader table has a common value with the SalesOrderDetail Table (SalesOrderID), let’s join these tables by SalesOrderID. To do this, please type the following code below. Remember, the “as H” is simply naming the table H. Note that we are joining the tables ON the common value, which is SalesOrderId.INNER JOIN [AdventureWorks2012].Sales.SalesOrderHeader as H on H.SalesOrderID = O.SalesOrderIDNext, let’s get some data from the [Producution].[Product] table. Again, we need to find a table that we are using with a common value. In this case, it looks like our SalesOrderDetail table has the value “ProductID”, and so does our [Producution].[Product] table. So we can join the tables based on this value. So please type the following on the next line of your pane. Remember, “as P” is redefining the [Producution].[Product] table as simply “P”. INNER JOIN [AdventureWorks2012].[Production].[Product] as P on P.[ProductID] = O.[ProductID]Now we need to do the same thing for the [Production].[ProductSubcategory] table, since this is where our product subcategories are stored. We need to again join this table with a common value. Our “P” table above (i.e., the [Production].[Product] table) has the value of ProductSubcategoryID, just as our Production.ProductSubcategory table. So, let’s join these tables with the common value of ProductSubcategory. To do so, enter the SQL code below. Remember, “as S” simply refedines the[Production].[ProductSubcategory] table as simply “S.” INNER JOIN [AdventureWorks2012].[Production].[ProductSubcategory] as S on S.[ProductSubcategoryID] = P.[ProductSubcategoryID]Last, because we want to look at data for the year 2006 only, we need to tell SQL how to filter the data by year. This is done by using a WHERE clause, which extracts only those records that fulfill a specified criterion. In our case, the criterion is the data for the year 2006, which falls between January 1st, 2006 and December 31st, 2006. To accomplish this, please type in the code below:WHERE h.OrderDate BETWEEN '1/1/2006' and '12/31/2006'Now, your overall code should look like this: Click Execute. You should now see a Message that says 19353 row(s) affected. This means that 19353 rows of data have now been imported from the AdventureWorks2012 data warehouse into your own database. Now, expand your own database, expand the Tables folder, and you should see a table called “[Your SQL User Name].[2006SalesData].” If you do not, please right click on your database and click “Refresh.” Mine looks like this: Now let’s see what the data in the tables look like. Right click on the 2006SalesData table and select “Select Top 1000 Rows. Yay, we have data!Part 4: Exporting Data into ExcelWe will now export the 2006SalesData table we just imported into our own database using SQL Server into MS Excel so that we can analyze it later. Import into MS Excel: Open MS Excel and select Blank Workbook Click on Data Select From Other Sources and “From SQL Server.”Server Name: CB-OT-DEVST03.AD.WSU.EDUSelect Use Windows Authentication; If you are off campus, thisIn the drop-down menu, select your database. For example, mine is CC43christophercaliff. You should now see the Countries and the 2006SalesData tables in this window.Select the 2006SalesData and click NextClick FinishThen select “PivotTable Report”. Your screen should now look like the picture below. Notice that the PivotTable field to the right are the values that we selected from the database, all nicely rolled into one table. For kicks, let’s do a basic report. Drag Category and Product name into Rows. Drag OrderQty into Values. Then, select Analyze in the PivotTable Tools ribbon above. Select Insert Slicer > and check ColorThen select Analyze again, select Insert Timeline, and Select Order DateNow select Analyze again, click Pivot Chart and select Clustered Column. You can now quickly slice you’re the Order Quantity with a table and a column chart by Color and Order Date. It should look something like this: Save this File as YourName_Excel Example 1.xlsPart 5: On Your OwnNow that we have learned how to insert data into your own SQL databases and to import it into MS Excel and Tableau, please do the following based on the following scenario. The AdventureWorks inventory manager is planning to get rid of some wasted inventory for July 2008. She wants to use the sales data for July 2007 to know what mountain bikes and road bikes to keep and what to get rid of based on color. In other words, she is wondering “What colors of mountain bikes sold the most in July 2007, and what colors did not?” That way she can order more popular bikes for the next July.All the manager has is access to the AdventureWorks2012 data warehouse through SQL Server. So, the manager decides that she needs the following values to analyze the data: Order ID, Order Quantity, Product ID, Line Total, Order Date, Product Name, Product Color, Product Size, and Product Subcategory Name. Further, she wants to filter the data by only those sales orders that occurred between July 1st, 2007 and July 31st, 2007. The manager has no idea how to do this, so she summons you, the data analyst, to help her out. Using what we learned in the above tutorial, please create the manager a table in your own database Called “July2007SalesData” and insert data pulled from AdventureWorks2012 into your own database. Also, she needs you to import the data from SQL Server into MS Excel, and to perform some basic analysis on mountain bikes and road bikes by color. For example, you could analyze the data by Category and Color and the quantity of orders for July 2007. Your manager knows that you have very limited knowledge in MS Excel, but she is encouraging you to try. (We will learn more about these programs in the weeks to come. But for now, start to get your feet wet.)Also, based on your analysis, she wants you to write up a couple paragraphs about some recommendations for what to do for July 2008 based on what you found in July 2007. Your recommendations should tell her what products to keep, what products to get rid of, and to tell her WHY you think she should keep and/or get rid of these products. This is where you need to tell a nice practical story based on the data and your analysis. What to Turn In: Please upload one Word document with a screen short of your July2007Sales database, your MS Excel analysis (chart, report, etc.), and a couple paragraphs that detail your recommendations. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download