Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

SQL at a Glance Part 2

Monday, September 15th, 2014 by Servage

Intro to SQL

In previous part of this series, we have explored the fundamental concepts of databases in relation to SQL. Therefore, keeping the thread continue, we will see how SQL accesses and manipulates databases in this post.

In my experiences with various intricate data driven web development project such as patient registry, clinical trials, etc. where dealing with huge databases was primary task, many of my colleagues were afraid of SQL and database management, junior web developers in particular. No doubt, understanding numerous data interactions in such projects is a daunting task at first hand, but executing SQL commands afterwards is not tough at all. SQL syntax is easy and mostly based on simple English language like structure so even newbies can perform outstanding if have strong desires in mind.

SQL has many flavors and variants, but we will stick with ANSI standards. The flavor of SQL is changing with the database software available in the present market. For instance, Oracle uses PL/SQL whereas Microsoft is betting on Transact-SQL. SQL commands has mainly two sub sections: DDL and DML. The former is for adding or deleting databases or database objects. The later one is dealing with database management aspects like insert, retrieve and modify the data in and across databases.

DDL Section of SQL

As discussed above, Data Definition Language (DDL) section of SQL has main two intentions: create and destroy. As their names suggest, they are acting when DBMS is install or remove on client or server. Apart from this, they use to create database objects within databases or across the databases. Primarily, database tables are referring as database objects. Simple CREATE tag is enough to execute command.

We can write create table statement in following standard way:

create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");

For instance, if we are going to create a table for employee in an organization the code would be:

create table employee
(first varchar(15),
last varchar(20),
age number(3),
address varchar(30),
city varchar(20),

You may have noticed that we have used varchar replacing data type. Here varchar is coming from char that indicates the fixed length of character string and its size is specify in parenthesis. Therefore, varchar is simple variable length of character string same the way.

Same the way we can other DDL commands are USE, ALTER, and DROP. If we look at them in brief, we can say that USE command is working to specify the database you want to work with within DBMS. However, it is quite different from SELECT command and only act on entire database or data object a whole instead selecting columns or rows. ALTER command is altering the definition of table once it created as per needs. Moreover, it can avoid deletion and recreation of table in order to altering the structure of table itself. Its code example is:

ALTER TABLE personal_info
ADD salary money null

Finally, DROP command is allowing us to remove entire table/database/data object from the DBMS. It is quite different from DELET command in a sense that it removes entire table not parts of individual records like column or row after employing SELECT and DELET commando of DML.
For instance,

drop table "tablename"
drop table myemployees_ts0211;

However, use of DDL commands are limited and done with utmost care as they are dealing directly with entire databases or tables in DBMS against the most frequently used DML commands to manipulate existing databases. Due to space constraints, we will see them in next post of this series.

References & More Reading
Structured Query Language (SQL)
Data Definition Language
Data Manipulation Language

SQL at a Glance Part 2, 3.7 out of 5 based on 3 ratings
Categories: Guides & Tutorials


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.