Skip to content

Page 1

SQL - Structured Query Language

ND 211049 SQL for ND-500

ND 211050 SQL Library for ND-100

+------------------------+
|   Artname    |  Price  |
+------------------------+
|   T-shirt    |  35.50  |
|   Bicycle    |  1 990  |
|   Telescope  | 12 800  |
+------------------------+

+-------+----------+-----+---------+
| 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  | 1846.00|
| West Garage           |    11      | T-shirt|  35.50  |  390.50|
| Aerospace             |     9      | T-shirt|  35.50  |  319.50|
| Aerospace             |     1      |Telescope|12 800.00|12800.00|
+-----------------------+------------+--------+--------+--------+

Data Management That Makes Sense to People

[Logo: 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 both users and those who implement.

SQL on the ND-500 consists of three parts:

  • SQLI: the interactive query editor
  • SQLLIB: the application programming library
  • SQLSERVER: the common server for query compilation, optimisation and execution.

Only the application program library is available for ND-100. The library requires the use of an SQLSERVER on a local or remote ND-500.

SQL uses SIBAS as the database management system, allowing access to any SIBAS database. Selection of data 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 administrators’ 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. The query effectively provides a customised view of the database.
  • Simplified programming 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 an hour.
  • 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.
  • Recognized 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 SQLSERVER reduces communications costs compared to corresponding SIBAS backend calls.
  • A view may include data from more than one physical database (not first version).
  • Greatly simplifies procedures for maintaining distributed data.

Product Description

SQL represents data in table form. When used with SIBAS version G or older, a realm is treated as a table, and each record is regarded as a row.

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. Redefinition of physical structure, and to some extent data types, may thus be made without affecting application programs or queries using the data.

These features make SQL a valuable tool for 4th-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. The size of the screen area adjusts automatically according to terminal types (dynamically for FACIT TWIST).

SQLI allows the user to:

  • Write and execute ad hoc queries.
  • Look at the result with a movable screen window.
  • Adjust column width, to print repeated column values as blanks, and to print zero values as blanks.
  • List the result on a printer or a NOTIS-WP-file.
  • 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, to make data access through SQL simple and straightforward.

Page 3

SQL - Structured Query Language

SQLSERVER - the SQL Database Manager

This system program executes SQL-calls to SIBAS, received from application programs or SQLLIB. 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.

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-500 and ND-100, for the standard programming languages COBOL, FORTRAN and PLANC.

  +----------------+   +----------------+
  |     ND-500     |   |     ND-100     |
  +----------------+   +----------------+
  | INTERACTIVE    |   | USER PROGRAM   |
  | QUERY EDITOR   |   +----------------+
  +----------------+   |    SQLLIBRARY  |
  | USER PROGRAM   |   +----------------+
  +----------------+
  |    SQLLIBRARY  |   +----------------+
  +----------------+   |   SQL SERVER   |
                       |   SIBAS DBMS   |
                       +----------------+
        COSMOS

Examples

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

To retrieve a whole table:

SELECT * from Order;
Custno Artname Qty Date
117 T-shirt 11 860321
130 T-shirt 9 860321
130 Telescope 5 860130
119 Bicycle 8 861007

To select a table of those customers located in Oslo, only presenting the Name and Location Columns:

SELECT Name, Location
FROM Customer
WHERE Location = 'Oslo';
Name Location
Scandinavian Design Oslo
Norsk Data Oslo

The ability to merge data from 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.

SELECT Name "Customer", Qty "Quantity", Article.Artname "Article", 
       Price "Unitprice", Qty*price DISPLAY "kr.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

Built-in functions

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

Page 4

First-version Restrictions

Version A of SQL does not include the programming library, i.e. only ND-211049 may be ordered, consisting of the SQL Server and Interactive Query Editor modules for ND-500 systems. There are also certain limitations in version A, due to functions not yet implemented:

  • Database definition and redefinition through SQL
    (use SIBAS)
  • Queries referencing more than one database
  • Queries to a server on a remote system
  • Reference to a stored view, — in version A each
    query must specify complete parameters and
    predicates, making some queries more complicated,
    but without loss of functionality
  • Subqueries, i.e. WHERE-clauses containing new
    queries and EXIST keywords.
  • Access control (authorisation)
  • COMMIT and ROLLBACK are not implemented
  • NULL value (undefined) is not implemented, blanks
    and 0 are used

Prerequisites

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

Documentation

Document Reference No.
SQL User Manual ND 60.258 E N
SQL Reference Manual ND 60.259 E N 

  __      ______   
 /\ \    /\__  _\  
 \_\ \   \/_/\ \/  
 /'_` \     \ \ \  
/\ \L\ \     \_\ \ 
\ \___,_\    /\____\
 \/__,_ /    \/____/
Norsk Data