Page 1

SQL

SQL - Structured Query Language

ND 211049 SQL for ND-5000
ND 211050 SQL/Link for ND-100

+------------+--------+
| Artname    | Price  |
+------------+--------+
| T-shirt    |  35.50 |
| Bicycle    | 1 990.00 |
| Telescope  | 12 800.00 |
+------------+--------+

+--------+---------+-----+--------+
| Custno | Artname | Qty | Date   |
+--------+---------+-----+--------+
| 102    | T-shirt |  52 | 860208 |
| 117    | T-shirt |  11 | 860321 |
| 130    | T-shirt |   9 | 860321 |
| 130    | Telescope |  1 | 860130 |
+--------+---------+-----+--------+

+--------+---------------------+-------------+
| Custno | Name                | Location    |
+--------+---------------------+-------------+
| 102    | Scandinavian Design | Oslo        |
| 110    | Norsk Data          | Oslo        |
| 117    | West Garage         | London      |
| 130    | Aerospace           | Las Palmas  |
+--------+---------------------+-------------+

Customer	Quantity	Article	Unitprice	Total
Scandinavian Design	52	T-shirt	35.50 kr	1846.00
West Garage	11	T-shirt	35.50 kr	390.50
Aerospace	9	T-shirt	35.50 kr	319.50
Aerospace	1	Telescope	12800.00 kr	12800.00

DATA MANAGEMENT THAT MAKES SENSE TO PEOPLE

   _  __
  | \|  \ 
  |     | 
  |_|\__| 
   N D
  Norsk Data

---

Page 2

SQL - Structured Query Language

Introduction

SQL (Structured Query Language) provides a standard data access facility for ND systems. Offering a high-level database interface for ad hoc queries as well as for application programs, it simplifies data management for users as well as system developers.

SQL on the ND-5000 (and ND-500) consists of four parts:

• SQLI: the interactive query editor
• SQLLIB: the application programming libraries for FORTRAN, COBOL, PLAN C and C
• SQLPREC: SQL precompilers for COBOL and FORTRAN
• SQLSERVER: the common server for query compilation, optimisation and execution.

Only the application program libraries and the precompilers are available in the SQL/Link for ND-100. The libraries require the use of an SQLSERVER on a local or remote ND-5000.

SQL version A allows access to any SIBAS database. Subsequent versions of SQL will be restricted to SIBAS databases that have been specially set up for relational access.

Data selection is unrestricted by the defined physical structure, subject only to consistency constraints. This extremely powerful feature permits very flexible use of data and simplifies the database administrator’s task. SQL ensures automatic navigation to any customised set of data.

Features

Set Processing - what, not how

• A set of data is selected with a query and presented as a table, effectively providing a customised “view” of the database.
• Programming is simplified because of invisible physical structure.
• The physical structure only affects performance. Any keys or indexes available will be used optimally and automatically by SQL.

Structured

• Using statements of structured logic, each user may select a precise, tailored subset of the database.
• Simple: a query may be written within a minute.
• Permits simple expression of complex selection criteria.
• Choice of predefined criteria (“view”) or ad hoc (dynamic) selection.
• The query may be stored for “ready-to-run” recall.

Standard

• Originating from IBM research, now widely used in many systems.
• Recognised by ANSI and ISO standardisation bodies.

Distributed Processing

• A query (whether interactive or providing data to an application) may access a remotely located database.
  • The power of SQL calls to the remote system reduces communications costs.
  • Greatly simplifies procedures for maintaining distributed data.

Product Description

SQL represents data in table form.

The SQL interface hides the internal database structures from the user. The existence of any indexes or sets only affects performance. SQL will always use the optimal method for manipulating data, and uses the index keys and set definitions available. The physical structure, and to some extent data types, may thus be redefined without affecting application programs or queries using the data.

These features make SQL a valuable tool for fourth-generation software, both for the products in the ND-DIALOGUE family and for future systems.

SQLI - the Interactive Query Editor

The query editor is a screen-oriented editor, closely related to NOTIS-WP. SQLI uses the same menu system and editing keys, but has a one-page editing area.

SQLI allows the user to:

