SQL Select with Order By, Insert, Update and Delete Command

SQL Insert

SQL INSERT statement is used to add rows to a table. Insert can be used in several ways:

• To insert a single complete row
• To insert a single partial row

For a full row insert, SQL Query should start with “insert into “ statement followed by table name and values command followed by the values that need to be inserted into the table. To insert a full row data into employee table, SQL query should be as shown below.

Insert into employee values (1,'Joy',28)

If we want to insert data only to few columns of the table, we should specify the column names to which data is going to be inserted after table name. To insert a value into a column or set of columns (Partial),SQL query will be as shown below.

Insert into employee(EMPLOYEE_ID, EMPLOYEE_NAME) values (1,'Joy')

SQL Update

SQL Update is used to update data in a row or set of rows specified in the filter condition. The basic format of an SQL UPDATE statement is, "Update" command followed by table to be updated and SET command followed by column names and their new values followed by filter condition that determines which rows should be updated.

To update a row in employee table, SQL query will be as shown below.

Update employee set age = 35 where EMPLOYEE_NAME = 'Joy'



SQL Delete

SQL Delete is used to delete a row or set of rows specified in the filter condition. The basic format of an SQL DELETE statement is, "DELETE From" command followed by table name followed by filter condition that determines which rows should be updated. To update a row in employee table, SQL query will be as shown below.

Delete from employee where EMPLOYEE_NAME = 'Joy'



SQL Select Statement

SQL “select” statement is used to retrieve data from table. For example, to retrieve data from the table given below, following SQL select statements are used

Table Name : Employee

Employee_ID
Employee_Name
Age
1
John
28
2
Alex
24
3
James
35
4
Roy
22
5
Kay
44





To retrieve complete data from table use SQL select Query

Select * from Employee;



Result is as given below

Employee_ID
Employee_Name
Age
1
John
28
2
Alex
24
3
James
35
4
Roy
22
5
Kay
44


To retrieve a column from table, use following SQL select Query

Select Employee_Name from Employee



The above query will produce following result.

Employee_Name
John
Alex
James
Roy
Kay





Select Employee_Name,Age from Employee


The result of the above query is given below.

Employee_Name
Age
John
28
Alex
24
James
35
Roy
22
Kay
44


SQL Alias Name

SQL Alias name is used to give a alias name to a column or a calculated field. For example, if item wise total need to be obtained from Sales table given below

Table Name : Sales

Item
Quantity
Price
Reduction
Item1
5
400
10
Item2
6
300
10
Item3
3
15
5
Item4
2
30
5
Item5
2
300
5
Item6
6
400
10





Select items, quantity * price Total from sales



Result of the above query is as shown below.

Item
Total
Item1
2000
Item2
1800
Item3
45
Item4
60
Item5
600
Item6
2400





Here result of quantity * price is returned as “Total” which is the alias name.

SQL Order By

SQL “Order by” clause is used to sort data retrieved from a table. For example, to retrieve data from the table given below sorted by employee name, following SQL select statements are used

Select * from Employee order by Employee_Name



Employee_ID
Employee_Name
Age
2
Alex
24
3
James
35
1
John
28
5
Kay
44
4
Roy
22





To retrieve data from the table sorted by multiple table columns, following SQL select statements are used.

Select * from Employee order by Employee_id, age



Employee_ID
Employee_Name
Age
1
John
28
2
Alex
24
3
James
35
4
Roy
22
5
Kay
44





SQL Sort Direction

To retrieve data from employee table, sorted by name descending, following SQL statement is used.

Select * from Employee order by employee_name desc





Employee_ID
Employee_Name
Age
4
Roy
22
5
Kay
44
1
John
28
3
James
35
2
Alex
24