Retrieve, update and delete data from table with multiple conditions.

In Lab 2, we learnt to use WHERE clause to specify which data to retrieve, update or delete. This is usually done through the use of primary key. However, sometimes database administrator or user need to perform to some data in the table based on several criteria. The keyword AND and OR is used so that the WHERE clause filtering can be done with multiple conditions.

The AND operator displays a record if all the conditions separated by AND is TRUE. Figure 1 shows the example of using AND to retrieve data that matches two condition given. 

Figure 1: SQL statements with AND keyword in WHERE clause.

Noticed in the first SQL statement returned a data where both of the condition in the WHERE clause are met. (stdname = "Enson Liu" AND intakeyear = 2018). While the second SQL statement return zero data because only one of the condition are true.

The OR operator displays a record if any of the conditions separated by OR is TRUE. Figure 2 shows the example using OR to retrieve data with either one or both condition is matches.

Figure 2: SQL statements with OR Keyword in WHERE clauses.

Noted the first SQL statement return directly the data regarding Enson because both of the condition matches. Second SQL statements returned the data for Enson Liu and also John Sena because the data for "Enson Liu" is TRUE for the first condition, even though the second condition is wrong, the statement still return TRUE. For the data of John Sena, the first condition is FALSE, but the second condition is TRUE. Therefore under the OR selection, the data for John Sena is still TRUE for the SQL statement.

The third SQL statements did not return any data because there are no data in the table that matches neither one of the condition given.

The use of OR and AND is applicable for all the WHERE clauses in SQL statements, includes UPDATE and DELETE statements.