The American Programmer.com SQL Extra Workshops

The American Programmer

Home

Programming

Books for Computer Professionals

Privacy

Terms

Home > Programming > SQL Book > Supplementary

SQL Book

Supplementary Practice Problems

for a DB2 system

Section 1 Sample Tables

The tables are named STAFF, ORG, APPLICANT, DEPT, EMP

or Q.STAFF, Q.ORG, Q.APPLICANT, Q.DEPT, Q.EMP

or Userid.STAFF, Userid.ORG, Userid.APPLICANT, Userid.DEPT, Userid.EMP

SELECT * FROM STAFF;

ID NAME DEPT JOB YEARS SALARY COMM

10 SANDERS 20 MGR 7 18357.50 ---------

20 PERNAL 20 SALES 8 18171.25 612.45

30 MARENGHI 38 MGR 5 17506.75 ---------

40 O'BRIEN 38 SALES 6 18006.00 846.55

50 HANES 15 MGR 10 20659.80 ---------

60 QUIGLEY 38 SALES ------ 16808.30 650.25

70 ROTHMAN 15 SALES 7 16502.83 1152.00

80 JAMES 20 CLERK ------ 13504.60 128.20

90 KOONITZ 42 SALES 6 18001.75 1386.70

100 PLOTZ 42 MGR 7 18352.80 ---------

110 NGAN 15 CLERK 5 12508.20 206.60

120 NAUGHTON 38 CLERK ------ 12954.75 180.00

130 YAMAGUCHI 42 CLERK 6 10505.90 75.60

140 FRAYE 51 MGR 6 21150.00 ---------

150 WILLIAMS 51 SALES 6 19456.50 637.65

160 MOLINARE 10 MGR 7 22959.20 ---------

170 KERMISCH 15 CLERK 4 12258.50 110.10

180 ABRAHAMS 38 CLERK 3 12009.75 236.50

190 SNEIDER 20 CLERK 8 14252.75 126.50

200 SCOUTTEN 42 CLERK ------ 11508.60 84.20

210 LU 10 MGR 10 20010.00 ---------

220 SMITH 51 SALES 7 17654.50 992.80

230 LUNDQUIST 51 CLERK 3 13369.80 189.65

240 DANIELS 10 MGR 5 19260.25 ---------

250 WHEELER 51 CLERK 6 14460.00 513.30

260 JONES 10 MGR 12 21234.00 ---------

270 LEA 66 MGR 9 18555.50 ---------

280 WILSON 66 SALES 9 18674.50 811.50

290 QUILL 84 MGR 10 19818.00 ---------

300 DAVIS 84 SALES 5 15454.50 806.10

310 GRAHAM 66 SALES 13 21000.00 200.30

320 GONZALES 66 SALES 4 16858.20 844.00

330 BURKE 66 CLERK 1 10988.00 55.50

340 EDWARDS 84 SALES 7 17844.00 1285.00

350 GAFNEY 84 CLERK 5 13030.50 188.00

SELECT * FROM ORG;

DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION

10 HEAD OFFICE 160 CORPORATE NEW YORK

15 NEW ENGLAND 50 EASTERN BOSTON

20 MID ATLANTIC 10 EASTERN WASHINGTON

38 SOUTH ATLANTIC 30 EASTERN ATLANTA

42 GREAT LAKES 100 MIDWEST CHICAGO

51 PLAINS 140 MIDWEST DALLAS

66 PACIFIC 270 WESTERN SAN FRANCISCO

84 MOUNTAIN 290 WESTERN DENVER

SELECT * FROM APPLICANT;

TEMPID NAME ADDRESS EDLEVEL COMMENTS

400 FROMMHERZ SAN JOSE,CA 12 NO SALES EXPERIENCE

410 JACOBS POUGHKEEPSIE,NY 16 GOOD CANDIDATE FOR WASHINGTON

420 MONTEZ DALLAS,TX 13 OFFER SALES POSITION

430 RICHOWSKI TUCSON,AZ 14 CAN'T START WORK UNTIL 12/92

440 REID ENDICOTT,NY 14 1 YEAR SALES EXPERIENCE

450 JEFFREYS PHILADELPHIA,PA 12 GOOD CLERICAL BACKGROUND

460 STANLEY CHICAGO,IL 11 WANTS PARTIME JOB

470 CASALS PALO ALTO,CA 14 EXPERIENCED SALESMAN

480 LEEDS EAST FISHKILL,NY 12 NEEDS INTERVIEW WITH BROWN

490 GASPARD PARIS,TX 16 WORKED HERE FROM 1/90 TO 6/90

Section 2 SQL Workshops

Problem 1. Show all columns and all rows on APPLICANT

Expected results Problem 1:

TEMPID NAME ADDRESS EDLEVEL COMMENTS

400 FROMMHERZ SAN JOSE,CA 12 NO SALES EXPERIENCE

410 JACOBS POUGHKEEPSIE,NY 16 GOOD CANDIDATE FOR WASHINGTON

420 MONTEZ DALLAS,TX 13 OFFER SALES POSITION

430 RICHOWSKI TUCSON,AZ 14 CAN'T START WORK UNTIL 12/92

440 REID ENDICOTT,NY 14 1 YEAR SALES EXPERIENCE

450 JEFFREYS PHILADELPHIA,PA 12 GOOD CLERICAL BACKGROUND

460 STANLEY CHICAGO,IL 11 WANTS PARTIME JOB

470 CASALS PALO ALTO,CA 14 EXPERIENCED SALESMAN

480 LEEDS EAST FISHKILL,NY 12 NEEDS INTERVIEW WITH BROWN

490 GASPARD PARIS,TX 16 WORKED HERE FROM 1/90 TO 6/90

Problem 2. Show the columns tempid and name, and all rows on APPLICANT

Expected results Problem 2:

TEMPID NAME

400 FROMMHERZ

410 JACOBS

420 MONTEZ

430 RICHOWSKI

440 REID

450 JEFFREYS

460 STANLEY

470 CASALS

480 LEEDS

490 GASPARD

Problem 3. Show all columns and all rows on APPLICANT with no duplicates

Expected results Problem 3:

TEMPID NAME ADDRESS EDLEVEL COMMENTS

400 FROMMHERZ SAN JOSE,CA 12 NO SALES EXPERIENCE

410 JACOBS POUGHKEEPSIE,NY 16 GOOD CANDIDATE FOR WASHINGTON

420 MONTEZ DALLAS,TX 13 OFFER SALES POSITION

430 RICHOWSKI TUCSON,AZ 14 CAN'T START WORK UNTIL 12/92

440 REID ENDICOTT,NY 14 1 YEAR SALES EXPERIENCE

450 JEFFREYS PHILADELPHIA,PA 12 GOOD CLERICAL BACKGROUND

460 STANLEY CHICAGO,IL 11 WANTS PARTIME JOB

470 CASALS PALO ALTO,CA 14 EXPERIENCED SALESMAN

480 LEEDS EAST FISHKILL,NY 12 NEEDS INTERVIEW WITH BROWN

490 GASPARD PARIS,TX 16 WORKED HERE FROM 1/90 TO 6/90

Problem 4. Show the columns tempid and name, and all rows on APPLICANT with no duplicates

Expected results Problem 4:

TEMPID NAME

400 FROMMHERZ

