Simple Queries in SQL

Simple Queries in SQL

  • Introduction to the basic SELECT-FROM-WHERE query structure, which is analogous to a selection in relational algebra.
  • Performing projection by listing specific attributes in the SELECT clause instead of using *.
  • Renaming columns in the query result using the AS keyword, also known as aliasing.
  • Using expressions, calculations, and constants in the SELECT clause, similar to an extended projection.
  • Constructing selection conditions in the WHERE clause using comparison operators (=, <>, <, >, <=, >=) and logical operators (AND, OR, NOT).
  • Understanding the relationship between simple SQL queries and the relational algebra expression \(\pi_L(\sigma_C(R))\)
  • Comparing strings based on lexicographical (dictionary) order.
  • Matching strings to patterns using the LIKE operator with wildcard characters % (any sequence of zero or more characters) and _ (any single character).
  • Using the ESCAPE clause to search for the literal % and _ characters within a LIKE pattern.
  • Representing and comparing date and time values using the DATE, TIME, and TIMESTAMP data types and their standard formats.
  • Understanding the concept and interpretations of NULL values (e.g., value unknown, inapplicable, or withheld).
  • Rules for handling NULL in expressions: arithmetic operations with NULL result in NULL, and comparisons with NULL result in the truth-value UNKNOWN.
  • Checking for nullity using the IS NULL and IS NOT NULL predicates.
  • Introduction to three-valued logic (TRUE, FALSE, UNKNOWN) and its application to logical operators AND, OR, and NOT.
  • The WHERE clause filtering mechanism, which only includes tuples for which the condition evaluates to TRUE, discarding those that are FALSE or UNKNOWN.
  • Sorting query results using the ORDER BY clause, with options for ascending (ASC) or descending (DESC) order on one or more attributes.
  • Using expressions in the ORDER BY clause to sort based on calculated values.
#1

