SQL samples

File | SQL | Description
Simple_Select.sql | select * from employee | This sample illustrates use of simple data retrieval from single table.
Distinct.sql | select distinct FirstName from employee | This sample illustrates use of DISTINCT keyword. The query returns all names from the field "FirstName" excluding duplicates.
Select_Top.sql | select Top 5,4 * from venues | This sample illustrates use of TOP option. The query retrieves first five entries beginning with 4th.
Select_Order_By.sql | select Event_Name, Venue, Ticket_Price from Events e, Venues v where e.VenueNo = v.VenueNo order by Ticket_Price asc, Venue desc | This example illustrates use of ORDER BY clause. The query lists all events and venues where they take place. Result dataset is sorted by ticket price (ascending) and venue name (descending order).
Select_Union.sql | select Event_Name from events where VenueNo<3 union select Event_Name from events where VenueNo>8 | This sample illustrates use of UNION keyword. In this query results of two "select" statements are combined in single dataset.
Select_Intersect.sql | select VenueNo from Venues intersect corresponding by (VenueNo) select VenueNo fromEvents | This sample illustrates use of INTERSECT keyword. The query shows only those VenueNo that are present in both tables.
Select_Having.sql | select VenueNo, Avg(Ticket_Price) from events group by VenueNo Having Event_Date<now | This sample illustrates use of HAVING clause. The query retrieves average ticket price for every venue using events that happened before.
Select_Group_By.sql | select FirstName, count(FirstName) cnt from Employee group by FirstName order by cnt desc | This example illustrates use of GROUP BY keywords. After performing this query the resulting dataset will contain names of employees and a number - how many times this name is met in the table.
Except_Corresponding.sql | select * from Venues except corresponding by (VenueNo) select * from Events | This query illustrates use of EXCEPT keyword. In the example below dataset returned will contain all entries from field "VenueNo" that exist in table "Venues" but do NOT exist in table "Events".
Expr_Arithmetic.sql | select (ticket_price+10)*2 as price from events | This sample illustrates use of arithmetic functions in queries. In this query for every entry value of field "ticket_price" is added to 10 and then multiplied by two. The result column is given name "price".
Select_Different_DB.sql | select Event_Name, Venue, Ticket_Price from "C:\Program Files\AbsoluteDatabase\Demos\Data\Demos.abs".Events e, "C:\Program Files\AbsoluteDatabase\Demos\Data\Demos.abs".Venues v where e.VenueNo = v.VenueNo | This example illustrates get data from TWO DATABASES IN SAME QUERY. The query lists all events, event prices and venues where they take place. Note that you can specify two different databases.
Select_From_2_Tables.sql | select Event_Name, Venue from Events e, Venues v where e.VenueNo = v.VenueNo order by Event_Name, Venue | This example illustrates how to get data from two tables using EQUI-JOIN. The query lists all events and venues where they take place. Notice that only those entries will be shown that have corresponding values in both tables. If event name has empty field "VenueNo" or its value is never met in same field of table "Venues", then this event will not be included in the resulting dataset.
Inner_Join.sql | select Event_Name, Venue from Events e inner join Venues v ON (e.VenueNo=v.VenueNo) order by Event_Name | This sample illustrates use of INNER JOIN statment. The query shows events and venues where it will occur. Notice that if VenueNo corresponding to event is not found in "venues" table, it will not be shown.
Left_Join.sql | select Event_Name, Venue from Events e left join Venues v on e.VenueNo = v.VenueNo order by Event_Name, Venue | This example illustrates use of LEFT JOIN action. This query retrieves all event names and places where they happen. Venue is attached to event name if value of field "VenueNo" is equal in both tables. If there is no corresponding venue for event, then NULL is placed beside it.
Full_Join.sql | select Event_Name, Venue from Events e full join Venues v on e.VenueNo = v.VenueNo order by Event_Name, Venue | This example illustrates use of FULL JOIN action. This query retrieves all event names and places where they happen. Venue is attached to event name if value of field "VenueNo" is equal in both tables. If there is no corresponding venue for event, then NULL is placed beside it. Likewise, if a corresponding event for a venue couldn't be found, NULL is placed to the left of it.
Natural_Full_Join.sql | select Event_Name, Venue from Events natural full join Venues order by Event_Name, Venue | This example illustrates use of FULL JOIN action with NATURAL option. This query retrieves all event names and places where they happen. Venue is attached to event name if value of same named field is equal in both tables. If there is no corresponding venue for event, then NULL is placed beside it. Likewise, if a corresponding event for a venue couldn't be found, NULL is placed to the left of it.
Function_Avg.sql | select VenueNo, Avg(Ticket_Price) from events group by VenueNo | This sample illustrates use of AVG function. The query retrieves average ticket price for every venue.
Function_Cast.sql | select Event_Name, Cast(Event_Date As String) from events | This sample illustrates use of CAST function. The query retrieves all events and their dates as strings.
Function_Count.sql | select count(*) c from Employee | This sample illustrates use of COUNT function. This query counts number of rows in the table "Employee". Having performed it, you will have one column named "c" and one row in it containing number of rows in the table.
Function_Length.sql | select Event_Name, Length(Event_Name) from events | This sample illustrates use of LENGTH function. The query retrieves events' names and their lengths.
Function_Lower.sql | select Event_Name, Lower(Event_Name) from events | This sample illustrates use of LOWER function. The query retrieves events' names in normal capitalization and with all letters in lower case.
Function_Max.sql | select max(Capacity) from venues | This sample illustrates use of MAX function. The query retrieves maximal capacity from venues.
Function_Min.sql | select min(Capacity) from venues | This sample illustrates use of MIN function. The query retrieves minimal capacity from venues.
Function_Pos.sql | select Event_Name, Pos('Finals', Event_Name) from events | This sample illustrates use of POS function. The query retrieves events' names address of position of the word 'Finals' in it.
Function_Substring.sql | select SubString(Event_Name,2,9) from events | This sample illustrates use of SUBSTRING function. The query retrieves events' names where first letter is excluded as well as letters after tenth.
Function_Sum.sql | select VenueNo, Sum(Ticket_Price) from events group by VenueNo | This sample illustrates use of SUM function. The query retrieves sum of ticket prices for every venue.
Function_Sysdate_Now.sql | select Event_Name, Event_Date, sysdate as CurDate from events where Event_Date < now | This sample illustrates use of SYSDATE and NOW functions. The query retrieves all events that have occured already. Notice that functions NOW and SYSDATE do same things and you can use either.
Function_ToDate.sql | select Event_Name, Event_Date, sysdate as CurDate from events where Event_Date < ToDate('11/19/2003 10:15:30 am','MM/DD/YYYY hh:nn:ss ampm') | This sample illustrates use of TODATE function. The query retrieves all events that have occured before the specified date.
Function_ToString.sql | select Event_Name, ToString(Event_Date, "'Event date is 'mm/dd/yyyy hh24:nn:ss '!'") Formated_Date from events where Event_Date < now | This sample illustrates use of TOSTRING function. The query retrieves all events that have occured already together with formatted date.
Function_Trim.sql | select Trim(' '+Event_Name+' ') from events | This sample illustrates use of TRIM function. The query retrieves events' names. Although some blanks are appended on both sides of column "Event_Name", function trim removes them. Use LTRIM or RTRIM if you want to remove blanks from on side only.
Str_Concat.sql | select FirstName + ' ' + LastName as Name from employee | This sample illustrates how to use string concatenation. The query constructs a column "Name" using field "FirstName", then blank space, then field "LastName" from table "Employee".
Str_Concat_2.sql | select FirstName || ' ' || LastName as Name from employee | This sample illustrates how to use string concatenation. The query constructs a column "Name" using field "FirstName", then blank space, then field "LastName" from table "Employee".

