Projection
The projection consists of selecting the name of the column(s) of the table(s) you wish to be displayed in the response. If you wish to display all columns, "*" must be used. Column names are inserted next to the SELECT clause.
-Display students' names and gender codes.
SELECT Nometu, Cdsexe FROM ETUDIANT;
-Display the contents of the ETUDIANT table
SELECT * FROM ETUDIANT;
Test queries examples
Q01: Display the name, number and date of birth of students.
Selection
The operation of selection involves selecting rows (tuples) of one (or several) table(s) which meet certain conditions. Conditions are specified after the WHERE clause.
-List all male students.
SELECT * FROM ETUDIANT WHERE Cdsexe='H';
Q02: List teachers with more than two years' seniority in their rank.
Projection and Selection
It is clear that the projection and selection operations can be used in the same SQL query.
-Show number and name of students born in 1980.
SELECT Numetu, Nometu FROM ETUDIANT WHERE Dtnaiss >= '1980-01-01' AND Dtnaiss <= '1980-12-31';
Converting data and processing dates.
A given date must be expressed in the American format (yyyy-mm-dd) and not in the French format (dd-mm-yyyy). An individual date may be considered:
-As a string. For instance '1998-06-25' (25 June 1998). In that case, no calculations can be made, but comparisons can be made.
-As a date.
To compute the difference, in days, between two dates, use the DATEDIFF function.
Syntax of DATEDIFF function:
DATEDIFF(Date2,Date1).
Date2 must be greater than Date1
The CURRENT_DATE function is used to get the system date.
Example:
To find out the age of the students at the current date, enter: DATEDIFF(CURRENT_DATE,Dtnaiss)/365.
Q03: Names of female students who were born after 1980.
ORDER BY clause
Using the ORDER BY clause, you can categorize the results of a query based on the value of certain attributes (columns).
- Display the list of teachers by rank and in descending order of name.
SELECT Grade, Nomens FROM ENSEIGNANT ORDER BY Grade, Nomens DESC;
The previous query, which uses the column names, is equivalent to the following query, which uses the column numbers used to order the results.
SELECT Grade, Nomens FROM ENSEIGNANT ORDER BY 1, 2 DESC;
Test queries examples