Applications of Databases to Humanities and Social Sciences

SQL : predicates NULL, IN, LIKE, BETWEEN

Predicate NULL

An attribute (a column, a field) can have value "NULL" either because of incomplete information (the value was not known at the time of the data acquisition) or because the data is not relevant. In a table which models individuals, the attribute " Marital Name " is not relevant for the individuals of male sex and thus as the value "NULL". Value "NULL" is different from the default value of the attribute : zero for a numeric attribute and space for a string or character attribute.

Syntax is: IS NULL and its negation IS NOT NULL.

-Number, name and sex of the students whose date of birth is not known

SELECT Numetu, Nometu
FROM ETUDIANT
WHERE Dtnaiss IS NULL;

Predicate IN

It comprises a list of values and checks if a particular value appears on this list.

Syntax is: IN (val1 , val2, ...) and its negation NOT IN (val1 , val2, ...).

When the list of values is known and fixed, the predicate IN can be replaced by a list of logical operators OR. When the list of values is not known, as in subqueries, this predicate is necessary.

-Number and name of the teachers who are either assistants (ASS) or professors (PR)

Predicate LIKE

This predicate allows to search inside a character string, when one has incomplete information. It uses 2 generic characters:

% : used to represent a character string of variable length.
_   : used to represent a single character.

Syntax is : LIKE 'Research string' and its negation NOT LIKE 'Research string'

-Name and date of birth of the students whose 2 ° letter of the name is 'a'.

SELECT Nometu, Dtnaiss
FROM ETUDIANT
WHERE Nometu LIKE '_a%';

SQL summary

Q04: Display the Name of the students of female sex whose first letter of the first name is a "J".

SQL Summary

Predicate BETWEEN

It allows to compare the value of a field with a lower and an upper limit.

-Name of the subjects which have a coefficient ranging between 1 and 2.

SELECT Nomat
FROM MATIERE
WHERE Coeff BETWEEN 1 AND 2;

This predicate, is a simplication of writing and can be replaced by :

SELECT Nomat
FROM MATIERE
WHERE (Coeff >=1) AND (Coeff <=2);

© Marc Grange, February 2001 | Last update: January 29, 2009