Home arrow MySQL arrow Page 4 - SQL In Simple English Part 1/2
MYSQL

SQL In Simple English Part 1/2


SQL is the backbone of all databases. It is flexible, robust, and can be used to extract and manipulate data from databases. In this two part article, Kiran is going to describe the basics of SQL, as well as the more advanced features and functions, such as group by's, joins, etc. If you're new to SQL then this article will get you up to scratch in no time.

Author Info:
By: Kiran Pai
Rating: 5 stars5 stars5 stars5 stars5 stars / 8
May 09, 2002
TABLE OF CONTENTS:
  1. · SQL In Simple English Part 1/2
  2. · What Is SQL?
  3. · What is the INSERT statement and how do I use it?
  4. · Mathematical functions in SQL
  5. · Conclusion

print this article
SEARCH DEVARTICLES

SQL In Simple English Part 1/2 - Mathematical functions in SQL
(Page 4 of 5 )

There are many simple operations that you can 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

Same as the previous query, except it gets the average age of all people over 30.

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.

Ok, here are two 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 two keywords called GROUP BY and HAVING. Both of these are used in conjunction with aggregate statements like SUM , AVG , etc..

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



The GROUP BY keywords has 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, which we can get like this:

SELECT name, SUM(profit) FROM companies GROUP BY name

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

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

The query above 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

The query above uses field name alias. It returns the two columns with the headings as "Firm" and "Profit" instead of "Company" and "Amount".

As far as queries are 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’s see an example of using an alias now:

SELECT name "firm", profit "riches" FROM companies

Would return a recordset consisting of 3 records each with 2 values. Basically all of 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.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials