An Introduction to SQL

Table of contents:

As its name implies, SQL is used to express a database query. SQL has facilities for defining which fields should be returned from the query, if and how the query should be filtered and sorted, and so on. Although SQL is an industry standard language, various specific versions and editions of the standard are implemented by SQL-based databases. Crystal Reports does not use just a single syntax, but rather is robust enough to handle most major SQL language derivations. The rest of this appendix walks you through the SQL Language and points out specific areas that are of concern to Crystal Reports. Although it doesn focus on a specific version of SQL, it does point out differences where appropriate.

The SELECT Statement

Even though the name implies that SQL is only about querying databases, most implementations also enable you to insert, delete, and update records inside the database. Each of these distinct actions has its own command: SELECT (query), INSERT, UPDATE, and DELETE. Although SQL commands allow any valid SQL statement that returns records to be used, SELECT statements are generally the only statements to be used. However, there are situations in which other statements can be used in addition to a SELECT statement. One example of this is running an INSERT statement to create a record to log the fact that the report is being run. This section focuses on describing the SELECT statement from SQL.

A basic SELECT statement has the following syntax:

SELECT field-list

FROM table-list

SELECT statements always begin with the word SELECT. The general convention is to capitalize all SQL keywords used in the query to make it clear which is SQL and which is a table or field name. The list of fields to include is a comma-separated list of field names, such as "Name, Age, Gender." To include all fields in the specified table(s), use an * instead of listing individual field names. If the name of a field contains a space, the field name should be surrounded by a quote character (field name). Various SQL implementations allow different quotes, but most of them support (single quote) as a quote character. The list of tables follows the same convention: They are separated by commas and are optionally enclosed in a quote. Any extra whitespace or carriage returns are usually ignored by the database. The following is a sample SQL statement using the Xtreme Sample Database:

SELECT `Customer Name`, City, Country

FROM Customer

Notice that quotes were only used for the Customer Name field because it was the only field with a space in the name. However, as a general convention, quote all your field and table names to be safe. The same statement could be written like this:

SELECT Customer Name, City, Country

FROM Customer

Depending on the type of database, table names can also be prefixed with the associated database name, for example, MyDatabase.MyTab

Part I. Crystal Reports Design

Creating and Designing Basic Reports

Selecting and Grouping Data

Filtering, Sorting, and Summarizing Data

Understanding and Implementing Formulas

Implementing Parameters for Dynamic Reporting

Part II. Formatting Crystal Reports

Fundamentals of Report Formatting

Working with Report Sections

Visualizing Your Data with Charts and Maps

Custom Formatting Techniques

Part III. Advanced Crystal Reports Design

Using Cross-Tabs for Summarized Reporting

Using Record Selections and Alerts for Interactive Reporting

Using Subreports and Multi-Pass Reporting

Using Formulas and Custom Functions

Designing Effective Report Templates

Additional Data Sources for Crystal Reports

Multidimensional Reporting Against OLAP Data with Crystal Reports

Part IV. Enterprise Report Design Analytic, Web-based, and Excel Report Design

Introduction to Crystal Repository

Crystal Reports Semantic Layer Business Views

Creating Crystal Analysis Reports

Advanced Crystal Analysis Report Design

Ad-Hoc Application and Excel Plug-in for Ad-Hoc and Analytic Reporting

Part V. Web Report Distribution Using Crystal Enterprise

Introduction to Crystal Enterprise

Using Crystal Enterprise with Web Desktop

Crystal Enterprise Architecture

Planning Considerations When Deploying Crystal Enterprise

Deploying Crystal Enterprise in a Complex Network Environment

Administering and Configuring Crystal Enterprise

Part VI. Customized Report Distribution Using Crystal Reports Components

Java Reporting Components

Crystal Reports .NET Components

COM Reporting Components

Part VII. Customized Report Distribution Using Crystal Enterprise Embedded Edition

Introduction to Crystal Enterprise Embedded Edition

Crystal Enterprise Viewing Reports

Crystal Enterprise Embedded Report Modification and Creation

Part VIII. Customized Report Distribution Using Crystal Enterprise Professional

Introduction to the Crystal Enterprise Professional Object Model

Creating Enterprise Reports Applications with Crystal Enterprise Part I

Creating Enterprise Reporting Applications with Crystal Enterprise Part II

Appendix A. Using Sql Queries In Crystal Reports

Creating Enterprise Reporting Applications with Crystal Enterprise Part II

Special Edition Using Crystal Reports 10
Special Edition Using Crystal Reports 10
ISBN: 0789731134
EAN: 2147483647
Year: 2003
Pages: 341 © 2008-2020.
If you may any questions please contact us: