Before we start talking about SQL, we first need to define some basic concepts related to relational databases. One of these concepts is a relation - a relation is analogous to an entity type from the entity-relationship model. Later, we will also see that in fact, a relation is one table in a database. Similarly, a relational schema is a collection of relations, and represents the entire database. The reason we are mentioning this is the fact that the entire concept of relational databases has a mathematical foundation. All of the operations we will be performing on tables (relations) are based on a special field of mathematics called relational algebra. We will not focus on the mathematics in this series, but it is important to know what a relation is and understand that every operation is strictly defined. From this point on, we will be using the terms table and relation synonymously, for the sake of simplicity.
SQL (Structured Query Language) is a powerful, high-level declarative language created for working with relational databases. The first commercial implementation of SQL was created by Oracle (then called Relational Software, Inc.) in 1979. The language quickly became popular, and thanks to many implementations, constant development and numerous upgrades, the specification of SQL (standardized by ANSI) became too complex for practical purposes. This is why today, the SQL standard demands that database management system (DBMS) manufacturers declare the level of conformance with the standard. The minimal level of conformance required is called SQL core conformance, but unfortunately, due to various reasons, even the most significant DBMSs do not meet these requirements. Still, standardization did enable a high level of portability between systems with different DBMSs.
Basic SQL Data Types
SQL has the following basic, predefined data types: numeric types, character strings, date/time types, and logical type.
Numeric types can be divided into two groups - exact and approximate. Examples of exact numerical types in SQL are:
- smallint - an integer data type whose domain depends on the implementation. Most commonly the number of bits used for representing this type is 16. SQL only specifies that the precision of smallint is less than the precision of int.
- int - similarly to smallint, this data type is usually represented with 16 bits.
- bigint - similarly to the above, this data type is usually represented with 19 bits.
- numeric(p,s) - a numeric data type for representation of decimal numbers. p signifies the total number of digits, and s signifies the number of digits after the decimal point. For example, if data is specified as numeric(4,2), it can contain the value of 15.99.
Approximate data types are:
- real/double precision
- float(n) - an approximate numeric type with a binary precision of at least n binary digits.
are fixed or variable size data types which contain char arrays. Examples are:
Date/Time data types
- char(n) - specifying a fixed size character array. If n is left out, the default size is 1. If the string contains less than n elements, the remaining places are filled with white spaces. Constant char arrays are specified by single quotes, for example, 'myTestString'.
- varchar(n) - specifying a variable size character array - the length can range from 0 to n characters.
are used to define data related to calendar dates, timestamps or time intervals. Date/time data types are:
- date - contains day, month and year in the following format: DD-MM-YYYY. Constant values can be specified using the date keyword, like date'28-06-2015'.
- time - similarly, time is formatted like HH-MM-SS, and the time keyword is also available.
- timestamp - combines the date and time. Constant calues can be specified using the timestamp keyword like this: timestamp'28-06-2015 13:22:05'.
The logical (boolean) type has the standard values of true and false.
You can see that these types are quite similar to those often found in "regular" programming languages. In the next couple of articles we are going to continue learning about SQL, create domains, relations, restrictions and CRUD operations. After we have done all that, we should be able to write complex SQL triggers and stored procedures, which can be a powerful tool for managing a relational database.