📄 apf.htm
字号:
various functions. The <TT>GROUP BY</TT> clause acts like the <TT>ORDER BY </TT>clause
in that it orders the results of the query in the order the columns are listed in
the <TT>GROUP BY</TT>.</P>
<P><B>3.</B> Will this <TT>SELECT</TT> work?
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> SELECT NAME, AVG(SALARY), DEPARTMENT
FROM PAY_TBL
WHERE DEPARTMENT = 'ACCOUNTING'
ORDER BY NAME
GROUP BY DEPARTMENT, SALARY;</FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>No, the syntax is incorrect. The GROUP BY must come before the <TT>ORDER BY</TT>.
Also, all the selected columns must be listed in the <TT>GROUP BY</TT>.
<P><B>4. </B>When using the <TT>HAVING</TT> clause, do you always have to use a <TT>GROUP
BY</TT> also?<BR>
Yes.</P>
<P><B>5. </B>Can you use <TT>ORDER BY</TT> on a column that is not one of the columns
in the <TT>SELECT</TT> statement?</P>
<P>Yes, it is not necessary to use the <TT>SELECT</TT> statement on a column that
you put in the <TT>ORDER BY</TT> clause.
</DL>
<H3><FONT COLOR="#000077">Exercise Answers</FONT></H3>
<DL>
<DD><B>1.</B> Using the <TT>ORGCHART</TT> table from the preceding examples, find
out how many people on each team have <TT>30</TT> or more days of sick leave.
<P>Here is your baseline that shows how many folks are on each team.</P>
<H5>INPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, COUNT(TEAM)
FROM ORGCHART
GROUP BY TEAM;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">TEAM COUNT
=============== ===========
COLLECTIONS 2
MARKETING 3
PR 1
RESEARCH 2</FONT></PRE>
</BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"></FONT></PRE>
<DL>
<DD>Compare it to the query that solves the question:
<H5>INPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, COUNT(TEAM)
FROM ORGCHART
WHERE SICKLEAVE >=30
GROUP BY TEAM;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">TEAM COUNT
=============== ===========
COLLECTIONS 1
MARKETING 1
RESEARCH 1</FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>The output shows the number of people on each <TT>team</TT> with a <TT>SICKLEAVE</TT>
balance of <TT>30</TT> days or more.
<P><B>2. </B>Using the <TT>CHECKS</TT> table, write a <TT>SELECT</TT> that will return
the following:</P>
<H5>OUTPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">CHECK#_____PAYEE_______AMOUNT__
1 MA BELL 150</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT CHECK#, PAYEE, AMOUNT
FROM CHECKS
WHERE CHECK# = 1;</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>You can get the same results in several ways. Can you think of some more?
</DL>
<H2><FONT COLOR="#000077">Day 6, "Joining Tables"</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>
<DL>
<DD><B>1.</B> How many rows would a two-table join produce if one table had 50,000
rows and the other had 100,000?
<P>5,000,000,000 rows.</P>
<P><B>2.</B> What type of join appears in the following select statement?
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"> select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where e.employee_id = ep.employee_id;</FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>The preceding join is an equi-join. You are matching all the <TT>employee_id</TT>s
in the two tables.
<P><B>3. </B>Will the following <TT>SELECT</TT> statements work?
</DL>
<PRE><FONT COLOR="#0066FF"> select name, employee_id, salary
from employee_tbl e,
employee_pay_tbl ep
where employee_id = employee_id
and name like '%MITH';
</FONT></PRE>
<DL>
<DD>No. The columns and tables are not properly named. Remember column and table
aliases.
</DL>
<PRE><FONT COLOR="#0066FF"> select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where name like '%MITH';
</FONT></PRE>
<DL>
<DD>No. The <TT>join</TT> command is missing in the <TT>where</TT> clause.
</DL>
<PRE><FONT COLOR="#0066FF"> select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where e.employee_id = ep.employee_id
and e.name like '%MITH';
</FONT></PRE>
<DL>
<DD>Yes. The syntax is correct.
<P><B>4. </B>In the <TT>WHERE</TT> clause, when joining the tables, should you do
the join first or the conditions?</P>
<P>The joins should go before the conditions.</P>
<P><B>5. </B>In joining tables are you limited to one-column joins, or can you join
on more than one column?</P>
<P>You can join on more than one column. You may be forced to join on multiple columns
depending on what makes a row of data unique or the specific conditions you want
to place on the data to be retrieved.
</DL>
<H3><FONT COLOR="#000077">Exercise Answers</FONT></H3>
<DL>
<DD><B>1. </B>In the section on joining tables to themselves, the last example returned
two combinations. Rewrite the query so only one entry comes up for each redundant
part number.
<H5>INPUT/OUTPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B>SELECT F.PARTNUM, F.DESCRIPTION,
S.PARTNUM,S.DESCRIPTION
FROM PART F, PART S
WHERE F.PARTNUM = S.PARTNUM
AND F.DESCRIPTION <> S.DESCRIPTION
AND F.DESCRIPTION > S.DESCRIPTION</B>
PARTNUM DESCRIPTION PARTNUM DESCRIPTION
========== ================ =========== ====================
</FONT></PRE>
<PRE><FONT COLOR="#0066FF"> 76 ROAD BIKE 76 CLIPPLESS SHOE</FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>2. </B>Rewrite the following query to make it more readable and shorter.
<H5>INPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"> <B>select orders.orderedon, orders.name, part.partnum,
part.price, part.description from orders, part
where orders.partnum = part.partnum and orders.orderedon
between '1-SEP-96' and '30-SEP-96'
order by part.partnum;</B></FONT></PRE>
<P><FONT COLOR="#000000"><I>Answer:</I></FONT></P>
<PRE><FONT COLOR="#0066FF">SQL> <B>select o.orderedon ORDER_DATE, o.name NAME, p.partnum PART#,
p.price PRICE, p.description DESCRIPTION
from orders o,
part p
where o.partnum = p.partnum
and o.orderedon like '%SEP%'
order by ORDER_DATE;</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>3. </B>From the <TT>PART</TT> table and the <TT>ORDERS</TT> table, make up
a query that will return the following:
<H5>OUTPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">ORDEREDON NAME PARTNUM QUANTITY
================== ================== ======= ========
2-SEP-96 TRUE WHEEL 10 1</FONT></PRE>
<P><FONT COLOR="#000000"><I>Answer:</I></FONT></P>
<PRE><FONT COLOR="#0066FF"> <B>select o.orderedon ORDEREDON, o.name NAME, p.partnum PARTNUM, o.quanity QUANITY
from orders o,
part p
where o.partnum = p.partnum
and o.orderedon like '%SEP%';</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>Many other queries will also work.
</DL>
<H2><FONT COLOR="#000077">Day 7, "Subqueries: The Embedded SELECT Statement"</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>
<DL>
<DD><B>1. </B>In the section on nested subqueries, the sample subquery returned several
values:
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE</FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>Some of these are duplicates. Why aren't these duplicates in the final result
set?<BR>
The result set has no duplicates because the query that called the subquery
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SELECT ALL C.NAME, C.ADDRESS, C.STATE,C.ZIP
FROM CUSTOMER C
WHERE C.NAME IN</FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>returned only the rows where <TT>NAME</TT> was in the list examined by the statement
<TT>IN</TT>. Don't confuse this simple <TT>IN</TT> statement with the more complex
join.
<P><B>2. </B>Are the following statements true or false?</P>
<P>The aggregate functions <TT>SUM</TT>, <TT>COUNT</TT>, <TT>MIN</TT>, <TT>MAX</TT>,
and <TT>AVG</TT> all return multiple values.<BR>
False. They all return a single value.</P>
<P>The maximum number of subqueries that can be nested is two.<BR>
False. The limit is a function of your implementation.<BR>
Correlated subqueries are completely self-contained.<BR>
False. Correlated subqueries enable you to use an outside reference.</P>
<P><B>3. </B>Will the following subqueries work using the <TT>ORDERS</TT> table and
the <TT>PART</TT> table?</P>
<H5>INPUT/OUTPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"> SQL> <B>SELECT *
FROM PART;</B>
PARTNUM DESCRIPTION PRICE
54 PEDALS 54.25
42 SEATS 24.50
46 TIRES 15.25
23 MOUNTAIN BIKE 350.45
76 ROAD BIKE 530.00
10 TANDEM 1200.00
6 rows selected.
</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> SQL> <B>SELECT *
FROM ORDERS;</B>
ORDEREDON NAME PARTNUM QUANITY REMARKS
15-MAY-96 TRUE WHEEL 23 6 PAID
19-MAY-96 TRUE WHEEL 76 3 PAID
2-SEP-96 TRUE WHEEL 10 1 PAID
30-JUN-96 BIKE SPEC 54 10 PAID
30-MAY-96 BIKE SPEC 10 2 PAID
30-MAY-96 BIKE SPEC 23 8 PAID
17-JAN-96 BIKE SPEC 76 11 PAID
17-JAN-96 LE SHOPPE 76 5 PAID
1-JUN-96 LE SHOPPE 10 3 PAID
1-JUN-96 AAA BIKE 10 1 PAID
1-JUN-96 AAA BIKE 76 4 PAID
1-JUN-96 AAA BIKE 46 14 PAID
11-JUL-96 JACKS BIKE 76 14 PAID
13 rows selected.
</FONT></PRE>
<P><FONT COLOR="#000000">a. </FONT><FONT COLOR="#0066FF"><TT>SQL> <B>SELECT *
FROM ORDERS<BR>
WHERE PARTNUM =<BR>
SELECT PARTNUM FROM PART<BR>
WHERE DESCRIPTION = 'TRUE WHEEL';</B></TT></FONT></P>
</BLOCKQUOTE>
<DL>
<DD>No. Missing the parenthesis around the subquery.
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<P><FONT COLOR="#000000">b. </FONT><FONT COLOR="#0066FF"><TT>SQL> <B>SELECT PARTNUM<BR>
FROM ORDERS<BR>
WHERE PARTNUM =<BR>
(SELECT * FROM PART<BR>
WHERE DESCRIPTION = 'LE SHOPPE');</B></TT></FONT></P>
</BLOCKQUOTE>
<DL>
<DD>No. The SQL engine cannot correlate all the columns in the <TT>part</TT> table
with the operator <TT>=</TT>.
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<P><FONT COLOR="#000000">c. </FONT><FONT COLOR="#0066FF"><TT>SQL> <B>SELECT NAME,
PARTNUM<BR>
FROM ORDERS<BR>
WHERE EXISTS<BR>
(SELECT * FROM ORDERS<BR>
WHERE NAME = 'TRUE WHEEL');</B></TT></FONT></P>
</BLOCKQUOTE>
<DL>
<DD>Yes. This subquery is correct.
</DL>
<H3><FONT COLOR="#000077">Exercise Answer</FONT></H3>
<P>Write a query using the table <TT>ORDERS</TT> to return all the <TT>NAME</TT>s
and <TT>ORDEREDON</TT> dates for every store that comes after <TT>JACKS BIKE</TT>
in the alphabet.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME >
(SELECT NAME
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -