SQL

* SQL is a standard computer language for accessing and manipulating databases.

What is SQL?

• SQL stands for Structured Query Language
• SQL allows you to access a database
• SQL is an ANSI standard computer language
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert new records in a database
• SQL can delete records from a database
• SQL can update records in a database
• SQL is easy to learn

SQL Data Manipulation Language (DML):

• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database table

SQL Data Definition Language (DDL):

• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

SQL Data Control Language (DCL):

• Grant
• Revoke
• Rollback
• Commit

Way of procedure to create database and table (database name is vel and table name is murugan):

First we have to create database

Ex;

Create database vel;

Then use that database

Ex;

Use vel;

And then we have to create table

Ex;

Create table murugan (id int, name varchar (20), city varchar (20));

After create table we have to insert values

Ex;

Insert into murugan values (1,’velmurugan’,’cuddalore’);

If we want see that tables then we have to use following queries

Ex;

Select * from murugan;

Id	Name	City
1	Velmurugan	cuddalore

If you want to add more than two tables then

We should using like following queries

Ex;

Insert into murugan values (2,’balaji’,’cuddalore’),

(3,’suresh’,’cuddalore’), (4,’selvi’,’chennai’), (5,’sam’, ‘pondy’);

Then you want see that table

Ex;

Select*from murugan;

Id	Name	City
1             2             3             4             5	Velmurugan       Balaji       Suresh       Selvi        Sam	Cuddalore       Cuddalore       Cuddalore        Chennai          pondy

Select statement:

If you want see id no: 2 only means then we have to use

Ex;

Select id from murugan;

Id

1             2             3             4             5

If you want to see more than two fields, for example consider in name, city field.

Then

Ex;

Select name, city from murugan;

Name	City
Velmurugan       Balaji       Suresh       Selvi        Sam	Cuddalore       Cuddalore       Cuddalore        Chennai          pondy

In city field cuddalore comes three times if we want to see the city without difference then we have to use

Distinct statement;

Ex;

Select distinct city from murugan;

City

Cuddalore        Chennai          pondy

Where clause :

If we want to find out particularly city=cuddalore then

Ex;

Select * from murugan where city=’cuddalore’;

Id	Name	City
1             2             3	Velmurugan       Balaji       Suresh	Cuddalore       Cuddalore       Cuddalore

Condition cause (<,>, <=,>=, <> (not equal)):

If you want to find out less than and more than from numeric field i.e. id then

Ex;

Select * from murugan where id<3;

Id	Name	City
1             2	Velmurugan       Balaji	Cuddalore       Cuddalore

Ex;

Select * from murugan where id>3;

Id	Name	City
4             5	Selvi        Sam	Chennai          pondy

Ex;

Select * from murugan where id <=3;

Id	Name	City
1             2             3	Velmurugan       Balaji       Suresh	Cuddalore       Cuddalore       Cuddalore

Ex;

Select * from murugan where id >=3;

Id	Name	City
3             4             5	Suresh       Selvi        Sam	Cuddalore        Chennai          pondy

If we want find out without city=cuddalore then

Ex;

Select * from murugan where city<>’cuddalore’;

Id	Name	City
4             5	Selvi        Sam	Chennai          pondy

Like conditions:

If we want to find out the city with first letter then

Ex;

Select * from murugan where city like ‘c%’;

Id	Name	City
1             2             3             4	Velmurugan       Balaji       Suresh       Selvi	Cuddalore       Cuddalore       Cuddalore        Chennai

If we want to find out the name with last letter then

Ex;

Select * from murugan where name like ‘%i’;

Id	Name	City
2             4	Balaji       Selvi	Cuddalore         Chennai

If we want to find out the city with first two letters then

Ex;

Select * from murugan where city like ‘%cu%’;

Id	Name	City
1             2             3	Velmurugan       Balaji       Suresh	Cuddalore       Cuddalore       Cuddalore

Insert into statements:

If we want insert the data’s only for specified columns then

Ex;

Insert into murugan (id, city) values (7,’vadalur’);

Then

Select * from murugan;

Id	Name	City
1             2             3             4             5             7	Velmurugan       Balaji       Suresh       Selvi        Sam        NILL	Cuddalore       Cuddalore       Cuddalore        Chennai          Pondy         vadalur

Update statment :

If we want to update the data’s for empty columns then

Ex;

Update murugan set name=’barani’ where id=7;

Id	Name	City
1             2             3             4             5             7	Velmurugan       Balaji       Suresh       Selvi        Sam        barani	Cuddalore       Cuddalore       Cuddalore        Chennai          Pondy         vadalur

If we want update more than one column then

Ex;

Update murugan set id=6, city=’Salem’ where name=’barani’;

Id	Name	City
1             2             3             4             5             6	Velmurugan       Balaji       Suresh       Selvi        Sam        barani	Cuddalore       Cuddalore       Cuddalore        Chennai          Pondy          Salem

Delete statment :

If we want to delete that particular column from table then

Ex;

Delete from murugan where id=6;

Id	Name	City
1             2             3             4             5	Velmurugan       Balaji       Suresh       Selvi        Sam	Cuddalore       Cuddalore       Cuddalore        Chennai          pondy

If we want to delete overall table then

Ex; Delete from murugan;

Or

Delete * from murugan;

Order by clause:

If we want to see the name and city in alphabetical order then

Ex;

Select name, city from murugan order by name, city;

Name	City
Balaji         Sam         Selvi        Suresh                    Velmurugan	Cuddalore          Pondy        Chennai       Cuddalore                            Cuddalore

The above format we can use another model like (asc (ascending order), desc (descending order))                            

Ex;

Select name, city from murugan order by name asc, city desc;

And or clause:

The AND operator displays a row if ALL conditions listed are true.

Ex;

Select * from murugan where id=3 and city=’cuddalore’;

Id	Name	City
3	Suresh	Cuddalore

The OR operator displays a row if ANY of the conditions listed are true.

Ex;

Select * from murugan where id=3 or city=’cuddalore’;

Id	Name	City
1             2             3	Velmurugan       Balaji       Suresh	Cuddalore       Cuddalore       Cuddalore

You can also combine AND and OR (use parentheses to form complex expressions):

Ex;

Select * from murugan where (id=3 and city=’cuddalore’) and id=3;

Id	Name	City
3	Suresh	Cuddalore

In clause:

If we want to see that selected data’s it can be two or three then

Ex;

Select * from murugan where name in (‘balaji’,’selvi’);

Id	Name	City
2             4	Balaji       Selvi	Cuddalore         Chennai

Between ….And …clause:

The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.
Ex;

Select * from murugan where id between 2 and 5;

Id	Name	City
2             3             4             5	Balaji       Suresh       Selvi        Sam	Cuddalore       Cuddalore        Chennai          pondy

Ex;

Select * from murugan where id not between 2 and 5;

Id	Name	City
1	Velmurugan	cuddalore

Aliases clause:
If we want to change the table name and column name then we have to use alias clause
If you want change column name then
Ex;
Select id as userid, name as employeename from murugan;

Userid	employeename
1             2             3             4             5	Velmurugan       Balaji       Suresh       Selvi        Sam

If you want change the table name of particular column of table then
Ex;
Select name, city from murugan as balaji;

Name	City
Velmurugan       Balaji       Suresh       Selvi        Sam	Cuddalore       Cuddalore       Cuddalore        Chennai          pondy

The above table name is balaji.