SQL IN A NUTSHELL

Structural Query Language (SQL) is the universal language that is used to communicate with modern database systems. SQL commands are used to retrieve (SELECT) insert new data and update or delete existing data in a database.

There are only four essential SQL query commands: SELECT, INSERT, UPDATE and DELETE.  However, because the Recordset dialog box currently only handles the SELECT and the Advanced mode only support multiple tables, you need to help a working knowledge of all of them. The following typographic conventions will be used:

• SQL keywords are required and are case-insensitive; however, best practice is to use uppercase.
• Expressions that are optional are in square brackets
• A pipe ( | ) separates alternate options
• Whitespace is ignored except for function (i.e., RAND(). It is best practice to split queries over several lines for best readability
• SQL command and their clauses must be in the correct order. For example, LIMIT cannot come be ORDER BY

NOTE: Only the most common statements are discussed in this tutorial.
NOTE: SQL uses queries (SELECT, INSERT, UPDATE and DELETE), clauses (i.e., WHERE), keywords (i.e., DISTINCT) and functions(i.e., COUNT(), RAND( ) ).

SELECT

The SELECT query is used, as the name suggests, to "select" or retrieve records from one or more tables FROM  a database. Notice that the only required elements are the SELECT and FROM statements. The SELECT statement specifies the column, set of columns or the expression to query. The FROM is used to specify the table to retrieve from.  Unlike the other three queries that will be discussed, the SELECT query has many CLAUSES that can be added to it.

SYNTAX:

SELECT [DISTINCT] selected_columns
[AS alias_name]
FROM selected_tables
[WHERE where_condition]
[GROUP BY
[HAVING
[ORDER BY column_name | expression] | [ASC | DESC]
[LIMIT [row_offset, ] row_count]

SELECT AND FROM CLAUSE

The SELECT clause must at minimum specify what database table to query (SELECT) and what columns to return (FROM).

NOTE: The WHERE clause is optional, but is used to limit the number of records returned. If omitted, all records will be returned. Also, you should use the primary key to identify a unique row when using the SELECT query statement so that you do not return duplicate records needlessly..

The selected_columns is a comma-separated list of columns. The selected_tables is a comma-separated list of tables:

SELECT first_name, last_name
FROM students

An asterisk ( * ) could be used instead of scutellated to select all table columns:

SELECT *
FROM students

NOTE: While this is convenient, it is best practice to select only the columns you need to return for what you are doing. Otherwise, it will take longer to execute query. Also, when data is retrieved from multiple tables, all tables must be listed.

When you are selecting from multiple tables, you must use unambiguous references syntax (which is the table name followed by a period and then the column name) if the same column names is used in more than one table.  While not required if there is not a conflict, it is best practice to use them.

GIVE EXAMPLE HERE....

The SELECT query can contains a number of OPTIONAL clauses, kewords, functions, etc.

DISTINCT CLAUSE

The DISTINCT clause could be added to tell the database to return only distinct rows to eliminate duplicated rows. For example, several employees are from the same cities so they will have the same cities listed in the database column. To look at all of the cities without duplicates use the distinct clause

SELECT DISTINCT last_name
FROM students

WHERE CLAUSE

Witout a WHERE clause, a SELECT query will return all records from a database.

The WHERE clause is used to specify a search criteria and uses various operators that will be discussed later to determine the criteria. In the case below, the equal sign ( = ) operator is used.

SELECT first_name, last_name
FROM students
WHERE first_name = 'Cornelius'

NOTE: Notice that the string must be wrapped with single quotes. If you used double quotes, you will receive an SQL Execution Error.

The WHERE clause can use a variety of operators. It acts as a ROW-LEVEL FILTER. Below is a list of the most common ones where most is self-explanatory. The comparison operators varry from one DBMS to another so it is important to check to see if your DBMS support these comparisons:

• Comparison (<, <=, =, <>, !=, >, >=, IS NULL, IS NOT NULL, IN( ) and BETWEEN min AND max)
  • IN( ) is used to evaluate a comma-separated list of values and returns true if one or more values is found (i.e., SELECT * FROM student WHERE grades IN ("A", "B", "C"))
  • Although BETWEEN min AND max is typically used with numbers, it can also be used with strings (i.e., BETWEEN "a" AND "f" returns true for a,b,c,d,e and f but not A,B,C,D, E or F.  To retrieve all records that began with a-f you could use SELECT * FROM CUSTOMERS WHERE CUSTOMERid BETWEEN 'H*' AND 'I*') Notice the asterisks (*).
    • Both IN( ) and BETWEEN min AND max can be preceded by NOT to create opposition comparison. For BETWEEN min AND max, some DBMS return the data that i s on the row that matches the specified minimum value in addition to that on ohter rows up to hte specified maximum value.  Other DBMS only return the data for rows that truly match between the specified minimum and maximum values.  
  • The IS NULL comparison is useful when you want to show all records that does not have a specify value (i.e., SELECT * FROM students WHERE city IS NULL. DO NOT USE "= NULL").
• Arithmetic (+, -, *, /, DIV and %) -- The DIV is integer division that returns the result of a division with an integer with no remainder (i.e., 5/2 = 2, 5 DIV 2 = 2). The modulo (%) is used to return a result with just the remainder (i.e., 5 % 2 = 1)
• Logical (AND, OR, &&, || )
• Pattern-matching (LIKE, NOT LIKE , LIKE BINARY and NOT LIKE BINARY) -- LIKE/NOT LIKE are case-insensitive matches and LIKE BINARY / NOT LIKE BINARY are case-sensitive matches. These operators are used in conjunction with two wildcards (The percent sign (%) matches any sequence of characters or none and the underscore ( _ ) is used to match exactly one character.) For example, WHERE first_name LIKE 'corn%' will return "Cornelius" or "corn" but not acorn. To match acorn as well, place a % in FRONT of the search pattern (i.e., WHERE first_name LIKE '%corn%') To match the literal percent sign or the underscore precede it with either with a backslash (i.e., \% or \_).
  • The percent (%) wildcard represents zero or more characters in a search pattern.  The underscore ( _ ) character always represents just a single character in a search pattern.  Like the "%" , the " _" can appera several times in a single search pattern . It can also be used with the "%" wildcard. (i.e., SELECT * FROM student WHERE first_name LIKE "Co_ne_ius" will return Cornelius).
    • There are three options when using the % wildcard
      • Co% -- will return all records STARTING with Co.
      • %Co -- will return all records ENDING with Co
      • %Co% -- will return all records that CONTAIN Co anywhere in the column.
        
  • Making comparison against a text string with SQL comparison operators (i.e., = ) will only return dat wht the column's text  EXACTLY matches the specifeid comparison text.  Using pattern matching operators (i.e., LIKE) against a text string does not require an exact match. This  is typically used to create search criteria for text with one or more wildcards against the column string.
  • If you want a SET of conditions to be considered, you can group them.  However, it is important to note the conditions are evaluated from LEFT to RIGHT and that the logical OR operator takes precedence over the AND operator:
    • WHERE first_name LIKE 'Corn%' OR first_name LIKE 'Jos%' AND last_name = 'Chopin' will return Cornelius Chopin, Joshua Chopin,  and Josiah Chopin. The reason for this is that the OR operator is successful regardless of the AND operator.
  • To ensure the AND operator is evaluated, the two conditions connected with the OR operator can be surrounded by parenthesis:
    
    • WHERE (first_name LIKE 'Corn%' OR first_name LIKE 'Jos%') AND last_name = 'Chopin' will return Cornelius Chopin, but not Joshua Chopin,  and Josiah Chopin.
  • TIP: Always use parenthesis to explicity specify the evaluation order in any WHERE clause that includes both OR and AND.
  • Some DBMS support powerful search patterns in the form of regular expressions using the REGEXP keyword. Special characters are added to text in the search pattern to determine the result. Below are a few examples:
    • "A" -- matches a SINGLE character
    • "[ABC]" -- matches a LIST of characters
    • "[A-K]" -- A RANGE of CHARACTERS
    • "[0-10]" -- a RANGE of NUMBERS
    • "^M" -- A SINGLE character at the BEGINNINGG of a text string
    • "H$" -- a SINGLE character at the END of a text string
  • TIP: Place the NOT keyword in front of the REGEXP keyword to return all strings that DO NOT match the expression.
  • TIP: Single characters can be matched by a dot. For example, "^.....$" will match any string that contains exactly five characters.
    

NOTE: The != (not equal to) and the || (logical or) are used by other database systems. Avoid using || or OR because they have different meaning in standard SQL.

AS CLICES

To return an ALIAS name for the returned column or set of columns, the key word AS can be used:

SELECT first_name, last_name
AS 'Mickey Mouse'
FROM students
WHERE first_name = 'Cornelius'

GROUP BY CLAUSE

The GROUP BY clause arranged data into logical groups by specifying a column name to group by. The COUNT( ) function is used in conjunction with the GROUP BY clause to operate on each gorup to return the number of items in each group.

SELECT *
FROM CUSTOMERS
GROUP BY city

SEE SQL IN EASY STEPS -- PAGE 142

Below is a list of built-in functions:

COUNT -- get a COUNT of records (i.e., SELECT COUNT (*) FROM CUSTOMERS)

SUM -- finds MINIMUM value of a set of records

MIN -- finds the MINIMUM value of a set of records

MAX -- finds the MAXIMUM value of a set of records

AVG -- finds the AVERAGE value of a set of records

HAVING CLAUSE

The HAVING clause is used to filter GROUP, whereas, the SELECT clause is used to filter rows. Also, the HAVING clause filter data AFTER records are grouped; whereas, the WHERE clause filter records BEFORE data is grouped. You can us HAVING with GROUP BY. This clause lets you specify ADDITIONAL criteria tha tthe records must meet.

SELECT *
FROM CUSTOMERS
GROUP BY city
HAVING city LIKE 'Aus%'

See SQL in Easy steps -- page 144.

ORDER BY CLAUSE

The ORDER clause specifies the alphabetical or numerical sort "order" of the records returned. It can be specified as a single column, a coma-separated list of columns or an expression such as the random function RAND( ). The default order is ascending (A-Z or 0-9) and does not need to be specified. But you can specify DESC to reverse the order.

NOTE: The data returned by a SELECT statement may not always appear in the same order as the database table's rows especially after an INSERT query.

CAUTION: An ORDER BY clause must only appear as the final clause of a SELECT statement.

TIP: Records retrieved from a column can be sorted by the order of another column whose data is not actually retrieved.

TIP: The ORDER BY clause can also retrieved records from multiple columns.

SELECT first_name, last_name
FROM students
ORDER BY last_name, first_name.

NOTE: The column will first be sorted by first name and if there is a tie (someone with the same last name) then the column will be sorted by first_name to BREAK THE TIE.

 The ORDER BY clause can also refer to column by its position instead of its name. The first column is position 1, the second column is position two, etc. This works only with retrieved columns ONLY. In the example below notice that last_name is the position of column 2 and it is RETRIEVED.

SELECT first_name, last_name, address
FROM students
ORDER BY 2

LIMIT CLAUSE

The LIMIT  clause, as the name implies, "limit" the number of records returned. It take a SINGLE number the represent the maximum number of records to return from the database or TWO NUMBERS separated by commas that represents the RANGE where the first number is represents the number of rows to SKIP and the second number to total number to return. For example, LIMIT 20,100 returns 21-100. If fewer results exist than the limit specified, only the number or records that are available are returned.

For more details on SELECT, go to http://dev.mysql.com/doc/refman/5.0/en/select.html

INSERT

The INSERT query is used, as the name implies, to "insert" or add a NEW record or a set of NEW records INTO a database table. In MySQL the word INTO is optional. The INSERT statement DOES NOT use the WHERE statement.

SYNTAX:

INSERT [INTO] table_name (column_names)
VALUES (values)

The column_names and values are comma-separated lists and both must be in the same order but does not have to match the order of the columns in the database. The columns  list does not have to include all of the database table columns providing that the omitted columns allow NULL values. This allow the INSERT query to insert partial rows. Primary key column with auto_increment should not be included. Also, columns with defaults values need not be included as their value will be automatically inserted if there is a default value.

INSERT INTO students (first_name, last_name, city)
VALUES ('Cornelius', 'Chopin', 'Round Rock')

CAUTION: All text data values must be surrounded by quotes.

To insert MULTIPLE records into a database at the same time, add parenthesis around the values and separate them with commas:

INSERT INTO students (first_name, last_name, city)
VALUES ('Cornelius', 'Chopin', 'Round Rock') , ('Joshua', 'Chopin' , 'Austin), ('Josiah' , 'Chopin', 'Georgetown)

NOTE: Any columns omitted from an INSERT query are set to their DEFAULT values if there are any.  EXCEPTION: The primary key value is automatic set because it is set to auto_increment and as a result, this column should always be left out.

SYNTAX 2:
Records can be inserted from one table to another with the INSERT SELECT query.

INSERT [INTO] destination_table_name (column_names)
SELECT * FROM  source_table

The following example copies all records from students table to employees table:

INSERT INTO employees (first_name, last_name, city)
SELECT * FROM students.

For more details on INSERT, go to http://dev.mysql.com/doc/refman/5.0/en/insert.html

UPDATE

The INSERT query is used, as the name implies, to "update" or edit an EXISTING record or a set of EXISTING records INTO a database.

SYNTAX:

UPDATE table_name
SET column_name = value [, column_name = value]
[WHERE where_expression]

CAUTION: The WHERE filter tells which record or records should be updated. Without it, all of the record in the database will be given the same value. NOT GOOD!!!!. Also, you should use the primary key to identify a unique row when using the UPDATE query statement so that multiple rows do not get accidently inserted if a row have to same value.

TIP: Is is helpful to remove all the values in a database column using an UPDATE query to set each column to NULL -- providing that the column definition allows NULL values.

UPDATE students
SET 'first_name' = 'Rickey'
WHERE first_name = 'Cornelius'

For more details on UPDATE, go to http://dev.mysql.com/doc/refman/5.0/en/update.html

DELETE

The DELETE query is used, as the name implies, to "delete" an EXISTING record or a set of EXISTING records even ALL records FROM a database.

SYNTAX:

DELETE FROM table_name
[WHERE where_expression]

CAUTION: You need to exercise extreme caution because if you forget to add a legitimate WHERE statement, ALL of the records will be deleted from the database. Not good!!!!. Also, you should use the primary key to identify a unique row when using the DELETE query statement so that multiple rows do not get accidently deleted if a row have to same value.

NOTE: It is best practice to prompt the user that a record or set of records will be deleted from a database via a dialog box and to ask for their confirmation to delete or abort the operation.

NOTE: The DELETE statement does not delete the table but the CONTENT of the table. To delete a table, you have to use the DROP statement.

For more details on DELETE, go to http://dev.mysql.com/doc/refman/5.0/en/delete.html