ISYS1055 Chap.6 Single-Table SQL in SQLite: SELECT, WHERE and LIKE
Single-Table SQL in SQLite: SELECT, WHERE and LIKE
Week 5 opens the four-week SQL block, and its outcomes are precise: form a simple SELECT against one table, form a simple WHERE condition, build more complex conditions with AND, OR and string functions such as LOWER(), use aggregates such as COUNT() and refine them with DISTINCT, use set membership with IN and ranges with BETWEEN, and write partial-match queries with LIKE. The course states its own scope up front - SQL-92 standard features only, except for the handling of dates and times, with all activity carried out on SQLite through SQLite Studio - and it requires a written format: keywords in capitals, everything else lower case, one clause per line. Single-table querying is quiz material from Week 7 onwards and the foundation of every query in Part B of Assessment Task 2.
What this chapter covers
- 01SQL as a declarative language: state what you want, not how to get it; keywords and identifiers are case-insensitive but string literals are case-sensitive
- 02SELECT statement anatomy - SELECT, FROM, WHERE, ORDER BY - with only SELECT and FROM required, plus the logical evaluation order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
- 03SELECT list features: all attributes, chosen attributes, column aliases with AS, arithmetic expressions and constants
- 04WHERE conditions: comparison operators including <>, AND / OR / NOT, and parentheses to force evaluation order when mixing AND with OR
- 05Case-normalising both sides of a comparison with LOWER(); other string functions named by the course - UPPER(), LENGTH(), RTRIM(), SUBSTR()
- 06NULL handling: never compare with =, always IS NULL or IS NOT NULL
- 07Partial matching with LIKE and its wildcards, % for zero or more characters and _ for exactly one; set membership with IN; inclusive ranges with BETWEEN
- 08DISTINCT and the aggregate functions COUNT, SUM, AVG, MIN, MAX - including COUNT(*) vs COUNT(attribute) vs COUNT(DISTINCT attribute), and why bare attributes must not be mixed with aggregates
Build one compound single-table query, then count the distinct categories
- +1Start with the SELECT list and the FROM clause, one clause per line and keywords capitalised. SELECT common_name, category, price FROM plant; - this already runs and is worth checking before conditions are added.
- +1Add the category condition and parenthesise it. Two alternatives are equivalent: (LOWER(category) = 'fern' OR LOWER(category) = 'palm') or the tidier LOWER(category) IN ('fern', 'palm'). The parentheses matter because AND binds more tightly than OR, so an unparenthesised OR mixed with the later ANDs would change the meaning of the query. LOWER() is applied because string literals are case-sensitive and you cannot assume how the data was entered - normalise both sides.
- +1Add the range and the partial match. BETWEEN 15 AND 40 is inclusive of both endpoints, so it needs no extra >= or <=. For the name, LIKE with the % wildcard matches zero or more characters on either side, and LOWER() again removes any dependence on how the name was capitalised: LOWER(common_name) LIKE '%silver%'. The single underscore wildcard would match exactly one character and is not what is wanted here.
- +1Add the sort and write part (b). ORDER BY price DESC gives highest first; ascending is the default when no direction is given. For the count, the question asks how many different categories exist, so duplicate elimination has to happen before counting: SELECT COUNT(DISTINCT category) FROM plant; - COUNT(*) would count rows, and COUNT(category) would count non-null category values rather than distinct ones.
Key terms
- Declarative language
- A language in which you specify what you want rather than the procedure for obtaining it. Reading a file row by row in an imperative language takes an open, loop, read, break and close sequence; in SQL the same intent is one SELECT statement, and the engine decides how to execute it.
- Logical evaluation order
- The order in which the clauses of a query are conceptually applied: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. It is not the order in which they are written, and it explains why a column alias defined in SELECT can be used in ORDER BY but not in WHERE.
- LIKE wildcards
- % matches zero or more characters and _ matches exactly one, so '%island%' finds the word anywhere in the value, 'island%' anchors it to the start and 'eli_abeth' matches both spellings of a name. NOT LIKE negates the test.
- IN and BETWEEN
- IN tests membership of a listed set and replaces a long chain of ORs; BETWEEN tests an inclusive range, so BETWEEN 15 AND 40 includes both 15 and 40. NOT IN inverts membership, but be careful with a list that contains a null, because the test then returns no rows.
- DISTINCT
- Eliminates duplicate rows from a result, either across the whole SELECT list or inside an aggregate as COUNT(DISTINCT attribute). Duplicates are more than an annoyance - they silently corrupt any count computed over them.
- Aggregate function
- COUNT, SUM, AVG, MIN and MAX, which collapse many rows into one value. COUNT(*) counts tuples including those with nulls, while COUNT(attribute) counts only the non-null values of that attribute.
Single-Table SQL in SQLite: SELECT, WHERE and LIKE FAQ
Why does the course insist on a particular SQL layout?
Because SQL ignores case and whitespace, which means a badly laid-out query is still correct and still unreadable - and unreadable queries are where errors hide. The required format is keywords in capitals, everything else lower case except string literals, and one clause per line. Assessment Task 2 Part B is marked on queries written this way, and it makes a long WHERE clause far easier to debug.
Why does my WHERE clause with = NULL return nothing?
Because NULL means the value is absent, so any comparison with it evaluates to unknown rather than true, and a row is only returned when the condition is true. Use IS NULL or IS NOT NULL. This is the single most common single-table error in the course, and it also shows up in the Assessment Task 1 question that asks you to judge whether a given SQL statement is valid.
When should I use IN instead of a chain of ORs?
Whenever you are testing one attribute against a list of constants. WHERE category = 'fern' OR category = 'palm' OR category = 'cycad' says exactly the same thing as WHERE category IN ('fern', 'palm', 'cycad'), and the set form is shorter, harder to get wrong and easier to extend. Reserve OR for conditions on different attributes.
Can AI help me learn SQL for ISYS1055?
Yes, as a study aid. Sia is an AI tutor built to mirror how ISYS1055 is taught and assessed at RMIT University: paste a query that returns the wrong rows and it will walk you through the clause that is at fault, explain why parentheses change the result, or set you a fresh practice question in the same style. It does not complete graded assessment for you, and RMIT University academic-integrity rules apply - the queries you submit must be your own, and you should run every one of them in SQLite Studio before submitting.
Assessment move
Never read SQL - run it. Open SQLite Studio against the supplied database and build each query in layers: SELECT and FROM first, then one condition at a time, checking the row count after every addition, because a query that returns rows is not the same as a query that answers the question. Keep a personal error log of the three classics - an unparenthesised OR, a comparison with NULL, and a bare attribute beside an aggregate - and re-read it before each quiz; quiz 3 falls in your Week 7 practical and covers Weeks 5 and 6. Write everything in the course's required format from the first week rather than tidying it later, and confirm on Canvas which database file the current offering supplies for practice.
Working through Single-Table SQL in SQLite: SELECT, WHERE and LIKE in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 Single-Table SQL in SQLite: SELECT, WHERE and LIKE question and get a clear, step-by-step explanation grounded in how ISYS1055 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