410 JACOBS

420 MONTEZ

430 RICHOWSKI

440 REID

450 JEFFREYS

460 STANLEY

470 CASALS

480 LEEDS

490 GASPARD

Problem 5. Double everyone's education level on APPLICANT

Expected results Problem 5:

NAME

FROMMHERZ 24

JACOBS 32

MONTEZ 26

RICHOWSKI 28

REID 28

JEFFREYS 24

STANLEY 22

CASALS 28

LEEDS 24

GASPARD 32

Problem 6. Multiply everyone's commission by their years on STAFF

(this is not a meaningful number)

notice what happens to the people with a null commission

Expected results Problem 6:

NAME

SANDERS --------------

PERNAL 4899.60

MARENGHI --------------

O'BRIEN 5079.30

HANES --------------

QUIGLEY --------------

ROTHMAN 8064.00

JAMES --------------

KOONITZ 8320.20

PLOTZ --------------

More...

Problem 7. Glue everyone's name and comments together on APPLICANT

Expected results Problem 7:

FROMMHERZNO SALES EXPERIENCE

JACOBSGOOD CANDIDATE FOR WASHINGTON

MONTEZOFFER SALES POSITION

RICHOWSKICAN'T START WORK UNTIL 12/92

REID1 YEAR SALES EXPERIENCE

JEFFREYSGOOD CLERICAL BACKGROUND

STANLEYWANTS PARTIME JOB

CASALSEXPERIENCED SALESMAN

LEEDSNEEDS INTERVIEW WITH BROWN

GASPARDWORKED HERE FROM 1/90 TO 6/90

Problem 8. Find out if you can display a column twice. Can you?

the suggested answer will use STAFF, name, and name again

Expected results Problem 8:

NAME NAME

SANDERS SANDERS

PERNAL PERNAL

MARENGHI MARENGHI

O'BRIEN O'BRIEN

HANES HANES

QUIGLEY QUIGLEY

ROTHMAN ROTHMAN

JAMES JAMES

KOONITZ KOONITZ

PLOTZ PLOTZ

More...

Problem 9. Show names of managers and their jobs

Expected results Problem 9:

NAME JOB

SANDERS MGR

MARENGHI MGR

HANES MGR

PLOTZ MGR

FRAYE MGR

MOLINARE MGR

LU MGR

DANIELS MGR

JONES MGR

LEA MGR

QUILL MGR

Problem 10. Show names and salaries of clerks

Expected results Problem 10:

NAME SALARY

JAMES 13504.60

NGAN 12508.20

NAUGHTON 12954.75

YAMAGUCHI 10505.90

KERMISCH 12258.50

ABRAHAMS 12009.75

SNEIDER 14252.75

SCOUTTEN 11508.60

LUNDQUIST 13369.80

WHEELER 14460.00

BURKE 10988.00

Problem 11. Find out salaries of everyone in department 05

Expected results Problem 11:

NAME SALARY

DSNE610I NUMBER OF ROWS DISPLAYED IS 0

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Problem 12. How would you use O'BRIEN in a WHERE clause?

where name = ?

the answer will show you how, if you haven't already figured it out

Expected results Problem 12:

ID NAME DEPT JOB YEARS SALARY COMM

40 O'BRIEN 38 SALES 6 18006.00 846.55

Problem 13. Show department names in the Eastern division as well as those located in New York

Expected results Problem 13:

DEPTNAME DIVISION LOCATION

HEAD OFFICE CORPORATE NEW YORKNEW ENGLAND EASTERN BOSTON

MID ATLANTIC EASTERN WASHINGTON

SOUTH ATLANTIC EASTERN ATLANTA

Problem 14. Show applicants whose id is greater than 120 with an education level greater than 12

Expected results Problem 14:

TEMPID NAME ADDRESS EDLEVEL COMMENTS

410 JACOBS POUGHKEEPSIE,NY 16 GOOD CANDIDATE FOR WASHINGTON

420 MONTEZ DALLAS,TX 13 OFFER SALES POSITION

430 RICHOWSKI TUCSON,AZ 14 CAN'T START WORK UNTIL 12/92

440 REID ENDICOTT,NY 14 1 YEAR SALES EXPERIENCE

470 CASALS PALO ALTO,CA 14 EXPERIENCED SALESMAN

490 GASPARD PARIS,TX 16 WORKED HERE FROM 1/90 TO 6/90

Problem 15. Show department names for managers 160, 10, and 270

Expected results Problem 15:

DEPTNAME

HEAD OFFICE

MID ATLANTIC

PACIFIC

Problem 16. Show department names for departments located

in New York, Boston, and Dallas

Expected results Problem 16:

DEPTNAME

HEAD OFFICE

NEW ENGLAND

PLAINS

Problem 17. Display clerks and sales persons who are in department 66

Expected results Problem 17:

ID NAME DEPT JOB YEARS SALARY COMM

280 WILSON 66 SALES 9 18674.50 811.50

310 GRAHAM 66 SALES 13 21000.00 200.30

320 GONZALES 66 SALES 4 16858.20 844.00

330 BURKE 66 CLERK 1 10988.00 55.50

Problem 18. Show managers in department 20 as well as clerks in department 84

Expected results Problem 18:

ID NAME DEPT JOB YEARS SALARY COMM

10 SANDERS 20 MGR 7 18357.50 ---------

350 GAFNEY 84 CLERK 5 13030.50 188.00

Problem 19. Show managers in department 20 as well as clerks in department 84

but only if they make more than 18,000

Expected results Problem 19:

ID NAME DEPT JOB YEARS SALARY COMM

10 SANDERS 20 MGR 7 18357.50 ---------

Problem 20. Show applicant names with education levels 10, 12, 14

who live in SAN JOSE,CA or PALO ALTO,CA,

also show the applicant with tempid 490

(this last condition makes the query unrealistic,(dumb)

but just do it for practice)

Expected results Problem 20:

TEMPID NAME ADDRESS EDLEVEL COMMENTS

400 FROMMHERZ SAN JOSE,CA 12 NO SALES EXPERIENCE

470 CASALS PALO ALTO,CA 14 EXPERIENCED SALESMAN

490 GASPARD PARIS,TX 16 WORKED HERE FROM 1/90 TO 6/90

Problem 21. Are these two WHERE clauses going to produce the same results?

--show jobs other than MGR or SALES - don�t show MGR or SALES

WHERE NOT (JOB = 'MGR' OR JOB = 'SALES')

Problem 21A.

WHERE JOB <> 'MGR' AND JOB <> 'SALES'

Expected results Problem 21:

ID NAME DEPT JOB YEARS SALARY COMM

80 JAMES 20 CLERK --- 13504.60 128.20

110 NGAN 15 CLERK 5 12508.20 206.60

120 NAUGHTON 38 CLERK --- 12954.75 180.00

130 YAMAGUCHI 42 CLERK 6 10505.90 75.60

170 KERMISCH 15 CLERK 4 12258.50 110.10

180 ABRAHAMS 38 CLERK 3 12009.75 236.50

190 SNEIDER 20 CLERK 8 14252.75 126.50

200 SCOUTTEN 42 CLERK --- 11508.60 84.20

230 LUNDQUIST 51 CLERK 3 13369.80 189.65

250 WHEELER 51 CLERK 6 14460.00 513.30

