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:
- A "static" part, illustrated by "typical" queries that can be entered and performed as training in a frame called "Test queries examples".
- 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