Overview Queries in Query Templates

Below you find typical sql queries. They are included with the querytree's templates.

sql query templates

 

Basic     

Joins         

Aggregate functions

duplicates query wizard.. and delete them

Opens the Ms Access's wizard.  The resulting query in SQL looks like the following. You get a new table with the duplicate values and the number of times they appear. Example: you want to find cities with more than 1 customers.

SELECT First(TableName.[Fieldname]) AS [Fieldname Field], Count(TableName.[Fieldname]) AS NumberOfDups
FROM TableName
GROUP BY TableName.[Fieldname]
HAVING (((Count(TableName.[Fieldname]))>1));

The following SQL deletes the duplicate values from the specified table, just one record remains.

DELETE [FieldName]

FROM TableName

WHERE id Not In (SELECT Max([id]) FROM [TableName] GROUP BY [FieldName]);

Find unmatched queries

Opens the Ms Access'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.

SELECT Table1.ID
FROM Table1 LEFT JOIN Table2 ON Table1.[ID] = Table2.[customerid]
WHERE (((Table2.customerid) Is Null));

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.

Average  

returns the average value of a numeric column.

SELECT AVG{FIELD} FROM {TABLE};

The following returns the average value of a numeric column grouped by a specified field.

SELECT DISTINCTROW {FIELD1}, Avg({FIELD2}) AS [Avg Of {FIELD2}] FROM {TABLE} GROUP BY {FIELD1};

Count distinct records(*)

returns the number of unique records in a specified table.

SELECT COUNT(*)
FROM (SELECT DISTINCT * FROM table)  AS table;

Count groupby 1 field 

returns the number of records grouped by 1 field, Null values not included.

SELECT {FIELD1}, Count({FIELD2}) AS CountOf{FIELD2} FROM {TABLE} GROUP BY {FIELD1};

Count groupby 2 fields

returns the number of records grouped by 2 fields, Null values not included

SELECT {FIELD1}, {FIELD2}, Count({FIELD3}) AS CountOf{FIELD3} FROM {TABLE} GROUP BY {FIELD1}, {FIELD2};

Count records(*)

returns the number of records in a specified table.  

SELECT Count(*) AS [Count Of {TABLE}] FROM {TABLE};

Count(column name)

returns the number of values (NULL values will not be counted) of the specified column.

SELECT COUNT(columnName)
FROM (SELECT DISTINCT columnName FROM tableName)  AS tableName;

Joins

Joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

Join inner

also called: join, returns all rows from 2 or more tables which match the join predicate.

SELECT * FROM {TABLE1} INNER JOIN {TABLE2} ON {FIELD1} = {FIELD2} ;

Join left

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.

SELECT * FROM {TABLE1} LEFT JOIN {TABLE2} ON {FIELD1} = {FIELD2} ;

Join right 

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.

SELECT * FROM {TABLE1} RIGHT JOIN {TABLE2} ON {FIELD1} = {FIELD2} ;

Join outer

returns all records from both tables even if no matching record exists.

SELECT * FROM {TABLE}, {TABLE1};

Join full outer

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.

SELECT * FROM {TABLE1} INNER JOIN {TABLE2} ON {FIELD1} = {FIELD2} UNION SELECT * FROM {TABLE1} LEFT JOIN {TABLE2} ON {FIELD1} = {FIELD2} WHERE  {FIELD1} IS NULL UNION SELECT * FROM {TABLE1} RIGHT JOIN {TABLE2} ON {FIELD1} = {FIELD2} WHERE  {FIELD2} IS NULL;

Max()

returns the largest value group by a specified field.

SELECT DISTINCTROW {FIELD1}, Max({FIELD2}) AS [Max Of {FIELD2}] FROM {TABLE} GROUP BY {FIELD1};

Min()

returns the smallest value group by a specified field.

SELECT DISTINCTROW {FIELD1}, Min({FIELD2}) AS [Min Of {FIELD2}] FROM {TABLE} GROUP BY {FIELD1};

First()

returns the first value of a specified column 

SELECT FIRST({FIELD}) FROM {TABLE};

Last()

returns the last value of a specified column 

SELECT LAST({FIELD}) FROM {TABLE};

Fraction

returns the number of records and determine what the fraction is of the total of records, grouped by the specified field.

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};

Percentage

returns the number of records in percentage.

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};

select The IN operator allows you to specify multiple values in a WHERE clause. SELECT ename WHERE ename IN ('value1', 'value2', ...)

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

Alias

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.

SELECT TOP 10 * AS Alias FROM {table};

Select where like

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. 

SELECT * FROM {table} WHERE {FIELD} LIKE 'ka%';

Select all the records where the fieldvalues are in the listed values:

SELECT * FROM {table} WHERE {FIELD} IN ('value1','value2','etc..');

Select all the records ordered by the specified field:

SELECT * FROM {TABLE} ORDER BY {FIELD}

Select into

The SELECT INTO statement selects data from one table and inserts it into a different table. Useful to make back ups .

Sum()

Returns the sum of the numeric values in a column, grouped by a specified field.

SELECT DISTINCTROW {FIELD1}, Sum({FIELD2}) AS [Sum Of {FIELD2}] FROM {TABLE} GROUP BY {FIELD1};

Top

Selects the specified [number of] values from a not sorted table and presents them in order.

SELECT DISTINCT TOP 10 {FIELD} FROM {TABLE} ORDER BY {FIELD} DESC;

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. 

Select the first 10 records:

SELECT TOP 10 * FROM {TABLE};

Select the first 10 values from the specified fieldname:

SELECT TOP 10 {Fieldname} FROM {table};

Union

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.

SELECT * FROM {TABLE1} UNION (SELECT * from {TABLE2});

UNION ALL -used to combine the result-set of two or more SELECT statements with duplicate values . 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.

SELECT * FROM {TABLE1} UNION ALL (SELECT * from {TABLE2});

Delete

removes some or all rows from a table. If no WHERE condition is specified, all rows will be removed.

DELETE * FROM {TABLE} WHERE {FIELD}={VALUE};

Drop

removes a table from the database. All the tables' rows, indexes and privileges will also be removed. The operation cannot be rolled back.

DROP TABLE {TABLE};

Insert

inserts a second specified table in your table.

Insert values

inserts single or multiple records into a table.

INSERT INTO table (column-1, column-2, ... column-n) VALUES (value-1, value-2, ... value-n); update -

Record(s) will be updated in the specified columns. If you omit the WHERE clause, all records will be updated.