SQL In Simple English Part 1/2 - What Is SQL?
(Page 2 of 5 )
SQL stands for 'Structured Query Language'. There are dozens of databases around today, the most common ones being Oracle, Microsoft SQL Server and MySQL. For us to write programs that interact with these databases easily, there has to be a way that we can get information from all of the database types using a common method. For this purpose SQL was developed. It is a kind of language (simple when compared to the likes of C or C++) that enables you to ask queries to a database without bothering about the exact type of database.
Ok lets get straight to the point. Suppose you have a database which has a table called people 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.
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 that 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.
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.
Now we will look at the basic SQL statements such as SELECT, INSERT, UPDATE and DELETE. For all the examples in this article we would be using a sample table (called people), which is shown below:
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.
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.
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'.
You can use boolean operators such as AND and OR to make complex queries. 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 the 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'.
Next: What is the INSERT statement and how do I use it? >>
More MySQL Articles
More By Kiran Pai
