SQL - Structured Query Language

SQL (Sequel) is the language used in many relational database systems on many different platforms.

With the increase in popularity of Data Warehouse and the increase in specialized desktop systems, many people outside of the MIS area are using SQL. You may find people in marketing using it to help identify key target segments, people in finance may use it to generate financial reports or executives may use it to help identify company trends.

We will be using SQL/DS - Data Systems, on our system.

With SQL you can generate queries (retrieve specfic data from a table and present it in some specified manner), add data, update and delete. You can also define your database.

SQL is non-procedural; COBOL is procedural; SQL can be single statements which produce a set of instructions to the database.

SQL is used in two ways:

1.) Interactively - display data, sort it, set up control breaks to group data, generate statistics or update the database.

2.) In COBOL, or some other language, you can have embedded SQL statements in your code, it is converted to COBOL code if you embed the SQL in a COBOL program. One SQL statement can generate a lot of COBOL statements.

We will be learning a fair amount of SQL so that you will be able to create a database, update the database and issue queries.

1.) We will start with a brief overview of relational databases

2.) We will then proceed to learn the syntax of SQL statements

3.) You will build and issue some SQL statements to the database.

4.) You will then learn how to use SQL in your COBOL

We are not going to be concerned with the inner workings of SQL/DS as we did with VSAM.

SQL Tables

Data are stored in tables, not files.

Tables consist of Rows or Tuples (records), and Columns (fields)

Employee Table:



SSN
NAME
TITLE
DEPT.CODE
SALARY

ROW1
111111111
Sam
Prog/Anal I
002
34,200

ROW2
123453425
Sue
Market Research Assist.
010
24,320

ROW3





ROW4





Advantages/Differences between Relational Tables and files

1.) Can easily retrieve specific rows

2.) Can easily retrieve specific columns instead of the entire record

3.) SQL retains your table definition, column names. You can retrieve data by column name ignoring the position in a record.

4.) You can use SQL interactively, retrieving any data with one command. You can easily generate many reports.

5.) SQL tables are easy to change, add columns, "join" two tables.

Defining Tables

1.) Assign a name to a table.

2.) Define column names and data types: character, binary, packed decimal, etc.

CREATE TABLE [creator.]table-name
(Colname1 data-type1,
Colname2 data-type2,
Colname3 data-type3)
IN [owner.]dbspace-name

1.) Table-name - up to 18 characters long. Must begin with A-Z, 0-9, $, @, #

2.) Column name - follows the same rules.

  1. dbspace

Example: Create an SQL table, don't include data, simply record table and column names in SQL

CREATE TABLE S954323.EMPMAST
(	SSN CHAR(9),
	NAME CHAR(30),
	TITLE CHAR(30),
	DEPTCODE CHAR(5),
	SALARY DECIMAL(9,2)  )
	IN S999999.MYDBSPACE

Data Types and COBOL equivalents

1.) CHAR[ACTER](n) - PIC X(n)

2.) INT[EGER] - full word binary - PIC S9(9) COMP/BINARY

3.) SMALLINT - halfword binary - PIC S9(4) COMP/BINARY

4.) DEC[IMAL](z,y) - packed decimal - PIC S9(x)V9(y) COMP-3/PACKED-DECIMAL

where x + y = z; SQL will round even numbers to the next highest odd number

Querying Tables

SELECT statement

1.) Online - displays requested data, requested rows and columns

2.) In programs - similar to a READ, reads data into variables

3.) You can get data from more than one table.

SELECT column-names
	FROM [creator.]table-name

Examples:

1) SELECT SSN, NAME, TITLE, DEPTCODE, SALARY

FROM S999999.EMPMAST

This example selects the specified columns from the EMPMAST table. All rows are selected.

2) SELECT * FROM S999999.EMPMAST

This example selects all rows and columns from EMPMAST.

3.) SELECT NAME, SALARY, TITLE

FROM S999999.EMPMAST

This example demonstrates the ability to display only the specified columns in a different order than they appear in the table.

SELECTING Specific Rows - WHERE clause

WHERE column-name operator literal/expression

operators: =, ^=, <>, >, <, >=, <=

1.) SELECT * FROM S999999.EMPMAST

WHERE DEPTCODE = '01000'

