How do I delete a record from a database?
Use the DELETE statement to remove records or any particular column values from a database.

DELETE FROM people WHERE lastname = 'Hunter'

Would remove the entire record which represents any person whose lastname is 'Hunter'. In our case it would remove 1 record from the sample database table people. It would remove all the values that were a part of that record.

Is there a way to update any record in a database?
Yes. You could use the UPDATE statement. The update statement updates (or replaces) those values that were specified in the SQL statement with the new values provided.

UPDATE people SET age = 50, city = 'Mumbai'
	WHERE (lastname = 'Hunter' AND firstname='Jason')

Would change Jason Hunter's age from 41 to 50 and would make him shift his residence from 'San Jose' to 'Mumbai'. Isn't that cool?? A new Java Guru is Mumbai !!

Are there better ways to use SELECT?
Yes there are.. and now you will learn some of the better ways of using the SELECT 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 :

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 2.

SELECT 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.

How do I delete a record from a database?
Use the DELETE statement to remove records or any particular column values from a database.

DELETE FROM people WHERE lastname = 'Hunter'

Would remove the entire record which represents any person whose lastname is 'Hunter'. In our case it would remove 1 record from the sample database table people. It would remove all the values that were a part of that record.

Is there a way to update any record in a database?
Yes. You could use the UPDATE statement. The update statement updates (or replaces) those values that were specified in the SQL statement with the new values provided.

UPDATE people SET age = 50, city = 'Mumbai'
	WHERE (lastname = 'Hunter' AND firstname='Jason')

Would change Jason Hunter's age from 41 to 50 and would make him shift his residence from 'San Jose' to 'Mumbai'. Isn't that cool?? A new Java Guru is Mumbai !!

DELETE FROM people WHERE lastname = 'Hunter'

UPDATE people SET age = 50, city = 'Mumbai'
	WHERE (lastname = 'Hunter' AND firstname='Jason')

SELECT DISTINCT lastname FROM people

SELECT firstname, age, city FROM people ORDER BY firstname

SELECT firstname, age, city FROM people ORDER BY firstname DESC

SELECT COUNT(*) FROM people WHERE age > 40

SELECT COUNT(city) FROM people

SELECT DISTINCT COUNT(lastname) FROM people

SELECT AVG(age) FROM people

SELECT AVG(age) FROM people WHERE age > 30

SELECT MAX(age) FROM people

SELECT MIN(age) FROM people

SELECT SUM(age) FROM people WHERE age > 20

DELETE FROM people WHERE lastname = 'Hunter'

UPDATE people SET age = 50, city = 'Mumbai'
	WHERE (lastname = 'Hunter' AND firstname='Jason')