SQL
The language you use to ask a database for data, and to change it.
Also written Structured Query Language
Why it matters. The bank's account data lives in Db2 tables. Most questions about it are one short SQL statement.
See it
List the columns you want back.
Syntax
SELECT columns FROM schema.table [WHERE condition] [GROUP BY columns] [ORDER BY column [DESC]] [FETCH FIRST n ROWS ONLY] INSERT INTO table [(columns)] VALUES (values) UPDATE table SET column = value [WHERE condition] DELETE FROM table [WHERE condition]
- SELECT
- The columns to show.
- FROM
- The table to read.
- WHERE
- Keep only the rows that pass the test.
- GROUP BY
- One result row for each value.
- ORDER BY
- Sort the result. Add DESC for biggest first.
- FETCH FIRST n ROWS ONLY
- Stop after n rows.
- INSERT / UPDATE / DELETE
- Add, change or remove rows.
Try it SELECT
- 1
You're in the SYSIN lines of job SQLASK, a DSNTEP2 step. Write the statement that shows the account number and the balance of the account ACCT0001. The table is M1B.ACCOUNTS.
SYSIN SELECT ACCTNO, BALANCE FROM M1B.ACCOUNTS WHERE ACCTNO = 'ACCT0001'; - 2
Add a second statement: the three biggest balances in the bank. Show the account number, the type and the balance.
SYSIN SELECT ACCTNO, ACCTTYPE, BALANCE FROM M1B.ACCOUNTS ORDER BY BALANCE DESC FETCH FIRST 3 ROWS ONLY; - 3
Submit the job SQLASK from your DEV JCL library.
SUBMIT 'ZB0001.DEV.JCL(SQLASK)' - 4
Read the output of the job.
OUTPUT SQLASK
Remember
- SELECT columns FROM table WHERE test.
- Text goes in single quotes. Numbers do not. Test for a missing value with IS NULL.
- ORDER BY sorts the result. FETCH FIRST n ROWS ONLY keeps the first n.
The usual mistake
SELECT * FROM M1B.ACCOUNT
A letter is missing. Db2 has no table with that name, and answers with SQLCODE -204.
SELECT * FROM M1B.ACCOUNTS
On real z/OS
MF/OS runs each SQL statement in one run, with no locking or isolation levels, and it uses ASCII order for sorting. The seeded bank tables carry no keys, so duplicates are allowed there.
Where can I practise SQL?
Right here, at ZedBank. You take a junior job at a simulated bank and use SQL in real work, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.
- Embedded SQLSQL statements written directly inside a COBOL program, marked off so they can be prepared before compiling.
- IndexA structure that lets a database find rows by value without reading the whole table.
- Primary keyThe column or columns that uniquely identify each row in a table, so no two rows share the same value.
- CursorA mechanism for stepping through a set of rows one at a time, when a query returns more than one result.