Supported ANSI-SQL syntax

ANSI-SQL Syntax

---

ANSI-SQL spec. contains a lot of SQL syntax, a portion we use a lot, another we use less, this page tells you what portion JackHare supports, what portion does not.

SELECT

Selects data from a table.
Examples:

:::SQL
--sample #1
SELECT FNAME, MINIT, LNAME FROM EMPLOYEE;

--sample #2
SELECT FNAME, MINIT, LNAME FROM EMPLOYEE WHERE ADDRESS = 'Hsinchu';

--sample #3
SELECT
-- * asterisk not supported yet !!
FNAME, minit, LNAME 
FROM employee 
WHERE BDATE IS NOT NULL 
AND (LNAME = 'MIAO' OR fname LIKE 'TAKESHI') ;

INSERT

Inserts a new row / new rows into a table.
Examples:

:::SQL
--sample #1
INSERT INTO EMPLOYEE (SSN, BDATE, FNAME, LNAME) 
VALUES (1000, '2011-10-12', 'TAKESHI', 'MIAO'); 

--sample #2
INSERT INTO EMPLOYEE1 (SSN, BDATE, FNAME, LNAME) 
SELECT SSN, BDATE, FNAME, LNAME FROM EMPLOYEE2

--sample #3
INSERT INTO EMPLOYEE (SSN, BDATE, FNAME, LNAME) 
SELECT SSN, BDATE, FNAME, LNAME FROM EMPLOYEE2 
WHERE 
( 
   FNAME LIKE '%TAKESHI%' OR (LNAME NOT LIKE '%MIAO%' AND MINIT = 'SURNAME') 
) 
OR ADDRESS LIKE '%Hsinchu%' 

UPDATE

Updates data in a table.
Examples:

:::SQL
--sample #1
UPDATE EMPLOYEE 
SET FNAME = 'TAKESHI', LNAME = 'MIAO';

--sample #2
UPDATE EMPLOYEE 
SET FNAME = 'TAKESHI', LNAME = 'MIAO' 
WHERE ID = 1;

DELETE

Deletes rows form a table.
Examples:

:::SQL
--sample #1
DELETE FROM EMPLOYEE;

--sample #2
DELETE FROM EMPLOYEE WHERE ID = 1;

--sample #3
DELETE FROM EMPLOYEE 
WHERE LNAME LIKE '%MIAO%' AND FNAME = 'TAKESHI';

CREATE TABLE

Creates a new table.
Examples:

:::SQL
--by now, we support the NOT NULL, UNIQUE, PRIMARY KEY constraints
--sample #1, we support row-level constraints
CREATE TABLE EMPLOYEE 
( 
   SSN bigint PRIMARY KEY, 
   BDATE Integer NOT NULL, 
   FNAME varchar(20) NOT NULL UNIQUE, 
   LNAME varchar(20) NOT NULL UNIQUE, 
   MINIT varchar(20) NOT NULL, 
   SEX varchar(10) NOT NULL, 
   ADDRESS varchar(50) NOT NULL, 
   SALARY decimal(19, 2) NOT NULL 
) 

--sample #2, we also support table-level constraints
Create Table EMPLOYEE 
( 
   SSN bigint NOT NULL, 
   BDATE Int NOT NULL, 
   FNAME varchar(20) NOT NULL, 
   LNAME varchar(20) NOT NULL, 
   MINIT varchar(20) NOT NULL, 
   SEX varchar(10) NOT NULL, 
   ADDRESS varchar(50) NOT NULL, 
   SALARY decimal(19, 2) NOT NULL, 
   CONSTRAINT employee_ssn_pk PRIMARY KEY(SSN), 
   CONSTRAINT employee_ssn_uk UNIQUE(FNAME, LNAME) 
)

ALTER TABLE ADD COLUMN

Adds a new column to a table.
Examples:

:::SQL
--sample #1
ALTER TABLE EMPLOYEE 
ADD COLUMN ADDRESS VARCHAR(50) DEFAULT 'Hsinchu city' NOT NULL UNIQUE;

--sample #2
ALTER TABLE EMPLOYEE 
ADD /*COLUMN can be bypassed*/ ADDRESS VARCHAR(50) DEFAULT 'Hsinchu city' NOT NULL UNIQUE;

ALTER TABLE DROP COLUMN

Removes a column from a table.
Examples:

:::SQL
--sample #1
ALTER TABLE EMPLOYEE DROP COLUMN ADDRESS;

--sample #2
ALTER TABLE EMPLOYEE DROP /*COLUMN can be bypassed*/ ADDRESS;

ALTER TABLE ALTER COLUMN

Changes the default value of a column, set NULL, set NOT NULL, or rename a column.
Examples:

:::SQL
--sample #1, set default value
ALTER TABLE EMPLOYEE 
ALTER COLUMN ADDRESS SET DEFAULT 'Kaohsiung city';

--sample #2, drop default value
ALTER TABLE EMPLOYEE 
ALTER COLUMN ADDRESS DROP DEFAULT;

--sample #3, set NULL
ALTER TABLE EMPLOYEE 
ALTER COLUMN ADDRESS SET NULL;

--sample #4, set NOT NULL
ALTER TABLE EMPLOYEE 
ALTER COLUMN ADDRESS SET NOT NULL;

--sample #5, rename a column
ALTER TABLE EMPLOYEE 
ALTER COLUMN ADDRESS RENAME TO ADDRESS_1;

ALTER TABLE ADD CONSTRAINT

Adds a constraint to a table.
Examples:

:::SQL
--sample #1
ALTER TABLE EMPLOYEE 
ADD CONSTRAINT EMPLOYEE_LAST_NAME_UK UNIQUE(LAST_NAME);

--sample #2
ALTER TABLE EMPLOYEE 
ADD /*CONSTRAINT EMPLOYEE_LAST_NAME_UK can be bypassed*/ UNIQUE(LAST_NAME);

--sample #3
--we DO NOT SUPPORT to add PRIMARY KEY, following syntax will throw exception
ALTER TABLE EMPLOYEE
ADD CONSTRAINT EMPLOYEE_LAST_NAME_PK PRIMARY KEY(LAST_NAME)

ALTER TABLE DROP CONSTRAINT

Removes a constraint from a table.
Examples:

:::SQL
--sample #1
ALTER TABLE EMPLOYEE 
DROP CONSTRAINT EMPLOYEE_LAST_NAME_UK 

ALTER TABLE DROP CONSTRAINT

Removes a table.
Examples:

:::SQL
--sample #1
DROP TABLE EMPLOYEE 

JOIN TABLE

NOTICE

---

The ANSI-SQL syntax we supported are all listed here, the other not supported syntax may be release in the not far future. we welcome your opinions very much, please go to our discussion.