SQL compliance

From Wikipedia, the free encyclopedia

Generally, compliance means adhering to a rule, such as a policy, standard, specification, or law. So, SQL Compliance means adhering to rules laid for SQL. Some standards and protocols related to SQL are mentioned in this.

Function Description Firebird IBM DB2 Oracle Database Microsoft SQL MonetDB MySQL PostgreSQL SQLite Apache Ignite Mimer SQL NexusDB
E011 Numeric data types Partial Yes Yes Un­known Yes Un­known Yes Un­known Partial Yes Yes
E011-01 INTEGER and SMALLINT data types (including all spellings) Yes Yes Yes Un­known Yes Yes Yes Un­known Yes Yes Yes
E011-02 REAL, DOUBLE PRECISION, and FLOAT data types Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E011-03 DECIMAL and NUMERIC data types Partial Yes Yes Un­known Yes Un­known Yes Un­known Partial Yes Yes
E011-04 Arithmetic operators Yes Yes Yes Un­known Yes Un­known Yes Un­known Partial Yes Yes
E011-05 Numeric comparison Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E011-06 Implicit casting among the numeric data types Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E021 Character string types Yes Yes Partial Partial Partial Un­known Partial Un­known Partial Yes Yes
E021-01 CHARACTER data type (including all its spellings) Yes Yes Yes Un­known Yes Un­known Yes Un­known Partial Yes Yes
E021-02 CHARACTER VARYING data type (including all its spellings) Yes Yes Partial Un­known Yes Un­known Yes Un­known Partial Yes Yes
E021-03 Character literals Yes Yes Partial Un­known Yes Un­known Yes Un­known Yes Yes Yes
E021-04 CHARACTER_LENGTH function Yes Yes No No Yes Yes Partial[note 1] No Yes Yes Yes
E021-05 OCTET_LENGTH function Yes Yes No No Yes Yes Yes No[note 2] Yes Yes Yes
E021-06 SUBSTRING function Yes Yes No No Yes Yes Yes No Yes Yes Yes
E021-07 Character concatenation Yes Yes Yes No Yes No Yes Yes Yes Yes Yes
E021-08 UPPER and LOWER functions Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E021-09 TRIM function Yes Yes Yes No[note 3][1] Yes Yes Yes No Yes Yes Yes
E021-10 Implicit casting among the fixed-length and variable-length character string types Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E021-11 POSITION function Yes Yes No[note 4] No[note 5][2] No[note 6] Yes Yes No Yes Yes Yes
E021-12 Character comparison Yes Yes Partial Un­known Yes Un­known Yes Un­known Yes Yes Yes
E031 Identifiers Yes Yes Partial Un­known Yes Un­known Yes Un­known Yes Yes Yes
E031-01 Delimited identifiers Yes Yes Yes Yes Yes Un­known Yes Yes Yes Yes Yes
E031-02 Lower case identifiers Yes Yes Un­known Un­known Yes Un­known Yes Un­known Yes Yes Yes
E031-03 Trailing underscore Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E051 Basic query specification Partial Yes Partial Un­known Yes Partial Yes Un­known Partial Yes Yes
E051-01 SELECT DISTINCT Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E051-02 GROUP BY clause Yes Yes Yes Yes Yes Yes Yes Yes Partial Yes Yes
E051-04 GROUP BY can contain columns not in <select-list> Yes Yes Yes Un­known Yes No Yes Un­known Yes Yes Yes
E051-05 Select list items can be renamed Yes Yes Yes Yes Yes Yes Yes Un­known Yes Yes Yes
E051-06 HAVING clause Yes Yes Yes Yes Yes Yes Yes Un­known Yes Yes Yes
E051-07 Qualified * in select list Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E051-08 Correlation names in the FROM clause Yes Yes Partial Un­known Yes Un­known Yes Un­known Yes Yes Yes
E051-09 Rename columns in the FROM clause No Yes Un­known Un­known Yes Un­known Yes Un­known No Yes Yes
E061 Basic predicates and search conditions Yes Partial Partial Un­known Yes Un­known Yes Un­known Partial Yes Yes
E061-01 Comparison predicate Yes Yes Partial Un­known Yes Un­known Yes Un­known Yes Yes Yes
E061-02 BETWEEN predicate Yes Yes Partial Yes Yes Yes Yes Un­known Yes Yes Yes
E061-03 IN predicate with list of values Yes Yes Partial Yes Yes Yes Yes Un­known Yes Yes Yes
E061-04 LIKE predicate Yes Yes Partial Yes Yes Yes Yes Un­known Partial Yes Yes
E061-05 LIKE predicate: ESCAPE clause Yes Yes Partial Un­known Yes Un­known Yes Un­known Partial Yes Yes
E061-06 NULL predicate Yes Yes Partial Un­known Yes Un­known Yes Un­known Yes Yes Yes
E061-07 Quantified comparison predicate Yes Yes Partial Un­known Yes Un­known Yes Un­known Partial Yes Yes
E061-08 EXISTS predicate Yes Yes Partial Yes Yes Yes Yes Un­known Yes Yes Yes
E061-09 Subqueries in comparison predicate Yes Yes Partial Un­known Yes Un­known Yes Un­known Yes Yes Yes
E061-11 Subqueries in IN predicate Yes Yes Partial Yes Yes Yes Yes Un­known Yes Yes Yes
E061-12 Subqueries in quantified comparison predicate Yes Yes Partial Un­known Yes Un­known Yes Un­known No Yes Yes
E061-13 Correlated subqueries Yes Yes Partial Yes Yes Un­known Yes Un­known Yes Yes Yes
E061-14 Search condition Yes Partial[note 7] Partial Un­known Yes Un­known Yes Un­known Yes Yes Yes
E071 Basic query expressions Partial Yes Partial Un­known Yes Un­known Yes Un­known Yes Yes Yes
E071-01 UNION DISTINCT table operator Yes Yes[note 8] Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E071-02 UNION ALL table operator Yes Yes Yes Yes Yes Yes Yes Un­known Yes Yes Yes
E071-03 EXCEPT DISTINCT table operator No Yes[note 8] No[note 9] Un­known Yes Un­known Yes Un­known Yes Yes Yes
E071-05 Columns combined via table operators need not have exactly the same data type Yes Yes Yes Yes Yes Un­known Yes Un­known Yes Yes Yes
E071-06 Table operators in subqueries Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E081 Basic Privileges Yes Yes Partial Un­known Partial Un­known Yes Un­known Un­known Yes No
E081-01 SELECT privilege at the table level Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes No
E081-02 DELETE privilege Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes No
E081-03 INSERT privilege at the table level Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes No
E081-04 UPDATE privilege at the table level Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes No
E081-05 UPDATE privilege at the column level Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes No
E081-06 REFERENCES privilege at the table level Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes No
E081-07 REFERENCES privilege at the column level Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes No
E081-08 WITH GRANT OPTION Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes No
E081-09 USAGE privilege Yes Yes No Un­known No Un­known Yes Un­known Un­known Yes No
E081-10 EXECUTE privilege Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes No
E091 Set functions Yes Yes Yes Un­known Yes Un­known Yes Un­known Partial Yes Yes
E091-01 AVG Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E091-02 COUNT Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E091-03 MAX Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E091-04 MIN Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E091-05 SUM Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E091-06 ALL quantifier Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E091-07 DISTINCT quantifier Yes Yes Yes Yes Yes Yes Yes Un­known Yes Yes Yes
E101 Basic data manipulation Yes Yes Yes Un­known Yes Un­known Yes Partial Partial Yes Yes
E101-01 INSERT statement Yes Yes Yes Yes Yes Yes Yes Un­known Partial Yes Yes
E101-03 Searched UPDATE statement Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E101-04 Searched DELETE statement Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E111 Single row SELECT statement Yes Yes Yes Un­known Yes Un­known Yes Un­known Un­known Yes Yes
E121 Basic cursor support Partial Yes Partial Un­known No Un­known Yes Partial No Yes Yes
E121-01 DECLARE CURSOR Yes Yes Partial Un­known No Un­known Yes No No Yes Yes
E121-02 ORDER BY columns need not be in select list Yes Yes Yes Yes Yes Yes Yes Yes[note 10] No Yes Yes
E121-03 Value expressions in ORDER BY clause Yes Yes Yes Yes Yes Yes Yes Un­known No Yes Yes
E121-04 OPEN statement Yes Yes Yes Un­known No Un­known Yes Un­known No Yes Yes
E121-06 Positioned UPDATE statement Yes Yes Yes Un­known No Un­known Yes Un­known No Yes No
E121-07 Positioned DELETE statement Yes Yes Yes Un­known No Un­known Yes Un­known No Yes No
E121-08 CLOSE statement Yes Yes Yes Un­known No Un­known Yes Un­known No Yes Yes
E121-10 FETCH statement: implicit NEXT Yes Yes Partial Un­known No Un­known Yes Un­known No Yes Yes
E121-17 WITH HOLD cursors No Yes Un­known Un­known No Un­known Yes Un­known No Yes No
E131 Null value support (nulls in lieu of values) Yes Yes Un­known Yes Yes Yes Yes Un­known Yes Yes Yes
E141 Basic integrity constraints Partial Yes Yes Un­known Partial Un­known Yes Partial Partial Yes Yes
E141-01 NOT NULL constraints Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E141-02 UNIQUE constraints of NOT NULL columns Yes Yes Yes Un­known Yes Un­known Yes Yes No Yes Yes
E141-03 PRIMARY KEY constraints Yes Yes Yes Yes Yes Yes Yes Yes[note 11] Partial Yes Yes
E141-04 Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action Yes Yes Yes Un­known Yes Un­known Yes Un­known No Yes Yes
E141-06 CHECK constraints Yes Yes Yes Un­known No Un­known Yes Un­known No Yes Yes
E141-07 Column defaults Yes Yes Yes Yes Yes Yes Yes Un­known No Yes Yes
E141-08 NOT NULL inferred on PRIMARY KEY Yes Yes Yes Un­known Yes Un­known Yes Partial[note 12] Partial Yes Yes
E141-10 Names in a foreign key can be specified in any order No Yes Yes Un­known No Un­known Yes Un­known No Yes Yes
E151 Transaction support Partial Partial Yes Yes Yes Un­known Yes Partial No Yes Yes
E151-01 COMMIT statement Yes Yes Yes Yes Yes Un­known Yes Yes No Yes Yes
E151-02 ROLLBACK statement Yes Yes Yes Yes Yes Un­known Yes Yes No Yes Yes
E152 Basic SET TRANSACTION statement Partial Yes Yes Un­known Yes Un­known Yes No No Yes Partial
E152-01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause No Partial[note 13] Yes Un­known Yes Un­known Yes Un­known No Yes Partial
E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses Yes No Yes Un­known Yes Un­known Yes Un­known No Yes Partial
E* Other Partial Partial Partial Un­known Yes Un­known Partial Un­known Un­known Yes Partial
E153 Updatable queries with subqueries Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes
E161 SQL comments using leading double minus Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
E171 SQLSTATE support Partial Yes Yes Un­known Yes Un­known Yes Un­known Partial Yes No
E182 Host language binding (previously "Module Language") No Partial[note 14] Un­known Un­known Yes Un­known No Un­known No Yes
F021 Basic information schema No Partial No Yes No Yes[note 15] Yes No No Yes
F021-01 COLUMNS view No Partial[note 16] No Yes No Yes[note 17] Yes No[note 18] No Yes
F021-02 TABLES view No Partial[note 16] No Yes No Yes[note 17] Yes No[note 18] No Yes
F021-03 VIEWS view No Partial[note 16] No Yes No Yes[note 17] Yes No[note 18] No Yes
F021-04 TABLE_CONSTRAINTS view No Partial[note 16] No Yes No Yes Yes No No Yes
F021-05 REFERENTIAL_CONSTRAINTS view No Partial[note 16] No Yes No Yes Yes No No Yes
F021-06 CHECK_CONSTRAINTS view No Partial[note 16] No Yes No Yes Yes No No Yes
F031 Basic schema manipulation Partial Partial Partial Un­known Partial Un­known Yes Partial Partial Yes
F031-01 CREATE TABLE statement to create persistent base tables Yes Yes Yes Yes Yes Yes Yes Un­known Yes Yes
F031-02 CREATE VIEW statement Yes Yes Yes Yes Yes Un­known Yes Yes No Yes
F031-03 GRANT statement Yes Yes Yes Un­known Yes Un­known Yes No No Yes
F031-04 ALTER TABLE statement: ADD COLUMN clause Yes Yes Un­known Un­known Yes Yes Yes Un­known Yes Yes
F031-13 DROP TABLE statement: RESTRICT clause Partial Yes No Un­known Yes Un­known Yes Un­known No Yes
F031-16 DROP VIEW statement: RESTRICT clause Un­known Yes No Un­known Yes Un­known Yes Un­known No Yes
F031-19 REVOKE statement: RESTRICT clause Partial No No Un­known No Un­known Yes Un­known No Yes
F041 Basic joined table Yes Yes Yes Un­known Yes Un­known Yes Partial Yes Yes
F041-01 Inner join (but not necessarily the INNER keyword) Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
F041-02 INNER keyword Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
F041-03 LEFT OUTER JOIN Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
F041-04 RIGHT OUTER JOIN Yes Yes Yes Yes Yes Yes Yes No Yes Yes
F041-05 Outer joins can be nested Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes
F041-07 The inner table in a left or right outer join can also be used in an inner join Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes
F041-08 All comparison operators are supported (rather than just =) Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes
F051 Basic date and time Partial Partial Yes Un­known Yes Un­known Yes Partial Partial Yes
F051-01 DATE data type (including support of DATE literal) Yes Yes Yes No Yes Yes Yes No Partial Yes
F051-02 TIME data type (including support of TIME literal) with fractional seconds precision of at least 0 Yes Yes No Un­known Yes Un­known Yes Un­known Partial Yes
F051-03 TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 Yes Yes Yes Un­known Yes Un­known Yes Un­known Partial Yes
F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data types Yes Yes Yes Un­known Yes Un­known Yes Un­known Yes Yes
F051-05 Explicit CAST between datetime types and character string types Yes Yes Yes Un­known Yes Un­known Yes Partial Yes Yes
F051-06 CURRENT_DATE Yes Yes Yes Un­known Yes Yes Yes Yes Yes Yes
F051-07 LOCALTIME Partial No No Un­known Yes Yes Yes No Yes Yes
F051-08 LOCALTIMESTAMP Partial No Yes Un­known Yes Yes Yes No Yes Yes
F081 UNION and EXCEPT in views Partial Yes Un­known Yes Yes Un­known Yes Yes No Yes
F131 Grouped operations Yes Un­known Yes Un­known Yes Un­known Yes Un­known No Yes
F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views Yes Un­known Yes Un­known Yes Un­known Yes Un­known No Yes
F131-02 Multiple tables supported in queries with grouped views Yes Un­known Yes Un­known Yes Un­known Yes Un­known No Yes
F131-03 Set functions supported in queries with grouped views Yes Un­known Yes Un­known Yes Un­known Yes Un­known No Yes
F131-04 Subqueries with GROUP BY and HAVING clauses and grouped views Yes Un­known Yes Un­known Yes Un­known Yes Un­known No Yes
F131-05 Single row SELECT with GROUP BY and HAVING clauses and grouped views Yes Un­known Yes Un­known Yes Un­known Yes Un­known No Yes
F* Other Partial Un­known Partial Un­known Yes Un­known Partial Un­known Partial Yes
F181 Multiple module support No Un­known Yes Un­known Yes Un­known No Un­known No Yes
F201 CAST function Yes Un­known Yes Un­known Yes Yes Yes Yes Yes Yes
F221 Explicit defaults No Un­known Un­known Un­known Yes Un­known Yes Un­known Yes Yes
F261 CASE expression Yes Un­known Yes Yes Yes Yes Yes Un­known Yes Yes
F261-01 Simple CASE Yes Un­known Yes Un­known Yes Un­known Yes Un­known Yes Yes
F261-02 Searched CASE Yes Un­known Yes Un­known Yes Un­known Yes Un­known Yes Yes
F261-03 NULLIF Yes Un­known Yes Un­known Yes Un­known Yes Un­known Yes Yes
F261-04 COALESCE Yes Un­known Yes Yes Yes Yes Yes Un­known Yes Yes
F311 Schema definition statement No Un­known Yes Un­known Partial Un­known Yes Partial No Yes
F311-01 CREATE SCHEMA No Un­known Yes Un­known Yes Un­known Yes No No Yes
F311-02 CREATE TABLE for persistent base tables No Un­known Yes Yes Yes Yes Yes Un­known No Yes
F311-03 CREATE VIEW No Un­known Yes Yes Yes Yes Yes Yes No Yes
F311-04 CREATE VIEW: WITH CHECK OPTION No Un­known Yes Un­known No Un­known Yes Un­known No Yes
F311-05 GRANT statement No Un­known Yes Un­known Yes Un­known Yes No No Yes
F471 Scalar subquery values Yes Un­known Yes Un­known Yes Un­known Yes Yes Yes Yes
F481 Expanded NULL predicate Yes Un­known Yes Un­known Yes Un­known Yes Un­known Yes Yes
F501 Features and conformance views Un­known Un­known No No No Un­known Partial Un­known No Yes
F501-01 SQL_FEATURES view No No No No No No Yes No No Yes
F501-02 SQL_SIZING view Un­known Un­known No No No Un­known Yes No No Yes
F501-03 SQL_LANGUAGES view Un­known Un­known No No No Un­known Yes No No Yes
F812 Basic flagging No Un­known SQL-92 Un­known No Un­known No Un­known No Yes
S011 Distinct data types No Un­known Un­known Un­known No Un­known No Un­known No Yes
S011-01 USER_DEFINED_TYPES view Un­known Un­known Un­known Un­known No Un­known No Un­known No Yes
T321 Basic SQL-invoked routines Partial Un­known Partial Un­known Partial Un­known Partial Un­known No Yes
T321-01 User-defined functions with no overloading Yes Un­known Un­known Yes Yes Yes Yes Un­known No Yes
T321-02 User-defined stored procedures with no overloading Yes Un­known Un­known Yes Yes Un­known Yes Un­known No Yes
T321-03 Function invocation Yes Un­known Yes Yes Yes Yes Yes Un­known No Yes
T321-04 CALL statement Partial Un­known Yes Un­known Yes Un­known Yes No No Yes
T321-05 RETURN statement Partial Un­known Partial[note 19] Un­known Yes Un­known No No No Yes
T321-06 ROUTINES view Un­known Un­known No[note 20] Un­known No[note 21] Un­known Yes Un­known No Yes
T321-07 PARAMETERS view Un­known Un­known No[note 22] Un­known No[note 23] Un­known Yes Un­known No Yes
T631 IN predicate with one list element Yes Un­known Yes Un­known Yes Un­known Yes Un­known No Yes

