|
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 column 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
|
