Data types in SQL

Fields in a database will often represent a specific kind of data: a name, a date, or an ID number or an amount of points in a competition. And depending on how we use these fields, we may not care much what kind of information they represent. But, most databases will keep track of what sort of data is in a field, if you ask it to. And this can be helpful because certain types of data offer a certain abilities when writing SQL statements. For example, it doesn't make sense to ask the database to add up the value of all of the names in a table. But it might make sense to add up all of the points won in a game.

Generally speaking, SQL has a few categories of data types you should be aware of. Those dealing with binary information, with dates and times, with numbers, and with text. Within each of those general categories, there are specific types with specific purposes and as you learn more about using SQL in your field and in your software of choice, you'll get a better understanding of which types are helpful for you and which ones are supported on your database. Not all of them are supported in every implementation of SQL so be sure to check your documentation for your setup. The data type indicates what a field is optimized to store and what operations are possible on it.

The binary fields are optimized to store ones and zeroes, representing whatever data that might be in a fixed or variable way. The date and time types indicate that the data in a field should be treated as a time value. And that's reflected in the way they're stored, compared, and used. The number types represent different kinds of numeric values, including integers of different lengths, floating points of various lengths, and precisions, and so on. And the text types represent different lengths of characters treated as text rather than as numbers or binary data.

So to store a person's name, we would use a text to type with a variable length, usually VARCHAR, or VARCHAR, depending on how you like to pronounce it, for variable character length. To store the number of points that someone earned on a quiz, we'd probably use an integer type, because we aren't concerned about decimal points there. To store calendar date, we'd use a date type. And to store a true false value, we might us a binary, but there's a special type called BOOLEAN that's usually a better choice. These types come into play mostly when we're creating a table.

So we can tell the system how to treat data that's going to be added. There's a special value that I want to mention too, and that's called Null. Null is a value that represents nothing in a particular field. It's a value different than zero and different than no or false and boolean. It means we don't have a value for something and so it's treated differently from other values. Because of this, I'll be sure to point out where null is used and how it's treated differently throughout the course. But for now, just remember: null is not zero and null is not no.