Simple_Case.sql

select event_name, case VenueNo
when 2 then 'Memorial Stadium'
when 4 then 'Coliseum'
else 'unknown venue: VenueNo='+VenueNo
end as Venue
from events

This sample illustrates use of simple CASE function. For each row in the dataset being retrieved, field "VenueNo" is compared to 2, and, if it is true, the string 'Memorial Stadium' appears to the right of event name. If it is not true, then "VenueNo" is compared to 4, and if it equals to 4, string 'Coliseum' appears. If "EventNo" matches neither 2 nor 4, ELSE part of the statement is executed and 'unknown venue: VenueNo='is shown followed by value of the field "VenueNo".

Searched_Case.sql | select Event_Name, Case when Event_Date when Event_Date>now then 'In the future' else 'Right now' end as 'When' from events | This sample illustrates use of SEARCHED CASE construction. The query retrieves all events and notification whether it had happened already.
Operator_Like.sql | select * from events where Event_Name Like '%men%' | This sample illustrates use of LIKE operator. The query retrieves all records from "events" where "Event_Name" contains substring 'men'.
Operator_Between.sql | select * from events where VenueNo Between 3 and 8 | This sample illustrates use of BETWEEN operator. The query retrieves all records from "events" where VenueNo is more than 2 and less than 9.
In_Operator.sql | select * from Employee where FirstName in ('Roberto', 'Roger') order by FirstName | This example shows how to use keyword IN. The query retrieves all data from table "Employee" concerning employees with names 'Roberto' or 'Roger'.
Function_Upper.sql | select Event_Name, Upper(Event_Name) from events | This sample illustrates use of UPPER function. The query retrieves events' names in normal capitalization and with all letters in upper case.
Subquery_where_exists.sql | select Venue from venues v where not exists (select * from events where VenueNo=v.VenueNo) | This sample demonstrates use of subquery in where clause together with EXISTS keyword. The query returns all venues where no event will take place.
Subquery_where_in.sql | select Venue from venues where VenueNo in (select VenueNo from events) | This sample illustrates use of subquery in where clause together with IN keyword. The query returns only those venues where some event will occur.
Subquery_where_equal.sql | select Venue from venues v where 1 = (select count(*) from events where VenueNo=v.VenueNo) | This sample illustrates use of subquery in where clause when result being evaluated is compared to a constant. This query shows all venues where only one event will occur.
Subquery_where_compare_any.sql | select e.Event_Name, v.Capacity from events e, venues v where e.VenueNo=v.VenueNo and v.Capacity > any (select Capacity from venues where VenueNo between 3 and 4) | This example illustrates use of keyword ANY in where clause. This query returns events that will occur on venues with capacity more than in venue 3 or 4.
Subquery_where_compare_all.sql | select e.Event_Name, v.Capacity from events e, venues v where e.VenueNo=v.VenueNo and v.Capacity < all (select Capacity from venues where VenueNo between 3 and 4) | This example illustrates use of keyword ALL in where clause. This query returns events that will occur on venues with capacity lower than in venues 3 and 4.
subquery_update.sql | update Events e set Ticket_price = (select Capacity from Venues where VenueNo=e.VenueNo)*2 where Ticket_price <= 5 | This sample demonstrates use of subquery in SET clause. The query works as follows. First, only those entries are picked that have "Ticket_price" 5 of less. Then corresponding venue is taken, its capacity is multiplied by two and set as the new ticket price. Notice that NULL fields are not affected.
Subquery_select_as_field.sql | select Venue, (select count(*) from Events where VenueNo = v.VenueNo) EventsCount from Venues v order by EventsCount desc | This sample demonstrates use of subquery representing a separate field. For each entry in table "Venues" the query counts quantity of events from table "Events" and places this number in a column named "EventsCount".
Subquery_select_from_select.sql | select count(*) UniqueNamesCount from (select distinct FirstName from Employee) | This sample illustrates use of a subquery in FROM clause. The subquery retrieves count of unique names in table "Employee", then function "count" counts quantity of these names.

Subquery_Insert_Into.sql

drop table venues2;
create table venues2
( VenueNo autoinc, Venue varchar(30), Capacity integer, Venue_Map graphic, Remarks memo, primary key idxPrimaryKey (VenueNo) );
insert into Venues2
select * from venues v
where not exists (select * from events where VenueNo=v.VenueNo)

This sample demonstrates use of subquery in INSERT INTO clause. The first query in the script deletes table Venues2. Next one creates it. The third query inserts into the new table all venues where no event will take place. Notice that two subqueries here are enclosed one into another.

Alter_Table_Add.sql

drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'2');
insert into MyTable (val1, val2) values (4,'3');
insert into MyTable (val1, val2) values (5,'9');
insert into MyTable (val1, val2) values (7,'Hello');
alter table MyTable add (val3 float);
select * from MyTable order by ID val2 desc;

This sample illustrates use of ALTER TABLE statement with ADD option.
This SQL script contains eight queries. First one deletes table named "MyTable". The second creates this table with 3 fields. Queries 3 - 6 insert some data into the table.
The seventh query adds a new column named "val3" of type float to the table. Query #8 retrieves all rows from this table.

Alter_Table_Memory.sql

drop table memory MyTable;
create table memory MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into memory MyTable (val1, val2) values (2,'2');
insert into memory MyTable (val1, val2) values (4,'3');
insert into memory MyTable (val1, val2) values (5,'9');
insert into memory MyTable (val1, val2) values (7,'Hello');
alter table memory MyTable add (val3 float);
select * from memory MyTable order by ID val2 desc

This sample illustrates use of ALTER TABLE statement with MEMORY option.
This SQL script contains eight queries. First one deletes table named "MyTable". The second creates this table IN-MEMORY with 3 fields. Queries 3 - 6 insert some data into the table. The seventh query adds a new column named "val3" of type float to the table. Query #8 retrieves all rows from this table.

Alter_Table_Modify.sql

drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'2');
insert into MyTable (val1, val2) values (4,'3');
insert into MyTable (val1, val2) values (5,'9');
insert into MyTable (val1, val2) values (7,'Hello');
alter table MyTable mofify (val1 string(5));
select * from MyTable order by ID val2 desc;

This sample illustrates use of ALTER TABLE statement with MODIFY option.
This SQL script contains eight queries. First one deletes table named "MyTable". The second creates this table with 2 fields. Queries 3 - 6 insert some data into the table.
The seventh query modifies column named "val1" so that it turns to be string. Query #8 retrieves all rows from this table.

Create_Drop_Table.sql

drop table developers;
create table developers
( id AutoInc, Code Integer, Name varchar(20), Birthday Date, Photo Graphic
BlobCompressionMode 9
BlobBlockSize 1024
BlobCompressionAlgorithm ZLib );
select * from developers;

This sample demonstrates use of CREATE TABLE and DROP TABLE statements. This SQL script contains three queries. First one deletes table named "developers". The second creates this table with 5 fields and 3 database options. The third query retrieves all rows from this table (of course, the table is empty at this point).

Create_Index.sql | drop table developers; create table developers ( id AutoInc, Code Integer, Name varchar(20) ); insert into developers (code, name) values (2, 'Janet'); create unique index UniqueName ON developers (name desc nocase, code); select * from developers; | This sample illustrates use of CREATE INDEX statement. This SQL script contains five queries. First one deletes table named "developers". The second creates this table with 3 fields. The third query inserts some data into the table. The fourth creates index so that "name"+"code" combination must be unique, for field "name" sorting order is descending and is case-insensitive. The new index is given name "UniqueName". The fifth query retrieves all rows from this table.

Create_InMemory_Table.sql

drop table memory developers;
create table memory developers (id AutoInc, code Integer, name varchar(20));
insert into memory developers (code, name) values (5, 'Bob');
insert into memory developers (code, name) values (7, 'Jeck');
insert into memory developers (code, name) values (2, 'Carol');
insert into memory developers (code, name) values (5, 'Harry');
insert into memory developers (code, name) values (4, 'Sam');
insert into memory developers (code, name) values (9, 'Chris');
select * from memory developers order by name desc, code, id;

This sample illustrates use of CREATE TABLE statement with MEMORY option. This SQL script contains nine queries. First one deletes table named "developers". The second creates this table in-memory with 3 fields. Queries 3-8 insert some data into the table. The ninth query retrieves all rows from this table.

