Applications of Databases to Humanities and Social Sciences

SQL: Projection, selection

Projection

The operation of projection consists in selecting the name of the columns of table(s) which one wishes to see appearing in the answer. If one wants to display all the columns "*" should be used. The columns are given after the SELECT clause.

-Display the Name and the code sex of the students.

SELECT Nometu, Cdsexe
FROM ETUDIANT;

-Display the contents of the table ETUDIANT

SELECT *
FROM ETUDIANT;

SQL Summary

Q01: Display the Name the number and date of birth of students

SQL summary

Selection

The operation of selection consists in selecting rows (tuples) of one (or several) table(s) which satisfy certain conditions. The conditions are expressed after the WHERE clause.

-Display the list of the students of male sex.

SELECT * 
FROM ETUDIANT
WHERE Cdsexe='H';

Q02: List teachers who have more than 2 years of seniority in rank.

SQL summary

Projection and Selection

The operations of projection and selection can obviously be used in the same SQL request.

-Display the number and the name of the students born in 1980

SELECT Numetu, Nometu
FROM ETUDIANT
WHERE Dtnaiss >= '01-01-1980' AND Dtnaiss <= '12-31-1980';

Conversion of the data and treatment of dates

A particular date must be expressed in the American form (mm-dd-yyyy) and not in the french form (dd-mm-yyyy). One can consider a particular date :

-As a character string. For example '06-25-1998' (on June 25, 1998 ). One cannot , in that case, make calculation but one can make comparisons.
-As a date. It is then necessary to use the data conversion function "CAST " and one can then make calculations.

Syntax of the CAST function :

CAST (< col > AS < Datatype >)
    <Col>: Column name or value.

    < Datatype >: {Integer | Decimal | Char | Date | ...}

The notation:

'06-25-1998 '-Dtnaiss is not allowed, it is thus necessary to use
CAST ('06-25-1998' AS Date)-Dtnaiss

The function CURRENT_DATE allows to recover the system date.

Q03: Name of the students of female sex born after 1980

SQL summary

ORDER BY clause

It is possible, with the ORDER BY clause, to order the results of a query according to the value of some attributes (columns).

- Display the list of the teachers by rank and in decreasing order of names.

SELECT Grade, Nomens
FROM ENSEIGNANT
ORDER BY Grade, Nomens DESC;

The preceding query, which uses the names of columns, is equivalent to the following query which uses the numbers of the columns which are used to order results.

SELECT Grade, Nomens
FROM ENSEIGNANT
ORDER BY 1, 2 DESC;

© Marc Grange, February 2001 | Last update: April 10, 2006