- From: Kingsley Idehen <kidehen@openlinksw.com>
- Date: Wed, 3 May 2023 08:56:11 -0400
- To: "public-lod@w3.org" <public-lod@w3.org>, "semantic-web@w3.org" <semantic-web@w3.org>
- Message-ID: <190ca290-feec-33d7-2db1-07d3ca2ceaff@openlinksw.com>
>>
>>
>> On Tuesday, 2 May 2023 at 09:13:56 UTC+12 kidehen wrote:
>>
>> All,
>>
>> ChatGPT 3.5 is less capable that 4.0, but much faster. Here’s a
>> transcript demonstrating the creation of SQL and SPARQL
>> cheatsheets – that I just generated (whimsically).
>>
>>
>> Transcript
>>
>> 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.
>> A:
>> 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, with an examples column using Northwind
>> A:
>> 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
>> ProductName, UnitPrice FROM Products;|
>> DISTINCT Retrieves unique values |SELECT DISTINCT CategoryID
>> FROM Products;|
>> WHERE Filters records based on a condition |SELECT ProductName,
>> UnitPrice FROM Products WHERE UnitPrice > 20;|
>> AND/OR Combines multiple conditions |SELECT ProductName,
>> UnitPrice FROM Products WHERE CategoryID = 1 AND Discontinued = 0;|
>> ORDER BY Sorts the result set |SELECT ProductName, UnitPrice
>> FROM Products ORDER BY UnitPrice DESC;|
>> LIMIT Limits the number of results returned |SELECT
>> ProductName, UnitPrice FROM Products LIMIT 10;|
>> JOIN Combines rows from two or more tables based on a related
>> column between them |SELECT Products.ProductName,
>> Suppliers.CompanyName FROM Products JOIN Suppliers ON
>> Products.SupplierID = Suppliers.SupplierID;|
>> GROUP BY Groups rows based on a column |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;|
>> INSERT INTO Inserts new records into a table |INSERT INTO
>> Products (ProductName, UnitPrice) VALUES ('Chai', 18.00);|
>> UPDATE Modifies existing records in a table |UPDATE Products
>> SET UnitPrice = 20 WHERE ProductID = 1;|
>> DELETE FROM Deletes records from a table |DELETE FROM Products
>> WHERE ProductID = 1;|
>> I hope this updated version of the SQL cheatsheet with examples
>> based on the Northwind database schema is helpful for you to
>> practice with.
>>
>>
>> Q: Repeat, for SPARQL using DBpedia for examples.
>> A:
>> 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 (https://dbpedia.org/sparql). 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, COPY, and MOVE
>> commands
>> A:
>> 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>
>> <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 }|
>> COPY Copies a named graph from one location to another |COPY
>> <http://example.org/graph1> <http://example.org/graph1> TO
>> <http://example.org/graph2> <http://example.org/graph2>|
>> MOVE Moves a named graph from one location to another |MOVE
>> <http://example.org/graph1> <http://example.org/graph1> TO
>> <http://example.org/graph2> <http://example.org/graph2>|
>>
>> Note: The examples above assume the use of the DBpedia SPARQL
>> endpoint (https://dbpedia.org/sparql). If you want to test these
>> queries, you can use the DBpedia SPARQL endpoint or install your
>> own copy of DBpedia.
>>
All,
Testing SPARQL Cheatsheet Query Examples, courtesy of ChatGPT 3.5
& Linked Data Principles
Create a rendition using these guidelines, for the first 5 rows:
1. Create a DBpedia SPARQL URL from the Example column values
within quotations
2. Use the URL to hyperlink values in the “Command” Column
Command Description Example
SELECT
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=SELECT%20%3Fname%20WHERE%20%7B%20%3Fs%20dbo%3AbirthPlace%20dbr%3AGermany%3B%20foaf%3Aname%20%3Fname%20%7D%20LIMIT%2010&format=text%2Fhtml>
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
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=ASK%20%7B%20dbr%3AAlbert_Einstein%20dbo%3AbirthPlace%20dbr%3AGermany%20%7D&format=text%2Fhtml>
Tests whether a query pattern has a solution. Returns a boolean value
(true or false). ASK { dbr:Albert_Einstein dbo:birthPlace dbr:Germany }
DESCRIBE
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=DESCRIBE%20dbr%3AAlbert_Einstein&format=text%2Fhtml>
Retrieves information about resources in the RDF graph. Returns an RDF
graph as the result. DESCRIBE dbr:Albert_Einstein
CONSTRUCT
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=CONSTRUCT%20%7B%20%3Fs%20a%20foaf%3APerson%20%7D%20WHERE%20%7B%20%3Fs%20a%20foaf%3APerson%20%7D%20LIMIT%2010&format=text%2Fhtml>
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()
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=SELECT%20(SAMPLE(%3Fname)%20AS%20%3Fsample_name)%20WHERE%20%7B%20%3Fs%20foaf%3Aname%20%3Fname%20%7D%20GROUP%20BY%20%3Fs&format=text%2Fhtml>
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()
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=SELECT%20(COUNT(%3Fs)%20AS%20%3Fcount)%20WHERE%20%7B%20%3Fs%20a%20dbo%3AScientist%20%7D&format=text%2Fhtml>
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
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=SELECT%20%3Fname%20WHERE%20%7B%20%3Fs%20dbo%3AbirthPlace%20dbr%3AGermany%3B%20foaf%3Aname%20%3Fname%20FILTER%20(lang(%3Fname)%20%3D%20%22en%22)%20%7D&format=text%2Fhtml>
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
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=SELECT%20%3Fname%20%3FbirthDate%20WHERE%20%7B%20%3Fs%20foaf%3Aname%20%3Fname%20OPTIONAL%20%7B%20%3Fs%20dbo%3AbirthDate%20%3FbirthDate%20%7D%20%7D%20LIMIT%2010&format=text%2Fhtml>
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
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=SELECT%20%3Fname%20%3FbirthDate%20WHERE%20%7B%20%3Fs%20foaf%3Aname%20%3Fname%3B%20dbo%3AbirthDate%20%3FbirthDate%20%7D%20ORDER%20BY%20%3FbirthDate&format=text%2Fhtml>
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
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=SELECT%20%3FbirthPlace%20(COUNT(%3Fs)%20AS%20%3Fcount)%20WHERE%20%7B%20%3Fs%20dbo%3AbirthPlace%20%3FbirthPlace%3B%20a%20dbo%3AScientist%20%7D%20GROUP%20BY%20%3FbirthPlace&format=text%2Fhtml>
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
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=SELECT%20%3FbirthPlace%20(COUNT(%3Fs)%20AS%20%3Fcount)%20WHERE%20%7B%20%3Fs%20dbo%3AbirthPlace%20%3FbirthPlace%3B%20a%20dbo%3AScientist%20%7D%20GROUP%20BY%20%3FbirthPlace%20HAVING%20(COUNT(%3Fs)%20%3E%205)&format=text%2Fhtml>
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
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=DESCRIBE%20dbr%3AAlbert_Einstein&format=text%2Fhtml>
Provides an RDF graph that describes the resources found. DESCRIBE
dbr:Albert_Einstein
CONSTRUCT
<http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=CONSTRUCT%20%7B%20%3Fs%20a%20dbo%3AScientist%20%7D%20WHERE%20%7B%20%3Fs%20a%20dbo%3AScientist%20%7D&format=text%2Fhtml>
Constructs an RDF graph based on the query pattern. CONSTRUCT { ?s a
dbo:Scientist } WHERE { ?s a dbo:Scientist }
--
Regards,
Kingsley Idehen
Founder & CEO
OpenLink Software
Home Page:http://www.openlinksw.com
Community Support:https://community.openlinksw.com
Weblogs (Blogs):
Company Blog:https://medium.com/openlink-software-blog
Virtuoso Blog:https://medium.com/virtuoso-blog
Data Access Drivers Blog:https://medium.com/openlink-odbc-jdbc-ado-net-data-access-drivers
Personal Weblogs (Blogs):
Medium Blog:https://medium.com/@kidehen
Legacy Blogs:http://www.openlinksw.com/blog/~kidehen/
http://kidehen.blogspot.com
Profile Pages:
Pinterest:https://www.pinterest.com/kidehen/
Quora:https://www.quora.com/profile/Kingsley-Uyi-Idehen
Twitter:https://twitter.com/kidehen
Google+:https://plus.google.com/+KingsleyIdehen/about
LinkedIn:http://www.linkedin.com/in/kidehen
Web Identities (WebID):
Personal:http://kingsley.idehen.net/public_home/kidehen/profile.ttl#i
:http://id.myopenlink.net/DAV/home/KingsleyUyiIdehen/Public/kingsley.ttl#this
Attachments
- application/pkcs7-signature attachment: S/MIME Cryptographic Signature
Received on Wednesday, 3 May 2023 12:56:25 UTC