SQL : A Practical Introduction.

Akeel I Din

ISBN 1-85554-357-5

Order a printed copy of this book from Amazon.


Cover Design by -Michael Finn & Associates.
 

For your free copy of this book in PDF format, please let me know your name and what part of the world you are from. 

I won't sell, rent your details to any third party. Your privacy is guaranteed.

Full Name

E-mail

City

Country

I'm currently working on the second edition of this book. Leave this box checked if you want to receive this for free. I edit a 'Database News and Views' newsletter which I'll send you also free of charge. 

 

Sample Chapter From SQL : A Practical Introduction

Chapter 1 INTRODUCTION.

The Structured Query Language, SQL is a query language which is used with relational databases. This chapter starts by describing some of the terms used in data processing and how they relate to SQL. The later part of this chapter describes relational databases and how SQL is used to query them.

 

1.1 "A Collection of Related Data": Databases and Database Management Systems.

Let's start from basics. What is a database? In very general terms, a database is a collection of related data. Notice the word related, this implies that the collection of letters on this page do not by themselves constitute a database. But if we think of them as a collection of letters arranged to form words, then they can be conceptualised as data in a database. Using similar reasoning, we can also say that a tome such as a telephone directory is also a database. It is a database first, because it is a collection of letters that form words and second, because it is an alphabetical listing of people's names, their addresses and their telephone numbers. How we think of a database depends on what use we want to make of the information that it contains.

So far, we have talked about a database in it's broadest sense. This very general definition is not what most people mean when they talk about a database. In this electronic age, the word database has become synonymous with the term "computerised database". Collins English Dictionary describes a database as "A store of a large amount of information, esp. in a form that can be handled by a computer." In this book, we will be dealing only with computerised databases. In keeping with popular trend though, we will be using the word database to refer to a computerised database.

A database (computerised database remember) by itself, is not much use. The data is stored electronically on the computer's disk in a format which we humans cannot read or understand directly. What we need is some way of accessing this data and converting it into a form which we do understand. This is the job of the database management system or DBMS for short. A DBMS is essentially a suite of programs that act as the interface between the human operator and the data held in the database. Using the DBMS, it is possible to retrieve useful information, update or delete obsolete information and add new information to the database. As well as data entry and retrieval, the DBMS plays an important role in maintaining the overall integrity of the data in the database. The simplest example of is ensuring that the values entered into the database conform to the data types that are specified. For example, in the telephone book database, the DBMS might have to ensure that each phone number entered conforms to a set format of XXX-XXXXXXX where X represents an integer.

 

1.2 "The Database as a Collection of Tables": Relational databases and SQL.

In the early days of computerised databases, all large database systems conformed to either the network data model or the hierarchical data model. We will not be discussing the technical details of these models except to say that they are quite complex and not very flexible. One of the main drawbacks of these databases was that in order to retrieve information, the user had to have an idea of where in the database the data was stored. This meant that data processing and information retrieval was a technical job which was beyond the ability of the average office manager. In those days life was simple. data processing staff were expected to prepared the annual or monthly or weekly reports and managers were expected to formulate and implement day to day business strategy according to the information contained in the reports. Computer literate executives were rare and DP staff with business sense were even more rare. This was the state of affairs before the advent of relational databases.

