Tags :: Language

Very high level language which allows the user to “say what to do” rather than “how to do it”.

Avoids data manipulation details in other procedural languages.

Database management systems figure out “best” way to execute query, called “query optimization”.

Language overview

Data Definition Language (DDL)

• Define relation schemata
• Create/alter/delete tables and attributes
• Always a temporary output

Data Manipulation Language (DML)

• Insert/delete/modify tuples in tables
• Query one or more tables
• Permanently modifies

Syntax

Language is subdivided into several elements

• Clauses: Constiuent components of statements and queries.
• Expressions: Whcih can produce either scalar values, or tables consisting columns and rows of data.
• Predicates: Specify conditions that can be evaluated to SQL three valued logic and are used to limit the effects of statements and queries, or to change program flow.
• Queries: retrieve the data based on specific criteria. important element of SQL.
• Statements: Have persistent effect on schemata and data, or may control transactions, program flow, connections, etc.
• Insignificant whitespace is generally ignored.

Common statements

Select will return sub set of attributes, while where returns subset of tuples

Select will name retrieve column a1, where will choose either item1.1 or item1.3

Select from where

SELECT desired attributes
FROM one or more tables
WHERE condition about tuples of the tables

Beers example

Using Beers(name, manf) what beers are made by Anhueser-Busch?

SELECT name
FROM Beers
WHERE manf = 'Anheuser-Busch';

Select returns a relation with a single attribute (column) name.

Operations

Operational semantics

• Think of a tuple variable visiting each tuple of the relation mentioned in FROM.
• Check if the current tuple satisfies the WHERE clause.
• If so, compute the attributes or expressions of the SELECT clause using the components.

Where theis is on relation in the FROM clause, * in the select clause stands for all attributes.

Rename attributes in results

SELECT name AS beer, manf
FROM Beers
WHERE manf = 'Anheuser-Busch';

Apply operations on data

SELECT bar, beer,
     price*144 AS priceInYen
from Sells

Constants as expressions

using Likes(drinker, beer)

SELECT drinker,
     'likes Bud' AS whoLikesBud
FROM Likes
WHERE beer = 'Bud';

Pattern matching

A condition can compare a string to a pattern by

<Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern>

Pattern is a qouted string with %“any string;_=“any character”=

Create Table

CREATE TABLE <RelationName> (
  attribute_name data_type,
    ...
    CONSTRAINT name_of_constraint Type_of_Key
    (attribute_name)
);

Examples

• Person table

  CREATE TABLE person (
    person_id SMALLINT UNSIGNED,
    * ... ,
    gender CHAR(1) CHECK(gender IN ('M', 'F', 'T', 'N')),
    postal_code VARCHAR(20),
    CONSTRAINT pk_person PRIMARY_KEY (person_id)
  );
  

• Adding favorite foods

  • Every person has some favorite food
  • Where (how) do you want to store it
    • Attribute in Person relation
      • Disadvantages: ::Would not allow multiple favorite foods::
    • As a seperate relationship
      • Advantages: ::Allows as many favorite foods as you want::

  CREATE TABLE favorite_food (
      person_id SMALLINT UNSIGNED,
      food VARCHAR(20),
      CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
      CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
          REFERENCES person (person_id)
  )
  

Modify Table

A Modification command does not return a result, but changes the database in some way.

There are three types: insert, delete, update.

In general you are not required to provide data for every attribute (column), unless constraint is specified.

E.g. to update person_id to automatically increment its primary key we can do

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

References