This example selects all rows from EMPMAST which match the department code '01000'. All columns are displayed.

2.) SELECT * FROM S999999.EMPMAST

WHERE SALARY > 20000

This example selects all rows from EMPMAST where the employee salary is greater than 20000.

Multiple conditions:

3.) SELECT * FROM S999999.EMPMAST

WHERE TITLE = 'PROGRAMMER' AND

SALARY > 20000

SELECT * FROM S999999.EMPMAST

WHERE DEPTCODE = '01000' OR DEPTCODE = '02000'

Sorting Rows: ORDER BY

Ascending order is the default

4.) SELECT * FROM S999999.EMPMAST

ORDER BY NAME

5.) SELECT * FROM S999999.EMPMAST

ORDER BY DEPTCODE, SALARY DESC

This example sorts the output by SALARY in descending order within DEPTCODE

Specifying a range of values

6.) SELECT * FROM S999999.EMPMAST

WHERE SALARY BETWEEN 20000 AND 25000

Specifying a list of values

7.) SELECT * FROM S954323.EMPMAST

WHERE DEPTCODE IN('01000', '02000')

Search for a string

% - represents any string

_ (underscore) represents any single character

8.) SELECT * FROM S999999.EMPMAST

WHERE DEPTCODE LIKE '01%'

9.) SELECT * FROM S999999.EMPMAST

WHERE DEPTCODE LIKE '010_0'

10.) SELECT * FROM S999999.EMPMAST

WHERE TITLE LIKE '%PROGRAMMER%'

This example will select both PROGRAMMER/ANALYST and SYSTEMS PROGRAMMER

11.) SELECT * FROM S999999.EMPMAST

WHERE NAME LIKE 'SMITH%'

This example will select any name beginning with SMITH.

Functions - AVG, MAX, MIN, SUM, COUNT

a.) Cannot mix functions with column names in a SELECT. A function is aggregating data while a column produces values for each row.

b.) AVG, SUM can only be used on numeric columns

c.) MAX, MIN can be used on any data type

d.) COUNT(DISTINCT DEPTCODE) - DISTINCT eliminates the counting of duplicate values; how many distinct values of Deptcode are there?; otherwise COUNT simply produces a count of the number of rows. Use COUNT(*) for counting rows.

Examples:

SELECT COUNT(*) FROM S999999.EMPMAST

WHERE SALARY > 25000

This example returns a count of the number of employees with a salary greater than 25,000

SELECT COUNT(DISTINCT DEPTCODE)

FROM S999999.EMPMAST

This example returns the number of unique department codes in the table.

GROUP BY

#16 on Example handout:

SELECT DEPTCODE,AVG(SALARY) FROM EMPMAST

GROUP BY DEPTCODE

Another table: Department Code Table (DEPTTAB)

SELECT * FROM DEPTTAB

JOINING TABLES

Two are more tables can be "joined". A connection is made between two tables by a column they share. If a row exists in each table with the same value for the common column, the rows are joined and the combined data are in the result.

Example #18 on Example handout:

SELECT NAME, DEPTNAME FROM EMPMAST X, DEPTTAB Y

WHERE X.DEPTCODE = Y.DEPTCODE ORDER BY NAME

The comparison condition is called the "join condition". A row is included in the result only if corresponding matching rows were found in each table. If an employee has a deptcode that doesn't exist in the dept table, then the join will not contain the employee.

Using ISQL and Creating a Table (refer to handout)

1) PSETUP SQL - This has been done for you in INSPROF when you logon

2) Initialize your CMS userid: SQLINIT DB(SQLDBA)

a) SQLDBA is the name of the database

3) To create a table, we will use the Database Services Utility (DBSU)

a) This utility is useful for executing longer SQL statements such as CREATE TABLE which are stored in a file.

b) DBSU can also load data from a sequential file, backup a table, restore a table, extract data from a table into a sequential file.

c) It reads from SYSIN, writes output to SYSPRINT - must do filedefs

4) Review steps for creating a table on the handout

a) Look at EMPMAST DBSUIN D

b) Look at EMPMAST EXEC D

c) Look at EMPMAST DATA D

d) Type EMPMAST to run the exec and process your SQL CREATE TABLE

e) Browse the output file EMPMAST DBSUOUT