Attribute Data Types (SQL)

SQL has a number of data types that may be used for attributes:

• Integers: typically we use INT / INTEGER which are implicitly signed, we may used UNSIGNED INTEGER for the unsigned variant. INT(n) specifies the number of digits used.

• Approximate real numbers: use FLOAT / REAL / DOUBLE, can specify digit precision as FLOAT(n) where n is the number of bits used to store the mantissa of the floating point number.

• Exact real numbers: use DECIMAL(i,j) to specify a fixed-point decimal number with i being the precision (total number of digits to store the number), and j denoting the position of the point / the scale.

• Strings: We can use a number of types here:

  • CHAR(n) / CHARACTER(n): fixed length, right padded with spaces
  • VARCHAR(n) / CHAR VARYING(n): varying length
  • CLOB / TEXT: character large object

• Binary data: Again we can use similar notation:

  • BIT(n): fixed length
  • BIT VARYING(n): varying length
  • BLOB: binary large object

  title: Best practice: hash binary data and store that in the database with the metadata.

• Boolean: use BOOLEAN or BIT(1), choice may vary from implementation to implementation, and in some scenarios it may be `NULL`

• Date and Time: We have multiple different types to specify time:

  • DATE: made up of year-month-day (“yyyy-mm-dd”)
  • TIME: made up of hour:minute:second (“hh:mm:ss”)
  • TIME(i): TIME plus i additional digits for fractions of a second (“hh:mm:ss:ii…i”)
  • DATETIME / TIMESTAMP: both DATE and TIME components

• Interval: we can use INTERVAL to specify relative time value as opposed to absolute, can be day/time intervals or year/month intervals. Can be positive or negative when added to or subtracted from an absolute value, the result is an absolute value.

• There are additional domain specific / complex types which aren’t relevant for the course:

  • Special Types: CURRENCY / MONEY
  • Spatial Types (GIS): GEOMETRY type
  • Enumerated Types: ENUM("One", "Two", "Three")
  • Collection Types: SET / VALUE_MAP