ÿþ<HTML xmlns="http://www.w3.org/1999/xhtml"><HEAD><TITLE>SQL queries overview</TITLE> <meta name="description" content= "important Sql queries in Query Templates, an overview"> <meta name="keywords" content= "sql,sql code,query,queries, querytree"> <META content="text/html; charset=unicode" http-equiv=Content-Type> <META name=GENERATOR content="MSHTML 8.00.6001.18928"> <link href="../css/style.css" rel="stylesheet" type="text/css" > <link href="../css/4tops.css" rel="stylesheet" type="text/css" > </HEAD> <BODY style="margin-left:180px"> <H1>Overview Queries in Query Templates</H1> <P></P> <table style="width: 90%"> <tr><td style="width: 250px"> Below you find typical sql queries. They are included with the querytree&#39;s templates. <ul> <li><A href="#Find duplicates query wizard">Find duplicates query wizard</A></li> <li><A href="#Find unmatched queries">Find unmatched queries</A></li> </ul></td> <td><IMG src="sql_querytemplates.jpg" alt="sql query templates" width="358" height="210"></td> </tr> <tr><td style="width: 250px"> <H2>Basic operations</H2> <ul> <li><A href="#Select where like">Select where like</A></li> <li><A href="#Select into">Select into</A></li> <li><A href="#Insert">Insert</A> </li> <li><A href="#Insert values">Insert values</A></li> <li><A href="#deletes the duplicate values">Delete duplicate values</A> </li> <li><A href="#Union">Union</A></li> <li><A href="#Alias">Alias</A></li> <li><A href="#Delete">Delete</A></li> <li><A href="#Drop">Drop</A></li> <li><A href="#Count(column name)">Count(column name)</A></li> </ul> <H2>Joins</H2> <ul> <li><A href="#Join inner">Join inner</A></li> <li><A href="#Join left">Join left</A></li> <li><A href="#Join right">Join right</A></li> <li><A href="#Join outer">Join outer</A></li> <li><A href="#Full join">Full join</A></li> <li><A href="#Join full outer">Join full outer</A></li> </ul></td> <td style="width: 300px"> <H2>Aggregate functions</H2> <ul> <li><A href="#Max()">Max()</A></li> <li><A href="#Min()">Min()</A></li> <li><A href="#Top">Top</A></li> <li><A href="about:blank#Average">Average</A></li> <li><A href="#Sum()">Sum()</A></li> <li><A href="#FIRST()">First()</A> </li> <li><A href="#LAST()">Last() </A></li> <li><A href="#Count distinct records(*)">Count distinct records(*)</A></li> <li><A href="#Count groupby 1 field">Count groupby 1 field</A></li> <li><A href="#Count groupby 2 fields">Count groupby 2 fields</A></li> <li><A href="#Count records(*)">Count records(*)</A></li> <li><A href="#Count(column name">Count(column name</A>)</li> <li><A href="about:blank#Fraction">Fraction</A></li> <li><A href="#Percentage">Percentage</A></li> </ul></td></tr> </table> <H2> <A name="Find duplicates query wizard">Find duplicates query wizard.. and delete them</A></H2> <P>Opens the Ms Access&#39;s wizard.&nbsp; The resulting query in SQL looks like the following. You get a new table with the duplicate values&nbsp;and the number of times they appear. Example: you want to find cities with more than 1 customers.</P> <P>SELECT First(TableName.[Fieldname]) AS [Fieldname Field], Count(TableName.[Fieldname]) AS NumberOfDups<BR>FROM TableName<BR>GROUP BY TableName.[Fieldname]<BR>HAVING (((Count(TableName.[Fieldname]))&gt;1));</P> <P>The following SQL <A name="deletes the duplicate values">deletes the duplicate values</A> from the specified table, just one record remains.</P> <P>DELETE [FieldName]</P> <P>FROM TableName</P> <P>WHERE id Not In (SELECT Max([id]) FROM [TableName] GROUP BY [FieldName]); <H2><A name="Find unmatched queries">Find unmatched queries</A></H2> <P>Opens the Ms Access&#39;s wizard. The query you create list records that have no related records in a second table you specify. Example: find customers with no orders.</P> <P>SELECT Table1.ID<BR>FROM Table1 LEFT JOIN Table2 ON Table1.[ID] = Table2.[customerid]<BR>WHERE (((Table2.customerid) Is Null));<BR></P> <P>A crosstab query is a matrix, where the column headings come from the values in a field. You can use the data from 1 table at the time. </P> <H2><A name=Average>Average</A> &nbsp;</H2> <P>returns the average value of a numeric column.</P> <P>SELECT AVG{FIELD} FROM {TABLE};</P> <P>The following returns the average value of a numeric column grouped by a specified field.</P> <P>SELECT DISTINCTROW {FIELD1}, Avg({FIELD2}) AS [Avg Of {FIELD2}] FROM {TABLE} GROUP BY {FIELD1};</P> <H2><A name="Count distinct records(*)">Count distinct records(*)</A> </H2> <P>returns the number of unique records in a specified table. </P> <P>SELECT COUNT(*)<BR>FROM (SELECT DISTINCT * FROM table)&nbsp; AS table;</P> <H2><A name="Count groupby 1 field">Count groupby 1 field</A>&nbsp;</H2> <P>returns the number of records grouped by 1 field, Null values <STRONG> not</STRONG> included.</P> <P>SELECT {FIELD1}, Count({FIELD2}) AS CountOf{FIELD2} FROM {TABLE} GROUP BY {FIELD1};</P> <H2><A name="Count groupby 2 fields">Count groupby 2 fields</A></H2> <P>returns the number of records grouped by 2 fields, Null values <STRONG> not</STRONG> included </P> <P>SELECT {FIELD1}, {FIELD2}, Count({FIELD3}) AS CountOf{FIELD3} FROM {TABLE} GROUP BY {FIELD1}, {FIELD2};</P> <H2><A name="Count records(*)">Count records(*)</A></H2> <P>returns the number of records in a specified table.&nbsp;&nbsp;</P> <P>SELECT Count(*) AS [Count Of {TABLE}] FROM {TABLE};</P> <H2><A name="Count(column name)">Count(column name)</A></H2> <P>returns the number of values (NULL values will not be counted) of the specified column.</P> <P>SELECT COUNT(columnName)<BR>FROM (SELECT DISTINCT columnName FROM tableName)&nbsp; AS tableName;</P> <H2><A name=Joins>Joins</A></H2> <P>Joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. </P> <H2><A name="Join inner">Join inner</A></H2> <P>also called: join, returns all rows from 2 or more tables which match the join predicate. </P> <P>SELECT * FROM {TABLE1} INNER JOIN {TABLE2} ON {FIELD1} = {FIELD2} ;</P> <H2><A name="Join left">Join left</A></H2> <P>also called:left outer join, returns all rows from the left table, even if there are no matches in the right table, plus matched values from the right table. </P> <P>SELECT * FROM {TABLE1} LEFT JOIN {TABLE2} ON {FIELD1} = {FIELD2} ;</P> <H2><A name="Join right">Join right&nbsp;</A></H2> <P>also called: right outer join, returns all rows from the right table, even if there are no matches in the left table, plus matched values from the left table. </P> <P>SELECT * FROM {TABLE1} RIGHT JOIN {TABLE2} ON {FIELD1} = {FIELD2} ;</P> <H2><A name="Join outer">Join outer</A></H2> <P>returns all records from both tables even if no matching record exists. </P> <P>SELECT * FROM {TABLE}, {TABLE1};</P> <H2><A name="Join full outer">Join full outer</A></H2> <P>combines the results of both left and right outer joins. The joined table contains all records from both tables, and fill in NULLs for missing matches on either side. </P> <P>SELECT * FROM {TABLE1} INNER JOIN {TABLE2} ON {FIELD1} = {FIELD2} UNION SELECT * FROM {TABLE1} LEFT JOIN {TABLE2} ON {FIELD1} = {FIELD2} WHERE&nbsp; {FIELD1} IS NULL UNION SELECT * FROM {TABLE1} RIGHT JOIN {TABLE2} ON {FIELD1} = {FIELD2} WHERE&nbsp; {FIELD2} IS NULL;</P> <H2><A name=Max()>Max()</A> </H2> <P>returns the largest value group by a specified field.</P> <P>SELECT DISTINCTROW {FIELD1}, Max({FIELD2}) AS [Max Of {FIELD2}] FROM {TABLE} GROUP BY {FIELD1};</P> <H2><A name=Min()>Min()</A>&nbsp;</H2> <P>returns the smallest value group by a specified field.</P> <P>SELECT DISTINCTROW {FIELD1}, Min({FIELD2}) AS [Min Of {FIELD2}] FROM {TABLE} GROUP BY {FIELD1};</P> <H2><A name=FIRST()>First()</A></H2> <P>returns the first value of a specified column&nbsp; </P> <P>SELECT FIRST({FIELD}) FROM {TABLE};</P> <H2><A name=LAST()>Last() </A></H2> <P>returns the last value of a specified column&nbsp; </P> <P>SELECT LAST({FIELD}) FROM {TABLE}; </P> <H2><A name=Fraction>Fraction&nbsp;</A></H2> <P>returns the number of records and determine what the fraction is of the total of records, grouped by the specified field. <P>SELECT TOP 100 PERCENT {FIELD}, Count({FIELD})/( SELECT Count([{FIELD}]) AS [Count{FIELD}] FROM {TABLE} ) AS Fract{FIELD}, Count({FIELD}) AS CountContactName FROM {TABLE} GROUP BY {FIELD} ORDER BY {FIELD}; </P> <H2><A name=Percentage>Percentage</A></H2> <P>returns the number of records in percentage.</P> <P>SELECT TOP 100 PERCENT {FIELD}, 100*Count({FIELD})/( SELECT Count([{FIELD}]) AS [Count{FIELD}] FROM {TABLE} ) AS Perc{FIELD}, Count({FIELD}) AS CountContactName FROM {TABLE} GROUP BY {FIELD} ORDER BY {FIELD};</P> <P>select The IN operator allows you to specify multiple values in a WHERE clause. SELECT ename WHERE ename IN (&#39;value1&#39;, &#39;value2&#39;, ...) </P> <P>The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates. </P> <H2><A name=Alias>Alias</A></H2> <P>You can give a table or a column another name by using an alias. Useful if you have very long or complex table names or column names. As an alias name usually is short it makes it easier to write and to read queries. </P> <P>SELECT TOP 10&nbsp;* AS Alias FROM {table};</P> <H2><A name="Select where like">Select where like&nbsp;</A></H2> <P>The LIKE operator is used to search for a specified pattern in a column. The % can be used as wildcard before or after the pattern.&nbsp;</P> <P>SELECT * FROM {table} WHERE {FIELD} LIKE &#39;ka%&#39;;</P> <P>Select all the records where the fieldvalues&nbsp;are in the listed values:</P> <P>SELECT * FROM&nbsp;{table} WHERE {FIELD} IN (&#39;value1&#39;,&#39;value2&#39;,&#39;etc..&#39;); <P>Select all the records ordered by the specified field: <P>SELECT * FROM {TABLE} ORDER BY {FIELD} </P> <H2><A name="Select into">Select into</A></H2> <P>The SELECT INTO statement selects data from one table and inserts it into a different table. Useful to make back ups .</P> <H2><A name=Sum()>Sum()</A></H2> <P>Returns the sum of the numeric values in a column, grouped by a specified field.</P> <P>SELECT DISTINCTROW {FIELD1}, Sum({FIELD2}) AS [Sum Of {FIELD2}] FROM {TABLE} GROUP BY {FIELD1};</P> <H2><A name=Top>Top</A></H2> <P>Selects&nbsp;the specified&nbsp;[number of] values from a not sorted table and presents them in order.</P> <P>SELECT DISTINCT TOP&nbsp;10 {FIELD} FROM {TABLE} ORDER BY {FIELD} DESC;</P> <P>Combinations with TOP are also used to specify the number of records to return. This can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.&nbsp;</P> <P>Select the first 10 records:</P> <P>SELECT TOP&nbsp;10 * FROM {TABLE}; </P> <P>Select the first 10 values from the specified fieldname:</P> <P>SELECT TOP 10 {Fieldname} FROM {table};</P> <H2><A name=Union>Union</A> </H2> <P>The UNION operator is used to combine the result-set of two or more SELECT statements, only distinct values. Note: each SELECT statement must have the same number of columns. The columns must have similar data types.The columns must be in the same order. The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.</P> <P>SELECT * FROM {TABLE1} UNION (SELECT * from {TABLE2});</P> <P>UNION ALL -used to combine the result-set of two or more SELECT statements <STRONG> with duplicate values</STRONG>. The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION. </P> <P>SELECT * FROM {TABLE1} UNION ALL (SELECT * from {TABLE2});</P> <H2><A name=Delete>Delete</A>&nbsp;</H2> <P>removes some or all rows from a table. If no WHERE condition is specified, all rows will be removed. </P> <P>DELETE * FROM {TABLE} WHERE {FIELD}={VALUE};</P> <H2><A name=Drop>Drop</A> </H2> <P>removes a table from the database. All the tables&#39; rows, indexes and privileges will also be removed. The operation cannot be rolled back. </P> <P>DROP TABLE {TABLE};</P> <H2><A name="Insert">Insert</A>&nbsp;</H2> <P>inserts a second specified table in your table. </P> <H2><A name="Insert values">Insert values </A></H2> <P>inserts single or multiple records into a table.</P> <P>INSERT INTO table (column-1, column-2, ... column-n) VALUES (value-1, value-2, ... value-n); update -</P> <P>Record(s) will be updated in the specified columns. If you omit the WHERE clause, all records will be updated. </P> <H2></H2> </BODY></HTML>