Lesson #5: Querying Data
Querying data from a single table is fairly straightforward because we choose the table, then the columns, and finally the filtering rules to select the data. We list the desired columns in the SELECT
clause, the table where the columns are found in the FROM
clause, and then we put the filtering conditions in the WHERE
clause.
When the data doesn’t reside in one table, we use joins to create super tables. Super tables are really only result sets. A result set exists whether its source is a single table or multiple tables.
Result sets are two-dimensional structures that contain a list of structures, where the structures are defined by a one or more column definitions. A list is like an array except it doesn’t have an upward element limit. Lists typically keep track of elements by numerically indexing them. A single column list is typically identified by the numeric index alone but a database may resolve it through either the row identifier or a combination of the row identifier and column name. A multiple column list identifies the row by the index, and the column by the element name. Element names are defined when you create structures, and in databases their definition becomes part of the data catalog (or metadata). Some databases only support tables and views (a preserved join between tables or other views) in the database catalog. More advanced databases, like Oracle, support object types and tables of object types in the database catalog.
The idea of columns is simple by contrast to the output from SQL functions. SQL functions can process column results into new values on a row-by-row basis. The processing can convert a column’s data type or value. Some functions let you cast from one data type to another without changing a value.
You also have the ability to concatenate columns into new columns with new names. You assign the new names by using column aliases. Lastly, you can preserve a query that does any of this in the data dictionary by creating a VIEW
.
This lesson focuses on the skills to: (a) query rows of data from single or multiple tables, (b) apply SQL functions to transform elements of a row in the result set, (c) apply SQL functions to aggregate rows of data based on some unique subset of column values, and (d) glue elements of a row together into complex strings (gluing is formally known as concatenation).
- Single Table Queries
- Learn how to write a query against a single table with column and table aliases.
- Multiple Table Queries
- Learn how to join tables by using surrogate keys, example demonstrate ANSI SQL:89 with Oracle’s proprietary extentions and ANSI SQL:92.
- Learn what a
CROSS JOIN
is, and how to write one in ANSI SQL:89 and ANSI SQL:92 SQL syntax. - Learn what a
INNER JOIN
is, and how to write one in ANSI SQL:89 and ANSI SQL:92 SQL syntax. - Learn what a
NATURAL JOIN
is, and how to write one in ANSI SQL:89 and ANSI SQL:92 SQL syntax. - Learn what a
LEFT JOIN
is, and how to write one in ANSI SQL:89 and ANSI SQL:92 SQL syntax. - Learn what a
RIGHT JOIN
is, and how to write one in ANSI SQL:89 and ANSI SQL:92 SQL syntax. - Learn what a
FULL OUTER JOIN
is, and how to write one in ANSI SQL:89 and ANSI SQL:92 SQL syntax. - Learn what a set operators are, and how to write ANSI SQL:89 and ANSI SQL:92 SQL syntax for them.
- Learn what a self join is, and how to write one in ANSI SQL:89 and ANSI SQL:92 SQL syntax.
- Learn what a anti-join is, and how to write ANSI SQL:89 and ANSI SQL:92 SQL syntax for them.
- Inline Views
- Learn how to create and use an inline view.
- SQL Functions
- Learn how to use SQL functions to change values or data types.
- SQL Concatenation
- Learn how to concatenate, or glue together, strings into mega-strings, and how the process is supported differently by SQL dialects.
- SQL Date Math
- Learn how to use SQL dialect to add, subtract, and manage dates and timestamps.
“The processing can convert their data type, change their value, or change their data type.”
So what is the difference between changing and converting data type?
Rachelle Haynes
30 Jan 10 at 8:13 pm
It wasn’t worded well. I’ve changed it. Does that make it clearer?
michaelmclaughlin
30 Jan 10 at 9:20 pm
I found a link thats broken on your page.
The FULL OUTER JOIN link that points to
michaelmclaughlin.info/db1/lesson-5-querying-data/multiple-table-query/full-outer-join/ doesn’t exist
michael stokes
5 Jul 10 at 1:54 pm
The link on Full Outer Join is wrong. it should be:
https://michaelmclaughlin.info/db1/lesson-5-querying-data/multiple-table-query/full-join/
The current broken link is:
https://michaelmclaughlin.info/db1/lesson-5-querying-data/multiple-table-query/full-outer-join/
Brad Lawrence
18 May 11 at 12:07 pm
Finally, it’s fixed. Hope you’re well Michael.
michaelmclaughlin
22 Oct 14 at 2:18 am