Quiz #1
First: Which of the following outputs would be produced when this SELECT is executed? Explain. (Note: data shown
below in question #2!)
SQL> SELECT donor.idno, name, yrgoal, donation.driveno, contamt, drivename
2 FROM donation, donor, drive
3 WHERE donation.idno = donor.idno and donation.driveno = drive.driveno;
Output A:
IDNO NAME YRGOAL DRI CONTAMT DRIVENAME
----- --------------- --------- --- --------- ---------------
11111 Stephen Daniels 500 100 25 Animal Home
12121 Jennifer Ames 400 200 40 Animal Home
23456 Susan Ash 100 100 20 Animal Home
33333 Nancy Taylor 50 300 10 Animal Home
22222 Carl Hersey 100 10 Animal Home
12121 Jennifer Ames 400 100 50 Animal Home
11111 Stephen Daniels 500 200 35 Animal Home
23456 Susan Ash 100 300 10 Animal Home
Output B:
IDNO NAME YRGOAL DRI CONTAMT DRIVENAME
----- --------------- --------- --- --------- ---------------
23456 Susan Ash 100 100 20 Kids Shelter
23456 Susan Ash 100 100 20 Animal Home
23456 Susan Ash 100 100 20 Health Aid
23456 Susan Ash 100 100 20 Half Way
33333 Nancy Taylor 50 300 10 Kids Shelter
33333 Nancy Taylor 50 300 10 Animal Home
33333 Nancy Taylor 50 300 10 Health Aid
33333 Nancy Taylor 50 300 10 Half Way
23456 Susan Ash 100 300 10 Kids Shelter
23456 Susan Ash 100 300 10 Animal Home
23456 Susan Ash 100 300 10 Health Aid
23456 Susan Ash 100 300 10 Half Way
Output C:
IDNO NAME YRGOAL DRI CONTAMT DRIVENAME
----- --------------- --------- --- --------- ---------------
11111 Stephen Daniels 500 100 25 Kids Shelter
23456 Susan Ash 100 100 20 Kids Shelter
22222 Carl Hersey 100 10 Kids Shelter
12121 Jennifer Ames 400 100 50 Kids Shelter
12121 Jennifer Ames 400 200 40 Animal Home
11111 Stephen Daniels 500 200 35 Animal Home
33333 Nancy Taylor 50 300 10 Health Aid
23456 Susan Ash 100 300 10 Health Aid
Second: What would be produced? Illustrate and explain (data shown below).
SQL> SELECT name, yrgoal, contamt
2 FROM donor, donation
3 WHERE donor.idno = donation.idno AND contamt > yrgoal/12;
Donor table
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- -----------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa
Donation table (note that dri is actually driveno)
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
11111 200 12-JUN-99 35
12121 200 23-FEB-99 40
12121 100 04-JUN-99 50
22222 100 14-MAR-99 10
23456 100 03-MAR-99 20
23456 300 14-JUN-99 10
33333 300 10-MAR-99 10
Drive table (note that dri is actually driveno)
DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR
--- --------------- ------------ --------- ---------
100 Kids Shelter Ann Smith 10000 0
200 Animal Home Linda Grant 5000 0
300 Health Aid David Ross 7000 0
400 Half Way Robert Doe 0 0
Third: What would be produced? Explain! Note the tables below are sample tables that
come with Oracle.
SQL> SELECT ename, job, sal, comm, grade
2 FROM emp, salgrade
3 WHERE sal BETWEEN losal and hisal and sal > comm * 2;
Salgrade table
GRADE LOSAL HISAL
--------- --------- ---------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
Emp table
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Fourth: What would be produced? Explain for each of the three selects below. Note the table
is the donor table with data shown in #2 above.
SQL> SELECT contact, AVG(yrgoal)
2 FROM donor
3 GROUP BY contact;
SQL> SELECT state, contact, AVG(yrgoal)
2 FROM donor
3 GROUP BY state, contact;
SQL> select state, contact, avg(yrgoal)
2 from donor
3 group by state;
Fifth: Which of the following output would be produced from the SELECT below? What change could
be made to the code to produce the other results?
Table used:
SQL> SELECT * FROM first_pay;
PAY_ NAME JO STARTDATE SALARY BONUS
---- -------------------- -- --------- --------- ---------
1111 Linda Costa CI 15-JAN-97 45000 1000
2222 John Davidson IN 25-SEP-92 40000 1500
3333 Susan Ash AP 05-FEB-00 25000 500
4444 Stephen York CM 03-JUL-97 42000 2000
5555 Richard Jones CI 30-OCT-92 50000 2000
6666 Joanne Brown IN 18-AUG-94 48000 2000
7777 Donald Brown CI 05-NOV-99 45000
8888 Paula Adams IN 12-DEC-98 45000 2000
8 rows selected.
SQL> SELECT SUM(salary) "TOTAL SALARY",
2 SUM(DECODE(jobcode,'CI',salary)) "SUM CI",
3 COUNT(DECODE(jobcode,'CI',salary)) "COUNT CI",
4 SUM(DECODE(jobcode,'IN',salary)) "SUM IN",
5 COUNT(DECODE(jobcode,'IN',salary)) "COUNT IN",
6 SUM(DECODE(jobcode,'CM',salary)) "SUM CM",
7 COUNT(DECODE(jobcode,'CM',salary)) "COUNT CM",
8 SUM(DECODE(jobcode,'AP',salary)) "SUM AP",
9 COUNT(DECODE(jobcode,'AP',salary)) "COUNT AP"
10 FROM first_pay;
Output A:
TOTAL SALARY SUM CI COUNT CI SUM IN COUNT IN SUM CM COUNT CM SUM AP COUNT AP
------------ --------- --------- --------- --------- --------- --------- --------- ---------
25000 0 0 0 25000 1
140000 140000 3 0 0 0
42000 0 0 42000 1 0
133000 0 133000 3 0 0
Output B:
TOTAL SALARY SUM CI COUNT CI SUM IN COUNT IN SUM CM COUNT CM SUM AP COUNT AP
------------ --------- --------- --------- --------- --------- --------- --------- ---------
340000 140000 3 133000 3 42000 1 25000 1