SQL.pdf

1 SQL – Structured Query Language

1.1 Tables

In relational database systems (DBS) data are represented using tables ( relations ). A query

issued against the DBS also results in a table. A table has the following structure:

Column 1 Column 2 ...

Column n

←− Tuple (or Record)

...

A table is uniquely identiﬁed by its name and consists of rows that contain the stored informa-

tion, each row containing exactly one tuple (or record ). A table can have one or more columns.

A column is made up of a column name and a data type, and it describes an attribute of the

tuples. The structure of a table, also called relation schema , thus is deﬁned by its attributes.

The type of information to be stored in a table is deﬁned by the data types of the attributes

at table creation time.

SQL uses the terms table, row ,and column for relation, tuple ,and attribute , respectively. In

this tutorial we will use the terms interchangeably.

A table can have up to 254 columns which may have diﬀerent or same data types and sets of

values (domains), respectively. Possible domains are alphanumeric data (strings), numbers and

date formats.

Oracle

oﬀers the following basic data types:

•

char ( n ): Fixed-length character data (string), n characters long. The maximum size for

n is 255 bytes (2000 in

8). Note that a string of type char is always padded on

right with blanks to full length of n .( ☞ can be memory consuming).

Example: char (40)

Oracle

•

varchar2 ( n ): Variable-length character string. The maximum size for n is 2000 (4000 in

Oracle

8). Only the bytes used for a string require storage. Example: varchar2 (80)

•

number ( o, d ): Numeric data type for integers and reals. o = overall number of digits, d

= number of digits to the right of the decimal point.

Maximum values: o =38, d =

84 to +127. Examples: number (8), number (5,2)

Note that, e.g., number (5,2) cannot contain anything larger than 999.99 without result-

ing in an error. Data types derived from number are int[eger] , dec[imal] , smallint

and real .

−

•

date : Date data type for storing date and time.

The default format for a date is: DD-MMM-YY. Examples : ’13-OCT-94’, ’07-JAN-98’

•

long : Character data up to a length of 2GB. Only one long column is allowed per table.

-SQLthereisnodatatype boolean . It can, however, be simulated by using

either char (1) or number (1).

Oracle

As long as no constraint restricts the possible values of an attribute, it may have the special

value null (for unknown). This value is diﬀerent from the number 0, and it is also diﬀerent

from the empty string ’’ .

Further properties of tables are:

•

the order in which tuples appear in a table is not relevant (unless a query requires an

explicit sorting).

•

a table has no duplicate tuples (depending on the query, however, duplicate tuples can

appear in the query result).

A database schema is a set of relation schemas. The extension of a database schema at database

run-time is called a database instance or database , for short.

1.1.1 Example Database

In the following discussions and examples we use an example database to manage information

about employees, departments and salary scales. The corresponding tables can be created

under the UNIX shell using the command demobld . The tables can be dropped by issuing

the command demodrop under the UNIX shell.

The table EMP is used to store information about employees:

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 30

...........................................................

7698 BLAKE MANAGER 01-MAY-81 3850 30

7902 FORD ANALYST 7566 03-DEC-81 3000 10

For the attributes, the following data types are deﬁned:

EMPNO : number (4), ENAME : varchar2 (30), JOB : char (10), MGR : number (4),

HIREDATE : date , SAL : number (7,2), DEPTNO : number (2)

Each row (tuple) from the table is interpreted as follows: an employee has a number, a name,

a job title and a salary. Furthermore, for each employee the number of his/her manager, the

date he/she was hired, and the number of the department where he/she is working are stored.

Note: In

The table DEPT stores information about departments (number, name, and location):

DEPTNO DNAME

LOC

STORE

CHICAGO

RESEARCH DALLAS

SALES

NEW YORK

MARKETING BOSTON

Finally, the table SALGRADE contains all information about the salary scales, more precisely, the

maximum and minimum salary of each scale.

GRADE LOSAL HISAL

700 1200

1201 1400

1401 2000

2001 3000

3001 9999

1.2 Queries (Part I)

In order to retrieve the information stored in the database, the SQL query language is used. In

the following we restrict our attention to simple SQL queries and defer the discussion of more

complex queries to Section 1.5

In SQL a query has the following (simpliﬁed) form (components in brackets [ ] are optional):

select [ distinct ] < column(s) >

from < table >

[ where < condition > ]

[ order by < column(s) [ asc

desc ] > ]

1.2.1 Selecting Columns

The columns to be selected from a table are speciﬁed after the keyword select . This operation

is also called projection . For example, the query

select LOC, DEPTNO from DEPT ;

lists only the number and the location for each tuple from the relation DEPT . If all columns

should be selected, the asterisk symbol “

∗

” can be used to denote all attributes. The query

select ∗ from EMP ;

retrieves all tuples with all columns from the table EMP . Instead of an attribute name, the select

clause may also contain arithmetic expressions involving arithmetic operators etc.

select ENAME, DEPTNO, SAL ∗

1.55 from EMP;

For the diﬀerent data types supported in Oracle, several operators and functions are provided:

•

for numbers: abs, cos, sin, exp, log, power, mod, sqrt ,+ , −, ∗,/ ,...

•

for strings: chr , concat (string1, string2), lower, upper , replace (string, search string,

replacement string), translate , substr (string, m, n), length , to date ,...

•

for the date data type: add month, month between, next day, to char ,...

The usage of these operations is described in detail in the SQL*Plus help system (see also

Section 2).

Consider the query

select DEPTNO from EMP ;

which retrieves the department number for each tuple. Typically, some numbers will appear

more than only once in the query result, that is, duplicate result tuples are not automatically

eliminated. Inserting the keyword distinct after the keyword select , however, forces the

elimination of duplicates from the query result.

It is also possible to specify a sorting order in which the result tuples of a query are displayed.

For this the order by clause is used and which has one or more attributes listed in the select

clause as parameter. desc speciﬁes a descending order and asc speciﬁes an ascending order

(this is also the default order). For example, the query

select ENAME , DEPTNO , HIREDATE from EMP;

from EMP

order by DEPTNO [ asc ], HIREDATE desc ;

displays the result in an ascending order by the attribute DEPTNO . If two tuples have the same

attribute value for DEPTNO , the sorting criteria is a descending order by the attribute values of

HIREDATE . For the above query, we would get the following output:

ENAME DEPTNO HIREDATE

FORD 10 03-DEC-81

SMITH 20 17-DEC-80

BLAKE 30 01-MAY-81

WARD 30 22-FEB-81

ALLEN 30 20-FEB-81

...........................

1.2.2 Selection of Tuples

Up to now we have only focused on selecting (some) attributes of all tuples from a table. If one is

interested in tuples that satisfy certain conditions, the where clause is used. In a where clause

simple conditions based on comparison operators can be combined using the logical connectives

and , or ,and not to form complex conditions. Conditions may also include pattern matching

operations and even subqueries (Section 1.5).

Example: List the job title and the salary of those employees whose manager has the

number 7698 or 7566 and who earn more than 1500:

select JOB, SAL

from EMP

where ( MGR = 7698 or MGR = 7566) and SAL > 1500;

For all data types, the comparison operators = , != or <>, <, >, < =, = > are allowed in the

conditions of a where clause.

Further comparison operators are:

•

Set Conditions: < column > [ not ] in ( < list of values > )

Example: select

∗

from DEPT where DEPTNO in (20,30);

•

Null value: < column > is [ not ] null ,

i.e., for a tuple to be selected there must (not) exist a deﬁned value for this column.

Example: select

from EMP where MGR is not null ;

Note: the operations = null and ! = null are not deﬁned!

∗

•

Domain conditions: < column > [ not ] between < lower bound > and < upper bound >

Example:

select EMPNO, ENAME, SAL from EMP

where SAL between 1500 and 2500;

• select ENAME from EMP

where HIREDATE between ’02-APR-81’ and ’08-SEP-81’;

1.2.3 String Operations

In order to compare an attribute with a string, it is required to surround the string by apos-

trophes, e.g., where LOCATION = ’DALLAS’. A powerful operator for pattern matching is the

like operator. Together with this operator, two special characters are used: the percent sign

% (also called wild card), and the underline , also called position marker. For example, if

one is interested in all tuples of the table DEPT that contain two C in the name of the depart-

ment, the condition would be where DNAME like ’%C%C%’. The percent sign means that any

(sub)string is allowed there, even the empty string. In contrast, the underline stands for exactly

one character. Thus the condition where DNAME like ’%C C%’ would require that exactly one

character appears between the two Cs. To test for inequality, the not clause is used.

Further string operations are:

•

upper ( < string > ) takes a string and converts any letters in it to uppercase, e.g., DNAME

= upper ( DNAME )( The name of a department must consist only of upper case letters. )

lower ( < string > ) converts any letter to lowercase,

• initcap ( < string > ) converts the initial letter of every word in < string > to uppercase.

•

length ( < string > ) returns the length of the string.

•

substr ( < string > , n [, m ]) clips out a m character piece of < string > , starting at position

n .If m is not speciﬁed, the end of the string is assumed.

substr (’DATABASE SYSTEMS’, 10, 7) returns the string ’SYSTEMS’.

•

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: