Database Fields

SELECT [DISTINCT | ALL] {* | <val> [, <val> ...]}
FROM <tableref> [, <tableref> ...]
	[WHERE <search_condition>]
	[ORDER BY <order_list>]
	
<val> = {
col | <constant> | <expr> | <function> | NULL
}

<constant> = num | 'string'

<function> = {
COUNT (* | [ALL] <val> | DISTINCT <val>)
	| SUM ([ALL] <val> | DISTINCT <val>)
	| AVG ([ALL] <val> | DISTINCT <val>)
	| MAX ([ALL] <val> | DISTINCT <val>)
	| MIN ([ALL] <val> | DISTINCT <val>)
	| CAST (<val> AS <datatype>)
	| UPPER (<val>)
}

<tableref> = <joined_table> | table [alias]

<joined_table> = <tableref> <join_type> JOIN <tableref>
ON <search_condition> | (<joined_table>)

<join-type> = {[INNER] | {LEFT | RIGHT} [OUTER]} JOIN

<search_condition> = {<val> <operator>
{<val> | (<select_one>)} 
	| <val> [NOT] BETWEEN <val> AND <val> 
	| <val> [NOT] LIKE <val> [ESCAPE <val>] 
	| <val> [NOT] IN (<val> [, <val> ...] | <select_list>) 
	| <val> IS [NOT] NULL 
	| <val> {[NOT] {= | < | >} | >= | <=} 
		{ALL | SOME | ANY} (<select_list>) 
	| EXISTS (<select_expr>) 
	| SINGULAR (<select_expr>) 
	| <val> [NOT] CONTAINING <val> 
	| <val> [NOT] STARTING [WITH] <val> 
	| (<search_condition>) 
	| NOT <search_condition> 
	| <search_condition> OR <search_condition>
	| <search_condition> AND <search_condition>}
	
<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

<select_one> = SELECT on a single column that returns exactly one row.

<select_list> = SELECT on a single column that returns zero or more rows.

<select_expr> = SELECT on a list of values that returns zero or more rows.

<order_list> = 
{col | int} [COLLATE collation] [ASC[ENDING] | DESC[ENDING]]
[, <order_list>]

"      1 A"

"1234567AB"

"      1 A"

"1234567AB"

"      1 A"

"1234567AB"

• The SQL operators, table names, and field names are not case sensitive.
• LAT, LON, NLAT, and NLON are treated as straight numbers e.g. -50 degrees latitude is always considered less than -40 degrees latitude even though -50 is at a larger inclination.
• You can use this interface to query tables other than the frames table. The Web form bases its queries on the frames table, so to submit queries which do not reference the frames table, use this interface.
• Be aware that the frames table has a field named "caption" as does the captions table but that these two fields contain different data and are vastly different in size. The frames table caption field is only one character whereas the captions table caption field is over 32000 characters.
• The anchors in the table of results you get when you submit your SQL query will only show up if your results contain unabiguous values for the fields mission, roll, and frame.
• If you do an OUTER join (LEFT and RIGHT joins are OUTER joins) and use * for the field list for either table, the fields that exist in both tables that have the same name will show up twice and one will have values and the other won't if corresponding records do not exist in both tables. This is because the SQL server will return fields with the same name for each table in which the name exists. For example, SELECT f.*,i.* FROM frames f LEFT JOIN images i ON f.mission=i.mission AND f.roll=i.roll AND f.frame=i.frame WHERE f.mission='MER3' will return two MISSION and ROLL fields and one of them will be blank because those fields exist by that name in both the frames and images tables but the rolls table contains no entries for MER3. When this happens, then the table you get that displays your query results will not have an anchor for viewing more information on the record. This is because the software detects the NULL values for the key fields.

  You can resolve this problem by using the SQL AS operator to cause the returned fields to have different names in cases where there is name contention. For example: SELECT f.*,i.mission AS imagems,i.roll AS imageroll,i.width,i.height FROM frames f LEFT JOIN images i ON f.mission=i.mission AND f.roll=i.roll WHERE f.mission="MER3"

  Note that on INNER joins, even though duplicate fields will be returned, if you've joined using the table's key fields then records will be returned only when they exist in both tables and therefore the key fields will be equal.

• It use to be that data values could be specified in quotation marks ("). Since the database software was updated, the new version reserves the quotation marks for field and other such token names to allow them to have spaces. So now when you specify data values in your queries you will need to use apostrophies ('). For example:
  SELECT COUNT(*) FROM frames WHERE mission="MER3"
  is not valid. Now this syntax is required:
  SELECT COUNT(*) FROM frames WHERE mission='MER3'
  

This returns a count of how many images there are under the scanned directory of the images table:
SELECT COUNT(*) FROM images WHERE directory STARTING WITH 'scanned'

This returns a count of how many images there are in the images table:
SELECT COUNT(*) FROM images

This returns a count of how many images there are in the images table that have comments:
SELECT COUNT(comments) FROM images

This returns a list of photo IDs (mission, roll, frame) for images under the scanned directory:
SELECT mission,roll,frame FROM images WHERE directory STARTING WITH 'scanned'

This returns a list of photos that have captions:
SELECT mission,roll,frame,lat,lon,geon,feat,pdate,expo,cldp,tilt,image,caption FROM frames WHERE caption='Y'

This returns a list of photos that have the word 'Houston' (not case sensitive) in their caption regardless of whether or not the photo exists in the frames table:
SELECT mission,roll,frame FROM captions WHERE caption CONTAINING 'Houston'

This returns a count of records that have no center point nor nadir point:
SELECT count(*) FROM frames WHERE lat IS NULL AND nlat IS NULL
Only the latitude fields are queried because there should be no cases in which either the latitude or longitude is NULL but the other is not NULL.

This returns a count of how many records have captions but do not have a corresponding record in the frames table:
SELECT count(*) FROM captions c LEFT JOIN frames f ON c.mission=f.mission AND c.roll=f.roll AND c.frame=f.frame WHERE frames.caption IS NULL

This shows the caption records from the above query that have no frames table record:
SELECT c.mission,c.roll,c.frame FROM captions c LEFT JOIN frames f ON c.mission=f.mission AND c.roll=f.roll AND c.frame=f.frame WHERE f.caption IS NULL

This lists the image records that do not have a corresponding frames record:
SELECT DISTINCT i.mission,i.roll,i.frame FROM images i LEFT JOIN frames f ON i.mission=f.mission AND i.roll=f.roll AND i.frame=f.frame WHERE f.mission IS NULL

This counts how many rolls of film are of type 5017:
SELECT count(*) FROM rolls WHERE film='5017'

This displays all the captions containing the word 'Houston' regardless of case:
SELECT * FROM captions WHERE caption CONTAINING 'Houston'

This lists the distinct FILMMS values for ISS002 along with counts of how many records there are of each:
SELECT filmms,COUNT(filmms) FROM frames WHERE mission='ISS002' GROUP BY filmms
Note that the GROUP BY takes care of providing the distinct list so no DISTINCT keyword is used.