330 BURKE 66 CLERK 1 10988.00 55.50

350 GAFNEY 84 CLERK 5 13030.50 188.00

Expected results Problem 21A:

ID NAME DEPT JOB YEARS SALARY COMM

80 JAMES 20 CLERK --- 13504.60 128.20

110 NGAN 15 CLERK 5 12508.20 206.60

120 NAUGHTON 38 CLERK --- 12954.75 180.00

130 YAMAGUCHI 42 CLERK 6 10505.90 75.60

170 KERMISCH 15 CLERK 4 12258.50 110.10

180 ABRAHAMS 38 CLERK 3 12009.75 236.50

190 SNEIDER 20 CLERK 8 14252.75 126.50

200 SCOUTTEN 42 CLERK --- 11508.60 84.20

230 LUNDQUIST 51 CLERK 3 13369.80 189.65

250 WHEELER 51 CLERK 6 14460.00 513.30

330 BURKE 66 CLERK 1 10988.00 55.50

350 GAFNEY 84 CLERK 5 13030.50 188.00

Problem 22. Is this right? (see the previous problem)

where job <> 'MGR' or job <> 'SALES'

Expected results Problem 22:

ID NAME DEPT JOB YEARS SALARY COMM

10 SANDERS 20 MGR 7 18357.50 ---------

20 PERNAL 20 SALES 8 18171.25 612.45

30 MARENGHI 38 MGR 5 17506.75 ---------

40 O'BRIEN 38 SALES 6 18006.00 846.55

50 HANES 15 MGR 10 20659.80 ---------

60 QUIGLEY 38 SALES --- 16808.30 650.25

More... (the entire table is selected)

Problem 23. Show all employees who earn a commission of 612.45

Expected results Problem 23:

ID NAME DEPT JOB YEARS SALARY COMM

20 PERNAL 20 SALES 8 18171.25 612.45

Problem 24. Show all employees who don't earn a commission of 612.45

Expected results Problem 24:

ID NAME DEPT JOB YEARS SALARY COMM

40 O'BRIEN 38 SALES 6 18006.00 846.55

60 QUIGLEY 38 SALES ------ 16808.30 650.25

70 ROTHMAN 15 SALES 7 16502.83 1152.00

80 JAMES 20 CLERK ------ 13504.60 128.20

90 KOONITZ 42 SALES 6 18001.75 1386.70

110 NGAN 15 CLERK 5 12508.20 206.60

120 NAUGHTON 38 CLERK ------ 12954.75 180.00

130 YAMAGUCHI 42 CLERK 6 10505.90 75.60

150 WILLIAMS 51 SALES 6 19456.50 637.65

170 KERMISCH 15 CLERK 4 12258.50 110.10

180 ABRAHAMS 38 CLERK 3 12009.75 236.50

190 SNEIDER 20 CLERK 8 14252.75 126.50

200 SCOUTTEN 42 CLERK ------ 11508.60 84.20

220 SMITH 51 SALES 7 17654.50 992.80

230 LUNDQUIST 51 CLERK 3 13369.80 189.65

250 WHEELER 51 CLERK 6 14460.00 513.30

280 WILSON 66 SALES 9 18674.50 811.50

300 DAVIS 84 SALES 5 15454.50 806.10

310 GRAHAM 66 SALES 13 21000.00 200.30

320 GONZALES 66 SALES 4 16858.20 844.00

330 BURKE 66 CLERK 1 10988.00 55.50

340 EDWARDS 84 SALES 7 17844.00 1285.00

350 GAFNEY 84 CLERK 5 13030.50 188.00

--... (PERNAL is not shown, rows with null COMM are not shown)

Problem 25. Show all employees whose time on the job is unknown.

Expected results Problem 25:

ID NAME DEPT JOB YEARS SALARY COMM

60 QUIGLEY 38 SALES ------ 16808.30 650.25

80 JAMES 20 CLERK ------ 13504.60 128.20

120 NAUGHTON 38 CLERK ------ 12954.75 180.00

200 SCOUTTEN 42 CLERK ------ 11508.60 84.20

Problem 26. Find out if each null is really distinct from the other nulls. Run this as shown:

SELECT DISTINCT YEARS

FROM STAFF

Expected results Problem 26:

YEARS

--------

Problem 27. Find out if you can add a null and get a realistic answer. (Yes or no)

--show name and total of salary and commission. Run this as shown:

SELECT NAME, SALARY + COMM

FROM STAFF

NAME

SANDERS ----------

PERNAL 18783.70

MARENGHI ----------

O'BRIEN 18852.55

HANES ----------

QUIGLEY 17458.55

ROTHMAN 17654.83

JAMES 13632.80

KOONITZ 19388.45

PLOTZ ----------

NGAN 12714.80

NAUGHTON 13134.75

More...

Problem 28. Show all employees whose name ends with S

Expected results Problem 28:

ID NAME DEPT JOB YEARS SALARY COMM

10 SANDERS 20 MGR 7 18357.50 ---------

50 HANES 15 MGR 10 20659.80 ---------

80 JAMES 20 CLERK --- 13504.60 128.20

150 WILLIAMS 51 SALES 6 19456.50 637.65

180 ABRAHAMS 38 CLERK 3 12009.75 236.50

240 DANIELS 10 MGR 5 19260.25 ---------

260 JONES 10 MGR 12 21234.00 ---------

300 DAVIS 84 SALES 5 15454.50 806.10

320 GONZALES 66 SALES 4 16858.20 844.00

340 EDWARDS 84 SALES 7 17844.00 1285.00

Problem 29. Show all employees whose job ends with S

Expected results Problem 29:

NAME JOB

PERNAL SALES

O'BRIEN SALES

QUIGLEY SALES

ROTHMAN SALES

KOONITZ SALES

WILLIAMS SALES

SMITH SALES

WILSON SALES

DAVIS SALES

GRAHAM SALES

GONZALES SALES

EDWARDS SALES

Problem 29A. Show all employees whose job ends with R

Expected results Problem 29A:

NAME JOB

SANDERS MGR

MARENGHI MGR

HANES MGR

PLOTZ MGR

FRAYE MGR

MOLINARE MGR

LU MGR

DANIELS MGR

JONES MGR

LEA MGR

QUILL MGR

Problem 30. Show all employees with two S's in their name

Expected results Problem 30:

ID NAME DEPT JOB YEARS SALARY COMM

10 SANDERS 20 MGR 7 18357.50 ---------

Problem 31. Show all employees with two consecutive L's in their name

Expected results Problem 31:

ID NAME DEPT JOB YEARS SALARY COMM

150 WILLIAMS 51 SALES 6 19456.50 637.65

290 QUILL 84 MGR 10 19818.00 ---------

Problem 32. Show all applicants who live in CA

Expected results Problem 32:

TEMPID NAME ADDRESS EDLEVEL COMMENTS

400 FROMMHERZ SAN JOSE,CA 12 NO SALES EXPERIENCE

470 CASALS PALO ALTO,CA 14 EXPERIENCED SALESMAN

Problem 33. Show all applicants with the word SALES in comments

Expected results Problem 33:

TEMPID NAME ADDRESS EDLEVEL COMMENTS

400 FROMMHERZ SAN JOSE,CA 12 NO SALES EXPERIENCE

420 MONTEZ DALLAS,TX 13 OFFER SALES POSITION

