Questions tagged [sql-standard]

For questions concerning the SQL Standard itself (also known as the ANSI or ISO SQL Standard) including writing queries that conform to that standard. This tag is NOT to be used for questions about SQL Server Standard Edition.

Filter by
Sorted by
Tagged with
0
votes
1answer
239 views

Drastic change in query speed when changing from Oracle 9i to ANSI outer join syntax

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed ...
2
votes
1answer
42 views

Does the ANSI SQL Standard allow filters or parameters in JOIN conditions?

Does the ANSI Standard find the following SQL sinppet syntactically correct? I am interested specifically in the last line: SELECT name, dept_name FROM employee JOIN department ON employee....
2
votes
1answer
142 views

Does the SQL-1992 standard restrict naming identifiers to 18 characters?

In this draft of SQL:1992, I found… Section 5.2 Leveling Rules .... The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) No <...
4
votes
2answers
3k views

In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?

I understand the distinction between: Scalar functions Set-Returning-Functions (SRF)s Internal functions Window functions Aggregate functions of all sorts User-Implemented functions (which in ...
0
votes
0answers
40 views

DBCC Command Not being run by current user

I have a stored Proc that runs a DBCC command CHECKIDENT The sproc is being run by a user with adequate permissions. however, it is failing on the DBCC command with the error. User 'DifferentUser' ...
28
votes
2answers
64k views

CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard?

Just came across a weird issue, whereby depending on how I create an index, an index name is required. http://dev.mysql.com/doc/refman/5.5/en/create-index.html http://dev.mysql.com/doc/refman/5.5/en/...
0
votes
1answer
16 views

How can I reference column aliases from a projection inside the projection?

Calculations on the projection (select) values are useful and common. Yet column aliases declared in the projection can't be used there. select sellprice as x, cost as y, x/y as markup from ...
2
votes
1answer
333 views

What is the order when SELECT-ing unordered set from an ordered SELECT?

What does the SQL standard and its mainstream DB implementations (SQL Server, Oracle, DB2, PostgreSQL, Firebird, MySQL, etc.) say about the final order in the following general case: SELECT * FROM (...
1
vote
1answer
25 views

Why pad instead of trim when comparing strings?

The ANSI/SQL-92 standard states that strings have to be padded to the same length when compared: 3) The comparison of two character strings is determined as fol- lows: a) If the ...
0
votes
0answers
19 views

MySQL and SQL 99

Is MySQL SQL 99 compatible? I've been searching on the net in vain. I found, for example, the expressions SQL 1999, SQL 999 but not the answer to my question.
1
vote
0answers
24 views

Why can you specify CASCADE after DROP but not after DELETE? [closed]

What is the reasoning behind the design that you can specify CASCADE after a DROP query (DML) like DROP users CASCADE; but not in a DELETE query (DDL) like DELETE FROM users WHERE user_id = 2 CASCADE;?...
0
votes
1answer
48 views

What are some technical reasons for not including N-ary multiplication in the SQL standard? [closed]

If I want to add up two values in a query, I can use the + operator. If I want to sum up all values returned by a query, I can use SUM. If I want to multiply two values in a query, I can use the * ...
4
votes
1answer
73 views

When is the AS keyword required?

In the following statement (PostgreSQL 11): => SELECT c cost FROM tt; ERROR: syntax error at or near "cost" LINE 1: SELECT c cost FROM tt; I get an error. Adding parenthesis around the field ...
4
votes
1answer
65 views

Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql

In mysql 5.6, Consider these 2 examples creating relationships between A, B, C and D. Example 1 CREATE TABLE `a` ( id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE ...
5
votes
2answers
4k views

SET ANSI_NULLS at the database level versus setting it at the table level

I have generated schema-only script for my sql server 2008 R2, here is part of it:- USE [master] GO /****** Object: Database [****] Script Date: 03/25/2015 12:17:09 ******/ CREATE DATABASE [****] ...
0
votes
1answer
42 views

Recursive CTE throws temp tablespace is empty error

I need to aggregate the contents of multiple rows into a row as a delimited text. Here's the simplified table with sample data which represents what i want to do. CREATE TABLE SPD_OWNER.EMP ( ...
1
vote
1answer
46 views

ORDER BY usage in subquery (derived table) SQL standard allowed or not

MariaDB documentation is mentioning that ORDER BY clause in subqueries (derived table) are never allowed by SQL standards. Meaning the SQL query SELECT field1 , field2 FROM ( SELECT ...
2
votes
1answer
435 views

Is an anomaly possible if a serializable transaction interfers with another isolation level?

Let's assume transaction A runs in SERIALIZABLE and transaction B runs in a more permissive isolation level, like READ UNCOMMITTED. Can Transaction A now suffer from any anomaly introduced by ...
0
votes
2answers
163 views

Can any database table name support double quotes "?

I need to know whether any database supports double quotes as a part of their table name. I am especially interested in JDBC compliant databases. And if they do, can I please know how?
0
votes
0answers
40 views

Why information_schema.columns is_nullable is varchar? [duplicate]

Looking for all columns "not null" is very useful sometimes, right? So, I'm thinking on "why on earth the 'is_nullable' columns from information_schema.columns is varchar, not boolean?" On first ...
3
votes
1answer
1k views

Ansi SQL: Auto Numbered Column

Is there an ANSI standard for auto-numbered columns. Currently, we have a choice of SERIAL, AUTOINCREMENT, AUTO_INCREMENT, IDENTITY() and good old NEXTVAL() among others. I read somewhere that there ...
108
votes
11answers
92k views

SQL: SELECT All columns except some

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table. Something like: SELECT * -...
9
votes
3answers
2k views

Is the keyword “ALIAS” actually used?

According to PostgreSQL 7.1 through 9.1 (now unsupported), ALIAS is listed as a reserved word, at least for SQL-99. Later versions do not show it - suggesting that it has been dropped as a reserved ...
2
votes
1answer
1k views

Does MariaDB or MySQL implement the VALUES (expression) table value constructor?

I am just wondering if MariaDB or MySQL implements the <table value constructor> in the SQL Spec. In SQL Server and PostgreSQL this is done with standardized VALUES (expression)? SELECT * FROM (...
8
votes
4answers
1k views

Best practices to maximize portability in SQL Server 2016

When it comes to developing the prototype of a solution, often the technologies has not been decided yet and might not be the same that will be used in the finished product. In this scenarios I tend ...
0
votes
1answer
128 views

Does the SQL Standard mention trigger events on DDL (ALTER, CREATE, DROP)?

PostgreSQL supports "Event Triggers" which is defined as, To supplement the trigger mechanism discussed in Chapter 39, PostgreSQL also provides event triggers. Unlike regular triggers, which are ...
0
votes
1answer
46 views

Are null values allowed in any column of a table in SQL?

I have doubts over null values.Therefore can you please explain me briefly what is a null value in sql and can we use null values at any place in sql tables?
4
votes
3answers
477 views

What actually is a SQL clause?

This may sound like an overly simple question but I'm not finding it easy to locate a proper answer. To the question "What are sql clauses?" most of the resources on internet simply provide a list ...
4
votes
2answers
4k views

Difference between “<> ANY(…)” and “NOT = ANY(…)”?

Could you please explain the difference between these two operators? sID != smth and not sID = smth. At a first glance they seem perfectly equal. But they give different results.
7
votes
1answer
76 views

the <set clause>'s <multiple column assignment>

I just know a little sql basics and need to write a plain sql syntax highlighter/checker So I'm fighting through the standard... I stumbled over somthing I'm not sure I understand it right, as I never ...
1
vote
1answer
83 views

Result of a query in SQL

Let us say that we have to write a query for: Find the distinct names of all students with percent>90 in courseno=107 given three relations: Students (rolno:integer,sname:string) Courses (...
0
votes
2answers
4k views

Delete row if table exists

I want to delete a row from a table if that table exists. Below is the delete statement DELETE FROM UM_CLAIM_BEHAVIOR WHERE UM_TENANT_ID = ? Can I extend this query to check the existence of the ...
4
votes
1answer
153 views

According to the SQL92 spec, can I store the value 1 in a field where the precision = scale?

Is it SQL92 compliant to store the number 1 in a field that is defined as NUMERIC 3,3? What about DECIMAL 3,3? Does this mean that the precision is a floating decimal place or static that must ...
6
votes
1answer
160 views

Are there any fully declarative implementations of SQL

I was recently bitten by a bug ( in my code ) where these two queries had drastically different runtimes: select * from smalltable st inner join bigtable bt on st.btid = bt.btid select * from ...
2
votes
4answers
988 views

What computations can NOT be performed in standard SQL? [closed]

I know loops cannot be expressed in standard SQL. What other forms of processing can it not perform?
2
votes
1answer
31 views

ANSI SQL for links

Oracle provides a "link" mechanism in order to access a different database instance than the one on which the query is running. DIM_DATES@OTHERDB Is there any ANSI SQL standard way to code this?
4
votes
1answer
88 views

How locks in Postgres behave when quitting the JVM abnormally

I have a table 'car', as follows Table "public.car" Column | Type | Collation | Nullable | Default ----------+--------+-----------+----------+--------- car_no | bigint | ...
2
votes
2answers
3k views

Databases: are there universal datatypes?

While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in ...
0
votes
0answers
43 views

Standard behaviour in executing multiple alter statements in Postgres

I was just experimenting with ALTER TABLE commands in psql. I came across a fact that we can give multiple alter clause within same ALTER TABLE command. Like ALTER TABLE EmployeeLoans add col1, add ...
3
votes
1answer
627 views

Order of Execution of Set clause in Update Query (Postgres)

I recently came across this strange behaviour in Postgres. I had a table like the following one: sasdb=# \d emp_manager_rel Table "db3004db.emp_manager_rel" Column | Type | ...
0
votes
1answer
633 views

Updating Multiple Tables with single Update query [duplicate]

I have two tables TableA, TableB as follows. Table "db3004db.tableb" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- empid | integer | ...
5
votes
2answers
2k views

Is Microsoft SQL Server 2016 fully ANSI SQL-92 compliant?

I am trying to find a compliance confirmation for MS SQL 2016 - if it is fully compliant with ANSI SQL-92 starndard. I found this article on Microsoft Docs which states it is not, but it refers to ...
2
votes
2answers
238 views

Creating same unique constraint with different names

In MySQL 5.7 alter table mydb.roles add constraint uk1 unique (role_name); alter table mydb.roles add constraint uk2 unique (role_name); The 2nd alter command threw me a warning stating: ...
0
votes
2answers
90 views

What is the F301: the CORRESPONDING clause in query expression?

The SQL 2011 Spec supports a CORRESPONDING clause, Feature F301, “CORRESPONDING in query expressions”: What is this feature? How is it used? And, is it supported by any industry RDBMs?
-2
votes
1answer
1k views

What is a “clob” or Character Large Object?

I see in this post on listagg — Rows to Delimited Strings talking about SQL's LISTAGG() clause ON OVERFLOW The return type of listagg is either varchar or clob with an implementation defined length ...
2
votes
1answer
255 views

What is the SQL Standard method of doing LIMIT OFFSET?

I know LIMIT and OFFSET are pretty common but unstandardized methods to page through a result set. I know there is a standardized method, but I can never find this when I'm going to look it up, The ...
1
vote
2answers
193 views

Are duplicates in Read Commited standard behaviour?

This refers to this question: Duplicate records returned from table with no duplicates The "read commited" isolation level assumes that one transaction could read same record differently, but it does ...
2
votes
1answer
776 views

How do I do a JOIN in ANSI SQL 89 syntax?

In Oracle, I use SELECT * FROM table1...JOIN... where the dots represent either the type of join or the condition on which to be joined. Is this ANSI 89 syntax? If not, then how would I perform a ...
2
votes
1answer
437 views

Is INSERT INTO … SET standard SQL?

I'm just wondering if this syntax is standard or where it comes from? For example INSERT INTO gtest3 SET gDateTime='2017-07-16 01:00:00' You can see it documented in MySQL but PostgreSQL does not ...
6
votes
1answer
504 views

Where can I find the SQL-89 standard?

This question is different but similar to this request looking for SQL-86. SQL 1989 is almost 30 years old. Is this spec freely available anywhere. The standard does not have a Wikipage or the like? ...