sc2sql: Old Sculptor SQL

Old Sculptor SQL

SQL (Structured Query Language) is a powerful but simple method of extracting data from a Sculptor database. It is a stand-alone, command line program that executes queries on a Sculptor file or files, enabling data to be obtained by end users without the need for a Sculptor development system or the ability to write a Sculptor program. The user specifies selection criteria and the fields for inclusion, which are displayed in columnar form. Sculptor SQL cannot be used to update files.

A query can be executed in one of the following ways.

1. Type the query in at the command line.

   This method is obviously only suitable for brief queries that are unlikely to be repeated.

   Type sc2sql at the DOS prompt to load Sculptor SQL. Header information such as Version No is displayed, followed by an asterisk. Start typing the query after the asterisk.

   The query may be single-line or multi-line. A semi-colon indicates that the query is complete. If a line does not end with a semi-colon, SQL begins a new line and the prompt changes as follows:

   * select s_firstname, s_surname, s_dept
   *>
   

   Continue to type the query text until complete, ending with a semi-colon (which may be the only character of the last line). The query is then executed and the selected data displayed. If the query was invalid for any reason an error message is issued. The user is prompted to enter the RETURN key to continue, after which the asterisk indicating the start of a new query is displayed.

   The UP and DOWN arrow keys can be used to cycle through previously entered lines.

   Type exit to quit Sculptor SQL.

2. Use a saved query file.

   This method is more suitable for complex queries and also facilitates repeated use of the same query:

   sc2sql "*filename*"
   

   The query text can be created with any text editor. Note that a semi-colon at the end of the file is not required. The file does not need to have any particular suffix, though Sculptor example query files follow the convention of using “.sql”.

The following examples illustrate the format of an SQL query:

1. Select all fields from all records:

   select * from staff
   

2. Select specified records and fields:

   select s_firstname, s_surname, s_dept
   from staff
   where s_dept = "A1" or s_dept = "A2"
   

3. Include mathematical expressions and headings:

   select s_surname, s_dept,
          s_income                        heading "Old",
          s_income * 1.08                 heading "New",
          s_income * 1.08 - s_income      heading "Diff."
   
   from staff
   where s_dept = "A1"
   

4. A “join” - fields are included from more than one file:

   select X.s_firstname, X.s_surname, X.s_dept, Y.d_name
   from staff X, dept Y
   where X.s_dept = Y.d_code
   

5. A simple nested sub-query:

   select *
   from staff
   where s_dept =
      (  select d_code
         from dept
         where d_name = "Sales" )
   

6. Format a query for a report and save to disk:

   select s_firstname right heading "First Name" width 10,
          " ",
          s_surname width 8,
          s_income total format "###,###"
   
   from staff
   into "report" in pages with line numbers
   

NOTES

• Sculptor SQL is not to be confused with the sql command, which is a built-in command only available for use with a database that has an ODBC driver.