found,many,articles,good,detailed,they,really
Quick Search for:  in language:    
found,many,articles,good,detailed,they,really
   Code/Articles » |  Newest/Best » |  Community » |  Jobs » |  Other » |  Goto » | 
CategoriesSearch Newest CodeCoding ContestCode of the DayAsk A ProJobsUpload
SQL Stats

 Code: 31,327 lines
 Jobs: 372 postings

 
Sponsored by:

 

You are in:

 
Login



Latest Code Ticker for SQL.
Unix Date Convertor Function
By George Graff on 10/23


Insert pipe delimited rows into multiple rows.
By Charles Toepfer on 10/21


Logfiles by PL/SQL
By Stephan Rechberger on 10/21


To display a name in a default language if the given one doesn't exist
By Serge Alard on 10/18


Order by column except a few values
By Serge Alard on 10/18


Introduction to PL/SQL (Series 3) Cursors
By David Nishimoto on 10/14


Sorting a String using T-SQL
By Gaurav Pugalia on 10/12


Protecting against TSQL virii, worms and time bombs
By Joseph Gama on 10/11


Click here to see a screenshot of this code!Get size in bytes of SP, View, Trigger, UDF or Rule
By Joseph Gama on 10/11

(Screen Shot)

Click here to put this ticker on your site!


Add this ticker to your desktop!


Daily Code Email
To join the 'Code of the Day' Mailing List click here!





Affiliate Sites



 
 
   

SQL In Simple English - Part 1

Print
Email
 

Submitted on: 3/5/2002 2:12:50 AM
By: Kiran Pai 
Level: Beginner
User Rating: By 19 Users
Compatibility:SQL Server 7.0, SQL Server 6.5 and earlier, Oracle, Other

Users have accessed this article 10614 times.
 
(About the author)
 
     I have found that many articles found on the net are good and detailed but they are not really friendly to novice programmers. In my article the topic is presented as a Q&A; session. Most of the questions are the ones that most novices would like to ask. This approach is excellent and readers who aren not familiar with the technology. Since this article is totally language neutral, programmers using any language and who want to do database programming will find it useful.

 
 
Terms of Agreement:   
By using this article, you agree to the following terms...   
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.   
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
SQL In Simple English
SQL In Simple English - Part 1



What is SQL?
SQL stands for 'Structured Query Language'

 

What does that mean?

There are a lot of Databases available in the market such as MS Access, Oracle and many others. For you to write programs that interact with these databases easily, there has to be a way where you could get information from all these databases using the same method. For this purpose SQL was developed. It is a kind of language (simple when compared to the likes of C or C++) which enables you to ask all your queries to a database without bothering about the exact type of database.

 

What the heck is SQL??!!!

Ok lets get straight to the point. Suppose you have a database which has a table called people. (I hope you know what tables are). And you want the details of all persons whose firstname is 'Reena'. So you could use a SQL statement as follows

SELECT * FROM people WHERE firstname = 'Reena'

When you use this Query the database engine would first find the table called people. Then it would find a column called firstname. Next it would compare all the values in that column with 'Reena'. Finally it would return all the details wherever it finds a match for the firstname.


Tell me something more of the bigger picture..
When you write a database program in VC++ or Java or any other language for that matter, you would make a database connection to your database and then you would query the database using SQL queries. When you query the database with any SQL query the database returns a recordset. A recordset is basically a set of records (all the entries that your query returns). This recordset is received in your program and all languages have a data structure which represents a recordset. Once this data structure (in your program) gets populated with the results from the database query, your could use a for loop to loop through all the entries.


How do I connect to a Database through my program?
Hey guys.. this is a tutorial on SQL.. so I wouldn't be focussing on those aspects in this series


What was that thing about recordsets?
When you connect to a database and execute SQL Queries,the results of the Query are returned back to your program. This returned data has to be stored in some Object or Data Structure within your program to be used by your program. Once you store the results in this Object you no longer have to be connected to the Database. For a more detailed explanation please refer to a book on Database programming.



What are you going to discuss now?
Now you will learn basic SQL statements such as SELECT, INSERT, UPDATE and DELETE.
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
Pai
Kiran
22
Mahavir Nagar
Mumbai
Hunter
Jason
41
Oak Street
San Jose
Kanetkar
Yashwant
38
Rajabhai Street
Nagpur

 

What is SELECT statement? How do I use it?
The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query. Here are some ways to use the SELECT statement. I have listed the SQL statements and the respective results that you would obtain if you would execute those queries.

SELECT lastname,firstname FROM people
Would return a recordset with 3 records. Each record would have 2 values.
The first record would have 2 values 'Pai' and 'Kiran'. Whereas the second record would have 2 values 'Hunter' and 'Jason'.

SELECT * FROM people WHERE firstname='Jason'
Would return a recordset with 1 record. The record would have 5 values in that, namely 'Hunter' , 'Jason' , '41' , 'Oak Street' and 'San Jose'.

