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.
103
questions
9
votes
1answer
740 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?
...
1
vote
1answer
83 views
Does SQL/MED (ISO/IEC 9075-9) defines a writable foreign data wrapper interface
I just can't find anything about it.
I don't have the paper or draft of the SQL:2011, and have almost only drafts from SQL:2008, 2006, 2003.
Does anyone know if "SQL/MED (ISO/IEC 9075-9)" defines a ...
0
votes
1answer
38 views
What is the history of the SQL Server ANSI (ISO) compatibility settings?
I know what these settings do and when to use them, what I'd really like to know is why this problem even exists.
Recently, I came across an ugly situation--make that, yet another ugly situation--...
0
votes
1answer
253 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 ...
0
votes
0answers
9 views
How do I group by Quarters in Google big query/dbt?
I have a table with 6 columns and I want to group them by quarters of the year, so far this is my code
SELECT
DATE_TRUNC(date(timestamp_column), QUARTER),
Column_2,
Column_3,
Column_4,
MAX(column_4)...
1
vote
0answers
30 views
Composed primary keys : “should” duplicates and null values be accepted, and according to which norm?
I just passed some SQL test including a question about the possibility to have duplicates in a primary key column.
The expected answer was NO. Mine is, "Yes, if the column is part of a composed ...
7
votes
1answer
2k views
Which DBMS are more standard compliant? [closed]
I find it frustrating that the same queries don't work in different DBMS.
Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such ...
2
votes
1answer
167 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 <...
0
votes
1answer
383 views
Is the double dollar sign ($$) quotation method in SQL standard?
In PostgreSQL, strings can be quoted very flexibly using a pair of dollar signs ($) with a tag in between. For example,
SELECT $f$ Hi $f$;
Is this quoting mechanism a part of any SQL standard, or ...
1
vote
1answer
26 views
How can I group together rankings by number of users?
I have a table which includes the following:
ID evar event_date ranking
1 landing 2019-01-01 1
1 content 2019-01-02 2
1 homepage 2019-01-03 3
2 ...
0
votes
1answer
23 views
Correct table quoting
I want to generically use any SQL database(I'm using jdbc, it works if the driver and the URL are provided).
It seems, that different DBMS use different quoting for table names.
For example, I have ...
0
votes
1answer
54 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 ...
0
votes
2answers
59 views
Same query, different result (depending on user's permissions)
A question about SQL in general:
If two users execute the same sql statement, is it possible that user1 gets different results than user2?
Imagine user1 has more permissions than user2.
With other ...
125
votes
11answers
111k 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 * -...
20
votes
3answers
739 views
What is the correct result for this query?
I came across this puzzle in the comments here
CREATE TABLE r (b INT);
SELECT 1 FROM r HAVING 1=1;
SQL Server and PostgreSQL return 1 row.
MySQL and Oracle return zero rows.
Which is correct? ...
2
votes
1answer
123 views
How do I convert a BIGINT to a DATETIME in Informix 12.1
I'm new to Informix, and I'm trying to convert a bigint to a datetime.
SELECT sdatetime
FROM CallDetail;
Results:
sdatetime
----------
1572509662678
1572518550704
1572519033540
This seems to be the ...
0
votes
1answer
790 views
ANSI Joins in Oracle
We are doing a large ETL load from Oracle 12c. Every now and then Oracle optimizer decides to create a new execution plan for a SQL statement that has previously been running just fine. Usually this ...
6
votes
2answers
682 views
Does the spec permit column-count mismatch on INSERT?
I'm just wondering if the spec permits excluding the column-list clause in an INSERT statement if you're inserting in fewer columns than the table provides. I see PostgreSQL does, and MySQL and SQL ...
2
votes
1answer
45 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....
6
votes
2answers
5k 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 ...
Erwin Brandstetter
119k16 gold badges267 silver badges410 bronze badges
0
votes
0answers
59 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' ...
30
votes
2answers
67k 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/...
2
votes
1answer
489 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
29 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 ...
1
vote
0answers
25 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
50 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
84 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
120 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 ...
6
votes
2answers
5k 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
71 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
283 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
503 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
519 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?
4
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 ...
a_horse_with_no_name
56.3k11 gold badges109 silver badges139 bronze badges
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
2k 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
181 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
47 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?
7
votes
3answers
3k 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
6k 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
85 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
85 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
6k 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
154 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
161 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
1k 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
39 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
186 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 | ...
5
votes
2answers
5k 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 ...
