Creating my first table in SQL

In my previous post we have seen how to create a database and use it.
Today we will create a table called as myAddressBook. Before that lets get into some basics of Table.

In context to myAddressBook table let us consider a simple example below, Everyone  often feels difficulty in remembering the contact details, birth date of family members, friends &colleagues. So usually we write it down in a sticky note/piece of paper but don’t you think it will be difficult to manage.

Let us assume that you have around 30 contacts in your list and you need to look for a specific persons contact details. You need to go through the multiple sticky notes/piece of papers to find the contact details. Now what if you have  more than one person with the same first name and also as the number of contacts grows it might be difficult to manage.

Well well.. now if you want to add some additional information like  phone number / profession,  then you need to modify the existing sticky notes. Here is the real challenge in managing the data or organizing it in a proper way.

To overcome this let us identify and categorize the information based on common characteristics like firstname, lastname, status, gender, location, birthday & email  as shown below.

firstName	lastName	status	gender	location	birthday	email
Naveen	Kumar	Single	M	London	1982-05-05	navi@testersdb.com
John	Mathew	Divorced	M	California	1980-12-12	john@catchme.com
Isabella	Hamilton	Single	F	SanDeigo	1984-04-02	isabi@mtsc.com
Isabella	Serge	Married	F	SanFransico	1982-05-02	isa_serg@abcd.com
Naveen	Krishna	Married	M	Sunnyvale	1985-10-03	naveenk@dcba.com

Here firstName, lastName, status, gender, location, birthday & email represent the categories based on which we have written our contact information in an organized way.

Now that we have written it in a structured way, still there is some effort involved in extracting the right piece of information, as the contact grows and if you are looking for a persons details somewhere in the middle/last or based on his last name/status/location you still have to go through the entire list till you get into the right information, isn’t it true? :)

From our previous post we understood that a database contains tables. With the above contact information being organized properly. Let us put it in the form of a table into our database.

A table in a database is nothing but a structure that contains data organized in columns and rows.  Here in our example the above categories becomes the column names to our table. Each row in a table represent a specific persons information i.e. each sticky note represent a row.

Now let us see how to create a myAddressBook Table. We shall use testers_db database to create our table (If you haven’t used testers_db as the current database, refer to my previous post on how to do it).

Type the below SQL command to create a myAddressBook table.

CREATE TABLE myAddressBook

(firstName VARCHAR(30), lastName VARCHAR(20), status VARCHAR(20), gender CHAR(1), location VARCHAR(50), birthday DATE, email VARCHAR(50));

Some important points to be noted here:

• CREATE TABLE is the SQL Command
• myAddressBook is the Table Name
• Opening parenthesis opens the list of columns to create followed by the column names.
• VARCHAR represents VARIABLE CHARACTER data type and holds text information, 30 indicates it can hold up to 30 characters, similarly applicable for the rest enclosed within brackets.
• CHAR represents CHARACTER data type holding 1 character in this example.
• DATE Data type to hold DATE information.
• Closing parenthesis closes the list of columns.
• Semicolon indicates the end of the command.

Now that we have created a Table, How will I view the created Table just to ensure everything is correct?

To view, how the created table looks, type in the below command.

DESC myAddressBook;

DESC – is nothing but DESCRIBE

Lets not worry on the NULL , DEFAULT and others as of now, I shall write about those in my next article.

Answering to my question from previous post, A DATABASE can have multiple TABLES in it. :)

In my next article we shall look into more details on NOT NULL, DEFAULT and also on deleting a Table. Till then happy reading..!!

Please feel free to drop me a comment either if you like my posts or not..!! :)

Technorati Tags: Create Table in SQL,Describe Table in SQL,create table from command line,mysql create table,mysql,sql