SELECT * FROM people WHERE age>25
Would return a recordset with 2 records.

Note : That whenever you are comparing a varchar the value should be enclosed in single inverted commas ( ' ). Whereas when you compare an integer the value need not be enclosed in single inverted commas.

 

How can I compare a part of the name rather than the entire name?


SELECT * FROM people WHERE firstname LIKE '%an%'
Would return a recordset with 2 records. This statement would return 2 records since the sequence 'an' occurs in 2 firstname values, 'Kiran' and 'Yashwant'.


Can I use Boolean operators such as AND or OR to make complex queries?
Good news!! Yes you can.. Actually as a matter of fact, once you start developing professional database applications you would almost always use such Boolean operators to make effective queries.

SELECT address FROM people WHERE (firstname='Kiran' OR city='Nagpur') AND age>30
Would return a recordset consisting of 1 record with 1 value only. Since AND condition specifies that the firstname of the person could be 'Kiran' or his city could be 'Nagpur' , BUT that person has to be over the age of 30. The recordset would have only 1 value in it : 'Rajabhai Street'.


What is the INSERT statement? How do I use it?
The INSERT statement lets you insert information into a database. A few examples are shown below

INSERT INTO people VALUES ('Bush', 'George', 47 , 'White House', 'Washington')
Would insert a new row at the bottom of the table people consisting of the values in parentheses in the above statement.

INSERT INTO people (lastname, city) VALUES ('Gates', 'Redmond')
Would insert a new row at the bottom of the table people consisting of only 2 values as present in the above statement, namely 'Gates' and 'Redmond'. The remaining columns for that particular record would be left empty (null).

Note : A null value is different from 0 or ''(Empty String). A perfect example of this would be a column describing the hair colour for many people. In case the person is bald then the value of the colour should be null rather than empty. This would be perfect from the database design view. A particular entity which doesn't exist should be represented similarly and not by empty Strings.


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

Table Name : people
lastname
firstname
age
address
city
Gates
Anthony
11
Circuit City
Bangalore
Hunter
Jason
41
Oak Street
San Jose
Gates
Bill
59
Microsoft Corp.
Redmond
Kanetkar
Yashwant
38
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 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 !!




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.


This article has been written by Kiran Pai. All comments and feedback may be sent to paikiran@yahoo.com
This article should not be modified in any form. In case you want to host a copy of this article on your site please request for authors permission before doing so


Other 1 submission(s) by this author

 

 
Report Bad Submission
Use this form to notify us if this entry should be deleted (i.e contains no code, is a virus, etc.).
Reason:
 
Your Vote!

What do you think of this article(in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor See Voting Log
 
Other User Comments
3/6/2002 4:38:25 AM:Charles Kincaid
There is a little understood side effect of SELECT *. The order of the returned columns is not guaranteed. Some database providers support an “ordinal position” parameter for columns and some do not. I have actually been in a situation where reloading a database caused the column order to change and broke my code that used SELECT *.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
3/6/2002 10:40:17 AM:Chris Tucker
Yes, with SQL server, if there is a clustered index, that will be used to sort the fields, otherwise you are limited to how the records are written to the data pages. Setting index's on fields that are commonly sorted is one of the first optimizations that you should create for a table. This is a good start as far as beginner articles, you should continue on next with good design practices incudint "Normailization Rules" and so on. Thanks
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
3/6/2002 10:44:24 AM:Hung
What happens if you want to do subsummaries, for example, sort by name, sort by city and then sum them up? In excel one uses the double/triple sort function?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/18/2002 3:52:32 AM:Abhi Malani
really good reference.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/22/2002 11:46:10 PM:Robin Thompson
There when I needed it! Working late one night on a college project when I ran up against a problem where I needed to search a database in VB. I was simply trying to search a field in one table, compare the values in the field to a user input on a form so the name would not be reused. I knew I needed SQL but had no knowledge of how to conduct a search with it. This site provided just what I needed. Thank you very much. Sincerely Robin Thompson
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/30/2002 1:09:26 AM:Peter Laing
Just the thing I needed to
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
Add Your Feedback!
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you.  If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.
 
Name:
Comment:

 

Categories | Articles and Tutorials | Advanced Search | Recommended Reading | Upload | Newest Code | Code of the Month | Code of the Day | All Time Hall of Fame | Coding Contest | Search for a job | Post a Job | Ask a Pro Discussion Forum | Live Chat | Feedback | Customize | SQL Home | Site Home | Other Sites | About the Site | Feedback | Link to the Site | Awards | Advertising | Privacy

Copyright© 1997 by Exhedra Solutions, Inc. All Rights Reserved.  By using this site you agree to its Terms and Conditions.  Planet Source Code (tm) and the phrase "Dream It. Code It" (tm) are trademarks of Exhedra Solutions, Inc.