Wednesday, July 17, 2013

What is SQL? What are its basic commands?

SQL (Structured Query Language) is a database computer language used for the retrieving and managing data in relational database management systems (RDBMS).

SQL contains DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language) and TCL (Transaction Control) commands. DDL statements are used to define the database structure or schema. There are different commands lies under this category.

DDL Commands:  Create, Alter, Drop, Rename, Truncate
            CREATE –      command is used to create objects in the database. Such as Create Table.
            ALTER –        command is used to alter the structure of the database. For example we can ALTER                                     Table for modifying existing column or adding new column in it.
            DROP -          delete objects from the database and it can’t be rolled back.
           TRUNCATE - remove all records from a table, including all spaces allotted for the records. It can’t                                      be rolled back. And it does not have where clause in command.
            RENAME –   command is used to rename an object

DML statements are used for managing data. DML Commands include Insert, Update, Delete and Select commands.
            INSERT -   insert data into a table
            UPDATE – it updates present data within a table
            DELETE - deletes all records from a table, but the space for the records remain means structure of                                 table exists. And it can be roll back and have where clause in command.
           SELECT -  retrieve data from the a database

Here are few examples emphasizing on syntax and usage of these commands. If we want to create Table Person, we will have to use the following syntax.

DDL Commands:

            CREATE TABLE Person
             (          PID numeric(10,0),
                        PName varchar(100) );
Now if you want to add some other column or modifying its type. Use Alter command.

// Adding Column
             ALTER TABLE Person
             ADD PAddress varchar(100);          
// Deleting Column
             ALTER TABLE Person
             DROP COLUMN PAddress;
// Alter Data Type of a column
             ALTER TABLE Person
             ALTER COLUMN PAddress varchar(MAX)

For deleting table, use DROP command. 
DROP TABLE Person;
To rename table,
use this code.

sp_RENAME "Person","Persons";

To delete all the rows from table, the query would be like,
TRUNCATE TABLE Person;

DML Commands:

Select clause can use with variations like this.

select* FROM Person //select all rows from table

SELECT Name FROM Person // it will select only Name column from Person (Selecting column wise)

SELECT * FROM Persons                
            WHERE Age = 31 // it will display record where age will be 31.
SELECT DISTINCT Name FROM Persons

For inserting data in table, use following code.

INSERT INTO course
VALUES (12,'OOP') // this query will insert 12 and OOP in respective columns of course table.

UPDATE  course
SET Course_Name = 'C++'               
WHERE Course_ID = 12;

Above command will update course table.


These were few examples concentrating on SQL commands. I hope you will find this post easy and helping.

1 comment:

  1. This is my first time visit at here and i am actually impressed to read everthing at alone place.


    my web page - Free Minecraft games

    ReplyDelete