Chapter 10: Using SQL to Perform Common Database Operations

Overview

As pointed out in the preceding chapter, SQL is the common denominator among all of the Advantage data access mechanisms. No matter how you connect to the Advantage Database Server (ADS), you can access your data and data dictionaries using the structured query language.

This chapter is designed to provide you with a large collection of examples of Advantage SQL statements. In selecting the examples presented here, we are driven by two concerns. First, we have chosen SQL statements that we feel are essential for common database-related tasks. Second, we want to provide you with a wide variety of examples that convey the breadth and power of Advantage SQL, given the limited space of a single chapter.

What we do not attempt to do with this chapter is provide the detailed syntax of the SQL statements that we cover. If we tried to do that, this chapter would be one big syntax statement. For a detailed description of the syntax of Advantage SQL, see the Advantage help.

The topics discussed in this chapter are primarily focused on tables, indexes, and data. Specifically, we do not discuss the larger issue of creating and managing data dictionaries, users, and groups within this chapter. Those topics are covered in Chapter 11, where you will also learn how to access and use metadata in your applications.

If you are already a seasoned SQL programmer, you can scan this chapter for anything that looks new or interesting. If you are new to SQL programming, we recommend that you try using each of these statements against the sample database included on this book’s CD-ROM using the Native SQL Utility (discussed in Chapter 9).

However, before you begin working with these queries, we suggest that you make a duplicate of the data tables and your data dictionary, and use the Alias Configuration Utility to provide an alternative alias for this copy (see the ADS help for specific information on using the Alias Configuration Utility). You can then use this copy of the data dictionary and the tables without worrying about accidentally deleting or modifying data. Then, you can be creative with the copy of the database, modifying the presented queries to better understand what options you have when writing your own SQL. We are assuming that you are connecting to ADS remotely using the administrator account (ADSSYS).

ON THE CD

If you’d rather not manually type in the queries in this chapter, you can find all of them listed in this chapter in the file named ch10queries.sql on this book’s CD-ROM. Each of the queries in this file has been commented out. To use a query in this file, copy the query text to the clipboard and insert it into in the Native SQL Utility or a querying tool of your choice, and then remove the comments before executing the query. Alternatively, open the ch10queries.sql file in the in the Native SQL Utility, remove comments for the desired query, and then execute it.