First: Which of the following outputs would be produced when this SELECT is executed? Explain. (Note:...
Fantastic news! We've Found the answer you've been seeking!
Question:
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
Expert Answer:
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
Related Book For
Cost management a strategic approach
ISBN: 978-0073526942
5th edition
Authors: Edward J. Blocher, David E. Stout, Gary Cokins
Posted Date:
Students also viewed these computer network questions
-
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,...
-
C C C This is a typical blending model, where three inputs must be blended to produce four outputs. Any inputs not used in producing outputs can be sold for the salvage values shown. The percentages...
-
Question 4 A Bookmark this page Homework due Sep 14, 2021 18:00 +03 Consider the following aggregate production planning problem for earphones. The company manufactures four types of earphones, and...
-
In each of the homeowners forms, OA) the property coverage is the same OB) the liability coverage varies OC) both the property and liability coverage are the same OD) the property coverage varies
-
Simply Spa Collections had a current ratio of 2.5 to 1 on December 31 of the current year. On that date, the companys assets were as follows: Cash ................. $ 100,000 Accounts receivable...
-
Suppose that the function f(x,y) depends only on x. The differential equation y' = f(x,y) can then be written as y' = f(x), y(x0) = 0 Explain how to apply Euler's Method to this differential equation...
-
Find an article on tips for jury selection. What tips are provided? Are the tips helpful for determining questions to ask during jury voir dire?
-
Fun Ltd., a Texas company, is an expert in the assembly of a variety of video games consoles and they also offer repair parts for these systems. The products range from small handheld consoles that...
-
25-32 Explain, using Theorems 4, 5, 7, and 9, why the function is continuous at every number in its domain. State the domain. 2x2x1 25. F(x) = = 26. G(x) = x + 1 x-2 27. Q(x) = 28. R(t) = x- 2 29....
-
Suppose you are a bank lending officer at the Midtown National Bank considering a loan request from Miller Manufacturing company for $1.05 million. The firm currently has $1 million in equity and its...
-
During a continuing education seminar, a nurse learns about the idea of patient-centered language. As part of this concept, the person giving the seminar notes that instead of calling a patient who...
-
Crane Manufacturing Inc. provides you with the following data for the month of June: Prime costs were $ 1 9 5 , 0 0 0 , conversion costs were $ 1 4 0 , 2 0 0 , and total manufacturing costs incurred...
-
What are three diffrences between batch processing and real time processing accounting software for debit and credit jounal entries? Explain.
-
explain in five sentences and show the work : Janis Company recorded the following transactions in the current accounting period: Collected $200,000 from customers Paid $50,000 in salaries Paid...
-
Scott and Stacy are married, and they will file a joint return. Their modified adjusted gross income for the year is $185,000. They have an active participation rental real estate loss of $32,000. If...
-
Economic Analysis is a tool which may be used to select among project alternatives. Various Economic Indicators are created. 1. Define Benefit/Cost analysis. 2. What are some of the benefits to using...
-
When accounting for asset exchanges, if the fair values of either the asset given up or the asset received cannot be ascertained, then the Board of Directors may assign an arbitrary value. an asset...
-
In Exercises find dy/dx by implicit differentiation. xy - y = x
-
For what types of firms is target costing most appropriate and why?
-
Tinsley Inc. is an industry-leading cardboard manufacturer. You have been asked to determine a market value for the firm's equity. The firm has 100,000 shares outstanding, earnings per share of $2.50...
-
Can strategic performance measurement be used for service firms and not-for-profit organizations? How?
-
A common-size income statement for Liberty would report (amounts rounded) a. Net income of \(16 \%\) b. Cost of sales at \(36 \%\) c. Sales of \(100 \%\) d. All the above
-
During 2005 , Liberty's days' sales in receivables ratio was a. 39 day's b. 37 day's c. 35 day's d. 30 days
-
The company has 2,500 shares of common stock outstanding. What is Liberty's earnings per share? a. 2.04 b. 3.6 times c. \(\$ 1.22\) d. \(\$ 3.05\)
Study smarter with the SolutionInn App