Lesson 28 : More Advanced SQL Queries

In the previous lesson, we have learned how to use the basic SQL keywords SELECT and FROM to manipulate database in Visual Basic 6 environment. In this lesson, you will learn how to use more SQL keywords. One of the more important SQL keywords is WHERE. This keyword allows the user to search for data that fulfill certain criteria.

The Syntax is as follows:

SELECT fieldname1,fieldname2,.....,fieldnameN  FROM  TableName WHERE  Criteria

The criteria can be specified using operators such as =, >,<, <=, >=, <> and more. Using the database books.mdb created in the previous chapter, we shall show you a few examples. First ,start a new project and insert a DataGrid control and an ADO control into the form. At the ADODC property pages dialog box, click on the Recordsource tab and select 1-adCmdText  under command type and under Command Text(SQL) key in SELECT * FROM book. Next, insert one textbox and place it on top of the DataGrid control for the user can enter SQL query text. Insert one command button and change the caption to Query.

The design interface is shown below in Figure 28.1 below:

Figure 28.1: The Design Interface

Example 28.1: Query based on Author

Run the program and key in the following SQL query statement

SELECT Title, Author FROM book WHERE Author='Liew Voon Kiong'

Where you click on the query button, the DataGrid will display the author name Liew Voon Kiong. as shown in Figure 28.2 below:

Figure 28.2

Example 28.2:Query based on year

Run the program and key in the following SQL query statement:

SELECT * FROM book WHERE Year>2005

When you click on the query button, the DataGrid will display all the books that were published after the year 2005, as shown in Figure 28.3 below.

Figure 28.3

You can also try following queries:

• SELECT * FROM book WHERE Price<=80
• SELECT * FROM book WHERE Year=2008
• SELECT * FROM book WHERE Author<>'Liew Voon Kiong'

You may also search for data that contain certain characters by pattern matching. It involves using the Like operator and the % symbol(also known as wildcard character). For example, if you want to search for a author name that begins with alphabet J, you can use the following query statement

SELECT * FROM book WHERE Author Like 'J%'>

Where you click on the query command button, the records where authors' name start with the alphabet J will be displayed, as shown in Figure 28.4 below:

Figure 28.4

Next, if you wish to rank order the data, either in ascending or descending order, you can use the ORDER By , ASC (for ascending) and DESC(Descending) SQL keywords.

The general structures are

  SELECT fieldname1, fieldname2.....FROM table ORDER BY fieldname ASC

  SELECT fieldname1, fieldname2.....FROM table ORDER BY fieldname DESC

Example 28.3:

The following query statement will rank the records according to Author in ascending order.

SELECT Title, Author FROM book ORDER BY Author  ASC.

The runtime interafce is as shown in Figure 28.5 below:

Figure 28.5

Example 28.4

The following query statement will rank the records according to price in descending order.

SELECT Title, Price  FROM book ORDER BY Price  DESC.

The runtime interafce is as shown in Figure 28.6 below:

Figure 28.6