Basics of SQL - part II

In the last post we discussed about the basic statments like Create, Insert, Update and Delete statements. In this post we will play around with queries. For sorting the resultset, we can use the order by clause.

Lets create a table called Salary for using in our queries.

Execute the following statements.

Create table Salary(name varchar(30), Salary int)

and then insert some rows into the table. Insert the following rows.

Insert Salary values('A',759)
Insert Salary values('A',312)
Insert Salary values('A',456)

Now if you see the contents of the table by a select statement say

Select * from Salary

You would get the resultset like the following.

If you see in the picture the rows are not sorted as per salary. If you want to sort the rows by salary, you can use the following statement

Select * from Salary order by salary.

Now how to get the maximum salary. For this execute a statement like the following.

Select max(Salary) from Salary

For getting the minimum salary, execute the following query

Select min(salary) from Salary

You must have heard about the Desc command in Oracle which displays the schema information about a table. Similarly, in sql you have the sp_help statement which displays the information related to the structure of an object.

Execute the following statement

sp_help Salary

Its output would consists of the table information like name of the table, the columns the table has and any constraint the table is having

Now going back to the queries how could you get the total salary. For this, you can go for the sum function. Execute the following statement.

Select sum(salary) from Salary

What if you are interested to know the average salary. For this you can execute the following query

Select avg(salary) from Salary