First: Which of the following outputs would be produced when this SELECT is executed? Explain. (Note:...

Transcribed Image Text:

First: Which of the following outputs would be produced when this SELECT is executed? Explain. (Note: data shown below in question #2!) SQL SELECI 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 11111 Stephen Daniels 12121 Jennifer Ames 23456 Susan Ash 33333 Nancy Taylor 22222 Carl Hersey 12121 Jennifer Ames. 11111 Stephen Daniels 23456 Susan Ash Output B: IDNO NAME 23456 Susan Ash 23456 Susan Ash 23456 Susan Ash 23456 Susan Ash 33333 Nancy Taylor. 33333 Nancy Taylor. 33333 Nancy Taylor 33333 Nancy Taylor. 23456 Susan Ash 23456 Susan Ash 23456 Susan Ash 23456 Susan Ash Output C: IDNO NAME 11111 Stephen Daniels 196 23456 Susan Ash 22222 Carl Hersey 12121 Jennifer Ames 12121 Jennifer Ames 11111 Stephen Daniels 33333 Nancy Taylor. 23456 Susan Ash YRGOAL DRI CONTAMT DRIVENAME 500 100 400 200 100 100 50 300 100 400 100 500 200 100 300 YRGOAL DRI 100 100 100 100 100 100 100 100 50 300 50 300 50 300 50 300 100 300 100 300 100 300 100 300 YRGOAL DRI 500 100 100 100 100 400 100 400 200 500 200 50 300 100 300 25 Animal Home 40 Animal Home. 20 Animal Home 10 Animal Home 10 Animal Home 50 Animal Home 35 Animal Home. 10 Animal Home. CONTAMT DRIVENAME 20 Kids Shelter 20 Animal Home 20 Health Aid 20 Half Way 10 Kids Shelter 10 Animal Home 10 Health Aid 10 Half Way 10 Kids Shelter 10 Animal Home 10 Health Aid 10 Half Way CONTAMT DRIVENAME 25 Kids Shelter 20 Kids Shelter 10 Kids Shelter 50 Kids Shelter 40 Animal Home 35 Animal Home 10 Health Aid 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 11111 Stephen Daniels 123 Elm St 12121 Jennifer Ames 24 Benefit St 22222 Carl Hersey 24 Benefit St 23456 Susan Ash 21 Main St 33333 Nancy Taylor. 26 Oak St 34567 Robert Brooks 36 Pine St Donation table (note that dri is actually driveno) CONTAMT STADR IDNO DRI CONTDATE 11111 100 07-JAN-99 11111 200 12-JUN-99 12121 200 23-FEB-99 12121 100 04-JUN-99 22222 100 14-MAR-99 23456 100 03-MAR-99 23456 300 14-JUN-99 33333 300 10-MAR-99 25 35 40 50 10 20 10 10 CITY DRIVECHAIR Drive table (note that dri is actually driveno) DRI DRIVENAME 100 Kids Shelter 200 Animal Home 300 Health Aid 400 Half Way Ann Smith Linda Grant David Ros3 Robert Doe SI ZIP DATEFST Seekonk MA 02345 03-JUL-98 Providence RI 02045 24-MAY-97 Providence RI 02045 03-JAN-98 Fall River MA 02720 04-MAR-92 Fall River MA 02720 04-MAR-92 Fall River MA 02720 04-APR-98 LASTYEAR THISYEAR 10000 5000 7000 0 0000 YRGOAL CONTACT 500 John Smith 400 Susan Jones Susan Jones 100 Amy Costa 50 John Adams 50 Amy Costa 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 1 2 3 4 5 LOSAL Emp table EMPNO ENAME ---- 700 1201 1401 2001 3001 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER HISAL JOB 1200 1400 2000 3000 9999 CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK MGR HIREDATE 7902 17-DEC-80 7698 20-FEB-81 7698 22-FEB-81 7839 02-APR-81 7698 28-SEP-81 7839 01-MAY-81 ----- 7839 09-JUN-81 7566 19-APR-87 17-NOV-81 7698 08-SEP-81 7788 23-MAY-87 7698 03-DEC-81 7566 03-DEC-81 7782 23-JAN-82 SAL 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 COMM 300 500 1400 0 DEPTNO 20 30 30 20 30 30 10 20 10 30 20 30 20 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; SQL> select state, avg (yrgoal) from donor 3 where yrgoal > 100 4 group by state. 5 having state = 'MA'; 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 1111 Linda Costa 2222 John Davidson. 3333 Susan Ash 4444 Stephen York 5555 Richard Jones. 6666 Joanne Brown. 7777 Donald Brown. 8888 Paula Adams 8 rows selected. SQL SELECT SUM (salary) "TOTAL SALARY", 2 3 4 5 6 Output A: TOTAL SALARY 25000 140000 42000 133000 Output B: SUM (DECODE (jobcode, 'CI', salary)) "SUM CI", COUNT (DECODE (Jobcode, 'CI', salary)) "COUNT CI", SUM (DECODE (jobcode, 'IN', salary)) "SUM IN", COUNT (DECODE (jobcode, 'IN', salary)) "COUNT IN", SUM (DECODE (jobcode, 'CM', salary)) "SUM CM", COUNT (DECODE (jobcode, 'CM', salary)) "COUNT CM", SUM (DECODE (jobcode, 'AP', salary)) "SUM AP", COUNT (DECODE (jobcode, 'AP', salary)) "COUNT AP" 10 FROM first_pay; 7 8 9 TOTAL SALARY 340000 JO STARTDATE CI 15-JAN-97 IN 25-SEP-92 AP 05-FEB-00 CM 03-JUL-97 CI 30-OCT-92 IN 18-AUG-94 CI 05-NOV-99 IN 12-DEC-98 140000 SUM CI COUNT CI 140000 0 3 0 0 SUM CI COUNT CI 3 SUM IN SALARY 45000 40000 25000 42000 50000 48000 45000 45000 133000 COUNT IN 133000 0 0 0 3 SUM IN COUNT IN BONUS 3 1000 1500 500 2000 2000 2000 2000 SUM CM COUNT CM 42000 OOHO 42000 0 0 1 0 SUM CM COUNT CM 1 SUM AP COUNT AP 25000 24000 25000 1 SUM AP COUNT AP 1 First: Which of the following outputs would be produced when this SELECT is executed? Explain. (Note: data shown below in question #2!) SQL SELECI 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 11111 Stephen Daniels 12121 Jennifer Ames 23456 Susan Ash 33333 Nancy Taylor 22222 Carl Hersey 12121 Jennifer Ames. 11111 Stephen Daniels 23456 Susan Ash Output B: IDNO NAME 23456 Susan Ash 23456 Susan Ash 23456 Susan Ash 23456 Susan Ash 33333 Nancy Taylor. 33333 Nancy Taylor. 33333 Nancy Taylor 33333 Nancy Taylor. 23456 Susan Ash 23456 Susan Ash 23456 Susan Ash 23456 Susan Ash Output C: IDNO NAME 11111 Stephen Daniels 196 23456 Susan Ash 22222 Carl Hersey 12121 Jennifer Ames 12121 Jennifer Ames 11111 Stephen Daniels 33333 Nancy Taylor. 23456 Susan Ash YRGOAL DRI CONTAMT DRIVENAME 500 100 400 200 100 100 50 300 100 400 100 500 200 100 300 YRGOAL DRI 100 100 100 100 100 100 100 100 50 300 50 300 50 300 50 300 100 300 100 300 100 300 100 300 YRGOAL DRI 500 100 100 100 100 400 100 400 200 500 200 50 300 100 300 25 Animal Home 40 Animal Home. 20 Animal Home 10 Animal Home 10 Animal Home 50 Animal Home 35 Animal Home. 10 Animal Home. CONTAMT DRIVENAME 20 Kids Shelter 20 Animal Home 20 Health Aid 20 Half Way 10 Kids Shelter 10 Animal Home 10 Health Aid 10 Half Way 10 Kids Shelter 10 Animal Home 10 Health Aid 10 Half Way CONTAMT DRIVENAME 25 Kids Shelter 20 Kids Shelter 10 Kids Shelter 50 Kids Shelter 40 Animal Home 35 Animal Home 10 Health Aid 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 11111 Stephen Daniels 123 Elm St 12121 Jennifer Ames 24 Benefit St 22222 Carl Hersey 24 Benefit St 23456 Susan Ash 21 Main St 33333 Nancy Taylor. 26 Oak St 34567 Robert Brooks 36 Pine St Donation table (note that dri is actually driveno) CONTAMT STADR IDNO DRI CONTDATE 11111 100 07-JAN-99 11111 200 12-JUN-99 12121 200 23-FEB-99 12121 100 04-JUN-99 22222 100 14-MAR-99 23456 100 03-MAR-99 23456 300 14-JUN-99 33333 300 10-MAR-99 25 35 40 50 10 20 10 10 CITY DRIVECHAIR Drive table (note that dri is actually driveno) DRI DRIVENAME 100 Kids Shelter 200 Animal Home 300 Health Aid 400 Half Way Ann Smith Linda Grant David Ros3 Robert Doe SI ZIP DATEFST Seekonk MA 02345 03-JUL-98 Providence RI 02045 24-MAY-97 Providence RI 02045 03-JAN-98 Fall River MA 02720 04-MAR-92 Fall River MA 02720 04-MAR-92 Fall River MA 02720 04-APR-98 LASTYEAR THISYEAR 10000 5000 7000 0 0000 YRGOAL CONTACT 500 John Smith 400 Susan Jones Susan Jones 100 Amy Costa 50 John Adams 50 Amy Costa 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 1 2 3 4 5 LOSAL Emp table EMPNO ENAME ---- 700 1201 1401 2001 3001 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER HISAL JOB 1200 1400 2000 3000 9999 CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK MGR HIREDATE 7902 17-DEC-80 7698 20-FEB-81 7698 22-FEB-81 7839 02-APR-81 7698 28-SEP-81 7839 01-MAY-81 ----- 7839 09-JUN-81 7566 19-APR-87 17-NOV-81 7698 08-SEP-81 7788 23-MAY-87 7698 03-DEC-81 7566 03-DEC-81 7782 23-JAN-82 SAL 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 COMM 300 500 1400 0 DEPTNO 20 30 30 20 30 30 10 20 10 30 20 30 20 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; SQL> select state, avg (yrgoal) from donor 3 where yrgoal > 100 4 group by state. 5 having state = 'MA'; 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 1111 Linda Costa 2222 John Davidson. 3333 Susan Ash 4444 Stephen York 5555 Richard Jones. 6666 Joanne Brown. 7777 Donald Brown. 8888 Paula Adams 8 rows selected. SQL SELECT SUM (salary) "TOTAL SALARY", 2 3 4 5 6 Output A: TOTAL SALARY 25000 140000 42000 133000 Output B: SUM (DECODE (jobcode, 'CI', salary)) "SUM CI", COUNT (DECODE (Jobcode, 'CI', salary)) "COUNT CI", SUM (DECODE (jobcode, 'IN', salary)) "SUM IN", COUNT (DECODE (jobcode, 'IN', salary)) "COUNT IN", SUM (DECODE (jobcode, 'CM', salary)) "SUM CM", COUNT (DECODE (jobcode, 'CM', salary)) "COUNT CM", SUM (DECODE (jobcode, 'AP', salary)) "SUM AP", COUNT (DECODE (jobcode, 'AP', salary)) "COUNT AP" 10 FROM first_pay; 7 8 9 TOTAL SALARY 340000 JO STARTDATE CI 15-JAN-97 IN 25-SEP-92 AP 05-FEB-00 CM 03-JUL-97 CI 30-OCT-92 IN 18-AUG-94 CI 05-NOV-99 IN 12-DEC-98 140000 SUM CI COUNT CI 140000 0 3 0 0 SUM CI COUNT CI 3 SUM IN SALARY 45000 40000 25000 42000 50000 48000 45000 45000 133000 COUNT IN 133000 0 0 0 3 SUM IN COUNT IN BONUS 3 1000 1500 500 2000 2000 2000 2000 SUM CM COUNT CM 42000 OOHO 42000 0 0 1 0 SUM CM COUNT CM 1 SUM AP COUNT AP 25000 24000 25000 1 SUM AP COUNT AP 1

Answer rating: 100% (QA)

1 First we have create a table for donation donor drive salgrade employee and firstpay 2 Insert the values for the table donation donor drive salgrade firstpay and employee 3 Then fetch the sql query ... View the full answer