Sql For Beginners

SQL for Beginners By Anne-Marie Wright In this article I will be trying to show how sql statements work.

The database model -------Name Table------------NameId type Long Surname type String Firstname type String Middlename type string Male type boolean AddressId type Long

-------Address Table---------AddressId Type Long Line1 type string line2 type string City type string ZipCde type string

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 1 2 3 4 5

Surname Smith Smithe Wright Jones Wright

FirstName Andrew Fred Anne Emily David

MiddleName Male AddressId John true 1 John true 2 false 3 Anne false 1 Peter true 3

Address Table AddressId 1 2 3

Line1 Line2 A Street A Road A Town A House Village

City ZipCode London Oxon 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 1 Smith Andrew John 2 Smithe Fred John 3 Wright Anne 4 Jones Emily Anne 5 Wright David Peter Or we could only return certain fields

Male true true false false true

AddressId 1 2 3 1 3

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

SELECT NameId, Surname, FirstName FROM Name; Result: NameID 1 2 3 4 5

Surname Smith Smithe Wright Jones Wright

FirstName Andrew Fred Anne Emily 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 1 2 3 5

Surname Smith Smithe Wright Wright

FirstName Andrew Fred Anne David

MiddleName Male AddressId John true 1 John true 2 false 3 Peter true 3

SQL:

SELECT * FROM Name WHERE (Surname Like 'Smith%' AND MiddleName='John') Or Male=False; Result: NameID 1 2 3 4

Surname Smith Smithe Wright Jones

FirstName Andrew Fred Anne Emily

MiddleName Male AddressId John true 1 John true 2 false 3 Anne false 1

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

o o

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 Andrew Anne David Jones Fred

Surname Smith Wright Wright Emily 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 Fred Jones David Anne Andrew

Surname Smithe Emily Wright Wright 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 Smith Smithe Wright

FirstName Andrew Fred David

MiddleName John John 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 A 1 Street A 1 Street A 2 Road A 3 House A 3 House

Line2

A Town

City

ZipCode NameId Surname FirstName MiddleName Male AddressId

London

1

Smith

Andrew

John

true 1

London

4

Jones

Emily

Anne

false 1

Oxon

2

Smithe

Fred

John

true 2

3

Wright

Anne

5

Wright

David

Village Oxon Village Oxon

OX1 3ED OX1 3ED

false 3 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 A 3 House A 3 House

Line2

City

ZipCode NameId Surname FirstName MiddleName Male AddressId

Village Oxon OX1 3ED 3

Wright

Anne

Village Oxon OX1 3ED 5

Wright

David

false 3 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 After update:

Wright

Anne

false

3

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 3 After update:

Dickens

Anne

NameID Surname FirstName 3

Wright

MiddleName Male AddressId false

3

MiddleName Male AddressId

Ann 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 1 2 3 4 5 6

Surname Smith Smithe Wright Jones Wright Davis

FirstName Andrew Fred Anne Emily David Ivan

MiddleName Male AddressId John true 1 John true 2 false 3 Anne false 1 Peter true 3 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 Howells

Steve 32 Zara 25

SQL:

INSERT INTO Name(Surname, FirstName) FROM SELECT Surname, Name FROM OtherNames; Result: NameID 1 2 3 4 5

Surname Smith Smithe Wright Jones Wright Green Black Howells

FirstName Andrew Fred Anne Emily David Vicky Steve Zara

MiddleName Male AddressId John true 1 John true 2 false 3 Anne false 1 Peter true 3

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 1 2 3 4 5

Surname Smith Smithe Wright Jones Wright Green Howells

FirstName Andrew Fred Anne Emily David Vicky Zara

MiddleName Male AddressId John true 1 John true 2 false 3 Anne false 1 Peter true 3

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 1 2 3 4 5 After Delete: NameID 3 4

Surname Smith Smithe Wright Jones Wright Surname Wright Jones

FirstName Andrew Fred Anne Emily David FirstName Anne Emily

Or if we wanted to delete just one record

MiddleName Male AddressId John true 1 John true 2 false 3 Anne false 1 Peter true 3 MiddleName Male AddressId false 3 Anne false 1

SQL:

DELETE FROM Names WHERE NameId=3 Before Delete: NameID 1 2 3 4 5 After Delete: NameID 1 2 4 5

Surname Smith Smithe Wright Jones Wright Surname Smith Smithe Jones Wright

FirstName Andrew Fred Anne Emily David FirstName Andrew Fred Emily David

MiddleName Male AddressId John true 1 John true 2 false 3 Anne false 1 Peter true 3 MiddleName Male AddressId John true 1 John true 2 Anne false 1 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.