SQL In Simple English - Part I

2005-06-11

**SQL In Simple English - Part I
**

**

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

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.

**