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.
SQL In Simple English Part 1/2 - What is the INSERT statement and how do I use it? (Page 3 of 5 )
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 people table 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 statement above, namely 'Gates' and 'Redmond'. The remaining columns for that particular record would be left empty (null).
A null value is different from 0 or '' (empty string). A perfect example of this would be a column describing the hair color for many people. In case the person is bald then the value of the color 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.
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 table people. It would remove all the values that were a part of that record.
You can use an UPDATE statement to update one or more records in a table. 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?
There are better ways to use the SELECT command, and we will now learn some of the better ways of using it, along with some other SQL terms keywords as DISTINCT , ORDER , MAX , MIN , AVG , etc..
For all the examples in this article we will be using the sample table that is shown below:
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 of course 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.
There are also 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). For example, 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 records as the above case, but this time the records would be in reverse order. Namely, the first record would be 'Yashwant' and the fourth one would be 'Anthony'.
To get the number of records for a specific query, we can use the COUNT keyword. You could use the COUNT keyword in many ways. Here are some of them:
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. That value would be equal to 4. The important point to note is that this statement returns the total number of Non-Null entries only.
SELECT COUNT DISTINCT(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 people, but you only get the total number of records that exist in the database that match your requirements. In this case since DISTINCT was also used it would find the total number of records where there are distinct firstname values only.