A-+SQL+Part+II

=__**Using Wildcard Filtering**__=

__**Wildcards**__- special characters used to match parts of a value. __**search pattern**__- a search condition made up of literal text, wildcard characters, or any combination of the two.

Wildcard filtering is used when you need to filter against unknown values. An example of this would be filtering data by searching for a certain text. This can not be done with simple comparison operators it needs to be done by wildcards. Wildcards are actually characters which represent meanings within the SQL WHERE clauses. To use wildcards you must use the LIKE operator, this informs the DBMS (database management system) to have a search pattern of a wildcard rather than a straight match. There are three types of wildcards.


 * Percent Sign Wildcard %-** this is the most frequently used wildcard. The % sign tells the database to match any number of occurences of any character. For example if we use the search pattern "cat%" the DBMS (database management system) will retrieve any value that starts with cat regardless of how many other characters follow. Be aware that sometimes wildcards are case sensitive depending on your DBMS. The search pattern"%cat%" will result in a search pattern which matches any value containing the word cat anywhere within it, regardless of how many characters come before or after cat. Wildcards can be used in the middle of search patterns. The search pattern "d%m" will result in a search pattern of all items starting with d and ending with m. % represents zero, one, or more than one character. This means that by entering % you can receive matches that are exactly the same or have various characters and words associated with your search.

For more information visit http://msdn.microsoft.com/en-us/library/ms189454.aspx


 * Underscore Wildcard _-** the underscore wildcard is used just as percent sign wildcard except it does not match multiple characters, one single underscore matches a single character. This means that you need to know how many characters long the text is that you are looking for. For example if you are looking for something that is 3 characters long in front of dog you would use "_ dog" as a search pattern. This would produce results that have three characters in front of the word dog, such as "fat dog" or "bad dog". You would not receive any results that had more than 3 character or less than 3 characters in front of dog such as "black dog". A search pattern of dog with four underscores following it would look like "dog " and would produce results such as " dog food" or "dog dish". Underscore wildcards are helpful for when you know how many characters you will be searching for.

For more information visit http://msdn.microsoft.com/en-us/library/ms174424.aspx


 * Brackets Wildcard [ ]-** is used for when you are searching for something and you know the specific location of a certain character. Say you are looking for customers whose names started with T or D your search pattern would look like "[TD]%". This search pattern searches for all customers whose names begin with T or D because the [TD] only searches for one single character and then the % wildcard following it matches any number of characters that would follow the T or D. Since the [TD] comes before the % it means that T or D have to be the first character. If you wanted to search for something that ended with T or D your search pattern would look like "%[TD]".

For more information visit http://msdn.microsoft.com/en-us/library/ms179884.aspx

=__Grouping Data__=

Grouping is used when you want to divide data into sets where you can use this information to make calculations for each group. A clear example of this if found at http://www.w3schools.com/sql/sql_groupby.asp.

SELECT customer_id, SUM(invoice_amt) FROM Invoices GROUP BY customer_id customer_id : invoice_amt 13 : 45 15 : 27 17 : 72 19 : 175 Your results for this would be two columns, customer id and invoice amt. By using GROUP BY the information is sorted by the customer_id. This makes the SUM of invoice_amt to be calculated once per customer_id instead of calculating the SUM of invoice_amt for all of the customer_ids combined. The GROUP BY tells the DBMS to group the data first by customer_id and then find the SUM of invoice_amts for each individual group "customer_id".
 * Creating Groups-** you can create groups by using the GROUP BY clause in your select statement. An example of this could be as follows.
 * INPUT**
 * OUTPUT**

You can also enter more than one GROUP BY clause. This is called nesting. It allows you to have more control over how your data is grouped. When using nesting your data is always summarized by your last group specified in the GROUP BY clause. An example of this can be found at http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/8499;pt=8007. The following table was taken from this website.

code from titles group by pub_id, type** code
 * select pub_id, type, avg(price), sum(total_sales)

code pub_id type --   --  --- 0736    business       2.99  18,722 0736   psychology    11.48   9,564 0877   UNDECIDED      NULL    NULL 0877   mod_cook      11.49  24,278 0877   psychology    21.59     375 0877   trad_cook     15.96  19,566 1389   business      17.31  12,066 1389   popular_comp  21.48  12,875

code SELECT customer_id, SUM(invoice_amt) FROM Invoices GROUP BY customer_id HAVING SUM(invoice_amt) >=100 customer_id : invoice_amt 19 : 175
 * Filtering Groups**-not only can you search for certain groups using the GROUP BY clause you can also filter groups that you want to exclude from your search. If you are already familiar with SQL you might think you can use the WHERE clause. This is completely untrue instead you have to use the HAVING clause. However you can have a query which contains a WHERE and HAVING clause. An example of using the HAVING clause can be seen in the following query.
 * INPUT**
 * OUTPUT**

As you can see the query called for a group that had an invoice amount equal to or more than 100, and it returned exactly what it called for.

It is very important to note that grouping and sorting are not the same thing. ORDER BY always sorts the generated output from the query, GROUP BY just groups rows it may not put the groups in any particular order. GROUP BY is used in particular to get specific values for certain groups while ORDER BY is used specifically for putting output in order. Here is an example of a query using the ORDER BY clause. SELECT customer_id, SUM(invoice_amt) FROM Invoices GROUP BY customer_id HAVING SUM(invoice_amt) >=30 ORDER BY SUM(invoice_amt) customer_id : invoice_amt 13 : 45 17 : 72 19 : 175
 * Grouping and Sorting**
 * INPUT**
 * OUTPUT**

As you can see by the example the query called for customer ids and the sum of invoice amounts that were over 30, grouped by customer ids, and in order of invoice amounts.