Learning SQL - Beginners Tutorial - Part 2
Hello and Welcome back to SQL beginners tutorial part 2. In the previous article, as a first part in the series we learnt till how to insert the data in the tables. Today we dig further and we will try to learn more SQL functions and working more with table.
Ok, before moving any further lets talk about naming conventions first. Few important thins in this regard as described below:
- Decide the names in such a way that it clears the purpose of the table like table name 'personal' better than writing 'abc123', although which is correct but it doesn't clear about the kind of data it will store.
- Be consistent in the type of names you create for ex. if your one table name says 'data_puneet' then the other tables in your databasse should also follow the process of having an underscore in the table name. You won't realize it now, but when you revisit your database after some time or if someone looks at it, it will be very easy for him to understand that what kind of objects are represented by which kind of names.
- Try to use command names in capitals which will help differentiating in commands and table or row name. Ex. the command CREATE TABLE 'personal'. Now doesn't it makes it clear ?
- Use Semicolon at the end of your command to clear that the particular command has ended
Please not that the above naming conventions are just to add ease to the work, they are nothing like mandatory to have to work with SQL because SQL is a case insensitive language, so you need not to worry about writing it in any manner. Just make sure you can understand it.
Data Types
As you can remember, while creating the table, we defined the column names with some data type which that column can use. Lets try to see which are the different data types available in SQL that we can use. One imprtant thing to note is that it is not necessary that one data type is consistent in differnt database systems. Our purpose is to understand the data types. So just try to go through the help section of the particular database you are using and which data type are supported by it. Take a look at below screenshot.
Above table is having the columns which are using different data types:
- VARCHAR:This data type hold the text data upto 255 characters and adapt to the length of the data.
- CHAR: Its a rigid data type and prefers data to be of the same length.
- INT: This data type takes only numbers and can also handle negative numbers.
- DATE: This data type handles the dates and it will only allow that format only ex: 11-17-2012.
- BLOB: This data type just needs text in large amount and can be used to add comments for understanding.
The above are the basic and the most used data types, but this is not the end of the list. There are other data types used also and speficily to improve the performance.
Note: We will use VARCHAR in most of the cases because lets say we define a VARCHAR(50) for the name , but the name may or may not be completely upto 50 characters. So VARCHAR will use only that much space which is being defined by the value in that column, where as CHAR data type doesn't support this behaviour.
Checking the Data Type
In case you come accross a table which is already created and you want to see the data types being defined in the column, you need to use the command
DESC <TABLE NAME>
And it will show the output as shown in the below screenshot. Here DESC means describe the table.
Alter Your Table
There are many times when we create a table and after that there is a need to change either the data type of the column or even adding a new column to the table and in that case alter table comes to our rescue. Lets see how to use that. We have our table 'personal' which we created in the last tutorial and in that table lets say you want to add the `email` column. We can do that using below command:
ALTER TABLE `personal` ADD email varchar(50);
The above command in SQL will add the email column in your table and your table will look like this:
Isn't that easy? There is one another hard way, which is, deleting your table and creating it once again. Will it make sense to you? No, i don't think so. So lets stick to alter table only.
Dropping Your Table
Lets say after creating your table, you had a feeling that this is not the table you wanted or all the column names have are wrong and you want to start from scratch. Or you just want to delete the table without any reason, so we have an option in SQL to delete the table in that case using the command:
DROP <Table Name>
In table name , write the name of the table you want to delete and you are done. The table is removed from the database. Please take care while running this particular command as it will also delete any kind of data present in the table and there is no option to take it back. There are cases when system doesn't allow you to drop the table because some fields of that table might be used by another table, which is a concept of refrencing, and in that case we need to remove the refernece first and then drop the table. We will cover that in the coming tutorials.
Defining Primary Key
Every table in the database needs to have a primary key. It means there should be atleast one column in the table which uniquely defines a particular row from the other rows and that value can never be same in two different columns. For example our mobile number is unique. No two people can have the same mobile number and so we can define the number column as the primary key in our table which defines the primary key.
The main purpose of primary key is also that it will never allow you to have two entries with the same primary key, so you will always get the error when you try to insert a row in the table with same primary key. In case there is no field in the table which can be defines as a primary key , then we can use multiple columns together to define a primary key.
You can use below command to define the primary key in a table:
As shown, we have added 'phone no' as primary key and hence it will always be unique in each row , which helps us to identify each row seperately.
Two Columns as Primary Key
In other case, if lets say be any reason we don't want to have phone number as a unique constraint because there might be a case when two people are using the same phone number, so in that case we to still define a primary key , we can use two columns as shown in the below screenshot.
The above combination can be a very good combination to define the primary key and is very comonly used to define the unique constraint whenever we dont have specific column do be defined as primary key. You can add more columns also in case two columns still doesn't make a unique constraint.
Do practice them and feel free to ask me about any issues.
To be Continued..!!!
Like it on Facebook, Tweet it or share this article on other bookmarking websites.