SQL Language

This presentation focuses on querying databases with SQL. Language training is facilitated by linking to a relational DBMS which enables interactive processing of requests. For a more in-depth training in the SQL language, download IBEasy+ software for design and administration of relational databases.

All the queries refer to the "STUDENTS" database created under the column DB Design. The data diagram is available for each interactive request.

Specifications of the language.

SQL (Structured Query Language) is a unique language used to describe, manage, control access and query relational databases. It is a declarative language, intended for both first-time users and experienced programmers. It is regulated by a standard (ANSI/ISO) that guarantees the portability of the language on the different hardware and software platforms. A SQL command, written in a Windows environment under ACCESS can, often unaltered, be used directly in an Oracle environment under Unix...

SQL commands

Category SQL commands
Data Description (DDL)
CREATE Creating tables.
ALTER Modifying tables.
DROP Removing tables.
Data Manipulation (DML)
INSERT Inserting rows in the table.
UPDATE Updating the rows of a table.
DELETE Removing rows within a table.
Data Control (DCL)
GRANT Providing access rights.
REVOKE Removing access rights.
COMMIT Handling the updates.
ROLLBACK Removed the updates.
Interrogation  
SELECT Queries

In this presentation, we will only process commands for retrieving data: SELECT clause.

Syntax of 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 has 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 typed in as presented.
...                               : The preceding values can be repeated several times
_ Underlined              : indicate the default value.

Meaning of the clauses

SELECT Specify the columns that will be displayed in the reply.
FROM Specify the table(s) that are relevant to the request.
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 or columns within a group.
HAVING Specify the conditions that apply to a group.
ORDER BY Specify the order that will appear in the lines of the reply:
-ASC:  in ascending order (default)
-DESC: in descending order
SQL Interactive Learning.

To facilitate language learning, each chapter contains two parts:

  1. A "static" part, illustrated by "typical" queries that can be entered and performed as training in a frame called "Test queries examples".
  2. A "dynamic" part composed of a series of queries to verify if the examples are well understood. These questions are indicated by the red label Q01 ... and contain a framework allowing keyboarding and execution of the corresponding SQL query as well as an "Answer" button.

Warning: In all interactive SQL queries, table names must be capitalized.

The course is organized into six sections:

1-Projection, selection
2-Predicates: NULL, IN, LIKE, BETWEEN
3-Joins
4-Subsets, functions COUNT, SUM, AVG, MIN, MAX
5-Sub-queries
6-Summary exercises