SQL

SQL Queries

Top Previous Next

Executing Queries

Structured Query Language (SQL) is the industry standard query language used for defining, organizing, managing, and retrieving data stored in relational databases. Unlike traditional procedural programming languages such as C and Pascal, you do not need to explicitly define how to perform a database operation. You can simply enter a request to the database using the English-like SQL syntax, and the database will determine the best method to process the request and return the results to you when it is finished.

To execute any SQL statement use the dbExecSQL command or the dbPrepareSQL command. This section of the users guide will concentrate solely on constructing and executing statements. Retrieving results from an executed statement will be covered in the next section.

The functions provided by SQL go beyond simple data retrieval, although that is still one of its most important functions. SQL is actually divided into three parts, known as Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). Each of these performs a specific role, and together you can use them to perform all functions a DBMS provides, including:

•	Data definition - lets you define the structure and organization of data and the relationships between data.

•	Data manipulation -allows you to retrieve existing data from the database and update the database by adding new data, deleting old data, and modifying previously stored data.

•	Data control -allows you to protect data against unauthorized access, and define integrity constraints to protect data from corruption.

Data Definition Language

The schema of a database is handled by a set of SQL statements in the SQL Data Definition Language (DDL). DDL makes use of the CREATE, DROP or ALTER SQL commands to define, remove or modify the definition of a database object. We will briefly explain the CREATE TABLE statement.

A database can contain many tables, and each table in a database stores information. Tables are composed of rows (records) and columns (fields). You can use the CREATE TABLE statement to create a new table in a database. The basic syntax of the SQL CREATE TABLE statement is:

CREATE TABLE table-name (column-name data-type, .... )

Example:

pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH+"db1.mdb","")
hstmt = dbExecSQL(pdb,"CREATE TABLE account (lname CHAR(15),fname CHAR(10), branch integer)")

The ANSI/ISO SQL standard specifies a minimal set of data types that a DBMS should support. Almost all commercial SQL drivers support these data types. The table below lists the SQL data types and their EBASIC equivalents.

Data Type	EBASIC Equivalent	Description
CHAR(len)	STRING or ISTRING	Fixed-length character string
VARCHAR(len)	STRING or ISTRING	Variable-length character string
BINARY(len)	Array/POINTER/UDT	Binary data
COUNTER [(int,int)]	INT/UINT	Auto-increment integer. Optional start, amount
SMALLINT	WORD/SWORD	Small integer number
INTEGER [INT]	INT/UINT	Integer number
FLOAT	FLOAT	Low-precision floating point number
DOUBLE	DOUBLE	High-precision floating point number
DATE	DBDATE*	Date UDT
TIME	DBTIME*	Time UDT
TIMESTAMP	DBTIMESTAMP*	Timestamp UDT
DECIMAL [DEC] DECIMAL(precision,scale)	DOUBLE / STRING	Decimal numbers (use default precision and scale) Default precision is 17 and default scale is 6

*UDT's defined by the database command pak.

COUNTER is the keyword used by the Microsoft Access Driver for an auto-incrementing integer. Other drivers may use IDENTITY or some other keyword. Consult your database documentation for details.

ODBC handles cross type conversions. For example A string variable can be bound to a field containing numeric data and the driver will perform the conversion during retrieval.

Data Manipulation Language (DML)

Retrieving or manipulating the data in a database is handled by a set of SQL statements called the SQL Data Manipulation Language, or DML. The basic DML statements are SELECT, INSERT, DELETE and UPDATE.

Selecting Records

The basic syntax of the SELECT statement is:

SELECT item_list FROM table_list WHERE search_condition

The basic SELECT statement is made up of three components: SELECT, FROM and WHERE. The functions of each of these components is listed below:

•	SELECT - specifies the columns or calculated columns to be retrieved by the query.

•	FROM - specifies the tables that contain the items in the SELECT list.

•	WHERE - specifies the search condition that must be met to select a row.

The WHERE clause may contain multiple search conditions. The search conditions that can be included in the WHERE clause are shown in the following list. They can include:

•	Comparison operators (=, >, <, >=, <=, <>, !=)

•	Ranges (BETWEEN and NOT BETWEEN)

•	Lists (IN and NOT IN)

•	String matches (LIKE and NOT LIKE)

•	BLOB matches (MATCH and NOT MATCH)

•	Unknown values (IS NULL and IS NOT NULL)

•	Logical combinations (AND, OR)

•	Negations (NOT)

For example, to perform a query to find all customers whose account balance is greater than $10,000, you would use the following select statement:

hstmt = dbExecSQL(pdb,"SELECT lname, fname, balance FROM account WHERE balance > 10000")

Inserting Records

The INSERT statement is used to add a new row to a table. The basic syntax of the INSERT statement is:

INSERT INTO table_name(column_names) VALUES value_list

The INSERT statement is made up of two components: INSERT INTO and VALUES. The function of these components is listed below:

•	INSERT INTO - specifies the table you want to insert a row into. It can optionally contain a column list to specify that data should only be inserted into those columns. Columns not in this list will be inserted with NULL values.

•	VALUES - specifies the data value you want to insert. You can insert values by using constants or parameters.

As stated above, the value list may contain constants or parameters. A constant is any numeric, text or date value that can be expressed in text form, such as `John', `Monday', 123, 54.823, etc. An example of the INSERT command using constants is shown below. This example adds a new account for John Smith to the database:

hstmt = dbExecSQL(pdb,"INSERT INTO account (lname, fname, branch, balance) VALUES ('john', 'smith', 101, 10000)")

Parameter data is represented by a question mark (?) in the value list, and values can be inserted later. Parameters can be used when the data values are unknown at preparation time, or when you want to save preparation time. An example of the INSERT command using parameters is shown below. This example is used to insert rows into the database, but the values are not currently known. The actual values to be inserted are bound before execution with the dbBindParameter command. After the statement is prepared it is executed with dbExecute.

hstmt = dbPrepareSQL(pdb,"INSERT INTO account (lname, fname, branch) VALUES (?,?,?)")

Deleting Records

The DELETE statement deletes one or more rows from a table. The basic syntax of the DELETE statement is:

DELETE FROM table_name WHERE search_condition

The DELETE statement is made up of two components: DELETE FROM and WHERE. The function of these components is listed below:

•	DELETE FROM - specifies the table you want to delete rows from.

•	WHERE - specifies the search conditions that must be met to delete a row.

The WHERE clause may contain multiple search conditions. For a list of search conditions that can be included in the WHERE clause, refer to "Retrieving Data from the Database".

The following example will delete the account for John Smith from the database:

hstmt = dbExecSQL(pdb,"DELETE FROM account WHERE fname = 'john' AND lname = 'smith'")

Updating the Data

The UPDATE statement changes data in existing rows in a table. The basic syntax of the UPDATE statement is:

UPDATE table_name SET column_names expression WHERE search_condition

The UPDATE statement is made up of three components: UPDATE, SET and WHERE. The function of these components is listed below:

•	UPDATE - specifies the table you want to update rows in.

•	SET - specifies the columns you want to change and an expression that defines the changes to be made for each column.

•	WHERE - specifies the search conditions that must be met to update a row.

The WHERE clause may contain multiple search conditions. For a list of search conditions that can be included in the WHERE clause, see the SQL Command and Function Reference.

The following example will add 6% interest to all accounts with a balance greater than $1000.

hstmt = dbExecSQL(pdb,"UPDATE account SET balance = balance * 1.06 WHERE balance > 1000")