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;
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
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
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
SQL SELECT name, yrgoal, contamt 2 FROM donor, donation 3 WHERE donor.idno = donation.idno AND contamt > yrgoal/12;
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
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
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
SQL SELECT ename, job, sal, comm, grade 2 FROM emp, salgrade 3 WHERE sal BETWEEN losal and hisal and sal > comm * 2;
GRADE LOSAL HISAL
--------- --------- ---------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
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
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;
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;
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
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