Understanding How SQL Databases work

What are SQL tables?

A database can be made up of many tables, and a table in a database is made up of intersecting columns and rows that form a grid. A good way to think about this is to imagine a checkerboard. Along the top row of the checkerboard, there are labels for the data you wish to store, for example, Name, Age, Gender, Eye Color, etc. In all the rows below, information is stored. Each row is one entry (all the data in a single row, belongs to the same person in this case) and each column contains a specific type of data as indicated by its label. Here is something to help you visualize a table:

Understanding SQL Relational Databases

So what is a 'relational' database, and how does it use these tables? Well, a relational database lets us 'relate' data from one table to another. Let's say for example we were making a database for a car dealership. We could make one table to hold all of the details for each of the cars we were selling. However, the contact information for 'Ford' would be the same for all of the cars they make, so we do not need to type that data more than once.

What we can do is create a second table, called manufacturers. In this table, we could list Ford, Volkswagen, Chrysler, etc. Here you could list the address, phone number, and other contact information for each of these companies. You could then dynamically call the contact information from our second table for every car in our first table. You would only ever have to type this information once despite it being accessible for every car in the database. This not only saves time but also valuable database space as no piece of data needs to be repeated.

SQL Data Types

Each column can only contain one type of data which we must define. An example of what this means is; in our age column we use a number. We could not change Kelly's entry to "twenty-six" if we had defined that column to be a number. The main data types are numbers, date/time, text, and binary. Although these have many subcategories, we will just touch on the most common types that you will use in this tutorial.

INTEGER: This stores whole numbers, both positive and negative. Some examples are 2, 45, -16 and 23989. In our example, the age category could have been an integer.

FLOAT: This stores numbers when you need to use decimals. Some examples would be 2.5, -.664, 43.8882, or 10.00001.

DATETIME: This stores a date and time in the format YYYY-MM-DD HH:MM:SS

VARCHAR: This stores a limited amount of text or single characters. In our example, the name column could have been varcar (short for a variable character)

BLOB: This stores binary data other than text, for example, file uploads.