Db2 Basics

Section 1. Querying data

This section shows you how to query data from the Db2 database. We will start with a simple SELECT statement that allows you to query data from columns of a table.

• SELECT – query data from one or more column of a table.

Section 2. Sorting a result set

• ORDER BY – sort the result set in ascending or descending order based on values in a one or more columns or expressions.

Section 3. Filtering data

• WHERE – specify a search condition for rows to be returned by a query.
• DISTINCT  – remove duplicate rows from the result set of a query.
• AND – query rows that satisfy both predicates of the search condition.
• OR– query rows that satisfy either or both predicates of the search condition.
• IN – check if a value matches any value in a list of literal values or a result set returned by a subquery.
• BETWEEN – test whether a value lies between two other values.
• LIKE  –  check if a string contains a certain pattern.

Section 4. Limiting rows

• LIMIT – limit the number of rows returned by a query.
• FETCH – an ANSI-SQL version of LIMIT that restricts the number of rows returned by a query.

Section 5. Aliases

• Alias – learn how to use column aliases to change the column heading of the query output and table aliases to improve the readability of queries.

Section 6. Joining tables

• Join – learn the overview of Db2 joins including inner join, left join, right join and full outer join.
• INNER JOIN – select rows from a table that have matching rows in another table.
• LEFT JOIN – return all rows from the left table and matching rows from the right table. In case the right table does not have the matching rows, use NULL to fill the columns from the right table.
• RIGHT JOIN – learn a reversed version of the left join.
• FULL OUTER JOIN – return matching rows from both left and right tables, and rows from each side if no matching rows exist.
• Self-join – join a table to itself using to query hierarchical data or compare rows within the same table.
• CROSS JOIN – make a Cartesian product of rows from the joined tables.

Section 7. Grouping data

• GROUP BY– divide rows into groups based on specified columns.
• HAVING – specify a search condition for groups returned by GROUP BY clause.

Section 8. Subquery

• Subquery – learn about subqueries and how to use them to query data from the database.

Section 9. Set Operators

This section describes how to use the set operators including union, intersect, and except to combine two or more result sets from subselects.

• UNION – combine two or more result sets of subselect into a single result set and remove duplicate rows. The UNION ALL  operator retains the duplicate rows in the result.
• INTERSECT – combine two or more result sets and return the rows that exist in all the result sets.
• EXCEPT – return the result of the first subselect minus any matching rows from the result set of the second subselect.

Section 10. Common Table Expression (CTE)

• CTE – use common table expressions to simplify complex queries.

Section 11. Modifying Data

In this section, you will learn how to insert, update, and delete data from a table.

• INSERT – insert a row into a table
• INSERT multiple rows – insert multiple rows into a table.
• INSERT INTO SELECT – insert data returned by a SELECT statement into a table.
• UPDATE – modify the existing data in a table.
• DELETE – delete one or more rows from a table.

Section 12. Data Definition Language

This section shows you how to manage the most important database objects including databases and tables.

• CREATE TABLE – show you how to create a new table in the database.
• Identity column – learn how to define an identity column for a table.
• ADD COLUMN – describe how to add one or more columns to a table
• ALTER COLUMN – show you how to modify definitions of existing columns in a table.
• DROP COLUMN – walk you through the process of dropping one or more columns in a table.
• DROP TABLE – show you how to delete table objects permanently from the database.
• TRUNCATE TABLE – give you a more efficient way to delete all data from a big table.
• Rename a table –  learn how to change the name of an existing table to the new one.

Section 13. Db2 Data Types

• Integers – learn various integer types in Db2 including BIGINT, INT, and SMALLINT.
• Decimal – introduce you to decimal type and show you how to use it to store decimal numbers in tables.
• CHAR – learn how to store fixed-length character strings in the database.
• VARCHAR – store varying character strings in the database.
• DATE – discuss the DATE data type and how to store the dates in the database.
• TIME – learn about the TIME data type and how to work with time data.
• TIMESTAMP – introduce you to timestamp data type and show you how to store the timestamp in the database.

Section 14. Constraints

• Primary key  – learn about the primary key and how to use the primary key constraint to define the primary key for a table.
• Foreign key – enforce referential integrity between tables by using foreign key constraints.
• DEFAULT – specify a default value for a column to be inserted when the application doesn’t supply the value.
• NOT NULL – prevent NULL from inserting or update to a column.
• UNIQUE – enforce the data in a column or group of columns is unique in all rows of a table.
• CHECK – place a condition on the values which are being inserted or updated into a table.

Section 15. Useful Expressions & Functions

• CASE – use simple and searched CASE expressions to add if-else logic to queries.
• COALESCE – learn how to handle NULL values effectively.
• CAST – convert a value of one type to another.