read: Section 6.1: Simple Queries in SQL (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Introduction to the basic `SELECT-FROM-WHERE` query structure, which is analogous to a selection in relational algebra.
- Performing projection by listing specific attributes in the `SELECT` clause instead of using `*`.
- Renaming columns in the query result using the `AS` keyword, also known as aliasing.
- Using expressions, calculations, and constants in the `SELECT` clause, similar to an extended projection.
- Constructing selection conditions in the `WHERE` clause using comparison operators (`=`, `<>`, `<`, `>`, `<=`, `>=`) and logical operators (`AND`, `OR`, `NOT`).
- Understanding the relationship between simple SQL queries and the relational algebra expression $\pi_L(\sigma_C(R))$
- Comparing strings based on lexicographical (dictionary) order.
- Matching strings to patterns using the `LIKE` operator with wildcard characters `%` (any sequence of zero or more characters) and `_` (any single character).
- Using the `ESCAPE` clause to search for the literal `%` and `_` characters within a `LIKE` pattern.
- Representing and comparing date and time values using the `DATE`, `TIME`, and `TIMESTAMP` data types and their standard formats.
- Understanding the concept and interpretations of `NULL` values (e.g., value unknown, inapplicable, or withheld).
- Rules for handling `NULL` in expressions: arithmetic operations with `NULL` result in `NULL`, and comparisons with `NULL` result in the truth-value `UNKNOWN`.
- Checking for nullity using the `IS NULL` and `IS NOT NULL` predicates.
- Introduction to three-valued logic (TRUE, FALSE, UNKNOWN) and its application to logical operators `AND`, `OR`, and `NOT`.
- The `WHERE` clause filtering mechanism, which only includes tuples for which the condition evaluates to `TRUE`, discarding those that are `FALSE` or `UNKNOWN`.
- Sorting query results using the `ORDER BY` clause, with options for ascending (`ASC`) or descending (`DESC`) order on one or more attributes.
- Using expressions in the `ORDER BY` clause to sort based on calculated values.

6.1 Simple Queries in SQL

  • The SELECT-FROM-WHERE Statement

    The most basic form of a query in SQL consists of three clauses: SELECT, FROM, and WHERE. This structure is analogous to the selection operation of relational algebra.

    • SELECT lists the attributes to be displayed in the result.
    • FROM specifies the relation from which to retrieve tuples.
    • WHERE provides a condition that tuples must satisfy to be included in the result.
Definition: Simple SQL Query and Relational Algebra

A simple SQL query of the form:

SELECT L
FROM R
WHERE C;

where \(L\) is a list of expressions, \(R\) is a relation, and \(C\) is a condition, is equivalent to the relational-algebra expression:

\[ \pi_L(\sigma_C(R)) \]

The query first applies the selection condition \(C\) to the relation \(R\) and then projects the result onto the attributes and expressions specified in list \(L\).

  • Projection in SQL

    To project a relation onto a subset of its attributes, list the desired attributes in the SELECT clause. The wildcard * is used to select all attributes.

    -- Selects only the title and length from the Movies relation
SELECT title, length
FROM Movies
WHERE studioName = 'Disney' AND year = 1990;

  • Renaming Attributes and Expressions

    The AS keyword allows for the renaming of an attribute or an expression in the output relation. The keyword AS is optional.

    SELECT title AS name, length AS duration
FROM Movies;

  • Expressions in the SELECT Clause

    The SELECT clause can include expressions, which are evaluated for each selected tuple. This functionality is analogous to extended projection in relational algebra. Constants may also be included as expressions.

    -- Calculates length in hours and includes a constant 'hrs.' column
SELECT title, length/60.0 AS lengthInHours, 'hrs.' AS units
FROM Movies;

  • Conditions in the WHERE Clause

    The WHERE clause filters tuples using a condition.

    • Comparison Operators: =, <> (not equal), <, >, <=, >=.
    • Logical Operators: AND, OR, NOT. AND has higher precedence than OR. Parentheses may be used to clarify the order of evaluation.
    SELECT title
FROM Movies
WHERE (year > 1970 OR length < 90) AND studioName = 'MGM';

  • String Operations

    • Comparison: Strings are compared lexicographically (i.e., dictionary order). 'fodder' is less than 'foo'. A string is less than another if it is a proper prefix, e.g., 'bar' is less than 'bargain'.
    • Pattern Matching: The LIKE operator is used for matching strings to patterns.
      • % matches any sequence of zero or more characters.
      • _ matches any single character.
    -- Finds movies with a title containing 's
WHERE title LIKE '%''s%';
    • Escape Characters: The ESCAPE clause allows for searching for literal % or _ characters.
    -- Matches a string that starts and ends with a literal '%'
WHERE name LIKE 'x%%x%' ESCAPE 'x';

  • Date and Time Types

    SQL provides special data types for dates and times, which can be compared.

    • DATE: DATE 'YYYY-MM-DD'
    • TIME: TIME 'HH:MI:SS'
    • TIMESTAMP: TIMESTAMP 'YYYY-MM-DD HH:MI:SS'

  • Null Values

    SQL uses the special value NULL to represent missing information. NULL is not a value in the conventional sense. Its interpretation can be:

    • Value Unknown: A value exists but is not known.
    • Value Inapplicable: No meaningful value exists for the attribute.
    • Value Withheld: The value is not available to the user.

  • Expressions and Comparisons with NULL

    Special rules govern operations involving NULL.

    1. An arithmetic expression involving NULL evaluates to NULL. For example, if x is NULL, then x + 5 is NULL.
    2. A comparison involving NULL evaluates to the special truth-value UNKNOWN. For example, if x is NULL, then x = 5 is UNKNOWN.
    3. To test if a value is NULL, use the IS NULL or IS NOT NULL predicate. The expression x = NULL is not valid for this purpose.

  • Three-Valued Logic

    The presence of UNKNOWN requires a three-valued logic system. The logical operators AND, OR, and NOT are extended as follows, where TRUE=1, UNKNOWN=1/2, and FALSE=0.

    • x AND y = min(x, y)
    • x OR y = max(x, y)
    • NOT x = 1 - x

    A WHERE clause only accepts tuples for which the condition evaluates to TRUE. Tuples for which the condition is FALSE or UNKNOWN are rejected.

  • Ordering the Output

    The ORDER BY clause sorts the tuples of the result.

    • Sorting can be on one or more attributes. Subsequent attributes break ties from previous ones.
    • ASC specifies ascending order (the default).
    • DESC specifies descending order.
    • The ORDER BY clause can also contain expressions.
    SELECT *
FROM Movies
WHERE studioName = 'Disney' AND year = 1990
ORDER BY length DESC, title;
  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 12:50:27
  • Duration: 30.130687952041626
  • Cummulative cost: $4.12e-02