|
|
|
SQL In Simple English - Part I
|
Author: Kiran Pai |
|
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'
|
|
|
|
|
|
|
|
|
