In the Management Studio, you should be viewing

The examples shown are run with the database AdventureWorks, which is installed as part of the Documentation and Samples option in the SQL Server 2005 installation. Suppose you were interested in querying the Sales.SalesOrderHeader table in the AdventureWorks database. You might start with the following query: SELECT count(*) FROM Sales.SalesOrderHeader The count function used here tells SQL Server that it is interested in returning the number of rows in the database table. The count function is not casesensitive and it requires column reference(s) in parentheses after the function name to indicate which columns it is counting. The use of the asterisk is a shorthand way of indicating all columns referenced in the FROM clause. The answer SQL Server returns is that 31,465 orders are in the Sales.SalesOrderHeader table. That s a lot of orders! Before you start plowing through that data, how about finding out the date of the last order as well as when the first order occurred? (Basically, I m talking about the date range of the table here.)You can do that in the Sales.SalesOrderHeader table by using the following two SQL statements or queries: – Find maximum value for OrderDate SELECT max(OrderDate) FROM Sales.SalesOrderHeader — Find minimum value for OrderDate SELECT min(OrderDate) FROM Sales.SalesOrderHeader Figure 2-3 shows the result set for these two queries. Note that the results window is split to show the results of all queries in the query window. Also note that I can use dashes (–) to indicate that what follows on that line is a comment and is not to be evaluated as part of the SQL statement. You can use this technique to comment your statements. You can use comments to explain in English what is being queried or to explain what to do with the results of the query in the flow of processing that follows. By looking at the result sets in Figure 2-3, you can conclude that the Sales. SalesOrderHeader table contains orders dating from July 1, 2001, through July 31, 2004. If you would like to see all the columns of a table, you don t have to spell out all those column names in your SELECT clause. Rather, you can use the following convention: SELECT * FROM Sales.SalesOrderHeader 36 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

In the Management Studio, you should be viewing

In the Management Studio, you should be viewing the Object Explorer window on the left side. If it is not displayed, you can open it by choosing View.Object Explorer. Object Explorer uses a tree structure to group information into folders. Expand the folder named Databases to show all the databases available to you. For this example, click the AdventureWorks database. 4. Open the Query Editor and begin constructing your SQL query. Right-click the database named AdventureWorks and you will see a popup menu of options. Select the New Query menu item and you will see the Query window open to the right of your Object Explorer. Note also that a new toolbar displays after this action. The second toolbar is the SQL Editor toolbar to help in executing SQL queries. An alternative way of opening the Query Editor is to click the New Query button on the standard toolbar to do this. However, if you use this method, you will be prompted to connect to the server with another Connect to Server dialog box like the one shown in Figure 2-2. In this case, you need to select Database Engine as the server type and make sure it is connecting to the same server with the same authentication as you entered in Step 2 above. Then click the Connect button to open the Query Editor window. You see a dialog box similar to the one in Figure 2-3. You can now enter SQL statements to execute on the server that s running SQL Server 2005. Figure 2-3: Query Analyzer with a connection to the Adventure Works database. Chapter 2: Retrieving Data from a Database 35

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Another look at the ER diagram in Figure

Follow along with SQL Server Query Analyzer Getting a handle on SQL statements takes some doing, so it wouldn t hurt to get some practice. Here s what I suggest: If you have SQL Server (or its utilities) loaded on your computer either at home or at the office you should be able to run the program Query Analyzer. (Query Analyzer for SQL Server works like most database SQL command processor utilities in that it allows you to connect to databases and write SQL queries to return result sets.) Follow these steps to start Query Analyzer and connect with the AdventureWorks sample database: 1. Start the SQL Server Management Studio by choosing Start.All Programs.Microsoft SQL Server 2005.SQL Server Management Studio. The SQL Server Management Studio main window appears along with the Connect to Server dialog box. 2. Connect to the server. The Connect to Server dialog box is shown in Figure 2-2. It prompts you for the server type, server name, and authentication. You have several options for server type: Database Engine, Analysis Services, Reporting Services, and others. In order to write queries for this example, select Database Engine as the server type. Also specify the server name to which you are connecting and select Windows Authentication in the third combo box. If you specify Windows Authentication, you do not need to provide any more information. For SQL Server authentication, you need to provide user ID and password information for a valid SQL Server access. Then click the Connect button to connect to SQL Server. 3. Select the database you want to query. Figure 2-2: The Connect to Server dialog box. 34 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Another look at the ER diagram in Figure

Another look at the ER diagram in Figure 2-1 shows you that a salesperson is related to a customer which is another way of saying that a salesperson has responsibility for a customer. You ll also find tables (SalesOrderHeader and SalesOrderDetail, for example) that contain attributes that relate to money transactions between customers and the company. Building a SQL Query Having all your stuff nicely stored in a database (relational or otherwise) is a nice start, but if you can t access your information quickly and efficiently, you ll find yourself up a certain creek rather quickly without a certain paddle. This is where querying a database comes in. As you might expect with anything involving computers, a specific language is needed to enable us humans to communicate with that laptop or desktop machine and actually do something with the information stored in that database on the hard drive. This language is Structured Query Language (SQL). SQL was specifically designed to work with databases and can be used to access data (to be displayed in a report), update data within a table, or insert new values into the table. For the purposes of working with Reporting Services, you need to focus only on using SQL to display information. Being SELECTive You build SQL statements or queries by stringing together terms including one or more keywords that look pretty much like plain old English. The SELECT keyword, for example, indicates that you want to retrieve information from one or more tables in the database to which you are connected. When you use the SELECT statement, you also have to indicate what you want to select as well as from which table in the database. The keyword FROM is used to precede the names of the tables in which the selected columns are located. For example, the following syntax retrieves the customer ID, order date, and subtotal from the SalesOrderHeader table: SELECT customerID, OrderDate, SubTotal FROM SalesOrderHeader Refer to your handy entity-relationship (ER) diagram (see Figure 2-1) for the column names and then use those names as the columns to select in the SQL statement. If you do not correctly spell what you want to select, or you don t use the proper syntax, you ll receive an error. The error message can look like Incorrect syntax near . . . or Invalid object name. . . . Chapter 2: Retrieving Data from a Database 33

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services