• Foundation
  • About 14 min
  • 5 practice questions

More SQL: conditions, patterns and changing data

Use AND, OR, NOT, BETWEEN, IN, LIKE, ORDER BY and DISTINCT, and add, change or remove rows with INSERT, UPDATE and DELETE.

If something looks wrong, please tell us.

Simple SELECT statements fetch whole tables. Real work needs filters: books under a certain price, names that start with a letter, or customers from two cities. SQL has small, readable tools for this. Everything here is explained with one made-up table so that you can trace each answer by hand.

The sample table BOOK

BOOK(BookID, Title, Author, Price, Copies): (1, Garden Birds, Meera Rao, 250, 4), (2, Green Rivers, Sunil Das, 180, 0), (3, Grammar Made Easy, Meera Rao, 320, 6), (4, Map Skills, Arun Nair, 150, 3), (5, Gentle Maths, Sunil Das, 210, 5). All titles and prices are invented for practice.

Conditions in WHERE

  • Comparison: =, <>, <, >, <=, >=.
  • AND: both conditions must be true. OR: at least one must be true. NOT: reverses a condition.
  • BETWEEN a AND b: value from a to b, including both ends.
  • IN (list): value matches any item in the list.
  • LIKE with patterns: % stands for any number of characters, and _ for exactly one character.

Sorting and duplicates

  • ORDER BY column sorts ascending by default; add DESC for descending. You may sort by one column and then another.
  • DISTINCT removes repeated values from the result.

Changing data

  • INSERT INTO BOOK VALUES (6, 'River Atlas', 'Arun Nair', 275, 2); would add a row (the examples and questions below use the original five rows).
  • UPDATE BOOK SET Price = Price + 20 WHERE Author = 'Sunil Das'; changes chosen rows.
  • DELETE FROM BOOK WHERE Copies = 0; removes chosen rows.

Forgetting WHERE in UPDATE or DELETE affects every row, a very costly mistake.

Worked examples

Example 1. SELECT Title FROM BOOK WHERE Author = 'Meera Rao' AND Price < 300; Meera Rao wrote books 1 and 3 with prices 250 and 320. Only 250 is below 300, so the output is Garden Birds.

Example 2. SELECT Title, Price FROM BOOK WHERE Price BETWEEN 150 AND 250; BETWEEN includes both ends. Prices 250, 180, 150 and 210 qualify; 320 does not. Output: Garden Birds 250, Green Rivers 180, Map Skills 150, Gentle Maths 210.

Example 3. SELECT Title FROM BOOK WHERE Title LIKE 'G%'; Titles beginning with G: Garden Birds, Green Rivers, Grammar Made Easy, Gentle Maths. Map Skills is excluded. With LIKE '_a%' (second letter a) the output would be Garden Birds and Map Skills.

Example 4. SELECT DISTINCT Author FROM BOOK; The Author column has five entries but three different authors, so the output has 3 rows: Meera Rao, Sunil Das, Arun Nair.

Example 5. After UPDATE BOOK SET Price = Price + 20 WHERE Author = 'Sunil Das'; the prices of books 2 and 5 become 200 and 230. Then DELETE FROM BOOK WHERE Copies = 0; removes book 2, so 4 rows remain.

WHERE picks rows. AND needs both, OR needs one. % means many characters, _ means one. Never run UPDATE or DELETE without WHERE unless you mean every row.

— Remember

Key takeaways

  • Combine conditions with AND, OR and NOT.
  • BETWEEN includes both ends.
  • LIKE uses % and _ for patterns.
  • ORDER BY sorts; DISTINCT removes duplicates.
  • UPDATE and DELETE need a careful WHERE.

Check your understanding

  1. 1. In the BOOK table, how many titles have a price above 200?

    1. 2
    2. 4
    3. 3
    4. 5
    Show answer

    Answer: C. Prices above 200 are 250, 320 and 210, so three titles qualify.

  2. 2. Which clause sorts the result from highest to lowest price?

    1. ORDER BY Price DESC
    2. GROUP BY Price DESC
    3. WHERE Price DESC
    4. SORT BY Price DESC
    Show answer

    Answer: A. ORDER BY with DESC sorts from largest to smallest.

  3. 3. Which pattern finds titles beginning with M?

    1. LIKE '%M'
    2. LIKE '_M'
    3. LIKE 'M%'
    4. LIKE '%M%'
    Show answer

    Answer: C. 'M%' means starting with M followed by anything. '%M' means ending with M.

  4. 4. SELECT DISTINCT Author FROM BOOK returns how many rows?

    1. 5
    2. 4
    3. 2
    4. 3
    Show answer

    Answer: D. There are three different authors: Meera Rao, Sunil Das and Arun Nair.

  5. 5. DELETE FROM BOOK; without a WHERE clause will

    1. Remove nothing
    2. Remove all rows
    3. Remove one row
    4. Remove the first column
    Show answer

    Answer: B. Without WHERE, DELETE affects every row of the table.

Privacy · Terms · Refunds · Delivery