SQL Basics

Structured Query Language
Structured Query Language, which is better known as SQL (ES-Que-EL) is a computer language for database, used to retrieve, store, update, delete and to create database.

Brief History
In 1970, SQL was developed by Donald Messerly and Reymond Boyce who were working in IBM. Later in 1986 it was formally standardized by ANSI. Now SQL 2008 is being used in various development centers of Software around the world. This is the seventh of SQL Language series.

At Present
Now a days all available relational database management systems like MS SQL, MSDE, DB@, My SQL, Access, MS SQL Server etc use SQL as their standard for the creation of the database. So, it became very essential to learn the basics of the SQL. Here I’m providing the various SQL commands and good tutorials for the persons who have zeal to learn the language.


Fundamentals
The language SQL is used to performing different operation to manipulate date of the database like creating, selecting, inserting, dropping, deleting.. To run the each command a different command is used.

For example: If we want to select a “Last_Name” from our database “Login” whose “id” is “14”. Then our command would be

SELECT Last_Name FROM Login WHERE id=14;

In the above command what should be learn?

“SELECT” is the clause [keyword] used to select the name, this keyword is used to select the particular record name.

“FROM” clause [keyword] is used to refer the database “Login”. Here the name of the database is “Login”. The FROM clause [keyword] is to refer or indicate the database and from where the data will be accessed.

WHERE Clause [keyword] is used to refer the condition to select a particular condition from the database.

One more thing is to learn i.e. SQL is not a case sensitive. It means we can use and run the following commands as well.

Select last_name from login where id=14;

“ ; ” (Semicolon) is not mandatory, is optional. But it is a good programming practice to use a semicolon at the end of the command line.


1. Create Database

To create database there is a simple syntax is available and that will create a database.

Syntax

CREATE DATABASE database_name;

Example:

CREATE DATABASE sql_basic;

Or

Create database sql_basic;


2. Create
In database management system, a table is very important which refer to be a set of data of a particular database which must have columns and rows. The Keyword CREATE is used to create a table.

Syntax:

CREATE TABLE table_name
("column_01" "data_type", "column_02" "data_type", "column_03" "data_type") ;

Here in the above syntax the
“column_01”, “column_02”, “column_03” are the name of columns of the table table_name and
“data_type” refers that what type of data should be.

Example:

CREATE TABLE Login (
ID int NULL,
First_Name varchar (50),
Last_Name varchar (50),
Email varchar (50),
Gender varchar (50),
Martial_Status varchar (50),
Date_of_Birth datetime NULL ,
Password varchar (50),
Contact_Number varchar (50),
Company varchar (50),
Address varchar (100),
Salary float NULL,
City varchar (50),
State varchar (50),
Country varchar (50)
);

The above code will create a table of name “Login” in which there are 15 “columns” and their “data_types” are available with “size”.
Few points are to take a look:

* ID is taken as int and null.
* Names and other coulnms are taken as “varchar” and their sizes are as per their requirements.
* There is a function datetime has been used to get the time and date, there is no data_type is associated with the fuctions
* Salary has been taken as “float”.

Here we need to know what the data types:
Data Type refers that what kind of data should be like int, or char, or varchar.
Here few common data types are given below which can be used in this section.

# Char : 0 to 255 with 1 byte storage
# Varchar : 0 to 255
[Both are character data types of either fixed length or variable length]

# DateTime : January 1, 1753 to December 31, 9999

# Float : Depends upon the value of n
1-24 (n-value) 7 digits (precision) of 4 bytes
25-53(n-value) 15 digits(precision) of 8 bytes

# Number : Number value as per the columns are available in the table.



3. Insert
In database management system data are inserted into the table using the keyword INSERT. An statement with the keyword INSERT INTO will insert the data into the record.
There are two ways to insert the data into the table.


Syntax:

INSERT INTO table_name
VALUES (value_01, value_02, value_03,....)

or

INSERT INTO table_name
(column_01, column_02, column_03,...)
VALUES (value_01, value_02, value_03,....)


Here in the above syntax the INSERT INTO is the keyword followed by columns names of the table (column_01, column_02, column_03,..), VALUES is the keyword refers that following data is to be inserted in the table and value_01, value_02, value_03 are the value for their respective columns.

Example :

INSERT INTO Login
VALUES ('Kabir’, ‘Roy, 'Belapur', ‘coolreyan@gmail.com’, ‘M’, ‘Single’, getdate(), ’16564646’, ‘9823070730’, ‘MicroTech’, ‘Mahape’, ‘7000’, Navi Mumbai, ‘Maharastra’, ‘India’)

OR

INSERT INTO Login
(First_Name, Last_Name, Location,
Email, Gender, Martial_status, Date_of_birth,
Password, Contact_Number, Company_Name,
Address, Salary, City, Country)
VALUES ('Kabir’, ‘Roy, 'Belapur', ‘coolreyan@gmail.com’, ‘M’, ‘Single’, getdate(), ’16564646’, ‘9823070730’, ‘MicroTech’, ‘Mahape’, ‘7000’, Navi Mumbai, ‘Maharastra’, ‘India’)

Here in the above code “Login” is the table name and the value are in the statement are same as per the syntax. This is a very simple statement and used keywords are in a simple format.
Only one there is a point to note down. i.e. getdate() fuction is written in the value of Date_of_birth field. This function will automatically insert the current date and time in the database.
This function will be explained later in next article.

### Practice more with CREATE TABLE and INSERT INTO keywords.


4. UPDATE

In the database management system when a data is inserted , created and
manipulated then sometimes we need to update them as the time changes,
records are always for change.
In SQL we use a keyword UPDATE to update the database.

SYNTAX:

UPDATE table _name
SET column_01=value, column_02=value, column_03=value,...
WHERE My_column_01=My_value_01;

Here UPDATE keyword is followed by table_name, the the keyword SET is to change the values of their respective columns and WHERE keyword is indicating that a specific keyword to address the statement. This keyword can be the column_name of the database whose date is to update.

Example:

UPDATE Login
SET Salary=6100, Address=Andheri
WHERE id=15;

Here in this example the fields are to update, Salary and Address, and WHERE denotes that whose id is 15, that data is to update.

4. DELETE DATA

To remove a data from a database DELETE keyword [command] is used.

DELETE FROM “table _name”

Or

DELETE FROM “table_name”
WHERE My_Column = ‘My_Value’
[ Condition ];

Here in the above syntax there are as written DELETE is the command [Keyword] is to delete the entire table of the database.

Where in the second statement the condiation is same here but a condition is available. The condition States that should be true.

Example:

DELETE FROM Login;

OR

DELETE FROM Login
WHERE last_name=’Roy’;

Here the above command will remove the entire table “Login”
Whereas the nest Command will delete only one data whose “Last_name” is ‘Roy’. So, one shouldn’t skip the line starting with WHERE otherwise the entire table can be deleted.

DELETE command is very easy and simple to execute.

The rest will be in the next Ariticle.
Thank you
For any kind of assistance please mail me at
devashishbiswas@rediffmail.com


Comments

Author: Rajendra13 Mar 2009 Member Level: Gold   Points : 0

OK, GOOD ATTEMPT :)

Author: Phagu Mahato15 Jan 2014 Member Level: Gold   Points : 8

SQL stands for Structured Query Language.Two Types of SQL
DML ––Data Manipulation Language (SELECT)
DDL ––Data Definition Language (CREATE TABLE)

SQL Statements
The following SQL statement selects all the records in the "Examples" table:
SELECT * FROM Examples;
Most Important SQL Commands

  1. SELECT - extracts data from a database
  2. UPDATE - updates data in a database
  3. DELETE - deletes data from a database
  4. INSERT INTO - inserts new data into a database
  5. ALTER DATABASE - modifies a database
  6. CREATE TABLE - creates a new table
  7. ALTER TABLE - modifies a table
  8. DROP TABLE - deletes a table
  9. CREATE INDEX - creates an index
  10. DROP INDEX - deletes an index
  11. CREATE DATABASE - creates a new database


Pros & Cons of SQL



Pros:
Very flexible
Universal (Oracle, Access, Paradox, etc)
Relatively Few Commands to LearnRelatively Learn
Cons:
Requires Detailed Knowledge of the Structure of the Databaseof Database
Can Provide Misleading ResultsCan Results



Basic Structural Elements


  1. SELECT
  2. FROM
  3. WHERE
  4. ORDER BY
  5. GROUP BY

Selecting From Multiple TablesSelecting Tables


Join Multiple Tables via Primary and Foreign KeysJoin Keys
Aliases



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: