FileMaker 16 SQL 参考

Transcription

1 FileMaker 16 SQL

2 FileMaker, Inc. FileMaker, Inc Patrick Henry Drive Santa Clara, California FileMaker FileMaker Go FileMaker, Inc. FileMaker WebDirect FileMaker Cloud FileMaker, Inc. FileMaker FileMaker FileMaker URL URL FileMaker, Inc. 01

3 1 5 5 SQL 5 FileMaker 5 ExecuteSQL Unicode 7 SQL 7 SELECT 8 SQL 9 FROM 9 WHERE 11 GROUP BY 11 HAVING 12 UNION 12 ORDER BY 13 OFFSET FETCH FIRST 13 FOR UPDATE 14 DELETE 17 INSERT 17 UPDATE 19 CREATE TABLE 20 TRUNCATE TABLE 21 ALTER TABLE 22 CREATE INDEX 22 DROP INDEX 23 SQL /

4 4 SQL FileMaker 35 FileMaker 35 FileMaker 36 SQL 37 40

5 1 SQL FileMaker Pro SQL FileMaker ODBC JDBC ODBC JDBC FileMaker Pro ExecuteSQL FileMaker Pro FileMaker SQL FileMaker ODBC JDBC SQL FileMaker Pro ExecuteSQL SELECT 1 ODBC JDBC FileMaker Pro SQL 1 FileMaker Pro ODBC JDBC SQL 1 FileMaker Pro FileMaker Pro FileMaker Pro Advanced FileMaker Pro Advanced SQL SELECT SQL SQL 2 FileMaker SQL FileMaker FileMaker ODBC JDBC FileMaker ODBC JDBC FileMaker FileMaker ODBC JDBC SQL FileMaker FileMaker ODBC JDBC FileMaker ODBC JDBC FileMaker ODBC JDBC SQL

6 1 6 ExecuteSQL FileMaker Pro ExecuteSQL ExecuteSQL ExecuteSQL ExecuteSQL ExecuteSQL ExecuteSQL ExecuteSQL SELECT 8 SELECT ExecuteSQL ({}) SQL-92 ISO ExecuteSQL ODBC/JDBC ExecuteSQL FileMaker Pro

7 2 FileMaker ODBC JDBC ODBC JDBC FileMaker FileMaker FileMaker Pro FileMaker Server 1 ODBC ODBC JDBC JDBC ODBC JDBC SQL-92 SQL-92 Unicode ODBC JDBC Unicode API ASCII Unicode Unicode SQL_C_WCHAR SQL ODBC JDBC SQL 1 SELECT 8 1 DELETE 17 1 INSERT 17 1 UPDATE 19 1 CREATE TABLE 20 1 TRUNCATE TABLE 21 1 ALTER TABLE 22 1 CREATE INDEX 22 1 DROP INDEX 23 ODBC SQL JDBC SQL FileMaker FileMaker ODBC JDBC SQL ODBC JDBC FileMaker

8 2 8 SELECT SELECT SELECT last_name SALARY * 1.05 SELECT SELECT [DISTINCT] {* column_expression [[AS] column_alias],...} FROM table_name [table_alias],... [ WHERE expr1 rel_operator expr2 ] [ GROUP BY {column_expression,...}] [ HAVING expr1 rel_operator expr2 ] [ UNION [ALL] (SELECT...)] [ ORDER BY {sort_expression [DESC ASC]},...] [ OFFSET n {ROWS ROW} ] [ FETCH FIRST [ n [ PERCENT ] ] { ROWS ROW } {ONLY WITH TIES } ] [ FOR UPDATE [OF {column_expression,...}]] column_alias department dept SELECT dept AS department FROM emp EMP.LAST_NAME E.LAST_NAME E EMP DISTINCT SELECT DISTINCT dept FROM emp

9 2 9 SQL ODBC JDBC SQL SQL FROM 9 WHERE 11 GROUP BY 11 HAVING 12 UNION 12 ORDER BY 13 OFFSET 13 FETCH FIRST 13 FOR UPDATE 14 SELECT FileMaker Pro FileMaker Pro $200,000 SELECT SQL SELECT FROM FROM SELECT FROM table_name [table_alias] [, table_name [table_alias]] table_name table_alias _LASTNAME ExecuteSQL SELECT "_LASTNAME" from emp

10 2 10 FROM employee E LAST_NAME E.LAST_NAME SELECT SELECT * FROM employee E, employee F WHERE E.manager_id = F.employee_id (=) INNER JOIN SELECT * FROM Salespeople INNER JOIN Sales_Data ON Salespeople.Salesperson_ID = Sales_Data.Salesperson_ID LEFT OUTER JOIN SELECT * FROM Salespeople LEFT OUTER JOIN Sales_Data ON Salespeople.Salesperson_ID = Sales_Data.Salesperson_ID Salespeople 1 RIGHT OUTER JOIN 1 FULL OUTER JOIN

11 2 11 WHERE WHERE WHERE WHERE expr1 rel_operator expr2 expr1 expr2 rel_operator $20,000 SELECT last_name,first_name FROM emp WHERE salary >= WHERE WHERE expr1 IS NULL WHERE NOT expr2 SELECT WHERE GROUP BY GROUP BY GROUP BY 列 GROUP BY FROM columns FROM SELECT dept_id, SUM (salary) FROM emp GROUP BY dept_id ID ID

12 2 12 HAVING HAVING $200,000 HAVING expr1 rel_operator expr2 expr1 expr2 SELECT rel_operator $200,000 SELECT dept_id, SUM (salary) FROM emp GROUP BY dept_id HAVING SUM (salary) > UNION UNION SELECT SELECT ALL (UNION ALL) SELECT statement UNION [ALL] SELECT statement UNION SELECT SELECT last_name, salary, hire_date FROM emp UNION SELECT name, pay, birth_date FROM person EMP SALARY RAISES LAST_NAME SELECT SELECT last_name, salary FROM emp UNION SELECT salary, last_name FROM raises

13 2 13 ORDER BY ORDER BY SELECT ORDER BY ORDER BY {sort_expression [DESC ASC]},... sort_expression (ASC) last_name first_name SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name 2 3 last_name first_name SELECT emp_id, last_name, first_name FROM emp ORDER BY 2,3 FileMaker SQL Unicode FileMaker Pro OFFSET FETCH FIRST OFFSET FETCH FIRST OFFSET OFFSET SELECT 0 FETCH FIRST 1 OFFSET SELECT OFFSET FETCH FIRST OFFSET OFFSET FETCH FIRST OFFSET OFFSET OFFSET n {ROWS ROW} ] n n ROWS ROW

14 2 14 FETCH FIRST FETCH FIRST FETCH FIRST [ n [ PERCENT ] ] { ROWS ROW } {ONLY WITH TIES } ] n n 1 n 1 PERCENT n PERCENT ROWS ROW WITH TIES ORDER BY ORDER BY WITH TIES FETCH last_name first_name 26 SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS 10 SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY 10 ORDER BY SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS WITH TIES FOR UPDATE FOR UPDATE SQL FOR UPDATE [OF column_expressions] column_expressions column_expressions SALARY $20,000 SELECT * FROM emp WHERE salary > FOR UPDATE OF last_name, first_name, salary

15 2 15 BLOB a * SQL SELECT 'CatDog' FROM Salespeople SELECT 999 FROM Salespeople SELECT DATE ' ' FROM Salespeople SELECT TIME '02:49:03' FROM Salespeople SELECT TIMESTAMP ' :49:03' FROM Salespeople SELECT Company_Name FROM Sales_Data SELECT DISTINCT Company_Name FROM Sales_Data SELECT Amount FROM Sales_Data SELECT DISTINCT Amount FROM Sales_Data SELECT Date_Sold FROM Sales_Data SELECT DISTINCT Date_Sold FROM Sales_Data SELECT Time_Sold FROM Sales_Data SELECT DISTINCT Time_Sold FROM Sales_Data SELECT Timestamp_Sold FROM Sales_Data SELECT DISTINCT Timestamp_Sold FROM Sales_Data SELECT Company_Brochures FROM Sales_Data SELECT GETAS(Company_Logo, 'JPEG') FROM Sales_Data SELECT * FROM Salespeople SELECT DISTINCT * FROM Salespeople a. BLOB FileMaker column FileMaker (*) SELECT * FROM Salespeople Salespeople SELECT DISTINCT * FROM Salespeople Salespeople 1 FileMaker SELECT * FROM test WHERE c ='' SELECT * FROM test WHERE c <>'' 1 SELECT GetAs() CAST() GetAs()

