24
Wed, Jul
0 New Articles

Introduction to SQL, Part 1

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

This article is an excerpt from Mastering IBM i (MC Press, 2011).

 

Structured Query Language (SQL) has become the mainstay for data manipulation on most platforms today. It is important for today's IBM i professional to understand the traditional DDS tools, but most new database systems are created and manipulated using SQL. The intent of this series of articles is to expose the reader to both the traditional tools and the latest tools and concepts. SQL is a complete database language with data-definition, data-manipulation, and data-control components.

 

Whole books are written about SQL—some quite large. This series provides just a taste of SQL to give you a hint of its capabilities. SQL is a powerful language, and you should learn it well before you try to apply it to a production database. Our scope here is limited to that part of the Data Manipulation Language (DML) that lets you retrieve information from the database and change (update, insert, and delete) records in the database. Also included is a short introduction to Data Definition Language (DDL) Create statements, which let you create SQL objects, and DROP statements, which let you delete SQL objects.

A Short History of SQL

Andrew Richardson, Donald C. Messerly, and Raymond F. Boyce at IBM developed the precursor of today's SQL in the 1970s. This version, called SEQUEL, was developed to manipulate data stored in IBM's original database product, System R. System R, which IBM patented in 1985, was a research project that was the first to demonstrate that a relational database management system (RDBMS) could provide good transaction-processing performance. The American Institute of Standards and Technology standardized SQL in 1986 as SQL-86, and in 1987 the International Organization for Standardization (ISO) standardized SQL. Although the original SQL standard designated the official pronunciation of SQL as "es queue el," many people still mispronounce the abbreviation as "sequel."

Introduction to SQL Terms

Before we continue, we will compare the terminology of SQL and the traditional DB2 system file-access methods. Table 13.1 lists the common terms for both methods of access.

 

Table 13.1: Comparison of   SQL and System File-Access Terms

SQL Term

Traditional File-Access Term

Schema—A number of related objects that a user can reference by name; consists   of a library, a journal, a journal receiver, an SQL catalog, and, optionally,   a data dictionary. A schema is often referred to as a collection or   a database.

Library—A number of related objects that a user can reference by name.

Table—A set of columns and rows.

Physical file—A set of records.

Row—The   horizontal part of a table that contains a serial set of columns.

Record—A set of related fields.

Column—The vertical part of a table of one data type.

Field—One or more bytes of related information of one data type.

View—A subset of columns and rows of one or more tables.

Logical file—A subset of fields or records of up to 32 physical files.

Index—A collection of data in the columns of a table, logically arranged in   ascending or descending order.

Index—A type of logical file.

Package—An object that contains control structures for SQL.

SQL package—An object that contains control structures for SQL statements.

Catalog—A set of tables and views that contain information about tables,   packages, views, indexes, and constraints.

(No similar object.)

 

Introduction to SQL

In the IBM i OS, we can use SQL on physical and logical files created by compiling DDS source code, as well as on SQL-created tables (physical files) and views (logical files). This is an important feature because many Power Systems using the IBM i OS contain DB2 databases that were not created with SQL. In today's programming environment, we need to use SQL's powerful query and update capability to access these databases.

 

SQL statements can exist in and be executed from several different environments on the system. In this series, we limit our discussion to what IBM calls dynamic SQL statements submitted to the interactive Run SQL Scripts interface, which is part of IBM i Navigator. In addition to interactive SQL, in which you type a statement on an SQL command line (similar to a CL command), you also can use SQL from within a C, COBOL, Java, or RPG program. The statements can be static SQL, in which the statements are hard coded but typically use host program variables for comparison values, or dynamic SQL, in which the program prepares the SQL statement as a character string that is then passed to the IBM i DB2 database manager for execution. When used from within a program, SQL typically takes over all or much of the traditional file input/output (I/O) and data-access responsibilities from the program logic.

Interfaces Used to Enter SQL Commands

The traditional STRSQL interface, used within a 5250 session, lets you enter, run, and display the results of SQL statements, as you see in Figure 13.1.

 

 111412BuckFigure 3 1

Figure 13.1: STRSQL Interface Using a 5250 Session

 

Although the examples in this series will work with traditional tools such as the 5250 interactive STRSQL program, we will be using the Run SQL Scripts interface, part of the IBM i Navigator product, here. Figure 13.2 shows this interface.

 

 111412BuckFigure13 2

Figure 13.2: Run SQL Scripts Interface

 

We will not discuss all the features of the Run SQL Scripts interface. For our purposes here, you should consider the interface as an editor, which allows you to run and display the results of SQL statements.

 

An additional feature of this editor is the capability to run CL commands that do not require an interactive display. Figure 13.3 shows the CPYF CL command after it has been entered and executed. The messages from the command are also displayed in the lower pane. The complete command is

 

CL: CPYF INTROCLASS/EMPPF CIS001/EMPPF CRTFILE(*YES)

 

 111412BuckFigure13 3

Figure 13.3: CPYF Command Entered in the Run SQL Scripts Interface

 

You can enter numerous commands in this window at the same time. All commands must end with a semicolon (;), and you must designate CL commands by starting them with CL:.

Starting the Run SQL Scripts Interface

Let's assume we have connected to our system using IBM i Navigator and have expanded the Databases icon, right-clicked the local database icon (on this system, S191ff5c), and clicked Run SQL Scripts, as Figure 13.4 shows.

 

 111412BuckFigure13 4

Figure 13.4: Starting the Run SQL Scripts Interface

 

Figure 13.5 shows the open Run SQL Scripts interface with the "input" and "output" panes. We use the upper, input pane (labeled 1) to create and edit SQL and CL commands. The lower, output pane (2) displays the results from running an SQL or CL command.

 

