SQL Table Commands

In the previous tutorials of SQL Database Commands and Data Types in SQL, now we are moving forward with Operations on Database Tables. In the past few tutorials we completely understood the concept of Database and now we are going to use it.

Just to let you know that data in the databases are stored in the form of Tables and most of the SQL Commands are work on the tables itself.. So, before we get started, we need to understand what are tables and why are they important in databases?

And after that, we will learn about the various commands relating to tables

What are Tables in Database?

Frankly speaking, in databases, tables are actually the containers. These containers actually stores the data. For the purpose of a definition, we can term tables as a structured format in a database which holds the data. To put in the importance of tables in SQL in a nutshell, we can say that tables are the heart of SQL. Without tables, you cannot store any data in a database. So, to be precise, tables are the most basic part of a database.

Tables are actually made up of two things:

• Columns
• Rows

In the picture below, you can clearly see what a row and a column is.

 

You must have seen this table before in the tutorial of Introduction to SQL. We saw this table as the example of SQL Queries. Both rows and column have their role and meaning, so let’s take a look at them.

Columns

Columns can be defined as the vertical division of a table that contains data of a particular type.

As you can see in the example, we have a column only contains a particular type of data like a name is a string, which may be stored using a varchar.  So in the column name, all the data entries are actually varchar. Which also means that we can not store two different data types in a column.

For the purpose of definition, you can put a column as: Set of data values of a particular data type is known as a column.

A column is made up of two parts:

• Attributes
• Fields

These two things are a lot of the times intermixed. We need to understand the difference between the two. Let’s start with Attributes.

 

Attributes

In the context of a column, an attribute can be defined as the header of the column, as we can see in the picture.

In the above example, we can clearly see that name and city are the two attributes and they can define data goes into the corresponding column. So, in general, we can say that an attribute is actually a characteristic, which defines the nature of the column and the data type of attribute defines the data type of that column.

 

Fields

The data in a table is contained inside a cell known as a field. How do we define this in a column and how is it related to a column, let’s understand that using the example below:

While the topmost row of the column actually contains the attribute, each row under the attribute contains data corresponding to that column. Each such data value in a table is known as a cell. These cells actually contain the data in a table. So, each such cell makes up a field. Therefore, while at the top resides the attribute, each attribute actually heads a number of fields, which contain the corresponding data.

Hence, now that we know what columns are and what they are made of let’s move forward to study about rows and their contribution.

 

Rows

Rows are “horizontal” collections of items belonging to a certain category. These are the actual data present in a table. The data in a row is actually inter-related and represent the property of a single data item.Therefore, a cell is actually the part of a column and as well as a corresponding row. So, to put it precisely, a cell is actually the intersection of a row and a column, leaving apart attributes.

Another name for a row is a tuple. A row can be put up as a single, implicitly structured data item in a table. A row contains the data which is distributed among the various columns, like:

In the table above, Vishu belongs to the attribute ‘name’ of the column while Delhi belongs to the attribute ‘city’ of the column. These two together make up a single data entry in the table and are part of the data for the same person. Thus the data is distributed in the various columns.

 

Importance of Data Types in Tables

Now as we know about the components of the table and we know the data is stored according to data types, we need an answer to one question: Why data in columns stored according to data types? Why can’t we store data of multiple data types in a single column? We will also get to know the importance of data types in SQL tables through this question. To solve this issue, let’s take an example of a dummy table:

I’ve created two tables which have exactly the same data, but While the first table has the same type of data throughout the column, take a look at the second table

                

You must have noticed the difference by now. Yes, the data members of the column ‘Age’ are not all the same.

Now, to understand the difference, let’s think about a situation. Imagine, you have to find the average age, where the average is defined as: (Sum of all the data values) / Number of data values.  In the first table, it is quite easy to add the ages as they are all in numbers. But what about the second table? We all are aware that it is going to be impossible for a calculator to understand ‘Nineteen’ or ‘Eighteen’, since these are strings and not numbers. Hence, the second table poses problems as the data types are different. This helps us conclude that it is quite important to have the same data types in columns. 

With all this discussed, we are quite clear with the concept of tables in databases and their importance as they hold the data. Now, we can work with the Table Commands.

 

SQL Table Commands

With your basic knowledge of database command from the tutorial, SQL Database Commands, I hope you’ll be able to create a simple database and use it. Once that is done, we can go on with tables.

In this tutorial, we will look at only the basic commands like:

• create table
• show tables
• show columns from
• drop table

Here we go, creating some tables.

How to create table in Database using SQL?

The very first command we are going to study is “create table”, which helps us to create a table with the column attributes and their data types. The basic syntax of this command looks like this:

In this manner, you can create a table. As create table is the command, we need to include the table name along with it. Apart from the table name, we need to add the details of the columns, where these details actually define the attribute of the column and its data type. This would result in a empty Table with a Header.

Note: The periods(.) are only used to depict that you can add certain more columns to there, they are not part of the command. Use “column_name data_type,” the periods and complete the execution of the command.

 

With all that done, we can now run a simple command to create a table. In the example below, I created a table “Book” in the database “Library”. The code I’m using for creating the table is:

CREATE TABLE books(

name VARCHAR(50),

author VARCHAR(50),

edition INT,

year_of_publication INT

);

And here we go, we created our very first table. But how do we know that the execution was successful? You may have got a similar output saying “Query OK, 0 rows affected”, this actually represents that the execution was successful. But what happens if the something went wrong? Well, the output looks something like this:

The error goes something like this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 5

This actually represents that you put a wrong syntax in your code like for me I wrote the year_of_publication line like this: year_of_publication INT;

Here, I actually put up an extra semicolon(;) before using ‘)’ which certainly is wrong as this is the last attribute and we need a closing bracket.

Now that we have actually created a table, we need to know whether we did it correctly or not. So, for that purpose, we need to look up for the table. Let’s see how to do that:

 

How to show tables in SQL?

The second command we are going to learn today is “show tables“. This command is pretty helpful in determining what tables are present inside the database. You can look at the following syntax which is used for this command:

With this command, you can look up for all the tables present in the database and hence, you can determine which table to use. Also, this command helps you to conclude whether the previous command was correctly executed or not. So, let’s try it out:

You can see, the output actually depicts the table books as part of the database. It is quite clear that our command worked, but what about the columns? How do we know about the columns? For that purpose, we have our next command, let’s take a look at it.

 

How to show columns in SQL?

This command is pretty useful. It actually helps you to look at the content of the table, which in turn will help you in data insertion later on. Following is the syntax of this command:

With this command, you can actually explore the table you created along with its major highlight. Let us give it a run:

Note: This command is helpful to determine the layout of the table, i.e, what are the main highlight. We are right now concerned with what are the parts of the table and not the data. For the purpose of looking up for the data and take a complete look inside the table, we use the SELECT command, which we will look up in the next tutorial when we work with data also.

Well, here you can see a pretty detailed view of the table we just created. Unlike the “show tables” command, this command, in turn gives us the actual content of the table. Don’t bother yourself with those extra details, like “Null”, “Key”, “Default” and “Extra”, as we will get back to them in later on as they are more related to data.

So, now we have created tables and we have also learned how to look up to them. But how do we delete an extra table or any table? Well, our next command gives us the authority to do that too. Any guesses about this one?

 

How to delete a table in SQL?

Well, you guessed it right, it is indeed the drop command, pretty similar to the “drop database”. The only difference is instead of “database”, we use “table”. Therefore, the command changes from “drop database” to “drop table”. The syntax is pretty similar too, with just a small change, which can be seen underneath:

With the command ready to go, let’s try it on our example.

Well, you can notice that we actually got rid of the table books. To further verify, if it worked or not, you can run the “show tables” command as I just did. The result won’t give you the table anymore.

Note: If you are confused with the use of CAPITALS in the syntax of the commands, then I would like to clarify it to you that there is no problem in using CAPITALS too and they also work fine. Rest you can try and explore yourself, maybe you’ll find something interesting.

 

 

What we learned?

This tutorial gave us an insight into tables, how to create, delete and do a certain operation on them. I think it’s time we actually summarize what we learned. Here’s a summary of that:

 

So, that was all about tables and the basic commands surrounding them. Moving on, in the next tutorial, we will work with data and thus give our tables the life they need.