Are there better ways to use SELECT?
Yes there are.. and now you will learn some of the better ways of using the S
ELECT
along with some other SQL terms such as DISTINCT
, ORDER
, MAX
, MIN
, AVG
, etc..
For all the examples in this article we would be using a sample database table
which is shown below
Table Name : people
lastname | firstname | age | address | city |
Gates
|
Anthony
|
|
Circuit City
|
Bangalore
|
Hunter
|
Jason
|
|
Oak Street
|
San Jose
|
|
|
|
|
|
Kanetkar
|
Yashwant
|
|
Rajabhai Street
|
Nagpur
|
How could I get distinct entries from a table?
The SELECT
statement in conjunction with DISTINCT
lets
you select a set of distinct values from a table in a database. The values selected
from the database table would ofcourse depend on the various conditions that
are specified in the SQL query. Here are some ways to use the DISTINCT
keyword.SELECT DISTINCT lastname FROM people
Would return a recordset with 3 records. Each record would have 1 value in it.
So basically the first record would contain 'Gates', the second would contain
'Hunter' and the third would contain 'Kanetkar'. Inspite of the lastname
'Gates' being present twice in the table, only one occurrence of it will be considered
since the DISTINCT
keyword was used in the SQL statement.
Is there a way to get the results of a Query sorted in any order?
Yes there are ways which will sort the results and return the sorted results
to your program.. thus saving you the pain of carrying out the sorting yourself.
The ORDER BY keyword is used for sorting.SELECT firstname, age, city FROM people ORDER BY firstname
Would return a recordset with 4 records. Each record would have 3 values
corresponding to firstname, age and city. But the specialty
of this statement is that the records would be sorted according to the firstname
in ascending alphabetical order (A first - Z last).
e.g. The first record would be that corresponding to the person whose firstname
is 'Anthony' , followed by 'Bill' and then 'Jason' and finally 'Yashwant'.SELECT firstname, age, city FROM people ORDER BY firstname DESC
Would return a recordset with 4 record as the above case, but this time
the records would be in the reverse order. Namely the first record would be 'Yashwant'
and the fourth one would be 'Anthony'
How can I find the total number of records in a table?
You could use the COUNT keyword in many ways.. here are some ways.SELECT COUNT(*) FROM people WHERE age>40
Would return a recordset consisting of 1 value. Contrary to previous SQL statements
the COUNT statement return one value which directly indicates the total number
of records existing in the database that fulfill your conditions
e.g. In our case the above statement would return a value of 2SELECT COUNT(city) FROM people
Would return a recordset consisting of 1 value. And that value would be equal
to 4. The important point to note is that this statement return the total number
of Non-Null entries only.SELECT DISTINCT COUNT(lastname) FROM people
Would return a recordset consisting of 1 value. And that value would be equal
to 3. Remember that when you use the COUNT
keyword you do not get
the actual lastname of the persons but you only get the total number of
records that exist in the database that match your requirements. And in this
case since DISTINCT
was also used it would find the total number
of records where there are distinct firstname only.
I heard there is some mathematical stuff in SQL?
Yeah.. there are many simple operations that you could do in order to formulate
some useful information from a database rather than getting simple records from
the database. Here are a few examples of these mathematical operations SELECT AVG(age) FROM people
Would return 1 value corresponding to the average age of all the persons
that exist in the table people.SELECT AVG(age) FROM people WHERE age>30
You should be able to figure that out yourself.. if not please start reading
right from the first article in this series ;-)
SELECT MAX(age) FROM people
Returns the maximum age among all the persons in the table people.SELECT MIN(age) FROM people
Returns the minimum age among all the persons in the table people.SELECT SUM(age) FROM people WHERE age>20
Returns the total sum of all the ages of the persons whose age
is above 20 from the table people.
Comments