Lecture 04 Intro to SQL

Domain Types in SQL

char(n). Fixed length character string, with user-specified length n.

varchar(n). Variable length character strings, with user-specified

maximum length n.
int. Integer (a finite subset of the integers that is machine-dependent).

smallint. Small integer (a machine-dependent subset of the integer domain type).

numeric(p,d). Fixed point number, with user-specified precision of pdigits, with d digits to the right of decimal point. (ex., numeric(3,1), allows 44.5 to be stores exactly, but not 444.5 or 0.32)

real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision.

float(n). Floating point number, with user-specified precision of at least n digits.

 

Create Table Construct

create table r (A1 D1, A2 D2, ..., An Dn, (integrity-constraint1),

..., (integrity-constraintk))

r is the name of the relation
each Ai is an attribute name in the schema of relation r

Di is the data type of values in the domain of attribute Ai 

Example:

create table instructor (

　　ID char(5),

　　name varchar(20), 

　　dept_name varchar(20), 

　　salary numeric(8,2))

 

Integrity Constraints in Create Table

not null
primary key (A1, ..., An )

foreign key (Am, ..., An ) references r

Example:

create table student (
ID varchar(5),
name varchar(20) not null, 

dept_name varchar(20), 

tot_cred numeric(3,0), 

primary key (ID),

foreign key (dept_name) references department);

 

Updates to tables

Insert

　　insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000); 

Delete

Remove all tuples from the student relation 

　　delete from student

Drop Table

　　drop table r 

Alter

　　alter table r drop A D

 where A is the name of an attribute of relation r and D is the domain of A.

    All exiting tuples in the relation are assigned null as the value for the new attribute.

　　alter table r add A D

　　where A is the name of the attribute to be added to relation

     Dropping of attributes not supported by many databases.

 

Basic Query Structure

A typical SQL query has the form:

　　　　select A1, A2, ..., An 

　　　　from r1, r2, ..., rm 

　　　　where P

Ai represents an attribute

Ri represents a relation

P is a predicate.

The result of an SQL query is a relation.

corresponds to the projection operation of the relational algebra

 

NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.)

 

SQL allows duplicates in relations as well as in query results.

To force the elimination of duplicates, insert the keyword distinct

after select.

　　select distinct dept_name from instructor

The keyword all specifies that duplicates should not be removed. 

　　select all dept_name from instructor

 

An asterisk in the select clause denotes “all attributes” 

　　select * 

　　from instructor

An attribute can be a literal with no from clause

　　select ‘437’
Results is a table with one column and a single row with value “437” Can give the column a name using:

　　select ‘437’ as FOO

An attribute can be a literal with from clause:

　　select ‘A’ 

　　from instructor

Result is a table with one column and N rows (number of tuples in theinstructors table), each row with value “A”

 

The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples.

The query:

　　select ID, name, salary/12 

　　from instructor

would return a relation that is the same as the instructor relation, except that the value of the attribute salary is divided by 12.

Can rename “salary/12” using the as clause: 

　　select ID, name, salary/12 as monthly_salary

 

The where Clause:

The where clause specifies conditions that the result must satisfy

Corresponds to the selection predicate of the relational algebra.

　　select name
　　from instructor
　　where dept_name = ‘Comp. Sci.'

Comparison results can be combined using the logical connectives and, or, and not

 

The from Clause:

The from clause lists the relations involved in the query

Find the Cartesian product instructor X teaches 

　　select *  

　　from instructor, teaches

 

The Rename Operation:

The SQL allows renaming relations and attributes using the as clause:old-name as new-name

　　select distinct T.name
　　from instructor as T, instructor as S
　　where T.salary > S.salary and S.dept_name = ‘Comp. Sci.’

 

String Operations：

SQL includes a string-matching operator for comparisons on character strings.

The operator like uses patterns that are described using two special characters:

percent ( % ). The % character matches any substring.

underscore ( _ ). The _ character matches any character.

Find the names of all instructors whose name includes the substring “dar”.

　　select name
　　from instructor
　　where name like '%dar%'

Match the string “100%”
　　like ‘100 \%' escape '\'

in that above we use backslash (\) as the escape character.

 

Ordering the Display of Tuples:

　　select distinct name 

　　from instructor 

　　order by name

We may specify desc for descending order or asc for ascending

order, for each attribute; ascending order is the default.

order by name desc

If order by is followed by serveral attributes, it means that we have a comparing sequence.　　　　

It can also be followed by the arithmetic expressions of attributes. 

 

Where Clause Predicates:

 

SQL includes a between comparison operator

Example: Find the names of all instructors with salary between $90,000

and $100,000 (that is, >=$90,000 and <=$100,000)

　　select name
　　from instructor
　　where salary between 90000 and 100000

Tuple comparison

　　select name, course_id

　　from instructor, teaches

　　where (instructor.ID, dept_name) = (teaches.ID, ’Biology’);

 

Set Operations：

Set operations union, intersect, and except

　　·Each of the above operations automatically eliminates duplicates

To retain all duplicates use the corresponding multiset versions union all, intersect all and except all.

Suppose a tuple occurs m times in r and n times in s, then, it occurs:

m +n times in r union all s 

min(m,n) times in r intersect all s

max(0, m – n) times in r except all s

 

 

 

