FileMaker 16 SQL
2013-2017 FileMaker, Inc.. FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker, FileMaker Go FileMaker, Inc.. FileMaker WebDirect FileMaker Cloud FileMaker, Inc... FileMaker. FileMaker. FilmMaker.,, URL,, URL.. URL. FileMaker, Inc.. (http://www.filemaker.com/kr/). : 01
1 소개 5 5 SQL 5 FileMaker 5 ExecuteSQL 6 2 지원표준 7 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 23 23 23 / 25 25 25 25 26 27 28
4 SQL 28 28 30 32 33 34 FileMaker 35 FileMaker 35 FileMaker 36 SQL 37 인덱스 40
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 FileMaker Pro ODBC JDBC. SQL 1 FileMaker Pro, ODBC JDBC SQL.. 1 FileMaker Pro Advanced, "FileMaker Pro" FileMaker Pro FileMaker Pro Advanced. SQL Structured Query Language. SELECT., SQL,. SQL.. FileMaker SQL 2, " ". FileMaker FileMaker ODBC JDBC, FileMaker ODBC JDBC. FileMaker FileMaker, ODBC JDBC SQL FileMaker. FileMaker ODBC JDBC FileMaker ODBC JDBC. FileMaker ODBC JDBC SQL.
1 6 ExecuteSQL FileMaker Pro ExecuteSQL.. ExecuteSQL. ExecuteSQL ExecuteSQL. ExecuteSQL. ExecuteSQL. ExecuteSQL 8 "SELECT ". SELECT. ExecuteSQL ({}) SQL-92 ISO. ExecuteSQL ODBC/JDBC,. ExecuteSQL FileMaker Pro.
2 FileMaker ODBC JDBC ODBC JDBC FileMaker. FileMaker FileMaker Pro FileMaker Server. 1 ODBC ODBC 3.0 1. 1 JDBC JDBC 3.0. 1 ODBC JDBC SQL-92 SQL-92. SQL ODBC JDBC API., ASCII ( ). SQL_C_WCHAR. 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.
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
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
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.
2 11 WHERE WHERE. WHERE. WHERE expr1 rel_operator expr2 expr1 expr2,. rel_operator. 2,000,000. SELECT last_name,first_name FROM emp WHERE salary >= 2000000 WHERE. WHERE expr1 IS NULL WHERE NOT expr2 SELECT(), WHERE. GROUP BY GROUP BY... GROUP BY columns GROUP BY FROM. columns FROM... SELECT dept_id, SUM (salary) FROM emp GROUP BY dept_id ID. ID.
2 12 HAVING HAVING ( : 2,000,000 ).. HAVING expr1 rel_operator expr2 expr1 expr2,. SELECT. rel_operator. 2,000,000. SELECT dept_id, SUM (salary) FROM emp GROUP BY dept_id HAVING SUM (salary) > 2000000 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
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. FileMaker Pro. OFFSET FETCH FIRST OFFSET FETCH FIRST. " ". OFFSET. OFFSET SELECT 0. FETCH FIRST OFFSET 1. OFFSET FETCH FIRST SELECT, OFFSET. OFFSET FETCH FIRST. OFFSET OFFSET. OFFSET n {ROWS ROW} ] n. n,. ROWS ROW.
2 14 FETCH FIRST FETCH FIRST. FETCH FIRST [ n [ PERCENT ] ] { ROWS ROW } {ONLY WITH TIES } ] n. n 1. PERCENT n 1. n PERCENT,. ROWS ROW. WITH TIES ORDER BY. WITH TIES FETCH, ORDER BY. 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 2,000,000. SELECT * FROM emp WHERE salary > 2000000 FOR UPDATE OF last_name, first_name, salary...
2 15 BLOB a Wildcard * SQL SELECT 'CatDog' FROM Salespeople SELECT 999 FROM Salespeople SELECT DATE '2019-06-05' FROM Salespeople SELECT TIME '02:49:03' FROM Salespeople SELECT TIMESTAMP '2019-06-05 02: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() ".
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, SQL_VARCHAR SELECT. 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
2 17 'GIFf' 'JPEG' 'TIFF' 'PDF' 'PNGf' GIF(Graphics Interchange Format) Raster PDF(Portable Document Format) 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')..
2 18. INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date) VALUES ('Smith', 'John', 'E22345', 27500, DATE '2019-06-05') INSERT. emp. 5. Null. 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. INSERT SELECT.,,.. SELECT INSERT INSERT/UPDATE ODBC JDBC. SELECT INSERT. SELECT.
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() ".
2 20 CREATE TABLE CREATE TABLE. CREATE TABLE. CREATE TABLE table_name ( table_element_list [, table_element_list...] ). 1 table_name. table_name 100..., ( ). 1 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 1~32000. 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 TIMESTAMP,. TIMESTAMP(6). VARCHAR CHARACTER VARYING,. VARCHAR(255) 1 DEFAULT. expr,. USER, USERNAME, CURRENT_USER, CURRENT_DATE, CURDATE, CURRENT_TIME, CURTIME, CURRENT_TIMESTAMP, CURTIMESTAMP NULL.
2 21 1 UNIQUE FileMaker. 1 NOT NULL FileMaker. FileMaker Pro. 1 BLOB, VARBINARY BINARY VARYING field_type. 1 EXTERNAL. relative_path_string FileMaker. FileMaker Pro. SECURE OPEN. calc_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 WHERE TRUNCATE TABLE. TRUNCATE TABLE. table_name.. TRUNCATE TABLE., FileMaker 301(" ").
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
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.. 1 1 1 / 1 1 1 1 1 1 calc Sales_Data.Invoice_ID.., PRICE * 1.05 1.05. 30 Number_Of_Days_In_June. (')., ( : 'Don''t').
2 24 ODBC JDBC, FileMaker ({}) ODBC/JDBC,. 1 {D '2019-06-05'} 1 {T '14:35:10'} 1 {TS '2019-06-05 14: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 '2019-06-05' { D '2019-06-05' } {06/05/2019} {06/05/19} ODBC/JDBC SQL-92. TIME '14:35:10' { T '14:35:10' } {14:35:10} TIMESTAMP '2019-06-05 14:35:10' { TS '2019-06-05 14:35:10'} {06/05/2019 14:35:10} {06/05/19 14:35:10} : 4 FileMaker. ODBC/JDBC SQL-92..,.
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 '2019-01-30'. + hire_date + 5 DATE '2019-02-04' - hire_date - DATE '2019-01-01' 29 hire_date - 10 DATE '2019-01-20' SELECT Date_Sold, Date_Sold + 30 AS agg FROM Sales_Data SELECT Date_Sold, Date_Sold - 30 AS agg FROM Sales_Data
2 26 = <> > >= < <= LIKE NOT LIKE IS NULL IS NOT NULL BETWEEN IN NOT IN EXISTS ANY ALL Null Null 'True' ( =, <>, >, >=, < <= ), = ( =, <>, >, >=, < <= )
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 = 40000 AND exempt = 1 NOT. NOT (salary = 40000 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'
2 28.... 1 Unary '-', Unary '+' 2 ^, ** 3 *, / 4 +, - 5 =, <>, <, <=, >, >=, Like, Not Like, Is Null, Is Not Null, Between, In, Exists, Any, All 6 Not 7 AND 8 OR WHERE salary > 40000 OR hire_date > (DATE '2008-01-30') AND dept = 'D101' AND 2008 1 30 D101 40,000.,. WHERE (salary > 40000 OR hire_date > DATE '2008-01-30') AND dept = 'D101' 40,000 2008 1 30 D101. SQL FileMaker SQL.,,,.. SELECT ( : AVG(SALARY)) ( : AVG(SALARY * 1.07)). DISTINCT.
2 29 COUNT (DISTINCT last_name). SUM AVG COUNT MAX MIN., SUM(SALARY).., AVG(SALARY).., COUNT(NAME). COUNT, COUNT null. COUNT(*), null.., 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("Expressions involving aggregations are not supported")., SUM ROUND. SELECT ROUND(SUM(Salary), 0) FROM Payroll.. SELECT SUM(ROUND(Salary, 0)) FROM Payroll
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 '2019-12-25') '2019-12-25' TIME TIMEVAL USERNAME USER ID 9:49, TIME() 21:49:00 TIME(). SQL CURRENT_TIME.
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
2 32 ABS ATAN ATAN2 CEIL CEILING DEG DEGREES x y DAY DAY(DATE '2019-01-30') 30 DAYOFWEEK (1-7) DAYOFWEEK(DATE '2004-05-01') 7 MOD MOD(10,3) 1 EXP FLOOR HOUR (e) INT Int(6.4321) 6 LENGTH LENGTH('ABC') 3 MONTH MONTH(DATE '2019-01-30') 1 LN LOG MAX MAX(66,89) 89 MIN MIN(66,89) 66 MINUTE NUMVAL PI RADIANS. pi NUMVAL('123') 123 ROUND ROUND(123.456,0) 123 ROUND(123.456,2) 123.46 ROUND(123.456,-2) 100 SECOND SIGN SIN : -1, 0 0, 1
2 33 SQRT TAN YEAR YEAR(DATE '2019-01-30') 2019 CURDATE CURRENT_DATE CURTIME CURRENT_TIME CURTIMESTAMP CURRENT_TIMESTAMP TIMESTAMPVAL TIMESTAMPVAL('2019-01-30 14:00:00'). DATE TODAY 11/21/2019, DATE() 2019-11-21 DATEVAL DATEVAL('2019-01-30') 2019-01-30 DATE(). SQL CURRENT_DATE.
2 34 CASE WHEN CASE input_exp value_exp. CASE input_exp {WHEN value_exp THEN result...} [ELSE result] END CASE WHEN true. CASE {WHEN boolean_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 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
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'
2 36 FileMaker FileMaker () FileMaker ( ). ODBC, SQLSpecialColumns. JDBC, DatabaseMetaData getversioncolumns. ExecuteSQL. ROWID ROWID ID. FileMaker Pro Get( ID). ROWMODID ROWMODID. FileMaker Pro Get( ). SELECT ROWID, ROWMODID FROM MyTable WHERE ROWMODID > 3
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
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
2 39 ROWS RTRIM SCHEMA 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
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 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 ) 23 E ExecuteSQL 6 EXISTS 26 EXP 32 EXTERNAL(SQL ) 21 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 H HAVING(SQL ) 12 HOUR 32 I IN 26 INNER JOIN 10 INSERT(SQL ) 17 INT 32 IS NOT NULL 26 IS NULL 26 J JDBC 7 7 L LEFT OUTER JOIN 10 LEFT 30 LENGTH 32
41 LIKE 26 LN 32 LOG 32 LOWER 30 LTRIM 30 M MAX 32 MIN 32 MINUTE 32 MOD 32 ModCount 35 MONTH 32 MONTHNAME 30 N NOT IN 26 NOT LIKE 26 NOT NULL(SQL ) 21 NOT 27 null 18 NULLIF 34 NUMVAL 32 O ODBC 7 7 ODBC 7 ODBC 14 OFFSET(SQL ) 13 OR 27 ORDER BY(SQL ) 13 OUTER JOIN 10 P PI 32 PREVENT INDEX CREATION 23 PutAs 18, 19 R RADIANS 32 RIGHT OUTER JOIN 10 RIGHT 30 ROUND 32 ROWID 36 ROWMODID 36 RTRIM 30 S SECOND 32 SELECT(SQL ) 8 BLOB 15 15 15 SIGN 32 SIN 32 SPACE 30 SQL ALTER TABLE 22 CREATE INDEX 22 CREATE TABLE 20 DELETE 17 DROP INDEX 23 INSERT 17 SELECT 8 TRUNCATE TABLE 21 UPDATE 19 37 7 SQL 28 SQL 7 SQL 23 26 25 27 25 23 25 28 25 23 28 SQL 25 SQL 26 SQL 25 SQL 27 SQL 25 SQL 23 SQL 25 SQL 28 SQL 25 SQL 23 SQL 28 SQL_C_WCHAR 7 SQL-92 7 SQL 28 SQL 23 SQRT 33 STRVAL 30 SUBSTR 30 SUBSTRING 30
42 T TableId 35 TableName 35 TAN 33 TIME 30 TIMESTAMPVAL 33 TIMEVAL 30 TODAY 33 TRIM 30 TRUNCATE TABLE (SQL ) 21 U UNION(SQL ) 12 UNIQUE(SQL ) 21 UPDATE(SQL ) 19 UPPER 30 USERNAME 30 V VALUES(SQL ) 17 W WHERE(SQL ) 11 WITH TIES(SQL ) 14 Y YEAR 33 37 24 10 8 18 SQL 37 7, SELECT 15 13 CREATE TABLE 21 INSERT 18 PutAs 18 SELECT 16 UPDATE 19 21, SQL 37 24 8, 9 7 7 14 17, 20 17 14 30 25, SELECT 15 24 35