Here are some examples of database query languages that have at least one working implementation, for comparison:
See ExperimentalQueryLanguageComparison for examples in potential future languages.
Example 1: Retrieve all columns/attributes and rows/tuples from a table/RelVar
SQL:
SELECT * FROM myThings
Dataphor:
select myThings;
Tutorial D:
myThings
Datalog:
?- myThings(A,B,C,Name) // browse the results
myThings(A,B,C,Name) // if used in another query
Example 2: Restriction
SQL:
SELECT * FROM myThings WHERE name = 'blah'
Dataphor:
select myThings WHERE name = 'blah'
Tutorial D:
myThings WHERE name = 'blah'
Datalog:
?- myThings(A,B,C,blah)
Example 3: Restriction and projection with unique result
SQL:
SELECT DISTINCT a, b, c FROM myThings WHERE name = 'blah'
DISTINCT is needed unless a, b, c constitute a key of myThings.
Dataphor:
select myThings over {a, b, c}, WHERE name = 'blah'
Tutorial D:
(myThings WHERE name = 'blah') {a, b, c},
Datalog:
query(A,B,C) :- myThings(A,B,C,blah)
?- query(A,B,C)
Example 3b: Restriction and projection without required uniqueness
SQL:
SELECT a, b, c FROM myThings WHERE name = 'blah'
Example 4: Aggregation
SQL:
SELECT snumber, COUNT(*) AS p_count FROM sp GROUP BY snumber
Dataphor:
select sp group by {snumber}, add { Count() p_count },;
Tutorial D:
SUMMARIZE sp BY {snumber}, ADD (COUNT() AS p_count)
Datalog:
support for mutual recursion precludes internal support for aggregation;
aggregate would need to be in higher language layer w/ scoped queries
Example 5: Insertion
SQL:
INSERT INTO myThings(a, b, c, name) VALUES
(1, 2, 3, 'glub'),
(4, 5, 6, 'glob');
Dataphor:
insert table {
row {a 1, b 2, c 3, name 'glub'},,
row {4, 5, 6,'glob'},,
}, into myThings
Tutorial D:
INSERT myThings RELATION {
TUPLE {a 1, b 2, c 3, name 'glub'},,
TUPLE {a 4, b 5, c 6, name 'glob'},
},
Datalog:
myThings(1,2,3,glub).
myThings(4,5,6,glob).
Example 6: Table/relvar creation
SQL:
CREATE TABLE myThings (
name TEXT NOT NULL PRIMARY KEY,
a INTEGER,
b INTEGER,
c INTEGER
)
Dataphor:
create table Employee
{
name : String,
a: Integer,
b: Integer,
c: Integer,
key { name},
},;
Tutorial D:
VAR myThings REAL RELATION {
name CHAR,
a INTEGER,
b INTEGER,
c INTEGER
}, KEY {name},
Datalog:
add first entry to 'myThings' when ready
Cannot specify keys.
Example 7: Transitive closure or recursion - "all paths" of a digraph
Datalog:
path(A,B) :- edge(A,B).
path(A,B) :- edge(A,C),path(C,B).
?- path(A,B).
Tutorial D:
TCLOSE edges // presumes degree 2 and matching types
Example 8: Natural equi-join
SQL:
SELECT foo, bar
FROM red NATURAL JOIN blue
Tutorial D:
(red JOIN blue) {foo, bar},
Datalog:
no 'natural' join
Example 9: Equi-join
SQL:
SELECT foo, bar
FROM red, blue
WHERE x = y
--
-- Variation 2 --
--
SELECT foo, bar
FROM red INNER JOIN blue
ON x = y
Tutorial D:
JOIN {red RENAME (x AS j), blue RENAME (y AS j)}, {foo, bar},
Datalog:
query(Foo,Bar) :- red(X,Foo),blue(X,Bar)
?- query(A,B)
Example 10: Equi-join with same-named key
SQL
SELECT foo, bar
FROM red INNER JOIN blue
ON red.x = blue.x
Tutorial D:
(red JOIN blue) {foo, bar},
Datalog:
attributes positional in standard Datalog
Example 11: Top 3 largest planets, use name if tie
SQL (Microsoft Dialect)
SELECT TOP 3 * FROM planets
ORDER BY diameter DESCENDING, name
Tutorial D:
WITH (result := planets ORDER(DESC diameter, DESC name)) :
WRITELN result[0] || result[1] || result[2];
.
-
Question: why not show the array manipulation portion for a fuller comparison? It also doesn't appear to address the "tie" requirements. (Dot is a work-around to a wiki formatting bug.)
-
Answer: The ARRAY manipulation is entirely dependent on what you intend to do with the results. A future update will provide built-in operators for slicing and dicing ARRAYs. Accessing ARRAY elements is via conventional indexing, e.g., p[n] where p is an ARRAY and n is a 0-based index. I have addressed the "tie" requirements.
-
Thanks for the tie adjustment. As far as the array, some kind of basic display ("print") or console output is probably sufficient for this context in my opinion. I would accept displaying just the planet name even though the other examples display the whole row because the requirements are ambiguous on that.
-
Done.
-
Thank You.
Added some DataLog comparisons above. At the moment, Datalog can operate as a query language excepting some functional transforms and a variety of aggregation operations (group by, count, min, max, top N, etc.). Datalog would need some tooling to serve as a data definition or manipulation language (mutations aren't supported, nor is defining tables with keys or enforcing constraints between relations).
In Datalog, aggregations and functions on infinite domains must be lifted into a higher layer - they compete with transitive closures and recursive queries in a manner such that having both features in the same layer leads readily to 'infinite' results sets. I favor lifting them into a 'functional' layer with scoped queries that simply return all the constructed relations. I might eventually explain all this on another page...
These examples would be informative, and more amenable to proof-reading, if they operated on a defined schema.
Good point. As time permits, I'll endeavour to change the above to use either Oracle's familiar DEPT & EMP or ChrisDate's classic SupplierPartsDatabase, etc., and lead off with the table/relvar definitions followed by the 'insert' examples. I'll change the SQL, Dataphor, Tutorial D, and possibly SMEQL (it seems intuitive) examples, but I'll leave the Datalog -- with which I have least familiarity -- examples to their original author.
Chapter 15 (http://web.cecs.pdx.edu/~maier/TheoryBook/MAIER/C15.pdf) of TheTheoryOfRelationalDatabases compares the way to write queries in many (now obsolete?) (pseudo?) relational query languages: ISBL, QUEL, QBE and PIQUE
See: TutorialDee, DataLog, AlphoraDataphor, StructuredQueryLanguage, TqlRoadmap (aka SMEQL), ExperimentalQueryLanguageDiscussion, HowOtherQueryLanguagesAddressSqlFlaws
CategoryQueryLanguage, CategoryProgrammingLanguageComparisons