440 REID ENDICOTT,NY 14 1 YEAR SALES EXPERIENCE

470 CASALS PALO ALTO,CA 14 EXPERIENCED SALESMAN

Problem 34.What's the difference between these two (34 AND 34A)?: Run them to find out

SELECT *

FROM STAFF

WHERE NAME LIKE '%L ' -- NOTE THE SPACE AFTER L

-- ANY NAMES ENDING IN L SPACE?

-- NO, BECAUSE NAME IS VARCHAR (NO EXTRA SPACES)

Expected results Problem 34:

ID NAME DEPT JOB YEARS SALARY COMM

DSNE610I NUMBER OF ROWS DISPLAYED IS 0

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Problem 34a.

SELECT *

FROM STAFF

WHERE JOB LIKE '%R %' -- NOTE THE SPACE AFTER R

-- ANY JOBS CONTAINING R SPACE?

Expected results Problem 34a:

ID NAME DEPT JOB YEARS SALARY COMM

---------+---------+---------+---------+---------+---------+---------+

10 SANDERS 20 MGR 7 18357.50 ---------

30 MARENGHI 38 MGR 5 17506.75 ---------

50 HANES 15 MGR 10 20659.80 ---------

100 PLOTZ 42 MGR 7 18352.80 ---------

140 FRAYE 51 MGR 6 21150.00 ---------

160 MOLINARE 10 MGR 7 22959.20 ---------

210 LU 10 MGR 10 20010.00 ---------

240 DANIELS 10 MGR 5 19260.25 ---------

260 JONES 10 MGR 12 21234.00 ---------

270 LEA 66 MGR 9 18555.50 ---------

290 QUILL 84 MGR 10 19818.00 ---------

DSNE610I NUMBER OF ROWS DISPLAYED IS 11

What's the difference between these two (35 AND 35A)?: Run them to find out

Problem 35.

SELECT *

FROM STAFF

WHERE NAME LIKE '%U' -- NO SPACE AFTER U

-- ANY NAMES ENDING IN U?

-- YES, LU. NAME IS VARCHAR (NO EXTRA SPACES)

Expected results Problem 34:

ID NAME DEPT JOB YEARS SALARY COMM

210 LU 10 MGR 10 20010.00 -----

Problem 35a.

SELECT *

FROM STAFF

WHERE JOB LIKE '%R' -- NO SPACE AFTER R

-- ANY JOBS ENDING in R?

Expected results Problem 35a:

ID NAME DEPT JOB YEARS SALARY COMM

DSNE610I NUMBER OF ROWS DISPLAYED IS 0

Problem 36. what happens with this one?

SELECT *

FROM STAFF

WHERE YEARS BETWEEN 12 AND 7

Expected results Problem 36: You�ll have to run it to find out.

Problem 37. Show all employees who make anywhere between 13,000 and 22,000

Expected results Problem 37:

ID NAME DEPT JOB YEARS SALARY COMM

10 SANDERS 20 MGR 7 18357.50 ---------

20 PERNAL 20 SALES 8 18171.25 612.45

30 MARENGHI 38 MGR 5 17506.75 ---------

40 O'BRIEN 38 SALES 6 18006.00 846.55

50 HANES 15 MGR 10 20659.80 ---------

60 QUIGLEY 38 SALES ------ 16808.30 650.25

70 ROTHMAN 15 SALES 7 16502.83 1152.00

More...

Problem 38. Show all applicants with an education level of 12, 11, 99

Expected results Problem 38:

TEMPID NAME ADDRESS EDLEVEL COMMENTS

400 FROMMHERZ SAN JOSE,CA 12 NO SALES EXPERIENCE

450 JEFFREYS PHILADELPHIA,PA 12 GOOD CLERICAL BACKGROUND

460 STANLEY CHICAGO,IL 11 WANTS PARTIME JOB

480 LEEDS EAST FISHKILL,NY 12 NEEDS INTERVIEW WITH BROWN

Problem 39. Show all applicants in order by education level, highest first.

Expected results Problem 39:

NAME EDLEVEL

JACOBS 16

GASPARD 16

RICHOWSKI 14

REID 14

CASALS 14

MONTEZ 13

FROMMHERZ 12

LEEDS 12

JEFFREYS 12

STANLEY 11

Problem 40. Display the department names in order by manager.

Expected results Problem 40:

MANAGER DEPTNAME

10 MID ATLANTIC

30 SOUTH ATLANTIC

50 NEW ENGLAND

100 GREAT LAKES

140 PLAINS

160 HEAD OFFICE

270 PACIFIC

290 MOUNTAIN

Problem 41. Show the jobs that the company offers, without showing a job more than once.

Expected results Problem 41:

JOB

CLERK

MGR

SALES

Problem 42. 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

Expected results Problem 42:

JOB

CLERK

MGR

SALES

Problem 43. What divisions do people work in?

--show employee names, departments and divisions

Expected results Problem 43:

NAME DEPT DIVISION

MOLINARE 10 CORPORATE

LU 10 CORPORATE

DANIELS 10 CORPORATE

JONES 10 CORPORATE

HANES 15 EASTERN

ROTHMAN 15 EASTERN

NGAN 15 EASTERN

KERMISCH 15 EASTERN

SANDERS 20 EASTERN

PERNAL 20 EASTERN

JAMES 20 EASTERN

SNEIDER 20 EASTERN

MARENGHI 38 EASTERN

O'BRIEN 38 EASTERN

QUIGLEY 38 EASTERN

NAUGHTON 38 EASTERN

ABRAHAMS 38 EASTERN

KOONITZ 42 MIDWEST

PLOTZ 42 MIDWEST

More...

Problem 44. What divisions do managers work in?

--show manager names, department, division

Expected results Problem 44:

NAME DEPT DIVISION

MOLINARE 10 CORPORATE

DANIELS 10 CORPORATE

LU 10 CORPORATE

JONES 10 CORPORATE

HANES 15 EASTERN

SANDERS 20 EASTERN

MARENGHI 38 EASTERN

PLOTZ 42 MIDWEST

FRAYE 51 MIDWEST

LEA 66 WESTERN

QUILL 84 WESTERN

Problem 45. What division does Molinare work in?

--show MOLINARE�s department, division

Expected results Problem 45:

NAME DEPT DIVISION

MOLINARE 10 CORPORATE

What if you omit the join condition?

Problem 46. No join condition

Result is not shown (no room) you�ll have to do it.

SELECT *

FROM STAFF, ORG

Problem 47.

What does this return?

SELECT AVG(SALARY)

FROM STAFF

WHERE NAME = �BOOTS�

Problem 48. What is highest salary in each job? (group by)

Expected results Problem 47:

JOB

CLERK 14460.00

MGR 22959.20

SALES 21000.00

Problem 49. Which dept has the highest salary? Use group by but sort so highest salary shows first.

Expected results Problem 49:

DEPT

10 22959.20

51 21150.00

66 21000.00

15 20659.80

84 19818.00 20 18357.50

42 18352.80

38 18006.00

Problem 50. Show total of salary and commission, but substitute a zero for a null commission.

Expected results Problem 50:

NAME

18357.50 SANDERS

18783.70 PERNAL

17506.75 MARENGHI

18852.55 O'BRIEN

20659.80 HANES

17458.55 QUIGLEY

17654.83 ROTHMAN

13632.80 JAMES

19388.45 KOONITZ

18352.80 PLOTZ

More...

Problems 51, 52, 53.

Create the job, location and programs tables shown under the header Three Additional Training Tables..

You will very likely need to find out if you are authorized to create tables,

and the database and tablespace combination to use.

Note: executing in QMF may avoid the need for database and tablespace.

Do not define Primary keys or foreign keys.

Problem 54: What is the max salary that JONES can earn?

Problem 55: What is Sanders� zip code?

Problem 56: What is total rent paid for all sales people?

Problem 57: What zip codes do managers work in?

Problem 58: What are the names of the people who can earn a maximum of 90000?

Problem 59: What are the departments with zip 20002?

Problem 60: Who works in zip code 10493?

Problem 61: What is the total salary of all people whose minimum salary is 18000?

Problem 62: Who makes more than their maximum salary?. (A table join)

Problem 63: Who makes between their maximum salary and their minimum salary?

Problem 64: Who works at a place with a rent over 7500?

Problem 65: Which programs were done by managers?

Problem 66: What programs are worked on by people who work in zip 10493?

Problem 67: Are any ASM programs worked on by people who work in zip 20002?

Problem 68: What is the maximum salary of people whose programs abended more than 3 times?

Problem 69: Are all the programs complete that are worked on in zip 20002?

Problem 70: Who makes more than a peon�s Maximum salary?

Problem 71: What are the programs worked on by people whose Maximum salary

Is > 19020 and whose Mininum salary is < 19020?

Problem 72.What languages are used in programs written in Atlanta by people

Whose Maximum salary Is > 22000 and whose Mininum salary is < 22000?

Problem 73: Have all managers completed their programs?

Problem 74. an outer join

-- you may run this in SPUFI or QMF

-- you may need to specify a database and tablespace,

-- unless you run in QMF (QMF gives you a default)

CREATE TABLE players

(players_number smallint,

players_name char(15),

players_team char(15))

--IN database.tablespace �-specify yours here

CREATE TABLE WINNERS

(winners_number smallint,

winners_name char(15),

winners_team char(15))

--IN database.tablespace �-specify yours here

INSERT INTO players (players_number, players_name, players_team)

VALUES

(10, 'SCOTT', 'BEARS');

INSERT INTO players (players_number, players_name, players_team)

VALUES

(20,'JIM', 'COWBOYS');

INSERT INTO players (players_number, players_name, players_team)

VALUES

(30, 'JEN', 'PATS');

INSERT INTO WINNERS (winners_number, winners_name, winners_team)

VALUES

(10, 'SCOTT', 'BEARS');

--who played but didn't win

SELECT * FROM players p

left OUTER JOIN WINNERS W

ON p.players_NUMBER = W.WINNERS_NUMBER;

--the old way (try it too)

SELECT * from players p

WHERE NOT EXISTS

(SELECT * FROM WINNERS W

WHERE P.players_NUMBER = W.WINNERS_NUMBER);

--also run this

Select p.players_name, value(w.winners_name,'lost')

from players p

left outer join winners w

on p.players_number = w.winners_number

--please put the rollback in your sql in spufi

rollback;

Do with SQL and SPUFI

Check the results with a select!

Then Rollback after every one of these.

Problem 76.

Delete everyone who makes less than the min_sal for their job

Problem 77.

Delete the departments (ORG) that grunts work in.

Problem 78.

Any salesperson who makes less than their MAX_SAL change their department name to �CHEAP�.

Problem 79.

Double the rent for anyone whose programs have abended.

Problem 80.

Delete programs worked on by anyone whose maximum salary is greater than their actual salary.

Problem 81:

Create a view named STAFF_INFO that shows employee names, their MAX_SAL, their ZIP_CODE and their combined SALARY + COMM (null = 0).

Problem 82:

Select from the view STAFF_INFO all who make more than their MAX_SAL

Problem 83:

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")

Section 3 Answers to SQL Workshops

--PROBLEM 1. SHOW ALL COLUMNS AND ALL ROWS ON APPLICANT

SELECT *

FROM APPLICANT;

--PROBLEM 2. SHOW THE COLUMNS TEMPID AND NAME, AND ALL ROWS ON APPLICANT

SELECT TEMPID, NAME

FROM APPLICANT;

--PROBLEM 3. SHOW ALL COLUMNS AND ALL ROWS ON APPLICANT WITH NO DUPLICATES

SELECT DISTINCT *

FROM APPLICANT;

--PROBLEM 4. SHOW THE COLUMNS TEMPID AND NAME, AND ALL ROWS ON APPLICANT

-- WITH NO DUPLICATES

SELECT DISTINCT TEMPID, NAME

FROM APPLICANT;

--PROBLEM 5. DOUBLE EVERYONE'S EDUCATION LEVEL ON APPLICANT

SELECT NAME, EDLEVEL * 2

FROM APPLICANT;

--PROBLEM 6. MULTIPLY EVERYONE'S COMMISSION BY THEIR YEARS ON STAFF

-- (THIS IS NOT A MEANINGFUL NUMBER)

-- NOTICE WHAT HAPPENS TO THE PEOPLE WITH A NULL COMMISSION

SELECT NAME, COMM * YEARS

FROM STAFF;

--PROBLEM 7. GLUE EVERYONE'S NAME AND COMMENTS TOGETHER ON APPLICANT

SELECT NAME CONCAT COMMENTS

FROM APPLICANT;

--PROBLEM 8. FIND OUT IF YOU CAN DISPLAY A COLUMN TWICE. CAN YOU?

-- THE SUGGESTED ANSWER WILL USE STAFF, NAME, AND NAME AGAIN

SELECT NAME, NAME

FROM STAFF;

--PROBLEM 9. SHOW NAMES OF MANAGERS AND THEIR JOBS

SELECT NAME, JOB

FROM STAFF

WHERE JOB = 'MGR';

--PROBLEM 10. SHOW NAMES AND SALARIES OF CLERKS

SELECT NAME, SALARY

FROM STAFF

WHERE JOB = 'CLERK';

--PROBLEM 11. FIND OUT SALARIES OF EVERYONE IN DEPARTMENT 05

SELECT NAME, SALARY

FROM STAFF

WHERE DEPT = 05;

--PROBLEM 12. HOW WOULD YOU USE O'BRIEN IN A WHERE CLAUSE?

-- WHERE NAME = ?

-- THE ANSWER WILL SHOW YOU HOW, IF YOU HAVEN'T ALREADY

-- FIGURED IT OUT

SELECT *

FROM STAFF

WHERE NAME = 'O''BRIEN';

--PROBLEM 13. SHOW DEPARTMENT NAMES IN THE EASTERN DIVISION AS WELL AS THOSE LOCATED IN NEW YORK

SELECT DEPTNAME, DIVISION, LOCATION

FROM ORG

WHERE DIVISION = 'EASTERN'

OR LOCATION = �NEW YORK�;

--PROBLEM 14. SHOW APPLICANTS WHOSE ID IS GREATER THAN 120

-- WITH AN EDUCATION LEVEL GREATER THAN 12

SELECT *

FROM APPLICANT

WHERE TEMPID > 120

AND EDLEVEL > 12;

