DevASP.Net - SQL In Simple English


	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 COUNT DISTINCT(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 !!

Show me something new in SQL..
Ok here are 2 new things that I have used only a few times in my programs. But they maybe useful to you,so I shall talk about them. There are 2 keywords called GROUP BY and HAVING.

Both these are used in conjunction with the aggregate statements like SUM , AVG , etc..

For all the examples in this article we would be using a sample database table which is shown below

Table Name : companies

name	profit
Sega	25000
Microsoft	50000
Sega	10000

So what is GROUP BY? When do I use it?
The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.

SELECT name, SUM(profit) FROM companies
Returns a recordset with 3 records. Each record has 2 values. The first record would have the value 'Sega' and '85000'. The second record would have the values ' Microsoft' and '85000'. And the third record would have the values 'Sega' and '85000'.
Thus it is clear that this is not what was required. There is no sense in getting the sum of all the profits of all the companies along with each company name. What would be acceptable is the sum of all the profits of the respective companies along with that company's name. Read the next statement..

SELECT name, SUM(profit) FROM companies GROUP BY name
Returns a recordset with 2 records. Each record has 2 values. The first record would have the value 'Sega' and '35000'. The second record would have the values 'Microsoft' and '50000'.

And what is the HAVING keyword?
The HAVING keyword has been added to SQL because a WHERE keyword can not be used against aggregate functions (like SUM). Without the HAVING keyword it would not be possible to test for function result conditions.

SELECT name, SUM(profit) FROM companies GROUP BY name HAVING SUM(profit)>40000
Returns a recordset with 1 record. This record would have 2 values, namely 'Microsoft' and '50000'. Since the sum of the profits of the company by the name 'Sega' is only 35000 (which is lesser than 40000 as required in the Query)

SELECT Company "Firm", Amount "Profit" FROM Sales
Alias - Returns the 2 cols with the heading as "Firm" and "Profit" instead of "Company" & "Amount"

Do I have to work with the same column names that exist in the database tables, within my program?
Yes and No. As far as the Query is concerned, you have to include the column names that exist in the Database table in your Query. But you can also include an alias with which you can carry on further work with the returned results by the Database. Let me show you an example of using an alias

SELECT name "firm", profit "riches" FROM companies
Would return a recordset consisting of 3 records each with 2 values. Basically all the 3 records from the sample database would be returned , but the column names would be changed to those that were mentioned in the SQL statement. Thus name would be changed to firm. And profit would change to riches.

That all for now!! I guess you have got a basic idea of what SQL is all about. I shall be explaining more of SQL in SQL In Simple English - Part II