SQL Delete

The DELETE statement is used to delete rows in a table


SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value

The "custmast" table

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Chennai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30

Example : 1

Delete from custmast where custcode=5

custcode	custname	city	qty	rate
1	Siva	Virudhunagar	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Chennai	100	200
4	Vijay	Chennai	30	30

Example 2:

Delete from custmast where city='Chennai'

custcode	custname	city	qty	rate
1	Siva	Virudhunagar	50	70
2	Bala	Sivakasi	30	56

Delete All Records 


Delete from Custmast

custcode	custname	city	qty	rate

Delete * from custmast 

Error :  Incorrect syntax near '*'.


Notes :  Not use * Delete query in Sql server

Posted by Siva 0 comments

Read More

SQL Update

The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

SQL Update Example

The "Custmast" table:

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30
5	Kodee	Rajapalayam	60	30

Now we want to update the person "Siva" in the "custmast" table.

Example 1: 

Update CustMast Set City='Virudhunagar' Where custcode=1


Output :

custcode	custname	city	qty	rate
1	Siva	Virudhunagar	50	70

Example 2:

UPDATE custmast
SET custcode=6
WHERE CustName='Kodee' AND City='Rajapalayam'

Select * from custmast

Output :

custcode	custname	city	qty	rate
1	Siva	Virudhunagar	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30
6	Kodee	Rajapalayam	60	30

Posted by Siva 0 comments

Read More

SQL Insert

The INSERT INTO statement is used to Insert a New row in a table.

 It is possible to write the INSERT INTO statement in Two forms.

1) The first form Doesn't specify the column names where the data will be inserted, only their values:

Syntax

INSERT INTO table_name
VALUES (value1, value2, value3,...)

2) The second form specifies both the column names and the values to be inserted:

Syntax

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Example :( Doesn't specify the column names )

Insert into custmast values(1,'Siva','Srivilliputtur',50,70)

Output :

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70

Example :(  Specifies Both The Column Names  )

Insert into custmast (custcode,custname,rate) values(2,'Bala',56)

Output :

custcode	custname	city	qty	rate
2	Bala			56

Select * from Custmast

Output :

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala			56

Posted by Siva 0 comments

Read More

SQL Create Table

The CREATE TABLE statement is used to create a table in a database.

Create Table Syntax 

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

Create Table Example

 Create Table CustMast (

custcode numeric (12, 0),

custname varchar (50), 

city varchar (50), 

qty numeric (12, 0),

rate numeric (12, 2) )

 Learn to Sql Data Types  Click Me 

Posted by Siva 0 comments

Read More

SQL Create DB

The CREATE DATABASE statement is used to Create a Database.

SQL CREATE DATABASE Syntax

CREATE DATABASE  DataBasename

CREATE DATABASE Example

We use the following CREATE DATABASE statement:

CREATE DATABASE Inventory

Database tables can be added with the CREATE TABLE statement.

Posted by Siva 0 comments

Read More

SQL Order By

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.

The "custmast" table:

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30

Now we want to select all the CustMast from the table above, however, we want to sort the persons by their custname.

We use the following SELECT statement:

SELECT * FROM CustMast
ORDER BY CustName 

               (OR)

SELECT * FROM CustMast
ORDER BY CustName ASC

custcode	custname	city	qty	rate
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30
1	Siva	Srivilliputtur	50	70
4	Vijay	Chennai	30	30

ORDER BY DESC Example

Now we want to select all the persons from the table above, however, we want to sort the persons descending by their custname

We use the following SELECT statement:

SELECT * FROM CustMast
ORDER BY CustName DESC

custcode	custname	city	qty	rate
4	Vijay	Chennai	30	30
1	Siva	Srivilliputtur	50	70
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30
3	Kanna	Madurai	100	200
2	Bala	Sivakasi	30	56

Posted by Siva 0 comments

Read More