16 2 16 CAST() GetAs() 1 QuickTime CAST() SELECT 1 JPEG GetAS(field name, 'JPEG') SELECT SELECT NULL CAST() SELECT SELECT CAST(Company_Brochures AS VARCHAR) FROM Sales_Data 1 FileMaker Pro SELECT SQL_VARCHAR 1 FileMaker Pro SELECT 1 SELECT? FileMaker Pro Untitled.dat SELECT GetAs() 1 GetAs() DEFAULT SELECT GetAs(Company_Brochures, DEFAULT) FROM Sales_Data 1 FileMaker Pro GetAs() > GetAs() 'FILE' SELECT GetAs(Company_Brochures, 'FILE') FROM Sales_Data > 'JPEG' SELECT GetAs(Company_Logo, 'JPEG') FROM Company_Icons

17 2 17 'GIFf' 'JPEG' 'TIFF' 'PDF' 'PNGf' DELETE DELETE DELETE DELETE FROM table_name [ WHERE { conditions } ] WHERE WHERE emp DELETE FROM emp WHERE emp_id = 'E10001' DELETE WHERE ID E10001 ID INSERT INSERT 1 1 SELECT INSERT INSERT INTO table_name [(column_name,...)]values (expr,...) column_name VALUES column_name (expr) column_name lastdates[4] expr (') 'Don''t'

18 2 18 INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date) VALUES ('Smith', 'John', 'E22345', 27500, DATE ' ') INSERT emp 空 INSERT PutAs() INSERT INTO table_name (container_name) VALUES(?AS 'filename.file extension') FILE PutAs() PutAs(col, 'type') 16 CAST() GetAs() SELECT column_name SELECT INSERT SELECT INSERT INTO emp1 (first_name, last_name, emp_id, dept, salary) SELECT first_name, last_name, emp_id, dept, salary from emp WHERE dept = 'D050' INSERT SELECT SELECT INSERT SELECT INSERT INSERT/UPDATE ODBC JDBC SELECT INSERT SELECT

