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 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 changed to a list of logic operators OR. If the list of values is not known, as in sub-queries, this predicate is necessary.

-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 enables you to search in a string, when 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 : s: 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

SQL summary

Q04: Show the names of female students whose first letter of name is a "J".

SQL Summary

Predicate BETWEEN

This enables you to compare a field's value with a lower and upper bound.

-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

Next section | Previous section | SQL summary