![]() |
![]() |
|
![]() |
Local SQL Part 3 SELECT .. FROM © 2001 Lawrence G. DiGiovanni Digico, Inc. www.digicoinc.com Preface Local SQL is a very powerful yet commonly underutilized tool that is provided with Paradox (as part of the Borland Database Engine). It exposes the power and flexibility of the SQL sublanguage to both Paradox developers as well as interactive end-users. This is the third in a planned series of articles on using Local SQL with Paradox/BDE applications. This article describes how to use the SELECT and FROM clauses of the SQL select statement. Introduction As we discussed in the previous article, the SQL SELECT statement consists of a number of required and optional clauses. The only two required clauses are the SELECT clause and the FROM clause. The FROM clause tells SQL which table or tables to query, and the SELECT clause tells SQL which columns will be in the result set (i.e., ANSWER table). As we’ll later see, the SQL language also provides the ability to create tables and populate them with data. We will approach that subject in a future article, but for now, we will work on a predefined database, which will be used as an example in this and future articles. The database we will use is the DBDEMOS database, which contains the Biolife example database. This database ships and typically installs with the Borland Database Engine. This sample database describes a sales order-entry system. The CUSTOMER table lists the customer names and addresses. In the following examples, you are encouraged to paste these queries directly into a SQL query in Paradox or the Database Desktop. Example: Single Table SELECT Consider the following SQL SELECT statement: SELECT company, city, state FROM customerThis query creates a three-column result set (an ANSWER table) consisting of all rows of CUST. The columns of the ANSWER table have the same name and field type as they do in the CUST table. The answer table is equivalent to one produced by the following QBE query: customer | Company | City | State | | CheckPlus | CheckPlus | CheckPlus |Why CheckPlus and not Check? SQL SELECT statements don’t eliminate duplicates by default. For example, let’s look at the following SQL SELECT statement, which is the same as the earlier one, but we’ve left off company from the select-list: SELECT city, state FROM customerWhen this query is executed, Christiansted, St. Croix shows up twice. That’s because there are two rows in CUST with Christiansted, St. Croix, and SQL SELECT statements don’t, by default, eliminate duplicates. If you want to eliminate duplicates, use the DISTINCT predicate: SELECT DISTINCT company, city, state FROM customerNow there is only one row with Christiansted, St. Croix in the result set. Calculated Fields In addition to simple column names, we can also include more complicated expressions in our select list, including arithmetic operations and string concatenation involving columns. This same general capability is available in QBE using the CALC keyword. For example, our PARTS table shows both cost and list price for each item sold. In order to find the difference for each item, we could use a query: SELECT Description, ListPrice - Cost FROM partsOr, if we wanted the percent markup for each item: SELECT Description, (ListPrice - Cost)/Cost FROM partsNote we grouped our operations using parentheses, since standard arithmetic rules apply for order of operation. Also notice that in our ANSWER table resulting from the second example, the column is expressed as a currency value. This is because two currency values were used in the expression. We’ll see how to convert this "on the fly" in a later article. As noted above, we can also perform string concatenation. Recall our first example query on the customer table. We could essentially format the result as follows: SELECT Company, City + ', ' + State FROM customerWhich would produce a two-column result set consisting of Company and an expression of the form City, State. The above example uses the plus (+) symbol as the concatenation operator, which is valid. It is clearer to use the concatenation operator (||) (two vertical bars) for clarity sake, to distinguish between concatenation and arithmetical addition as shown below. SELECT Company, City || ', ' || State FROM customer Renaming Result Set Columns on the Fly: AS In each of the above examples, the column created by our arithmetic or string concatenation was named for the expression. This is unwieldy at best. Like QBE, SQL allows us to rename our columns on the fly. Consider the following modification to the previous query: SELECT Company, City || ', ' || State AS Location FROM customerThe addition of the AS Location causes our query to name this column LOCATION, which is much easier to reference in code or subsequent queries. Functions In addition to arithmetical expressions, Local SQL provides a number of functions that may also be included in the select-list. These functions fall into two categories: Aggregate functions and simple (non-aggregate) functions. Functions: Aggregate Functions Aggregate functions (AVG, COUNT, MAX, MIN, SUM) operate on multiple rows and perform their calculation across those rows. Exactly which rows are included is a function of the WHERE, GROUP BY, and HAVING clauses, and we’ll detail how they affect aggregate functions later. For now, we’ll show how they operate on an otherwise unqualified query, in which all rows are used to calculate. For instance, suppose we want to know how many customers we have. We could simply execute the following query: SELECT COUNT(Company) FROM customerThis query results in a single row, single column ANSWER table containing the total number of rows in our customer table. Aggregate functions can also operate on column expressions. For instance, the total value of our on-hand inventory can be found in the PARTS table as the sum of Quantity on Hand times ListPrice. We can express that as the following SQL query: SELECT SUM(onHand * ListPrice) FROM partsThis query also results in a single row, single column ANSWER table, this time showing us the total value of our on-hand inventory. We’ll get back to aggregate functions in detail in a later article. Functions: Simple Functions Simple Functions (scalar functions) are generally included in most SQL dialects as an extension to the SQL Standard. They operate on column values in a single row and produce a single value which is included in the result set. Local SQL has several such functions, including:
Note that these functions are not available with QBE. Suppose we need to generate a list of all company names, but instead of mixed caps, we need them in all upper case. We can generate a result set containing a capitalized list of all company names using the following query: SELECT Upper(Company) Company FROM CustomerOur ANSWER table will have a single column containing the Company name for each company, all in uppercase characters. In the above example, we explicitly named the new column ‘COMPANY’. These functions are described in detail in the LocalSQL.HLP file. They will be explored in detail in a later article. Summary The SQL SELECT statement creates a result set (i.e., an ANSWER table) consisting of specific rows and columns of a table. The result set can consist of simple columns derived from the queried table, calculated expressions or the result of a SQL function. There are two types of SQL functions, aggregate functions, which operate across multiple rows, and simple functions, which operate on single rows. Result set columns may have different names assigned to them during the SQL query using the AS operator in the query. Local SQL Part 4 Discussion of this article |
![]() Feedback | Paradox Day | Who Uses Paradox | I Use Paradox | Downloads ![]() |
|
![]() The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation. Paradox is a registered trademark of Corel Corporation. ![]() |
|
![]() Modified: 15 May 2003 Terms of Use / Legal Disclaimer ![]() |
![]() Copyright © 2001- 2003 Paradox Community. All rights reserved. Company and product names are trademarks or registered trademarks of their respective companies. Authors hold the copyrights to their own works. Please contact the author of any article for details. ![]() |
![]() |
|