If you are reading this article,
I assume that you already have an idea that databases are used to store data.
Still, if you are a novice, you will be wondering how to ask the database for
data and how to put data in to the database. SQL – Structured Query Language
provides you with an opening for talking to your database.
Every database has its own
unique ways to fetch data to its users. As time passed, there evolved a uniform
method of accessing data stored in the database – the SQL. The SQL, evolved as a
readable form of commands are now one of the most important methods to retrieve
data stored in your database.
During this article, we will use
SQL statements to create various database objects, select data from database,
populate the database with data and edit the data.
Introduction to SQL
SQL can be broadly defined as
the readable form of commands that are send to a database to retrieve data from
it. We are traveling in a structure by which, at the end of this article, you
will be having a fully functioning database that you had created using SQLs.
- The SQL Commands used here
are to be used with SQL Server 2000.
- To run all SQL Commands
discussed in this article, use SQL Query Analyzer, available with SQL Server
- The statements that are
discussed here are the simplest forms available. The complexity can increase
depending on actual requirements.
There are three broad categories
to which all SQLs are divided into. The DDL, the DML and the DCL. Let us take a
quick look at what these are.
DDL stands for Data Definition
Language. These are the set of commands that are used to define the shape of
data, and how the data is stored in the database. These commands also define the
structure of database and create the database objects.
The abbreviated form of Data
Modification Language, DML, is used to define/manipulate the actual data that is
stored in the database. This includes, inserting new data and updating stored
DCL or the Data Control
Language, provides you commands by which you can control access to your data.
This includes creating users and granting and revoking permissions to/from them.
Destination 1 – Create
Let us start our journey by
creating a database. To create a database, the CREATE DATABASE command is used.
You guessed that right. This is one among the commands in the Data Definition
This command creates a database
with the name as specified in the database_name section.
CREATE DATABASE my_Database
The command creates a database
by the name my_Database, and allocates storage space for the same.
Destination 2 – Create and
Since we have now created our
own database in the previous destination, the next step will be to find out how
we can create the tables in the database.
Note: Before running any of the
commands discussed in the following sections, make sure you select my_Database
from the database dropdown available in the Query Analyzer.
To create a table, you use the
CREATE TABLE command. This command also falls in the DDL category.
[Datatype] [Constraint constraint_name]
FirstTable_Field1 int PRIMARY KEY,
FirstTable_Field2 text )
Here, we create a table by name
my_firstTable, which has two rows, one called FirstTable_Field1 stores Integer
data and the second FirstTable_Field2 stores Text data. A real life analogy
will be a table that stores Student Ids and Student Names.
If you already have a table structure created, and you need
to modify the table structure, you use the Alter Table command. For example, you
have already created the my_firstTable table in the pervious section, and you
want to add one more row in to it or you want to modify one columns data type,
then, you can use the Alter Table command. You have a guess in which category
does the Alter Table command falls in to? Bingo! The DDL.
Let us see the prototype and an example.
ALTER TABLE table_name
ALTER TABLE my_firstTable Add
Using this ALTER TABLE command,
we are adding a new column to the table that we had already created in the
In a real life analogy, consider
the Students table that we had created in the previous section. When you get a
request that you also need to store Address information of all students, what
can you do? Well, now you know.. Use the alter table command to add a new
Destination 3 –
This is one important area of SQL, which is vast and is
used more often than any other SQL commands. This batch of commands falls under
the DML category of SQL. There are three important commands in this section.
Those are the Insert, Update and Delete commands.
The insert commands are used when you have to insert a
record to the table. Discussed below is the prototype, and an example.
INSERT INTO table_name
Where table_name is the table In
to which we are inputting the data, and column_names are the columns in sequence
in to which the data has to go, separated by commas. The data field holds the
actual data that goes in to the column names specified.
(1,’Name here’,’Address here’)
And as a real life example,
consider we are storing the details of a student, named “Steven”, who live in
“#42, My home street, My Town”.
Here is the command that we need
INSERT INTO x
(1,’Steven’,’ “#42, My home street, My Town’)
This command can be defined as,
the command that helps you update one or more records in a table.
In a real life analogy, consider
the student Steven has changed his name to Stefan. Then the database has to be
updated with his recent name. What will the query be? Let us see.
Where ID = 1
Destination 4 –
Now since we have discussed how to create a database,
create a table and insert/update data in to the tables, its time to discuss how
we can fetch data from the database. The SELECT statement is used to fetch data
from the table.
[ WHERE search_condition ]
[ ORDER BY order_expression ] ASC | DESC [ ]
We can elect to use the WHERE
condition which helps to filter the records that we need, and also the ORDER BY
clause which helps to retrieve the data in some logical order.
In a real life analogy, let us
consider this question from the Principal. He asks you to get the names and
address of students whose Ids are up to 10, in alphabetical order. Let us see
how you could please him.
Well it is that easy!
In this article, I have tried to
explain the mere basics of SQL. But I feel this can act as the spark from which
you can light the torch. Let us see how many of you can light it.