Login
Latest Code Ticker for SQL.
Daily Code Email
|
|
|
| | 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 | | | Your Vote! |
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 *.
| 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
| 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?
| 4/18/2002 3:52:32 AM:Abhi Malani really good reference.
| 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
| 4/30/2002 1:09:26 AM:Peter Laing Just the thing I needed to
| | 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. | | |
|