For my link archive:
- [WayBack] GitHub – ulid/spec: The canonical spec for ulid
- [WayBack] GitHub – rs/xid: xid is a globally unique id generator thought for the web
–jeroen
Posted by jpluimers on 2020/12/15
For my link archive:
–jeroen
Posted in Database Development, Development, PostgreSQL, Software Development | Leave a Comment »
Posted by jpluimers on 2019/10/30
I could not find any vendors/architectures have public database servers.
So there is no good way to go beyond SQLFiddle (of which I wrote before in SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions and David Rodriguez: a few nice posts on SQL (via: Google+)), that does not provide database access, but allows you to fire SQL statements onto these architectures:
I get the thing (it is very hard to secure an “over the internet” connection to a database server; do NOT do this: [WayBack] connectivity – Connect to SQL Server over Internet – Database Administrators Stack Exchange), so the alternative is to run locally.
If you run locally, there are plenty of example/demo database, like:
- download AdventureWorks-oltp-install-script.zip and extract the zip file to the C:\Samples\AdventureWorks folder.
- Open
C:\Samples\AdventureWorks\instawdb.sql
in SQL Server Management Studio and follow the instructions at the top of the file.
In step 2, do not forget to set the query window to SQLCMD
syntax. Or just run the script using the [WayBack] sqlcmd Utility | Microsoft Docs (which reminds me I should update SQL Sever: batch files to find instances and sqlcmd.exe/osql.exe)
–jeroen
Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »
Posted by jpluimers on 2019/08/29
Via [WayBack] SQL select only rows with max value on a column, I bumped into http://sqlfiddle.com/#!9/a6c585/1:
Application for testing and sharing SQL queries.
Source: [WayBack] SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.
It is a cool site, currently supporting these SQL back-ends:
You can host it yourself using [WayBack] GitHub – zzzprojects/sqlfiddle2: New version of SQL Fiddle based on OpenIDM (in the past it was [WayBack] GitHub – zzzprojects/sqlfiddle)
Other resources for learning and playing around with SQL:
–jeroen
Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, Software Development, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2019/01/08
There was a nice discussion on [WayBack] What RDBMS do you use when doing location-aware things?More specifically, I am looking into a simple app with use of GPS… – Andrea Raimondi – Google+
TL;DR: use PostGIS on top of PostgresSQL.
–jeroen
Posted in Database Development, Development, PostGIS | Leave a Comment »
Posted by jpluimers on 2018/06/26
I need to write up some notes, but there are some links that will help me:
It’s a question of readability. There is no difference in performance.
Old versions of SQL Server were silly enough to look up meta data, but not any more.SELECT foo FROM bar WHERE EXISTS (SELECT * FROM baz WHERE baz.id = bar.id); SELECT foo FROM bar WHERE EXISTS (SELECT 1 FROM baz WHERE baz.id = bar.id);
I am not considering NULL or “fun variants” which don’t seem intuitive to me.
SELECT foo FROM bar WHERE EXISTS (SELECT NULL FROM baz WHERE baz.id = bar.id);
SELECT foo FROM bar WHERE EXISTS (SELECT 1/0 FROM baz WHERE baz.id = bar.id);
The question popped up in comments just now. I researched the manuals of the most popular RDBMS:
- MS SQL seems to favor
SELECT *
in the manual.- The example in the PostgreSQL 9.4 manual uses
SELECT 1
.- Oracle 11g has
SELECT *
in the language reference.- MySQL 5.7 has
SELECT *
in the reference manual but alsoSELECT 1
in the comments.- SQLite has no example in the language reference.
A search on SO for
code:"EXISTS (SELECT 1"
yields 5,048 results.
A search on SO forcode:"EXISTS (SELECT *"
yields 5,154 results.
Updated links and counts 07.2015.So
SELECT *
has the popular vote and the big commercial RDBMS on its side.
I findSELECT 1
more intuitive. It’s like saying “if at least one exists”.
IsSELECT *
more intuitive?
–jeroen
Posted in Database Development, Development, Firebird, InterBase, MySQL, PostgreSQL, SQL, SQL Server | Leave a Comment »