Predicate NULL
An attribute (a column, a field) can have the 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 that models individuals, the attribute "Marital Name" is not relevant for male individuals and thus as the value "NULL". The value "NULL" differs from the default value of the attribute: zero for a numeric attribute and space for a string or character attribute.
The syntax is: IS NULL and its negation IS NOT NULL.
-Number, name and gender of students with unknown birth dates.
SELECT Numetu, Nometu FROM ETUDIANT WHERE Dtnaiss IS NULL;
Predicate IN
It consists of a list of values and verifies if a specific value is in the list.
The syntax is: IN (val1 , val2, ...) and its negation NOT IN (val1 , val2, ...).
When the list of values is known and set, the IN predicate can be replaced by a list of logic operators OR. If the list of values is not known, as in sub-queries, this predicate is required.
-Number and name of the teachers who are either assistants (ASS) or professors (PR)
| IN version | OR version |
| SELECT Numens, Nomens FROM ENSEIGNANT WHERE Grade IN ('ASS', 'PR'); |
SELECT Numens, Nomens FROM ENSEIGNANT WHERE Grade='ASS' OR Grade='PR'; |
Predicate LIKE
This predicate allows searching in a string, where you have incomplete information. There are two wildcards:
%: is used to represent a character string of variable length.
_ : is used to represent just one character.
Syntax is : LIKE 'Research string' and its negation NOT LIKE 'Research string'
-Name and date of birth of students whose 2° letter of name is “a”.
SELECT Nometu, Dtnaiss FROM ETUDIANT WHERE Nometu LIKE '_a%';
Test queries examples
Q04: Show the names of female students whose first letter of name is a "J".
Predicate BETWEEN
This lets you compare the value of a field with a lower and higher limit.
-The names of individuals whose coefficient ranges from 1 to 2.
SELECT Nomat FROM MATIERE WHERE Coeff BETWEEN 1 AND 2;
This predicate, is a simplification of writing and can be replaced by:
SELECT Nomat FROM MATIERE WHERE (Coeff >=1) AND (Coeff <=2);
Test queries examples