SQL Language

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

All queries reference the "STUDENTS" database created in the DB Design column. 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. This is a declarative language for both new users and experienced programmers. It is regulated by a standard (ANSI/ISO) which ensures the portability of the language on the various hardware and software platforms. An SQL command, written in a Windows environment under ACCESS can, often unchanged, 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 deal with commands to retrieve 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 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