Delete.sql

drop table developers;
create table developers (id AutoInc, code Integer, name varchar(20));
insert into developers (code, name) values (5, 'Bob');
insert into developers (code, name) values (7, 'Jeck');
delete from developers where name='Bob';
select * from developers order by name desc, code, id

This sample illustrates use of DELETE keyword. This SQL script contains six queries. First one deletes table named "developers". The second creates this table with 3 fields. Queries 3 and 4 insert some data into the table. The next query deletes all strings in the table that have 'Bob' as value of "name" field. The sixth query retrieves all rows from this table.

Drop_Index.sql

drop table developers;
create table developers ( id AutoInc, Code Integer, Name varchar(20) );
insert into developers (code, name) values (2, 'Janet');
create unique index UniqueName ON developers (name desc nocase, code); drop index developers.UniqueName;
select * from developers;

This sample illustrates use of DROP INDEX statement. This SQL script contains six queries. First one deletes table named "developers". The second creates this table with 3 fields. Query #3 inserts some data into the table. The fourth creates index so that "name"+"code" combination must be unique, for field "name" sorting order is descending and is case-insensitive. The new index is given name "UniqueName". The fifth query deletes index "UniqueName" from table "developers". The sixth query retrieves all rows from this table.

Insert_Into.sql

drop table developers;
create table developers (id AutoInc, code Integer, name varchar(20));
insert into developers (code, name) values (5, 'Bob');
insert into developers (code, name) values (7, 'Jeck');
insert into developers (code, name) values (2, 'Carol');
insert into developers values (10, 5, 'Harry');
insert into developers (code, name) values (4, 'Sam');
insert into developers (code, name) values (9, 'Chris');
select * from developers order by name desc, code, id;

This sample illustrates use of INSERT statement. This SQL script contains nine queries. First one deletes table named "developers". The second creates this table with 3 fields. Queries 3-8 insert some data into the table. The ninth query retrieves all rows from this table. Notice that you don't have to specify column names as long as you let parameters follow the order of columns in the table (line 6).

Select_Into.sql | select distinct FirstName into NewTable from employee | This sample illustrates use of SELECT INTO statement. The query returns all names from the field "FirstName" excluding duplicates and places it inside a new table giving it name "NewTable".

Transactions_Flush.sql

drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'2');
insert into MyTable (val1, val2) values (4,'3');
start transaction;
insert into MyTable (val1, val2) values (5,'Flush1');
insert into MyTable (val1, val2) values (7,'Flush2');
commit flush;
select * from MyTable;

This sample illustrates use of FLUSH option in COMMIT statement.
This SQL script contains 9 queries. Table "test" is deleted, then recreated and filled with data. A transaction is started, data is inserted, then changes are comitted and immediately flushed to disk. At last you can see what data is contained in the table after all queries.

Transactions_SQL.sql

drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'Start');
start transaction;
insert into MyTable (val1, val2) values (4,'Rollback');
rollback;
start transaction;
insert into MyTable (val1, val2) values (5,'Commit');
commit;
select * from MyTable;

This sample illustrates use of TRANSACTIONS via SQL. Notice that TAbsDatabase supports transactions as class methods so you might find it better to use another way.
This SQL script contains 14 queries. Table "MyTable" is deleted, then recreated and filled with data. A transaction is started, data is inserted, then this last action is cancelled.
Another transaction is started. data is inserted, then changes are comitted. At last you can see what data is contained in the table after all queries.

Update.sql

drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'2');
insert into MyTable (val1, val2) values (4,'3');
insert into MyTable (val1, val2) values (5,'9');
insert into MyTable (val1, val2) values (7,'Hello');
update MyTable set val2='Many' where val1>2;
select * from MyTable;

This sample illustrates use of UPDATE statement.
This SQL script contains 8 queries. Table "MyTable" is deleted, then recreated and filled with data. The seventh query sets field "val2" to string 'Many' where "val1" is more than 2. At last you can see what data is contained in the table after all queries.