AEM JCR queries

Get all the pages which have property 
SELECT * from [cq:Page] AS t 
where ISDESCENDANTNODE('/content') and
      contains(t.*, 'componentName or URL')


SELECT * FROM [cq:Page] AS parent 
INNER JOIN [nt:base] AS child ON ISDESCENDANTNODE(child, parent) 
WHERE ISDESCENDANTNODE(parent, '/content/') AND child.[jcr:title]='config'

Get all the nodes where jcr:title have text config
SELECT * from [nt:base] AS t 
where ISDESCENDANTNODE('/content') and
contains(t.[jcr:title], 'config')

Get all the nodes from a directory where sling:resourceSuperType is component path.
SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/apps/weretail/components]) 
and [sling:resourceSuperType] = 'core/wcm/components/list/v1/list'

Get all the pages from a path by exculding a path as well
SELECT * FROM [cq:Page] AS parent WHERE (ISDESCENDANTNODE(parent , [/content/screens/we-retail]) 
AND NOT ISDESCENDANTNODE(parent, [/content/screens/we-retail/channels/])) 

Get all the components based on their group.
SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/apps/weretail/components]) 
and [componentGroup] = 'We.Retail'