Query Templates

This topic provides templates that show the structure of generic queries that may be applied in individual situations.

Replace the meta names in angle < > brackets with the appropriate items to create real queries from these templates. Field names created by the query are given in boldface within square [ ] brackets. Otherwise, square brackets indicate optional items.

Note that SQL is case insensitive. The style below uses the traditional CAPITALS for SQL words, but this is not necessary. SQL is also insensitive to white space such as tabs, space characters and new lines so that one may adopt whatever writing style format is judged most legible and understandable. The style below uses traditional formatting.

Simple Query

SELECT <fields> FROM <tables> WHERE <condition> GROUP BY <fields> HAVING <criteria> ORDER BY <expression>;

Show New Columns Created with Computations

SELECT <field1>+<field2> AS [Sum], <field1>*<field2> AS [Product] FROM <table>;

Using Aggregate Functions

SELECT Max(<field>), Min(<field>), Avg(<field>) FROM <table>;

Total Records

SELECT Count(*) FROM <table>;

Total Records with Condition

SELECT Count(*) FROM <table> WHERE <condition>;

Linked Tables

SELECT DISTINCT <table1>.* FROM <table1> INNER JOIN <table2>

ON <table1>.<field1> = <table2>.<field2>;

Linked Tables with Condition

SELECT DISTINCT <table1>.* FROM <table1> INNER JOIN <table2>

ON <table1>.<field1> = <table2>.<field2> WHERE <condition>;

Grouping by Field

SELECT <field1>, Count(<field2>) FROM <table> GROUP BY <field1>;

Grouping with Filter

SELECT <field> FROM <table> GROUP BY <field>

HAVING Count(*)> 1;

Text Pattern Matching

SELECT * FROM <table> WHERE <field> LIKE "A%";

Text Pattern Matching using Regular Expression

SELECT * FROM <table> WHERE <field> LIKEX "[A-F].+";

Filtering by Certain Values

SELECT * FROM <table> WHERE <field>

IN (<value1>, <value2>, <value3>);

Filtering by Subquery

SELECT * FROM <table1> WHERE <field1> > ANY

(SELECT <field2> FROM <table2> WHERE <condition>);

Ordering by Field

SELECT * FROM <table> ORDER BY <field> ASC;

Top Records

SELECT TOP <number_of_records> FROM <table>

ORDER BY <field> DESC;

First and Last Value

SELECT First(<field>) AS [First], Last(<field>) AS [Last]

FROM <table>;

Parameterized Query

PARAMETERS <parameter> <type>;

SELECT <fields> FROM <tables> WHERE <condition-with-parameter>;

Update Query

UPDATE <table> SET <field>=<expression> WHERE <condition>;

Drop Table

DROP TABLE <table>;

Create Table

CREATE TABLE <name> (<field1> <type>, <field2> <type>);

Add Table Field

ALTER TABLE <name> ADD <field> <type>;

Add Table Field with Default Value

ALTER TABLE <name> ADD <field> <type> DEFAULT <default>;

Change Table Field Type

ALTER TABLE <name> ALTER <field> <type>;

Rename Table Field

ALTER TABLE <name> RENAME <field> TO <newName>;

Drop Table Field

ALTER TABLE <name> DROP <field>;

Delete Records from Table

DELETE FROM <table> WHERE <condition>;

Insert Records into Table

INSERT INTO <table> VALUES (<value1>, <value2>);

Select Records into New Table

SELECT <fields> INTO <newTable> FROM <table> WHERE <condition>;