?? 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="../buttonart/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/ape.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../index.htm"><IMGSRC="../buttonart/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 22333MEZA AL 200 555-2222 UKMERRICK BUD 300 555-6666 CO 80212MAST JD 381 555-6767 LA 23456BULHER FERRIS 345 555-3223 IL 23332PERKINS ALTON 911 555-3116 CA 95633BOSS 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 FRIENDSWHERE 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 PART1INTERSECTSELECT 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 FIRSTNAMEFROM FRIENDSWHERE 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-6666MAST, JD 381-555-6767BULHER, 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 -------------- HAMHOCKERCASEY</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__________CODEK.A.P. 321 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 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>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -