SQLish, a vaguely SQL-like syntax for querying an RM database
SQLish has been moved to a Google Doc.
I undertook a similar exercise in my free download book SQL: A Comparative Survey. Mine was based entirely on the international standard for SQL, which has been revised a couple of times since I wrote it but I doubt whether those revisions would affect anything significantly.
Hugh
Quote from johnwcowan on June 26, 2019, 11:20 pmIf you are allergic to SQL (or to me), read this at your own risk. I am not responsible for runny noses, itchy rashes, or anaphylactic shock.
My main reaction (not a new one) is how bloated is SQL's syntax (and I think you've left out a few), with so many subtly different ways of saying the same-but-subtly-different thing. That is, "bloated" compared to Tutorial D; and I regard that as verbose compared to the more algebraic languages I prefer.
A couple of notes on specific points
1)
SELECT[DISTINCT] expr-listFROMtable-expr, table-expr ...These keywords are always used together. Project, rename, remove, extend, summarize. The elements of the expr-list can be as follows:
*: all attributes not otherwise mentioned in thisSELECT; no order implied
But I think there is some order, because if your SELECT is an operand to UNION, the columns from the two operands get matched up in sequential position, not by name. This is so confusing you'll usually see on help forums: a) don't use SELECT * but always name the columns wanted; b) don't use UNION -- oh, also because of the UNION ALL nonsense.
Note: If multiple relational expressions are given after FROM, they are joined.
No, they're cartesian-multiplied. Potentially leading to duplicate column names.
4) rel-expr
HAVINGboolean-expr:Also restrict but with a different operator precedence.
And with the nonsense that AS names introduced in the SELECT can't be used in the HAVING: you have to repeat the full aggregate expression. Grr.
5) rel-expr
UNION|INTERSECTION|MINUS|SEMIMINUSrel-expr:What it says on the tin.
Also in the standard is EXCEPT, except (hah!) most vendors haven't implemented it. (Roughly) equivalent to Tutorial D NOT MATCHING. For SEMIMINUS, I can never remember exactly what it means. Is it the same as ANTIJOIN or ANTISEMIJOIN?
SQLish has been moved to a Google Doc.
SQLish has been moved to a Google Doc.
Quote from johnwcowan on June 27, 2019, 2:27 pm...
DIVIDEis a great big mess and I'm leaving it out of SQLish altogether.
Ah, I didn't realise you were concocting another language. Then giving syntax (and syntax that looks like SQL) without semantics (where the semantics is different to SQL) seems like an exercise in self-flagellation. Why would anyone want to use a language as ugly as SQL? I use SQL only because my employer mandates it.
Re DIVIDE (and I see your separate thread on REMAINDER, and wrt your other question on usual/minimal set of operators), IMO you need only one grouping-type operator: GROUP, per Tutorial D. That gives you RVAs. With them you can do all the whole-set comparisons needed for the umpteen variants of DIVIDE, and you can control the corner cases of empty sets.
Quote from AntC on June 27, 2019, 11:01 pm
Ah, I didn't realise you were concocting another language.
Well, yes and no; SQLish is an alternative syntax for an existing language, as Elixir is a Pythonish syntax for Erlang, or Perligata is a Latinate syntax for Perl (where you write a bonos tum malos tum Foedum pugnatoribus da. and mean @gunslingers = (@good,@bad,@Ugly);
Then giving syntax (and syntax that looks like SQL) without semantics (where the semantics is different to SQL) seems like an exercise in self-flagellation. Why would anyone want to use a language as ugly as SQL?
A matter of taste and familiarity. If you love the Prologesque syntax of Erlang and detest Python, you're not going to like Elixir either, and if your Latin breaks Priscian's head, Perligata 'the thoroughly bound one (f.)' will not charm you. As far as familiarity goes, almost everyone is infinitely more familiar with SQL than with Tutorial D, and so SQLish will have a gentler learning curve, even if the semantics are not quite what people expect. With Tutorial D, you have to learn both the RM and Tutorial D syntax at the same time.
Re
DIVIDE(and I see your separate thread onREMAINDER, and wrt your other question on usual/minimal set of operators), IMO you need only one grouping-type operator:GROUP, per Tutorial D. That gives you RVAs. With them you can do all the whole-set comparisons needed for the umpteen variants ofDIVIDE, and you can control the corner cases of empty sets.
I'm aware. What I mean is that I'm not going to provide special syntax for division in SQLish.
Quote from johnwcowan on June 28, 2019, 12:23 amQuote from AntC on June 27, 2019, 11:01 pmAh, I didn't realise you were concocting another language.
Well, yes and no; SQLish is an alternative syntax for an existing language, as Elixir is a Pythonish syntax for Erlang, or Perligata is a Latinate syntax for Perl (where you write
a bonos tum malos tum Foedum pugnatoribus da.and mean @gunslingers = (@good,@bad,@Ugly);Then giving syntax (and syntax that looks like SQL) without semantics (where the semantics is different to SQL) seems like an exercise in self-flagellation. Why would anyone want to use a language as ugly as SQL?
A matter of taste and familiarity. If you love the Prologesque syntax of Erlang and detest Python, you're not going to like Elixir either, and if your Latin breaks Priscian's head, Perligata 'the thoroughly bound one (f.)' will not charm you. As far as familiarity goes, almost everyone is infinitely more familiar with SQL than with Tutorial D, and so SQLish will have a gentler learning curve, even if the semantics are not quite what people expect. With Tutorial D, you have to learn both the RM and Tutorial D syntax at the same time.
Speaking from experience of teaching databases at university level and from communicating with others who have done so, students usually learn SQL, Tutorial D, and the relational model roughly concurrently.
But students usually have some experience of Python, or Java, C#, C or (sometimes) C++ from at least one first-year course/module/unit -- plus whatever they're doing concurrently or prior in the same (usually 2nd) year, though they may have done some purely practical database work (e.g., in MS Access) in first year. As such, and as a fairly conventional and thus relatively semantically-familiar imperative language, Tutorial D seems to be easier to grasp than SQL in terms of appreciating relational theory and the relational operators.
SQL obscures; Tutorial D exposes.
A pure relational SQL-like would be a great way for SQL experts to explore a truly-relational variant, but I think it may confuse students who are equally new to SQL, Tutorial D, and the relational model -- particularly as database students are also probably learning NoSQL, historical database models, and whatever else the course/module/unit might cover, like DBMS internals or query optimisation, all at essentially the same time. Clear and categorical distinctions -- both semantic and syntactic -- are helpful.
Quote from Dave Voorhis on June 28, 2019, 9:23 amSpeaking from experience of teaching databases at university level and from communicating with others who have done so, students usually learn SQL, Tutorial D, and the relational model roughly concurrently.
Huh. Do most CS students (or database students, whatever) worldwide actually learn Tutorial D? I'm not only completely out of touch with universities, I don't have a CS degree myself. (If I wanted one back in the day, I'd have to have done at least two years of EE, which held zero appeal for me, being the burn-my-fingers-on-the-soldering-iron type.)
Any courses that cover the relational model and use Date's An Introduction to Database Systems as a primary text will at least mention Tutorial D, though in some cases it's to tell students to ignore the Tutorial D bits.
Otherwise, they'll actively use Rel or other tools, if they choose to do practical exercises around the RM. Many don't, covering the RM in a purely theoretical fashion and covering SQL (mainly) for the practical side.