Mini SQL Version 1.0.11 Specification
The mSQL language offers a significant subset of the features
provided by ANSI SQL. It allows a program or user to store, manipulate
and retrieve data in table structures. It does not support relational capabilities
such as table joins, views or nested queries. Although it does not support
all the relational operations defined in the ANSI specification, it does
provide the capability of ``joins'' between multiple tables. All keywords
are case-insensitive. Known commands that Mini SQL version 1.0.11
understands are:
The Create Clause
The create clause as supported by mSQL can only be used to create a table.
It cannot be used to create other definitions such as views. It should
also be noted that there can only be one primary key field defined for
a table. Defining a field as a key generates an implicit ``not null'' attribute
for the field.
CREATE TABLE table_name ( col_name col_type [ not null | primary key ]
[ , col_name col_type [ not null | primary key ] ] )
Example:
CREATE TABLE emp_details(first_name char(15) not null,
last_name char(15) not null, dept char(20),
emp_id int primary key, salary int )
The available types are:-
 |
char (len) String of chracters (or other 8 bit data) |
|
int Signed integer values |
|
real Decimal or Scientific Notation real values |
The Drop Clause
Drop is used to remove a table definition from the database:
The Insert Clause
Unlike ANSI SQL, you cannot nest a select within an insert (i.e. you cannot
insert the data returned by a select). If you do not specify the field
names they will be used in the order they were defined - you must specify
a value for every field if you do this.
INSERT INTO table_name [ ( column [ , column ] ) ]
VALUES (value [, value] )
Example:
INSERT INTO emp_details ( first_name, last_name, dept, salary)
VALUES (`David', `Hughes', `I.T.S.','12345') INSERT INTO emp_details
VALUES (`David', `Hughes', `I.T.S.','12345')
The number of values supplied must match the number of columns.
The Delete Clause
The syntax for mSQL's delete clause is
DELETE FROM table_name WHERE column OPERATOR value
[ AND | OR column OPERATOR value ]
where OPERATOR can be <, >, =, <=, >=, <>, or like
Example:
DELETE FROM emp_details WHERE emp_id = 12345
The Select Clause
The select offered by mSQL lacks some of the features provided by the SQL
spec:
|
No nested selects |
|
No implicit functions (e.g. count(), avg() ) |
It does however support:
|
Joins - including table aliases |
|
DISTINCT row selection |
|
ORDER BY clauses |
|
Regular expression matching |
|
Column to Column comparisons in WHERE clauses |
So, the formal syntax for mSQL's select is:-
SELECT [table.]column [ , [table.]column ]
FROM table [ = alias] [ , table [ = alias] ]
[ WHERE [table.] column OPERATOR VALUE
[ AND | OR [table.]column OPERATOR VALUE] ]
[ ORDER BY [table.]column [DESC] [, [table.]column [DESC] ]
where OPERATOR can be <, >, =, <=, >=, <>, or like,
and VALUE can be a literal value or a column name
Simple Example:
SELECT first_name, last_name FROM emp_details WHERE dept = `finance'
To sort the returned data in ascending order by last_name and descending
order by first_name the query would look like this
Example : (Sorting and removingduplicates):
SELECT first_name, last_name FROM emp_details WHERE dept = `finance'
ORDER BY last_name, first_name DESC
And to remove any duplicate rows, the DISTINCT operator could be used:
SELECT DISTINCT first_name, last_name FROM emp_details
WHERE dept = `finance' ORDER BY last_name, first_name DESC
The regular expression syntax supported by LIKE clauses is that of standard
SQL:
|
. `_' matches any single character |
|
. `%' matches 0 or more characters of any value |
|
. `\' escapes special characters (e.g. `\%' matches % and `\\' matches
\ ) |
|
. all other characters match themselves |
So, to search for anyone in finance who's last name consists of a letter
followed by `ughes', such as Hughes, the query could look like this:
SELECT first_name, last_name FROM emp_details
WHERE dept = `finance' and last_name like `_ughes'
The power of a relational query language starts to become apparent when
you start joining tables together during a select. Lets say you had two
tables defined, one containing staff details and another listing the projects
being worked on by each staff member, and each staff member has been assigned
an employee number that is unique to that person. You could generate a
sorted list of who was working on what project with a query like:
SELECT emp_details.first_name, emp_details.last_name, project_details.project
FROM emp_details, project_details
WHERE emp_details.emp_id = project_details.emp_id
ORDER BY emp_details.last_name, emp_details.first_name
mSQL places no restriction on the number of tables ``joined'' during a
query so if there were 15 tables all containing information related to
an employee ID in some manner, data from each of those tables could be
extracted, albeit slowly, by a single query. One key point to note regarding
joins is that you must qualify all column names with a table name. mSQL
does not support the concept of uniquely named columns spanning multiple
tables so you are forced to qualify every column name as soon as you access
more than one table in a single select. mSQL-1.0.6 adds table aliases so
that you can perform a join of a table onto itself. With this you could
find out from a list of child/parent tuples any grandparents using something
like
SELECT t1.parent, t2.child from parent_data=t1, parent_data=t2
WHEREt1.child = t2.parent
The table aliases t1 and t2 both point to the same table (parent_data in
this case) and are treated as two different tables taht just happen to
contain exactly the same data.
The Update Clause
The mSQL update clause cannot use a column name as a value. Only literal
values may by used as an update value
UPDATE table_name SET column=value [ , column=value ]
WHERE column OPERATOR value [ AND | OR column OPERATOR value ]
where OPERATOR can be <, >, =, <=, >=, <>, or like
Example:
UPDATE emp_details SET salary=30000 WHERE emp_id = 1234
