📄 apf.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Appendix F -- Answers to Quizzes and Exercises</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="ape.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ape.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG
SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Contents"
BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Appendix F -<BR>
Answers to Quizzes and Exercises</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077"><BR>
Day 1, "Introduction to SQL"</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>
<DL>
<DD><B>1. </B>What makes SQL a nonprocedural language?<BR>
SQL determines what should be done, not how it should be done. The database must
implement the SQL request. This feature is a big plus in cross-platform, cross-language
development.
<P><B>2.</B> How can you tell whether a database is truly relational?<BR>
Apply Dr. Codd's 12 (we know there are 13) rules.</P>
<P><B>3.</B> What can you do with SQL?<BR>
SQL enables you to select, insert, modify, and delete the information in a database;
perform system security functions and set user permissions on tables and databases;
handle online transaction processing within an application; create stored procedures
and triggers to reduce application coding; and transfer data between different databases.</P>
<P><B>4.</B> Name the process that separates data into distinct, unique sets.<BR>
Normalization reduces the amount of repetition and complexity of the structure of
the previous level.
</DL>
<H3><FONT COLOR="#000077">Exercise Answer</FONT></H3>
<P>Determine whether the database you use at work or at home is truly relational.<BR>
(On your own.)
<H2><FONT COLOR="#000077">Day 2, "Introduction to the Query: The <TT>SELECT
</TT>Statement"</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>
<DL>
<DD><B>1.</B> Do the following statements return the same or different output:
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SELECT * FROM CHECKS;
select * from checks;?</FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>The only difference between the two statements is that one statement is in lowercase
and the other uppercase. Case sensitivity is not normally a factor in the syntax
of SQL. However, be aware of capitalization when dealing with data.
<P><B>2.</B> None of the following queries work. Why not?</P>
<P><B>a.</B> <TT>Select *</TT><BR>
The FROM clause is missing. The two mandatory components of a <TT>SELECT</TT> statement
are the <TT>SELECT</TT> and <TT>FROM</TT>.</P>
<P><B>b.</B> <TT>Select * from checks</TT><BR>
The semicolon, which identifies the end of a SQL statement, is missing.</P>
<P><B>c.</B> <TT>Select amount name payee FROM checks;</TT><BR>
You need a comma between each column name: <TT>Select amount, name, payee FROM checks;</TT></P>
<P><B>3.</B> Which of the following SQL statements will work?</P>
<P><B>a.</B> <TT>select *</TT><BR>
<TT> from checks;</TT></P>
<P><B>b.</B> select * from checks;</P>
<P><B>c.</B> <TT>select * from checks</TT><BR>
<TT> /</TT><BR>
</P>
<P>All the above work.
</DL>
<H3><FONT COLOR="#000077">Exercise Answers</FONT></H3>
<DL>
<DD><B>1.</B> Using the <TT>CHECKS</TT> table from earlier today, write a query to
return just the check numbers and the remarks.
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SELECT CHECK#, REMARKS FROM CHECKS;</FONT></PRE>
</BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"></FONT></PRE>
<DL>
<DD><B>2.</B> Rewrite the query from exercise 1 so that the remarks will appear as
the first column in your query results.
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SELECT REMARKS, CHECK# FROM CHECKS;</FONT></PRE>
</BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"></FONT></PRE>
<DL>
<DD><B>3. </B>Using the <TT>CHECKS</TT> table, write a query to return all the unique
remarks.
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SELECT DISTINCT REMARKS FROM CHECKS;</FONT></PRE>
</BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"></FONT></PRE>
<H2><FONT COLOR="#000077">Day 3, "Expressions, Conditions, and Operators"</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>
<P>Use the <TT>FRIENDS</TT> table to answer the following questions.</P>
<PRE><FONT COLOR="#0066FF">LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
--------------- ---------------- -------- -------- -- ------
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
PERKINS ALTON 911 555-3116 CA 95633
BOSS SIR 204 555-2345 CT 95633
</FONT></PRE>
<DL>
<DD><B>1.</B> Write a query that returns everyone in the database whose last name
begins with <TT>M</TT>.
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B>SELECT * FROM FRIENDS WHERE LASTNAME LIKE 'M%';</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>2. </B>Write a query that returns everyone who lives in Illinois with a first
name of <TT>AL</TT>.
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B>SELECT * FROM FRIENDS
WHERE STATE = 'IL'
AND FIRSTNAME = 'AL';</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>3. </B>Given two tables (<TT>PART1</TT> and <TT>PART2</TT>) containing columns
named <TT>PARTNO</TT>, how would you find out which part numbers are in both tables?
Write the query.
<P>Use the <TT>INTERSECT</TT>. Remember that <TT>INTERSECT</TT> returns rows common
to both queries.
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B>SELECT PARTNO FROM PART1
INTERSECT
SELECT PARTNO FROM PART2;</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>4.</B> What shorthand could you use instead of <TT>WHERE a >= 10 AND a
<=30</TT>?
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B>WHERE a BETWEEN 10 AND 30;</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>5.</B> What will this query return?
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B>SELECT FIRSTNAME
FROM FRIENDS
WHERE FIRSTNAME = 'AL'
AND LASTNAME = 'BULHER';</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>Nothing will be returned, as both conditions are not true.
</DL>
<H3><FONT COLOR="#000077">Exercise Answers</FONT></H3>
<DL>
<DD><B>1.</B> Using the <TT>FRIENDS</TT> table, write a query that returns the following:
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">NAME ST
------------------- --
AL FROM IL</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT (FIRSTNAME || 'FROM') NAME, STATE</B>
2 <B>FROM FRIENDS</B>
3 <B> WHERE STATE = 'IL'</B>
4 <B>AND</B>
5 <B> LASTNAME = 'BUNDY';</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>2.</B> Using the <TT>FRIENDS</TT> table, write a query that returns the following:
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">NAME PHONE
-------------------------- ------------
MERRICK, BUD 300-555-6666
MAST, JD 381-555-6767
BULHER, FERRIS 345-555-3223</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL><B>SELECT LASTNAME || ',' || FIRSTNAME NAME,</B>
2 <B> AREACODE || '-' || PHONE PHONE</B>
3 <B>FROM FRIENDS</B>
4 <B>WHERE AREACODE BETWEEN 300 AND 400;</B></FONT></PRE>
</BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B></B></FONT></PRE>
<H2><FONT COLOR="#000077">Day 4, "Functions: Molding the Data You Retrieve"</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>
<DL>
<DD><B>1.</B> Which function capitalizes the first letter of a character string and
makes the rest lowercase?<BR>
<TT>INITCAP</TT>
<P><B>2.</B> Which functions are also known by the name ?<BR>
Group functions and aggregate functions are the same thing.</P>
<P><B>3.</B> Will this query work?
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT COUNT(LASTNAME) FROM CHARACTERS;</B></FONT></PRE>
</BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B></B></FONT></PRE>
<DL>
<DD>Yes, it will return the total of rows.<BR>
<B>4.</B> How about this one?
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">sql> <B>SELECT SUM(LASTNAME) FROM CHARACTERS</B></FONT></PRE>
</BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B></B></FONT></PRE>
<DL>
<DD>No, the query won't work because <TT>LASTNAME</TT> is a character field.
<P><B>5.</B> Assuming that they are separate columns, which function(s) would splice
together <TT>FIRSTNAME</TT> and <TT>LASTNAME</TT>?<BR>
The <TT>CONCAT</TT> function and the <TT>||</TT> symbol.</P>
<P><B>6.</B> What does the answer <TT>6</TT> mean from the following <TT>SELECT</TT>?</P>
<H5>INPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT COUNT(*) FROM TEAMSTATS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">COUNT(*)</FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><TT>6</TT> is the number of records in the table.
<P><B>7.</B> Will the following statement work?
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT SUBSTR LASTNAME,1,5 FROM NAME_TBL;</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>No, missing <TT>()</TT> around <TT>lastname,1,5</TT>. Also, a better plan is
to give the column an alias. The statement should look like this:
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT SUBSTR(LASTNAME,1,5) NAME FROM NAME_TBL;</B></FONT></PRE>
</BLOCKQUOTE>
<H3><FONT COLOR="#000077">Exercise Answers</FONT></H3>
<DL>
<DD><B>1. </B>Using today's <TT>TEAMSTATS</TT> table, write a query to determine
who is batting under .25. (For the baseball-challenged reader, batting average is
hits/ab.)
<H5>INPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT NAME FROM TEAMSTATS </B>
2 <B>WHERE (HITS/AB) < .25;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME
--------------
HAMHOCKER
CASEY</FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>2.</B> Using today's <TT>CHARACTERS</TT> table, write a query that will return
the following:
<H5>OUTPUT:</H5>
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">INITIALS__________CODE
K.A.P. 32
1 row selected.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>select substr(firstname,1,1)||'.'||
substr(middlename,1,1)||'.'||
substr(lastname,1,1)||'.' INITIALS, code
from characters
where code = 32;</B></FONT></PRE>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Day 5, "Clauses in SQL"</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>
<DL>
<DD><B>1. </B>Which clause works just like <TT>LIKE(<exp>%)</TT>?<BR>
<TT>STARTING WITH</TT>
<P><B>2.</B> What is the function of the <TT>GROUP BY</TT> clause, and what other
clause does it act like?<BR>
The <TT>GROUP BY</TT> clause groups data result sets that have been manipulated by
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -