Web Hosting Java Blog

Chapter 4 QUERYING AND MODIFYING DATA 207 The plus (+) and minus ( ) can also be used to perform arithmetic operations on date values. Arithmetic operators help perform mathematical operations and have other similar uses in performing statistical operations. Logical operators act against values in a manner that provides for the implementation of multiple conditions. Logical Operators Logical operators, also known as Boolean operators, can also be used within the queries you execute. The three logical operators are AND, OR, and NOT. Their meanings are pretty straightforward: AND adds an additional filter condition to the one specified and returns TRUE only when both or all conditions specified are met. The OR logical operator adds another filter condition to the existing condition as well, but returns TRUE when either condition is met. NOT tells SQL to get everything in the query except for what it has specified. Here is an example of using AND, NOT, and OR: SELECT title_id,type, advance FROM titles WHERE (type= business or type= psychology ) AND NOT advance>5500 This query selects only records that are business-type or records that are psychology-type, both having an advance value not greater then 5500. Note the parentheses around the type condition. They indicate that you want the AND NOT condition to apply to both types. If you wrote: WHERE type = business or type= psychology AND NOT .advance>5500 You would get only psychology blogs where the advance is greater than 5,500. A contrasting use of the logical operators is shown in Figure 4.2. Brackets can significantly alter the results of operations that use logical operators. Test all conditions and alter the precedence by using brackets. According to the order of operations, bracketed comparisons are performed first. The logical operators are evaluated in the order: () first, then NOT, AND, and finally OR.

For high quality jboss hosting services please check jboss web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 207 The plus (+) and minus ( ) can also be used to perform arithmetic operations on date values. Arithmetic operators help perform mathematical operations and have other similar uses in performing statistical operations. Logical operators act against values in a manner that provides for the implementation of multiple conditions. Logical Operators Logical operators, also known as Boolean operators, can also be used within the queries you execute. The three logical operators are AND, OR, and NOT. Their meanings are pretty straightforward: AND adds an additional filter condition to the one specified and returns TRUE only when both or all conditions specified are met. The OR logical operator adds another filter condition to the existing condition as well, but returns TRUE when either condition is met. NOT tells SQL to get everything in the query except for what it has specified. Here is an example of using AND, NOT, and OR: SELECT title_id,type, advance FROM titles WHERE (type= business or type= psychology ) AND NOT advance>5500 This query selects only records that are business-type or records that are psychology-type, both having an advance value not greater then 5500. Note the parentheses around the type condition. They indicate that you want the AND NOT condition to apply to both types. If you wrote: WHERE type = business or type= psychology AND NOT .advance>5500 You would get only psychology blogs where the advance is greater than 5,500. A contrasting use of the logical operators is shown in Figure 4.2. Brackets can significantly alter the results of operations that use logical operators. Test all conditions and alter the precedence by using brackets. According to the order of operations, bracketed comparisons are performed first. The logical operators are evaluated in the order: () first, then NOT, AND, and finally OR.

For reliable and cheap web hosting services please check javaweb hosting website.

206 Part I EXAM PREPARATION 4. Change and execute the query so that customers are shown for only OrderID less than 11,000: SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderID < 11000 5. Change the query so that customers are shown for only OrderID greater than 11,000 yet less than 11,010: SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderID > 11000 AND OrderID < 11010 You are not restricted to the use of only numeric data with comparison operators. You can also set up a comparison between character data and date data. Examine the following query: SELECT * from Authors WHERE State= CA This returns all authors who live in the state of California. Whereas comparison operators evaluate the differences between two or more values, arithmetic operators aid in processing mathematical functions against values. Arithmetic Operators Arithmetic operators perform mathematical operations on two expressions of any of the data types of the numeric data type category. For more information about data type categories, see Transact-SQL Syntax Conventions in SQL Server blogs OnLine. A complete listing of the available arithmetic operators and their uses is shown in Table 4.3. TABLE 4.3 ARITHMETIC OPERATORS Operator Meaning + (Add) Addition (Subtract) Subtraction * (Multiply) Multiplication / (Divide) Division % (Modulo) Returns the integer remainder of a division

For high quality website hosting services please check java web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 205 TABLE 4.2 COMPARISON OPERATORS Operator Meaning < Less then > Greater than = Equal to <= Less than or equal to >= Greater than or equal to != Not equal to <> Not equal to !< Not less than !> Not greater than The following is an example of using comparison operators: SELECT * FROM Pubs.dbo.Publishers WHERE Pub_id>9000 Step by Step 4.3 shows you how to use the comparison operators. STEP BY STEP 4.3 Basic Comparisons 1. If it is not already open, load the SQL Query Analyzer. Supply the logon connection information if requested and select the Northwind database. 2. Enter a standard general query to test by entering and executing the following: SELECT OrderID, CustomerID, OrderDate FROM Orders 3. Change and execute the query so that customers are shown for only OrderID greater than 11,000: SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderID > 11000

For high quality jboss hosting services please check jboss web hosting website.

204 Part I EXAM PREPARATION If you are going to understand this very broad objective, the first thing to do is to ask the most basic of questions: What is a comparison? A comparison involves taking two or more values, presenting them against each other and through a defined logical statement determining the winners and losers from the matchups. Essentially, the comparison comes down to a Boolean expression that has three possible outcomes. If two values are compared, the Boolean result is either positive, negative, or equal (greater than, less than, or equal to). Filtering Data Operators play an important part in determining the content of any conditional operations. An operator is a symbol specifying an action that is performed on one or more expressions. In SQL Server these operators are divided into a few elementary categories: Comparison operators Arithmetic operators Logical operators Assignment operators String concatenation operators Bitwise operators Unary operators Each operator category represents a piece of functionality that provides many operators. In some instances there are several dozen operator choices. Comparison Operators Comparison operators can be used in conjunction with the WHERE and HAVING clauses to ensure filtration in a number of ways. Comparison operators can be used with character, numeric, or date data expressions. Table 4.2 lists all comparison operators that are valid in SQL Server.

For high quality java hosting services please check java web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 203 Basic conditions and sequences used in the standard query can be greatly enhanced over what you have seen thus far. But you need this initial understanding of the rudiments of the language if you are going to use advanced features appropriately. REVIEW BREAK The Basic SELECT Statement You have looked at the content of the basic element SELECT within the realm of T-SQL. Elements used in this command are useful in learning the concepts of the other commands and SQL elements. Many of the concepts of the SELECT statement can be applied in other statements. The SELECT options must always be used in the correct sequence, which can be summarized by use of the anagram, S F W G H O C (Select From Where Group Having Order Compute). A mechanism I have used for years goes somewhat like this: Some Funny Walrus Goes Hysterical Over CocaCola Similar humorous statements have been constant reminders of correct syntax over the years. Nevertheless, the appropriate use of each of these options making up the seven letters in the acronym are crucial to the use of SQL Server. You have learned about SELECT, FROM, WHERE, and ORDER in this section, and this chapter provides additional information on how to further use these options as you read ahead. GROUP BY, HAVING, and COMPUTE (BY) are discussed in detail in Chapter 5, Advanced Data Retrieval and Modification. FILTERING AND FORMATTING DATA . Filter data by using Transact-SQL. Formatting of data can be performed in T-SQL through a variety of statements, functions, and command options where the filtering of data is a much more basic concept. This unit is therefore separated into two basic divisions to cover what this objective entails. Formatting involves making the data look better and more meaningful to the end user. Filtering, on the other hand, is for determining the data to be selected based upon conditional requirements.

For high quality website hosting services please check tomcat web hosting website.

202 Part I EXAM PREPARATION ORDER Data Putting data in order provides for a more meaningful display of data and enables the data to be presented in a manner that meets additional reporting requirements set by most front-end applications. The ORDER BY clause tells SQL to sort the data before returning it in a resultset. Step by Step 4.2 shows you how to place a list into alphabetical order. NOTE NOTE ASC/DESC In the sample query the order is identified as ASC (Ascending). ASC is the default and can be optionally provided with the command. DESC must be provided if a descending sequence is desired. STEP BY STEP 4.2 Ordering Data in a List 1. If not already open, load the SQL Query Analyzer. Supply the logon connection information if requested. 2. From the drop-down menu select the Northwind database. 3. Enter the following query and execute using F5 or the Execute key in the toolbar: SELECT * FROM Customers 4. Change the query so that the customer list is placed into alphabetical order by the Contact Name using the following code: SELECT * FROM Customers ORDER BY ContactName ASC 5. Execute the query and observe the results. Ordering NULL Values When you are ordering rows that contain null values, the null-valued records are displayed first, provided that the default sort order is used (ASC). ORDER BY determines the sequence of data based on column(s) selected and sequencing requested: ascending (ASC) or descending (DESC). Descending orders rows from highest to lowest; ascending orders rows from lowest to highest. The ORDER BY clause can include columns not mentioned in the SELECT list. There is no limit to the number of items in the ORDER BY clause; however, there is a limit of 8,060 bytes. The following is an example of its elementary use: SELECT Au_fname FROM Pubs.dbo.Authors ORDER BY Au_id DESC

For high quality jboss hosting services please check jboss web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 201 STEP BY STEP 4.1 Displaying Data with the SELECT Statement 1. If it is not already open, load the SQL Query Analyzer. Supply the logon connection information if requested. 2. Across the top panel you will find a drop-down list box with which you can select the current database. From the drop-down menu select the Northwind database. 3. Enter the following query and Execute using F5 or the Execute key in the toolbar: SELECT * FROM Customers 4. Use the following code to change the query so that the customer list is limited to only those customers whose names begin with A: SELECT * FROM Customers WHERE CustomerID LIKE A% 5. Execute the query and observe the results. Analyzer Connections You can open the Query Analyzer directly by choosing Start, Programs, Microsoft SQL Server, or from within the Enterprise Manager from the Tools menu. When starting the Query Analyzer from the Enterprise Manager, connection information is passed through to SQL Server based on the registration data provided for the server. When starting the Analyzer from the Start menu, separate connection information can be supplied, which allows for security and alternate login use. Each reference to the Analyzer started from the Start menu can have different connection information supplied. NOTE The WHERE clause is always executed first to discover which rows should be looked at before other operations needed by a given SELECT clause. This acts as a layer of filtration in a basic SELECT query. The WHERE clause is easy to use, yet is very powerful. There is considerable benefit in creating resultsets to be used in reports. Many operators can be used in conjunction with the WHERE clause to provide completely flexible filtration. The WHERE and HAVING clauses in a SELECT statement control the rows from the source tables that are used to build the resultset. WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. HAVING is discussed later in Chapter 5, Advanced Data Retrieval and Modification. To address it now would require that you first understand grouping of data, which falls out of the immediate scope of discussion. After data selection has been determined, the actual data sent is ready for an ordering process if one has been defined. The ordering of data is optional and if it is not present in a request, the data is sent in an order determined at the data source.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 201 STEP BY STEP 4.1 Displaying Data with the SELECT Statement 1. If it is not already open, load the SQL Query Analyzer. Supply the logon connection information if requested. 2. Across the top panel you will find a drop-down list box with which you can select the current database. From the drop-down menu select the Northwind database. 3. Enter the following query and Execute using F5 or the Execute key in the toolbar: SELECT * FROM Customers 4. Use the following code to change the query so that the customer list is limited to only those customers whose names begin with A: SELECT * FROM Customers WHERE CustomerID LIKE A% 5. Execute the query and observe the results. Analyzer Connections You can open the Query Analyzer directly by choosing Start, Programs, Microsoft SQL Server, or from within the Enterprise Manager from the Tools menu. When starting the Query Analyzer from the Enterprise Manager, connection information is passed through to SQL Server based on the registration data provided for the server. When starting the Analyzer from the Start menu, separate connection information can be supplied, which allows for security and alternate login use. Each reference to the Analyzer started from the Start menu can have different connection information supplied. NOTE The WHERE clause is always executed first to discover which rows should be looked at before other operations needed by a given SELECT clause. This acts as a layer of filtration in a basic SELECT query. The WHERE clause is easy to use, yet is very powerful. There is considerable benefit in creating resultsets to be used in reports. Many operators can be used in conjunction with the WHERE clause to provide completely flexible filtration. The WHERE and HAVING clauses in a SELECT statement control the rows from the source tables that are used to build the resultset. WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. HAVING is discussed later in Chapter 5, Advanced Data Retrieval and Modification. To address it now would require that you first understand grouping of data, which falls out of the immediate scope of discussion. After data selection has been determined, the actual data sent is ready for an ordering process if one has been defined. The ordering of data is optional and if it is not present in a request, the data is sent in an order determined at the data source.

For high quality website hosting services please check java web hosting website.

200 Part I EXAM PREPARATION save time as well as provide other coding benefits. Look at the following example: Use Pubs SELECT a.Au_id, a.Au_fname FROM Authors AS a It is also possible to have multiple table aliases assigned in the FROM statement. This is a very common practice to ease coding of columns from a variety of sources. It is also used in joining tables to provide more definition to the join operation. When using data from two or more tables, each table alias assignment is used in a prefix within the column list to ensure data pointers are defined to the correct table source: SELECT a.Au_id, ta.Title_id FROM Authors AS a INNER JOIN TitleAuthor AS ta ON a.Au_id = ta.Au_id This query selects Author ID from the Authors table and Title ID from the TitleAuthor table and groups them into one resultset. Joins are discussed in detail in Chapter 5. This is a powerful feature, especially when you need to create summary data. You can gather up the important information into a single resultset so that it can be coded more easily than it would be if you had to use the full table name as a prefix for every column name. Data is displayed unconditionally for the columns selected, except in instances where data rows have been filtered by a HAVING or WHERE condition, or a JOIN operation that limits the rows. The WHERE Clause Now that you ve seen the SELECT column list and FROM clause, it s time to move on to the additional optional specifications that enable you to further control what data is displayed. The WHERE clause is an optional specification that is used to limit the number of rows in the resultset based on defined restrictions. Restrictions are specified as conditional arguments, such as Salary>10000, LastName LIKE G% , or State = FL . See Filtering Data, later in this chapter. Most SELECT statements are used to display several columns of data from a table, which requires that conditions be met pertinent to the application. The general SELECT, FROM, and WHERE format is illustrated in Step by Step 4.1:

For high quality website hosting services please check java web hosting website.