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 ] ] )
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:
DROP TABLE table_name

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] )
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

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

UPDATE emp_details SET salary=30000 WHERE emp_id = 1234