SQL basics
<< | Firebird development using IBExpert | Creating your first database >>
tutorial available
SQL basics
If you are really new to SQL, first check the definitions for Structured Query Language, and DSQL, ESQL, isql and PSQL. You can find a reference of the most important commands in the SQL Language Reference, and the full range of Firebird 2.0 and 2.1 commands in the Firebird 2 Language Reference Update, the Firebird 2.1 Language Reference Update and the Firebird 2.5 Language Reference Update. However you will find that the following are the most commonly used commands, with which you will be able to do the majority of your work:
SELECT INSERT UPDATE DELETE | These commands are known collectively as DML (Data Manipulation Language) commands. They are a group of SQL commands, commonly known as SIUD, which can be used to manipulate a database's data. SIUD is the abbreviation for SELECT, INSERT, UPATE, DELETE. |
CREATE ALTER DROP EXECUTE SET | These commands belong to the Data Definition Language (DDL) set of commands, which define and manipulate the database and its structure (known as metadata). A full explanation of these commands can be found in the DDL - Data Definition Language chapter. |
Setting up a sample database
In order to follow the examples in this section and to offer the chance to play around with Firebird SQLs, we propose you install the demo database, db1.fdb supplied with IBExpert. Installation details can be found in the IBExpertDemoDB documentation.
Alternatively, Firebird also supplies a sample database, employee.fdb. However as this is the original sample database provided by InterBase® in the 1990's it's potential for testing is nowadays unfortunately somewhat limited.
Simple SELECT commands
The most basic SELECT command is:
select * from <table_name>
where * is a so-called wildcard. Let's take an example using our demo database, and enter the query in the IBExpert SQL Editor on the Edit page. If we want a list of all information in the product table:
select * from product
You will notice how IBExpert aids you when typing your database object name. When you enter PR the IBExpert Code Completion offers you a selection of all objects beginning with PR. When the key combination [Alt + Ctrl + T] is used, IBExpert offers a list of all tables beginning with PR.
If you've entered the object name correctly, for example the product table, IBExpert changes the text format (font color and underlined) if it recognizes the object, so you know immediately whether you have made a typing error (no change to text appearance) or not.
To run the query (EXECUTE) simply press the [F9] key or the green arrow icon:
The SQL Editor displays all resulting data sets found, which meet the conditions of the query (in this case all fields of all data sets in the product table):
Please note that in IBExpert you can define whether you wish the results to appear on the same page as your query (i.e. below the editing area) or on a separate page, and whether IBExpert should immediately display this Results page after the query has been executed. Please refer to Environment Options / Tools / SQL Editor for further information.
On the Messages page (to the left of the Results page) you can see a summary of how Firebird attained the information.
If you wish to make your query more selective, you can specify which specific information you wish to see, instead of all of it. For example, the DVD title and leading actor of all products:
select title, actor from product
When you're writing a select it can become very tiresome repeatedly writing out the full names of commonly used objects correctly. It's helpful to abbreviate such objects, also reducing the amount of frequent typing errors. This is possible by defining a so-called alias. For example, if you wish to define an alias for the product table, type select from product p. That way the server knows that whenever you type a p in this SQL, you are referring to the product table. IBExpert also recognizes the p as an alias and automatically offers me a list of all fields in the product table. By holding down the [Ctrl] key multiple fields can be selected, e.g. title and actor. By pressing the [Enter] key both fields are automatically inserted into the SQL with the alias prefix p.
Adding a WHERE clause
It is possible to set conditions on the information you want to see by adding a WHERE clause. For example:
select * from product p where p.category_id = 1
And if you only wish to see certain columns in the result sets:
select p.title, p.price, p.category from product p where p.category_id = 1
SELECTs can of course get a lot more complicated than this. It's important to try and keep it as simple as possible though. Because it's a mathematical notation, a complex SQL may look correct, but if you are not careful, you will get results that you did not really want. When you're working with many millions of data sets, you can't necessarily assess the values in the resulting statistical data, so it's vital you're sure there are no mistakes or logical errors in your query. Build your statements up gradually, checking each stage - this is easy in the IBExpert SQL Editor, as you can execute query parts by simply marking the segment you wish to test and executing. Only if no query areas are selected, does the SQL Editor execute the whole statement.
It is of course possible to specify more than one condition, e.g.:
select * from product where special=1 and category_id=2
CONTAINING
select * from product where title containing 'HALLOWEEN'
This will supply all films with the word HALLOWEEN somewhere in the title. CONTAINING is case-insensitive, and never uses an index, as it searches for a string contained somewhere in the field, not necessarily at the beginning.
ORDER BY
If you need your results in a certain format, you can specify that the results be ordered, alphabetically or numerically, by a certain field. For example, order by price in ascending order (lowest first, highest last):
select * from product order by price
The ascending order is the so-called default; that means it is not necessary to specify it specifically. However, if you wish to specify a descending order, this needs to be explicitly specified:
select * from product order by price desc
SELECT across multiple tables
To combine data across multiple tables you can JOIN the tables together, giving you results that contains information from both. For example, each film is categorized according to genre.
Now what we want to see is the category that these films are associated with:
select p.title, c.txt from product p join category c on c.id=p.category_im
JOIN is a flexible command. The above example is known as an INNER JOIN.
Theoretically there could be products that have not been categorized, or categories that have no products. If you want to include these products or these categories in your result list it is possible to define these using a so-called LEFT OUTER JOIN or a RIGHT OUTER JOIN.
The LEFT OUTER JOIN takes all information from the left-hand or first table (in our example product) and joins them to their categories. For example if you have a customer list with individual sales figures and you also want to see those customers without any sales.
The RIGHT OUTER JOIN fetches all products with a category and also all categories.
If you wish to combine two different sets of data together, even if they have nothing in common, you can use the CROSS JOIN, introduced in Firebird 2.0:
select p.title, c.txt from product p cross join category c
From these simple building blocks you can construct very complex structures with extremely complex results. If you are just beginning with SQL, we recommend the IBExpert Query Builder. This enables you to compile your SQL by simply dragging and dropping your objects and using point-and-click to specify which information you wish to see, set any conditions and sort the results.
Please refer to the IBExpert Tools menu item, Query Builder for further information.
Sub-SELECTs in fields and WHERE clauses
We can vary our query by replacing the second field by a sub-select:
select p.title,
(select c.txt from category c
where c.id=p.category_id)category_txt
from product
By replacing c.txt with where c.id=p.category_id) category_txt the JOIN is no longer necessary. This new second field is determined for each data set. As the sub-select is creating a new unnamed field, the field is given an alias, category_txt. You can name result columns as you like, particularly useful when columns with similar names from different tables are to be queried. For example, if you wish to see c.id and p.id in the same result set, you might want to rename c.id category_id and p.id product_id.
Physically this query is the same as the JOIN query, however this option offers more possibilities.
You can also insert a sub-select in a WHERE clause: select which fields you want from which tables and restrict it by adding a sub-select in the WHERE condition. For example, if you only want to see products from the first category:
select p.title, c.txt from product p join category c on c.id=p.category_id where c.id=(select first 1 id from category)
Be careful with this, as this is one of the areas of SQL where a lot of developers start to go wrong!
UNION SELECT
SELECTs enable you to retrieve almost any information you want with a single SELECT statement. A classic example of when you might need a UNION SELECT is with a database system that stores its current data in one table and archive data in another table, and a report is required which includes both sets of data being evaluated and presented as a single set of information.
The syntax is simple: two SELECT statements with a UNION in between to fuse them together:
Select
p.title,
cast('Children' as varchar(20))
from product p
join category c on c.id=p.category_id
where c.txt containing 'children'
union
Select
p.title,
cast('not for Children' as varchar(20))
from product p
join category c on c.id=p.category_id
where c.txt not containing 'children'
Here all titles are being selected that belong to the category children. These results are then going to be combined with another set where the category does not contain the text children, and all these results (i.e. every other category that isn't explicitly for children) will contain the category text not for Children, regardless of their genre. This artificial field supplies information that is not directly in the database in that form.
The rules regarding the joining together of two result sets is that you have to have columns with the same datatypes, i.e. you cannot mix INTEGERs and blobs in a single result column. You must have the same number of columns in the same layout, e.g. if you current orders table has 50 columns and the archive only 30 columns, you can only select common columns (which will be a maximum of 30) for the UNION SELECT.
IN operator
Select p.title,c.txt from product p join category c on c.id=p.category_id where c.id in (select first 5 id from category)
Here the value c.id is being limited to the first five, i.e. we only wish to see the first five resulting sets.
The IN operator is very powerful. Assume you wish to view film categories, Action, Animation and a couple of others and you had already retrieved the result that these categories were 1, 2, 5 and 7. Then you could query as follows:
Select p.title,c.txt from product p join category c on c.id=p.category_id where p.category_id in (1,2,5,7)
i.e. here it is asking for results where the category_id is in the specified set of values. The IN can be a set of values or a SELECT. You should be careful that there are not too many results, as this can considerably slow performance.
EXISTS operator
select c.* from customer c where not exists (select id from orders where orders.customer_id=c.id)
Here we are selecting the customers from the customer table where if one or more rows are returned then it will give you the value. If no values are returned then it omits it and does not show it. This means, these results will only return customers who have not placed any orders.
The EXISTS operator is almost always more helpful than the IN operator. The EXISTS operator searches if data sets meeting the conditions exist, and when it finds results sends them back. The IN operator would initially fetch all data sets, i.e. fetch all orders, and then narrow down the result sets according to the conditions.
If you have a choice between IN and EXISTS, always use EXISTS as it's quicker.
INSERT and UPDATE with values
insert into category values (20, 'Cartoons')
INSERT - As no columns have been named here the values 20 and Cartoons are inserted from left to right in the category table columns. If the column names are not specified, data has to be inserted into all columns (the category table only has two columns). For larger tables it is wise to be more specific and always name the columns you wish to insert data into, as you may not wish to insert into all columns.
insert into category (id,txt) values (21, 'More cartoons')
Always take into consideration that NOT NULL fields have to be filled.
UPDATE applies to the whole table. It is simply a list of z variables or fields and their new values, with a condition.
update product set title='FIREBIRD CONFERENCE DAY', Actor='FIREBIRD FOUNDATION' where id=1;
If you don't put a qualifying clause in there about what it's going to do, e.g. a WHERE clause, it will update everything! So always check thoroughly before committing!
Unlike SELECT, both these commands only interact with one table at a time.
You can also use INSERT INTO with SELECTed data:
insert into customer_without_orders select c.* from customer c where not exists (select id from orders where orders.customer_id=c.id)
This can be used to insert data into a table that's been supplied from another source (here the select from customer).
Whereas Firebird requires the table in which you want to insert data to already exist, the IBExpert SQL Editor however has a nice feature: it will create the table for you if it does not already exist! In the above example, if the customers_without_orders table does not already exist, IBExpert asks if it should create the table. If you agree, it creates a table according to the information supplied in the query and pushes the returns in to the new table customer_without_orders. This function is ideal if you wish to extract certain data for testing or for a temporary report.
DELETE
delete from orderlines where id<1000
This will delete all data sets with an id of less than 1000.
delete from orderlines where id between 1000 and 2000
This will delete all data sets with id between 1000 and 2000.
Be careful when defining your delete conditions. A mistake here and you will delete the wrong data sets or too many!
CREATE, ALTER and DROP
If you're just starting off, we would not recommend creating all database objects by writing SQL. Use IBExpert's DB Explorer to create and manipulate all your databases and database objects. Please refer to the IBExpert chapters: DB Explorer and Database Objects.
To understand how the database structure works, analyze the DDL code created by IBExpert as a result of your point and click actions. This can be found on the DDL page in all object editors.
Defining code templates in IBExpert
By now you should have had some practice at writing DDL and DML code. You will probably have already noticed that certain commands or series of commands occur repeatedly. To save time and the frustration of repeated typing errors, IBExpert offers two aids to speed up your day-to-day work.
In the IBExpert SQL Editor you can quickly find your most commonly used queries by clicking on the number buttons at the bottom of the Edit page. The History page offers you a summary of all saved SQLs for the current connected database.
Other pieces of code can be stored as templates. There is even the option to automatically insert the current date, time and author. Please take the time to refer to the IBExpert documentation chapter, Keyboard templates for details of this time-saving function.
See also:
SQL Editor
SELECT
SELECT - Firebird 2.1 Language Reference Update
SELECT - Firebird 2.0 Language Reference Update
DDL-Data Definition Language
DML - Data Manipulation Language
Database Objects
back to top of page
<< | Firebird development using IBExpert | Creating your first database >>
