SQL Row Values and Literal Values

The values held in the cells that form the intersections of the SQL database table’s rows and columns are the raw materials from which you can derive meaningful relationships and trends.

Row values

The most visible values in a database are table row values. These are the values that each row of a database table contains. A row value is typically made up of multiple components because each column in a row contains a value.

A field is the intersection of a single column with a single row. A field contains a scalar, or atomic, value. A value that’s scalar or atomic has only a single component.

Literal values

In SQL, either a variable or a constant may represent a value. Logically enough, the value of a variable may change from time to time, but the value of a constant never changes. An important kind of constant is the literal value. The representation is itself the value.

Just as SQL has many data types, it also has many types of literals. Table 8-1 shows some examples of literals of the various data types.

Notice that single quotes enclose the literals of the non-numeric types. These marks help to prevent confusion; they can, however, also cause problems.

Data Type Example Literal
BIGINT 8589934592
INTEGER 186282
SMALLINT 186
NUMERIC 186282.42
DECIMAL 186282.42
REAL 6.02257E23
DOUBLE PRECISION 3.1415926535897E00
FLOAT 6.02257E23
CHARACTER(15) ‘GREECE
Note: Fifteen total characters and spaces are between the quote marks above.
VARCHAR (CHARACTER VARYING) ‘lepton’
Note: Fifteen total characters and spaces are between the quote marks above.
CHARACTER LARGE OBJECT(512) (CLOB(512)) (A really long character string)
BINARY(4) ‘01001100011100001111000111001010’
VARBINARY(4) (BINARY VARYING(4)) ‘0100110001110000’
BINARY LARGE OBJECT(512) (BLOB(512)) (A really long string of ones and zeros)
DATE DATE ‘1969-07-20’
TIME(2) TIME ‘13.41.32.50’
TIMESTAMP(0) TIMESTAMP ‘2013-02-25-13.03.16.000000’
TIME WITH TIMEZONE(4) TIME ‘13.41.32.5000-08.00’
TIMESTAMP WITH TIMEZONE(0) TIMESTAMP ‘2013-02-25-13.03.16.0000+02.00’
INTERVAL DAY INTERVAL ‘7’ DAY

What if a literal is a character string that itself contains a phrase in single quotes? In that case, you must type two single quotes to show that one of the quote marks that you’re typing is a part of the character string and not an indicator of the end of the string. You’d type ‘Earth’’s atmosphere’, for example, to represent the character literal ‘Earth’s atmosphere’.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.