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.
Creating a table
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.
- The “create table” keyword that tells the database what we want to do
- The name of the table that we want to create, Menu
- A list of column names.
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.
- The “insert into” keywords that tell the database that we want to insert data into a table
- The name of the table that we want to insert data into. We also included the names of the columns
- The “values” keyword followed by the values that we want to insert. Notice that the values appear in the same order as the names of the columns they belong to
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.
- The select clause. This clause contains the select keyword telling the database that this is a query. This clause also contains the columns that we want to see
- The from clause. This clause contains the from keyword and tells the database which table we want to select data from
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?