Web Hosting Java Blog

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 271 GROUPING DATA WITH TRANSACT-SQL . Group data using Transact-SQL. SQL Server is all about reporting. If you think that the database you re designing is for storing data, you are looking at design from the wrong angle. Reporting is typically how things come out of the database. If your database is for a payroll system, reporting is how it prints checks. If your database is for a cargo shipping application, then reporting is how it prints invoices and how many widgets the company will need to make next month if sales continue at the same rate. Reporting is what makes money for companies. Without effective ways of reading and presenting data, there are no payroll checks, shipping invoices, or sales projections. Reporting is being able to count things, average things, find maximum values, and find minimum values, and do all that over different categories of data. If you ve ever been involved in a report design, then you know that most reports are all about grouping different entities together and describing how those entities behave. How many widgets did we sell last month, what was the average cost to produce, and what was the average selling price? Those are the things that drive how business is done. This section is going to cover big topics. It describes how to aggregate data, how to perform operations on those aggregates, and how to do computations across aggregates. Remember, it s all about the slice and dice. Aggregate Functions Aggregate functions are functions that provide summary data about sets. Questions like How many rows are in that table? How many widgets did we sell last week? and What is the average price we charged for a widget? are all answered with aggregate functions. Table 5.1 provides a list and a brief description of each aggregate function. This section also covers what they do and their syntax.

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

270 Part I EXAM PREPARATION IN THE FIELD UNION OPERATORS AND DATABASE DESIGN Recall for a moment Chapter 2, Database Modeling. Remember that the elements that make up a database are called tables, and tables should classify entities. A person is an entity; a company is an entity; a sale of a product is another entity. If you have one table for companies and a separate table for individual people, and you use those tables to track sales, it s pretty natural to write a query that would union the table of people with the table of companies to provide a summary report or even just a list of mailing addresses for a new company catalog. On the other hand, if you have several different tables of company information, and you re joining the tables together with a UNION, you may have something wrong with your database design. Whenever you use a UNION, ask yourself why the tables on which you are using the UNION are separate tables. Is it because they represent distinct entities, or because the database isn t designed the way it should be? In general, an overuse of the UNION operator is indicative of bad design. Objects that are so alike in structure that they can be joined with a UNION should in all probability be in the same table in the first place. One unique feature of the UNION is that it automatically removes duplicates from the final resultset. So, if the Table1 and Table2 tables have rows that are identical, SQL Server automatically filters them all out. Of course, this does have a great deal of overhead associated with it. To avoid that overhead, if you don t care about duplicates, you can use the UNION ALL command, like this: SELECT A, B, C FROM Table1 UNION ALL SELECT First, Second, Third FROM Table2 You can now write queries that join data horizontally across columns with the various JOIN clauses and that join data vertically across rows with the UNION operator. You ve also packed a few additional tools such as table aliases and derived tables into your toolbox. These are all foundational components for the next piece, grouping data.

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

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 269 INSERT INTO Table1 VALUES (1, 3.14, 42 , Bogus ) INSERT INTO Table1 VALUES (2, 2.1828, 93 , Data ) INSERT INTO Table2 VALUES (123.45, 3, 16) INSERT INTO Table2 VALUES (456.78, 4, 29) SELECT A, B, C FROM Table1 UNION SELECT First, Second, Third FROM Table2 Notice that the column names are specified. If you use SELECT * with both, they have an inconsistent number of columns, and you ll get an error, All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists. This returns successfully, and the data types are a numeric(5,2), a float, and an int. The numeric(5,2) is the compromise between an int and a numeric(5,2); the float is the compromise between a float and an int; and the data is converted to int because all the varchar values in Table1 can convert to int. If there had been a string that couldn t convert to an int, SQL Server would have thrown an error message. If it can t convert a string to a numeric value, it throws an error. Three additional notes on UNION. First, if you want to sort a UNION, you put the ORDER BY after the last SELECT statement, like this: SELECT A, B, C FROM Table1 UNION SELECT First, Second, Third FROM Table2 ORDER BY 1 Next, if you want to do a SELECT…INTO operation, you need to do it as follows: SELECT A, B, C INTO #UnionOutput FROM Table1 UNION SELECT First, Second, Third FROM Table2 Finally, the column names returned are taken from the first query in the set of UNION operators. So, in this example the columns would be named A, B, and C.

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

268 Part I EXAM PREPARATION SELECT statement in the list must return the same number of columns, and the columns must be of compatible types. So, if the first SELECT statement returns an int, a float, a varchar(15), and a char(6), the second has to contain two pieces of data that can be converted to numbers followed by two pieces of data that can be converted to characters. So, what happens if the columns are different types? Well, if they are compatible, meaning that the types and data can be implicitly converted, then the output set will contain as close to the types of the first SELECT as possible, with the following rules for a given column: If any of the SELECT statements return a mix of variable-length and fixed-length fields, the output is variable length. If the SELECT statements contain columns of different lengths, the longest length is used for the output. If the two values are numerics, the value with the most precision is used. So, if you have an integer and a float, the output is a float, because a float has the most precision. If you have an integer and a string that is a number (such as 42), then you will have an integer; but, if the string was 42.00, you d have a numeric type that could contain two decimal places and a full integer. These are the rules of thumb that ll get you through most situations. If you want to see the entire set of data type precedence rules, it is available in blogs Online. Search the index for Precedence, and choose the article on Data Type Precedence. Here s an example of a UNION. This example uses a different set of sample data than the rest of the chapter, because of the type conversion topics: CREATE TABLE Table1 ( A int, B float, C varchar(15), D char(10) ) CREATE TABLE Table2 ( First numeric(5,2), Second int, Third int )

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

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 267 The percent sign in this example is the modulo operator: it returns the remainder of the first number divided by the second number. Basically, what this SELECT statement does is return the names of all the odd-numbered (divisible by 2 with a remainder of 1) objects in the current database. When the ID modulo 2 is 1, then it s an odd number, and the CASE statement returns 1, which the WHERE clause then compares to the number 1, and the row is included in the resultset. Otherwise, the CASE statement returns 0, which does not equal one, so the row is not included in the resultset. The keen of wit will note that a better way to write this would be: SELECT name FROM sysobjects WHERE id % 2 = 1 That, however, would not demonstrate the point of using CASE statements in a WHERE clause, nor would it be nearly as convoluted. It would, however, be readable and efficient. You should be aware of a couple of shortcuts. The ISNULL function is a great way to handle NULL values without using a CASE statement. Instead of writing this: SELECT CASE sid WHEN null THEN 0 ELSE sid END FROM sysusers you could write this statement, which does the same thing: SELECT isnull(sid, 0) FROM sysusers Another statement that s a shortcut for a CASE statement is called COALESCE. It takes a series of values and returns the first one that s not null. You could rewrite the preceding statement with: SELECT coalesce(sid, 0) FROM sysusers and get the same results. Now that you ve got the CASE statement down, you can learn how to join tables together end-to-end with the UNION operator. The UNION Operator The UNION operator is used to join two queries together end-toend, instead of side-by-side. A UNION operator takes the output of two or more SELECT statements and creates one recordset. Each

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

266 Part I EXAM PREPARATION Although that s certainly usable in this case, it s not usable if you want to embed the logic into an INSERT or UPDATE statement; plus the CASE involves a lot less typing. Here s another way to write the same code with a different syntax of CASE statement: DECLARE @Result char(10) SET @Result = CASE WHEN datepart(weekday, getdate()) = 1 .THEN Sunday WHEN datepart(weekday, getdate()) = 2 THEN Monday WHEN datepart(weekday, getdate()) = 3 THEN Tuesday WHEN datepart(weekday, getdate()) = 4 THEN Wednesday WHEN datepart(weekday, getdate()) = 5 THEN Thursday WHEN datepart(weekday, getdate()) = 6 THEN Friday WHEN datepart(weekday, getdate()) = 7 THEN ‘Saturday ELSE Unknown END This code will do the exact same thing, but notice that the expression isn t split up. That allows you to do something like this: DECLARE @Result char(10) SET @Result = CASE WHEN datepart(weekday, getdate()) in .(1,7) THEN Weekend ELSE Weekday END PRINT @Result You couldn t use the IN clause with the simple case covered previously, but the searched form of the CASE statement is allowed to use IN along with the rest of the comparison operators. The CASE expression is most often used in SELECT statements to modify data. For example, imagine that you want to find out which objects in your database were created on a weekend: SELECT name, CASE WHEN datepart(weekday,crdate) in (1,7) THEN . Weekend ELSE Weekday END FROM sysobjects CASE statements can also be used in the WHERE clause, an ORDER BY clause, or anywhere else an expression is allowed, like this: SELECT name FROM sysobjects WHERE CASE id % 2 WHEN 1 THEN 1 ELSE 0 END = 1

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

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 265 CASE Expressions A CASE expression works like an IF statement, but can be used in locations where an IF statement cannot. Specifically, a CASE expression returns one of a specific set of values based on the outcome of one or more expressions. Here s an example: Select CASE datepart(weekday, getdate()) WHEN 1 then Sunday WHEN 2 then Monday WHEN 3 then Tuesday WHEN 4 then Wednesday WHEN 5 then Thursday WHEN 6 then Friday WHEN 7 then Saturday ELSE Unknown END This example gets the day of week for today and turns it into a string that represents the text for the day of week. If, for some reason, the day of the week returned by the datepart function is invalid, it returns the string Unknown. The result is placed into the variable @Result. This is the proper syntax to use when the comparison you want to use is equality in this situation, datepart(weekday, getdate()) = 1. Notice that the expression starts with the keyword CASE and ends with the keyword END. This is the only time you can use an END without a BEGIN. This is called a simple CASE statement, contrasted against the searched CASE statement, discussed later in this section. Now, if you wanted to write code with a similar result, you can write this: DECLARE @result varchar(30) IF datepart(weekday, getdate()) = 1 set @Result = Monday else if datepart(weekday, getdate()) = 2 set @Result = Tuesday else if datepart(weekday, getdate()) = 3 set @result = Wednesday else if datepart(weekday, getdate()) = 4 set @result = Thursday else if datepart(weekday, getdate()) = 5 set @result = Friday else if datepart(weekday, getdate()) = 6 set @result = Saturday else if datepart(weekday, getdate()) = 7 set @result = Sunday else set @result = Unknown PRINT @Result

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

264 Part I EXAM PREPARATION As you can see, there s a table here aliased to P that is actually a SELECT statement, and it s the same SELECT statement that was used earlier. So you can do some interesting things here with copy-andpaste: you can take a query and write another query around it. The problem is, as you can see from the example, the resulting query can be very difficult to format or read. Things that are difficult to format and read also tend to be difficult to optimize, modify, and debug. That s one example of using a query inside another query. Here s another. The IN Operator The IN clause can be used in comparisons inside nearly every SQL statement as an operator. The IN operator takes two arguments a value and a set and checks to see whether the value is part of the set. For example: SELECT * FROM Person WHERE PersonID IN (1, 3, 5) That s a good way to use the IN operator. It can also be used with select queries that return one and only one column, like this: SELECT * FROM Person WHERE PersonID IN (Select PersonID .from PersonAddress) In this case, the SQL statement returns every person who has an address, but it always returns each person only one time. If you run the same query with a join, you get back the same list, assuming each person has only one address. If some of the records in Person have more than one address, you end up with duplicates in the resultset. SELECT Person.* from Person INNER JOIN PersonAddress on PersonAddress.PersonID = .Person.PersonID This returns the same list, but SQL Server executes this differently. SQL Server is very efficient at processing joins, but it s not as efficient at processing IN clauses, so use IN clauses sparingly. Sometimes you need a bit more flexibility in your queries to decide what data to include, based on the data in other fields.

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

264 Part I EXAM PREPARATION As you can see, there s a table here aliased to P that is actually a SELECT statement, and it s the same SELECT statement that was used earlier. So you can do some interesting things here with copy-andpaste: you can take a query and write another query around it. The problem is, as you can see from the example, the resulting query can be very difficult to format or read. Things that are difficult to format and read also tend to be difficult to optimize, modify, and debug. That s one example of using a query inside another query. Here s another. The IN Operator The IN clause can be used in comparisons inside nearly every SQL statement as an operator. The IN operator takes two arguments a value and a set and checks to see whether the value is part of the set. For example: SELECT * FROM Person WHERE PersonID IN (1, 3, 5) That s a good way to use the IN operator. It can also be used with select queries that return one and only one column, like this: SELECT * FROM Person WHERE PersonID IN (Select PersonID .from PersonAddress) In this case, the SQL statement returns every person who has an address, but it always returns each person only one time. If you run the same query with a join, you get back the same list, assuming each person has only one address. If some of the records in Person have more than one address, you end up with duplicates in the resultset. SELECT Person.* from Person INNER JOIN PersonAddress on PersonAddress.PersonID = .Person.PersonID This returns the same list, but SQL Server executes this differently. SQL Server is very efficient at processing joins, but it s not as efficient at processing IN clauses, so use IN clauses sparingly. Sometimes you need a bit more flexibility in your queries to decide what data to include, based on the data in other fields.

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

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 263 Here s an example of using an ON clause to generate data for a custom order form: SELECT FirstName, LastName, ProductDescription FROM Person CROSS JOIN Product ORDER BY FirstName, LastName, ProductDescription The FROM clause of a SELECT statement is an incredibly complex piece of work. You have the different join types and their correlations all going on in there. So, how can you make it even more complex and harder to read? Derived Tables You can use derived tables to make your queries simpler to read, or at least simpler to write. To use a derived table, put a SELECT statement in parentheses in the FROM clause where you d normally put a table name. An alias is required for a derived table. Let s say that you have a query that looks something like this: SELECT Person.PersonID, FirstName, LastName, ProductID, .QtyPurchased FROM Sales RIGHT JOIN Person ON Person.PersonID = Sales.PersonID And you d like to get the address for the people returned as well. You ve already done this once in the earlier examples, but here s another way to do it: Select P.*, Address.StreetAddress, Address.City, .Address.ZipCode from (SELECT Person.PersonID, FirstName, LastName, .ProductID, QtyPurchased FROM Sales RIGHT JOIN Person ON Person.PersonID = Sales.PersonID) P INNER JOIN PersonAddress ON PersonAddress.PersonID = P.PersonID INNER JOIN Address ON Address.AddressID = PersonAddress. .AddressID

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