--PROBLEM 15. SHOW DEPARTMENT NAMES FOR MANAGERS 160, 10, AND 270

SELECT DEPTNAME

FROM ORG

WHERE MANAGER = 160

OR MANAGER = 10

OR MANAGER = 270;

-- Problem 15A ANOTHER WAY

SELECT DEPTNAME

FROM ORG

WHERE MANAGER IN (160, 10, 270);

--PROBLEM 16. SHOW DEPARTMENT NAMES FOR DEPARTMENTS LOCATED

-- IN NEW YORK, BOSTON, AND DALLAS

SELECT DEPTNAME

FROM ORG

WHERE LOCATION = 'NEW YORK'

OR LOCATION = 'BOSTON'

OR LOCATION = 'DALLAS';

--PROBLEM 17. DISPLAY CLERKS AND SALES PERSONS WHO ARE IN DEPARTMENT 66

SELECT *

FROM STAFF

WHERE DEPT = 66

AND (JOB = 'SALES' OR JOB = 'CLERK');

-- Problem 17A ANOTHER WAY

SELECT *

FROM STAFF

WHERE DEPT = 66

AND JOB IN ('SALES', 'CLERK');

--PROBLEM 18. SHOW MANAGERS IN DEPARTMENT 20

-- AS WELL AS CLERKS IN DEPARTMENT 84

SELECT *

FROM STAFF

WHERE (JOB = 'MGR' AND DEPT = 20)

OR (JOB = 'CLERK' AND DEPT = 84);

--PROBLEM 19. SHOW MANAGERS IN DEPARTMENT 20

-- AS WELL AS CLERKS IN DEPARTMENT 84

-- BUT ONLY IF THEY MAKE MORE THAN 18,000

SELECT *

FROM STAFF

WHERE SALARY > 18000

AND ((JOB = 'MGR' AND DEPT = 20)

OR (JOB = 'CLERK' AND DEPT = 84));

--PROBLEM 20. SHOW APPLICANT NAMES WITH EDUCATION LEVELS 10, 12, 14

-- WHO LIVE IN SAN JOSE, CA OR PALO ALTO, CA,

-- ALSO SHOW THE APPLICANT WITH TEMPID 490

-- (THIS LAST CONDITION MAKES THE QUERY UNREALISTIC,(DUMB)

-- BUT JUST DO IT FOR PRACTICE)

SELECT *

FROM APPLICANT

WHERE ((EDLEVEL = 10 OR EDLEVEL = 12 OR EDLEVEL = 14)

AND (ADDRESS = 'SAN JOSE,CA' OR ADDRESS = 'PALO ALTO,CA'))

OR TEMPID = 490;

--PROBLEM 21. ARE THESE 2 WHERE CLAUSES GOING TO PRODUCE THE SAME RESULTS?

-- WHERE NOT (JOB = 'MGR' OR JOB = 'SALES')

-- WHERE JOB <> 'MGR' AND JOB <> 'SALES'

SELECT *

FROM STAFF

WHERE NOT (JOB = 'MGR' OR JOB = 'SALES');

-- Problem 21A

SELECT *

FROM STAFF

WHERE JOB <> 'MGR' AND JOB <> 'SALES';

--PROBLEM 22. IS THIS RIGHT? (SEE THE PREVIOUS PROBLEM)

SELECT *

FROM STAFF

WHERE JOB <> 'MGR' OR JOB <> 'SALES';

--PROBLEM 23. SHOW ALL EMPLOYEES WHO EARN A COMMISSION OF 612.45

SELECT *

FROM STAFF

WHERE COMM = 612.45;

--PROBLEM 24. SHOW ALL EMPLOYEES WHO DON'T EARN A COMMISSION OF 612.45

SELECT *

FROM STAFF

WHERE COMM <> 612.45;

--PROBLEM 25. SHOW ALL EMPLOYEES WHOSE TIME ON THE JOB IS UNKNOWN.

SELECT *

FROM STAFF

WHERE YEARS IS NULL;

--PROBLEM 26. FIND OUT IF EACH NULL IS REALLY DISTINCT FROM THE OTHER NULLS

-- RUN THIS:

SELECT DISTINCT YEARS

FROM STAFF;

--PROBLEM 27. FIND OUT IF YOU CAN ADD A NULL AND GET A REALISTIC ANSWER

--RUN THIS:

SELECT NAME, SALARY + COMM

FROM STAFF;

--PROBLEM 28. SHOW ALL EMPLOYEES WHOSE NAME ENDS WITH S

SELECT *

FROM STAFF

WHERE NAME LIKE '%S';

--PROBLEM 29. SHOW ALL EMPLOYEES WHOSE JOB ENDS WITH S

SELECT NAME, JOB

FROM STAFF

WHERE JOB LIKE '%S';

--PROBLEM 29A. SHOW ALL EMPLOYEES WHOSE JOB ENDS WITH R

SELECT NAME, JOB

FROM STAFF

WHERE JOB LIKE '%R' --gets R in last position in column only

OR JOB LIKE '%R %' --space between R and %. Gets R with space after;

--PROBLEM 30. SHOW ALL EMPLOYEES WITH TWO S'S IN THEIR NAME

SELECT *

FROM STAFF

WHERE NAME LIKE '%S%S%';

--PROBLEM 31. SHOW ALL EMPLOYEES WITH TWO CONSECUTIVE L'S IN THEIR NAME

SELECT *

FROM STAFF

WHERE NAME LIKE '%LL%';

--PROBLEM 32. SHOW ALL APPLICANTS WHO LIVE IN CA

SELECT *

FROM APPLICANT

WHERE ADDRESS LIKE '%,CA%';

--PROBLEM 33. SHOW ALL APPLICANTS WITH THE WORD SALES IN COMMENTS

SELECT *

FROM APPLICANT

WHERE COMMENTS LIKE '%SALES%';

--WHAT'S THE DIFFERENCE BETWEEN THESE TWO (34 AND 34A)? RUN THEM TO FIND OUT

-- Problem 34.

SELECT *

FROM STAFF

WHERE NAME LIKE '%L ' -- NOTE THE SPACE AFTER L

-- ANY NAMES ENDING IN L SPACE?

-- NO, BECAUSE NAME IS VARCHAR (NO EXTRA SPACES)

-- Name is Varchar. There is no L space anywhere in the names

Problem 34a.

SELECT *

FROM STAFF

WHERE JOB LIKE '%R %' -- NOTE THE SPACE AFTER R

-- ANY JOBS CONTAINING R SPACE?

-- YES, MGR has two spaces following it

-- Problem 35

SELECT *

FROM STAFF

WHERE NAME LIKE '%U' -- NO SPACE AFTER U

-- ANY NAMES ENDING IN U?

-- YES, LU. NAME IS VARCHAR (NO EXTRA SPACES)

Problem 35a.

SELECT *

FROM STAFF

WHERE JOB LIKE '%R%' -- NO SPACE AFTER R

-- ANY JOBS ENDING in R?

-- NO. MGR actually is MGR space space, so no match

--the query says: "does any JOB column/field end in R?"

--Problem 36. WHAT HAPPENS WITH THIS ONE?

SELECT *

FROM STAFF

WHERE YEARS BETWEEN 12 AND 7;

--nothing retrieved. the 7 must be before the 12

--PROBLEM 37. SHOW ALL EMPLOYEES WHO MAKE ANYWHERE BETWEEN 13,000

