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

Negating Conditions with the NOT Operator

Of all the conditions tested by the logical operators discussed here, there is a way to negate each one of these operators to change the condition's viewpoint.

The NOT operator reverses the meaning of the logical operator with which it is used. The NOT can be used with the following operators in the following methods:

  • Not Equal
  • NOT BETWEEN
  • NOT IN
  • NOT LIKE
  • IS NOT NULL
  • NOT EXISTS
  • NOT UNIQUE

Each method is discussed in the following sections. First, let's look at how to test for inequality.

Not Equal

You have learned how to test for inequality using the <> operator. Inequality is worth mentioning in this section because to test for it, you are actually negating the equality operator. The following is a second method for testing inequality available in some SQL implementations:

Example

Meaning

WHERE SALARY <> '20000'

SALARY does not equal 20000

WHERE SALARY != '20000'

SALARY does not equal 20000

In the second example, you can see that the exclamation mark is used to negate the equality comparison. The use of the exclamation mark is allowed in addition to the standard operator for inequality <> in some implementations.

NOT BETWEEN

The BETWEEN operator is negated as follows:

Example

Meaning

WHERE Salary NOT BETWEEN '20000' AND '30000'

The value for SALARY cannot fall between 20000 and 30000, to include the values 20000 and 30000

   input_icon.gif

   SELECT *

   FROM PRODUCTS_TBL

   WHERE COST NOT BETWEEN 5.95 AND 14.5;

   output_icon.gif
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
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
2345       OAK BOOKSHELF                  59.99

7 rows selected.

NOT IN

The IN operator is negated as NOT IN. All salaries in the following example that are not in the listed values, if any, are returned:

Example

Meaning

WHERE SALARY NOT IN ('20000', '30000', '40000')

The SALARY cannot be equal to any of the given values for action to be taken

   input_icon.gif

   SELECT *

   FROM PRODUCTS_TBL

   WHERE PROD_ID NOT IN ('13','9','87','119');

   output_icon.gif
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
222        PLASTIC PUMPKIN 18 INCH         7.75
90         LIGHTED LANTERNS               14.5
15         ASSORTED COSTUMES              10
6          PUMPKIN CANDY                   1.45
1234       KEY CHAIN                       5.95
2345       OAK BOOKSHELF                  59.99

7 rows selected.

In this output, records were not displayed for the listed identifications after the NOT IN operator.

NOT LIKE

The LIKE, or wildcard, operator is negated as NOT LIKE. When NOT LIKE is used, only values that are not similar are returned. Examples include:

Example

Meaning

WHERE SALARY NOT LIKE '200%'

Finds any values that do not start with 200

WHERE SALARY NOT LIKE '%200%'

Finds any values that do not have 200 in any position

WHERE SALARY NOT LIKE '_00%'

Finds any values that have 00 starting in the second position

WHERE SALARY NOT LIKE '2_%_%'

Does not find any values that start with 2 and have a length of 3 or greater

   input_icon.gif

   SELECT PROD_DESC

   FROM PRODUCTS_TBL

   WHERE PROD_DESC NOT LIKE 'L%';

   output_icon.gif
PROD_DESC
------------------------
WITCHES COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF

10 rows selected.

In this output, the product descriptions starting with the letter L were not displayed.

IS NOT NULL

The IS NULL operator is negated as IS NOT NULL to test for values that are not NULL.

Example

Meaning

WHERE SALARY IS NOT NULL

Only NOT NULL rows are returned

   input_icon.gif

   SELECT EMP_ID, LAST_NAME, FIRST_NAME, PAGER

   FROM EMPLOYEE_TBL

   WHERE PAGER IS NOT NULL;

   output_icon.gif
EMP_ID    LAST_NAM FIRST_NA PAGER
--------- -------- -------- ----------
213764555 GLASS    BRANDON  3175709980
313782439 GLASS    JACOB    8887345678

2 rows selected.

NOT EXISTS

EXISTS is negated as NOT EXISTS.

Example

Meaning

WHERE NOT EXISTS (SELECT EMP_ID FROM EMPLOYEE_TBL WHERE EMP_ID = '333333333'

Searching to see whether the EMP_ID 3333333333 is not in the EMPLOYEE_TBL

   mysql_icon.gif
   input_icon.gif

   SELECT MAX(COST)

   FROM PRODUCTS_TBL

   WHERE NOT EXISTS ( SELECT COST
                   
   FROM PRODUCTS_TBL
                   
   WHERE COST > 100 );

   output_icon.gif
 MAX(COST)
-----------
     59.99

The maximum cost for the table is displayed in this output because there were not any records that existed where the cost was greater than 100.

NOT UNIQUE

The UNIQUE operator is negated as NOT UNIQUE.

Example

Meaning

WHERE NOT UNIQUE SALARY (SELECT FROM EMPLOYEE_TBL)

Testing to see whether there are salaries in the table that are not UNIQUE

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.