• Write and execute ad hoc queries.
• Look at the result with a movable screen window.
• Adjust column width, print repeated column values as blanks and print zero values as blanks.
• List the result on a printer or in a NOTIS-WP document.
• Store the result (in binary or ASCII format) for later processing.
• Store, run, fetch and list the queries in the database.
• Execute queries with parameters.
• Execute a series of queries in batch, and make simple reports.

SQLI incorporates a complete User Guide, available through the HELP facilities. Table and column descriptions are also available through HELP, making data access through SQL simple and straightforward.

SQLSERVER - the SQL Database Manager

This system program executes SQL calls to SIBAS, received from application programs or SQLI. It also fetches ready-to-run queries from the database, and recompiles the query if the database structure has been changed.

A query may consist of a select, update, insert or delete command.

---

Page 3

SQL - Structured Query Language

SQLLIB - SQL Programming Library for Application Programs

The library allows application programs to use an SQLSERVER in the same computer or in a remote system. The library is a set of subroutines or procedures that call on the SQLSERVER.

When the library conveys a query from the user program, the server returns a table which is buffered by the library. The table columns must correspond to local variables in the application program, and the result is transmitted row by row. The libraries are available both for ND-5000 and ND-100, for the standard programming languages COBOL, FORTRAN, C and PLANC.

SQL Precompilers

Precompilers allow SQL statements to be written directly in your COBOL and FORTRAN programs. The precompiler will translate the “embedded” statements into function calls to the SQL library.

The benefits are: - simple, straightforward programming - standardised, portable programs - reduced development and maintenance costs, by ensuring correct transformation of data between application and database system

Examples

In a relational database system, data is stored in tables, such as the example shown on the front page for the tables Customer, Order and Article.

To select a table of customers located in Oslo:

SELECT Name, Location          -- select columns
FROM Customer                  -- from a table
WHERE Location = 'Oslo';       -- select rows

Name	Location
Scandinavian Design	Oslo
Norsk Data	Oslo

The ability to merge two or more tables is one of the most powerful features of relational systems. The common column CUSTNO links the Order and Customer tables together, and Artname links Order and Article, allowing a join operation:

SELECT Name "Customer", Qty
       "Quantity", Article.Artname "Article", Price
       “Unitprice”, Qty*price DISPLAY "£:ZZZZZ.99"
       "Total"
FROM Order, Customer, Article
WHERE Order.Custno = Customer.Custno
AND Order.Artname = Article.Artname;

Customer	Quantity	Article	Unitprice	Total
Scandinavian	52	T-shirt	35.50 kr	1846.00
Design
West Garage	11	T-shirt	35.50 kr	390.50
Aerospace	9	T-shirt	35.50 kr	319.50
Aerospace	1	Telescope	12.800.00 kr	12800.00

An embedded SQL statement to fetch the name and phone number of an employee:

EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 SALESMAN     PIC X(26).
01 PHONENUM     PIC X(10).
01 ID           PIC X(3).

EXEC SQL END DECLARE SECTION END-EXEC.

EXEC SQL WHENEVER NOT FOUND GO TO UNKNOWN END-EXEC.

ACCEPT ID.

EXEC SQL
    SELECT NAME, PHONE
    INTO :SALESMAN, :PHONENUM
    FROM EMPLOYEE WHERE INITIALS = :ID;
END-EXEC

DISPLAY SALESMAN, PHONENUM.

Built-in functions work on columns of data. In this example SUM is used with GROUP BY to create a subtotal for each customer:

SELECT Customer.Name, SUM(Price*Qty)
FROM Order, Customer, Article
WHERE Order.Custno = Customer.Custno AND
      Order.Artname = Article.Artname
GROUP BY Order.Custno;

Customer.Name	SUM(Price * Qty)
Scandinavian Design	1846.00
West Garage	390.50
Aerospace	13119.50

Ordering

For ND-5000 and ND-500 order ND 211049 SQL for ND-5000 consisting of SQLSERVER, SQLLIB and precompilers

For ND-100 order ND 211050 SQL/Link, containing the SQL libraries and precompilers

Prerequisite

• SINTRAN III version K or later
• SIBAS-II version E or later
• SQL Link for ND-100 requires connection to an SQL Server

Documentation

• Dialogue SQL User Guide ND 60.258 EN
• Dialogue SQL Call Interface ND 60.259 EN

[Scanned by Jonny Oddene for Sintran Data © 2010]

---

Page 4

Norsk Data Contact Information

Corporate Headquarters

Olf Helsets vei 5
P.O. Box 5, Bogerud
0601 Oslo 6
Norway

• Tel.: +47-2-620000
• Twx.: 71566nd
• Telex: 79672 nd n
• Telefax: +472-628064 (A)
• Newbury: 44-35664
• Telefax: +47-2-926796 (A)

Norway

• Oslo, Tel: +47-2-620000
• Twx.: 781dn
• Bergen, Tel: +47-5-35800
• Trondheim, Tel: +47-7-621622
• Kristiansand, Tel: +47-5-60538
• Tromsø, Tel: +47-7-791822

ND Comtec Head Office

Olf Helsets vei 5
P.O. Box 5, Bogerud
0601 Oslo 6
Norway

• Tel.: +472-620000
• Twx: 71566nd (N)
• Telefax: +47-2-656801 (A)

ND-Silvidata Sweden

• S86103 Sundsvall
• Sweden
• Tel.: +46-60-151510
• Växjö, tel.: +46-470-18500
• Stockholm, tel.: +46-760-38400

Denmark

Norsk Data A.S
Lautrupbjerg 12 A
P.O. Box 32
2750 Ballerup
Denmark

• Tel.: +45-428665
• Th: 006-35607 nd dk
• Telefax: +45-2-628641 (A)

Finland

• Box 45
• 00201 Helsinki
• Finland
• Tel.: +358-0-2612464

West Germany

Norsk Data GmbH
Hohemborstrasse 10-12
6380 Bad Homburg v.d.H.
West Germany

• Tel.: 06171 - 4060 nd
• Th.: 0041 - 417063 nd

Sweden

ND Norsk Data AB
Karolinska Vägen 1 A
P.O. Box 721
194 27 Upplands Väsby
Sweden

• Tel.: 46-8-7989600
• Th.: 005-45786 noredata s
• Telefax: 46-760-28907 (A)

The Netherlands

Norsk Data Nederland B.V.
Brugwal 5
P.O. Box 50, 3430 AM Nieuwegein
The Netherlands

• Tel.: +31-3402-7211
• Telefax: +31-3402-72400

France

Norsk Data s.a.r.l.
aVue Breteurn
France

• Tel.: +332-353603
• Th.: 002-35830 noredata fermux
• Telefax: +33-53-028463 (A)

Switzerland

Norsk Data (Switzerland) S.A.
Ch. Villard/Le 13me
CH-1008 Prilly-Lausanne
Switzerland

• Tel.: +4121-205122
• Th.: 410-261 c
• Telefax: +41-21-691000

United Kingdom

Norsk Data Ltd.
Newbury, Berks RG16 8LU
United Kingdom

• Tel.: +441 - 302-7632
• Telefax: +441 - 302-9441

Ireland

Norsk Data Ireland Ltd. A/S
Dublin Industrial Estate, Santry Avenue
Dublin 9, Ireland

• Tel.: +353-1467744

USA

Norsk Data N.A., Inc.
Westborough Office Park
1980 West Park Drive
Suite 150
Westborough, MA 01581
USA

• Tel.: +1-617-366-6582

ND International Operations

Olf Helsets vei 5
P.O. Box 7, Bogerud
0607 Oslo 6
Norway

• Tel.: +47-2-620000
• Twx.: 005- Coronavirus non data

Hong Kong

• Norsk Data International
• Tel.: +852-5143/612053
• Th.: 805-012254 chand ling hk

Others

Iceland

• Norsk Data Iceland
• Tel.: +354-5184017

India

Norsk Data (India) Pty. Ltd.

• Tel.: +914-17730216 nd
• Th.: 0054-20856 alpha ent

Pakistan

• Norsk Data Pakistan Pvt. Ltd.

Thailand

Norsk Data Thai Ltd.

• Tel.: +662-253-9884

---

ND
Norsk Data

---