Basic SQL queries

SELECT
[ ALL | DISTINCT ]
{ * | expression [ [ AS ] column_alias ]
[, * | expression [ [ AS ] column_alias ]... ] }
FROM
table
[ WHERE
expression ]
[ GROUP BY
expression [, expression... ] ]
[ ORDER BY
{ expression } [ ASC | DESC ]
[,{ expression } [ ASC | DESC ]... ] ]

The supports SELECT only from a single table and does not support the INTO and UNION clauses. Table aliases are not supported. However, you can use the table.column_name convention when specifying select columns. This means that queries generated by query builders will run against the OLE DB Provider. For information on queries on HDATA, see Hierarchical SQL queries.

These examples use the Museum example Quanvert database, which is based on a survey of visitors to a museum. You can run these queries using the DM Query sample tool, which is supplied with the UNICOM Intelligence Developer Documentation Library. The examples were run using the connection option to return category names rather than category values. You select this option on the Advanced tab in the Data Link Properties dialog box. For more information, see Running the example queries in DM Query.

SELECT serial, age, gender, visits
FROM vdata
ORDER BY serial

This example returns all of the rows in the table because no WHERE clause is specified. It returns the serial, age, gender, and visits columns in that order. It uses the ORDER BY clause to sort the rows in serial number order. Here are the first ten rows in the result set:

1 {e3544_years} {male} 1
2 {e2534_years} {female}
3 {e2534_years} {female}
4 {e2534_years} {male} 6
5 {e3544_years} {female} 2
6 {e1720_years} {male} 1
7 {e2534_years} {female} 1
8 {e3544_years} {male} 1
9 {e4554_years} {female} 2
10 {e2534_years} {female} 6

Each row holds the answers given by a different respondent. The first and fourth columns are the serial and visits numeric variables. These store the respondent's serial number and the number of times the respondent has previously visited the museum, respectively. The second and third rows have blank values in the fourth column. These indicate missing values. The second and third columns are the age and gender single response categorical variables. These store the category selected by each respondent in answer to questions that ask for his or her age and gender.

SELECT *
FROM vdata
ORDER BY serial DESC

Like the previous example, this example also returns all of the rows in the table. However, it returns all of the columns instead of a subset of columns. The asterisk (*) indicates all columns. The DESC keyword sorts the rows in descending order.

Every row is unique in the results of Examples 1 and 2 because the results include the serial column, which contains the primary key. When a query does not include the primary key, the rows are not necessarily unique. This query excludes the serial column:

SELECT gender, before
FROM vdata
ORDER BY serial

{male} {yes}
{female} {no}
{female} {no}
{male} {yes}
{female} {yes}
{male} {yes}

SELECT DISTINCT gender, before
FROM vdata
ORDER BY serial

{male} {yes}
{female} {no}
{female} {yes}
{male} {no}

Now each row is unique: rows 3, 4, and 6 have been eliminated and an additional row is included. This row was in the previous result set, but was not shown because it was not one of the first six rows.

SELECT serial, age, gender, visits
FROM vdata
WHERE gender = {male}
ORDER BY serial

1 {e3544_years} {male} 1
4 {e2534_years} {male} 6
6 {e1720_years} {male} 1
8 {e3544_years} {male} 1

You can see that all of the female respondents have been filtered out of the results set. (You may want to compare these results with the results for Example 1.)

This example removes the respondents who have never visited the museum before, by filtering out the rows that do not have a value greater than zero in the visits column.

SELECT serial, age, gender, visits
FROM vdata
WHERE visits > 0
ORDER BY serial

1 {e3544_years} {male} 1
4 {e2534_years} {male} 6
5 {e3544_years} {female} 2
6 {e1720_years} {male} 1
7 {e2534_years} {female} 1
8 {e3544_years} {male} 1
9 {e4554_years} {female} 2
10 {e2534_years} {female} 6

When you compare the value in the data with a literal value (in this example 0 is a literal value), you must use US English formatting for date, time, and numeric values, even if you are working in another locale (region). For more information, see Locale handling in the UNICOM Intelligence Data Model.

SELECT name, address
FROM vdata
WHERE address LIKE '%London_W%

This example uses the Like operator to select rows that have text in the address text variable that partially matches a specified text, which includes two wildcards, the percent sign (%) and the underscore character (_). The percent sign matches any sequence of zero or more characters, and the underscore character matches any single character. Here are the first five rows in the result set:

Helen Miller 43 Dale Street, Stanhope, London W2
John Davis 78 Audley Grange, London W14
Daisy Ward 2 Dicconson Terrace, London W2
Sandy Malpas 8 Towngate Wyke, London W2
Bridget Marshas 73 Woodley Crescent, London W15

SELECT name, address
FROM vdata
WHERE NOT (address LIKE '%London%')

Roger Ferrar 124 Dill Hall Lane, Church Ditton
Shauna Pocklington 22 Southbank Road, Hounslow
Julia Von Bergen Gatehouse, Church Strarmthorpe
Tatum Walker 73 Kings Road, North Ormesby
David Memel 30 Great Queen Square, Aberdeen

The LIKE keyword matches case sensitively. So this query excludes respondents only if their address has London with an initial capital and all of the characters in lower case. If the address has London with any other combination of upper and lower case (such as LONDON or london), they are not excluded.

You can use an expression in the list of columns to create a calculated column. In the Museum database, the visits variable stores the number of times respondents have visited the museum before and visits12 stores how many times they have visited in the last 12 months. This example creates a column to show the combined number of visits. The WHERE clause filters out respondents for whom the value in either variable is not greater than zero.

SELECT serial, age, gender, (visits + visits12)
FROM vdata
WHERE visits > 0 or visits12 > 0
ORDER BY serial

1 {e3544_years} {male} 1
4 {e2534_years} {male} 6
5 {e3544_years} {female} 2
6 {e1720_years} {male} 1
7 {e2534_years} {female} 2
8 {e3544_years} {male} 1
9 {e4554_years} {female} 3
10 {e2534_years} {female} 9

SELECT serial AS Serial,
age AS Age,
gender AS Gender,
(visits + visits12) AS 'Total Visits'
FROM vdata
WHERE visits > 0 or visits12 > 0
ORDER BY serial

Serial Age Gender Total Visits
------ ------------- -------- ------------
1 {e3544_years} {male} 1
4 {e2534_years} {male} 6
5 {e3544_years} {female} 2
6 {e1720_years} {male} 1
7 {e2534_years} {female} 2
8 {e3544_years} {male} 1
9 {e4554_years} {female} 3
10 {e2534_years} {female} 9

The museums variable is a multiple response variable that stores the responses to a question that presents respondents with a list of other museums and galleries and asks them to indicate which ones they have visited. However, respondents were not asked this question if in the previous question they said they had never visited another museum. The museums variable value is NULL for respondents who were not asked the question.

SELECT serial AS Serial,
museums AS Museums
FROM vdata
ORDER BY serial

Serial Museums
------ -------------------------------------------------------------------
1 {national_museum_of_science}
2 {national_museum_of_science}
3 {museum_of_design,national_art_gallery}
4 {national_museum_of_science,archeological_museum,national_art_g...}
5 {national_museum_of_science}
6 {archeological_museum}
7 {museum_of_design,archeological_museum,national_art_gallery,nor...}
8
9 {national_museum_of_science;northern_gallery}
10

You can use any expression that is valid in the Evaluate component in a WHERE clause. This example uses the remember variable, which is a multiple response variable that records which galleries respondents remember seeing. Some of the respondents were interviewed when they entered the museum and these respondents were not asked the question and the variable has a NULL value for them. First you will run a query that filters on IS NOT NULL to remove the respondents who were not asked the question.

SELECT serial AS Serial,
remember AS Remember
FROM vdata
WHERE remember IS NOT NULL
ORDER BY serial

Serial Remember
------ --------------------------------------------------------------------
305 {dinosaurs,fish_and_reptiles,fossils,mammals,minerals,evolution,...
306 {dinosaurs,fish_and_reptiles,fossils,mammals,minerals,ecology,or,...
307 {dinosaurs,fossils,birds,insects,whales,mammals,minerals,origin_...
308 {dinosaurs,conservation,birds,mammals,minerals,other}
309 {dinosaurs,birds,minerals,human_biology,evolution}
310 {dinosaurs,birds,whales,mammals,origin_of_species,human_biology,...
311 {dinosaurs,birds,whales,mammals,minerals,botany,human_biology,ev...
312 {dinosaurs,insects,human_biology}
313 {dinosaurs,conservation,botany,other}
314 {dinosaurs,fossils,mammals,origin_of_species,human_biology,evolu...

You can use the greater than (>) Comparison operators to restrict the query to respondents whose responses include two of the less popular galleries.

SELECT serial AS Serial,
remember AS Remember
FROM vdata
WHERE remember > {Botany,Minerals}
ORDER BY serial

Serial Remember
------ --------------------------------------------------------------------
311 {dinosaurs,birds,whales,mammals,minerals,botany,human_biology,ev...