An Introduction to SQL - Part Two

Inserting Data

Having now built the structure of the database it is time to populate the tables with some data. In the vast majority of desktop database applications data entry is performed via a user interface built around some kind of GUI form. The form gives a representation of the information required for the application, rather than providing a simple mapping onto the tables. So, in this sample application you would imagine a form with text boxes for the user details, drop-down lists to select from the PC table, drop-down selection of the software packages etc. In such a situation the database user is shielded both from the underlying structure of the database and from the SQL which may be used to enter data into it. However we are going to use the SQL directly to populate the tables so that we can move on to the next stage of learning SQL.

The command to add new records to a table (usually referred to as an append query), is:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...]);

So, to add a User record for user Jim Jones, we would issue the following INSERT query:

INSERT INTO User (FirstName, LastName, UserID, Dept, EmpNo, PCType)
VALUES ("Jim", "Jones", "Jjones","Finance", 9, "DellDimR450");

Obviously populating a database by issuing such a series of SQL commands is both tedious and prone to error, which is another reason why database applications have front-ends. Even without a specifically designed front-end, many database systems - including MS Access - allow data entry direct into tables via a spreadsheet-like interface.

The INSERT command can also be used to copy data from one table into another. For example, if an organisation initially employs people on a 3-month trial period before making an offer for a permanent position, then it might want to store their details in a separate table. Let's call this table NewUsers and give it the same structure as the User, but with the addition of a new column called StartDate. If we also assume that at the end of the three-month trial period those people not offered a permanent position are removed from the table, leaving only those users who will become permanent employees, then we need to transfer the latter from the NewUsers table to the User. One way would be to re-key all the details into the User table, but this can lead to keying errors (this is obviously a bit of a contrived example, but it illustrates the point). A more elegant solution is to use an INSERT query to automate the task for us.

The SQL query to perform this is:

INSERT INTO User ( FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet )
SELECT FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet
FROM NewUsers;

Updating Data

The INSERT command is used to add records to a table, but what if you need to make an amendment to a particular record? In this case the SQL command to perform updates is the UPDATE command, with syntax:

UPDATE table
SET newvalue
WHERE criteria;

For example, let's assume that we want to move user Jim Jones from the Finance department to Marketing. Our SQL statement would then be:

UPDATE User
SET Dept="Marketing"
WHERE EmpNo=9;

Notice that we used the EmpNo field to set the criteria because we know it is unique. If we'd used another field, for example LastName, we might have accidentally updated the records for any other user with the same surname.

The UPDATE command can be used for more than just changing a single field or record at a time. The SET keyword can be used to set new values for a number of different fields, so we could have moved Jim Jones from Finance to marketing and changed the PCType as well in the same statement (SET Dept="Marketing", PCType="PrettyPC"). Or if all of the Finance department were suddenly granted Internet access then we could have issued the following SQL query:

UPDATE User
SET Internet=TRUE
WHERE Dept="Finance";

You can also use the SET keyword to perform arithmetical or logical operations on the values. For example if you have a table of salaries and you want to give everybody a 10% increase you can issue the following command:

UPDATE PayRoll
SET Salary=Salary * 1.1;

Deleting Data

Now that we know how to add new records and to update existing records it only remains to learn how to delete records before we move on to look at how we search through and collate data. As you would expect SQL provides a simple command to delete complete records. The syntax of the command is:

DELETE [table.*]
FROM table
WHERE criteria;

Let's assume we have a user record for John Doe, (with an employee number of 99), which we want to remove from our User we could issue the following query:

DELETE *
FROM User
WHERE EmpNo=99;

In practice delete operations are not handled by manually keying in SQL queries, but are likely to be generated from a front end system which will handle warnings and add safe-guards against accidental deletion of records. If you bear in mind that a single delete query can delete thousands of records in a single statement then you'll appreciate both the power and simplicity of SQL but also the dangers inherent in allowing users raw access to it.

Very often you would not want to issue DELETE commands on a single table in isolation. For example, if we delete one of the software packages in the Software table we may be left with a record in the UserSoftware table which refers to it (this record would be termed an orphan record). Where such relationships between tables occur then the delete operation needs to delete records in both tables at once. Some database systems, including MS Access, allow relationships between tables to be established including setting a cascading delete option, which means that deleting a record in one table will cause the delete of matching records in the linked tables. In such a case you need only issue the DELETE (or UPDATE) command to the primary table (in our case Software), and the deletion or change will ripple through to the linked tables (the UserSoftware table in this example).

Note that the DELETE query will delete an entire record or group of records. If you want to delete a single field or group of fields without destroying that record then use an UPDATE query and set the fields to Null to over-write the data that needs deleting. It is also worth noting that the DELETE query does not do anything to the structure of the table itself, it deletes data only. To delete a table, or part of a table, then you have to use the DROP clause of an ALTER TABLE query.

Populating The Sample Database

Rather than list a long sequence of SQL commands to populate the tables used for this article, the data is listed below and it will be referred to later on in this article. Please note that only those fields which will be of interest have been listed, for example the ScreenSize and DateOfPurchase fields in the PC table have not been included. Oh, and note the ancient technology this company is using, if you work for this type of organisation no wonder you're trawling the web looking for a free tutorial…

PC table

PCTypeName	Processor	OpSys	RAM	Disk
ASTAscP	Pentium 2	Win95	32	4.3
ASTAscC	Pentium 2	Win95	64	1.3
PilotDeskTop	Pentium Pro	Win98	64	4.3
DellDimR450	Pentium 2	Win98	128	12.9

User

