Database Design and SQL

SQL tutorial


Previous page Previous     TOC Contents     Next pageNext


Lesson 3 - Introduction to the SQL language


The SQL language

SQL = Structured Query Language
Usually pronounced 'Sequel' or, sometimes, 'ess-queue-el'







Relational database manipulation language developed in the 1970's by Dr. E.F.Codd and IBM.

Popularized by ORACLE

Advantage is that it allows databases that are not programmed the same to talk to each other - it is the basis of Client/Server architecture.

A Client application written in Visual Basic under Windows can communicate with a Server running Oracle - the Client sends the Server a SQL command which is interpreted and the result sent back to the Client.

Also, all DBMS's use SQL in their internal operations. Database Administrators (the people who build and maintain the database structure) need in-depth knowledge of the language.

To build the database and test our SQL commands we'll be using MS-Access and the Project Management database that we designed in previous lessons. If you haven't used Access before, take a look at our MS-Access tutorial to get the basics of the tool.




To create a SQL query with Access you simply go through the normal query procedure and select SQL View instead of the Query wizard; then specify that it is a new query (you don't have to identify the tables used):



Fig. 3-1


and then write the code and run it:



Fig. 3-2




SQL syntax in Access


SQL syntax is not very strict. A statement can be be written over several lines but, most implementations of SQL will insist on the semicolon (;) at the end.

Upper and lower cases don't matter but again, in most installations you will see common practices such as writing all command verbs and clauses in uppercase and table names, column names, etc. in lowercase.

Syntax errors will be flagged as soon as you try to execute the statement. Experience will show what the most common errors tend to be. Since SQL syntax is not very complicated to begin with, errors are usually easy to detect and to fix.

There is not a whole lot of punctuation involved. The ; at the end of the statement is important and, of course, parentheses have to be in the right places, like any other language. As for data types, string values are inclosed in single quotes, dates in pound signs and numeric in nothing.

For example,
e_salary = 55000
e_fname = 'Mike'
e_hiredate = #1995-10-10#


Top






SQL INSTRUCTIONS


The SQL instruction set consists of only about 30 instructions. Although there are SQL instructions to create and manipulate tables and the data they contain, it is quite possible that all the maintenance functions will be done using the DBMS (Access in this case). If that is the way your system is set up your applications will end up using the SELECT instruction 95% of the time.

In case you missed it previously, a query is a question, an interrogation, a lookup. That is what SQL is built for - it exists to get information from databases.

In this tutorial, we will assume that the database itself is already created and named. Some of the tables may have been created in Access but we will use SQL statements to create the others, just to make sure that we know how to do it.

Table manipulation statements


There are SQL statements to create tables, modify them or remove them.

To create a new table in the current active database:

CREATE TABLE table_name (column1 datatype not null, column2 datatype, ...);

Example:
CREATE TABLE employee (e_id string(3) not null, e_fname string(20),
e_salary single, e_hiredate date);

The usual datatypes are:
INTEGER Integer values between -32K and +32K
SINGLE Single-precision floating point
DOUBLE Double-precision floating point
DATE Date/time
STRING(n) Fixed-length string; n = number of characters
BOOLEAN True/False


We add the not null clause to the statement to indicate that null is not allowed in the column, if it is to be a primary key, for example.



Speaking of NULL

Everyone knows by now that when we speak of characters, we mean the letters of the alphabet and the numbers and punctuation signs and so on. The space ( ) is a character and so is the zero (0).

In SQL we will often have to refer to the NULL value. NULL is not a character; it is the absence of a character. In books they say that NULL means that the value is undetermined. In fact, it means that there is no value assigned to the field, it is completely empty. NULL is not numeric, nor string, nor date. Any type field can be NULL.

When the quantity-on-hand of an item in stock becomes 0, it is not null; it contains the numeric character 0. When I assign a 0 grade to an assignment (which happens all too frequently), that grade is included in the class average. If there is no grade assigned because the student was ill, that field is null and therefore it is not computed as part of the class average.

SQL commands will not consider nulls when they count or compute data. In some cases it is necessary to test if a field contains a value or not by using the clauses: IS NULL or IS NOT NULL in a statement.

In the example above, when entering data in the Employee table, you could theoretically have one employee with spaces as an Id but, you are not allowed to have one with an empty Id.



To change the structure of a table:
ALTER TABLE table_name ADD (column datatype);

Example:
ALTER TABLE Employee ADD (e_Address string(30));

ALTER TABLE Project ADD (p_Country string (20));

Note that there is no statement to change or remove a column.



To delete a table from the database:
DROP TABLE table_name

Example:
DROP TABLE Employee;




Top





Data manipulation statements


Data manipulation statements are used to work on the data contained in the tables.

To create a new record, a new row, in a table:

INSERT INTO table_name VALUES (value1, value2, ...);

Assuming that we have executed the CREATE TABLE and the ALTER TABLE statements from above (and not the DROP statement), the Employee table now contains 5 columns: Id, First name, Salary, Hire date and Address.

The INSERT statement will create a new employee record; it will add a row to the table. The number of data items must correspond to the number of columns and the type of data must correspond to the datatype of each column.

Example:




To change data in an existing record:
UPDATE table_name SET column1 = value1, column2=value2, ...
WHERE condition;

Example:
UPDATE Employee SET e_salary = 30000
      WHERE e_Id = '222';

UPDATE Employee SET e_Salary = e_Salary * 1.1
      WHERE e_Departement = '101';

The WHERE clause is SQL's IF statement. The update is done only if the condition in the WHERE clause is true.

In the first example, the update is performed only for the employee whose Id is '222'. His salary is set at 30000.

In the second example, the update is performed for every employee whose department is '101'.

In example 2, the command from the boss to launch the SQL statement would have been: "Give everybody in department 101 a 10% raise".

UPDATE Employee SET e_Salary = 100000;

If there is no WHERE clause in a statement, the update is performed on all the records in the table.


To remove records or rows from a table:
DELETE FROM table_name WHERE condition;

Example:
DELETE FROM Employee
      WHERE e_Id = '222';

DELETE FROM Employee
      WHERE e_Salary > 100000;

In the first case, delete employee '222'. In the second case, delete everybody earning more than 100K. Hey! What you gonna do? Times are tough all over!

DELETE FROM Employee;

If there is no WHERE clause, every record in the table is deleted! And it won't even ask "Are you sure?".









If you haven't found the database resource you're looking for,
use our Google Search box for more information.

Google








Top



Home | Tutorials | Contact | Sitemap | Add URL | Privacy policy