Home > Articles > Web Development

📄 Contents

  1. Sams Teach Yourself SQL in 24 Hours, Third Edition
  2. Table of Contents
  3. Copyright
  4. About the Authors
  5. Acknowledgments
  6. Tell Us What You Think!
  7. Introduction
  8. Part I: A SQL Concepts Overview
  9. Hour 1. Welcome to the World of SQL
  10. SQL Definition and History
  11. SQL Sessions
  12. Types of SQL Commands
  13. An Introduction to the Database Used in This Book
  14. Summary
  15. Q&A
  16. Workshop
  17. Part II: Building Your Database
  18. Hour 2. Defining Data Structures
  19. What Is Data?
  20. Basic Data Types
  21. Summary
  22. Q&A
  23. Workshop
  24. Hour 3. Managing Database Objects
  25. What Are Database Objects?
  26. What Is a Schema?
  27. A Table: The Primary Storage for Data
  28. Integrity Constraints
  29. Summary
  30. Q&A
  31. Workshop
  32. Hour 4. The Normalization Process
  33. Normalizing a Database
  34. Summary
  35. Q&A
  36. Workshop
  37. Hour 5. Manipulating Data
  38. Overview of Data Manipulation
  39. Populating Tables with New Data
  40. Updating Existing Data
  41. Deleting Data from Tables
  42. Summary
  43. Q&A
  44. Workshop
  45. Hour 6. Managing Database Transactions
  46. What Is a Transaction?
  47. What Is Transactional Control?
  48. Transactional Control and Database Performance
  49. Summary
  50. Q&A
  51. Workshop
  52. Part III: Getting Effective Results from Queries
  53. Hour 7. Introduction to the Database Query
  54. What Is a Query?
  55. Introduction to the <tt>SELECT</tt> Statement
  56. Examples of Simple Queries
  57. Summary
  58. Q&amp;A
  59. Workshop
  60. Hour 8. Using Operators to Categorize Data
  61. What Is an Operator in SQL?
  62. Comparison Operators
  63. Logical Operators
  64. Conjunctive Operators
  65. Negating Conditions with the <tt>NOT</tt> Operator
  66. Arithmetic Operators
  67. Summary
  68. Q&amp;A
  69. Workshop
  70. Hour 9. Summarizing Data Results from a Query
  71. What Are Aggregate Functions?
  72. Summary
  73. Q&amp;A
  74. Workshop
  75. Hour 10. Sorting and Grouping Data
  76. Why Group Data?
  77. The <tt>GROUP BY</tt> Clause
  78. <tt>GROUP BY</tt> Versus <tt>ORDER BY</tt>
  79. The <tt>HAVING</tt> Clause
  80. Summary
  81. Q&amp;A
  82. Workshop
  83. Hour 11. Restructuring the Appearance of Data
  84. The Concepts of ANSI Character Functions
  85. Various Common Character Functions
  86. Miscellaneous Character Functions
  87. Mathematical Functions
  88. Conversion Functions
  89. The Concept of Combining Character Functions
  90. Summary
  91. Q&amp;A
  92. Workshop
  93. Hour 12. Understanding Dates and Times
  94. How Is a Date Stored?
  95. Date Functions
  96. Date Conversions
  97. Summary
  98. Q&amp;A
  99. Workshop
  100. Part IV: Building Sophisticated Database Queries
  101. Hour 13. Joining Tables in Queries
  102. Selecting Data from Multiple Tables
  103. Types of Joins
  104. Join Considerations
  105. Summary
  106. Q&amp;A
  107. Workshop
  108. Hour 14. Using Subqueries to Define Unknown Data
  109. What Is a Subquery?
  110. Embedding a Subquery Within a Subquery
  111. Summary
  112. Q&A
  113. Workshop
  114. Hour 15. Combining Multiple Queries into One
  115. Single Queries Versus Compound Queries
  116. Why Would I Ever Want to Use a Compound Query?
  117. Compound Query Operators
  118. Using an <tt>ORDER BY</tt> with a Compound Query
  119. Using <tt>GROUP BY</tt> with a Compound Query
  120. Retrieving Accurate Data
  121. Summary
  122. Workshop
  123. Q&amp;A
  124. Part V: SQL Performance Tuning
  125. Hour 16. Using Indexes to Improve Performance
  126. What Is an Index?
  127. How Do Indexes Work?
  128. The <tt>CREATE INDEX</tt> Command
  129. Types of Indexes
  130. When Should Indexes Be Considered?
  131. When Should Indexes Be Avoided?
  132. Summary
  133. Q&amp;A
  134. Workshop
  135. Hour 17. Improving Database Performance
  136. What Is SQL Statement Tuning?
  137. Database Tuning Versus SQL Tuning
  138. Formatting Your SQL Statement
  139. Full Table Scans
  140. Other Performance Considerations
  141. Performance Tools
  142. Summary
  143. Q&amp;A
  144. Workshop
  145. Part VI: Using SQL to Manage Users and Security
  146. Hour 18. Managing Database Users
  147. Users Are the Reason
  148. The Management Process
  149. Tools Utilized by Database Users
  150. Summary
  151. Q&amp;A
  152. Workshop
  153. Hour 19. Managing Database Security
  154. What Is Database Security?
  155. How Does Security Differ from User Management?
  156. What Are Privileges?
  157. Controlling User Access
  158. Controlling Privileges Through Roles
  159. Summary
  160. Q&amp;A
  161. Workshop
  162. Part VII: Summarized Data Structures
  163. Hour 20. Creating and Using Views and Synonyms
  164. What Is a View?
  165. Creating Views
  166. Dropping a View
  167. What Is a Synonym?
  168. Summary
  169. Q&amp;A
  170. Workshop
  171. Hour 21. Working with the System Catalog
  172. What Is the System Catalog?
  173. How Is the System Catalog Created?
  174. What Is Contained in the System Catalog?
  175. Examples of System Catalog Tables by Implementation
  176. Querying the System Catalog
  177. Updating System Catalog Objects
  178. Summary
  179. Q&amp;A
  180. Workshop
  181. Part VIII: Applying SQL Fundamentals in Today's World
  182. Hour 22. Advanced SQL Topics
  183. Advanced Topics
  184. Cursors
  185. Stored Procedures and Functions
  186. Triggers
  187. Dynamic SQL
  188. Call-Level Interface
  189. Using SQL to Generate SQL
  190. Direct Versus Embedded SQL
  191. Summary
  192. Q&amp;A
  193. Workshop
  194. Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  195. SQL and the Enterprise
  196. Accessing a Remote Database
  197. Accessing a Remote Database Through a Web Interface
  198. SQL and the Internet
  199. SQL and the Intranet
  200. Summary
  201. Q&amp;A
  202. Workshop
  203. Hour 24. Extensions to Standard SQL
  204. Various Implementations
  205. Examples of Extensions from Some Implementations
  206. Interactive SQL Statements
  207. Summary
  208. Q&amp;A
  209. Workshop
  210. Part IX: Appendixes
  211. Appendix A. Common SQL Commands
  212. SQL Statements
  213. SQL Clauses
  214. Appendix B. Using MySQL for Exercises
  215. Windows Installation Instructions
  216. Linux Installation Instructions
  217. Appendix C. Answers to Quizzes and Exercises
  218. Hour 1, "Welcome to the World of SQL"
  219. Hour 2, "Defining Data Structures"
  220. Hour 3, "Managing Database Objects"
  221. Hour 4, "The Normalization Process"
  222. Hour 5, "Manipulating Data"
  223. Hour 6, "Managing Database Transactions"
  224. Hour 7, "Introduction to the Database Query"
  225. Hour 8, "Using Operators to Categorize Data"
  226. Hour 9, "Summarizing Data Results from a Query"
  227. Hour 10, "Sorting and Grouping Data"
  228. Hour 11, "Restructuring the Appearance of Data"
  229. Hour 12, "Understanding Dates and Time"
  230. Hour 13, "Joining Tables in Queries"
  231. Hour 14, "Using Subqueries to Define Unknown Data"
  232. Hour 15, "Combining Multiple Queries into One"
  233. Hour 16, "Using Indexes to Improve Performance"
  234. Hour 17, "Improving Database Performance"
  235. Hour 18, "Managing Database Users"
  236. Hour 19, "Managing Database Security"
  237. Hour 20, "Creating and Using Views and Synonyms"
  238. Hour 21, "Working with the System Catalog"
  239. Hour 22, "Advanced SQL Topics"
  240. Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet"
  241. Hour 24, "Extensions to Standard SQL"
  242. Appendix D. <tt>CREATE TABLE</tt> Statements for Book Examples
  243. <tt>EMPLOYEE_TBL</tt>
  244. <tt>EMPLOYEE_PAY_TBL</tt>
  245. <tt>CUSTOMER_TBL</tt>
  246. <tt>ORDERS_TBL</tt>
  247. <tt>PRODUCTS_TBL</tt>
  248. Appendix E. <tt>INSERT</tt> Statements for Data in Book Examples
  249. <tt>INSERT</tt> Statements
  250. Appendix F. Glossary
  251. Appendix G. Bonus Exercises
Recommended Book

Introduction to the SELECT Statement

The SELECT statement, the command that represents Data Query Language (DQL) in SQL, is the statement used to construct database queries. The SELECT statement is not a standalone statement, which means that one or more additional clauses (elements) are required for a syntactically correct query. In addition to the required clauses, there are optional clauses that increase the overall functionality of the SELECT statement. The SELECT statement is by far one of the most powerful statements in SQL. The FROM clause is the mandatory clause and must always be used in conjunction with the SELECT statement.

newterm_icon.gif

There are four keywords, or clauses, that are valuable parts of a SELECT statement. These keywords are as follows:

  • SELECT
  • FROM
  • WHERE
  • ORDER BY

Each of these keywords is covered in detail during the following sections.

The SELECT Statement

The SELECT statement is used in conjunction with the FROM clause to extract data from the database in an organized, readable format. The SELECT part of the query is for selecting the data you want to see according to the columns in which they are stored in a table.

The syntax for a simple SELECT statement is as follows:

   syntax_icon.gif
SELECT [ * | ALL | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE1 [ , TABLE2 ];

The SELECT keyword in a query is followed by a list of columns that you want displayed as part of the query output. The FROM keyword is followed by a list of one or more tables from which you want to select data. The asterisk (*) is used to denote that all columns in a table should be displayed as part of the output. Check your particular implementation for its usage. The ALL option is used to display all values for a column, including duplicates. The DISTINCT option is used to suppress duplicate rows from being displayed in the output. The default between DISTINCT and ALL is ALL, which does not have to be specified. Notice that the columns following the SELECT are separated by commas, as is the table list following the FROM.

newterm_icon.gif

Arguments are values that are either required or optional to the syntax of a SQL statement or command.

Explore the basic capabilities of the SELECT statement by studying the following examples. First, perform a simple query from the PRODUCTS_TBL table:

   input_icon.gif

   SELECT * FROM PRODUCTS_TBL;

   output_icon.gif
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
222        PLASTIC PUMPKIN 18 INCH         7.75
13         FALSE PARAFFIN TEETH            1.1
90         LIGHTED LANTERNS               14.5
15         ASSORTED COSTUMES              10
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95
1234       KEY CHAIN                       5.95
2345       OAK BOOKSHELF                  59.99

11 rows selected.

The asterisk represents all columns in the table, which, as you can see, are displayed in the form PROD_ID, PROD_DESC, and COST. Each column in the output is displayed in the order that it appears in the table. There are 11 records in this table, identified by the feedback 11 rows selected. This feedback differs among implementations; for example, another feedback for the same query would be 11 rows affected.

Now select data from another table, CANDY_TBL. Create this table in the image of the PRODUCTS_TBL table for the following examples. List the column name after the SELECT keyword to display only one column in the table:

   input_icon.gif

   SELECT PROD_DESC FROM CANDY_TBL;

   output_icon.gif
PROD_DESC
------------------
CANDY CORN
CANDY CORN
HERSHEYS KISS
SMARTIES

4 rows selected.

Four records exist in the CANDY_TBL table. You have used the ALL option in the next statement to show you that the ALL is optional and redundant. There is never a need to specify ALL; it is a default option.

   input_icon.gif

   SELECT ALL PROD_DESC

   FROM CANDY_TBL;

   output_icon.gif
PROD_DESC
-------------------
CANDY CORN
CANDY CORN
HERSHEYS KISS
SMARTIES

4 rows selected.

The DISTINCT option is used in the following statement to suppress the display of duplicate records. Notice that the value CANDY CORN is only printed once in this example.

   input_icon.gif

   SELECT DISTINCT PROD_DESC

   FROM CANDY_TBL;

   output_icon.gif
PROD_DESC
------------------
CANDY CORN
HERSHEYS KISS
SMARTIES

3 rows selected.

DISTINCT and ALL can also be used with parentheses enclosing the associated column. The use of parentheses is often used in SQL—as well as many other languages—to improve readability.

   input_icon.gif

   SELECT DISTINCT(PROD_DESC)

   FROM CANDY_TBL;

   output_icon.gif
PROD_DESC
------------------
CANDY CORN
HERSHEYS KISS
SMARTIES

3 rows selected.

The FROM Clause

The FROM clause must be used in conjunction with the SELECT statement. It is a required element for any query. The FROM clause's purpose is to tell the database what table(s) to access to retrieve the desired data for the query. The FROM clause may contain one or more tables. The FROM clause must always list at least one table.

The syntax for the FROM clause is as follows:

   syntax_icon.gif
FROM TABLE1 [ , TABLE2 ]

Using Conditions to Distinguish Data

newterm_icon.gif

A condition is part of a query that is used to display selective information as specified by the user. The value of a condition is either TRUE or FALSE, thereby limiting the data received from the query. The WHERE clause is used to place conditions on a query by eliminating rows that would normally be returned by a query without conditions.

There can be more than one condition in the WHERE clause. If there is more than one condition, they are connected by the AND and OR operators, which are discussed during Hour 8, "Using Operators to Categorize Data." As you also learn during the next hour, there are several conditional operators that can be used to specify conditions in a query. This hour only deals with a single condition for each query.

newterm_icon.gif

An operator is a character or keyword in SQL that is used to combine elements in a SQL statement.

The syntax for the WHERE clause is as follows:

   syntax_icon.gif
SELECT [ ALL | * | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE1 [ , TABLE2 ]
WHERE [ CONDITION1 | EXPRESSION1 ]
[ AND CONDITION2 | EXPRESSION2 ]

The following is a simple SELECT without conditions specified by the WHERE clause:

   input_icon.gif

   SELECT *

   FROM PRODUCTS_TBL;

   output_icon.gif
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
222        PLASTIC PUMPKIN 18 INCH         7.75
13         FALSE PARAFFIN TEETH            1.1
90         LIGHTED LANTERNS               14.5
15         ASSORTED COSTUMES              10
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95
1234       KEY CHAIN                       5.95
2345       OAK BOOKSHELF                  59.99

11 rows selected.

Now add a condition for the same query.

   input_icon.gif

   SELECT * FROM PRODUCTS_TBL

   WHERE COST < 5;

   output_icon.gif
PROD_ID    PROD_DESC                       COST
---------- ------------------------------- -----
13         FALSE PARAFFIN TEETH            1.1
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95

5 rows selected.

The only records displayed are those that cost less than $5.

In the following query, you want to display the product description and cost that matches the product identification 119.

   input_icon.gif

   SELECT PROD_DESC, COST

   FROM PRODUCTS_TBL

   WHERE PROD_ID = '119';

   output_icon.gif
PROD_DESC                       COST
------------------------------- -----
ASSORTED MASKS                  4.95

1 row selected.

Sorting Your Output

You usually want your output to have some kind of order. Data can be sorted by using the ORDER BY clause. The ORDER BY clause arranges the results of a query in a listing format you specify. The default ordering of the ORDER BY clause is an ascending order; the sort displays in the order A–Z if it's sorting output names alphabetically. A descending order for alphabetical output would be displayed in the order Z–A. Ascending order for output for numeric values between 1 and 9 would be displayed 1–9; descending order is displayed as 9–1.

The syntax for the ORDER BY is as follows:

   syntax_icon.gif
SELECT [ ALL | * | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE1 [ , TABLE2 ]
WHERE [ CONDITION1 | EXPRESSION1 ]
[ AND CONDITION2 | EXPRESSION2 ]
ORDER BY COLUMN1|INTEGER [ ASC|DESC ]

Begin your exploration of the ORDER BY clause with an extension of one of the previous statements. Order by the product description in ascending order or alphabetical order. Note the use of the ASC option. ASC can be specified after any column in the ORDER BY clause.

   input_icon.gif

   SELECT PROD_DESC, PROD_ID, COST

   FROM PRODUCTS_TBL

   WHERE COST < 20

   ORDER BY PROD_DESC ASC;

   output_icon.gif
PROD_DESC                 PROD_ID          COST
------------------------- --------------- ------
ASSORTED COSTUMES         15              10
ASSORTED MASKS            119              4.95
CANDY CORN                9                1.35
FALSE PARAFFIN TEETH      13               1.1
LIGHTED LANTERNS          90              14.5
PLASTIC PUMPKIN 18 INCH   222              7.75
PLASTIC SPIDERS           87               1.05
PUMPKIN CANDY             6                1.45

8 rows selected.

You can use DESC, as in the following statement, if you want the same output to be sorted in reverse alphabetical order.

   input_icon.gif

   SELECT PROD_DESC, PROD_ID, COST

   FROM PRODUCTS_TBL

   WHERE COST < 20

   ORDER BY PROD_DESC DESC;

   output_icon.gif
PROD_DESC                 PROD_ID          COST
------------------------- --------------- ------
PUMPKIN CANDY             6                1.45
PLASTIC SPIDERS           87               1.05
PLASTIC PUMPKIN 18 INCH   222              7.75
LIGHTED LANTERNS          90              14.5
FALSE PARAFFIN TEETH      13               1.1
CANDY CORN                9                1.35
ASSORTED MASKS            119              4.95
ASSORTED COSTUMES         15              10

8 rows selected.

There are shortcuts in SQL. A column listed in the ORDER BY clause can be abbreviated with an integer. The INTEGER is a substitution for the actual column name (an alias for the purpose of the sort operation), identifying the position of the column after the SELECT keyword.

An example of using an integer as an identifier in the ORDER BY clause follows:

   input_icon.gif

   SELECT PROD_DESC, PROD_ID, COST

   FROM PRODUCTS_TBL

   WHERE COST < 20

   ORDER BY 1;


   output_icon.gif
PROD_DESC                 PROD_ID          COST
------------------------- --------------- ------
ASSORTED COSTUMES         15              10
ASSORTED MASKS            119              4.95
CANDY CORN                9                1.35
FALSE PARAFFIN TEETH      13               1.1
LIGHTED LANTERNS          90              14.5
PLASTIC PUMPKIN 18 INCH   222              7.75
PLASTIC SPIDERS           87               1.05
PUMPKIN CANDY             6                1.45

8 rows selected.

In this query, the integer 1 represents the column PROD_DESC. The integer 2 represents the PROD_ID column, 3 represents the COST column, and so on.

You can order by multiple columns in a query, using either the column name itself or the associated number of the column in the SELECT:

ORDER BY 1,2,3 

Columns in an ORDER BY clause are not required to appear in the same order as the associated columns following the SELECT, as shown by the following example:

ORDER BY 1,3,2 

Case Sensitivity

Case sensitivity is a very important concept to understand when coding with SQL. Typically, SQL commands and keywords are not case-sensitive, which allows you to enter your commands and keywords in either uppercase or lowercase—whatever you prefer. The case may be mixed (both uppercase and lowercase for a single word or statement). See Hour 5, "Manipulating Data," on case sensitivity.

Case sensitivity is, however, a factor when dealing with data in SQL. In most situations, data seems to be stored exclusively in uppercase in a relational database to provide data consistency.

For instance, your data would not be consistent if you arbitrarily entered your data using random case:

SMITH 

Smith

smith

If the last name was stored as smith and you issued a query as follows, no rows would be returned.

SELECT * 
FROM EMPLOYEE_TBL
WHERE LAST_NAME = 'SMITH';

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.