MISY604 SQL

Aggregates

Called "set functions" Make it easier for us to perform common operations Only return a single value In the SELECT statement after Select Cannot be nested Cannot appear in WHERE clause Specifying conditions requires using either GROUP BY or HAVING clause, or a subquery Some ignore null values

ORDER BY

Comes after the GROUP BY clause Orders the selected data by one or more column names

SELECT statement

DML command for retrieving and displaying data Can include aggregates; non-aggregates are only included if specified in the GROUP BY statement Savage Fierce Warriors Get Hurt Often

GROUP BY

Organizes selected data into groups When used with aggregates, it controls the level at which the aggregates are computed Comes after FROM clause

Comparisons using a WHERE clause

SELECT column name FROM table name WHERE column name <operator> value; For numeric, a numeric value is placed after the operator For character, the characters comprising the value must be enclosed in single quotes

Arithmetic

SQL provides four basic functions Use AS to store results under new name, because the second column of the results will be derived from one of the tables original columns M/D are performed first, then the rest, unless parentheses are included then parentheses are first Can only be used in columns containing values Can be used together

ORDER BY statement

Used after the WHERE clause Is optional Can use column name or column number

DISTINCT

Used in the SELECT statement before the column name When used with COUNT and other aggregates, it can appear before the column name Adding it before tells SQL that each time the same value appears, only one of those values should be counted Can be used without aggregates; it appears immediately after SELECT but before the column name(s)

IS NULL and IS NOT NULL

Used in the WHERE clause Finds records with no value (nothing there; blank) Finds all records that have a value (something there) Can be any field regardless of type Cannot be used with simple comparisons (i.e. the = operator) Only select records that meet the requirements

IN and NOT IN

Used in the WHERE clause Restricts which records are selected Values consist of one or more values separated by commas and enclosed inner parentheses If CHAR, must be quoted in character strings Records returned are those where the field named <column name> contains one of the values specified in parentheses For the latter, the reverse is true Is the equivalent of grouping tests for equality or inequality together with ORs

WHERE statement

Used to specify conditions that a record must satisfy before it is returned Returns at least 1 values for every record in the table Lets us specify a predicate: something that is either true or false about each record in a table, and only those records for which the predicate is true will be returned

BETWEEN and NOT BETWEEN

Used with the WHERE clause Selects records where values of a specific field of record fall within a specified range Directly specifies the range of values The latter is the negative form

LIKE and NOT LIKE

Used with the WHERE clause to get a result set that matches the given string pattern Can only be used with CHARACTER columns Column name must identify a CHAR column and value must be a character string enclosed in single quotes Same as = and "not equal to" Power of these operators is based on defining two special wildcard characters: a percent sign and an underscore sign % matches character string value of any length _ matches any single character

HAVING

Works like a WHERE clause, but is applied to groups rather than individual records Only groups that meet criteria specified in this clause will be included in the query's results Comes after GROUP BY but before ORDER BY Can be used with or without GROUP BY

Inexact matching

Match against a pattern (LIKE / NOT LIKE operator)

Using NOT

NOT is used when you want to exclude a subject or term It will narrow your search results because it is not including a topic or category If you are retrieving too many records on an unrelated topic, try eliminating a word with the connector NOT Is NOT used to combine conditions in the WHERE clause Inserting this word reverses the condition

AND, OR, NOT

Boolean operators

FROM statement

Specifies the table(s) to retrieve information from Selects all columns in a table

Using OR

To find records that meet any of the conditions, enter the criteria on separate rows in the query grid: Enter the first criterion in the Criteria row. Enter the second criterion in the or row (the row immediately below the Criteria row). When using an OR construction with multiple fields, make sure that each criterion is on its own row. A record is selected and appears in the results of the query if one or both predicates are true If placed with AND, AND is evaluated first unless something is placed in closed parentheses it will be evaluated first

Using AND

To find records that meet two or more conditions in different fields, enter each of the criteria in the Criteria row. To find records that meet more than one condition in the same field, enter both criteria separated by the word AND in the Criteria row. If desired, one or both predicates can be enclosed in parentheses Is only true if BOTH parts are true