SQL &
Physical Layout

SQL

• Developed by IBM (for System R) in the 1970s.
• Standard used by many vendors.
  • SQL-86 (original standard)
  • SQL-89 (minor revisions; integrity constraints)
  • SQL-92 (major revision; basis for modern SQL)
  • SQL-99 (XML, window queries, generated default values)
  • SQL 2003 (major revisions to XML support)
  • SQL 2008 (minor extensions)
  • SQL 2011 (minor extensions; temporal databases)

A Basic SQL Query

            SELECT  [DISTINCT] targetlist
            FROM    relationlist
            WHERE   condition
          

1. Compute the $2^n$ combinations of tuples in all relations appearing in relationlist
2. Discard tuples that fail the condition
3. Delete attributes not in targetlist
4. If DISTINCT is specified, eliminate duplicate rows

This is the least efficient strategy to compute a query! A good optimizer will find more efficient strategies to compute the same answer.

Example Data

SELECT * FROM Trees;

Wildcards (*, tablename.*) are special targets that select all attributes.

            SELECT tree_id, spc_common, boroname
            FROM Trees
            WHERE boroname = 'Brooklyn'
          

In English, what does this query compute?

What is the ID, Commmon Name and Borough of Trees in Brooklyn?

      SELECT latitude, longitude 
      FROM Trees, SpeciesInfo
      WHERE Trees.spc_common = SpeciesInfo.name
        AND SpeciesInfo.has_unpleasant_smell = 'Yes';
          

In English, what does this query compute?

What are the coordinates of Trees with bad smells?

      SELECT Trees.latitude, Trees.longitude 
      FROM Trees, SpeciesInfo
      WHERE Trees.spc_common = SpeciesInfo.name
        AND SpeciesInfo.has_unpleasant_smell = 'Yes';
          

... is the same as ...

      SELECT T.latitude, T.longitude 
      FROM Trees T, SpeciesInfo S
      WHERE T.spc_common = S.name
        AND S.has_unpleasant_smell = 'Yes';
          

... is (usually) the same as ...

      SELECT latitude, longitude 
      FROM Trees, SpeciesInfo
      WHERE spc_common = name
        AND has_unpleasant_smell = 'Yes';
          

Expressions

            SELECT tree_id, 
                   stump_diam / 2 AS stump_radius,
                   stump_area = 3.14 * stump_diam * stump_diam / 4
            FROM Trees;
          

Arithmetic expressions can appear in targets or conditions. Use ‘=’ or ‘AS’ to assign names to these attributes. (The behavior of unnamed attributes is unspecified)

Expressions

  SELECT tree_id, spc_common FROM Trees WHERE spc_common LIKE '%maple'
          

SQL uses single quotes for ‘string literals’

LIKE is used for String Matches

‘%’ matches 0 or more characters

Union

    SELECT tree_id FROM Trees WHERE spc_common = 'red maple'
    UNION [ALL]
    SELECT tree_id FROM Trees WHERE spc_common = 'sycamore maple'
          

Computes the set-union of any two union-compatible sets of tuples

Adding ALL preserves duplicates across the inputs (bag-union).

Aggregate Queries

    SELECT [DISTINCT] targetlist
    FROM relationlist
    WHERE condition
    GROUP BY groupinglist
    HAVING groupcondition
          

Aggregate Queries

    SELECT spc_common, count(*) FROM Trees GROUP BY spc_common
          

Physical Layout

  from re import split;

  with open('Trees.csv', 'r') as f:
    for line in f:
      fields = split(",", line);
      if(fields[30] == 'Brooklyn'):
        print(fields[0]);
          

Record Layouts

Record Layout 1: Fixed

Record Layout 2: Delimiters

Record Layout 2: Headers

Record Formats

Fixed

Constant-size fields. Field $i$ at byte $\sum_{j < i} |Field_j|$

Delimited

Special character or string (e.g., ,) between fields

Header

Fixed-size header points to start of each field

 

 

File Formats

Fixed

Constant-size records. Record $i$ at byte $|Record| \times i$

Delimited

Special character or string (e.g., \r\n) at record end

Header

Index in file points to start of each record

Paged

Align records to paging boundaries

File

A collection of pages (or records)

Page

A fixed-size collection of records

Page size is usually dictated by hardware.
Mem Page $\approx$ 4KB   Cache Line $\approx$ 64B

Record

One or more fields (for now)

Field

A primitive value (for now)

  with db_open('Trees') as data:
    for record in data:
      if(record['BORONAME'] == 'Brooklyn'):
        print(record['TREE_ID']);