SQL for Beginners

The database model

-------Name Table-------------		-------Address Table----------
NameId type Long		AddressId Type Long
Surname type String		Line1 type string
Firstname type String		line2 type string
Middlename type string		City type string
Male type boolean		ZipCde type string
AddressId type Long

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

NameID	Surname	FirstName	MiddleName	Male	AddressId
1	Smith	Andrew	John	true	1
2	Smithe	Fred	John	true	2
3	Wright	Anne		false	3
4	Jones	Emily	Anne	false	1
5	Wright	David	Peter	true	3

 

Address Table

AddressId	Line1	Line2	City	ZipCode
1	A Street		London
2	A Road	A Town	Oxon
3	A House	Village	Oxon	OX1 3ED

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;

Result:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	2	Smithe	Fred	John	true	2
	3	Wright	Anne		false	3
	4	Jones	Emily	Anne	false	1
	5	Wright	David	Peter	true	3

Or we could only return certain fields

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

SELECT NameId, Surname, FirstName FROM Name;

Result:	NameID	Surname	FirstName
	1	Smith	Andrew
	2	Smithe	Fred
	3	Wright	Anne
	4	Jones	Emily
	5	Wright	David

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';

Result:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1

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%';

Result:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	3	Wright	Anne		false	3

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;

Result:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	5	Wright	David	Peter	true	3

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

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

Result:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	2	Smithe	Fred	John	true	2
	3	Wright	Anne		false	3
	5	Wright	David	Peter	true	3

SQL:

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

MiddleName='John') Or Male=False;

Result:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	2	Smithe	Fred	John	true	2
	3	Wright	Anne		false	3
	4	Jones	Emily	Anne	false	1

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;

or

SELECT Firstname, Surname FROM Name ORDER BY FirstName ASC;

Result:	FirstName	Surname
	Andrew	Smith
	Anne	Wright
	David	Wright
	Jones	Emily
	Fred	Smithe

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;

Result:	FirstName	Surname
	Fred	Smithe
	Jones	Emily
	David	Wright
	Anne	Wright
	Andrew	Smith

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;

Result:	Surname	FirstName	MiddleName
	Smith	Andrew	John
	Smithe	Fred	John
	Wright	David	Peter

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

Result:	AddressId	Line1	Line2	City	ZipCode	NameId	Surname	FirstName	MiddleName	Male	AddressId
	1	A Street		London		1	Smith	Andrew	John	true	1
	1	A Street		London		4	Jones	Emily	Anne	false	1
	2	A Road	A Town	Oxon		2	Smithe	Fred	John	true	2
	3	A House	Village	Oxon	OX1 3ED	3	Wright	Anne		false	3
	3	A House	Village	Oxon	OX1 3ED	5	Wright	David	Peter	true	3

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';

Result:	AddressId	Line1	Line2	City	ZipCode	NameId	Surname	FirstName	MiddleName	Male	AddressId
	3	A House	Village	Oxon	OX1 3ED	3	Wright	Anne		false	3
	3	A House	Village	Oxon	OX1 3ED	5	Wright	David	Peter	true	3

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

SYNTAX: UPDATE {tablename} SET [{tablename}.]{fieldname}=newvalue WHERE {criteria}

So if we want to change the record.

SQL:

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

Before update:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	3	Wright	Anne	false	3
After update:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	3	Dickens	Anne	false	3

SQL:

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

NameID=3;

Before update:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	3	Dickens	Anne	false	3
After update:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	3	Wright	Ann</EM< td>	false	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)

Result:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	2	Smithe	Fred	John	true	2
	3	Wright	Anne		false	3
	4	Jones	Emily	Anne	false	1
	5	Wright	David	Peter	true	3
	6	Davis	Ivan		true

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

OtherNames

Surname	Name	Age
Green	Vicky	12
Black	Steve	32
Howells	Zara	25

SQL:

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

FROM OtherNames;

Result:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	2	Smithe	Fred	John	true	2
	3	Wright	Anne		false	3
	4	Jones	Emily	Anne	false	1
	5	Wright	David	Peter	true	3
		Green	Vicky
		Black	Steve
		Howells	Zara

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;

Result:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	2	Smithe	Fred	John	true	2
	3	Wright	Anne		false	3
	4	Jones	Emily	Anne	false	1
	5	Wright	David	Peter	true	3
		Green	Vicky
		Howells	Zara

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;

Before Delete:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	2	Smithe	Fred	John	true	2
	3	Wright	Anne		false	3
	4	Jones	Emily	Anne	false	1
	5	Wright	David	Peter	true	3
After Delete:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	3	Wright	Anne		false	3
	4	Jones	Emily	Anne	false	1

Or if we wanted to delete just one record

SQL:

DELETE FROM Names WHERE NameId=3

Before Delete:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	2	Smithe	Fred	John	true	2
	3	Wright	Anne		false	3
	4	Jones	Emily	Anne	false	1
	5	Wright	David	Peter	true	3
After Delete:	NameID	Surname	FirstName	MiddleName	Male	AddressId
	1	Smith	Andrew	John	true	1
	2	Smithe	Fred	John	true	2
	4	Jones	Emily	Anne	false	1
	5	Wright	David	Peter	true	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.