-- AND 22,000

SELECT *

FROM STAFF

WHERE SALARY BETWEEN 13000 AND 22000;

--PROBLEM 38. SHOW ALL APPLICANTS WITH AN EDUCATION LEVEL OF 12, 11, 99

SELECT *

FROM APPLICANT

WHERE EDLEVEL IN (12, 11, 99);

--PROBLEM 39. SHOW ALL APPLICANTS IN ORDER BY EDUCATION LEVEL, HIGHEST FIRST.

SELECT NAME, EDLEVEL

FROM APPLICANT

ORDER BY EDLEVEL DESC;

--PROBLEM 40. DISPLAY THE DEPARTMENT NAMES IN ORDER BY MANAGER.

SELECT MANAGER, DEPTNAME

FROM ORG

ORDER BY MANAGER;

--PROBLEM 41. SHOW THE JOBS THAT THE COMPANY OFFERS, WITHOUT

-- SHOWING A JOB MORE THAN ONCE.

SELECT DISTINCT JOB

FROM STAFF;

--PROBLEM 42. 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 STAFF

ORDER BY JOB;

--PROBLEM 43. WHAT DIVISIONS DO PEOPLE WORK IN?

SELECT NAME, DEPT, DIVISION

FROM STAFF, ORG

WHERE DEPT = DEPTNUMB;

--PROBLEM 44. WHAT DIVISIONS DO MANAGERS WORK IN?

SELECT NAME, DEPT, DIVISION

FROM STAFF, ORG

WHERE DEPT = DEPTNUMB

AND JOB = 'MGR';

-- or, with qualifiers SELECT S.NAME, S.DEPT, O.DIVISION

FROM STAFF S, ORG O

WHERE S.DEPT = O.DEPTNUMB

AND S.JOB = 'MGR';

--PROBLEM 45. WHAT Division DOES MOLINARE WORK IN?

SELECT NAME, DEPT, DIVISION

FROM STAFF, ORG

WHERE DEPT = DEPTNUMB

AND NAME = 'MOLINARE';

-- 46 WHAT HAPPENS IF YOU OMIT THE JOIN CONDITION?

--PROBLEM 46. NO JOIN CONDITION

SELECT *

FROM STAFF, ORG;

--280 rows are selected

--every row of STAFF is thought to match every row of ORG

--the Cartesian Product

Problem 47. What does this return?

SELECT AVG(SALARY)

FROM STAFF

WHERE NAME = �BOOTS�

--a null. Notice that, because it makes you use a null-indicator

--in your embedded SQL in COBOL

--PROBLEM 48. WHAT IS HIGHEST SALARY IN EACH JOB? (GROUP BY)

SELECT JOB, MAX(SALARY)

FROM STAFF

GROUP BY JOB

ORDER BY JOB;

--PROBLEM 49. WHICH DEPT HAS THE HIGHEST SALARY? Use group by but sort so highest salary shows first.

SELECT DEPT, MAX(SALARY)

FROM STAFF

GROUP BY DEPT

ORDER BY 2 DESC;

--PROBLEM 50. SHOW TOTAL OF SALARY AND COMMISSION,

-- BUT SUBSTITUTE A ZERO FOR A NULL COMMISSION.

SELECT SALARY + VALUE(COMM,0), NAME

FROM STAFF;

PROBLEMS 51, 52, 53

--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(5),

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

-- IN DSNDB06.SYSDBASE -- THIS MAY BE YOUR DEFAULT

-- IN "DSQDBDEF"."DSQTSDEF" -- THIS MAY BE YOUR DEFAULT

-- IN EDUCTSDB.EDUCTS1

--IN DSQDBDEF.DSQTSDEF

;

INSERT INTO JOB

( JOB, MAX_SAL, MIN_SAL)

VALUES

('MGR', 90000, 40000);

INSERT INTO JOB

( JOB, MAX_SAL, MIN_SAL)

VALUES

('CLERK', 22000, 18000);

INSERT INTO JOB

( JOB, MAX_SAL, MIN_SAL)

VALUES

