INTRODUCTION TO SQL
The Structured Query Language ( SQL ) is an user interface for the storage and retrieval of information in a relational database. Using SQL, you can create, destroy or modify tables. Most importantly, though, you can query the information stored in tables. ODBC reserved words are shown in the "ODBC SQL Reserved Words" listing and may not be used as the name of a table, one if its columns or for anything else.
Although SQL was developed by IBM, other vendors of relational database systems quickly offered SQL on their products, each version differing from the others in some way. The International Organization for Standardization has developed an internationally recognized SQL standard. If some of the examples in this book do not work with your system, check your local documentation.
The Structure of SQL statements
SQL (Structured Query Language) is a relational database language. SQL is not only used for storing and retrieving information or for access to data files but also for creating or deleting tables and for adding, altering or deleting records in these tables. The relational terms row and column are equivalent to the database terms record and field respectively.
To allow access to the information in a database, SQL defines the reserved words (operations, functions and commands) of the language which indicates the actions to be performed on the database. The entire SQL statement has its own specific syntax as described in the "definitions of some commonly used SQL/ODBC commands" section. The best way to learn SQL is first to use the examples based on the sample databases included in this program, and then to try similar statements on your own database. Once you have mastered these simple examples, try out some of the more adventurous examples. These can all be found at the end of this tutorial.
SQL provides both the Data Definition Language (DDL) and the Data Manipulation Language (DML) commands. The DDL commands allow you to create and define new databases, fields and indexes while the DML commands let you build queries to sort, filter and extract data from the database.
DDL Commands
· CREATE is used to create new tables, fields and indexes.
· DROP is used to delete tables and indexes from the databases.
· ALTER is used to modify tables by adding fields or changing field definitions.
These DDL statements can only be used with JET databases. They are not supported for any external database formats. Flex/ODBC currently does not support DDL statements.
DML Commands
· SELECT is used to query the database for records that satisfy specific criteria.
· INSERT is used to load batches of data into the database in one operation or individually.
· UPDATE is used to change the values of particular records and fields.
· DELETE is used to remove records from a database table.
SQL Data Types
SQL precisely defines the types of data which can be stored in its tables. The SQL standard types are as follows:
Character: String of characters. Abbreviation: CHAR
Character returns the character with the ASCII value of expression.
Number: Numbers with precision and scale may be specified.
Decimal: Variant on the numeric theme but the precision is implementation-defined. Abbreviation: DEC. Decimal returns the numeric value of expression.
Integer: Variant on the numeric theme but the precision is implementation-defined and the scale is 0. Abbreviation : INT. Integer returns the integer value of expression, by eliminating its decimal portion, if any.
Smallint: Like INTEGER, SMALLINT has implementation-defined precision and a scale of 0. The only difference between the two types is that SMALLINT's precision must be less than INTEGER's in any given implementation. No abbreviation for SMALLINT.
Float: Approximations of floating point numbers. Table creator may again specify a precision for the values kept in this type of column. Float returns the floating-point value of expression.
Real: Approximation of floating point numbers. REAL differs from FLOAT in that its precision is implementation-defined.
Double- Approximations of floating point numbers. Has implementation-defined precision
Precision : like REAL, however, the precision of REAL must be less that that of DOUBLE-PRECISION.
Date: Date returns the date at the number of days since January 1st of the Year Zero equal to the value of expression. If an argument of three valid integers in order year, month, day is passed separated by commas, the specified date is returned.
Day: Returns the day (of the month) component of the date at the number of days since January 1st of the Year Zero equal to the value of expression.
Description of the Syntax Symbols
- A d A is optional;
- A I B d Either A or B may be provided
- A d... A may be provided 0 or more times consecutively.
- A I B d... Either A or B may be provided 0 or more times consecutively.
Multiple values must be separated by commas.
[ A I B ] Either A or B must be provided.
[ A I B ]... Either A or B must be provided at least once with no maximum number of occurrences, but multiple values must be separated by commas.
Definitions of some SQL/ODBC Commands
SELECT retrieves data from a table.
Syntax: SELECT - DISTINCT I ALL d [ * I [column_expression]...]
FROM [ table_identifier - aliasd ]...
- WHERE condition d
- GROUP BY [column_identifier]...
- HAVING condition dd
- ORDER BY [ ordering ] ...d
where column expression ::= [ table_identifier.* I expression I function I NULL ]
table-identifier ::= [ table_name I view_name I owner.table_name owner.view_name]
condition ::= [expression comparison_operator [expression I subquery]
I expression -NOTd BETWEEN expression AND
expression I expression -NOTd IN ([constant]...)
I expression -NOTd IN (subquery)
I expression comparison_operator [ANY I ALL I SOME ] (subquery)
I alphanumeric_expression -NOTd LIKE mask
I having_expression IS -NOTd NULL
I EXISTS (subquery) I NOT condition
I condition [AND I OR] condition I (condition) ]
expression ::= [arithmetic_expression I alphanumeric_expression]
comparison_operator ::= [= I I <= I>= I <> ]
subquery ::= a SELECT statement within a SELECT statement and may be used in conjunction with the IN, ANY and EXISTS operators. (A correlated subquery was
defined as a subquery naming a column that belongs to a table which is specified in another query block.)
arithmetic operator ::= [ * I / I + I - ]
alphanumeric_expression ::= [numeric_constant I column_identifier
I arithmetic_expression arithmetic_operator arithmetic_expression I (arithmetic_expression)]
constant ::= [integer_constant I decimal_constant
I floating_point_constant I alphanumeric_constant]
mask ::= I percent sign and another constant stands for 0 or
more random charactersI underscore stands for exactly
one character I appears after a LIKE operator
having_expression ::= [ expression I function ]
column_identifier ::= [ column_name I tabel_identifier I owner.column_name ]
having_condition ::= [having_expression comparison_operator [expression
I subquery]
I having_expression -NOTd BETWEEN expression AND expression
I having_expression -NOTd IN ([constant]...)
I having_expression -NOTd IN (subquery)
I having_expression comparison_operator
[ANY I ALL I SOME ](subquery)
I alphanumeric_expression -NOTd LIKE mask
I having_expression IS -NOTd NULL
I EXISTS (subquery) I NOT having_condition
I having_condition [AND I OR] having_condition
I (having_condition) ]
ordering ::= [column_identifier I column_sequence_number]
-ASC & DESCd
function ::= [ [ COUNT I MIN I MAX I SUM I AVG ]
( - DISTINCT d expression ) I COUNT (*) ]
SELECT statement with UNION :
Syntax: SELECT [ * I [column_expression]...] FROM [ table_identifier - aliasd ]...
- WHERE condition d...- GROUP BY [column_identifier]...- HAVING condition d d
UNION
SELECT [ * I [column_expression]..] FROM [ table_identifier - aliasd ]...
- WHERE condition d...- GROUP BY [column_identifier]...
- HAVING condition d d...- ORDER BY [ ordering ] ...d
The end results of the SELECT statements can be placed together underneath one another using the UNION operator. Union merges the tables resulting from two or more select statements into a single table. Rules for using the UNION operator:
An equal number of columns must be specified in the SELECT statements.
Columns that will be combined in the result table must have the same data type.
The ORDER BY clause can only appear in the last SELECT statement.
The SELECT clauses may not use the keyword DISTINCT because the duplicate rows are automatically removed when performing an UNION.
A Subquery is a SELECT statement nested inside a SELECT, SELECT ...INTO, INSERT INTO, DELETE or UPDATE statement or inside another subquery.
::= SELECT [column_expression] FROM [table_identifier]
- WHERE condition using ANY i ALL I SOMEd
SELECT [column_expression] FROM [table_identifier]
- WHERE condition d...HAVING condition...ORDER BY clause.
The Subquery statement must refer to only one column, the data type of which must be compatible with the data type of expression. The WHERE clause in the subquery provides a condition which is always TRUE, therefore the subquery always returns rows.
A Crosstab query lets select values from specified fields or expressions as column heading so that the data can be viewed in a more concise format.
::= TRANSFORM [aggregate function] SELECT statement
PIVOT [pivot field] [IN (value 1, value 2 ...)]
A pivot field is a field or expression you want to use to create columns ( via the fixed values ) in the query's result set.
Clauses
Clauses are modifying conditions used to define the data you want to select or manipulate.
The FROM clause identifies which table (view ) or tables (views) are to be accessed when the SELECT statement is processed. Two tables may have the same name if they belong to different owners. If you refer to a table (view) belonging to another owner, you must specify the owner name before the table (view) name. An alias is specified after the table identifier, if required. If an alias name has been specified for a table (view), it must be used in all relevant statements instead of the original table (view) name.
The rows required for the final result are defined in a condition in the WHERE clause.
A condition can take the following forms:
· Simple comparison
· Coupled with AND, OR and NOT
· BETWEEN, IN, LIKE, NULL, ANY, ALL, SOME and EXISTS operators
· Comparison and IN operator with subquery
The GROUP BY clause groups rows of an intermediate result of the FROM clause on the basis of equal value columns. You can group by more than one column. A group by clause is optional but may never come before a WHERE or FROM clause.
The HAVING clause selects the groups of an intermediate result of the GROUP BY clause on the basis of particular group characteristics. Each expression in a having condition may contain one or more functions (COUNT, MIN, MAX, SUM, AVG) which perform particular calculations on the values in a column. A HAVING clause may only be used if a GROUP BY clause is also present.
The ORDER BY clause sorts the final and remaining rows on the basis of the values in one or more columns but does not actually change the contents of the results. An ORDERBY clause is always last.
Comparison Operators
Comparison operators are used to compare the relative value of two expressions to determine what action should be taken.
ANY compares the value of an expression with any of the values returned by a subquery statement. The operator may be any of: =equal to, >=greater than or equal to, >greater than, <=less than or equal to, not equal to.
ALL compares the value of an expression with all the values returned by a subquery statement and all true results will be returned.
SOME is true only if expression is true (using whichever operator is specified) when compared with any of the values of the column in the subquery statement.
IN compares a value with the values of constants. A set may only contain distinct constants of the same data type.
IN compares a value with the results of the subquery statement listed in a set.
EXISTS tests for any row that satisfies the subquery criteria. The test is true if the subquery results in at least one row being selected and false if the subselect results in no rows being selected. A SELECT statement with an EXISTS operator is seldom executed quickly. The EXISTS operator is usually avoided because reformulating IN statement or JOINS cannot always be done.
BETWEEN tests if a value of an expression is between the values of two other expressions. If this value is between the values of two other expressions then the result is true.
LIKE compares expression1 with expression2.
You may use an asterisk (*) as a substitute for an unspecified number of characters (including no characters at all), and you may use a question mark (?) to substitute for a single unspecified character in expression2.
The NULL operator tests for empty rows in a particular column. The Statement: A is NOT NULL is equivalent to NOT (A is NULL), where A is an expression.
Logical Operators
Logical operators are used to connect expressions, usually within a where clause.
AND combines two predicates in the WHERE clause. The combined predicate is then true only if both of the individual predicates are true.
NOT is usually used to negate a Boolean.
OR is usually used to combine two Booleans. The combined Boolean is then true only if one or both of the individual Booleans are true.
Aggregate Functions
Aggregate functions are used within a SELECT clause on groups of records to return a single value that applies to a group of records.
The COUNT function calculates the number of rows in an expression. Used with the keyword DISTINCT, COUNT only calculates the number of those rows with unique values in the expression. An expression may not contain any nested combination of functions. NULL values are not included in calculations with functions.
The MIN (MAX) function calculates the lowest (highest) values of a particular column. The keyword DISTINCT can be used with MIN (MAX), but has no effect on the processing because duplicate values do not influence the lowest and highest values in a set.
The SUM function calculates the sum of all the values in a given column. Used with the keyword DISTINCT, SUM only calculates the sum of the unique values in the given column.
COUNT, MIN, MAX and SUM functions can only be applied to columns with numeric data type.
The AVG function calculates the average of all the selected values in a particular column. It is usually part of another expression or a select-list. The expression must contain date or numeric type data and it must not include any aggregate functions.
JOIN is used to join two or more tables together according to their relationship to create a new table containing information from both tables. Tables can be joined in three ways:
Inner Join is used to create a query that includes matching records from the two tables.
::= FROM table1 INNER JOIN table2 ON table1.field1 = table2.field2.
Left Join is used to create a left outer join which will include all the records from the first table plus any matching records from the second table.
::= FROM table1 [LEFT] JOIN table2 ON table1.field1 = table2.field2
Right Join is used to create a right outer join which will include all the records from the second table plus any matching from the first table.
::= FROM table1 [RIGHT] JOIN table2 ON table1.field1 = table2.field2
ASC sorts the results in ascending order.
DESC sorts the results in descending order.
DELETE ::= DELETE FROM table_identifier -WHERE conditiond
Rows are removed from a table using the DELETE statement. The table definition still exists.
If the WHERE clause is left out, all the rows are deleted from a table.
Subqueries in the WHERE clause of a DELETE statement cannot refer to the values being deleted.
INSERT ::= INSERT INTO table_identifier - ( [column_name]...) d
VALUES ( [constant I NULL]...)
Single-record append query syntax. New Rows are added (one at a time) to a table.
INSERT ::= INSERT INTO table_identifier - ( [column_name]...) d
SELECT statement
Rows stored in another table are copied and added to the table named, thus creating a new table. You can also append a set of records from another table or query using INSERT INTO with the SELECT FROM clause.
The following rules apply to the INSERT statement in addition to those applied to the INPUT statement:
· The SELECT statement can include subqueries, a join, a group but cannot refer to the table into which new rows will be inserted.
· The number of columns in the INSERT INTO clause must equal the number of columns expressions in the SELECT clause.
· The data type of the column in the INSERT INTO clause must conform with the data type of the columns in the SELECT clause.
· The INSERT statement can be used for changing data types, removing columns, renaming tables or columns and adding a Not Null column to a table.
INPUT ::= INPUT table_identifier- ( [column_name]...) d [const I NULL]...END
The INPUT statement is used to enter many new rows in one single operation.
Rules applied to the INPUT statements:
· Each row of data is entered on a new input line. When the input line ends with a dash, the data on the following line in the same row of the table is included.
· The INPUT statement is always closed with an END.
· It is not mandatory to specify column names.
· The sequence in which the data is inserted can be altered by specifying the columns and entering the data values accordingly.
· It is not necessary to insert values into every column with the INPUT statement but all NOT NULL columns must be included.
· A NULL value is inserted into all the columns not named in the INPUT statement.
UPDATE ::= UPDATE table_identifier
SET [column_name = [expression I NULL]]... -WHERE conditiond
The UPDATE statement is used to alter the values in the rows of a table.
Rules applied to the UPDATE statement:
· An UPDATE statement always refers to a table. The WHERE clause identifies the rows to be updated and the SET clause attributes the new values to the column(s).
· Updating values in columns on which an UNIQUE index has been defined can cause problems. (The index column is updated in the first row which makes it not UNIQUE anymore causing the processing to stop.) To avoid this problem, drop the relevant UNIQUE index, process the UPDATE and then recreate the index.
· For data consistency, an UPDATE has to be applied to all the tables in which the column to be updated resides.
· An UPDATE on a key column can be run without any problems by first checking for problems using the SELECT statement to COUNT the number of rows to be affected by such an UPDATE.
The DISTINCT predicate is used to omit records that contain duplicate data in the selected columns. The output of a recordset of a distinct query can not be updated and does not reflect changes made by other users.
The TOP predicate returns a certain number of records that fall at the TOP or BOTTOM of a range specified by an ORDER BY clause. If the ORDER BY clause is not included, the query will return an arbitrary set of 25 records from the specified table.
ABSOLUTE returns the absolute value of an expression, where the expression has to be numeric:
LOWER converts all letters for expression to lower case.
LOW returns the lower 16-bit value of expression.
MOD returns the value remaining after the largest multiple of expression2 that is less than expression1 is subtracted from expression2.This function is also called modulo division.
MONTH returns the month (of the year) component of the date.
SOME is true only if expression is true (using whichever operator is specified) when compared with any of the values of the column in the subquery statement.
STRING converts expression, which may be of any data type, to a string.
UPPER converts all letters of expression to upper case.
YEAR returns the year component of the date.
SQL / ODBC Query Examples
Select all customer records.
SELECT * FROM CUSTOMER
Select the company name, state and city of all customers.
SELECT CUSTOMER, STATE, CITY FROM CUSTOMER
Select all the Data Access Corporation records.
SELECT * FROM CUSTOMER WHERE CUSTOMER = 'Zorro Cutlery'
Select the names and addresses of all customers in California or Florida.
SELECT CUSTOMER, ADDRESS, STATE, CITY FROM CUSTOMER WHERE STATE = 'CA' OR STATE = 'FL' or
SELECT CUSTOMER, ADDRESS, STATE, CITY FROM CUSTOMER WHERE STATE IN ( 'CA', 'FL')
Select all customer records whose customer_no is greater than 7.
SELECT * FROM CUSTOMER WHERE NUMBER > 7
Select all customer records with customer numbers not equal to 15.
SELECT * FROM CUSTOMER WHERE NUMBER <> 15
Select the number, name and state of each customer who is situated in California and with profits between $1000 and $10,000.
SELECT NUMBER, CUSTOMER, STATE FROM CUSTOMER WHERE STATE = "CA" AND PROFIT >= 1,000.00 <= 10,000.00
Select the Customers' names and numbers whose name begins with A.
SELECT NUMBER, CUSTOMER FROM CUSTOMER WHERE CUSTOMER LIKE 'A%'
Select the Customers' names and numbers whose name ends with r.
SELECT NUMBER, CUSTOMER FROM CUSTOMER WHERE CUSTOMER LIKE '%r'
Select all the customers who have a customer_no.
SELECT * FROM CUSTOMER WHERE NUMBER is NOT NULL
Select the order and detail numbers and display the results grouped by ascending order of order numbers and then by detail number.
SELECT ORDER_NUMBER, DETAIL_NUMBER FROM ORDERDTL GROUP BY ORDER_NO, DETAIL_NUMBER
Select the salesmen who have made at least 3 orders.
SELECT SLSMN FROM ORDERHEA GROUP BY SLSMN HAVING COUNT (*) >3
Find the salesmen and the total amount ordered via these salesmen with totals greater than $1000
SELECT O.SLSMN, SUM (OD.AMOUNT) FROM O.ORDERHEA, OD.ORDERDTL WHERE OD.ORDER_NUMBER = O.ORDER_NUMBER GROUP BY O.SLSMN HAVING SUM (OD.AMOUNT) > 1000
Find the measure of spread (range) of the total ordered amounts.
SELECT AVG(TOTAL) FROM ORDERHEA
Group the customer numbers on the basis of the following criteria:
Total amount ordered is less than $100.00
Total amount ordered is greater than $150.00 and less than $1000.00
Total amount ordered is greater than $5000.00
SELECT CUSTOMER_NUMBER, TOTAL FROM ORDERHEA WHERE TOTAL <100 OR TOTAL > 150 AND TOTAL <1000 OR TOTAL > 5000 GROUP BY TOTAL
Find the maximum (minimum) amount spent on a particular event.
SELECT MAX(AMOUNT), EVENT FROM TRANSACT
SELECT MIN(AMOUNT), EVENT FROM TRANSACT
Find the parts, salesman names, customer details, order and detail numbers for all orders with the detail number = 4.
SELECT C.CUSTOMER, C.ADDRESS, C.CITY, O.ORDER_NUMBER, O.SLSMN, OD.DETAIL_NUMBER, OD.PART_ID FROM C.CUSTOMER, O.ORDERHEA, OD.ORDERDTL WHERE C.NUMBER = O.CUSTOMER_NUMBER AND OD.ORDER_NUMBER = O.ORDER_NUMBER AND (OD.DETAIL_NUMBER = 4)
Increase all discounts by 5%.
UPDATE CUSTOMER SET DISCOUNT = DISCOUNT * 1.05
Delete all orders made via JJT.
DELETE FROM ORDERHEA WHERE SLSMN = "JJT"
Find the names and codes of each customer and also give the parts sold to them.
SELECT CUSTOMER, NUMBER PART_ID FROM CUSTOMER A, ORDERHEA B, ORDERDTL C WHERE A.NUMBER = B.CUSTOMER_NUMBER AND B.ORDER_NUMBER = C.ORDER_NUMBER
Find the names and address details, order numbers and detail numbers of the parts ordered by a particular customer ordering the result by the order numbers in ascending order and detail numbers in descending order.
SELECT O.ORDER_NUMBER, O.ORDER_DATE, O.SLSMN, C.CUSTOMER, C.ADDRESS, C.CITY, OD.PART_ID, OD.DETAIL_NUMBER FROM CUSTOMER C, ORDERHEA O, ORDERDTL OD WHERE C.NUMBER = O.CUSTOMER_NUMBER AND O.ORDER_NUMBER = OD.ORDER_NUMBER ORDER BY O.ORDER_NUMBER ASC, OD.DETAIL_NUMBER DESC
SQL Operators and Functions not yet Supported by FlexODBC*
Nested Sub-Selects ( SubQueries):
Example: Select the company names and order dates of the least recent orders.
SELECT `Order Date`, Customers.`Company Name` FROM Customers Customers, Orders O WHERE Customers.`Customer ID` = O.`Customer ID` and O.`Order Date` IN ( SELECT Min ( P.`Order Date`) FROM Orders P)
Relational Operators:
Between
Example: Select the Company name, its ID, the contact person, country for all customers with ID's lying BETWEEN ANATR and BLONP.
SELECT Customers.`Company Name`, Customers.`Contact Name`, Customers.Country, Customers.`Customer ID` FROM Customers Customers
WHERE (Customers.`Customer ID` BETWEEN `ANATR' And `BLONP')
NOTE: YOU CAN OVERCOME THIS PROBLEM BY USING THE INEQUALITIES, <,>, <=,>=
Any
All
Exists
In
The above listed relational operators are used with the values returned by a subquery.
Union Operator:
Example: Select the company name, contact person and country of all customers in Belgium or Finland.
SELECT Customers.`Company Name` FROM Customers Customers WHERE Customers.Country ='Belgium' UNION
SELECT Customers.`Company Name` FROM Customers Customers
WHERE Customers.Country = `Finland'
BUT YOU CAN GET AROUND THIS PROBLEM BY USING IN ( SET OF FIELDS....) OR A JOIN.
Functions:
Concatenation and Trim
Example: Concatenating the first and last names of the employees.
SELECT TRIM (Employees.`First Name`) + ` ` + TRIM (Employees.`Last Name`) FROM Employees Employees
Ucase (NAME) or Upper (NAME) : Returns the name in upper case.
Lcase (NAME) or Lower (NAME): Returns the name in lower case.
Left (File.Fieldname, 3): Returns the 3 letters at the start of the fieldname.
Right (File.Fieldname, 3): Returns the 3 letters at the end of the fieldname.
Substr (File.Fieldname, 3, 5): Returns the 5 letters starting at the 3rd letter of the fieldname.
Space (20): Add spaces to fields.
Round (File.Fieldname, 2): Rounds the numeric field correct to 2 decimal places.
Truncate (File.Fieldname, 2): truncates the numeric field at the 2nd digit.
Int (File.Fieldname): Returns the integer part of a numeric field
Len (File.Fieldname): Counts the number of characters in each name of the product.
Mod ( Field.Fieldname): Returns the integer part of the quotient. ( Result to a division.)
Month-, Day-, Year (File.DateField): Returns the Month, day or year part respectively of the date field specified.
Date (): Return todays date.
Abs (file.Fieldname): Finds the absolute value of a numeric field
Cos, Sin, Tan, Exp, Log, Sqr, Atan, Asin, Acos, Cos, Cot, Cosec, Square, Standard Deviation and Variance of samples or populations.
* These operators and functions will be supported in later editions of Flex/ODBC.
(c) FLEXquarters