SQL All-in-One For Dummies
Book image
Explore Book Buy On Amazon
SQL is a popular and useful programming language. You can make SQL even more useful if you know the phases of SQL development, the criteria for normal forms, the data types used by SQL, a little bit about set and value functions, as well as some tips on how to filter tables with WHERE clauses.

Phases of SQL system development

In developing any system, you start at the beginning and go through to the end, and it’s no different with SQL. The following list shows you what to consider at each phase of the SQL development life cycle:

  • Definition phase: Precisely define the problem to be solved, its magnitude, and who will work on it.
  • Requirements phase: Develop a detailed description of exactly what the development effort will produce. Gather all relevant information and put it into a requirements document (Statement of Requirements). Get client signoff.
  • Evaluation phase: Determine exactly how you will meet the requirements. What tools will you use? How will you deploy your development team? Determine whether the job is doable within time and budget constraints.
  • Design phase: Create a database model and then design a database and database application that satisfy the terms of the requirements document.
  • Implementation phase: Build the database and the database application. Include copious documentation within the code and in external documents.
  • Final documentation and testing phase: Give the database and application a tough workout. Hit the system with every conceivable input condition and a few inconceivable ones. Try to overload it. See where it breaks. When it breaks, send it back to the implementers or even back to the designers. Document everything.
  • Maintenance phase: Fix latent bugs as they arise. Provide updates and enhancements called for by the client.

SQL criteria for normal forms

In SQL, normal forms are defining characteristics of relational databases. SQL forms get classified according to the types of modification anomalies they’re subject to. First, second, and third normal forms (1NF, 2NF, 3NF) serve as remedies to the three main sources of modification anomalies.

The normal forms are nested in the sense that a table that’s in 2NF is automatically also in 1NF. Similarly, a table in 3NF is automatically in 2NF, and so on. For most practical applications, putting a database in 3NF is sufficient to ensure a high degree of integrity. To be absolutely sure of its integrity, you must put the database into DK/NF.

The following lists lay out the criteria for each form:

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

Depending on their histories, different SQL implementations support a variety of data types. The SQL specification recognizes nine predefined general types, shown in the lists below

Exact numerics:

  • INTEGER

  • SMALLINT

  • BIGINT

  • NUMERIC

  • DECIMAL

  • DECFLOAT

Approximate numerics:

  • REAL

  • DOUBLE PRECISION

  • FLOAT

Boolean:

  • BOOLEAN

Character strings:

  • CHARACTER (CHAR)

  • CHARACTER VARYING (VARCHAR)

  • NATIONAL CHARACTER (NCHAR)

  • NATIONAL CHARACTER VARYING (NVARCHAR)

Datetimes:

  • DATE

  • TIME

  • TIMESTAMP

  • TIME WITH TIMEZONE

  • TIMESTAMP WITH TIMEZONE

Intervals:

  • INTERVAL DAY

  • INTERVAL YEAR

Large objects:

  • BLOB

  • CLOB

Collection types:

  • ARRAY

  • MULTISET

Other types:

  • ROW

  • XML

  • JSON

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.

String value functions

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
BTRIM Trims multiple characters both before and after the text
LTRIM Trims multiple characters to the left of the text
RTRIM Trims multiple characters to the right of the text
TRIM Trims off leading or trailing blanks
LPAD Adds padding characters to the left of the text
RPAD Adds padding characters to the right of the text
TRANSLATE Transforms a source string from one character set to
another
CONVERT Transforms a source string from one character set to
another

Numeric value functions

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

Datetime value functions

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
ANY_VALUE Returns a random value from a specified set of data
GREATEST Returns the largest value from a specified set of data
LEAST Returns the smallest value from a specified set of data
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

SQL WHERE clause predicates

Predicates boil down to either a TRUE or a FALSE result. You can filter out unwanted rows from the result of an SQL query by applying a WHERE clause whose predicate excludes the unwanted rows.

Comparison predicates

= Equal
<> Not equal
< Less than
<= Less than or equal
> Greater than
>= Greater than or equal

Other predicates

ALL BETWEEN
DISTINCT EXISTS
IN LIKE
MATCH NOT IN
NOT LIKE NULL
OVERLAPS SOME, ANY
UNIQUE

About This Article

This article is from the book:

About the book author:

Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Allen's activities, check out his online courses (at pioneer-academy1.teachable.com) and his blog (at www.allengtaylor.com). You can contact Allen at [email protected].

This article can be found in the category: