More SQL Keywords

<Previous Lesson><<Home>>< Next Lesson>

In the previous chapter, we have learned to use the basic SQL keywords SELECT and FROM to manipulate database in Visual Basic 6 environment. In this lesson, you will learn 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 Like.

Using the database books.mdb created in the previous chapter, we will show you a few examples. First of all,  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 put it on top of the DataGrid control, this will be the place where the user can enter SQL query text. Insert one command button and change the caption to Query. The design interface is shown below:

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 below:

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.

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. 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 below:

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

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

Share on Google Plus