BasicSQL.pdf

Basic Standard SQL

(Structured Query Language)

1. Schema Creation/Modification: DDL (Data Definition

Language) Features of SQL

Built-in Domain types

char(n) : fixed length

varchar(n) : variable length string upto n characters

int (or integer ): machine dependent,

smallint : machine dependent,

numeric(p, d) : fixed-point number (d of p to the right side of the decimal

point)

real and double precision : floating-point number and double-precision

floating-point number (the precision is machine dependent)

float(n) : floating-point number (user-defined precision: at least n digits)

date : 4 year digits+2 month digits + 2 day digits

time : 2 hour digits + 2 minute digits + 2 second digits

interval: date1-date2 or time1-time2 Note, date1 (or time1) >= date2 (or

time2). interval + (or -) time (or date) is time (or date)

Dr. Byunggu Yu

Creating a domain type (rename a domain type)

e.g., create domain fullname char(30)

Create Relation Schema

create table relation-name (

A1 D1 < not-null > ,

A2, D2 < not-null > , ... ,

An Dn < not-null > ,

<constraint1> ,

<constraint2> , ... ,

)

A1, A2, ..., An are attribute names and D1, D2, ..., Dn are domain types.

Note, the parameters in angle brackets are optional!

Note, there are three types of constraints: primary key ( Ai, Aj, ..., Al ) ,

foreign key ( Ai, Aj, ..., Al ) references relation-name < on delete cascade >

< on update cascade >, and check ( predicate )

Note, if an insertion, deletion, or update tuple incurs a failure of any

constraint or not-null on the tuple --> DBMS reject the operation and an

error is flagged.

Dr. Byunggu Yu

e.g. create table employee (

e-num integer not-null,

e-name fullname,

e-age integer,

dept-num integer,

primary key ( e-num ),

foreign key ( dept-num ) references department

on update cascade

on delete cascade,

check ( e-age <= 100 )

)

Note, the available predicates in "check" is discussed in Section 3 (the

"where" clause of SQL DML).

Modify Relation Schema (Add or Delete an attribute)

alter table relation-name add A D

alter table relation-name drop A

Note, "A" is an attribute and "D" is a domain type.

Note, when an attribute is added to a relation, all tuples in the relation are

assigned "null" as the value for the new attribute.

We cannot give any constraint (or not-null) on the new attribute.

e.g. alter table employee add e-office char(30)

alter table employee drop e-office

Drop (Delete) Relation Schema

drop table relation-name

Delete the relation schema and instance!

e.g. drop table employee

Create (Define)/Drop a View on one or more Relations

create view view-name < ( attribute names ) > as query-expression

Dr. Byunggu Yu

The query expression will be discussed in Section 3 (select ~ from ~ where

~). Note, ( attribute names ) is optional.

drop view view-name

e.g. create view dept-emp (d-num, d-name, emp-num, emp-name) as

select department.dept-num, dept-name, e-num, e-name

from department, employee

where department.dept-num = employee.dept-num

drop view dept-emp

Again, the query-expression will be discussed in Section 3.

Create (Build)/Drop an Index on a Relation (Not in standard SQL-92)

create < unique > index index-name on relation-name < ( attribute names ) >

drop index index-name

Note, the option " unique " is used only when the given attribute-list is a

superkey (i.e., each tuple has unique values on the attribute-list). Therefore,

when create an index or insert/update a tuple, if this condition fails, an error

will be flagged.

e.g. create unique index b-tree1 on employee (e-num)

create index b-tree2 on employee (e-name)

drop index b-tree2

Note, "create index" is not in SQL-92 standard. However, many commercial

DBMS support this.

Dr. Byunggu Yu

2. Instance Modification: DML (Data Manipulation

Language) Features of SQL (1 of 2)

Inserting Tuples into a Relation

Type 1: insert into relation-name < ( attribute names ) > values ( tuple value )

Type 2: insert into relation-name < ( attribute names ) > query-expression

Note, " ( attribute names ) " is optional. This option enables us to change the

order of domains.

Type1 e.g. insert into employee

values ( 001, "Sam Murlas", 35, 1101 )

e.g. insert into employee ( e-name, e-num, dept-num, e-age )

values ( "Sam Murlas", 001, 1101, 35 )

Type 2 uses SQL query-expression. SQL query-expression (i.e., select ~

from ~ where ~ ) is discussed in Section 3 in detail. So, this section gives

only one simple example.

Type2 e.g. insert into account ( branch-name, account-number, balance )

select branch-name, loan-number, 200

from loan

where branch-name = "Laramie"

Note, the result schema of the subquery must be the same as (attribute

names) in " into " clause.

Dr. Byunggu Yu

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: