SQL

Overview

The Structured Query Language (SQL) is the most commonly used language for interacting with relational data models. Some standard syntactic features include:

• Semicolons (;) are used to delimit statements.
• Single-line comment are denoted with -- whereas multiline comments are denoted between /* and */.

Basic Types

The SQL standard supports a variety of built-in types including:

• CHARACTER(N) or CHAR(N)
  • A fixed-length character string with fixed-length N. Strings of length <= N will be space-padded. Strings of length > N are truncated to fit.
• CHARACTER VARYING(N) or VARCHAR(N)
  • A variable-length character string with maximum length N.
• INTEGER or INT
  • A machine-dependent finite subset of the integers. Usually 4 bytes.
• SMALLINT
  • A machine-dependent finite subset of the integers. Usually 2 bytes.
• NUMERIC(P, S)
  • A fixed-point number consisting of P digits (plus a sign) of which S are to the right of the decimal point.
• REAL
  • A floating-point number with machine-dependent precision. Usually 4 bytes.
• DOUBLE PRECISION
  • A floating-point number with machine-dependent precision. Usually 8 bytes.
• FLOAT(N)
  • A floating-point number with precision N.

Common extensions include:

• BIGINT
  • A machine-dependent finite subset of the integers. Usually 8 bytes.

Strings

String literals are declared between two apostrophes (i.e. '). An apostrophe within a string can be escaped using two consecutive apostrophes (i.e. '').

A number of functions and operators are defined by the SQL standard to manipulate strings. These include:

• Concatenation of two strings is performed with the || operator.
• Pattern matching two strings with the LIKE operator.
  • The % character matches any substring whereas the _ character matches any character.
  • The ESCAPE keyword allows specifying a character to use for escaping other characters.

' ESCAPE ''`
Reference: silberschatz

ENDANKI
Basic
What is the result of the following query?

SELECT 1 + NULL;

Back: NULL
Reference: silberschatz

ENDANKI
Basic
What is the result of the following query?

SELECT 1 < NULL;

Back: UNKNOWN
Reference: silberschatz

ENDANKI
Basic
What truth values are supported in SQL?
Back: TRUE, FALSE, and UNKNOWN.
Reference: silberschatz

ENDANKI
Basic
Why does SQL support the UNKNOWN truth value?
Back: NULL is used to represent uknown values.
Reference: silberschatz

ENDANKI
Basic
How do NULL values compare in a WHERE clause?
Back: As distinct.
Reference: silberschatz

ENDANKI
Basic
How do NULL values compare in a SELECT DISTINCT clause?
Back: As equal.
Reference: silberschatz

ENDANKI
Basic
How do NULL values compare in a UNION?
Back: As equal.
Reference: silberschatz

ENDANKI
Basic
What name is given to the SQL construct $(v_1,\dots ,v_n)$?
Back: A row constructor.
Reference: silberschatz

ENDANKI
Basic
SQL row constructors are used to create what kind of mathematical objects?
Back: $n$-tuples.
Reference: silberschatz

ENDANKI
Basic
How is the following SQL fragment rewritten using row constructors?

WHERE X1 = Y1 AND X2 = Y2

Back:

WHERE (X1, X2) = (Y1, Y2)

Reference: silberschatz

ENDANKI
Basic
How is the following SQL fragment rewritten using row constructors?

WHERE X1 = Y1 OR X2 = Y2

Back: N/A. The OR construct is not translatable.
Reference: silberschatz

ENDANKI
Basic
How is the following SQL fragment rewritten without row constructors?

WHERE (X1, X2) = (Y1, Y2)

Back:

WHERE X1 = Y1 AND X2 = Y2

Reference: silberschatz

ENDANKI
Basic
How is the following SQL fragment rewritten without row constructors?

WHERE (X1, X2) < (Y1, Y2)

Back:

WHERE X1 < Y1 AND X2 < Y2

Reference: silberschatz

END%%