Tables are the secret sauce of relational databases, the databases that use sql. They are the basic building block of what is to come. Luckily, they are easy to understand. Tables are made of rows and columns
Each row of a table is a new entry. Literally.
If you have a table called fruits, you might have the following rows
Each row is it's own record. A table is a collection of zero or more of these records that are all grouped together.
When you group these records together a table you want to have a theme. That theme makes a great name for the table
Columns define the structure of the table. They allow you to define multiple fields in each record. They also each have a name.
Let's revisit the fruits table, this time we are going to add another column called color
Now we have two columns in our table for each of our 8 rows.
Let's take a look at how to create a table. Do to this we will use sql. Specifically we will be using data definition language (ddl).
In this case, let's make a menu table that will contain a list of foods and drinks on the menu. It will also contain a price in each record.
Create table Menu ( Dish varchar(50) ,Price decimal(5,2) );
Taking a closer at the sql, we see that there are three main parts.
You may notice the words varchar and decimal as well. These are important too. They tell the database what type of data will be in the column. We will go further in depth on types of data in a future post. For now let's say that a varchar is a general purpose column that will hold text. A decimal is a number that will hold a values with decimal places (i.e. 5.99).
Now that we have defined a menu table, let's insert some records into it. Each record will be something that you can buy at a food truck. To insert a record you will use a sql insert statement.
Insert into Menu (Dish, Price) Values ('Asada Fries', 4.99);
Let's take a closer look at this sql. It is made up of three parts as well.
We can insert the rest of the menu now. We can do it all at once. Sql doesn't care.
Insert into Menu (Dish, Price) Values ('Hamburger', 5.99); Insert into Menu (Dish, Price) Values ('Asada Fries', 4.99); Insert into Menu (Dish, Price) Values ('Hamburger', 5.99); Insert into Menu (Dish, Price) Values ('Sliders', 6.99); Insert into Menu (Dish, Price) Values ('Bacon Cheeseburger', 7.99); Insert into Menu (Dish, Price) Values ('Carne Guisada Tacos', 8.99); Insert into Menu (Dish, Price) Values ('Enchiladas Verdes', 9.99); Insert into Menu (Dish, Price) Values ('Bottled Water', 0.99); Insert into Menu (Dish, Price) Values ('Soda', 0.99); Insert into Menu (Dish, Price) Values ('French Fries', 1.99);
Once you have data in a database, you can query it. You do that with a select statement.
Select Dish ,Price From Menu;
You will notice that this select statement is broken up into two clauses.
You can try it out for yourself on SQL Fiddle. Just click the link and you should find the sql ready for you to try out.
Those are the basics of tables. Did you try it out? Were you able to change it? Break it? What did you do?