Create a DB2 SQL query
This tutorial teaches you how to create and run a DB2® SQL query by using the SQL Query Builder. It helps you build a SELECT statement that includes a table alias, a column alias, a database function expression, a CASE expression, and a grouping clause.
The tutorial uses a DB2 database
that is called VIDEOS. The VIDEOS database holds data that is related
to video store customers, inventory, video rentals, and employees.
The tutorial helps you create a query that allows store employees
to look up a particular customer's rented video titles and the
due dates. The query includes the following elements:
- Two joins
- A WHERE and GROUP BY clause
- A CASE expression
- A function
- A host variable to substitute the name of the customer at run time
Learning objectives
This tutorial teaches you how to do the following tasks:- Create a SELECT statement
- Add tables to the statement
- Add table aliases
- Specify result columns
- Add a CASE expression to the result column set
- Add a column alias
- Join tables
- Create a query condition
- Add a GROUP BY clause
- Run the DB2 SQL query and pass in a host variable for the query
This tutorial takes approximately 30 minutes to finish.
If you explore other concepts that are related to this tutorial, it
can take longer to complete.
Skill level
BeginnerAudience
Database application developers- Create and connect to the VIDEOS database
In this exercise, you create the VIDEOS database, create a data development project, and connect to the database. - Create a SELECT statement
You can use the SQL Query Builder to create SELECT, INSERT, UPDATE, DELETE, FULLSELECT, or WITH statements. For this tutorial, create a SELECT statement for the VIDEOS database. - Add tables to the statement
To continue creating thecustomerRentalsquery, add three tables to the SELECT statement. This query takes as input a particular customer and returns the customer's rented video titles and their due date. To accomplish this goal, the query must include the CUSTOMERS, RENTALS, and VIDEOS tables. - Add table aliases
Create an alias for each of the tables in the SELECT statement. An alias is an indirect method of referencing a table so that an SQL statement can be independent of the qualified name of that table. If the table name changes, only the alias definition must be changed. - Specify the result columns
Specify the columns to show in the query result. The query must return the customer name, the title of each video that the customer rented, and the day of the week that each video is due. To meet these requirements, include a CASE expression and assign it an alias for the third result column. - Add joins, a query condition, and a GROUP BY clause
Because the query returns the due date of rented videos for a particular customer, it must restrict results to customers who are currently renting videos and to videos that are currently rented. Results can be further restricted to a specific customer whose name you specify when the query is run. Finally, for readability, you can modify the query results by the day of the week the video is due, the video title, and the customer surname. - Run the DB2 SQL query
Before you incorporate the SQL query into the video store application, test the query to ensure that it returns correct results. You can test the query by using the SQL Query Builder. - Summary
This tutorial taught you how to create a simple DB2 SQL query by using the SQL Query Builder.