Notice that some columns have brackets and some don't. The order of columns is adjusted to place them in a preferred order to match the search criteria. This query is the same as the prior except that the SQL syntax ORDER BY is used to sort the returned results first by the territory name, then by customers within that territory. As you can see this is made easier by using the one letter table name aliases. If they are then you have to specify table. This is tedious however so its a good idea to save your queries so you do not have to re-run them.Īlso critical is to now see if any of the field names are in both tables. Since data is separated into different tables you will most likely have to use a lot of INNER JOINS to get the columns of data that you want.
So INNER JOIN is the syntax used to pull values from a second or third table (or more). It helps to use the AS alias to provide a one-letter 'AKA other name' for the table. What you have to do is identify a common field in both tables then write the INNER JOIN syntax that connects them.
The territory name is in another table so we have to do an INNER JOIN to connect to that table. This query is the same as the prior except that the data is made much more understandable by the inclusion of the English name of the territory (rather than relying on an ID# for sales Territory). This goofiness was replaced by the FORMAT(field name function in SQL Serve 2012). You can also see that a $ sign was appended to the field. But if you wanted to report out quickly some query results you can use the STR(field name) function shown in the Sub total field above which strips out the decimal places, or the CONVERT(field name) function to add the comma and 2 decimal places formatting. Normally you would copy the data into excel for formatting and report submission, or copy the SQL query into SQL Server reporting services when making a dataset. Notice the commas after each column EXCEPT THE LAST. Retrieve just the columns that are needed for analysis. Open up the AdventureWorks table and retrieve one month's of data records from the SalesOrderHeader table. a lot of SQL developers write their queries like this. The BETWEEN statement is very helpful! What to see why? You can delete the WHERE clause and run the query again to see why. You can do that by using SSMS's Object explorer to find the database and table then right-clicking the table name and columns to see the column names and their datatypes. You have to know the datatype of the column that you are filtering on first, before you craft your WHERE statement. You should see 174 rows.įor specifying filters in the where clause dates and words have to be in 'single quotes' and numbers do not.
You can add many different filters to the WHERE statement, which is pretty important because you rarely want ALL the data rows from a table. The month of data retrieved is in the WHERE statement. The FROM specifies what table to retrieve the data from. The USE statement tells SSMS what database to operate on. Open up the AdventureWorks database and retrieve either the sales order information for customer #1 (first query) or in the 2nd query one month's of data records from the SalesOrderHeader table. There is a ton of online content that explains what is happening (here’s an example). At this point just play with the queries then slowly create your own.
#How to use adventureworks database with visual studio password
To run these queries, open up SQL Server Management Studio (SSMS) and log into cb-ot-devst12.ad.wsu.edu, userid = mfstudent, password = MIS271.įind the New Query button on the toolbar and one at a time, copy and paste the following T-SQL query syntax, then press the crimson ! to run the query and retrieve data. You can learn a lot about SQL statements quickly just by running them and analyzing the results returned from the database.
0 kommentar(er)