Footnotes[]

  1. ^ Trims trailing spaces from CHARACTER values before counting
  2. ^ There is no built-in function by this name, although the number of octets in a string can be determined by using LENGTH(CAST(X AS BLOB)).
  3. ^ There are LTRIM and RTRIM functions for equivalent functionality.
  4. ^ There is INSTR.
  5. ^ There is the CHARINDEX function for equivalent functionality.
  6. ^ There is the charindex function for equivalent functionality.
  7. ^ Lacks support for [IS [NOT] TRUE|FALSE|UNKNOWN]
  8. ^ a b Without DISTINCT keyword
  9. ^ Use MINUS instead of EXCEPT DISTINCT
  10. ^ Except compound queries.
  11. ^ Only WITHOUT ROWID tables have real primary keys other than the rowid (INTEGER PRIMARY KEY)
  12. ^ Only for WITHOUT ROWID tables.
  13. ^ SET [CURRENT] ISOLATION used instead of SET TRANSACTION
  14. ^ Supports embedded language features but not the specific MODULE syntax
  15. ^ https://dev.mysql.com/doc/refman/5.7/en/information-schema.html
  16. ^ a b c d e f Included in SYSIBM schema
  17. ^ a b c Also includes MySQL-specific extension columns
  18. ^ a b c You can use PRAGMA for obtaining this information, and can create an information schema based on this, but it is not build in.
  19. ^ Oracle supports the following subfeature in PL/SQL but not in Oracle SQL.
  20. ^ Use the ALL PROCEDURES metadata view.
  21. ^ Use the sys.functions metadata view.
  22. ^ Use the ALL_ARGUMENTS and ALL_METHOD_PARAMS metadata views.
  23. ^ Use the sys.args metadata view.

See also[]

References[]

  1. ^ "[MS-TSQLISO02]: E021-09, TRIM function". docs.microsoft.com. Retrieved 22 April 2020.
  2. ^ "[MS-TSQLISO02]: E021-11, POSITION function". docs.microsoft.com. Retrieved 22 April 2020.
Retrieved from ""