19 2 19 UPDATE UPDATE UPDATE UPDATE table_name SET column_name = expr,...[ WHERE { conditions } ] column_name expr (') 'Don''t' WHERE emp UPDATE UPDATE emp SET salary=32000, exempt=1 WHERE emp_id = 'E10001' UPDATE WHERE ID E10001 ID emp UPDATE UPDATE emp SET salary = (SELECT avg(salary) from emp) WHERE emp_id = 'E10001' ID E10001 UPDATE PutAs() UPDATE table_name SET (container_name) =?AS 'filename.file extension' FILE PutAs() PutAs(col, 'type') 16 CAST() GetAs()

20 2 20 CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE table_name ( table_element_list [, table_element_list...]) 1 table_name table_name table_element_list field_name field_type [[repetitions]] [DEFAULT expr] [UNIQUE NOT NULL PRIMARY KEY GLOBAL] [EXTERNAL relative_path_string [SECURE OPEN calc_path_string]] 1 field_name _LASTNAME CREATE TABLE CREATE TABLE "_EMPLOYEE" (ID INT PRIMARY KEY, "_FIRSTNAME" VARCHAR(20), "_LASTNAME" VARCHAR(20)) 1 CREATE TABLE repetitions EMPLOYEE_ID INT[4] LASTNAME VARCHAR(20)[4] 1 field_type NUMERIC DECIMAL INT DATE TIME TIMESTAMP VARCHAR CHARACTER VARYING BLOB VARBINARY LONGVARBINARY BINARY VARYING NUMERIC DECIMAL DECIMAL(10,0) TIME TIMESTAMPTIMESTAMP(6) VARCHAR CHARACTER VARYING VARCHAR(255) 1 DEFAULT expr USER USERNAME CURRENT_USER CURRENT_DATE CURDATE CURRENT_TIME CURTIME CURRENT_TIMESTAMP CURTIMESTAMP NULL

21 UNIQUE FileMaker 1 NOT NULL FileMaker FileMaker Pro 1 field_type BLOB VARBINARY BINARY VARYING 1 EXTERNAL relative_path_string FileMaker FileMaker Pro SECURE OPENcalc_path_string relative_path_string (/) SQL CREATE TABLE T1 (C1 VARCHAR, C2 VARCHAR (50), C3 VARCHAR (1001), C4 VARCHAR (500276)) NOT NULL CREATE TABLE T1NN (C1 VARCHAR NOT NULL, C2 VARCHAR (50) NOT NULL, C3 VARCHAR (1001) NOT NULL, C4 VARCHAR (500276) NOT NULL) CREATE TABLE T2 (C1 DECIMAL, C2 DECIMAL (10,0), C3 DECIMAL (7539,2), C4 DECIMAL (497925,301)) CREATE TABLE T3 (C1 DATE, C2 DATE, C3 DATE, C4 DATE) CREATE TABLE T4 (C1 TIME, C2 TIME, C3 TIME, C4 TIME) CREATE TABLE T5 (C1 TIMESTAMP, C2 TIMESTAMP, C3 TIMESTAMP, C4 TIMESTAMP) CREATE TABLE T6 (C1 BLOB, C2 BLOB, C3 BLOB, C4 BLOB) CREATE TABLE T7 (C1 BLOB EXTERNAL 'Files/MyDatabase/' SECURE) CREATE TABLE T8 (C1 BLOB EXTERNAL 'Files/MyDatabase/' OPEN 'Objects') TRUNCATE TABLE TRUNCATE TABLE TRUNCATE TABLE table_name TRUNCATE TABLE WHERE TRUNCATE TABLE table_name TRUNCATE TABLE FileMaker 301

22 2 22 ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE table_name ADD [COLUMN] column_definition ALTER TABLE table_name DROP [COLUMN] unqualified_column_name ALTER TABLE table_name ALTER [COLUMN] column_definition SET DEFAULT expr ALTER TABLE table_name ALTER [COLUMN] column_definition DROP DEFAULT ALTER TABLE SQL ALTER TABLE Salespeople ADD C1 VARCHAR ALTER TABLE Salespeople DROP C1 ALTER TABLE Salespeople ALTER Company SET DEFAULT 'FileMaker' ALTER TABLE Salespeople ALTER Company DROP DEFAULT SET DEFAULT DROP DEFAULT CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX ON table_name.column_name CREATE INDEX ON table_name (column_name) CREATE INDEX FileMaker FileMaker FileMaker FileMaker FileMaker CREATE INDEX CREATE INDEX ON Salespeople.Salesperson_ID

23 2 23 DROP INDEX DROP INDEX DROP INDEX DROP INDEX ON table_name.column_name DROP INDEX ON table_name (column_name) FileMaker SELECT FileMaker PREVENT INDEX CREATION DROP INDEX ON Salespeople.Salesperson_ID SQL SELECT WHERE HAVING ORDER BY / calc Sales_Data.Invoice_ID PRICE * Number_Of_Days_In_June (') 'Don''t'

24 2 24 ODBC JDBC FileMaker ({}) ODBC/JDBC 1 {D ' '} 1 {T '14:35:10'} 1 {TS ' :35:10'} FileMaker D T TS FileMaker SQL-92 ISO 1 DATE 'YYYY-MM-DD' 1 TIME 'HH:MM:SS' 1 TIMESTAMP 'YYYY-MM-DD HH:MM:SS' FileMaker Pro ExecuteSQL SQL-92 ISO 'Paris' 1.05 DATE ' ' { D ' ' } {06/05/2019} {06/05/19} ODBC/JDBC SQL-92 2 TIME '14:35:10' { T '14:35:10' } {14:35:10} TIMESTAMP ' :35:10' { TS ' :35:10'} {06/05/ :35:10} {06/05/19 14:35:10} 2 : 4 FileMaker ODBC/JDBC SQL-92 2

25 2 25 / SELECT column1 / 3.4E+7 FROM table1 WHERE calc < 3.4E-6 * column2 + - * / ^ ** (+) (-) last_name 'JONES ' first_name 'ROBERT ' + first_name + last_name 'ROBERT JONES ' - first_name - last_name 'ROBERTJONES ' hire_date DATE ' ' + hire_date + 5 DATE ' ' - hire_date - DATE ' ' 29 hire_date - 10 DATE ' ' SELECT Date_Sold, Date_Sold + 30 AS agg FROM Sales_Data SELECT Date_Sold, Date_Sold - 30 AS agg FROM Sales_Data

26 2 26 = <> > >= < <= LIKE NOT LIKE IS NULL IS NOT NULL BETWEEN IN NOT IN EXISTS ANY ALL Null Null True = <> > >= < <= =Any In = <> > >= < <=

27 2 27 SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1' SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Invoice_ID <> 125 SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount > 3000 SELECT Sales_Data.Time_Sold FROM Sales_Data WHERE Sales_Data.Time_Sold < '12:00:00' SELECT Sales_Data.Company_Name FROM Sales_Data WHERE Sales_Data.Company_Name LIKE '%University' SELECT Sales_Data.Company_Name FROM Sales_Data WHERE Sales_Data.Company_Name NOT LIKE '%University' SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount IS NULL SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount IS NOT NULL SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Invoice_ID BETWEEN 1 AND 10 SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID IN (50,250,100) SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID NOT IN (50,250,100) SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID NOT IN (SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-4') SELECT * FROM Sales_Data WHERE EXISTS (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NOT NULL) SELECT * FROM Sales_Data WHERE Sales_Data.Amount = ANY (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1') SELECT * FROM Sales_Data WHERE Sales_Data.Amount = ALL (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NULL) AND OR salary = AND exempt = 1 NOT NOT (salary = AND exempt = 1) SELECT * FROM Sales_Data WHERE Sales_Data.Company_Name NOT LIKE '%University' AND Sales_Data.Amount > 3000 SELECT * FROM Sales_Data WHERE (Sales_Data.Company_Name LIKE '%University' OR Sales_Data.Amount > 3000) AND Sales_Data.Salesperson_ID = 'SP-1'

28 Unary '-' Unary '+' 2 ^ ** 3 * / = <> < <= > >= Like Not Like Is Null Is Not Null Between In Exists Any All 6 Not 7 AND 8 OR WHERE salary > OR hire_date > (DATE ' ') AND dept = 'D101' AND D101 $40,000 WHERE (salary > OR hire_date > DATE ' ') AND dept = 'D101' D101 $40, SQL FileMaker SQL SELECT AVG(SALARY) AVG(SALARY * 1.07) DISTINCT

29 2 29 COUNT (DISTINCT last_name) SUM AVG COUNT MAX MIN SUM(SALARY) AVG(SALARY) COUNT(NAME) COUNT COUNT COUNT(*) MAX(SALARY) MIN(SALARY) SELECT SUM (Sales_Data.Amount) AS agg FROM Sales_Data SELECT AVG (Sales_Data.Amount) AS agg FROM Sales_Data SELECT COUNT (Sales_Data.Amount) AS agg FROM Sales_Data SELECT MAX (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.Amount < 3000 SELECT MIN (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.Amount > 3000 FileMaker 8309 SUM ROUND SELECT ROUND(SUM(Salary), 0) FROM Payroll SELECT SUM(ROUND(Salary, 0)) FROM Payroll

30 2 30 CHR ASCII CHR(67) C CURRENT_USER DAYNAME ID RTRIM RTRIM('ABC ') 'ABC' TRIM TRIM(' ABC ') 'ABC' LTRIM LTRIM(' ABC') 'ABC' UPPER UPPER('Allen') 'ALLEN' LOWER LOWER('Allen') 'allen' LEFT LEFT('Mattson',3) 'Mat' MONTHNAME RIGHT RIGHT('Mattson',4) 'tson' SUBSTR SUBSTRING SUBSTR('Conrad',2,3) 'onr' SUBSTR('Conrad',2) 'onrad' SPACE SPACE(5) ' ' STRVAL STRVAL('Woltman') 'Woltman' STRVAL(5 * 3) '15' STRVAL(4 = 5) 'False' STRVAL(DATE ' ') ' ' TIME TIMEVAL USERNAME USER 9:49 TIME() 21:49:00 ID TIME() SQL CURRENT_TIME

31 2 31 SELECT CHR(67) + SPACE(1) + CHR(70) FROM Salespeople SELECT RTRIM(' ' + Salespeople.Salesperson_ID) AS agg FROM Salespeople SELECT TRIM(SPACE(1) + Salespeople.Salesperson_ID) AS agg FROM Salespeople SELECT LTRIM(' ' + Salespeople.Salesperson_ID) AS agg FROM Salespeople SELECT UPPER(Salespeople.Salesperson) AS agg FROM Salespeople SELECT LOWER(Salespeople.Salesperson) AS agg FROM Salespeople SELECT LEFT(Salespeople.Salesperson, 5) AS agg FROM Salespeople SELECT RIGHT(Salespeople.Salesperson, 7) AS agg FROM Salespeople SELECT SUBSTR(Salespeople.Salesperson_ID, 2, 2) + SUBSTR(Salespeople.Salesperson_ID, 4, 2) AS agg FROM Salespeople SELECT SUBSTR(Salespeople.Salesperson_ID, 2) + SUBSTR(Salespeople.Salesperson_ID, 4) AS agg FROM Salespeople SELECT SPACE(2) + Salespeople.Salesperson_ID AS Salesperson_ID FROM Salespeople SELECT STRVAL('60506') AS agg FROM Sales_Data WHERE Sales_Data.Invoice = 1

32 2 32 ABS ATAN ATAN2 CEIL CEILING DEG DEGREES x y DAY DAY(DATE ' ') 30 DAYOFWEEK (1-7) DAYOFWEEK(DATE ' ') 7 MOD MOD(10,3) 1 EXP FLOOR HOUR (e) INT INT(6.4321) 6 LENGTH LENGTH('ABC') 3 MONTH MONTH(DATE ' ') 1 LN LOG MAX MAX(66,89) 89 MIN MIN(66,89) 66 MINUTE NUMVAL PI RADIANS pi NUMVAL('123') 123 ROUND ROUND( ,0) 123 ROUND( ,2) ROUND( ,-2) 100 SECOND SIGN SIN SQRT TAN YEAR YEAR(DATE ' ') 2019

33 2 33 CURDATE CURRENT_DATE CURTIME CURRENT_TIME CURTIMESTAMP CURRENT_TIMESTAMP TIMESTAMPVAL TIMESTAMPVAL(' :00:00') DATE TODAY 2019/11/21 DATE() DATEVAL DATEVAL(' ') DATE() SQL CURRENT_DATE

34 2 34 CASE WHEN CASE input_exp value_exp CASE input_exp {WHEN value_exp THEN result...}[else result] END SELECT Invoice_ID, CASE Company_Name WHEN 'Exports UK' THEN 'Exports UK Found' WHEN 'Home Furniture Suppliers' THEN 'Home Furniture Suppliers Found' ELSE 'Neither Exports UK nor Home Furniture Suppliers' END, Salesperson_ID FROM Sales_Data CASE WHEN true CASE {WHEN boolean_exp THEN result...}[else result] END SELECT Invoice_ID, Amount, CASE WHEN Amount > 3000 THEN 'Above 3000' WHEN Amount < 1000 THEN 'Below 3000' ELSE 'Between 1000 and 3000' END, Salesperson_ID FROM Sales_Data COALESCE NULL SELECT Salesperson_ID, COALESCE(Sales_Manager, Salesperson) FROM Salespeople NULLIF NULL SELECT Invoice_ID, NULLIF(Amount, -1), Salesperson_ID FROM Sales_Data

35 2 35 FileMaker FileMaker SQL FileMaker FileMaker FileMaker_Tables FileMaker_Fields ODBC SQLTables JDBC DatabaseMetaData gettables ExecuteSQL FileMaker_Tables FileMaker_Tables FileMaker FileMaker_Tables 1 TableName - 1 TableId - ID 1 BaseTableName - 1 BaseFileName - FileMaker 1 ModCount - SELECT TableName FROM FileMaker_Tables WHERE TableName LIKE 'Sales%' FileMaker_Fields FileMaker_Fields FileMaker FileMaker_Fields 1 TableName - 1 FieldName - 1 FieldType - SQL 1 FieldId - ID 1 FieldClass - Summary Calculated Normal 1 FieldReps - 1 ModCount - SELECT * FROM FileMaker_Fields WHERE TableName='Sales'

36 2 36 FileMaker FileMaker FileMaker ODBC SQLSpecialColumns JDBC DatabaseMetaData getversioncolumns ExecuteSQL ROWID ROWID ID FileMaker Pro Get() ROWMODID ROWMODID FileMaker Pro Get( ) SELECT ROWID, ROWMODID FROM MyTable WHERE ROWMODID > 3

37 2 37 SQL DEC create table t ("dec" numeric) ABSOLUTE ACTION ADD ALL ALLOCATE ALTER AND ANY ARE AS ASC ASSERTION AT AUTHORIZATION AVG BEGIN BETWEEN BINARY BIT BIT_LENGTH BLOB BOOLEAN BOTH BY CASCADE CASCADED CASE CAST CATALOG CHAR CHARACTER CHARACTER_LENGTH CHAR_LENGTH CHECK CHR CLOSE COALESCE COLLATE COLLATION COLUMN COMMIT CONNECT CONNECTION CONSTRAINT CONSTRAINTS CONTINUE CONVERT CORRESPONDING COUNT CREATE CROSS CURDATE CURRENT CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CURTIME CURTIMESTAMP DATE DATEVAL DAY DAYNAME DAYOFWEEK DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFERRABLE DEFERRED DELETE DESC DESCRIBE DESCRIPTOR DIAGNOSTICS DISCONNECT DISTINCT DOMAIN DOUBLE DROP ELSE END

38 2 38 END_EXEC ESCAPE EVERY EXCEPT EXCEPTION EXEC EXECUTE EXISTS EXTERNAL EXTRACT FALSE FETCH FIRST FLOAT FOR FOREIGN FOUND FROM FULL GET GLOBAL GO GOTO GRANT GROUP HAVING HOUR IDENTITY IMMEDIATE IN INDEX INDICATOR INITIALLY INNER INPUT INSENSITIVE INSERT INT INTEGER INTERSECT INTERVAL INTO IS ISOLATION JOIN KEY LANGUAGE LAST LEADING LEFT LENGTH LEVEL LIKE LOCAL LONGVARBINARY LOWER LTRIM MATCH MAX MIN MINUTE MODULE MONTH MONTHNAME NAMES NATIONAL NATURAL NCHAR NEXT NO NOT NULL NULLIF NUMERIC NUMVAL OCTET_LENGTH OF OFFSET ON ONLY OPEN OPTION OR ORDER OUTER OUTPUT OVERLAPS PAD PART PARTIAL PERCENT POSITION PRECISION PREPARE PRESERVE PRIMARY PRIOR PRIVILEGES PROCEDURE PUBLIC READ REAL REFERENCES RELATIVE RESTRICT REVOKE RIGHT ROLLBACK ROUND ROW ROWID ROWS RTRIM SCHEMA

39 2 39 SCROLL SECOND SECTION SELECT SESSION SESSION_USER SET SIZE SMALLINT SOME SPACE SQL SQLCODE SQLERROR SQLSTATE STRVAL SUBSTRING SUM SYSTEM_USER TABLE TEMPORARY THEN TIES TIME TIMESTAMP TIMESTAMPVAL TIMEVAL TIMEZONE_HOUR TIMEZONE_MINUTE TO TODAY TRAILING TRANSACTION TRANSLATE TRANSLATION TRIM TRUE TRUNCATE UNION UNIQUE UNKNOWN UPDATE UPPER USAGE USER USERNAME USING VALUE VALUES VARBINARY VARCHAR VARYING VIEW WHEN WHENEVER WHERE WITH WORK WRITE YEAR ZONE

40 A ABS 32 ALL 26 ALTER TABLE (SQL ) 22 AND 27 ANY 26 ATAN 32 ATAN2 32 B BaseFileName 35 BaseTableName 35 BETWEEN 26 BLOB, SELECT 15 8, 9 7 C CASE WHEN 34 CAST 16 CEIL 32 CEILING 32 CHR 30 COALESCE 34 CREATE INDEX (SQL ) 22 CREATE TABLE (SQL ) 20 CURDATE 33 CURRENT_DATE 33 CURRENT_TIME 33 CURRENT_TIMESTAMP 33 CURRENT_USER 30 CURTIME 33 CURTIMESTAMP 33 D DATE 33 DATEVAL 33 DAY 32 DAYNAME 30 DAYOFWEEK 32 DEFAULT (SQL ) 20 DEG 32 DEGREES 32 DELETE (SQL ) 17 DISTINCT 8 DROP INDEX (SQL ) E ExecuteSQL 6 EXISTS 26 EXP 32 EXTERNAL (SQL ) 21, SELECT 15 F FETCH FIRST (SQL ) 14 FieldClass 35 FieldId 35 FieldName 35 FieldReps 35 FieldType 35 FileMaker_Fields 35 FileMaker_Tables 35 FLOOR 32 FOR UPDATE (SQL ) 14 FROM (SQL ) 9 FULL OUTER JOIN 10 G GetAs 16 GROUP BY (SQL ) 11, SQL 37 H HAVING (SQL ) 12 HOUR 32 J JDBC 7 Unicode 7 I IN 26 INNER JOIN 10 INSERT (SQL ) 17 INT 32 IS NOT NULL 26 IS NULL 26

41 41 K 25 18, SELECT 15 L LEFT 30 LEFT OUTER JOIN 10 LENGTH 32 LIKE 26 LN 32 LOG 32 LOWER 30 LTRIM M MAX 32 MIN 32 MINUTE 32 MOD 32 ModCount 35 MONTH 32 MONTHNAME 30 7 N NOT IN 26 NOT LIKE 26 NOT NULL (SQL ) 21 NOT 27 NULLIF 34 NUMVAL 32 O ODBC 7 ODBC 7 Unicode 7 ODBC 14 OFFSET (SQL ) 13 OR 27 ORDER BY (SQL ) 13 OUTER JOIN 10 P PI 32 PREVENT INDEX CREATION 23 PutAs 18, R RADIANS 32 RIGHT 30 RIGHT OUTER JOIN 10 ROWID 36 ROWMODID 36 ROUND 32 RTRIM CREATE TABLE 21 INSERT 18 PutAs 18 SELECT 16 UPDATE 19 S SECOND 32 SELECT (SQL ) 8 BLOB SIGN 32 SIN 32 SPACE 30 SQL SQL 23 SQL 26 SQL 28 SQL 25 SQL 27 SQL 25

42 42 SQL 25 SQL 28 SQL 25 SQL 23 SQL 25 SQL 7 SQL 28 SQL ALTER TABLE 22 CREATE INDEX 22 CREATE TABLE 20 DELETE 17 DROP INDEX 23 INSERT 17 SELECT 8 TRUNCATE TABLE 21 UPDATE SQL 23 SQL 28 SQL_C_WCHAR 7 SQL-92 7 SQRT 32 STRVAL 30 SUBSTR 30 SUBSTRING X 35 Y YEAR SQL 37 Z 17 17, T TableId 35 TableName 35 TAN 32 TIME 30 TIMESTAMPVAL 33 TIMEVAL 30 TODAY 33 TRIM 30 TRUNCATE TABLE (SQL ) 21 W VALUES (SQL ) 17 WHERE (SQL ) 11 WITH TIES (SQL ) 14 Unicode 7 UNION (SQL ) 12 UNIQUE (SQL ) 21 UPDATE (SQL ) 19 UPPER 30 USERNAME 30