FirstName	LastName	UserID	Dept	EmpNo	PCType
John	Smith	Jsmith	Marketing	23	ASTAscP
Mary	Jones	Mjones	Marketing	69	ASTAscP
Chloe	Feltham	Cfeltham	Finance	45	ASTAscC
Bharat	Patel	Bpatel	Development	12	PilotDeskTop
Terry	Jones	Tjones	Development	15	ASTAscC
Jim	Jones	Jjones	Development	9	DellDimR450
John	Smith	Jsmith2	Finance	78	DellDimR450

Software table

ProductName	Vers	ProductID	Licenses
MySQL	6	MySQL6	25
MySQL	5	MySQL5	25
OpenOffice	1	OpenOffice	10
J2SE1.3	5	J2SE1.3	10
VB6	6	VB6	10
PaintShop Pro	3	PSP3	1
MS Word	7	MSWord7	25

UserSoftware table

EmpNo	ProductID
69	MSWord7
69	OpenOffice
69	MySQL5
12	VB6
15	VB6
15	PSP3
9	VB6
9	MySQL6

Querying The Database

The Basic SELECT command

Having designed, built and populated our database with some sample data, we can now move on to what is considered the heart of SQL - the SELECT statement. This is the command that queries the database and which provides real value to any database.

Although the SQL SELECT statement can become extremely complex, the basic syntax is relatively straightforward:


SELECT columns
FROM table(s)
WHERE criteria;

So, to query the User to produce a list of surnames and employee numbers we could issue the command:

SELECT LastName, EmpNo
FROM User;

If we wanted to look at the entire table we could have used an asterisk as a wildcard for the entire table:

SELECT *
FROM User;

Or else you might want to catenate the LastName and FirstName fields to produce a single column with entries such as 'Jones, Terry', 'Patel, Bharat' and so on. This too is possible using the simplest of SELECT commands, though we have to create an alias column to contain the result - which in this instance we'll call FullName. SQL can then use the AS keyword to assign the result of an expression to the alias. In MSAccess the ampersand character is used to catenate the strings:

SELECT LastName & "," & FirstName AS FullName 
FROM User;

In contrast MySQL uses the CONCAT keyword to piece the strings together:

SELECT CONCAT(FirstName,",",LastName) AS FullName 
FROM User;

You can even add fixed text or expressions to SELECT queries to give results which are immediately readable, as shown in the query and results below:

SELECT FirstName & " " & Lastname & " works in the " & Dept & " department" AS Job
FROM User;

Results of query above

John Smith works in the Marketing department

Mary Jones works in the Marketing department

Chloe Feltham works in the Finance department

Bharat Patel works in the Development department

Terry Jones works in the Development department

Jim Jones works in the Marketing department

John Smith works in the Finance department

Now, what happens if we issue a simple query such as the following?

SELECT LastName
FROM User;

SQL will return a simple listing of the last names in the User, and, as you can see by looking at the data in our table, it will contain duplicates as there are three lots of Jones's listed in our database. If we want a listing of unique names then how can we generate it? SQL includes a number of 'predicates', which specifies which records the query is to return. In all of our examples so far we have assumed that we want all of the records to be returned, which is equivalent to including the ALL predicate with our statements.

If we want a listing of unique names then we can use the DISTINCT predicate to exclude those records which are not unique based on the SELECTed fields in the query. In other words the query becomes:

SELECT DISTINCT LastName
FROM User;

In other cases we may only want to exclude records where the entire record is duplicated, not just a given field or set of fields. To do this we use the DISTINCTROW predicate. In our database there are no records in the User which are duplicates, so using DISTINCTROW will return all the records in the table.

Where we are dealing with numeric data we may only be interested in a subset based on the position of the results, for example the top 10 records, or the bottom 20% and so on. The predicate for this is TOP, and differs from the previous ones in that it depends on your query to order the results so that it can pick out the relevant records.

The WHERE Clause

So far all the queries we have looked at operate on the complete contents of a table. What if you are only interested in a particular sub-set of a table? The WHERE keyword is used to provide a search mechanism which only produces records which meet a given search criterion. For example if we are only interested in our users in the Development department, we would modify one of our earlier commands as follows:

SELECT *
FROM User
WHERE Dept="Development";

We might equally have wanted to see a list of employees who do not work in development. In this case our WHERE statement can be amended to:

WHERE Dept<>"Development";

Multiple WHERE statements can be created to fine tune our queries and select very precise sub-sets of our data. For example, if we wanted to look at those employees not in development but who have Internet access, we can code the following query:

SELECT *
FROM User
WHERE Dept<>"Development" And PCType="DellDimR450";

The WHERE command can also be used with arithmetic and logical operators. For example in those systems that support a LIKE keyword, if we wanted all those employees whose surname begins with the letter J (which you might want when producing a company contact list):

SELECT *
FROM User
WHERE LastName Like "J*";

Or if your system includes a BETWEEN keyword you can select names in the group A-M:

WHERE LastName Between "A*" And "M*";

For a full list of legal WHERE criteria and keywords refer to the documentation for your particular implementation of SQL.

Sorting Query Results

Where a query might produce many records it is often useful to sort the result by a given field. SQL uses the ORDER BY keyword to do this. If we wanted to order our User by LastName, we would create the following query:

SELECT *
FROM User
ORDER BY LastName;

Or, if we wanted the table sorted by LastName within Department, the ORDER BY clause would become: ORDER BY Dept, LastName

You can also chose to have items sorted in ascending or descending order, as shown by our last ORDER BY example:

SELECT *
FROM User
Order By Dept DESC, LastName ASC;

<<Back:Introduction >>Next:Joining data

Return to home page