Developer Documentation Library > Data Model > Accessing the UNICOM Intelligence Data Model > Working with the Case Data Model > SQL syntax > SQL queries > Examples
 
Examples
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. For more information, see Running the example queries in DM Query.
1. Retrieve all rows and a subset of the columns and sort the rows in serial number order
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.
2. Retrieve all rows and all columns
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.
3. Eliminate duplicate rows
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
Here are the first six rows in the result set:
{male} {yes}
{female} {no}
{female} {no}
{male} {yes}
{female} {yes}
{male} {yes}
You can see that the results contain duplicate rows. You can eliminate the duplicates by using the DISTINCT keyword.
SELECT DISTINCT gender, before
FROM vdata
ORDER BY serial
Here is the result set:
{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.
4. Filter on a category of a categorical variable
You can use the WHERE clause to filter the rows. This example filters on the Male category of the gender variable.
SELECT serial, age, gender, visits
FROM vdata
WHERE gender = {male}
ORDER BY serial
Here are the first four rows in the result set:
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 might want to compare these results with the results for Example 1.)
5. Filter on a numeric variable
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
Here are the first eight rows in the result set:
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.
6. Filter on text variables using LIKE
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
7. Filter on text variables using NOT LIKE
You can use NOT LIKE to exclude rows that match the specified text. This query selects all respondents who do not live in London.
SELECT name, address
FROM vdata
WHERE NOT (address LIKE '%London%')
Here are the first five rows in the result set:
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.
8. Create a calculated column
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
Here are the first eight rows in the result set:
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
9. Create column headings
This example uses the AS clause to create headings for the columns in the result set.
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
Here are the first eight rows in the result set:
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
10. Filter using IS NOT NULL
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
Here are the first ten rows in the result set:
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
The Museums column is blank in rows 8 and 10. You can exclude these rows by using IS NOT NULL in the WHERE clause.
You can also use IS NULL in a WHERE clause to restrict the query to rows that are NULL.
11. Filter using an expression
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
Here are the first ten rows in the result set:
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
This excludes all but one of the first ten rows in the previous result set.
Serial Remember
------ --------------------------------------------------------------------
311 {dinosaurs,birds,whales,mammals,minerals,botany,human_biology,ev...
You can get exactly the same result using the ContainsAll function. However, using the operator generally gives a faster response.
SELECT serial AS Serial,
remember AS Remember
FROM vdata
WHERE remember.ContainsAll({Botany,Minerals})
ORDER BY serial
See also
Advanced SQL queries
Hierarchical SQL queries
Advanced hierarchical SQL queries
SQL queries