Using ORDER BY in SQL to sort data

Getting data sorted in advance is a wonderful feature of SQL. This means your program might not have to define it's own specific way to sort the information in your database, which can might life a lot simpler when programming in a complex language. Let's say we have a list of people.

Name,Age,Level,Job
"John Smith","39","Level 4","Computer Scientist"
"Jack Williams","22","Level 2","Mathemetician"
"John Black","29","Level 8","Artist"

You want to order them by age? Simple enough. SELECT Name,Age,Level,Job FROM People ORDER BY Age. This will return the lowest age first, and all the data with the person, etc, so you will get Williams, then Black, then Smith.

Using DELETE to delete rows from an SQL Database - Tutorial

Using DELETE to remove a row from your SQL Database is simple. You basically tell the delete command the name of the table, and you help it find which row to delete. Let us delete "John Smith" from our database, because he is planning to move out of the country and no longer will use our website.

DELETE FROM Persons WHERE Name='John Smith', Level='Level 3'

And that will delete any John Smith that we have in our database, so long as John also has a Level 3. Be very careful to specify your full deletion requirements. You don't want to accidentally delete the wrong people, or an entire table's worth of information! 'DELETE FROM Persons' or 'DELETE * FROM Persons' would both, in most cases delete all the information in the table. It still leaves the table structure, so it is useful if you need to start with fresh people and the same types of information.

UPDATE information in a database - SQL Tutorial

Updating information in a database is simple also. Last time you can see that we left off Jack's age. We found out it was his 23rd birthday last month, so now we can add this to the database. We'll do that simply by using an UPDATE statement to update his information.

UPDATE Persons SET Age=23 WHERE Name='Jack Smith'

If you're scared of there being two Jack Smiths, you might also add , Level='Level 8 to that statement. If you have two Level 8 Jack Smiths, then you really aught to learn a little about creating ids, so that you can only update the correct Jack.

INSERT information into your database with SQL

Inserting information into your database is also very simple. Just use the INSERT keyword, along with the data values that you want to put into your database. This creates a new row, or 'record', of information.

INSERT INTO Persons VALUES ('Jerry Smith', '32', 'Level 21')

This creates a new row which includes Jerry Smith, his age, and his level. You can also leave information empty in a new row, by telling SQL which fields you are adding. Do this with a slightly modifying INSERT INTO statement.

INSERT INTO Persons (Name, Level) VALUES ('Jack Smith', 'Level 8')

Notice that we don't know how old Jack is? This simply leaves Jack's age empty in the database. Sometimes, your database may not allow an empty (also called a NULL or NIL) value, so you might get an error. In that case, you might need to set something like 0 for a default age.

Searching through database with SELECT... WHERE

SELECT Name FROM Persons WHERE Age < 18

Let's say your website is now only for people 18 and older. You need to find everyone in your database that is under 18, so you can remove them or something. First, you use a select statement to return the information of all people under 18. How? With a WHERE age < 18. This returns anybody who's age is less than 18. You can also find other things, for example if you just want people named "John Smith" do this:

SELECT Name,Age,Level FROM Persons WHERE Name='John Smith'

Data Definition Language (DDL) - SQL Databases

The DDL part of SQL means that SQL creates databases and puts parameters on information. You don't want to have information such as "John Smith" "19" and "Level 5" floating around without definitions - your programs wouldn't know what to do with the information, and they wouldn't know what's what! DDL involves creating, modifying, or deleting tables.

CREATE TABLE - creates a new table
ALTER TABLE - changes things in an old table
DROP TABLE - deletes an entire table (and the data) from the database
CREATE INDEX - creates a search key (index) which you can use in SELECT and other statements
DROP INDEX - removes a search key (index)

Data Manipulation Language (DML) - Part of SQL

SQL allows you to manipulate data, which is really important. Let's say John's rank goes down and we need to change it to "Level 3" instead of "Level 5". We would use the UPDATE command to change his information.

SELECT - Retrieves information from the database for you.
UPDATE - Updates information in the database for you.
DELETE - Deletes data specified in the database.
INSERT INTO - inserts a new row of data in the database.