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