Lesson 28: Advanced SQL Queries in VB6

Master advanced SQL techniques including WHERE clauses, pattern matching, and sorting for powerful database queries

Previous Lesson Next Lesson

Key Takeaway

The WHERE keyword enables precise filtering of database records based on specific criteria, while ORDER BY provides control over result sorting - essential tools for efficient data retrieval.

Welcome to Lesson 28 of our Visual Basic 6 Tutorial! Building on the SQL fundamentals from Lesson 27, this lesson dives deeper into advanced SQL query techniques. You'll learn how to filter data using the WHERE clause, perform pattern matching with LIKE, and sort results with ORDER BY.

28.1 The WHERE Clause

The WHERE clause is one of SQL's most powerful features, allowing you to filter records based on specific conditions. The basic syntax is:

SQLSyntax.frm
SELECT field1, field2 FROM TableName
WHERE condition

28.1.1 Comparison Operators

SQL supports various operators for creating conditions:

Operator Description Example
= Equal to WHERE Year = 2008
<> Not equal to WHERE Author <> 'Liew Voon Kiong'
> Greater than WHERE Price > 50
< Less than WHERE Price < 30
>= Greater than or equal WHERE Year >= 2010
<= Less than or equal WHERE Price <= 80

28.2 Building the Query Interface

We'll create an advanced query interface using the book database from Lesson 27. The interface includes a textbox for entering SQL queries and a DataGrid for displaying results.

Advanced Query Interface
Figure 28.1: Query Interface Design

28.2.1 Implementation Steps

1Setup ADO Control

Set Command Type to 1-adCmdText and Command Text to SELECT * FROM book

2Add Query Textbox

Place a textbox above the DataGrid for entering SQL queries

3Create Query Button

Add a button to execute the SQL query from the textbox

QueryButton.frm
Private Sub cmdQuery_Click()
    Adodc1.RecordSource = txtSQL.Text
    Adodc1.Refresh
    Adodc1.Caption = Adodc1.RecordSource
End Sub

28.3 Advanced Query Examples

Using our query interface, we can execute various advanced SQL queries:

28.3.1 Query by Author

Retrieve books by a specific author:

AuthorQuery.sql
SELECT Title, Author FROM book
WHERE Author='Liew Voon Kiong'
Author Query Results
Figure 28.2: Author Query Results

28.3.2 Query by Year

Find books published after a specific year:

YearQuery.sql
SELECT * FROM book
WHERE Year > 2005
Year Query Results
Figure 28.3: Books Published After 2005

28.3.3 Pattern Matching with LIKE

Use the LIKE operator with wildcards for flexible searching:

PatternQuery.sql
-- Authors starting with 'J'
SELECT * FROM book
WHERE Author LIKE 'J%'
Pattern Matching Results
Figure 28.4: Authors Starting with 'J'

28.4 Sorting with ORDER BY

The ORDER BY clause allows you to sort results in ascending (ASC) or descending (DESC) order:

28.4.1 Sorting Authors Ascending

Sort authors alphabetically:

SortAuthor.sql
SELECT Title, Author FROM book
ORDER BY Author ASC
Author Sorting Results
Figure 28.5: Authors Sorted Ascending

28.4.2 Sorting Prices Descending

Sort books from highest to lowest price:

SortPrice.sql
SELECT Title, Price FROM book
ORDER BY Price DESC
Price Sorting Results
Figure 28.6: Prices Sorted Descending

Lesson Summary

In this lesson, you've mastered advanced SQL query techniques in VB6:

WHERE Clause

Filter records using comparison operators

Pattern Matching

Use LIKE with wildcards for flexible searching

Result Sorting

Organize data with ORDER BY (ASC/DESC)

Query Interface

Build a flexible SQL query executor

Important Note

Combining WHERE filters with ORDER BY sorting creates powerful queries that can extract and organize exactly the data you need from large databases.

Practice Exercises

Strengthen your advanced SQL skills with these exercises:

Exercise 1: Combined Conditions

Create a query that shows books priced between $20 and $50

Exercise 2: Multiple Sorts

Sort books by publisher ascending, then by price descending

Exercise 3: Advanced Pattern Matching

Find titles containing "Visual" but not "Basic"

Exercise 4: Year Range

Show books published between 2000 and 2010, sorted by year

Exercise 5: Case-Insensitive Search

Implement a case-insensitive author search

Next Lesson

Continue your VB6 journey with Lesson 29: Building an E-library Application.

Related Resources

SQL Basics

Review SQL fundamentals from Lesson 27

Previous Lesson

E-library App

Apply SQL skills in a complete application

Next Lesson

ADO Control

Database connection fundamentals

View Lesson

SQL Cheat Sheet

Quick reference for SQL syntax

View Resource
Visual Basic 6 Made Easy

Visual Basic 6 Made Easy

by Dr. Liew Voon Kiong

The comprehensive guide to mastering VB6 development, including in-depth coverage of database programming and SQL integration.

What You'll Learn:

  • Advanced SQL query techniques
  • Database application development
  • Practical examples and projects
  • Data filtering and sorting
  • Professional UI development
  • Query optimization strategies
Get on Amazon
Visual Basic 2022 Made Easy

Visual Basic 2022 Made Easy

by Dr. Liew Voon Kiong

The modern guide to VB.NET programming with Visual Studio 2022. Master database integration and modern development techniques.

What You'll Learn:

  • Modern VB.NET techniques
  • SQL Server integration
  • Entity Framework
  • Advanced query optimization
  • LINQ for data querying
  • Professional application design
Get on Amazon