Books on DB2 and SQL DB2 and SQL Manuals Solutions to Practice Problems This accompanies the SQL course Problem 1. Show all columns and all rows on DEPT. SELECT * FROM DEPT; Problem 2. Show the columns EMPNO and LASTNAME and all rows on EMP. SELECT EMPNO, LASTNAME FROM EMP; Problem 3. Show all columns and all rows on DEPT with no duplicates. SELECT DISTINCT * FROM DEPT; Problem 4. Show the columns EMPNO and LASTNAME, and all rows on EMP with no duplicates. SELECT DISTINCT EMPNO, LASTNAME FROM EMP; Problem 5. Double everyone's education level on EMP. SELECT LASTNAME, EDLEVEL * 2 FROM EMP; Problem 6. Multiply everyone's commission by their EDLEVEL on EMP (this is not a meaningful number). SELECT LASTNAME, COMM * EDLEVEL FROM EMP; Problem 7. Glue everyone's first name and last name together on EMP. SELECT FIRSTNME CONCAT LASTNAME FROM EMP; Problem 8. Find out if you can display a column twice. Can you? The suggested answer will use EMP, JOB and JOB again. SELECT JOB, JOB FROM EMP; Problem 9. Show names of managers and their jobs. SELECT LASTNAME, JOB FROM EMP WHERE JOB = 'MANAGER'; Problem 10. Show names and salaries of clerks. SELECT LASTNAME, SALARY FROM EMP WHERE JOB = 'CLERK'; Problem 11. Find out salaries of everyone in department B01. SELECT LASTNAME, SALARY FROM EMP WHERE WORKDEPT = 'B01'; Problem 12. How would you use O'CONNELL in a WHERE clause? where lastname = ? the answer will show you how, if you haven't already figured it out. SELECT FIRSTNME, LASTNAME FROM EMP WHERE LASTNAME = 'O''CONNELL';  Problem 13. Show department names for Admin Dept A00 and D01. SELECT DEPTNAME, DEPTNO, ADMRDEPT FROM DEPT WHERE ADMRDEPT = 'A00' OR ADMRDEPT = 'D01'; Problem 14. Show applicants whose EMPNO is greater than 200 with an education level greater than 12. SELECT LASTNAME, EMPNO, EDLEVEL FROM EMP WHERE EMPNO > '000200' AND EDLEVEL > 12; Problem 15. Show department names for managers 000020, 000060,000090. SELECT DEPTNAME, MGRNO FROM DEPT WHERE MGRNO = '000020' OR MGRNO = '000060' OR MGRNO = '000090'; -- PROBLEM 15A ANOTHER WAY SELECT DEPTNAME, MGRNO FROM DEPT WHERE MGRNO IN ('000020', '000060', '000090'); Problem 16. Show department names for Departments A00, B02. SELECT DEPTNAME, DEPTNO FROM DEPT WHERE DEPTNO = 'A00' OR DEPTNO = 'B02'; Problem 17. Display clerks and sales persons who are in department D21. SELECT LASTNAME, WORKDEPT, JOB FROM EMP WHERE WORKDEPT = 'D21' AND (JOB = 'SALESREP' OR JOB = 'CLERK'); -- PROBLEM 17A ANOTHER WAY SELECT LASTNAME, WORKDEPT, JOB FROM EMP WHERE WORKDEPT = 'D21' AND JOB IN ('SALESREP', 'CLERK'); Problem 18. Show managers in department D21 as well as clerks in department A00. SELECT LASTNAME, WORKDEPT, JOB FROM EMP WHERE (JOB = 'MANAGER' AND WORKDEPT = 'D21') OR (JOB = 'CLERK' AND WORKDEPT = 'A00'); Problem 19. Show managers in department A00 as well as Designers in department D11 but only if they make more than 25,000. SELECT LASTNAME, WORKDEPT, JOB, SALARY FROM EMP WHERE SALARY > 25000 AND ((JOB = 'MANAGER' AND WORKDEPT = 'A00') OR (JOB = 'DESIGNER' AND WORKDEPT = 'D11'));  Problem 20. Show employee names with education levels 10, 12, 16 with a middle initial A or B also show the employee with employee number 220 (this last condition makes the query unrealistic, but just do it for practice). SELECT LASTNAME, EDLEVEL, MIDINIT, EMPNO FROM EMP WHERE ((EDLEVEL = 10 OR EDLEVEL = 12 OR EDLEVEL = 16) AND (MIDINIT = 'A' OR MIDINIT = 'B')) OR EMPNO = '000220'; Problem 21. Are these two WHERE clauses going to produce the same results? (See Problem 21A). WHERE NOT (JOB = 'MANAGER' OR JOB = 'SALESREP') SELECT LASTNAME, JOB FROM EMP WHERE NOT (JOB = 'MANAGER' OR JOB = 'SALESREP'); Problem 21A. WHERE JOB <> 'MANAGER' AND JOB <> 'SALESREP' SELECT LASTNAME, JOB FROM EMP WHERE JOB <> 'MANAGER' AND JOB <> 'SALESREP'; Problem 22. Is this right? (see the previous problem). where job <> 'MANAGER' or job <> 'SALESREP' SELECT LASTNAME, JOB FROM EMP WHERE JOB <> 'MANAGER' OR JOB <> 'SALESREP'; --It’s not right. This selects all rows with no restrictions. Problem 23. Show all departments with manager number 10. SELECT DEPTNAME, MGRNO FROM DEPT WHERE MGRNO = '000010'; Problem 24. Show all departments with manager number other than 10. SELECT DEPTNAME, MGRNO FROM DEPT WHERE MGRNO <> '000010'; Problem 25. Show all departments with unknown manager numbers. SELECT DEPTNAME, MGRNO FROM DEPT WHERE MGRNO IS NULL; Problem 26. Show all employees whose name ends with S. SELECT LASTNAME FROM EMP WHERE LASTNAME LIKE '%S'; Problem 27. Show all employees whose job ends with K. SELECT LASTNAME, JOB FROM EMP WHERE JOB LIKE '%K';  Problem 27A. Show all employees whose job ends with S. SELECT LASTNAME, JOB FROM EMP WHERE JOB LIKE '%S' --GETS S IN LAST POSITION IN COLUMN ONLY OR JOB LIKE '%S %'; --SPACE BETWEEN S AND %. GETS S WITH SPACE AFTER; Problem 28. Show all employees with two O's in their name. SELECT LASTNAME FROM EMP WHERE LASTNAME LIKE '%O%O%'; Problem 29. Show all employees with two consecutive L's in their name. SELECT LASTNAME FROM EMP WHERE LASTNAME LIKE '%LL%'; Problem 30. Show all department names containing the word RAT. SELECT * FROM DEPT WHERE DEPTNAME LIKE '%RAT%'; Problem 31.What's the difference between these two (31 AND 31A)?: Run them to find out. SELECT LASTNAME FROM EMP WHERE LASTNAME LIKE '%S '; -- NOTE THE SPACE AFTER S -- ANY NAMES ENDING IN S SPACE? -- NO, BECAUSE NAME IS VARCHAR (NO EXTRA SPACES) -- NAME IS VARCHAR. THERE IS NO S SPACE ANYWHERE IN THE NAMES Problem 31A. SELECT LASTNAME, JOB FROM EMP WHERE JOB LIKE '%S %'; -- NOTE THE SPACE AFTER S -- ANY JOBS CONTAINING S SPACE? -- YES, PRES HAS 4 SPACES FOLLOWING IT What's the difference between these two (32 AND 32A)?: Run them to find out. Problem 32. SELECT LASTNAME FROM EMP WHERE LASTNAME LIKE '%Z'; -- NO SPACE AFTER Z -- ANY NAMES ENDING IN Z? -- YES, LUTZ. NAME IS VARCHAR (NO EXTRA SPACES) Problem 32A. SELECT LASTNAME, JOB FROM EMP WHERE JOB LIKE '%R'; -- NO SPACE AFTER R- ANY JOBS ENDING IN R? -- DESIGNERS ARE SELECTED, NOT MANAGERS -- MANAGER ACTUALLY IS MANAGER SPACE, SO NO MATCH. -- THE QUERY SAYS: "DOES ANY JOB COLUMN/FIELD END IN R?"  Problem 33. what happens with this one? SELECT LASTNAME, EDLEVEL FROM EMP WHERE EDLEVEL BETWEEN 12 AND 7; --NOTHING RETRIEVED. THE 7 MUST BE BEFORE THE 12 Problem 34. Show all employees who make anywhere between 40,000 and 50,000. SELECT LASTNAME, SALARY FROM EMP WHERE SALARY BETWEEN 40000 AND 50000; Problem 35. Show all employees with an education level of 12, 11, 99. SELECT LASTNAME, EDLEVEL FROM EMP WHERE EDLEVEL IN (12, 11, 99); Problem 36. Show all employees in order by education level, highest first. SELECT LASTNAME, EDLEVEL FROM EMP ORDER BY EDLEVEL DESC; Problem 37. Display the department names in order by manager. SELECT MGRNO, DEPTNAME FROM DEPT ORDER BY MGRNO; Problem 38. Show the jobs that the company offers, without showing a job more than once. SELECT DISTINCT JOB FROM EMP; Problem 39. Now do the previous with an ORDER BY. notice any difference? (there isn't any, DISTINCT will sort too) But remember the rule, if you want a sort, specify ORDER BY. SELECT DISTINCT JOB FROM EMP ORDER BY JOB; Problem 40. What administrative departments do people work in? Show employee names, work departments and admistrative departments. SELECT LASTNAME, WORKDEPT, ADMRDEPT FROM EMP, DEPT WHERE WORKDEPT = DEPTNO; Problem 41. What administrative departments do managers work in? Show manager last names, work department, administrative departments. SELECT LASTNAME, WORKDEPT, ADMRDEPT FROM EMP, DEPT WHERE WORKDEPT = DEPTNO AND JOB = 'MANAGER'; -- OR, WITH QUALIFIERS SELECT E.LASTNAME, E.WORKDEPT, D.ADMRDEPT FROM EMP E, DEPT D WHERE E.WORKDEPT = D.DEPTNO AND E.JOB = 'MANAGER';  Problem 42. What administrative department does Kwan work in? Show lastname, work department, administrative department. SELECT LASTNAME, WORKDEPT, ADMRDEPT FROM EMP, DEPT WHERE WORKDEPT = DEPTNO AND LASTNAME = 'KWAN'; Problem 43. What administrative department does the President work in? Show lastname, job, administrative department. SELECT LASTNAME, JOB, ADMRDEPT FROM EMP, DEPT WHERE WORKDEPT = DEPTNO AND JOB = ‘PRES’; Problem 44. No join condition What if you omit the join condition? SELECT * FROM EMP, DEPT; --EVERY ROW OF EMP IS THOUGHT TO MATCH EVERY ROW OF DEPT --THE CARTESIAN PRODUCT --TOO MANY ROWS ARE SELECTED. THE RESULTS ARE MEANINGLESS Problem 45. Create and execute the SQL shown. An outer join. Problem 46. What does this return? SELECT AVG(SALARY) FROM EMP WHERE LASTNAME = ‘BOOTS’; --A NULL. NOTICE THAT, BECAUSE IT MAKES YOU USE A NULL-INDICATOR --IN YOUR EMBEDDED SQL IN COBOL. Problem 47. What is highest salary in each job? (group by). SELECT JOB, MAX(SALARY) FROM EMP GROUP BY JOB ORDER BY JOB; Problem 48. Which dept has the highest salary? Use group by but sort so highest salary shows first. SELECT WORKDEPT, MAX(SALARY) FROM EMP GROUP BY WORKDEPT ORDER BY 2 DESC; Problem 49. Show total of salary and commission, but substitute a zero for a null commission. (There are currently no Nulls in EMP Table). SELECT SALARY + VALUE(COMM,0), LASTNAME FROM EMP; Problems 50, 51, 52 Create the JOB, LOCATION and PROGRAMS Tables shown here. --THIS IS USED TO CREATE 3 NEW SAMPLE TESTING TABLES -- JOB, LOCATION, PROGRAMS -- DROP TABLE JOB ;-- REMOVE LINE IF NOT NEEDED -- DROP TABLE LOCATION;-- REMOVE LINE IF NOT NEEDED -- DROP TABLE PROGRAMS;-- REMOVE LINE IF NOT NEEDED  CREATE TABLE JOB ( JOB CHAR(8), MAX_SAL DECIMAL(7,2), MIN_SAL DECIMAL(7,2) ) --REPLACE YOUR-DATABASE WITH THE NAME OF YOUR DATABASE --REPLACE YOUR-TABLESPACE WITH THE NAME OF YOUR TABLESPACE -- IN DATABASE.YOUR-TABLESPACE INSERT INTO JOB ( JOB, MAX_SAL, MIN_SAL) VALUES ('MANAGER', 90000, 40000); INSERT INTO JOB ( JOB, MAX_SAL, MIN_SAL) VALUES ('CLERK', 22000, 18000); INSERT INTO JOB ( JOB, MAX_SAL, MIN_SAL) VALUES ('SALESREP', 24000, 19000); INSERT INTO JOB ( JOB, MAX_SAL, MIN_SAL) VALUES ('PRES', 97000, 96000); INSERT INTO JOB ( JOB, MAX_SAL, MIN_SAL) VALUES ('GRUNT', 10000, 07000); INSERT INTO JOB ( JOB, MAX_SAL, MIN_SAL) VALUES ('PEON', 2000, 1000); CREATE TABLE LOCATION ( ADMRDEPT CHAR(3), ZIP_CODE CHAR(5), RENT DECIMAL(7,2) ) --REPLACE YOUR-DATABASE WITH THE NAME OF YOUR DATABASE --REPLACE YOUR-TABLESPACE WITH THE NAME OF YOUR TABLESPACE -- IN DATABASE.YOUR-TABLESPACE ; INSERT INTO LOCATION ( ADMRDEPT, ZIP_CODE, RENT) VALUES ('A00', '10012', 9000);  INSERT INTO LOCATION ( ADMRDEPT, ZIP_CODE, RENT) VALUES ('D01', '10493', 8000); INSERT INTO LOCATION ( ADMRDEPT, ZIP_CODE, RENT) VALUES ('E01', '20002', 7000); INSERT INTO LOCATION ( ADMRDEPT, ZIP_CODE, RENT) VALUES ('F01', '34976', 6000); CREATE TABLE PROGRAMS ( PROGRAM_ID CHAR(8) NOT NULL, LANGUAGE CHAR(5), EMP_ID CHAR(6) , COMPLETE CHAR(1), ABENDS SMALLINT) --REPLACE YOUR-DATABASE WITH THE NAME OF YOUR DATABASE --REPLACE YOUR-TABLESPACE WITH THE NAME OF YOUR TABLESPACE -- IN DATABASE.YOUR-TABLESPACE ; INSERT INTO PROGRAMS (PROGRAM_ID, LANGUAGE, EMP_ID, COMPLETE, ABENDS) VALUES ( 'WORK01', 'COBOL', '000010', 'Y', 5); INSERT INTO PROGRAMS (PROGRAM_ID, LANGUAGE, EMP_ID, COMPLETE, ABENDS) VALUES ( 'WORK02', 'COBOL', '000120', 'N', 0); INSERT INTO PROGRAMS (PROGRAM_ID, LANGUAGE, EMP_ID, COMPLETE, ABENDS) VALUES ( 'WORK10', 'COBOL', '000200', 'N', 0); INSERT INTO PROGRAMS (PROGRAM_ID, LANGUAGE, EMP_ID, COMPLETE, ABENDS) VALUES ( 'WORK12', 'ASM', '000020', 'N', 0); Problem 53: What is the max salary that KWAN can earn? SELECT E.LASTNAME, E.JOB, J.MAX_SAL FROM EMP E, JOB J WHERE E.JOB = J.JOB AND E.LASTNAME = 'KWAN'; Problem 54: What is HAAS zip code? SELECT E.LASTNAME, L.ZIP_CODE FROM EMP E, DEPT D, LOCATION L WHERE E.WORKDEPT = D.DEPTNO AND D.ADMRDEPT = L.ADMRDEPT AND E.LASTNAME = 'BROWN';  Problem 55: What is total rent paid for all sales people? SELECT SUM(L.RENT) FROM EMP E, DEPT D, LOCATION L WHERE E.WORKDEPT = D.DEPTNO AND D.ADMRDEPT = L.ADMRDEPT AND E.JOB = 'SALESREP'; Problem 56: What zip codes do managers work in? SELECT E.LASTNAME, L.ZIP_CODE FROM EMP E, DEPT D, LOCATION L WHERE E.WORKDEPT = D.DEPTNO AND D.ADMRDEPT = L.ADMRDEPT AND E.JOB = 'MANAGER'; Problem 57: What are the names of the people who can earn a maximum of 90000? SELECT E.LASTNAME FROM EMP E, JOB J WHERE E.JOB = J.JOB AND J.MAX_SAL = 90000; Problem 58: What are the departments with zip 20002? SELECT D.DEPTNO FROM DEPT D, LOCATION L WHERE D.ADMRDEPT = L.ADMRDEPT AND L.ZIP_CODE = '20002'; Problem 59: Who works in zip code 10493? SELECT D.DEPTNO FROM DEPT D, LOCATION L WHERE D.ADMRDEPT = L.ADMRDEPT AND L.ZIP_CODE = '20002'; Problem 60: What is the total salary of all people whose minimum salary is 18000? SELECT SUM(E.SALARY) FROM EMP E, JOB J WHERE E.JOB = J.JOB AND J.MIN_SAL = 18000; Problem 61: Who makes more than the maximum salary that they are allowed? (A Table join). SELECT J.MAX_SAL, E.LASTNAME FROM EMP E, JOB J WHERE E.JOB = J.JOB AND E.SALARY > J.MAX_SAL; Problem 62: Who makes between their maximum salary and their minimum salary? SELECT E.LASTNAME, E.SALARY FROM EMP E, JOB J WHERE E.JOB = J.JOB AND E.SALARY BETWEEN J.MIN_SAL AND J.MAX_SAL;  Problem 63: Who works at a place with a rent over 7500? SELECT E.LASTNAME, L.ADMRDEPT, L.RENT FROM EMP E, DEPT D, LOCATION L WHERE E.WORKDEPT = D.DEPTNO AND D.ADMRDEPT = L.ADMRDEPT AND L.RENT > 7500; Problem 64: Which programs were done by managers? SELECT P.PROGRAM_ID, E.LASTNAME, E.JOB FROM PROGRAMS P, EMP E WHERE P.EMP_ID = E.EMPNO AND E.JOB = 'MANAGER'; Problem 65: What programs are worked on by people who work in zip 10493? SELECT P.PROGRAM_ID, E.LASTNAME FROM PROGRAMS P, EMP E, DEPT D, LOCATION L WHERE P.EMP_ID = E.EMPNO AND E.WORKDEPT = D.DEPTNO AND D.ADMRDEPT = L.ADMRDEPT AND L.ZIP_CODE = '10493'; Problem 66: Are any ASM programs worked on by people who work in zip 20002? No. SELECT P.PROGRAM_ID, E.LASTNAME FROM PROGRAMS P, EMP E, DEPT D, LOCATION L WHERE P.EMP_ID = E.EMPNO AND E.WORKDEPT = D.DEPTNO AND D.ADMRDEPT = L.ADMRDEPT AND L.ZIP_CODE = '20002' AND P.LANGUAGE = 'ASM'; Problem 67: What is the maximum salary of people whose programs abended more than 3 times? SELECT E.LASTNAME, J.MAX_SAL FROM PROGRAMS P, EMP E, JOB J WHERE P.EMP_ID = E.EMPNO AND E.JOB = J.JOB AND P.ABENDS > 3; Problem 68: Are all the programs complete that are worked on in zip 20002? No. FROM PROGRAMS P, EMP E, DEPT D, LOCATION L WHERE P.EMP_ID = E.EMPNO AND E.WORKDEPT = D.DEPTNO AND D.ADMRDEPT = L.ADMRDEPT AND L.ZIP_CODE = '20002'; Problem 69: Who makes more than a peon’s highest possible salary? SELECT LASTNAME FROM EMP WHERE SALARY > (SELECT MAX_SAL FROM JOB WHERE JOB = 'PEON'); Problem 70: What are the programs worked on by people who are allowed to make 10920? SELECT P.PROGRAM_ID, E.LASTNAME FROM PROGRAMS P, EMP E, JOB J WHERE P.EMP_ID = E.EMPNO AND E.JOB = J.JOB AND 19020 BETWEEN J.MIN_SAL AND J.MAX_SAL;  Problem 71: What languages are used in programs written by people in Administrative department A00 by people who can make at least 22000? SELECT P.LANGUAGE FROM PROGRAMS P, EMP E, JOB J, DEPT D WHERE P.EMP_ID = E.EMPNO AND E.JOB = J.JOB AND E.WORKDEPT = D.DEPTNO AND J.MIN_SAL <= 22000 AND D.ADMRDEPT = 'A00'; Problem 72: Have all managers completed their programs? None selected. SELECT P.PROGRAM_ID, P.COMPLETE FROM PROGRAMS P, EMP E WHERE P.EMP_ID = E.EMPNO AND E.JOB = 'MANAGER' AND P.COMPLETE = 'Y'; Problem 73. For this and the following problems: Check the results with a select! Then Rollback. Delete everyone who makes less than the min_sal for their job. DELETE FROM EMP E WHERE SALARY < (SELECT MIN_SAL FROM JOB J WHERE E.JOB = J.JOB); Problem 74. Delete the departments (DEPT) that grunts work in. DELETE FROM DEPT WHERE DEPTNO IN (SELECT WORKDEPT FROM EMP WHERE JOB = 'GRUNT'); -- NO ONE, AT PRESENT Problem 75. Any salesperson who makes less than their MAX_SAL change their department name to ‘CHEAP’. UPDATE DEPT SET DEPTNAME = 'CHEAP' WHERE DEPTNO IN (SELECT E.WORKDEPT FROM EMP E, JOB J WHERE E.JOB = J.JOB AND E.JOB = 'SALESREP' AND E.SALARY < J.MAX_SAL); Problem 76. Double the rent for anyone whose programs have abended. UPDATE LOCATION SET RENT = RENT * 2 WHERE ADMRDEPT IN (SELECT D.ADMRDEPT FROM DEPT D, PROGRAMS P, EMP E WHERE D.DEPTNO = E.WORKDEPT AND E.EMPNO = P.EMP_ID AND P.ABENDS > 0);  Problem 77. Delete programs worked on by anyone whose maximum salary is greater than their actual salary. DELETE FROM PROGRAMS P WHERE P.EMP_ID IN (SELECT E.EMPNO FROM EMP E, JOB J WHERE E.JOB = J.JOB AND J.MAX_SAL > E.SALARY); Problem 78: Create a View named STAFF_INFO that shows employee names, their MAX_SAL, their ZIP_CODE and their combined SALARY + COMM (null = 0). CREATE VIEW STAFF_INFO (EMPNAME, MAX_SAL, ZIP_CODE, SALARY_COMM)--OTHER NAMES OK TOO AS SELECT E.LASTNAME, J.MAX_SAL, L.ZIP_CODE, VALUE(E.SALARY + E.COMM, 0) FROM EMP E, JOB J, DEPT D, LOCATION L WHERE E.JOB = J.JOB AND E.WORKDEPT = D.DEPTNO AND D.ADMRDEPT = L.ADMRDEPT; Problem 79: Select from the View STAFF_INFO all who make more than their MAX_SAL SELECT * FROM STAFF_INFO WHERE SALARY_COMM > MAX_SAL; Problem 80: Create a View named STAFF_DEJA_VIEW on the View STAFF_INFO . It will show name, MAX_SAL minus combined SALARY + COMM. (Name that field “NEXT_RAISE”). CREATE VIEW STAFF_DEJA_VIEW (EMPNAME, NEXT_RAISE) AS SELECT EMPNAME, MAX_SAL - SALARY_COMM FROM STAFF_INFO; --OR— CREATE VIEW STAFF_DEJA_VIEW2 AS SELECT EMPNAME, MAX_SAL - SALARY_COMM AS NEXT_RAISE FROM STAFF_INFO; Top of Page

List of books on JCL and other mainframe topics