Join Bill Weinman for an in-depth discussion in this video About numeric types, part of SQL Essential Training.
SQL data types are entirely platform dependent. The data types that are available on one database management system are almost guaranteed to be different than the data types available on another database management system. This also means that the numeric functions are going to have differences. Sometimes, the difference will be subtle, and sometimes, they will be significant. The functions presented in this chapter will give you an idea of how numeric functions work in SQL. But they may work differently on your system and there will certainly be many functions that are specifically suited to your system.
There are two basic categories of numeric types, for representing integers and real numbers. There will likely be many variations of these types on a full featured database management system. These types may be spelled differently and use different terminology but the integer types will mostly fall into these three categories. Integers are whole numbers that have no fractional part. There may be a precision option to specify how many bits are used to represent the integer. Decimals are fixed precision numbers with a decimal point.
These are not real or floating point numbers. Decimal types are more accurate than real types for quantities in a specific range of values. They are simply integers that have been scaled to accurately represent quantities less than one. Some systems provide money types. These are specialized cases of decimal types. Financial calculations are a very common usage for decimal types. So, some systems provide specialized types for this purpose. Real types are typically provided in various precisions.
They may be called real, or float, or some variation of these names. Real types sacrifice accuracy for scale. In other words, they're capable of representing very large or very small numbers, but only to a limited number of significant digits. For example, these expressions use real numbers. Both of the first two expressions return a result of 3.0. Yet, when you compare them, as in the third expression, they are not equal. That's because real numbers sacrifice accuracy for scale, so the values are not precise, and these are not equal.
The rest of this chapter will present a few sample numeric functions. This is a subset of functions that are likely available on all major database systems. This should give you a feel for how they are used and you can consult documentation for your system to find what functions are available and how they work.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.