In this article I will be trying to show how sql statements work.

The database model

The two tables are linked by the AddressId in a one to many relationship.

This means that there can be many Names linked to one address.

The data in the tables

Name Table

Address Table

So lets get down to the SQL.

SELECT

SYNTAX: SELECT [{tableName}.]{fieldname}[,[{tablename}.] {fieldname}] FROM {tablename}

This allows us to return all, or a subset, of the data in the tables.

SQL: to return all the fields and records in the name table

SELECT * FROM Name;

SQL: to return the id, surname, firstname fields of all the records of the name table

SELECT NameId, Surname, FirstName FROM Name;

WHERE

To enable us to have a subset of the data we can add a Where clause to the end of the statement.

SQL: to return all the fields, but only the records that contain Smith in the Surname field

SELECT * FROM Name WHERE Surname='Smith';

If we want to do a search.

SQL: to return all the fields, but only the records that start with 'An' in the Firstname table

SELECT * FROM Name WHERE Firstname Like 'An%';

There is no limited to the number of fields we can add to the WHERE clause

SQL: to return all fields from the records that have the Surname 'Wright AND that are Male

SELECT * FROM Name WHERE Surname='Wright' AND Male=True;

SQL: to return all the records that have the Surname 'Wright' OR that are Male

SELECT * FROM Name WHERE Surname='Wright' OR Male=True;

SQL:

SELECT * FROM Name WHERE (Surname Like 'Smith%' AND 

MiddleName='John') Or Male=False;

ORDER BY

It is possible to have the records returned in a certain order

• Ascending order is A to Z, 0 to 9
• Descending order is Z to A, 9 to 0

If nothing is specified then it is sorted into ascending order

SQL: to return the Surname and Firstname from all the records sorted in ascending order by the Firstname

SELECT Firstname, Surname FROM Name ORDER BY 

FirstName;

SELECT Firstname, Surname FROM Name ORDER BY FirstName ASC;

SQL: to return the Surname and Firstname from all the records sorted in descending order by the Firstname

SELECT Firstname, Surname FROM Name ORDER BY FirstName DESC;

GROUP BY

It is also possible to Group identical information together, but you have to put the fields that you want returned. It is not possible to put a * to say the whole table as we have been doing in the previous examples.

SQL: to return all the Male records and grouping the Middlename fields together, then the Surname and finally the FirstName

SELECT Surname, Firstname, MiddleName FROM Name WHERE Male=True 

GROUP BY Middlename, Surname, Firstname;

What if we want to select the address for the names. For that we need to use a JOIN (The way tables are joined together in a SQL statement depends on the database so I will give you 2 types Access and Oracle)

Access SQL:

SELECT Address.*, Name.* FROM Address INNER JOIN Name ON 

Address.AddressId = Name.AddressId;

Oracle SQL:

SELECT * FROM Address, Name WHERE 

Address.AddressID=Name.AddressID

We can put a Where clause statement on the end

Access SQL:

SELECT Address.*, Name.* FROM Address INNER JOIN Name ON 

Address.AddressId = Name.AddressId WHERE Name.Surname='Wright'; 

Oracle SQL:

SELECT * FROM Address, Name WHERE AddressID=Name.AddressID AND 

Name.Surname='Wright';

Modifying records

It's all very well being able to select the records but now we are looking at how to modify them.

The select statements are not going to return any errors if the sql is correct, they might return nothing, but they will work. The queries that modify records can return errors. You must make sure that all the fields that must have something in them are populated and that the fields have the correct type of data (no letters in number fields etc). Otherwise it will not be able to save the record and will return an error.

Another hick-up might be if you had a relationship between two or more tables, you may find that you cannot add data to one table before having a corresponding record in another table (i.e We have to have an address in the address table before we can create a record in the Name table to link to it). This could cause problems with deleting a record as well. There might be records in another table that are joined to the record you are trying to delete. This again will cause an error and stop the process.

UPDATE

So if we want to change the record.

SQL:

UPDATE Name SET Surname="Dickens" WHERE NameID=3;

SQL:

UPDATE Name SET Surname="Wright", FirstName="Ann" Where 

NameID=3;

INSERT

OK so now we need to add new records to the table. For this we use the INSERT command

SYNTAX: INSERT INTO {Tablename}({fieldname}[,{fieldname}]) VALUES ({value}[,{value}])

So to add a recordSQL:

INSERT INTO Name(NameId, Surname, FirstName, Male) VALUES 

(6, "Davis", "Ivan", true)

To add records to a table from another table assume we had another table called OtherNames:

OtherNames

SQL:

INSERT INTO Name(Surname, FirstName) FROM SELECT Surname, Name 

FROM OtherNames;

Notice the select statement it is wirtten in just the same way as if it was a SQL query on its own. So we could have had a subset of OtherNames added to the Name table.

i.e SQL:

INSERT INTO Name(Surname, FirstName) FROM SELECT Surname, Name 

From OtherNames WHERE Age < 30;

DELETE

Deleting a record This is acheived by using the DELETE command

SYNTAX: DELETE FROM {TableName} WHERE {criteria}

So if we wanted a table with just the women in we could use the following:

SQL:

DELETE FROM Names WHERE Male=true;

Or if we wanted to delete just one record

SQL:

DELETE FROM Names WHERE NameId=3

This I hope has given you a simple idea of how SQL works. There is a lot more too it, but knowing this should allow you to create small database applications.