A+-+SQL+Par+II

** Wildcard Filtering **  ** Caution: Searches may be case-sensitive depending on the DBMS and may return undesired results ** **Caution: Watch for trailing spaces.** >>  // **INPUT** // SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE ‘%bean bag%’;
 * ** Search patterns  **
 * A search condition composed of literal text, wildcard characters, or any combination of the two.
 * ** Wildcards  **
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Characters associated with specific meanings used to match parts of a value
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Used to create search patterns that can be compared against your data that cannot be done using simple comparison operators.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">One or multiple wildcards can be used anywhere in the search pattern.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">In order to use wildcards in search clauses, the LIKE operator must be used.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">LIKE instructs the DBMS that the following search pattern is to be compared using a wildcard rather than straight equality match.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Can be used only with text fields (strings), thus cannot be used to search fields of nontext datatypes.
 * **<span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">The Percent Sign % **
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Means match any number of occurrences with any character
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Represents zero, one or more than one characters at the specified location in the search pattern  <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">

// **OUTPUT** // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">prod_id prod_name <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">BNBG01 Fish bean bag toy <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">BNBG02 Bird bean bag toy <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">BNBG03 Rabbit bean bag toy <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Such a search pattern instructs to match any value that contains the text ‘bean bag’ anywhere in it, regardless of characters before or after that text.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">**The Underscore (_)**
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Used similarly to %, yet matches a single character.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Always matches one and only one character.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">// Microsoft Access may require the use of ? as a substitute. //

<span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">// **INPUT** // SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE ‘ inch teddy bear’; // **<span style="FONT-FAMILY: Arial, Helvetica, sans-serif">OUTPUT ** //<span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> prod_id prod_name BNBG02 12 inch teddy bear BNBG03 18 inch teddy bear <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> The search pattern used in this WHERE clause specifies two wild cards followed by literal text; the underscore matches the 12 in the first row and the 18 in the second. The 8 inch teddy bear did not match because the search pattern requires two wildcard matches not one. >> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> To find all contacts whose names begin with the letter J or M: //**I NPUT **// <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">SELECT cust_contact FROM Customers WHERE cust_contact LIKE ‘[JM]%’ ORDER BY cust_contact // **OUTPUT** // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">cust_contact <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> Jim Jones John Smith Michelle Green <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">The [JM] matches any contact name that begins with either of the letters within the brackets, and it also matches a single character. This means any names longer than one character do not match. Thus the % wildcard after the [JM] matches any number of characters after the first letter. <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> **<span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Understanding Data Grouping  ** <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> // **<span style="FONT-FAMILY: Arial, Helvetica, sans-serif">INPUT ** // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">SELECT vend_id, COUNT (*) AS num_products <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> FROM Products GROUP BY vend_id // **<span style="FONT-FAMILY: Arial, Helvetica, sans-serif">OUTPUT ** //<span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> vend_id num_prods BRS01 3 DLL01 4 FNG 2 <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> The GROUP BY clause instructs the DBMS to sort the data and group it by vend_id. This causes num_prods to be calculated once per vend_id rather than once for the entire table. Because GROUP BY was used, it is not necessary to specify each group to be evaluated and calculated. > o   Optional clause sometimes supported within GROUP BY >  o   Can be used to return all groups, even those that have no matching rows.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">**The Brackets [ ]**
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Used to specify a set of characters, anyone of which must match a character in the specified position (the location of the wildcard).
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">// May not be supported by all DBMS. //
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">This wildcard can be negated by prefixing it with ^.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">i.e. if you were looking for contacts that do not begin with J or M.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">The same result can be used through the NOT operator.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">// Microsoft may require the use of ! instead of ^ to negate a set. //
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">**Tips for Using Wildcards**
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Wildcard searches typically take far longer to process than any other search types.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Don’t overuse wildcards.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Try not to use wildcards at the beginning of the search pattern unless absolutely necessary as these are the slowest to process.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Pay careful attention to the placement of wildcard symbols.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">**Grouping**
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">allows the division of data into logical sets so that aggregate calculations can be performed on each group\
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">**Creating Groups**
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Use the GROUP BY clause in the SELECT statement.
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Rules to GROUP BY **<span style="FONT-FAMILY: Arial, Helvetica, sans-serif"><span style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-fareast-font-family: Wingdings; mso-bidi-font-family: Wingdings">
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">GROUP BY clauses may contain as many clauses as desired, enabling nesting of groups.
 * <span style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-fareast-font-family: Wingdings; mso-bidi-font-family: Wingdings">   <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">If groups are nested in the GROUP BY clause, all the columns specified are evaluated together when grouping is established.
 * <span style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-fareast-font-family: Wingdings; mso-bidi-font-family: Wingdings">   <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">If an expression is used in the SELECT statement, that same expression must be specified in GROUP BY.
 * <span style="FONT-SIZE: 10pt; FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">   <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Aliases cannot be used
 * <span style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-fareast-font-family: Wingdings; mso-bidi-font-family: Wingdings">   <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Most SQL implementations do not allow GROUP BY columns with variable length datatypes (such as text or memo fields).
 * <span style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-fareast-font-family: Wingdings; mso-bidi-font-family: Wingdings">   <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Every column in your SELECT statement must be present in the GROUP BY clause.
 * <span style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-fareast-font-family: Wingdings; mso-bidi-font-family: Wingdings">   <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">If the grouping column contains a row with a NULL value, NULL will be returned as a group.
 * <span style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-fareast-font-family: Wingdings; mso-bidi-font-family: Wingdings">   <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">// The GROUP BY clause must come after any WHERE clause and before any ORDER clause. //
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">**The ALL clause**

> o   Some SQL implementations allow specification of GROUP BY columns by the position in the SELECT list. > o   Not supported by all SQL implementations. > o   Risky in that it is highly susceptible to the introduction of errors when editing SQL statements. > o   Which groups to include and which to exclude. > o   Must filter based on complete groups, rather than individual rows. > o   Use HAVING instead of WHERE (they’re very similar) > <span style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-fareast-font-family: Wingdings; mso-bidi-font-family: Wingdings"> §  WHERE data before it is grouped, HAVING filters data after > <span style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-fareast-font-family: Wingdings; mso-bidi-font-family: Wingdings"> §  All the techniques and options pertaining to WHERE apply to HAVING // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">INPUT // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> SELECT cust_id, COUNT (*) AS orders FROM orders GROUP BY cust_id HAVING COUNT (*) >= 2; // OUTPUT // cust_id orders 10000001 2  The final line adds a HAVING clause that filters on those groups with the COUNT (*) >= 2 – meaning two or more orders. WHERE does work here because the filtering is based on the group aggregate value, not on the values of specific rows. To further filter the groups to include only the desired information. <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> §   What is Grouping and Sorting? <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> o   The group by function is used to specify whether a column in a table is collective with other columns in that same table. Below are the differences between Group by and Order by. Examples to follow. <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Differences between Group by and Order by  || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Order By  || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Group By  || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Sorts Generated Output || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Groups rows although they may not be in order. || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Any Columns may be Used (even if they aren't selected) || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Only selected columns may/must be used  || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Never Required || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Required if using columns with aggregate functions  || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Source – SQL in 10 Minutes 3rd edition by Ben Forta <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">SQL’s are best explained using examples. Below is a table from which we will be pulling only the data we would like to see using the group by/order by function. <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">John Smith || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/6/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Allan Babel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/6/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Tina Crown || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/6/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">John Smith || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/7/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">9 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Allan Babel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/7/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Tina Crown || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/7/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">10 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">John Smith || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/8/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Allan Babel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/8/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Tina Crown || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/8/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">9 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Source - <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">http://www.sql-tutorial.net/SQL-GROUP-BY.asp <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">For HRM using the SQL can make this table much easier to read and calculate information. Weekly hours can be calculated with a simple function. See below for the SQL used. <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">The results are as follows: <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">John Smith || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">25 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Allan Babel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">24 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Tina Crown || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">27 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">To make this information in consecutive order either by Employee or by EmployeeHours the Order by function comes in handy. See below: <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee // // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">ORDER BY Employee // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Allan Babel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">24 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">John Smith || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">25 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Tina Crown || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">27 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">This function allows for information to be taken from more than one table/worksheet and be put into one database of information. This information will not be stored anywhere in the file but will be able to be created using SQL statements. Using the SQL //SELECT//, **//this is one of the most important and powerful functions to learn and understand. //**  <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">This function can be used most effectively when having a large amount of information (such as the inventory at a store) and being able to figure out when the manager would need to purchase more of a certain product and who that vendor would be that they would need to purchase the product from. //In one way or another, the different tables need to be linked to one another by a common value such as the **Primary Key** we have used previously in our ERD’s.// <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> §   Information is never repeated. <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> §   If information changes, you can update 1 single file instead of multiple items in one database. <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> §   Since no data is repeated, the data will be consistent and up-to-date. <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> o   Enables more efficient storage <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> o   Easier manipulation of information <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> o   Greater scalability <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Using the tables below we are able to use the SQL Join function to put together any or all of the information we are looking for. <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> ** <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Employee Table ** <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">John Smith || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/6/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">13 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Allan Babel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/6/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">14 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Krysta Orzel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/6/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">15 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Shannon Perry || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/7/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">12 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">9 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Allan Babel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/7/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">14 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Krysta Orzel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/7/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">15 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">10 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">John Smith || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/8/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">13 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Allan Babel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/8/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">14 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">8 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Shannon Perry || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">5/8/2004 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">12 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">9 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> ** <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Department Table ** <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">12 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Sales  || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">13 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Engineering || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">14 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Clerical || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">15 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Marketing || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">SELECT Department name, Employee, (SUM) hours // // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">FROM Employee, Department // // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">WHERE Department.Department_ID=Employee.Department_ID // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">John Smith || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Engineering || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">16 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Allan Babel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Clerical || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">24 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Krysta Orzel || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Marketing || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">18 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Shannon Perry || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Sales || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">18 || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Looking at the input statements above you will be able to see that in the //SELECT// command two of the columns are from one table while the other is from another table. We then move to //FROM// where both of the tables are noted. //WHERE// is the most important statement in this file as you have to make sure to have the //primary key/foreign key// from each correct table. If //WHERE// were not included in the statement, every row from both files would be calculated into one file. (In the above example if we had not used the //WHERE// clause we would have created a table with 36 rows.  <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">  <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> Source - http://www.codinghorror.com/blog/archives/000976.html    <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">   <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Another way of writing the above example to retrieve the same results is as follows:   <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">  // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">SELECT Department name, Employee, (SUM) hours  // // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">FROM Department INNER JOIN Employee  // // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">ON Department.Department_ID=Employee.Department_ID  // <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">**Specifying** **Columns by Relative Position**
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">**Filtering Groups**
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Employee ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Date ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Employee ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Employee ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Lesson 12 – Joining Tables **
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Why do we use the function joining tables? **
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Joining tables allows for the following: **
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Creating a Join **
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Employee ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Date ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Department Id ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Department ID ** ||||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Department Name ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Department Name ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> || <span style="FONT-FAMILY: Arial, Helvetica, sans-serif"> ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Employee ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Department Name ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||
 * <span style="FONT-FAMILY: Arial, Helvetica, sans-serif">Hours ** ||