SQL Data Manipulation Language (DML)

Welcome to the detailed educational study guide for CS403 Lecture 29, SQL Data Manipulation Language (DML), dedicated to precise row filtering and search predicate evaluation in relational database systems. Building upon basic query construction, this lecture investigates the full expressive capability of the SQL WHERE clause across SELECT, UPDATE, and DELETE statements. In relational database management, the WHERE clause acts as a declarative horizontal filter, evaluating conditional predicates against each candidate tuple in the referenced tables to return only records satisfying the specified search criteria. The lecture systematically covers the full suite of relational comparison operators, including equality (=), inequality (<>, !=), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=), alongside non-standard comparison variants. To construct complex search expressions, students learn Boolean logical operators (AND, OR, NOT) and their strict evaluation precedence rules, utilizing parentheses to guarantee intended logical grouping. The curriculum then delves into specialized SQL predicate operators that streamline conditional querying. The BETWEEN ... AND operator provides an elegant syntax for testing inclusive numeric, textual, and chronological ranges, replacing verbose compound inequalities, and can be inverted using NOT BETWEEN. To simplify testing against discrete collections of values, the IN operator evaluates set membership, effectively replacing long chains of disjunctive OR conditions and preparing learners for nested subquery operations. For textual analysis, the lecture introduces string pattern matching through the LIKE operator, exploring standard wildcards: the percent sign (%) to match zero or more arbitrary characters, and the underscore (_) to match exactly one character. Crucially, the lecture addresses relational three-valued logic (3VL) and the handling of missing data, teaching students that null values cannot be compared using standard equality operators but must instead be evaluated using explicit IS NULL and IS NOT NULL predicates. Finally, the lecture introduces the ORDER BY clause, establishing how query result sets are sorted in ascending (ASC) or descending (DESC) sequence. Understanding these DML filtering mechanics is vital for excelling in Virtual University CS403 examinations. Students should review solved past papers by Moaaz and Waqar Siddhu, practice chapter-by-chapter solved MCQs, and study official handouts for exam success.