Thursday 6 June 2013

SQL Queries

SQL COMMANDS 

* CREATE
* INSERT
* UPDATE
* SELECT




1. CREATE TABLE :- Create table is used to create a table witin the selected database.The Syntax to create table in SQL is :-

CREATE TABLE table-name
(
Field-Name1  Data-Type,
Field-Name2  Data-Type,
                  |
                  |
Field-Name-N  Data-Type,
)
Here, CREATE TABLE is Keyword used to tell SQL to create a new table within the selected database. CREATE TABLE keyword is following by a Right Paranthesis as a starting bracket . Then we will provide the field name it could be according to type of table  we have made e.g.- name, id , salary, designation etc.
Along with field name we give data-type that determines the type of data that field is expected to intake.

EXAMPLE :-

create table employee
(
Emp_id     int ,
Emp_Name   varchar(10),
Emp_designation    varchar(10),
Emp_Salary    int,
)


2. INSERT :- Insert command is used to input values or insert values in table of selected database. The Syntax to Insert data in table is :-

Insert into Table-Name Values ( Valuefield-1,Valuefield-2,Valuefield-3,Valuefield-N)

Here Insert, Into and Values are keywords 
Table-name is name of table in which you want to insert  data
Valuefield-1,Valuefield-2,Valuefield-3,Valuefield-n are values that we want to assign to fields or colums of table . Values in this statement are given in order in which fields or colums are declared in Create Table command previously.

EXAMPLE :-

1.  INSERT INTO employee VALUES ( 1001, 'Pawan' ,'SWO' ,45000)

2. 
INSERT INTO employee VALUES ( 1002, 'Rohan', 'Peon', 15000)

3. DELETE :- Delete command is used to delete row from table. The syntax of DELETE is as follows :-

Delete from Table-Name 
Where column-Name= 'column-value'

Where Delete is keyword used to tell SQL that we want to delete data and From is also a keyword used to specify table-Name 
Column-Name and Column-value is used to identify which row is to delete

EXAMPLE :-

Delete from employee
Where Emp_id=1001

4UPDATE :-Update command is used to Edit, modify or update particular column-value. The Syntax of Update command is :-

update Table-Name
Set Column-Name = column-value
where Column-Name = column-value

Here, update keyword is used to tell update work is to be performed




EXAMPLE  :-

1.
update employee
set Emp_designation = 'Manager'
Where Emp-id = 1001

2. 


update employee


set Emp_designation = 'Manager'

Where Emp_salary>40000


3.
update employee


set Emp_designation = 'Manager'

Where Emp_Name = 'Pawan'

USE REPLACE INSTEAD OF UPDATE 
we can also use Replace instead of using update queries . It can be used in situations when sometimes we have a field 'Name' in Table 'Table1' and we have inserted wrong spellings  of Name of a person in Table and we want to correct it In this case instead of using three line query of Update we can use single line query of Replace. 

Syntax :-
Select Replace ( String or string variable , old String  , new String  )

EXAMPLE :-

Select replace ( Emp_Name , 'ruhan' , 'rohan' ) 




5SELECT :- Select command is used to retriece data from Tables of Database. The Syntax of Select command is as follows :-



Select { * , column-name}

from Table-Name




  • Here, SELECT is keyword used to tell which colums is to select.
  • FROM is a keyword used to identifies which table is to use.
  • '* '  :- If Asterisk '*'  is used with select statement then This statement will display all colums and rows of database.

EXAMPLE  :-

1
Select * from employee
This statement will show all data in database

2. 
Select Emp_Name,Emp_Salary
From employee

USE DISTINCT WITH 

If we use Distinct keyword with select statement It will suppress the duplicate column values
The syntax is as follows :-

SELECT  DISTINCT { * , column-name}  FROM Table-Name

EXAMPLE :-

Select Distinct  * from employee


5. DELETE :- Delete query in SQL is used to delete the specified row from table .The entire row will be deleted from table and others rows will not be affected . A set of rows can also be deleted from table by using specific condition . It is mostly used with where clause. The Syntax of DELETE command is :-

SYNTAX :-
DELETE      From  Table-Name
Where     condition 

In Syntax :-
* Table-Name is name of table on which delete operation is to perform
* condition tells which and what number of rows are to be deleted from table

EXAMPLE :-  If we have a table consist of fields shown in image below :-





QUERY :-

delete from student
where rollno=1003



OUTPUT :-










Share this

0 Comment to "SQL Queries"

Post a Comment