SQL criteria for normal forms
To ensure that database tables are designed in such a way that they will hold your data reliably, you need to be sure that they are not subject to modification anomalies. Normalizing your databases will give you that assurance. Compare the SQL criteria in the following list to the tables in your database. Doing so will alert you to the possibility of anomalies, when you find that your database is not sufficiently normalized.
First Normal Form (1NF):
-
Table must be two-dimensional, with rows and columns.
-
Each row contains data that pertains to one thing or one portion of a thing.
-
Each column contains data for a single attribute of the thing being described.
-
Each cell (intersection of row and column) of the table must be single-valued.
-
All entries in a column must be of the same kind.
-
Each column must have a unique name.
-
No two rows may be identical.
-
The order of the columns and of the rows does not matter.
Second Normal Form (2NF):
-
Table must be in first normal form (1NF).
-
All non-key attributes (columns) must be dependent on the entire key.
Third Normal Form (3NF):
-
Table must be in second normal form (2NF).
-
Table has no transitive dependencies.
Domain-Key Normal Form (DK/NF):
-
Every constraint on the table is a logical consequence of the definition of keys and domains.
SQL data types
Here’s a list of all the formal data types that ISO/IEC standard SQL recognizes. In addition to these, you may define additional data types that are derived from these.
Exact Numerics:
-
INTEGER
-
SMALLINT
-
BIGINT
-
NUMERIC
-
DECIMAL
Approximate Numerics:
-
REAL
-
DOUBLE PRECISION
-
FLOAT
- DECFLOAT
Binary Strings:
-
BINARY
-
BINARY VARYING
-
BINARY LARGE OBJECT
Boolean:
-
BOOLEAN
Character Strings:
-
CHARACTER
-
CHARACTER VARYING (VARCHAR)
-
CHARACTER LARGE OBJECT
-
NATIONAL CHARACTER
-
NATIONAL CHARACTER VARYING
-
NATIONAL CHARACTER LARGE OBJECT
Datetimes:
-
DATE
-
TIME WITHOUT TIMEZONE
-
TIMESTAMP WITHOUT TIMEZONE
-
TIME WITH TIMEZONE
-
TIMESTAMP WITH TIMEZONE
Intervals:
-
INTERVAL DAY
-
INTERVAL YEAR
Collection Types:
-
ARRAY
-
MULTISET
Other Types:
-
ROW
-
XML
SQL value functions
These SQL value functions perform operations on data. There are all kinds of operations that could conceivably be performed on data items, but these are some that are needed most often.
| Function | Effect |
|---|---|
| SUBSTRING | Extracts a substring from a source string |
| SUBSTRING SIMILAR | Extracts a substring from a source string, using POSIX-based regular expressions |
| SUBSTRING_REGEX | Extracts from a string the first occurrence of an XQuery regular expression pattern and returns one occurrence of the matching substring |
| TRANSLATE_REGEX | Extracts from a string the first or every occurrence of an XQuery regular expression pattern and replaces it or them with an XQuery replacement string |
| UPPER | Converts a character string to all uppercase |
| LOWER | Converts a character string to all lowercase |
| TRIM | Trims off leading or trailing blanks |
| TRANSLATE | Transforms a source string from one character set to another |
| CONVERT | Transforms a source string from one character set to another |
| Function | Effect |
|---|---|
| POSITION | Returns the starting position of a target string within a source string |
| CHARACTER_LENGTH | Returns the number of characters in a string |
| OCTET_LENGTH | Returns the number of octets (bytes) in a character string |
| EXTRACT | Extracts a single field from a datetime or interval |
| Function | Effect |
|---|---|
| CURRENT_DATE | Returns the current date |
| CURRENT_TIME(p) | Returns the current time; (p) is precision of seconds |
| CURRENT_TIMESTAMP(p) | Returns the current date and the current time; (p) is precision of seconds |
SQL set functions
The SQL set functions give you a quick answer to questions you may have about the characteristics of your data as a whole. How many rows does a table have? What is the highest value in the table? What is the lowest? These are the kinds of questions that the SQL set functions can answer for you.
| Function | Effect |
COUNT |
Returns the number of rows in the specified table |
MAX |
Returns the maximum value that occurs in the specified able |
MIN |
Returns the minimum value that occurs in the specified table |
SUM |
Adds up the values in a specified column |
AVG |
Returns the average of all the values in the specified column |
LISTAGG |
Transforms values from a group of rows into a delimited string |
Trigonometric and Logarithmic Functions
sin, cos, tan, asin, acos, atan, sinh, cosh, tanh, log(<base>, <value>), log10(<value>). ln( <value>)
JSON Constructor Functions
JSON_OBJECT
JSON_ARRAY
JSON_OBJECTAGG
JSON_ARRAYAGG
JSON Query Functions
JSON_EXISTS
JSON_VALUE
JSON_QUERY
JSON_TABLE
Knowing the MySQL WHERE clause format
The WHERE clause is used to modify a DELETE, SELECT, or UPDATE SQL query. This list shows the format you can use when writing a WHERE clause:
WHERE exp AND|OR exp AND|OR exp…
where exp can be one of the following:
column = value column > value column >= value column < value column <= value column BETWEEN value1 AND value2 column IN (value1,value2,…) column NOT IN (value1,value2,…) column LIKE value column NOT LIKE value
