SQL Interactive DML and Database Management at Georgia Tech - Prof. Leo Mark, Study notes of Deductive Database Systems

Download SQL Interactive DML and Database Management at Georgia Tech - Prof. Leo Mark and more Study notes Deductive Database Systems in PDF only on Docsity!

Database Group, Georgia Tech

SQL

- intergalactic dataspeak

[Stonebraker]

Database Group, Georgia Tech

History of SQL

• SQL: Structured Query Language

• SQL is based on the relational tuple

calculus

• SEQUEL: Structured English QUEry

Language; part of SYSTEM R, 1974

• SQL/86: ANSI & ISO standard

• SQL/89: ANSI & ISO standard

• SQL/92 or SQL2: ANSI & ISO standard

• SQL3: in the works...

• SQL2 supported by ORACLE,

SYBASE, INFORMIX, IBM DB2, SQL

SERVER, OPENINGRES,...

Database Group, Georgia Tech

FLT-SCHEDULE

FLT-INSTANCE

FLT-WEEKDAY

AIRPLANE

CUSTOMER

flt# date plane#

RESERVATION

flt# airline dtime from-airportcode atime to-airportcode miles price

flt# weekday

plane# plane-type total-#seats

cust# first middle last phone# street city state zip

flt# date cust# seat# check-in-status ticket#

AIRPORT airportcode name city state

#avail-seats

Database Group, Georgia Tech

DDL - Overview

• primitive types

• domains

• schema

• tables

Database Group, Georgia Tech

DDL - Primitive Types (cont.)

• character-string

• CHAR(N) (or CHARACTER(N)) is a fixed-

length character string

• VARCHAR(N) (or CHAR VARYING(N), or

CHARACTER VARYING(N)) is a variable-

length character string with at most N

characters

• bit-strings

• BIT(N) is a fixed-length bit string
• VARBIT(N) (or BIT VARYING(N )) is a bit

string with at most N bits

Database Group, Georgia Tech

• time^ Y10K databases are forever

• DATE is a date: YYYY-MM-DD

– TIME, a time of day: HH-MM-SS

– TIME(I), a time of day with I decimal

fractions of a second: HH-MM-SS-F....F

– TIME WITH TIME ZONE, a time with a

time zone added: HH-MM-SS-HH-MM

– TIME-STAMP, date, time, fractions of a

second and an optional WITH TIME

ZONE qualifier:

YYYY-MM-DD-HH-MM-SS-F...F{-HH-MM}

– INTERVAL, relative value used to

increment or decrement DATE, TIME, or

TIMESTAMP: YEAR/MONTH or DAY/TIME

DDL - Primitive Types (cont.)

Database Group, Georgia Tech

DDL - Domains (cont.)

• all domains contain the value, NULL.

• to define a different default value:

CREATE DOMAIN AIRPORT-CODE CHAR(3)

DEFAULT ‘’; CREATE DOMAIN AIRPORT-CODE CHAR(3) DEFAULT ‘niladic function’;

• literal, such as ‘???’, ‘NO-VALUE’,...

• niladic function, such as USER,

CURRENT-USER, SESSION-USER, SYSTEM-

USER, CURRENT-DATE, CURRENT-TIME,

CURRENT-TIMESTAMP

• defaults defined in a column takes

precedence over the above

Database Group, Georgia Tech

DDL - Domains (cont.)

• a domain is dropped as follows:

DROP DOMAIN AIRPORT-CODE RESTRICT;

DROP DOMAIN AIRPORT-CODE CASCADE;

• restrict: drop operation fails if the

domain is used in column definitions

• cascade: drop operation causes

columns to be defined directly on

the underlying data type

Database Group, Georgia Tech

DDL - Tables

• to create a table in the AIRLINE schema:

CREATE TABLE AIRLINE.FLT-SCHEDULE

(FLT# FLIGHTNUMBER NOT NULL ,

AIRLINE VARCHAR(25),

FROM-AIRPORTCODE AIRPORT-CODE,

DTIME TIME,

TO-AIRPORTCODE AIRPORT-CODE,

ATIME TIME,

PRIMARY KEY (FLT#),

FOREIGN KEY (FROM-AIRPORTCODE)

REFERENCES AIRPORT(AIRPORTCODE),

FOREIGN KEY (TO-AIRPORTCODE)

REFERENCES AIRPORT(AIRPORTCODE));

Database Group, Georgia Tech

DDL - Tables (cont.)

CREATE TABLE AIRLINE.FLT-WEEKDAY

(FLT# FLIGHTNUMBER NOT NULL ,

WEEKDAY CHAR(2),

UNIQUE (FLT#, WEEKDAY),

FOREIGN KEY (FLT#)

REFERENCES FLTT-SCHEDULE(FLT#));

CREATE TABLE AIRLINE.FLT-INSTANCE

(FLT# FLIGHTNUMBER NOT NULL ,

DATE DATE NOT NULL ,

#AVAIL-SEATS SMALLINT,

PRIMARY KEY (FLT#, DATE),

FOREIGN KEY FLT#

REFERENCES FLT-SCHEDULE(FLT#));

Database Group, Georgia Tech

DDL - Tables (cont.)

• to drop a table:

DROP TABLE RESERVATION RESTRICT;

DROP TABLE RESERVATION CASCADE;

• restrict: drop operation fails if the table

is referenced by view/constraint

definitions

• cascade: drop operation removes

referencing view/constraint definitions

Database Group, Georgia Tech

DDL - Tables (cont.)

• to add a column to a table:

ALTER TABLE AIRLINE.FLT-SCHEDULE

ADD PRICE DECIMAL(7,2);

• if no DEFAULT is specified, the new

column will have NULL values for all

tuples already in the database

• to drop a column from a table

ALTER TABLE AIRLINE.FLT-SCHEDULE

DROP PRICE RESTRICT ( or CASCADE) ;

• restrict: drop operation fails if the

column is referenced

• cascade: drop operation removes

referencing view/constraint definitions

Database Group, Georgia Tech

Interactive DML

- select-from-where

SELECT A 1 , A 2 , ... A n

FROM R 1 , R 2 , ... R m

WHERE P

• the SELECT clause specifies the

columns of the result

• the FROM clause specifies the tables

to be scanned in the query

• the WHERE clause specifies the

condition on the columns of the

tables in the FROM clause

• equivalent algebra statement:

πA 1 , A 2 , ... A n(σP(R 1 xR 2 x ... R m))

Database Group, Georgia Tech

Interactive DML

- select clause

• “Find the airlines in FLT-SCHEDULE”

SELECT AIRLINE

FROM FLT-SCHEDULE;

SELECT ALL AIRLINE

FROM FLT-SCHEDULE;

• “Find the airlines in FLT-SCHEDULE

with duplicates removed”

SELECT DISTINCT AIRLINE

FROM FLT-SCHEDULE;

• “Find all columns in FLT-SCHEDULE”

SELECT *

FROM FLT-SCHEDULE;

• “Find FLT# and price raised by 10%”

SELECT FLT#, PRICE*1.

FROM FLT-SCHEDULE;