In this article we will look at various SQL commands like Insert, Update, Delete, Select, Order By etc. which is used to manage and manipulate data with the help of examples.
SQL Insert
- To insert a single complete row
- To insert a single partial row
Insert into employee values (1,'Joy',28)
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.
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 |