Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
    • What Is an Operator in SQL?
    • Comparison Operators
    • Logical Operators
    • Conjunctive Operators
    • Negating Conditions with the NOT Operator
    • Arithmetic Operators
    • Summary
    • Q&A
    • Workshop
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

Arithmetic Operators

Arithmetic operators are used to perform mathematical functions in SQL—the same as in most other languages. There are four conventional operators for mathematical functions:

• + (addition)
• - (subtraction)
• * (multiplication)
• / (division)

Addition

Addition is performed through the use of the plus (+) symbol.

Subtraction

Subtraction is performed using the minus (-) symbol.

Multiplication

Multiplication is performed by using the asterisk (*) symbol.

The pay rate in the following example is multiplied by 1.1, which increases the current pay rate by 10 percent:

   

   SELECT EMP_ID, PAY_RATE, PAY_RATE * 1.1

   FROM EMPLOYEE_PAY_TBL

   WHERE PAY_RATE IS NOT NULL;

   
EMP_ID      PAY_RATE PAY_RATE*1.1
----------- -------- ------------
442346889      14.75       16.225
220984332         11         12.1
443679012         15         16.5

3 rows selected.

Division

Division is performed through the use of the slash (/) symbol.

Arithmetic Operator Combinations

The arithmetic operators can be used in combinations with one another. Remember the rules of precedence in basic mathematics. Multiplication and division operations are performed first, and then addition and subtraction operations. The only way the user has control over the order of the mathematical operations is through the use of parentheses. Parentheses surrounding an expression causes that expression to be evaluated as a block.

Precedence is the order in which expressions are resolved in a mathematical expression or with embedded functions in SQL.

In the following examples, notice that the placement of parentheses in an expression does not affect the outcome if only multiplication and division are involved. Precedence is not a factor in these cases. Although it may not appear to make sense, it is possible that some implementations of SQL do not follow the ANSI standard in cases like this, however unlikely.

The following are some more examples:

SELECT SALARY * 10 + 1000 
FROM EMPLOYEE_PAY_TBL
WHERE SALARY > 20000;

SELECT SALARY / 52 + BONUS
FROM EMPLOYEE_PAY_TBL;

SELECT (SALARY - 1000 + BONUS) / 52 * 1.1
FROM EMPLOYEE_PAY_TBL;

The following is a rather wild example:

SELECT SALARY 
FROM EMPLOYEE_PAY_TBL
WHERE SALARY < BONUS * 3 + 10 / 2 - 50;

Because parentheses are not used, mathematical precedence takes effect, altering the value for BONUS tremendously for the condition.

Share This