|
SQL language
This presentation is not an exhaustive course on SQL, but a first
approach of the language and its main functionalities. The
training of the language is eased by the coupling with a relational
DBMS which allows an interactive treatment of the queries.
For a more in deep training of SQL language, download IBEasy+, design and administration
software for relational databases.
All the queries refer to the "STUDENTS"
database created under the topic DB
Design. The diagram of data is available from each interactive
query.
Characteristics
of language
SQL ( Structured Query
Language) is the unique language which allows to
describe, handle, control the access and query relational databases.
It is a declarative language, which addresses at the same time the
beginner users and the experienced programmers. It is governed by a
standard (ANSI/ISO) which insures the portability of the language on
various hardware and software platforms. A SQL command, written in a
Windows environment under ACCESS can, often without modification, be
used directly in an environment ORACLE under Unix...
SQL
commands
| Category |
SQL commands |
|
Data Description (DDL)
|
CREATE
|
Creation of tables
|
|
ALTER
|
Modification of tables
|
|
DROP
|
Removal of tables
|
|
Data Manipulation (DML)
|
INSERT
|
Insertion of rows in a table
|
|
UPDATE
|
Update of rows in a table
|
|
DELETE
|
Removal of rows in a table
|
|
Data Control
(DCL)
|
GRANT
|
Grant of access rights
|
|
REVOKE
|
Removal of access rights
|
|
COMMIT
|
Treatment of updates
|
|
ROLLBACK
|
Removal of updates
|
|
Interrogation
|
SELECT
|
Queries
|
We will treat, in this presentation, only
commands related to the interrogation of data : SELECT clause.
Syntax of
the SELECT command
SELECT [DISTINCT]
{* | expr [AS alias], ... }
FROM table [alias], ...
[WHERE { conditions | under conditions} ]
[GROUP BY expr, ...] [HAVING conditions]
[ORDER BY {expr | num}{ASC | DESC},
...];
Syntactical
agreements
CAPITAL LETTERS : (SELECT) Enter values exactly as
presented.
Italic : column, table. Parameter
having to be replaced by the suitable value.
Alias : Synonym of a name of table or column.
Conditions : Expression having the true or false
value.
Under conditions : Expression containing a subquery.
Expr : Column, or calculated attribute (+,-, *, /)
Num
: Column number
{} : Ex {ON|OFF}. One of the values separated by "|" must
obligatory be typed in.
[ ] : optional Value.
( ) : the brackets and commas must be type in as presented.
... : The preceding values can be repeated several times
_ Underlined : indicate the default value.
Meaning of the clauses
|
SELECT
|
Specify the columns which will
appear in answer
|
|
FROM
|
Specify the table(s) intervening in
the query
|
|
WHERE
|
Specify filtering conditions to
apply on rows. One can find:
-Comparators : =, >, <, > = <, =, < >
-Logical operators : AND, OR, NOT
-Predicates : IN, LIKE, NULL, ALL, SOME, ANY, EXISTS...
|
|
GROUP BY
|
Specify the column(s) of a group
|
|
HAVING
|
Specify the conditions associated
with a group
|
|
ORDER BY
|
Specify the order in which will
appear the rows of the answer:
-ASC: in ascending order (default)
-DESC: in descending order
|
SQL
interactive training
To facilitate the learning of the language, each chapter
is composed of 2 parts :
- A "static" part, illustrated by "typical" queries
which can be keyboard and executed as training within a frame entitled
"Test queries examples".
- A "dynamic" part composed of a set of queries
allowing to check if the examples are well understood. These questions
are indicated by the wording in red Q01 ...
and contain a frame allowing keyboarding and execution of the
corresponding SQL query as well as a button "Answer".
The training is organized in 6 sections :
1-Projection, selection
2-Predicates: NULL, IN, LIKE, BETWEEN
3-Joins
4-Subsets, functions COUNT, SUM, AVG, MIN, MAX
5-Subqueries
6-Recapitulative exercise
|
