download free 30 days trial version buy simpledb explorer
Documentation   Download   Purchase   Support   FAQs   Forum   ScreenShots & Demos       

How to Use Select Queries on Amazon SimpleDB?

amazon simpledb queries

download sdb explorer for windows,linux and MAC

How to Use Select Queries on Amazon SimpleDB

SELECT query on Amazon SimpleDB provides more simple expressions similar to the standard SQL SELECT statement. You can use it to get items according to the specified condition.

SDB Explorer provides user interface to run SELECT queries on Amazon SimpleDB data.

Syntax for the Select Query.

select output_list from domain_name [where expression] [sort_instructions] [limit limit]

The output_list can be: *, itemName(),count(*), list of attributes

  • * for all attributes.
  • itemName() for the item name only.
  • count(*) for the total count of items matches the query expression. It returns the number of items in a result set instead of returning the items.
  • An explicit list of attributes (attribute1,..., attributeN)

The domain_name is the domain from which you want to search items.

The expression is the match expression for items.

  • You can use select expressions like =, <=, <, > =, like, not like, between, is null, is not null etc.

The sort_instructions sorts the results on a single attribute, in ascending or descending order.

The limit is the maximum number of results to return (default: 100, max. 2500).

Select Expressions:

                                         For Example Domain: - survey

Item Name Year Exported Items City
T01 1981 Copper London
S02 1981 Gold Paris
I04 1988 Diamond Delhi,Pna,Goa
A05 2001 Pearl Paris
T06 2003 Gold London
A08 2006 Platinum Delhi,Bombay

                                                                        Examples for simple and range queries expressions on domain survey :

Query Query Result
select * from survey where City = 'Delhi' Retrieves all items having attribute value of city as Delhi (It will return row having item name I04 and A08)
 
select * from survey where City ='paris' OR City ='Delhi' Retrieves all items contain Delhi or Paris in city attribute column. (It will return row having item name I04, S02, A05 and A08)
 
select * from survey where City != 'london' Retrieves all items for which the value of city is not London. (It will return row having item name I04, S02, A05 and A08)
 
select * from survey where City != 'london' AND City != 'Delhi' Retrieves all items which do not contain both London and Delhi. (It will return row having item name I04, S02, A05, T01, T06 and A08)
 
select * from survey where Year > '2000' Retrieves all items exported after year 2000 (It will return row having item name T06, A05 and A08)
 
select * from survey where Year >= '2001' Retrieves all items exported in year 2000 and after it. (It will return row having item name T06, A05 and A08)
 
select * from survey where Year < '2000' Retrieves all items exported before 2000. . (It will return row having item name I04, S02, T01)
 
select * from survey where Year <= '2001' Retrieves all items exported in 2001 and before 2001 (It will return row having item name I04, S02, T01 and A05)
 
select * from survey where City like 'lon%' Retrieves all items having city name with pre constant ‘lon’. (It will return row having item name T01 and T06)
 
select * from survey where City not like 'par%' Retrieves all items not having pre constant ‘par’ (It will return row having item name I04, T01, T06 and A08)
 
select * from survey where City = 'Delhi' intersection Year <= '2000' Retrieves all items having city Delhi and year greater than 2000 (It will return row having item name I04)
 
select * from survey where City like 'lon%' Retrieves all items having city name with pre constant ‘lon’. (It will return row having item name T01 and T06)
 
select * from survey where Year between '1981' and '2000' Retrieves all items exported after 1881 and before 2000 (It will return row having item name I04, S02, T01)
 
select * from survey where Year in('1981','2000','2003') Retrieves all items having year 1981 , 2000 and 2003 ((It will return row having item name T01, T06 and S02)
 
select * from survey where itemName() in('T01','S02','A05') Retrieves all items having Item Name T01 , S02 and A05 (It will return row having item name T01 , S02 and A05).
 
select * from survey where City like 'lon%' Retrieves all items having city name with pre constant ‘lon’. (It will return row having item name T01 and T06)
 
select * from survey where Year is null Retrieves items for which year field is empty. (It will not return any row)
 
select * from survey where Year is not null Retrieves items for which year field is not empty. (It will return all rows)
 
select * from survey where every(City) = 'london' Retrieves items for which city is only London (It will return row having item name T01 and T06)
 
select * from survey where every(City) like 'p%' Retrieves items for which city is only having city name with pre constant ‘lon’. (It will return row having item name A05 and S02)
 
select * from survey where Year = '1981' intersection City is not null order by City desc Retrieves all items exported in 1981 and sorts them by City in descending order. (It will return row having item name S02 and T01).
 
select * from survey where Year < '2000' order by Year limit 2 Retrieves two items that were exported before 2000 and lists them in ascending order. (It will return row having item name S02 and T01. After Click on next button it will return row having item name I04. )
 

NOTE: Covers all subsets of the full SQL language. Amazon SimpleDB does not support joins, aggregations, sub queries, custom syntax extensions to support multi-value in SELECT queries.

Query Editor Pane Improvement : SDB Explorer enhanced its query editor pane. Now you can use small but valuable option while wirting query, that are Undo, Redo, Copy, Paste, Comment - Un-Comment option. To writing  query again and again  to get data for specific condition is very boring. So Query Editor Pane  allow you to write new query without deleting earlier one. You just need to comment that unwanted query.

Undo : You can undo all your changes done till begining.

Redo : You can redo all your changes done till last edit.

Copy : You can copy the selected query or area, and paste where you want.

Paste : You can paste data in query editor pane which is copied from same pane as well as any other text file.

Comment : You can comment the desired query. Select the query and click on Comment tool button it will comment the query by /* query */ . you can also comment the code by covering query data by /* ---- */

Un-Comment : You can un-comment the desired query. Select the query and click on Comment tool button it will un-comment the query by removing  /*  */ . you can also un-comment the code by removing query data by /* ---- */


 

  Related Topics :


 

Give us your feedback