SQL: Projection, selection

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

SQL Summary

Q01: Display the name, number and date of birth of students.

SQL summary

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.

SQL summary

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.

SQL summary

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

Next section | Previous section | SQL summary