Lecturer : Damien Kettle Database Administration
Tutorial 2 – Inserting, Selecting, Updating and Deleting Data
Inserting
1 Now we have our table created we want to insert data.
We will do this using an INSERT Statement
Click on the New Query icon below.
Lecturer : Damien Kettle Database Administration
2 Type the following into the Query Window
Once you have done this click on Execute above with the exclamation mark. (highlighted in red box
above).
If it worked you should get below message (1 row affected) which means that one row has been
added to the members table:
Lecturer : Damien Kettle Database Administration
3 To insert more than one row we could write the following query:
Lecturer : Damien Kettle Database Administration
This would also give the same result:
Write the above SQL statement to insert the above records also into your Members table.
Lecturer : Damien Kettle Database Administration
Selecting Data
1 Return all records from the Members table using below SELECT Statement
SELECT * returns all columns.
Lecturer : Damien Kettle Database Administration
2 If we only wanted to return the FirstName, LastName and City we would write below query:
Lecturer : Damien Kettle Database Administration
3 WHERE CLAUSE: If we only want to return a subset of records based on some criteria we use a
WHERE CLAUSE. To retrieve all Customers who live in Galway write the following SQL and execute.
Lecturer : Damien Kettle Database Administration
4 ORDER BY CLAUSE: If we wanted to retrieve all customers in a certain order we would use an
ORDER BY CLAUSE.
If we wanted to retrieve all Customers ordered by age we would write the following SQL statement:
The above result set is returned in order of Age ascending starting with the smallest.
Ascending is the default order unless we explicitly specify the order.
Lecturer : Damien Kettle Database Administration
Write the below SQL and execute to return the records in order of Age Descending.
Lecturer : Damien Kettle Database Administration
Updating Data
1 We want to update the City where Carl Davis lives.
Write the following Update Statement and press Execute.
Lecturer : Damien Kettle Database Administration
You should get the message below in the Results pane.
Now let’s run a SELECT statement to check that our record has been updated:
Lecturer : Damien Kettle Database Administration
Deleting Data
1 Bill Gates (Member Id = 4) has left and we want to remove that member from the members table.
Write the following Delete statement and execute:
Lecturer : Damien Kettle Database Administration
2 Select all data from the table and you should no longer see a record for Bill Gates:
