Difference between revisions of "SQL queries"

Latest revision as of 16:50, 11 December 2012

SQL, often referred to as Structured Query Language, is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra.

A SQL query is composed normally by clausule SELECT and optionally FROM, WHERE and ORDER BY.

SELECT

Used to specify a property list ( columns ) by name that will apppear in results. The character * indicates all properties are selected.

FROM

Defines the nodes type ( tables ) selected.

Select all keyword in all documents

SELECT okm:keywords FROM okm:document

okm:keyword is an okm:document property. The nodes in repository has a hierarchical structure; the base type is nt:base: This type must be used to search all nodes (FROM nt:base)

WHERE

Used to filter selected rows by some criteria.

Select all documents with keywords not empty

SELECT okm:author, okm:keywords FROM okm:document WHERE okm:keywords <> ''

Operators evaluation order:

• ()
• operators: <, >, =, <=, >=, <>, LIKE, IS NULL, IS NOT NULL
• functions: CONTAINS
• logic opertors: NOT, AND, OR

LIKE

Used for pattern purposes in where clausule

Documents name starting with linux

SELECT * FROM okm:document WHERE okm:name LIKE 'linux%'

CONTAINS

Used to full text searching ( the indexed content )

Documents that contains jackrabbit

SELECT * FROM okm:resource WHERE CONTAINS(., 'jackrabbit')

There's some limitation there's no equivalent with this XPath query in SQL. There's a limitation in SQL to using CONTAINS in a descendant query.

//element(*, okm:document)[jcr:contains(okm:content, 'linux')]

ORDER BY

Used to sort (control ordering of) results. Normally used the jcr:score property.

Documents that contains linux ordered by score

SELECT * FROM okm:document WHERE CONTAINS(., 'linux') ORDER BY jcr:score DESC

Property jcr:path

Always it'll appear in results and indicates the full node path. jcr:path it can be used in queries like:

Exact search

jcr:path='/books/mybooks/EffectiveJava'

Searching for children

 
jcr:path LIKE '/books/%' AND NOT jcr:path LIKE '/books/%/%'

Descendants

jcr:path LIKE '/books/mybooks/%'