Subsections

• Overview
• Invocation
• Select
• Create
  • View
  • Index
• Exit

SQL Interface

This chapter describes how to use the structured query language to create samples and subsamples. This is not intended as a guide or introduction to SQL, but rather to explain how to use SQL from BROWSE. There is a separate and more detailed SQL User's Guide available. There are many standard texts which describe the SQL syntax and readers are referred to these. The SQL can only be used to query the database tables, it cannot be used to display or analyze products.

Overview

SQL, the Structured Query Language, is the ANSI standard language for manipulating and retrieving data from databases. Many commercial database management systems support SQL and BROWSE also allows SQL commands to be used for many tasks. Within the standard there is room for implementation-dependent differences so that even if you are familiar with SQL from elsewhere, it is still necessary to read this chapter to find out the local features.

Invocation

SQL commands can be executed from BROWSE or from the HEASARC> prompt by typing the command SQL and then entering the SQL commands in response to the SQL> prompt. Commands are entered, however long they may be, on a single command line with the use of as many continuation characters (-) immediately before carriage return as necessary. When a continuation has been given the user receives an _SQL> prompt from the system indicating that more input is expected. A normal carriage return shows that the SQL command has finished. SQL syntax is very strict and unforgiving: all SQL keywords are typed in full, no abbreviations are allowed, and spaces are expected as separators between all components of the command. The continuation character is also interpreted as a space. Because SQL interprets spaces in this way, any spaces in database parameter names should be replaced by underscores to avoid confusion. Thus, although the proper ME parameter is COUNT RATE, the name COUNT_RATE should be used in SQL commands if the name is given in full - database parameter names is the one part of EXOSAT SQL commands where unique abbreviations or number aliases are allowed. You can execute a single SQL command and return directly to BROWSE by giving the entire command prefaced by SQL.

Select

The most widely used and most important SQL command is SELECT. It is the central SQL command that retrieves the specified items from the specified table subject to the WHERE clause condition that is a logical quantity evaluated for all the table rows. It is usually made up of a combination of conditions applied to the values of the parameters in each row using brackets where necessary to remove ambiguities. The syntax is as follows:

SQL> SELECT items FROM table [WHERE condition] [ORDER BY parameter]

The items specifies the parameters to be listed on the screen. The star (*) specifies all parameters in the table of interest. The FROM table specifies the database or sample name from which the selection is to be made, for example, ME, SNR_LE. A table name must be either a database name itself or a sample name with _database at the end. The WHERE condition specifies parameter range(s). The legal conditions are the following:

       =  equals
      <>  not equals
      <   less than e.g WHERE COUNT_RATE < 10.
      <=  less than or equal
      >   greater than
      >=  greater than or equal
      IN  followed by a bracketed list of allowed values,
          such as, WHERE NAME IN (3C382, 3C273)
 BETWEEN  followed by two expressions separated by AND,
          such as, WHERE NAME BETWEEN '3C273' AND '3C382'

The ORDER BY specifies the parameter on which the selection is to be ordered. In the following example the SELECT command is used to select all the data in the ME database which have the NAME parameter equal to the value '3C382'. Notice that the single quotes are REQUIRED around string constants.

ME_TOTAL_DEC> sql select name from me where name = '3C382'

This is equivalent to the BROWSE command:

ME_TOTAL_DEC> sn 3C382

This nicely illustrates that, though somewhat clumsy, SQL is flexible enough to allow rather complex queries. For example, in order to select the ME sources that have count rates roughly equal to their galactic longitudes, one could conceivably type

SQL> select name from me where-
_SQL> ((count_rate between  0. and  10.) and (LII between  0. and  10.)) or-
_SQL> ((count_rate between 10. and  20.) and (LII between 10. and  20.)) or-
_SQL> ((count_rate between 20. and  30.) and (LII between 20. and  30.)) or-
_SQL> ((count_rate between 30. and  40.) and (LII between 30. and  40.)) or-
_SQL> .....etc.....

Create

View

A VIEW in SQL is equivalent to a SAMPLE in BROWSE. The create view command can be used to generate samples directly from the database tables. The syntax is as follows:

SQL> CREATE VIEW new_table AS SELECT....

The command creates pointers to the database table containing only those data specified by the following SELECT statement. For example, to create a VIEW/SAMPLE called best4U_ME consisting of all targets beginning with the name 4U, and with count rates greater than 20 and quality flag > 3, type the following:

SQL> create view best4U_ME as select * from me-
_SQL> where name like 4U%-
_SQL> and count_rate > 20.-
_SQL> and qflag >= 3

If you omit the ``_ME" from the name of a view, the system will add it for you.

Index

SQL> CREATE INDEX [*] ON table ( parameter )

The command creates an index file, with a name, *, decided by the system, which orders the table in ascending order of the parameter specified . You may subsequently load the table in BROWSE using that index. CREATE INDEX is equivalent to the BROWSE mind command. For example:

SQL> create index on snr_me(lii)
SQL> create index on snr_me(bii)
SQL> create index on snr_me(name)
SQL> create index on snr_me(count_rate)

Exit

SQL> EXIT The command exits the SQL program and returns control to the calling process.