Introduction

Introduction

This self-training guide provides information on how to use interactive SQL data manipulation language (DML).

After reading this guide 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 will use basic SQL DML or who will use SQL in programs can benefit from the exercises in this manual.

Online Exercises: You can do the exercises in this guide 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 guide 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 guide
• 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 guide in the CA IDMS/DB environment, be familiar with documentation of the tool you will use to submit SQL statements, such as the CA IDMS Common Facilities Guide manual. 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.

  Note: The exercises in this guide 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.

  Note: 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 this 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 Chapter 1, "Relational Database Concepts." Read the chapters in order and do the exercises and review exercises in each chapter. Keep in mind that several people in your organization may use this guide, so you probably don't want to mark in it.

If you are familiar with relational database concepts, begin with Chapter 2, "What Is SQL?" and read the chapters in order.

Allow five to eight hours to complete the entire self-training guide including the online practice exercises. You can complete the self-training guide in one sitting or in several sessions as follows:

• Session 1—Chapters 1 through 4
• Session 2—Chapters 5 and 6
• Session 3—Chapters 7 and 8

Practice exercises begin in Chapter 3, "Retrieving Data." Each exercise after the first builds on the previous exercise. If you are doing the exercises online, you can check your work by looking at the results shown after the exercise.

In Chapters 3 through 8, you see examples written out in full with the label How it's done. When you enter these statements online, you'll see a result table with the same contents as the one shown in the book. The table in the book may be abbreviated.

After each example and its result, there are exercises 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.Then check the correct answers in Appendix B, "Answers to Exercises."

In Appendix A, "Sample Data Description Language" you will see sample statements for database definition that you do not enter. They are for your information only.

At the end of each chapter, you will find review exercises covering the material you have just studied. These exercises allow you to evaluate how well you have learned the material presented. You are encouraged to do them.

In addition, Chapters 3 through 7 include scenarios at the 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 appear in Appendix B, "Answers to Exercises" on page 179 B.

The Demonstration Database: In the online practice exercises, you will access data from the personnel database 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 will not access it in these exercises.

The Human Resources and Accounting departments use the database for many of their activities. In this guide, 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 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 you will use:

Note: ₁—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.

Appendix C, "Table Descriptions" presents a description of each column in each table in the database.