In the figure, we have selected Options from the toolbar at the top of the window and then Display Results in Separate Window. Selecting this option will open another window to display the results of an SQL command. After we have selected this option, the output pane will display any messages resulting from running a command.

 

If we had not selected this feature (note the arrow), we could have clicked the tabs at the bottom of the window to switch panes and see messages and results.

 

 111412BuckFigure13 5

Figure 13.5: The Run SQL Scripts Interface

 

In Figure 13.6, we have run our simple SQL statement again. The results are displayed in a new window, and we now can see any messages that result from running the commands.

 

 111412BuckFigure13 6

Figure 13.6: Run SQL Scripts Interface, Continued

 

Run SQL Scripts uses Java Database Connectivity (JDBC) to connect to the IBM i DB2 database to run commands. You might have noticed in the preceding figures that the SQL command we used was qualified. This means that we included the schema and table name, as follows:

 

Select * From cis001.EMPPF

 

We needed to qualify the table because when we started Run SQL Scripts, we were at the logical database level and not on a specific schema. We can change the default schema by clicking the Connection option from the toolbar at the top of the window and then selecting Use Temporary JDBC Settings, as in Figure 13.7.

 

 111412BuckFigure13 7

Figure 13.7: Change Connection Settings

 

Selecting the Use Temporary JDBC Settings option displays the window in Figure 13.8. Here, we have changed the Default SQL schema to our student library, CIS001.

 

 111412BuckFigure13 8

Figure 13.8: Change Connection Settings, Continued

 

A number of tabs at the top of the window let you change additional defaults. For this example, we will change the default schema and click Connect. This change will enable us to write unqualified SQL statements, such as

 

Select * From EMPPF

SQL Syntax

The basic form of an SQL statement to obtain information from a database table or tables is

 

Select field-list

From file-list

Where conditional-expression

 

We will look at each of the values (in italics) more carefully.

 

Select field-list

 

In its simplest form, you can replace a field list with an asterisk (*), which means all fields from the specified file (or files). So if you coded

 

Select *

From EMPPF

 

you would be instructing SQL to display data for all fields in the record format of file EMPPF.

 

If you did not want the entire record format, you could name the fields that you did want, in any order, each separated from the next by a comma. SQL uses the comma (,) as an item separator for any kind of list. You would need to know the field names as they are recorded in the record format of the externally described file. For field names and SQL keywords, case does not matter: select * is the same as SELECT *. Only when you are dealing with quoted strings (e.g., 'Human Res') does case matter.

 

In a field list, you can rename a field temporarily (just for the output of that statement) using an AS clause. You also can perform arithmetic operations on numeric fields and string operations on alphanumeric field values to create new "alias" fields. For example, the following statement creates a virtual column named RAISE in the result table:

 

Select EMPNO, LASTNAME, SALARY,

SALARY * .035 As RAISE

From EMPPF

 

RAISE does not exist in the base table. It is calculated for each row of the result table as 3.5 percent of that employee's salary, by multiplying each record's current value of SALARY by the constant value .035. You can also use addition, subtraction, and division operators in this type of arithmetic expression.

 

Functions that work on the value of a field for each selected record are called scalar functions. You can use scalar functions, as well as constants and arithmetic or string operators, to create virtual columns. For example, to create a field called NAME, which consists of the first initial plus the last name, and display it along with employee number, you could code

 

Select Substring(FIRSTNAME,1,1) Concat '. ' Concat

LASTNAME As NAME, EMPNO

From EMPPF

 

 

Figure 13.9 displays the result of entering this SQL command in the input window and selecting the Run icon (note the arrow).

 

 111412BuckFigure13 9

Figure 13.9: Display Output of Virtual Column NAME and EMPNO from File EMPPF

 

Instead of spelling out Concat (concatenation operation), an alternative is to specify the two bar or pipe symbols (||). (Shift+Backslash provides the bar character on most keyboards.) The Substring scalar function has three arguments (as in Query for i5/OS)—source string, starting position, and length—but you use commas to separate the arguments.

 

SQL has many scalar functions for numeric and alphanumeric fields, data-type conversion, date and time operations, and trigonometric operations. You can find explanations of these functions at the IBM i Information Center or in IBM's DB2 for i SQL Reference.

 

We will continue our discussion of SQL syntax in the next article, exploring how to use the From file list and specify join operations, as well as how to code the Where clause.

 

 

James Buck
Jim Buck's career in IT has spanned more than 35 years, primarily in the college education, manufacturing, and healthcare industries. Past president (13 years) of the Wisconsin Midrange Computer Professional Association, he has served on several teams developing IBM and COMMON certification tests. Jim has co-authored several IBM i textbooks with Bryan Meyers that are used by many companies and in colleges worldwide. Other accomplishments include: recipient of the 2007 IBM System i Innovation - Education Excellence Award, 2014 COMMON President's Award, and 2013/2016/2017 IBM Champion - Power Systems.

Jim is the president and founder of imPower Technologies, where he provides professional IBM i training and consulting services. He is active in the IBM i community, working to help companies train their employees in the latest IBM technologies and develop the next generation of IBM i professionals.

MC Press books written by Jim Buck available now on the MC Press Bookstore.

Control Language Programming for IBM i Control Language Programming for IBM i
Master the A-Z of CL, including features such as structured programming, file processing enhancements, and ILE.
List Price $79.95

Now On Sale

Mastering IBM i Mastering IBM i
Get the must-have guide to the tools and concepts needed to work with today's IBM i.
List Price $85.95

Now On Sale

Programming in ILE RPG Programming in ILE RPG
Get the definitive guide to the RPG programming language.
List Price $95.95

Now On Sale

Programming in RPG IV Programming in RPG IV
Understand the essentials of business programming using RPG IV.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: