Create an SQL query >
< Previous | Next >

Creating a SELECT statement with tables

In this exercise, use the SQL builder to create a SELECT statement for the VIDEOS database, add the necessary database tables to the statement, and then assign aliases to each of the tables.

Creating a SELECT statement

You can add SELECT, INSERT, DELETE, UPDATE, WITH, and FULLSELECT statements by using a command on the pop-up menu of the SQL Scripts folder in the Data Definition view. You will now create a SELECT statement for the VIDEOS database.

To create a SELECT statement:

  1. In the Data Project Explorer view, right-click the SQL Scripts folder in the VideoStoreProject project and then click New > Select Statement.
  2. On the Specify a Project page of the New SQL Statement wizard, select VideoStoreProject in the Project list, and then click Next.
  3. On the Statement Type page of the wizard, type customerRentals for the statement name, and then click Finish.
The SELECT statement template is created and opens in the SQL builder, as shown here:

SELECT statement template in SQL builder

The SQL builder has three panes:

SQL Source
The top pane shows the SQL source for your statement as it is being built.
Tables
The middle pane shows the tables and table columns that are added to the statement.
Design
The bottom pane is used to provide details for statement clauses. This pane changes depending on the statement type being edited.

You will use all of these panes throughout this tutorial.

In addition to the Data Project Explorer view, you can use the following three views with the SQL builder:

Outline
Shows the statement that you are currently working on. For more complex statements such as WITH and FULLSELECT that can include, for example, subselects and common table expressions, the Outline view shows the structure of the statement.
Database Explorer
Each defined data connection is shown in this view. You can use the Tables folder under a connection as a source for adding tables to an SQL statement in the SQL builder.
Data Output
Shows the messages, parameters, and results that are related to running your SQL statement.

The SELECT statement that you created is already open in the SQL builder, so you can continue to construct it. You are creating only a SELECT statement in this tutorial, but you can also use the SQL Source pane, the Tables pane, and the Design pane of the SQL builder to create INSERT, DELETE, UPDATE, FULLSELECT, and WITH SQL statements.

Adding tables to the statement

You will now add three tables to the SELECT statement for the customerRentals query. You are creating a query that lets the video store employees look up the video titles that a particular customer has rented and the day of the week that the videos are due. The query needs to include the CUSTOMERS, RENTALS, and VIDEOS tables.

The CUSTOMERS table contains the name and ID (identification number) of each person who rents from the video store. The data in the VIDEOS table includes the ID and title of each video. The data in the RENTALS table includes the following information for each video that is currently rented: the customer ID, video ID, and the date that the video is due.

To add tables to the statement:

  1. In the Database Explorer view, expand the XMLTEST schema in the VIDEOS database tree, and then expand the Tables folder. You will see the tables for the database.

    Tables for the VIDEOS database

  2. Drag the CUSTOMERS table from the Database Explorer view to the Tables pane in the SQL builder. The CUSTOMERS table is shown in the Tables pane, and the source code in the SQL Source pane shows the addition of the CUSTOMERS table in the SELECT statement.
  3. In the SQL Source pane of the SQL builder, type ,xmltest.rentals (including the leading comma) after XMLTEST.CUSTOMERS, as shown in the following image, and then click anywhere outside the SQL Source pane.

    SQL builder - with typed text ',xmltest.rentals' shown in the SQL Source pane

    After the changed statement is parsed and determined to be valid, the RENTALS table is added in the Tables pane, and the code in the SQL SOURCE pane is formatted.

  4. Right-click in the Tables pane, and then click Add Table on the pop-up menu.
  5. In the Table name list, expand the XMLTEST schema, select VIDEOS, and then click OK. The VIDEOS table is added in the Tables pane, and the source code in the SQL Source pane shows the addition of the VIDEOS table in the SELECT statement.
All three tables are now shown in the Tables pane. Note the corresponding changes to the source code in the SQL Source pane.

SQL builder - added tables shown in the Tables pane, with the corresponding code for the tables in the SQL Source pane

Adding table aliases

Next, you will 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.

Table aliases can be added when you add the table to the statement, or after the table is added, by using the table pop-up menu in the Tables pane. You can also use the table pop-up menu to delete tables or create joins between tables.

The aliases for the CUSTOMERS, RENTALS, and VIDEOS tables will be C, R, and V, respectively.

To add table aliases:

  1. In the Tables pane, right-click the header in the CUSTOMERS table, and then click Update Alias on the pop-up menu.
  2. In the Change Table Alias window, type C as the alias for the table, and then click OK. In the Tables pane, the alias is shown in the header for the CUSTOMERS table. In the SQL Source pane, the alias is represented by the AS C code for the CUSTOMERS table.
  3. Repeat steps 1 and 2 to add aliases for the RENTALS (R) and VIDEOS (V) tables.
< Previous | Next >