Read an Excerpt
Chapter 8: Getting the Data You Want From the Database
In this chapter we'll take our first steps into the world of SQL. We'll see that SQL is one of the key bits of grammar that we use with the languages that allow us to communicate with relational databases. The great thing about learning about SQL here is that this knowledge is plicable to whichever relational database access technology we use - DAO, ADO, SQL Server, whatever. As an industry standard, SQL is an important tool for us to learn about and use. ere's how we'll go about discovering SQL:
- The background to SQL
- Getting at the data we need - SQL syntax and keywords
- Building a SQL query-tester
- Using SQL to retrieve specific data from databases
- Retrieving only data that matches specified criteria - filtering the recordset
- Grouping the results of queries
- Retrieving data from multiple tables - joining data
- Generating dynamic SQL based on choices selected on the user interface
We'll start by looking at the background to the development of SQL.
SQL - What's the Story?
Where do we start? Well, the beginning is usually a good place. This book is about Visual Basic 6.0 and databases. A database is a powerful tool, but if we can't retrieve the data we need, it's worse than useless. We would quickly become frustrated if we couldn't easily and quickly retrieve all of the valuable information stored inside.
Luckily there is a standardized way of asking our database to give us the specific information we need: Structured Query Language, or SQL. SQL is really a database programming language. It is closely associated with the invention of the relational database by Dr. E. F. Codd in the footloose and care-free days of the early 1970s.
Over the years, SQL has evolved into a widely used standard. To ensure consistency of the language, it is defined by an ANSI (American National Standards Institute) standard. However, even with the standardization, there are still minor variations of the language in different database implementations and Jet is no exception. The variations are minor and generally a superset of (or addition to) the ANSI SQL. So when we vary from the standard, this will be pointed out to you. You may or may not want to use the Jet specific variations. Although they are powerful, if you include them in a query requesting information from another database management system - Oracle, for example - they won't work. However, if you are using Jet with Access, then by all means take advantage of the extra power and ease of use that these database-specific implementations provide. As I said - they are powerful and easy to use.
If want to learn more about the differences between Jet SQL and the versions used by largescale enterprise databases, in particular SQL Server, then please refer to Appendix A One Standard, Many Flavors.
Is It a Language, Is It a Technology? No, It's SQL!
OK, even though SQL is called a language, that term can be just a bit misleading. You see SQL is not a complete programming language like Visual Basic. For example, it does not have an If Then Else construct for testing conditions. Nor does it have Do or For statements.
In reality, SQL is more like a sub-language consisting of about 30 or so specialized statements for database management tasks. These statements are then embedded into another real programming language, like Visual Basic. SQL is not a stand-alone product; it can't be used by itself. You can't go to your favorite computer store and say, "I want to purchase SQL". Instead, SQL is an important part of a database management system - a language for communicating with jet, or most database engines for that matter. What you learn in this chapter will help you communicate with just about any database on the market.
We have actually been using simple SQL statements when we programmed the data control. For example, the "SELECT * FROM TITLES" string is really an SQL statement. The Microsoft jet engine will accept SQL statements like the one above. Jet takes the SQL query - which is just text - and parses (breaks apart), analyzes and optimizes it. Then the action requested by the query is carried out and a recordset is returned.
SQL - Your Co-operative and Flexible Co-worker
With SQL, we can not only retrieve records, but perform other tasks as well, such as updating or deleting records in a database directly, on one or more tables. For example, say we want to update the price of all green widgets by 10%. A simple SQL statement can update these records for us in one fell swoop. Alternatively, we might wish to permit the user to delete all records that have a date less than, say, January 1, 1998, for a clean-up process. A simple one-line SQL statement will take care of that for us as well. So you see, SQL is a two-edged sword. It will our bidding, but it expects us to know what we are doing. We have to be on our toes always because it is so easy to delete hundreds or thousands of records with a single line of code. You may recall that in our class module, we always prompt the user when deleting a single record. Well, using SQL, we can delete all records with a single command! So we must use this power with caution. However, we will see that SQL can permit us to do a lot of work with a single command.
Another neat feature is that we can also create dynamic SQL queries. These are SQL queries that we actually build as the program is running. For example, you might have a form with several choices for the user. Once the choices are made, we can dynamically embed the variables that store these choices in a SQL statement that will retrieve only the records the user needs - all on the fly This approach is incredibly useful when we want the user to select parameters for a report or graph. The user might want to see all widgets purchased between March and April of ,1998. We can have a pre-structured SQL query that retrieves widgets; the only thing it needs us to do is prompt the user to insert a date range.
Getting the Data We Need
Essentially, SQL has a simple basic grammar. To get a database engine to process some SQL, we construct a SQL statement. In SQL terms, a statement is the whole string of SQL keywords and supplementary information (such as table and field names) that we ask the database engine to process for us. The statement tells the database engine (jet, in our case), what data we want to access, and what we want to do with it. A statement is broken up into clauses, each of which contains a specific SQL keyword and the additional information that belongs with that keyword. In this chapter we will build SQL statements using the clauses as building blocks.
Some SQL statements just read (query) the database, but others can update or delete records. In this chapter, although we will perform updating and deleting, we will concentrate on queries t retrieve data. These data retrieval queries are embodied in SELECT statements.
Introducing the SELECT Statement
When we look at SQL from 50,000 feet, we notice that there are just six fundamental parts (four of them optional) of a SQL SELECT statement.
We use these six basic SELECT statement components and add detailed information to them to fill out the specifics of the query The combination of SQL keywords and the information that we provide tells SQL (and thereby Jet) which fields from which tables we want to retrieve, and in what form we want to see them....