('SALES', 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

(

LOCATION VARCHAR(13),

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

-- IN DSNDB06.SYSDBASE -- THIS MAY BE YOUR DEFAULT

-- IN "DSQDBDEF"."DSQTSDEF" -- THIS MAY BE YOUR DEFAULT

-- IN EDUCTSDB.EDUCTS1

--IN DSQDBDEF.DSQTSDEF

;

INSERT INTO LOCATION

( LOCATION, ZIP_CODE, RENT)

VALUES

('NEW YORK', '10012', 9000);

INSERT INTO LOCATION

( LOCATION, ZIP_CODE, RENT)

VALUES

('BOSTON', '10493', 8000);

INSERT INTO LOCATION

( LOCATION, ZIP_CODE, RENT)

VALUES

('WASHINGTON', '20002', 7000);

INSERT INTO LOCATION

( LOCATION, ZIP_CODE, RENT)

VALUES

('BILOXI', '34976', 6000);

CREATE TABLE PROGRAMS

( PROGRAM_ID CHAR(8) NOT NULL,

LANGUAGE CHAR(5),

EMP_ID SMALLINT,

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

-- IN DSNDB06.SYSDBASE -- THIS MAY BE YOUR DEFAULT

-- IN "DSQDBDEF"."DSQTSDEF" -- THIS MAY BE YOUR DEFAULT

-- IN EDUCTSDB.EDUCTS1

--IN DSQDBDEF.DSQTSDEF

;

INSERT INTO PROGRAMS (PROGRAM_ID, LANGUAGE, EMP_ID, COMPLETE, ABENDS)

VALUES (

'WORK01', 'COBOL', 10, 'Y', 5);

INSERT INTO PROGRAMS (PROGRAM_ID, LANGUAGE, EMP_ID, COMPLETE, ABENDS)

VALUES (

'WORK02', 'COBOL', 120, 'N', 0);

INSERT INTO PROGRAMS (PROGRAM_ID, LANGUAGE, EMP_ID, COMPLETE, ABENDS)

VALUES (

'WORK10', 'COBOL', 200, 'N', 0);

INSERT INTO PROGRAMS (PROGRAM_ID, LANGUAGE, EMP_ID, COMPLETE, ABENDS)

VALUES (

'WORK12', 'ASM', 20, 'N', 0);

SELECT * FROM JOB;

SELECT * FROM LOCATION;

SELECT * FROM PROGRAMS;

--FOLLOWING IS OPTIONAL

GRANT ALL ON JOB TO PUBLIC;

GRANT ALL ON LOCATION TO PUBLIC;

GRANT ALL ON PROGRAMS TO PUBLIC;

Problem 54.

SELECT S.NAME, S.JOB, J.MAX_SAL

FROM STAFF S, JOB J

WHERE S.JOB = J.JOB

AND S.NAME = 'JONES'

;

Problem 55.

SELECT S.NAME, L.ZIP_CODE

FROM STAFF S, ORG O, LOCATION L

WHERE S.DEPT = DEPTNUMB

AND O.LOCATION = L.LOCATION

AND S.NAME = 'SANDERS'

;

Problem 56.

SELECT SUM(L.RENT)

FROM STAFF S, ORG O, LOCATION L

WHERE S.DEPT = O.DEPTNUMB

AND O.LOCATION = L.LOCATION

AND S.JOB = 'SALES'

;

Problem 57.

SELECT S.NAME, L.ZIP_CODE

FROM STAFF S, ORG O, LOCATION L

WHERE S.DEPT = O.DEPTNUMB

AND O.LOCATION = L.LOCATION

AND S.JOB = 'MGR'

;

Problem 58.

SELECT S.NAME

FROM STAFF S, JOB J

WHERE S.JOB = J.JOB

AND J.MAX_SAL = 90000

;

Problem 59.

SELECT O.DEPTNUMB

FROM ORG O, LOCATION L

WHERE O.LOCATION = L.LOCATION

AND L.ZIP_CODE = '20002'

;

Problem 60.

SELECT NAME

FROM STAFF S, ORG O, LOCATION L

WHERE S.DEPT = O.DEPTNUMB

AND O.LOCATION = L.LOCATION

AND L.ZIP_CODE = '10493'

;

Problem 61.

SELECT SUM(S.SALARY)

FROM STAFF S, JOB J

WHERE S.JOB = J.JOB

AND J.MIN_SAL = 18000

;

Problem 62.

SELECT J.MAX_SAL, S.NAME

FROM STAFF S, JOB J

WHERE S.JOB = J.JOB

AND S.SALARY > J.MAX_SAL

;

Problem 63. Who makes between their maximum salary and their minimum salary?

SELECT NAME, SALARY

FROM STAFF S, JOB J

WHERE S.JOB = J.JOB

AND S.SALARY BETWEEN J.MIN_SAL AND J.MAX_SAL

;

Problem 64. Who works at a place with a rent over 7500?

SELECT S.NAME, L.LOCATION, L.RENT

FROM STAFF S, ORG O, LOCATION L

WHERE S.DEPT = O.DEPTNUMB

AND O.LOCATION = L.LOCATION

AND L.RENT > 7500

;

--Problem 65. WHICH PROGRAMS WERE DONE BY MANAGERS?

SELECT P.PROGRAM_ID, S.NAME, S.JOB

FROM PROGRAMS P, STAFF S

WHERE P.EMP_ID = S.ID

AND S.JOB = 'MGR'

;

Problem 66. What programs are worked on by people who work in zip 10493?

SELECT P.PROGRAM_ID, S.NAME

FROM PROGRAMS P, STAFF S, ORG O, LOCATION L

WHERE P.EMP_ID = S.ID

AND S.DEPT = O.DEPTNUMB

AND O.LOCATION = L.LOCATION

AND L.ZIP_CODE = '10493'

;

Problem 67. Are any ASM programs worked on by people who work in zip 20002?

SELECT P.PROGRAM_ID, S.NAME

FROM PROGRAMS P, STAFF S, ORG O, LOCATION L

WHERE P.EMP_ID = S.ID

AND S.DEPT = O.DEPTNUMB

AND O.LOCATION = L.LOCATION

AND L.ZIP_CODE = '20002'

AND P.LANGUAGE = 'ASM'

;

Problem 68. What is the maximum salary of people whose programs abended more than 3 times?

SELECT S.NAME, J.MAX_SAL

FROM PROGRAMS P, STAFF S, JOB J

WHERE P.EMP_ID = S.ID

AND S.JOB = J.JOB

AND P.ABENDS > 3

;

Problem 69. Are all the programs complete that are worked on in zip 20002?

SELECT P.PROGRAM_ID, P.COMPLETE

FROM PROGRAMS P, STAFF S, ORG O, LOCATION L

WHERE P.EMP_ID = S.ID

AND S.DEPT = O.DEPTNUMB

AND O.LOCATION = L.LOCATION

AND L.ZIP_CODE = '20002'

;

Problem 70. Who makes more than a peon�s Maximum salary?

SELECT NAME FROM STAFF

WHERE SALARY >

(SELECT MAX_SAL FROM JOB WHERE JOB = 'PEON')

;

Problem 71: What are the programs worked on by people whose Maximum salary

Is > 19020 and whose Mininum salary is < 19020?

SELECT P.PROGRAM_ID, S.NAME

FROM PROGRAMS P, STAFF S, JOB J

WHERE P.EMP_ID = S.ID

AND S.JOB = J.JOB

AND 19020 BETWEEN J.MIN_SAL AND J.MAX_SAL

;

Problem 72.What languages are used in programs written in Atlanta by people

Whose Maximum salary Is > 22000 and whose Mininum salary is < 22000?

SELECT P.LANGUAGE

FROM PROGRAMS P, STAFF S, JOB J, ORG O

WHERE P.EMP_ID = S.ID

AND S.JOB = J.JOB

AND S.DEPT = O.DEPTNUMB

AND J.MIN_SAL <= 22000

AND O.LOCATION = �ATLANTA�

;

Problem 73. Have all managers completed their programs?

SELECT P.PROGRAM_ID, P.COMPLETE

FROM PROGRAMS P, STAFF S

WHERE P.EMP_ID = S.ID

AND S.JOB = 'MGR'

;

Problem 74. Create and run the SQL that is shown.

Problem 75. Create and run the SQL that is shown.

Problem 76. Delete everyone who makes less than the min_sal for their job

DELETE FROM STAFF S

WHERE SALARY < (SELECT MIN_SAL FROM JOB J WHERE S.JOB = J.JOB)

;

Problem 77. Delete the departments (ORG) that grunts work in.

DELETE FROM ORG

WHERE DEPTNUMB IN

(SELECT DEPT FROM STAFF WHERE JOB = 'GRUNT')

;

Problem 78. Any salesperson who makes less than their MAX_SAL change their department name to �CHEAP�.

UPDATE ORG

SET DEPTNAME = 'CHEAP'

WHERE DEPTNUMB IN

(SELECT S.DEPT FROM STAFF S, JOB J

WHERE S.JOB = J.JOB

AND S.JOB = 'SALES'

AND S.SALARY < J.MAX_SAL)

;

Problem 79. Double the rent for anyone whose programs have abended.

UPDATE LOCATION

SET RENT = RENT * 2

WHERE LOCATION IN

(SELECT O.LOCATION FROM ORG O, PROGRAMS P, STAFF S

WHERE O.DEPTNUMB = S.DEPT

AND S.ID = P.EMP_ID

AND P.ABENDS > 0)

;

Problem 80. 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 S.ID

FROM STAFF S, JOB J

WHERE S.JOB = J.JOB

AND J.MAX_SAL > S.SALARY)

;

Problem 81:

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

SELECT S.NAME, J.MAX_SAL, L.ZIP_CODE, VALUE(S.SALARY + S.COMM, 0)

FROM STAFF S, JOB J, ORG O, LOCATION L

WHERE S.JOB = J.JOB

AND S.DEPT = O.DEPTNUMB

AND O.LOCATION = L.LOCATION

Problem 82:

Select from the view STAFF_INFO all who make more than their MAX_SAL

SELECT *

FROM STAFF_INFO

WHERE SALARY > MAX_SAL

Problem 83:

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)

SELECT NAME, MAX_SAL - (SALARY + COMM)

FROM STAFF_INFO

--or�

CREATE VIEW STAFF_DEJA_VIEW

SELECT NAME, MAX_SAL - SALARY_COMM AS NEXT_RAISE

FROM STAFF_INFO