Filtering Rows Based On Conditions

By Tiationg Kho |
Tags:

Filtering Rows Based On Conditions

SELECT col_name
FROM table_name
WHERE condition;
-- condition can use `AND`, `OR`, `NOT`, `()`

SELECT col_name
FROM table_name
WHERE col_name IN (val_list); -- or sub_query
-- or `NOT IN`

SELECT col_name
FROM table_name
WHERE EXISTS (sub_query);
-- or `NOT EXISTS`

SELECT col_name
FROM table_name
WHERE col_name BETWEEN lower_bound AND upper_bound;

SELECT col_name
FROM table_name
WHERE col_name LIKE 'PatternWithWildcards';

Wildcard

  • _ wildcard is used to represent exactly one character.

  • % wildcard is used to represent zero, one, or multiple characters.

  • [] wildcard is used to specify a set or range of characters to match.

    • - is used within [] to specify a range of characters.
    • ^ is used within [] to exclude characters specified in the brackets.
    • , is used within [] to separate the candidate characters.
    SELECT documentTitle
FROM documents
WHERE documentTitle LIKE '[A-C]dam';

SELECT documentTitle
FROM documents
WHERE documentTitle LIKE 'Report [^0-9]%';

SELECT documentTitle
FROM documents
WHERE documentTitle LIKE '[a,b]%';

595. Big Countries

/* Write your T-SQL query statement below */

SELECT [name], [population], area
FROM World
WHERE area >= 3000000 OR [population] >= 25000000;

1527. Patients With a Condition

/* Write your T-SQL query statement below */

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions LIKE 'DIAB1%' OR conditions LIKE '% DIAB1%';

1757. Recyclable and Low Fat Products

/* Write your T-SQL query statement below */

SELECT product_id
FROM Products
WHERE low_fats = 'Y' AND recyclable = 'Y';

1873. Calculate Special Bonus

/* Write your T-SQL query statement below */

SELECT 
    employee_id, 
    CASE WHEN (employee_id % 2 = 1 AND [name] LIKE '[^M]%') THEN salary ELSE 0 END AS bonus
FROM Employees
ORDER BY employee_id ASC;