IS5413 Chap.9 SQL for Defining Tables and Querying One Table
SQL for Defining Tables and Querying One Table
Three sublanguages, and where each belongs in the life cycle
Structured query language is non procedural: you declare the result you want rather than the steps to obtain it. The course divides it into three parts and ties each to a development phase. The definition sublanguage creates, alters and drops schemas, tables, views and constraints, and belongs to physical design.
The manipulation sublanguage inserts, updates, deletes and selects, and belongs to implementation. The control sublanguage grants and revokes privileges and commits work, and belongs to maintenance.
The published benefits of having a standard at all are practical rather than theoretical: lower training cost, portability of applications between products, longer application life, and less dependence on one vendor.
Seven decisions live inside one create statement
The published steps for creating a table are to identify the data types, identify the columns that may not be null, identify the columns that must be unique without being the primary key, identify the primary and foreign key pairings, determine default values, identify the domain constraints on columns, and then create the table and its indexes.
Every one of those becomes a visible clause. Not null phrases answer the second. A default clause answers the fifth. A check clause restricting a column to a list of permitted values answers the sixth. A named primary key constraint answers the fourth, and a named foreign key constraint with its references clause completes it.
Naming constraints rather than leaving them anonymous is what makes an error message legible later.
Six clauses, one order, and the pair that has to stay apart
A select statement lists the columns to return, names the tables to take them from, states the condition a row must meet, optionally categorises the surviving rows, optionally filters those categories, and optionally sorts what is left.
The order matters because rows are filtered before groups exist. A condition about a row belongs in the row filter; a condition about a count, sum or average belongs in the group filter, because at row filtering time nothing has been counted yet.
Two smaller rules travel with it: a column named alongside an aggregate must also appear in the grouping clause, and an aggregate with no grouping returns exactly one row while the same aggregate with grouping returns one row per category.
Precedence decides more answers than it should
The published order is brackets, then negation, then conjunction, then disjunction.
The consequence is that a condition written as one alternative or another alternative and a limit applies the limit only to the second alternative, and bracketing the two alternatives together changes the result set. The course illustrates exactly that pair.
Two operators are worth knowing for the same reason: the membership operator tests a value against a list and is noted as more efficient than a chain of alternatives, and the pattern operator compares strings using wildcards where the percentage sign stands for any number of characters.
What this chapter covers
- 01
Definition, manipulation and control sublanguages
- 02
The seven decisions inside a create table statement
- 03
Delete against drop
- 04
Six clauses in evaluation order
- 05
Row filter against group filter
- 06
Scalar and vector aggregates
- 07
Operator precedence and the bracket that changes the answer
Worked example · free
Decide where each condition belongs
- 2Place the introduction date condition and justify it.
- 3Place the average price condition and justify it.
- 3Place the sort and say why its placement cannot change the result set.
Key terms
- Data Definition Language
- The sublanguage that defines a database, creating, altering and dropping tables, views and indexes and establishing constraints.
- Data Manipulation Language
- The sublanguage that maintains and queries a database, covering insert, update, delete and select.
- Schema
- The structure containing the descriptions of the objects created by a user, including base tables, views and constraints.
- Check Constraint
- A domain constraint written into a column definition that restricts the values the column may hold to a stated set or range.
- Scalar Aggregate
- A single value returned by a query using an aggregate function with no grouping clause, as opposed to one value per category.
- Vector Aggregate
- Multiple values returned by a query using an aggregate function together with a grouping clause, one for each category.
SQL for Defining Tables and Querying One Table FAQ
What is the difference between deleting rows and dropping a table?
Deleting removes rows while the table, its constraints and its indexes survive, so referential integrity rules still apply and a row referred to elsewhere may be refused. Dropping removes the table definition itself and takes every row, constraint and index with it. The pair is a favourite short question because the two words feel interchangeable in English and are not in this language.
Why can a count not go in the row filter?
Because the row filter runs before any grouping has happened, so at that moment there is nothing to count. A count, sum or average is a property of a group, and groups do not exist until the categorisation clause has run, which is why the second filtering clause exists at all. Reading the six clauses in evaluation order makes this obvious and is the fastest way to remember it.
Do I have to write the clauses in evaluation order?
You write them in the fixed order the syntax requires, which is very nearly the evaluation order, and that near coincidence is what causes the trouble. The one place they differ in effect is that the select clause is written first and evaluated second to last, which is why a column alias created there can be used by the sort clause but not by either filter.
Exam move
Write the six clauses down in evaluation order and keep the list beside you for a week. Then take twenty plain English questions about any single table and, without writing any statement, say for each one which clauses it needs and in which clause each condition belongs. Only after that start writing statements, because the clause placement decision is what is being examined and the syntax is the easy part.
Working through SQL for Defining Tables and Querying One Table in IS5413? Sia is AskSia’s AI Information Technology tutor — ask any IS5413 SQL for Defining Tables and Querying One Table question and get a clear, step-by-step explanation grounded in how IS5413 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