The relational data model was introduced in 1970, E. F. Codd, a research fellow working for IBM, in his article `A Relational Model of Data for Large Shared Databanks'. The relational database model represented the database as a collection of tables which related to one another.

Unlike network and hierarchical databases, the relational database is quite intuitive to use, with data organised into tables, columns and rows. An example of a relational database table is shown in Figure 1.1. We can see just by looking at Figure 1.1 what the table is. The table is a list of people's names and telephone numbers. It is similar to how we might go about the task of jotting down the phone numbers of some of our friends, in the back of our diary for example.

The relational data model consists of a number of intuitive concepts for storing any type of data in a database, along with a number of functions to manipulate the information.

NUM SURNAME FIRSTNAME PHONE_NUMBER
1

Jones

Frank 9635

2

Bates Norman 8313
3 Clark Brian 2917

4

Stonehouse Mark 3692

5

Warwick Rita 3487

Figure 1.1

The relational data model as proposed by Codd provided the basic concepts for a new database management system, the relational database management system (RDBMS). Soon after the relational model was defined, a number of relational database languages were developed and used for instructing the RDBMS. Structured Query Language being one of them.

The SQL language is so inextricably tied to relational database theory that it is impossible to discuss it without also discussing the relational data model. The next two sections briefly describe some of the concepts of this model.

 

1.2.1 Tables, columns and rows.

We have already seen that a relational database stores data in tables. Each column of the table represent an attribute, SURNAME, FIRSTNAME, PHONE_NUMBER for example. Each row in the table is a record. In the table in Figure 1.1, each row is a record of one person. A single table with a column and row structure, does not represent a relational database. Technically, this is known as a flat file or card index type database. Relational databases have several tables with interrelating data. Suppose that the information in the table of Figure 1.1 is actually the list of people working in the company with their telephone extensions. Now we get an idea that this simple table is actually a small part of the overall database, the personnel database. Another table, such as the one in Figure 1.2. could contain additional details on the persons listed in the first table.

NUM

D_O_B

DEPT

GRADE

2

12/10/63

ENG

4

5

07/05/50

DESIGN

7

3

03/11/45

SALES

9

1

09/03/73

ENG

2

Figure 1.2

1.2.2 The Primary key and the foreign Key.

The two tables described in the previous section and shown in Figures 1.1 and 1.2, now constitute a relational database. Of course, in a real personnel database, you would need to store a great deal more information and would thus need a lot more related tables.

Notice that the first column in each table is the NUM column. The information stored in NUM does not really have anything to do with the person's record. Why is it there? The reason is that NUM is used to uniquely identify each person's record. We could have used the person's name, but chances are that in a large company, there would be more than one person with the same name. NUM is known as the primary key for the table of Figure 1.1. For the table of Figure 1.2, where a primary key of another table is used to relate data, NUM is a called a foreign key.

The primary keys and foreign keys are a very important part of relational databases. They are the fields that relate tables to each other. In the table of Figure 1.2 for example, we know that the first record is for Norman Bates because the value for NUM is 2 and we can see from the table of Figure 1.1 that this is Norman Bates' record.

 

1.3 "Communicating to the DBMS what you want it to do": Introduction to the SQL language.

The Structured Query Language is a relational database language. By itself, SQL does not make a DBMS. It is just a medium which is used to as a means of communicating to the DBMS what you want it to do. SQL commands consist of english like statements which are used to query, insert, update and delete data. What we mean by `english like', is that SQL commands resemble english language sentences in their construction and use. This does not mean that you can type in something like "Pull up the figures for last quarter's sales" and expect SQL to understand your request. What it does mean is that SQL is a lot easier to learn and understand than most of the other computer languages.

SQL is sometimes referred to as a non-procedural database language. What this means is that when you issue an SQL command to retrieve data from a database, you do not have to explicitly tell SQL where to look for the data. It is enough just to tell SQL what data you want to be retrieved. The DBMS will take care of locating the information in the database. This is very useful because it means that users do not need to have any knowledge of where the data is and how to get at it. Procedural languages such as COBOL or Pascal and even older databases based on the network and hierarchical data models require that users specify what data to retrieve and also how to get at it. Most large corporate databases are held on several different computers in different parts of the building or even at different geographic locations. In such situations, the non-procedural nature of SQL makes flexible, ad hoc querying and data retrieval possible. Users can construct and execute an SQL query, look at the data retrieved, and change the query if needed all in a spontaneous manner. To perform similar queries using a procedural language such as COBOL would mean that you would have to create, compile and run one computer programs for each query.

Commercial database management systems allow SQL to be used in two distinct ways. First, SQL commands can be typed at the command line directly. The DBMS interprets and processes the SQL commands immediately, and any result rows that are retrieved are displayed. This method of SQL processing is called interactive SQL. The second method is called programmatic SQL. Here, SQL statements are embedded in a host language such as COBOL or C. SQL needs a host language because SQL is not really a complete computer programming language as such. It has no statements or constructs that allow a program to branch or loop. The host language provides the necessary looping and branching structures and the interface with the user, while SQL provides the statements to communicate with the DBMS.

 

1.4 "A Research Project Conducted by IBM": The history of SQL.

The origins of the SQL language date back to a research project conducted by IBM at their research laboratories in San Jose,

California in the early 1970s. The aim of the project was to develop an experimental RDBMS which would eventually lead to a marketable product. At that time, there was a lot of interest in the relational model for databases at the academic level, in conferences and seminars. IBM, which already had a large share of the commercial database market with hierarchical and network model DBMSs, realised quite quickly that the relational model would figure prominently in future database products.

The project at IBM's San Jose labs was started in 1974 and was named System R. A language called Sequel (for Structured English QUEry Language) was chosen as the relational database language for System R. In the project, Sequel was abbreviated to SQL. This is the reason why SQL is still generally pronounced as see-quel.

In the first phase of the System R project, researchers concentrated on developing a basic version of the RDBMS. The main aim at this stage was to verify that the theories of the relational model could be translated into a working, commercially viable product. This first phase was successfully completed by the end of 1975, and resulted in a rudimentary, single-user DBMS based on the relational model.

The subsequent phases of System R concentrated on further developing the DBMS from the first phase. Additional features were added, multi-user capability was implemented, and by 1978, a completed RDBMS was ready for user evaluation. The System R project was finally completed in 1979. During this time, the SQL language was modified and added to as the needs of the System R DBMS dictated.

The theoretical work of the System R project resulted in the development and release in 1981 of IBM's first commercial relational database management system. The product was called SQL/DS and ran under the DOS/VSE operating system environment. Two years later, IBM announced a version of SQL/DS for the VM/CMS operating system. In 1983, IBM released a second SQL based RDBMS called DB2, which ran under the MVS operating system. DB2 quickly gained widespread popularity and even today, versions of DB2 form the basis of many database systems found in large corporate data-centres.

During the development of System R and SQL/DS, other companies were also at work creating their own relational database management systems. Some of them, Oracle being a prime example, even implemented SQL as the relational database language for

their DBMSs concurrently with IBM.

Today, the SQL language has gained ANSI (American National Standards Institute) and ISO (International Standards Organization) certification. A version of SQL is available for almost any hardware platform from CRAY supercomputers to IBM PC microcomputers. In recent years, there has been a marked trend for software manufacturers to move away from proprietary database languages and settle on the SQL standard. The microcomputer platform especially has seen a proliferation of previously proprietary packages that have implemented SQL functionality. Even spreadsheet and word processing packages have added options which allow data to be sent to and retrieved from SQL based databases via a Local Area or a Wide Area network connection.

 

1.5 "SQL Commands Build Upon Themselves": Organization of this book.

After this introduction, this book first presents the SQL language in a nutshell. Subsequent chapters then focus on explaining each of the SQL command groups (the SELECT, the UPDATE, the CREATE etc) more fully. The reason for this method of presentation is that a lot of the SQL commands build upon themselves. For example, you cannot discuss the INSERT INTO with SELECT command without having knowledge of and understanding the SELECT statement itself. So where do you put the chapter on INSERT INTO with SELECT? You can't put it before the chapter on SELECT because as we've said, it requires the reader to have knowledge of the SELECT statement. You can't put it after the chapter on SELECT because the SELECT statement requires data to be input into the tables by using the INSERT statement. We have gone for the second option because it is a lot easier to take a leap of faith and believe that somehow the tables are already populated with data and use SELECT to query them rather than trying to understand the INSERT INTO with SELECT without any knowledge of how SELECT works.

To save having to put phrases such as "see the later chapter on SELECT" or "see the earlier chapter on INSERT" throughout the book, we have started off by describing the SQL language globally, and then detailing each command group separately. It's a bit like a course for auto mechanics, say, you start off by first describing the layout of the car and all it's major parts such as the engine, the gearbox etc., before going on to discuss topics like the detailed construction of the engine.

Primarily, this book is designed to teach you how to use SQL to create, modify, maintain and use databases in practical situations. It is not intended to be an academic treatise on the subject, and so does not go into the mathematical basis of the topics considered. What it does contain is lots of examples and discussions on how they work. You should work your way through this book by reading through a section, and actually trying out each SQL query presented for yourself. If you do not have access to an SQL based database, then you can order a fully functional ANSI/ISO SQL database at an affordable price, by sending off the order form at the back of this book. The quickest and easiest method of learning SQL (or indeed any computer language) is to use it in real life, practical situations. The chapters of this book are laid out so that each section builds upon the information and examples presented in the previous chapters. By following the SQL query examples, you will create a database, populate it and then use it to retrieve information.

Remember that the SQL queries in this book are only given as examples. They represent one possible method of retrieving the results that you want. As you gain confidence in SQL, you may be able to construct a more elegant query to solve a problem than the one that we have used. This just goes to show the power and flexibility of SQL.

The structure of this book is such that as you progress through it, you will be exposed to more and more complex aspects of SQL. If you follow through the book, you will find that you are not suddenly presented with anything particularly difficult. Rather, you will be gradually lead through and actively encouraged to try out SQL queries and variations of queries until you have thoroughly understood the underlying ideas.

The chapters will not all take the same amount of time to read and understand. You will benefit most if you sit down, start at a new section, and work your way through until it is completed. Although we understand that you may find some of the longer sections difficult to finish in one session. You should nonetheless endeavour to complete each section in as few sittings as possible. Taking short breaks to think over concepts learned as you progress through the section is also a good idea as it reinforces the learning process. You should try to understand the underlying concepts of what you are learning rather than coasting through the book.

 

1.5.1 Notational conventions.

The following notational conventions are used throughout this book:

BOLD TYPE These are keywords and data in a statement. They are to appear exactly as they are shown in bold.

{ } Curly braces group together logically distinct sections of a command. If the braces are followed by an asterix (*), then the section inside them can occur zero or more times in a statement. If followed by a plus (+), then the section inside must appear at least once in the statement.

[ ] Square brackets are used to signify sections of a statement that are optional.

( ) Parentheses in bold are part of the SQL command, and must appear as shown. Parentheses which are not in bold are to indicate the logical order of evaluation.

... The ellipses show that the section immediately proceeding them may be repeated any number of times.

| The vertical bar means "or".

Throughout this book, SQL command structure will be explained by using examples of actual statements.

 

 

This Page Viewed: