Learning CA IDMS SQL

This section provides self-training about how to use interactive SQL data manipulation language (DML).
idms19
This section provides self-training about how to use interactive SQL data manipulation language (DML).
After reading this section and doing the exercises, you should be able to:
  • Describe a relational database and state its benefits.
  • Create SQL statements to retrieve data, based on specific criteria.
  • Create SQL statements to insert, modify, and delete data from a table
Anyone who uses basic SQL DML or SQL in programs can benefit from the exercises in this section.
Online Exercises
You can do the exercises in this section online in any one of several processing environments. The exercises are designed to be used in the interactive environment.
If you want to do the exercises in this section online, you must:
  • Have online access to the demonstration database that is provided with the product installation and is used by the examples and exercises in this section
  • Know how to access and submit statement syntax to the interactive SQL tool in your environment.
  • Be familiar with the keyboard and terminal in your environment
Check with your system administrator for access to the appropriate system, database, and interactive SQL tool.
Accessing CA IDMS/DB
Before you begin doing the exercises in this section in the CA IDMS/DB environment, be familiar with documentation of the tool you use to submit SQL statements, such as Using Common Facilities. Also, check with your system administrator to learn:
  • The CA IDMS/DC or CA IDMS UCF system to which you should sign on so that you can access the demonstration database online.
    The exercises in this section use mixed upper and lower case characters. Before you invoke the interactive SQL tool, issue the DCUF SET UPLOW command to CA IDMS.
  • The dictionary to which your SQL session should be connected.
  • The qualifiers of the demonstration database table names—a table name in an SQL statement must include the qualifier unless the qualifier matches the default schema for your SQL session.
    You can set the default schema by submitting this statement: SET SESSION CURRENT SCHEMA schema-name.
  • Whether you should roll back (eliminate) changes you make to the demonstration database with INSERT, UPDATE, and DELETE statements.
If so, submit the following statement to the CA IDMS Command Facility before you begin the exercises:
set options autocommit off;
Then, after you finish a session of doing online exercises that update the database but before you exit the CA IDMS Command Facility, issue this statement:
rollback work;
How to Proceed
If you have had no experience with relational databases, begin with Relational Database Concepts. Read the sections in order, do the exercises, and review exercise answers in each section.
If you are familiar with relational database concepts, begin with What Is SQL? and read the sections in order.
Allow five to eight hours to complete the entire self-training section including the online practice exercises. You can complete the self-training section in one sitting or in several sessions as follows:
Practice exercises begin in the Retrieving Data in SQL section. Each exercise builds on the previous exercise. If you are doing the exercises online, you can check your work by looking at the results that are shown after the exercise.
In the sections beginning with Retrieving Data, complete examples with the label "How it's done" are provided. When you enter these statements online, you see a result table with the same contents as the example. The table in the example may be abbreviated.
After each example and its result, exercises are provided where the SQL statements are not given. Instead, a description of the requested information is given, and you write the statements necessary to achieve the result. These exercises are identified by the labels "Now you try it" and "Try another."
Practicing Without Access to a Database
You can go through these exercises without having access to a database. Simply write out your answers and view the correct answers in Answers to Exercises.
In the Sample Data Description Language section, sample statements appear for database definition that you do not enter. They are for your information only.
Review exercises covering the material that you have just studied are provided at the end of each section. These exercises allow you to evaluate how well you have learned the material presented. We encourage you to do them.
In addition, the Retrieving Data in SQL, Using Conditional Retrieval, Using Aggregate Functions, Accessing Multiple Tables, and Nesting SELECT Statements sections include scenarios at the section end. Each scenario requires you to create SQL statements to retrieve or update data based on a specific business requirement.
Answers to Exercises
Answers to online exercises, reviews, and scenarios are provided in Answers to Exercises.
The Demonstration Database
In the online practice exercises, you access data from the personnel database that was developed for a company called Commonwealth Auto.
Commonwealth Auto requires data to be maintained on all employees, jobs, skills, departments, benefits, and projects. Other associated employee information is also maintained, but you do not access it in these exercises.
The Human Resources and Accounting departments use the database for many of their activities. In this section, these departments make requests for reports or information that you satisfy through your knowledge and use of SQL. The requests concern salary and budget information, department lists, and vacation and project updates. They range from the simple to the complex.
The requests are based on actual information that is maintained by a small corporation.
The Commonwealth Auto database consists of two schemas:
  • DEMOEMPL tables containing employee information
  • DEMOPROJ tables containing project-related information
The information is maintained in several tables in the database. These are the tables in the portion of the database that are used:
Table
Schema
Contents
ASSIGNMENT
DEMOPROJ
The assignment of employees to projects
BENEFITS
DEMOEMPL
The benefits an employee has with the company
CONSULTANT
DEMOPROJ
Each consultant that is associated with the company
COVERAGE
DEMOEMPL
Employee's insurance information
DEPARTMENT
DEMOEMPL
Each department within the company
DIVISION
DEMOEMPL
Each division within the company
EMPLOYEE
DEMOEMPL
Personal information on each employee working for the company
EXPERTISE
DEMOPROJ
The skills each employee possesses
INSURANCE_PLAN
DEMOEMPL
Details of each insurance plan
JOB
DEMOEMPL
The jobs within the company
POSITION (see note)
DEMOEMPL
The jobs an employee has held and is currently holding within the company
PROJECT
DEMOPROJ
The projects within the company
SKILL
DEMOPROJ
The skills throughout the company
Table Descriptions presents a description of each column in each table in the database.
POSITION is also an SQL keyword. When it is used to qualify a column name, the table name must be enclosed in double quotation marks. For example, "POSITION".column-name. For information about qualifying column names, see Qualifying a Column Name.
More Information:
The following sections provide additional information that is related to this section.