THE SQL WHERE CLAUSE
You can download the images used in this video from this url: https://programming-4-students.blogsp...
The SQL Where ClauseSetting conditions or constraints in SQL commands.The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.SyntaxSELECT column1, column2, ...FROM table_nameWHERE condition;Ex:SELECT * FROM tblbooks WHERE book_year < 2000;SELECT * FROM tblbooks WHERE book_publisher = 1;SELECT a.book_title, b.auth_nameFROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_idINNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_idWHERE b.auth_name = 'Pitok Batolata';The SQL AND OperatorThe WHERE clause can contain one or many AND operators. The AND operator is used to filter records based on more than one condition.SELECT a.book_title, b.auth_nameFROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_idINNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_idWHERE b.auth_name = 'Pitok Batolata' AND a.book_year;The SQL OR OperatorThe WHERE clause can contain one or more OR operators. The OR operator is used to filter records based on more than one condition.SELECT a.book_title, b.auth_name, a.book_yearFROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_idINNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_idWHERE b.auth_name = 'Pitok Batolata' OR b.auth_name = 'Kulas D. Malas';The NOT OperatorThe NOT operator is used in combination with other operators to give the opposite result, also called the negative result.SELECT a.book_title, b.auth_name, a.book_yearFROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_idINNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_idWHERE NOT b.auth_name = 'Pitok Batolata';The SQL LIKE OperatorThe LIKE operator is used in a WHERE clause to search for a specified pattern in a column.There are two wildcards often used in conjunction with the LIKE operator: - The percent sign % represents zero, one, or multiple characters - The underscore sign _ represents one, single characterSELECT a.book_title, b.auth_name, a.book_yearFROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_idINNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_idWHERE a.book_title LIKE 'THE%';SELECT a.book_title, b.auth_name, a.book_yearFROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_idINNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_idWHERE a.book_title LIKE '%song%';The SQL IN OperatorThe IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.The SQL BETWEEN OperatorThe BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.SELECT a.book_title, b.auth_name, a.book_yearFROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_idINNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_idWHERE a.book_year IN (2023,2001)ORDER BY a.book_title;SELECT a.book_title, b.auth_name, a.book_yearFROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_idINNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_idWHERE a.book_year BETWEEN 1990 AND 2010;