ChatGPT is a powerful tool for acquiring new skills productively while also reinforcing existing skills. For instance, learning and mastering declarative query languages can be quite a challenging endeavor due to the disconnects that can exist across reference guides, query examples, and verifiable query solutions.
In this post, I provide a ChatGPT transcript comprising prompts (questions denoted as “Q:”) and responses (answers) that enhance a cheatsheet approach to learning and reinforcing existing knowledge of query languages such as SQL and SPARQL, courtesy of live hyperlinks constructed using Linked Data Principles.
Shortcuts
- SQL Cheatsheet enhanced by live hyperlinks
- SPARQL Cheatsheet enhanced by live hyperlinks
- SPARQL executed from SQL (SPASQL) Example
Transcript
ChatGPT 3.5 is less capable that version 4.0, but much faster. Here’s a transcript demonstrating the creation of SQL and SPARQL cheatsheets.
SQL Cheatsheet Generation
Q: Make a simple SQL Cheatsheet.
Q: Redo, but using a Table for enhanced presentation.
Q: Redo the SQL cheatsheet using a Markdown Table in a code-block.
Sure, here is the SQL cheatsheet presented in a markdown table inside a code block:
Command | Description |
---|---|
SELECT | Retrieves data from one or more tables |
SELECT column1, column2, ... FROM table_name; |
|
DISTINCT | Retrieves unique values |
SELECT DISTINCT column_name FROM table_name; |
|
WHERE | Filters records based on a condition |
SELECT column1, column2, ... FROM table_name WHERE condition; |
|
AND/OR | Combines multiple conditions |
SELECT column1, column2, ... FROM table_name WHERE condition1 AND/OR condition2; |
|
ORDER BY | Sorts the result set |
SELECT column1, column2, ... FROM table_name ORDER BY column_name ASC/DESC; |
|
LIMIT | Limits the number of results returned |
SELECT column1, column2, ... FROM table_name LIMIT number_of_rows; |
|
JOIN | Combines rows from two or more tables based on a related column between them |
SELECT column1, column2, ... FROM table1 JOIN table2 ON table1.column_name = table2.column_name; |
|
GROUP BY | Groups rows based on a column |
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1; |
|
HAVING | Filters groups based on a condition |
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) > value; |
|
INSERT INTO | Inserts new records into a table |
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); |
|
UPDATE | Modifies existing records in a table |
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; |
|
DELETE FROM | Deletes records from a table |
DELETE FROM table_name WHERE condition; |
I hope this presentation is helpful and easy to read.
Q: Redo, using ANSI SQL with an “Example” column comprising examples using the Northwind Database
Sure, here is an updated SQL cheatsheet presented in a markdown table inside a code block with an additional column for actual usage examples based on the Northwind database schema:
Command | Description | Example |
---|---|---|
SELECT | Retrieves data from one or more tables | SELECT CompanyName, ContactName FROM Customers |
DISTINCT | Retrieves unique values | SELECT DISTINCT Country FROM Customers |
WHERE | Filters query results based on a condition | SELECT ProductName, UnitPrice FROM Products WHERE CategoryID = 2 |
AND/OR | Combines multiple conditions | SELECT ProductName, UnitPrice FROM Products WHERE CategoryID = 2 AND UnitsInStock > 0 |
IN | Retrieves values that match a list of specified values | SELECT ProductName FROM Products WHERE SupplierID IN (1, 2, 3) |
LIKE | Retrieves values that match a specified pattern | SELECT ProductName FROM Products WHERE ProductName LIKE '%Chai%' |
ORDER BY | Sorts the query results | SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC |
GROUP BY | Groups query results based on a variable | SELECT CategoryID, COUNT(*) FROM Products GROUP BY CategoryID |
HAVING | Filters groups based on a condition | SELECT CategoryID, COUNT(*) FROM Products GROUP BY CategoryID HAVING COUNT(*) > 5 |
JOIN | Combines data from two or more tables based on a related column | SELECT Orders.OrderID, Customers.CompanyName FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID |
INNER JOIN | Returns only the rows that have matching values in both tables | SELECT OrderID, ProductName FROM OrderDetails INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID |
LEFT JOIN | Returns all rows from the left table and the matched rows from the right table | SELECT Customers.CompanyName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID |
RIGHT JOIN | Returns all rows from the right table and the matched rows from the left table | SELECT Orders.OrderID, OrderDetails.ProductID FROM Orders RIGHT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID |
FULL OUTER JOIN | Returns all rows from both tables and matches them where possible | SELECT Customers.CompanyName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID |
UNION | Combines the results of two or more SELECT statements | SELECT ProductName FROM Products WHERE CategoryID = 1 UNION SELECT ProductName FROM Products WHERE CategoryID = 2 |
INSERT INTO | Inserts new data into a table | INSERT INTO Customers (CustomerID, CompanyName, ContactName) VALUES ('ALFKI', 'Alfreds Futterkiste', 'Maria Anders') |
UPDATE | Modifies existing data in a table | UPDATE Products SET UnitPrice = 20 WHERE ProductName = 'Chai' |
DELETE FROM | Deletes data from a table | DELETE FROM Customers WHERE CustomerID = 'ALFKI' |
I hope this updated version of the SQL cheatsheet with examples based on the Northwind database schema is helpful for you to practice with.
Testing ANSI SQL Query Examples
Q: Redo, using these guidelines, for the first 3 rows:
Set the Virtuoso SPASQL Query Service URL to https: //demo.openlinksw.com/spasqlqb/
Qualify Table Names portion of the quoted values in “Example” column by adding Demo.Demo (representing Database and Schema respectively)
Use “Example” column values within quotations as values of for “sql” key in the JSON structure that follows
{
“v”: 1,
“url”: “/XMLA”,
“dsn”: “DSN=Local_Instance”,
“uid”: “demo”,
“pwd”: “demo”,
“path”: null,
“tab”: “exec”,
“idx”: null,
“fkey”: null,
“ref”: null,
“exec”: {
“sql”: “SELECT ProductName FROM Demo…Products WHERE CategoryID = 1 UNION SELECT ProductName FROM Demo…Products WHERE CategoryID = 2\n”
}
}Create an encoded Virtuoso SPASQL Query Service URL using JSON structure as the value for the ?permlink_e parameter
Use the constructed Virtuoso SPASQL Query Service URL to hyperlink values in the “Command” Column
Output to a Markdown Table in a code-block
Command | Description | Example |
---|---|---|
SELECT | Retrieves data from one or more tables | SELECT CompanyName, ContactName FROM Demo.Demo.Customers |
DISTINCT | Retrieves unique values | SELECT DISTINCT Country FROM Demo.Demo.Customers |
WHERE | Filters query results based on a condition | SELECT ProductName, UnitPrice FROM Demo.Demo.Products WHERE CategoryID = 2 |
Q: Redo for the next 3 rows.
Q: Redo “LIKE” command row.
Command | Description | Example |
---|---|---|
AND/OR | Combines multiple conditions | SELECT ProductName, UnitPrice FROM Demo.Demo.Products WHERE CategoryID = 2 AND UnitsInStock > 0 |
IN | Retrieves values that match a list of specified values | SELECT ProductName FROM Demo.Demo.Products WHERE SupplierID IN (1, 2, 3) |
LIKE | Retrieves values that match a specified pattern | SELECT ProductName FROM Demo.Demo.Products WHERE ProductName LIKE '%Chai%' |
Q: Repeat for the next 3 rows.
Q: Redo “HAVING” command row.
Command | Description | Example |
---|---|---|
ORDER BY | Sorts the query results | SELECT ProductName, UnitPrice FROM Demo.Demo.Products ORDER BY UnitPrice DESC |
GROUP BY | Groups query results based on a variable | SELECT CategoryID, COUNT(*) FROM Demo.Demo.Products GROUP BY CategoryID |
HAVING | Filters groups based on a condition | SELECT CategoryID, COUNT(*) FROM Demo.Demo.Products GROUP BY CategoryID HAVING COUNT(*) > 5 |
Q: Redo for next 3 rows.
Q: Redo “LEFT JOIN” row.
Command | Description | Example |
---|---|---|
JOIN | Combines data from two or more tables based on a related column | SELECT Orders.OrderID, Customers.CompanyName FROM Demo.Demo.Orders JOIN Demo.Demo.Customers ON Orders.CustomerID = Customers.CustomerID |
INNER JOIN | Returns only the rows that have matching values in both tables | SELECT OrderID, ProductName FROM Demo.Demo.OrderDetails INNER JOIN Demo.Demo.Products ON OrderDetails.ProductID = Products.ProductID |
LEFT JOIN | Returns all rows from the left table and the matched rows from the right table | SELECT Customers.CompanyName, Orders.OrderID FROM Demo.Demo.Customers LEFT JOIN Demo.Demo.Orders ON Customers.CustomerID = Orders.CustomerID |
Q: Redo for next 3 rows.
Q: Redo “UNION” row.
Command | Description | Example |
---|---|---|
RIGHT JOIN | Returns all rows from the right table and the matched rows from the left table | SELECT Orders.OrderID, OrderDetails.ProductID FROM Demo.Demo.Orders RIGHT JOIN Demo.Demo.OrderDetails ON Orders.OrderID = OrderDetails.OrderID |
FULL OUTER JOIN | Returns all rows from both tables and matches them where possible | SELECT Customers.CompanyName, Orders.OrderID FROM Demo.Demo.Customers FULL OUTER JOIN Demo.Demo.Orders ON Customers.CustomerID = Orders.CustomerID |
UNION | Combines the results of two or more SELECT statements | SELECT ProductName FROM Demo.Demo.Products WHERE CategoryID = 1 UNION SELECT ProductName FROM Demo.Demo.Products WHERE CategoryID = 2 |
Q: Redo for the next 3 rows.
Q: Redo “DELETE FROM” row.
Command | Description | Example |
---|---|---|
INSERT INTO | Inserts new data into a table | INSERT INTO Demo.Demo.Customers (CustomerID, CompanyName, ContactName) VALUES ('ALFKI', 'Alfreds Futterkiste', 'Maria Anders') |
UPDATE | Modifies existing data in a table | UPDATE Demo.Demo.Products SET UnitPrice = 20 WHERE ProductName = 'Chai' |
DELETE FROM | Deletes data from a table | DELETE FROM Demo.Demo.Customers WHERE CustomerID = 'ALFKI' |
Alternatively, you can verify each query against a Virtuoso-hosted Northwind Database via the following steps:
- Open up a Live Virtuoso SQL Query Editor instance
- Click on the “Connect” button to establish a Database Server connection
- Authenticate when challenged using “demo” for both username and password
- Copy and Paste a query of interest into the editor and then click on the “RunSQL” button
SPARQL Cheatsheet Generation
Q: Repeat, for SPARQL using DBpedia for examples.
Sure, here is the SPARQL cheatsheet presented in a markdown table inside a code block with an additional column for actual usage examples using DBpedia for the examples:
Command | Description | Example |
---|---|---|
SELECT | Retrieves data from one or more RDF graphs | SELECT ?person ?birthdate WHERE { ?person dbo:birthDate ?birthdate } LIMIT 10 |
DISTINCT | Retrieves unique values | SELECT DISTINCT ?category WHERE { ?product dcterms:subject ?category } |
WHERE | Filters RDF triples based on a condition | SELECT ?film WHERE { ?film rdf:type dbo:Film . ?film dbo:starring dbr:Harrison_Ford . } |
FILTER | Filters query results based on a condition | SELECT ?film WHERE { ?film rdf:type dbo:Film . ?film dbo:budget ?budget . FILTER(?budget > 100000000) } |
UNION | Combines the results of two or more queries | SELECT ?name WHERE { { ?person foaf:name ?name } UNION { ?organization foaf:name ?name } } |
OPTIONAL | Retrieves optional RDF triples | SELECT ?book ?author WHERE { ?book rdf:type dbo:Book . OPTIONAL { ?book dbo:author ?author } } |
ORDER BY | Sorts the query results | SELECT ?person ?birthdate WHERE { ?person dbo:birthDate ?birthdate } ORDER BY DESC(?birthdate) LIMIT 10 |
LIMIT | Limits the number of results returned | SELECT ?person WHERE { ?person rdf:type dbo:Person } LIMIT 10 |
GROUP BY | Groups query results based on a variable | SELECT ?category (COUNT(?product) AS ?count) WHERE { ?product dcterms:subject ?category } GROUP BY ?category |
HAVING | Filters groups based on a condition | SELECT ?category (COUNT(?product) AS ?count) WHERE { ?product dcterms:subject ?category } GROUP BY ?category HAVING (COUNT(?product) > 100) |
Note: The examples above assume the use of the DBpedia SPARQL endpoint (OpenLink Virtuoso SPARQL Query Editor). If you want to test these queries, you can use the DBpedia SPARQL endpoint or install your own copy of DBpedia.
Q: Redo for SPARQL including DESCRIBE, CONSTRUCT, INSERT, UPDATE, DELETE, COPY, and MOVE commands
Sure, here is the SPARQL cheatsheet presented in a markdown table inside a code block with additional commands and actual usage examples using DBpedia for the examples:
Command | Description | Example |
---|---|---|
SELECT | Retrieves data from one or more RDF graphs | SELECT ?person ?birthdate WHERE { ?person dbo:birthDate ?birthdate } LIMIT 10 |
DISTINCT | Retrieves unique values | SELECT DISTINCT ?category WHERE { ?product dcterms:subject ?category } |
WHERE | Filters RDF triples based on a condition | SELECT ?film WHERE { ?film rdf:type dbo:Film . ?film dbo:starring dbr:Harrison_Ford . } |
FILTER | Filters query results based on a condition | SELECT ?film WHERE { ?film rdf:type dbo:Film . ?film dbo:budget ?budget . FILTER(?budget > 100000000) } |
UNION | Combines the results of two or more queries | SELECT ?name WHERE { { ?person foaf:name ?name } UNION { ?organization foaf:name ?name } } |
OPTIONAL | Retrieves optional RDF triples | SELECT ?book ?author WHERE { ?book rdf:type dbo:Book . OPTIONAL { ?book dbo:author ?author } } |
ORDER BY | Sorts the query results | SELECT ?person ?birthdate WHERE { ?person dbo:birthDate ?birthdate } ORDER BY DESC(?birthdate) LIMIT 10 |
LIMIT | Limits the number of results returned | SELECT ?person WHERE { ?person rdf:type dbo:Person } LIMIT 10 |
GROUP BY | Groups query results based on a variable | SELECT ?category (COUNT(?product) AS ?count) WHERE { ?product dcterms:subject ?category } GROUP BY ?category |
HAVING | Filters groups based on a condition | SELECT ?category (COUNT(?product) AS ?count) WHERE { ?product dcterms:subject ?category } GROUP BY ?category HAVING (COUNT(?product) > 100) |
DESCRIBE | Retrieves RDF triples that describe a resource | DESCRIBE <http://dbpedia.org/resource/Barack_Obama> |
CONSTRUCT | Generates an RDF graph based on a query pattern | CONSTRUCT { ?book dbo:author ?author } WHERE { ?book rdf:type dbo:Book . ?book dbo:author ?author } |
ADD | Adds a named graph to the default graph | ADD <http://example.org/graph> TO DEFAULT |
COPY | Copies a named graph from one location to another | COPY <http://example.org/graph1> TO <http://example.org/graph2> |
MOVE | Moves a named graph from one location to another | MOVE <http://example.org/graph1> TO <http://example.org/graph2> |
INSERT | Inserts RDF triples into a named graph | INSERT DATA { GRAPH <http://example.org/graph> { <http://example.org/resource> <http://example.org/predicate> "object" } } |
UPDATE | Modifies RDF triples in a named graph | DELETE { GRAPH <http://example.org/graph> { <http://example.org/resource> <http://example.org/predicate> "old object" } } INSERT { GRAPH <http://example.org/graph> { <http://example.org/resource> <http://example.org/predicate> "new object" } } WHERE { GRAPH <http://example.org/graph> { <http://example.org/resource> <http://example.org/predicate> "old object" } } |
DELETE DATA | Deletes RDF triples from a named graph | DELETE DATA { GRAPH <http://example.org/graph> { <http://example.org/resource> <http://example.org/predicate> "object" } } |
DELETE WHERE | Deletes RDF triples from a named graph based on a condition | DELETE WHERE { GRAPH <http://example.org/graph> { ?subject ?predicate ?object } FILTER(?predicate = <http://example.org/predicate>) } |
Note: The examples above assume the use of the DBpedia SPARQL endpoint (OpenLink Virtuoso SPARQL Query Editor). If you want to test these queries, you can use the DBpedia SPARQL endpoint or install your own copy of DBpedia.
Q: Redo for SPARQL including DESCRIBE, CONSTRUCT, and SAMPLE() (Note, SAMPLE() is an aggregate function)
Command | Description | Example |
---|---|---|
SELECT | Retrieves data from one or more RDF graphs | SELECT ?person ?birthdate WHERE { ?person dbo:birthDate ?birthdate } LIMIT 10 |
DISTINCT | Retrieves unique values | SELECT DISTINCT ?category WHERE { ?product dcterms:subject ?category } |
WHERE | Filters RDF triples based on a condition | SELECT ?film WHERE { ?film rdf:type dbo:Film . ?film dbo:starring dbr:Harrison_Ford . } |
FILTER | Filters query results based on a condition | SELECT ?film WHERE { ?film rdf:type dbo:Film . ?film dbo:budget ?budget . FILTER(?budget > 100000000) } |
UNION | Combines the results of two or more queries | SELECT ?name WHERE { { ?person foaf:name ?name } UNION { ?organization foaf:name ?name } } |
OPTIONAL | Retrieves optional RDF triples | SELECT ?book ?author WHERE { ?book rdf:type dbo:Book . OPTIONAL { ?book dbo:author ?author } } |
ORDER BY | Sorts the query results | SELECT ?person ?birthdate WHERE { ?person dbo:birthDate ?birthdate } ORDER BY DESC(?birthdate) LIMIT 10 |
LIMIT | Limits the number of results returned | SELECT ?person WHERE { ?person rdf:type dbo:Person } LIMIT 10 |
GROUP BY | Groups query results based on a variable | SELECT ?category (COUNT(?product) AS ?count) WHERE { ?product dcterms:subject ?category } GROUP BY ?category |
HAVING | Filters groups based on a condition | SELECT ?category (COUNT(?product) AS ?count) WHERE { ?product dcterms:subject ?category } GROUP BY ?category HAVING (COUNT(?product) > 100) |
DESCRIBE | Retrieves RDF triples that describe a resource | DESCRIBE <http://dbpedia.org/resource/Barack_Obama> |
CONSTRUCT | Generates an RDF graph based on a query pattern | CONSTRUCT { ?book dbo:author ?author } WHERE { ?book rdf:type dbo:Book . ?book dbo:author ?author } |
SAMPLE | Selects a random value for a variable within a group | SELECT ?country (SAMPLE(?capital) AS ?sample) WHERE { ?country dbo:capital ?capital } GROUP BY ?country |
Testing SPARQL Query Examples
Courtesy of ChatGPT 3.5 and Linked Data Principles (natively implemented in our Virtuoso Multi-Model DBMS Platform), testing the SPARQL Cheatsheet examples boils down to yet another prompt.
Create a rendition using these guidelines, for the first 5 rows:
- Create a DBpedia SPARQL URL from the Example column values within quotations
- Use the URL to hyperlink values in the “Command” Column
Command | Description | Example |
---|---|---|
SELECT | Retrieves values by matching a query pattern. Returns a table with variables and their bindings. | SELECT ?name WHERE { ?s dbo:birthPlace dbr:Germany; foaf:name ?name } LIMIT 10 |
ASK | Tests whether a query pattern has a solution. Returns a boolean value (true or false). | ASK { dbr:Albert_Einstein dbo:birthPlace dbr:Germany } |
DESCRIBE | Retrieves information about resources in the RDF graph. Returns an RDF graph as the result. | DESCRIBE dbr:Albert_Einstein |
CONSTRUCT | Constructs an RDF graph based on the query pattern. Returns an RDF graph as the result. | CONSTRUCT { ?s a foaf:Person } WHERE { ?s a foaf:Person } LIMIT 10 |
SAMPLE() | Returns an arbitrary value from a group of values. Useful for selecting a single value per group. | SELECT (SAMPLE(?name) AS ?sample_name) WHERE { ?s foaf:name ?name } GROUP BY ?s |
Q: Repeat, but for the next 4 rows.
Command | Description | Example |
---|---|---|
COUNT() | Counts the number of solutions that match the query pattern. Returns an integer value. | SELECT (COUNT(?s) AS ?count) WHERE { ?s a dbo:Scientist } |
FILTER | Filters query results based on a condition. Excludes solutions that do not satisfy the condition. | SELECT ?name WHERE { ?s dbo:birthPlace dbr:Germany; foaf:name ?name FILTER (lang(?name) = “en”) } |
OPTIONAL | Allows parts of a query to be optional. If the optional part has no match, the query still succeeds. | SELECT ?name ?birthDate WHERE { ?s foaf:name ?name OPTIONAL { ?s dbo:birthDate ?birthDate } } LIMIT 10 |
ORDER BY | Orders the query results based on the specified variable(s). Can order in ascending or descending. | SELECT ?name ?birthDate WHERE { ?s foaf:name ?name; dbo:birthDate ?birthDate } ORDER BY ?birthDate |
Q: Repeat, for the next 4 rows.
Command | Description | Example |
---|---|---|
GROUP BY | Groups the query results by one or more variables and applies an aggregate function to each group. | SELECT ?birthPlace (COUNT(?s) AS ?count) WHERE { ?s dbo:birthPlace ?birthPlace; a dbo:Scientist } GROUP BY ?birthPlace |
HAVING | Filters the results of a GROUP BY query based on a condition. Excludes groups that do not satisfy the condition. | SELECT ?birthPlace (COUNT(?s) AS ?count) WHERE { ?s dbo:birthPlace ?birthPlace; a dbo:Scientist } GROUP BY ?birthPlace HAVING (COUNT(?s) > 5) |
DESCRIBE | Provides an RDF graph that describes the resources found. | DESCRIBE dbr:Albert_Einstein |
CONSTRUCT | Constructs an RDF graph based on the query pattern. | CONSTRUCT { ?s a dbo:Scientist } WHERE { ?s a dbo:Scientist } |
Alternatively, like the SQL example, you can also manually verify each query against the public DBpedia SPARQL Endpoint via the following steps:
- Open up the DBpedia SPARQL Query Editor
- Copy and Paste a query of interest into the editor and then click on the “Execute Query” button