facebook  linkedin  Twitter  skype  Rss googlePlus

Beginning SQL Server Development

Dec 21 2004
5110

Boarding Point

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.

Notes:

  1. The SQL Commands used here are to be used with SQL Server 2000.
  2. To run all SQL Commands discussed in this article, use SQL Query Analyzer, available with SQL Server 2000.
  3. 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.

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.

DML

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 data.

DCL

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 database.

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 Language.

Prototype.

CREATE DATABASE database_name 

This command creates a database with the name as specified in the database_name section.

Eg:

           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 alter Tables. 

Create Tables

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.

Prototype.

            CREAT TABLE table_name
                        [ColumnNames] [Datatype] [Constraint constraint_name]

eg:

Create Table my_firstTable (
            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.

Alter Tables

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.

Prototype

ALTER TABLE table_name
            Add
            [ColumnNames] [Datatype]

eg:

ALTER TABLE my_firstTable Add
             FirstTable_Field3 text

Using this ALTER TABLE command, we are adding a new column to the table that we had already created in the previous section.

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 column.

Destination 3 – Insert/Update/Remove Records.

 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.

Insert Command.

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
                        (column_names….)
                        values
                                    (data)

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.

Eg:

INSERT INTO my_firstTable
(FirstTable_Field1,FirstTable_Field2, FirstTable_Field3)
Values
            (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 to use.

INSERT INTO x
(ID,Student_name ,Student_address)
Values
            (1,’Steven’,’ “#42, My home street, My Town’)

Update Command.

This command can be defined as, the command that helps you update one or more records in a table.

Prototype.

Update table_name
            Set
Column_name
=data
WHERE
[condition]

eg:

Update my_firstTable
Set
FirstTable_Field2=’data’
Where FirstTable_Field1=1

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.

            Update Students_table
Set
Student_name = ‘Stefan’
Where ID = 1

Destination 4 –  Select/Retrieve data.

 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.

Prototype. 

SELECT column_names
FROM table_name
[ WHERE search_condition ]
[ ORDER BY order_expression ] ASC | DESC [ ]

Ex:

Select FirstTable_Field1,FirstTable_Field2, FirstTable_Field
            FROM my_firstTable

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.

Select ID,Student_name ,Student_address
                        FROM Students_table
                        WHERE ID <=10
                        ORDER BY Student_name

Well it is that easy!

Final Destination

 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.

About the Author, Staff

You can write for Santry.com, just email us at [email protected] for more information.


blog comments powered by Disqus

 

Thank you for visiting our site, before you leave, please visit some of the areas or information you may have missed.

Popular Articles