Books on DB2 and SQL DB2 and SQL Manuals Examples used in DB2/SQL course This accompanies the SQL course --------------Examples shown in the course -- you may copy and paste these into a mainframe file and execute them --EX0001 --Sample SQL Query SELECT EMPNO, LASTNAME, SALARY, COMM FROM EMP --A comment WHERE EMPNO = '000010' OR EMPNO = '000020'; --EX0002 SELECT WORKDEPT, SUM(SALARY) FROM EMP WHERE JOB > 'MANAGER' AND SALARY > 20000 GROUP BY WORKDEPT HAVING SUM(SALARY) > 20000 ORDER BY WORKDEPT FETCH FIRST 1 ROW ONLY -- v7 OPTIMIZE FOR 1 ROW FOR READ ONLY-- /FOR UPDATE WITH UR --/ RR / RS / CS ; --EX0003 SELECT WORKDEPT, SUM(SALARY) FROM EMP WHERE JOB > 'MANAGER' AND SALARY > 20000 GROUP BY WORKDEPT HAVING SUM(SALARY) > 20000 ORDER BY WORKDEPT ; --EX0004 SELECT * FROM EMP ; --EX0005 -- Show some columns, and all rows SELECT EMPNO, LASTNAME FROM EMP ; --EX0006 SELECT DISTINCT JOB FROM EMP ; --EX0007 SELECT DISTINCT WORKDEPT, JOB FROM EMP ; --EX0008 --Show total of salary --and commission. SELECT SALARY + COMM, LASTNAME FROM EMP ; --EX0009 --Give everyone a 5% raise SELECT SALARY * 1.05, LASTNAME FROM EMP ; --EX0010 -- Show total of salary and commission SELECT 'TOTAL COMPENSATION:', SALARY + COMM, LASTNAME FROM EMP ; --EX0011 -- glue name and job SELECT LASTNAME CONCAT JOB FROM EMP ; --EX0012 SELECT * FROM EMP WHERE LASTNAME = 'STERN' ; --EX0013 --WHERE WITH NUMERIC DATATYPE SELECT * FROM EMP WHERE SALARY > 19999 ; --EX0014 --WHERE WITH CHARACTER DATATYPE SELECT * FROM EMP WHERE LASTNAME = ‘SMITH’ ; --EX0015 --Show managers with more than 14 years SELECT LASTNAME, JOB, EDLEVEL FROM EMP WHERE JOB = 'MANAGER' AND EDLEVEL > 14 ; --EX0016 --Show employees with 14, 15, or 16 years SELECT LASTNAME, EDLEVEL FROM EMP WHERE EDLEVEL = 14 OR EDLEVEL = 15 OR EDLEVEL = 16 ; --EX0017 -- using Parentheses SELECT LASTNAME, JOB, SALARY, COMM FROM EMP WHERE JOB = 'CLERK' AND (SALARY > 20000 OR COMM > 3000) ; --EX0018 -- without parentheses SELECT LASTNAME, JOB, SALARY, COMM FROM EMP WHERE JOB = 'CLERK' AND SALARY > 20000 OR COMM > 3000 -- interpreted as: WHERE (JOB = 'CLERK' AND SALARY > 20000) -- OR COMM > 3000 ; --EX0019 SELECT * FROM DEPT WHERE MGRNO = '000030' ; --EX0020 SELECT * FROM DEPT WHERE MGRNO <> '000030' ; --EX0021 --You have to ask explicitly if it IS NULL. --show people who don’t have a manager SELECT * FROM DEPT WHERE MGRNO IS NULL ; --EX0022 --show people who do have a manager SELECT * FROM DEPT WHERE MGRNO IS NOT NULL ; --EX0023 --If there’s a percent sign or underscore in the data string, --you have to use an escape character. SELECT * FROM DEPT WHERE DEPTNAME LIKE '%DEPT 110!%%' ESCAPE '!' --(NONE LIKE THAT) ; --EX0024 --Show employees who have worked from 15 to 17 years. SELECT LASTNAME, EDLEVEL FROM EMP WHERE EDLEVEL BETWEEN 15 AND 17 ; --EX0025 --Show employees who work in departments A00 or D21 SELECT LASTNAME, WORKDEPT FROM EMP WHERE WORKDEPT IN ('A00', 'D21')-- ANY ORDER OK -- You use Parentheses and commas. ; --EX0026 --show whole dept Table; sort by MGRNO in reverse order SELECT * FROM DEPT ORDER BY MGRNO DESC --NOTICE WHAT HAPPENS WITH THE NULLS IN MGRNO ; --EX0027 --show managers higher than 10; sort by manager in reverse order SELECT * FROM DEPT WHERE MGRNO > '000010' ORDER BY MGRNO DESC ; --EX0028 --show lastname and job; sort by lastname and job SELECT LASTNAME, JOB FROM EMP ORDER BY LASTNAME, JOB ; --EX0029 SELECT LASTNAME, SALARY + COMM FROM EMP ORDER BY 2 --2 MEANS THE SECOND "COLUMN" SELECTED ; --EX0030 SELECT LASTNAME AS EMPLOYEE, SALARY AS PAY_AMOUNT FROM EMP ; --EX0031 SELECT LASTNAME, SALARY + COMM AS TOTAL_PAY FROM EMP ; --EX0032 --SHOW LASTNAME, DEPARTMENT, AND DEPARTMENT NAME SELECT LASTNAME, WORKDEPT, DEPTNO, DEPTNAME FROM EMP, DEPT -- TWO TABLES, INDICATES A JOIN WHERE WORKDEPT = DEPTNO-- THIS IS THE JOIN CONDITION ; --EX0033 --what are the departments for each person? SELECT DEPTNAME, EMPNO, LASTNAME FROM EMP, DEPT WHERE WORKDEPT = DEPTNO -- THE JOIN CONDITION ; --EX0034 --what are the departments for each person -- for administrative dept D01 only SELECT LASTNAME, EMPNO, DEPTNAME FROM EMP, DEPT WHERE WORKDEPT = DEPTNO -- THE JOIN CONDITION AND ADMRDEPT = 'D01' ; --EX0035 INSERT INTO DEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT) VALUES ( 'A99', 'NEW DEPARTMENT', '123456','A98'); ; SELECT D.DEPTNAME, D.MGRNO, E.EMPNO, E.LASTNAME FROM DEPT D LEFT OUTER JOIN EMP E ON D.MGRNO = E.EMPNO ORDER BY D.MGRNO ; --EX0036 -- show departments and the names of their manager -- also show any department without a manager (actually none) -- “who's in dept but not on emp (no one)” -- also show any employee who is not a manager with Nulls (most) -- “who's on emp but not a manager on dept” SELECT D.DEPTNAME, D.MGRNO, E.EMPNO, E.LASTNAME FROM DEPT D FULL OUTER JOIN EMP E ON D.MGRNO = E.EMPNO WHERE E.SEX = 'F' -- TO CUT DOWN ON OUTPUT ORDER BY D.MGRNO ; --EX0037 -- What is highest salary? SELECT MAX(SALARY) FROM EMP ; --EX0038 -- how many different jobs are there? (not-what are the jobs?) SELECT COUNT(DISTINCT JOB) FROM EMP ; --EX0039 -- Give average salary for each department SELECT WORKDEPT, AVG(SALARY) FROM EMP GROUP BY WORKDEPT ; --EX0040 SELECT WORKDEPT, AVG(SALARY) FROM EMP GROUP BY WORKDEPT HAVING AVG(SALARY) > 30000 ; --EX0041 SELECT SUBSTR(DEPTNAME,1,1) FROM DEPT ; --EX0042 INSERT INTO DEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT) -- column names VALUES ( 'A99', 'Y3K COMPLIANT PROGRAMS', '000099','A01') --data values ; --EX0043 DELETE FROM EMP WHERE JOB = 'MANAGER' ; --EX0044 DELETE FROM EMP WHERE WORKDEPT IN (SELECT DEPTNO FROM DEPT WHERE DEPTNAME = 'PLANNING') ; --EX0045 UPDATE EMP SET SALARY = SALARY + 1.50, LASTNAME = 'OLIVIA' WHERE COMM > 4000 ; --EX0046 --WHO MAKES MORE THAN LUTZ? SELECT LASTNAME FROM EMP WHERE SALARY > (SELECT SALARY FROM EMP WHERE LASTNAME = 'LUTZ') ; --EX0047 --FIRST, FIND OUT HOW MUCH LUTZ MAKES SELECT SALARY FROM EMP WHERE LASTNAME = 'LUTZ' ; --EX0048 --THEN, FIND OUT THE NAMES OF THOSE WHO MAKE MORE THAN 29840 SELECT LASTNAME FROM EMP WHERE SALARY > 29840.00 ; --EX0049 --SHOW PEOPLE WHOSE DEPARTMENT HAS A MANAGER (EMP TABLE) SELECT LASTNAME, WORKDEPT FROM EMP E WHERE EXISTS (SELECT * FROM EMP EE WHERE JOB = 'MANAGER' AND E.WORKDEPT = EE.WORKDEPT) ; --EX0050 --SHOW DEPARTMENTS WITH NO EMPLOYEES SELECT * FROM DEPT D WHERE NOT EXISTS (SELECT * FROM EMP E WHERE E.WORKDEPT = D.DEPTNO) ; --EX0051 --UNION ALL DOES NOT SORT, --DOES NOT ELIMINATE DUPLICATES SELECT LASTNAME, 'MALE' FROM EMP WHERE SEX = 'M' UNION ALL SELECT LASTNAME, 'FEMALE' FROM EMP WHERE SEX = 'F' ; --EX0052 --UNION SORTS, --ELIMINATES DUPLICATES SELECT LASTNAME, 'MALE' FROM EMP WHERE SEX = 'M' UNION SELECT LASTNAME, 'FEMALE' FROM EMP WHERE SEX = 'F' ; --EX0053 CREATE VIEW EMPV AS SELECT EMPNO, LASTNAME, SALARY, COMM FROM EMP WHERE SALARY > 30000 WITH CHECK OPTION ; --EX0054 SELECT LASTNAME, SALARY, CASE JOB WHEN ‘MANAGER’ THEN ‘SUP’ WHEN ‘CLERK’ THEN ‘CLRK’ WHEN ‘PRES’ THEN ‘BIG CHEESE’ END FROM EMP ; --EX0055 SELECT LASTNAME, CASE WHEN SALARY = 41250 THEN NULL ELSE SALARY END FROM EMP ; --ex0056 UPDATE EMP SET SALARY = 0 WHERE LASTNAME = 'SMITH'; SELECT LASTNAME, COMM, SALARY FROM EMP WHERE (CASE WHEN SALARY = 0 THEN 0 ELSE COMM / SALARY END) > .0799999 ; ROLLBACK; --EX0057 SELECT HIREDATE + 5 YEARS FROM EMP; SELECT LASTNAME FROM EMP WHERE (CURRENT-DATE - HIREDATE) > 5 YEARS; UPDATE EMP SET HIREDATE = HIREDATE - 5 YEARS; Top of Page

List of books on JCL and other mainframe topics