Download Report

How To Query A Community Development Database
Introduction
Divisions under the Department of Community Development provide access to their data banks
as a service to the public. Data gathered from the Divisions of Administrative Services, Building
and Housing, Neighborhood Development and Neighborhood Services are in Relational
Database Management format and can be queried using Structured Query Language (SQL).
These databases are continuously updated. As demands for additional information arise, new
databases will be added to this site.
Searching records in a database is easy. The purpose of this “How To” is to demonstrate the
power of SQL and how simple SQL commands allow you to extract information from a
database. The purpose is not to teach SQL. There are a number of good tutorials and
textbooks on this subject.
What Is SQL?
Structured Query Language (SQL) is a standard language for defining, querying, modifying, and
controlling data in a Relational Database Management System (RDMS). Structured Query
Language is particularly popular with Web-Enabled databases, such as the ones found on this
site.
Some Very Basic SQL
Just like any computer language, SQL uses various comparison operators to look at values in
relation to one another. Here are the comparison operators used:
Operator
Meaning
=
equal to
>
greater than
<
less than
>=
greater than or equal to
<=
less than or equal to
!=
not equal to (or)
<>
not equal to
AND
this AND that
OR
this OR that
LIKE
one LIKE another
BETWEEN this BETWEEN here and there
%
wildcard - can use before and/or after search string
NULL
a numeric field that does not contain data
NOT NULL
a numeric field that does contain data
Examples
For these examples, the "Land Bank" Database is used. With some modifications, examples
can be applied to any relational database used on this site. After working with these examples,
you should be able to put together your own queries with minimal effort.
Each example starts out with a description of the query followed by the fields to select, and the
information to be entered by you.
Before we can conduct a query, we first have to select a database:
Under DATABASES in the Database Browser Screen, <CLICK> on the database link called
"landbank". See illustration below.
This takes you to a screen where you select the database table. The only table required is the
"Cityport" table, so <CLICK> on the select link next to "Cityport".
After you have selected your database, you need to specify which fields you want to include in
the results of your search.
The screen shown below is the screen where you select your fields and enter your SQL
commands. As you can see, by default, all the fields (columns) are selected for display in the
query. (Note: The fields shown in this example are not necessarily the same as those you will
be working with.)
To select only one (1) field, place your mouse pointer on the field name and <CLICK> with the
left mouse button. (Note: You must select at least one field.)
To select a consecutive range of fields (i.e. from Ward to Sold_to), select (click on) the first field
(Ward), hold down the <SHIFT> key and select the last field (Sold_to).
To select non-consecutive fields, (i.e. Ward, Sold_to, Ordinance_Date), select the first field
(Ward), hold down the <CTRL> key and select each additional field you want displayed in your
query.
Use of the “WHERE CLAUSE”
The WHERE clause specifies a search condition and is part of the SELECT statement. The
WHERE clause will prove to be a powerful tool in extracting data from specific fields in the
databases on this site. The following examples show the use of the WHERE clause.
PLEASE NOTE: The field selection and the keys to press appear in Bold Print.
Example 1: Use of the Equal Sign (=)
Query Description:
Select all fields in the database but only show the records where ward is equal to 10.
Fields to Select: All
1. From the “Select fields” box select all fields. To select ALL fields,
<CLICK> on the first field (to highlight it) and while holding down the
Shift Key, <CLICK> on the last field.
2. Type the following field and value in the "Add Search Conditions
(body of the where clause)" box.
ward=10
3. <CLICK> on the "Go" button or simply press the <Enter> key. (See
illustrations below).
Results of Example 1:
After entering the variable ward and the value to search, a screen will appear with all of the
fields. Scroll across to see the entire screen. (Note that under the "ward" column, only the
value 10 appears; indicating that only ward 10 is selected.
To return to the Query Page, <CLICK> on the "landbank" link and then the "select" link.
Example 2: Use of the Equal Sign (=) but select specific fields
Query Description:
Select all records where ward is equal to 10.
Fields to Select: Street_Number Street_Name
1. From the “Select fields” box, <CLICK> on the Street_Number field to
highlight and while holding down the Ctrl key, <CLICK> on the
Street_Name field.
2. Type the following field and value in the “Add search conditions (body of
the where clause)" box.
ward=10 (See screen print below).
3. <CLICK> on the "Go" button below or simply press the <Enter> key.
The following screen will appear with just the Street_Number and Street_Name fields displayed
for ward 10
Results of Example 2
To return to the Query Page, <CLICK> on the "landbank" link and then the "select" link.
Example 3: Use of the "LIKE" and Wildcard (%)
Query Description:
Select all Permanent Parcels Numbers (PPN) that start with the number "1100".
Fields to select: All
1.
To select ALL fields, from the “Select fields” box, <CLICK> on the
first field to highlight and while holding down the Shift Key,
<CLICK> on the last field.
2.
Type the following field and value in the “Add search conditions”
“where” clause box. IMPORTANT: Make sure to enclose the ppn
value in single quotes. All string values have to be enclosed with
quotes.
ppn like "1100%" (See example below).
3.
<CLICK> on the "Go" button below or simply press the <Enter>
key.
The following screen will appear:
Results of Example 3
The results screen only displays the PPN fields beginning with "1100". Scroll across to see
remaining fields.
To return to the Query Page, < CLICK > on the "landbank" link and then on the "select" link.
Example 4. Use of Variable List with Simplified "Plug-In"-Values
For those who wish to simply "plug-in" a value for any given field, a list of all fields is
provided. You simply enter the value in the box next to the field and press <Enter> or
press the "Go" button to launch the search. The query will come back to the screen
with the results. Like all other queries, you may print the screen with the results.
Query Description:
Select all parcels on Barber Ave
Fields to select: Street_Name
Type the value "Barber Ave" or "barber ave" into the box next to the field
Street_Name. NOTE: The string value is not case sensitive.
Either press <Enter> or the "Go" Button. (See screen shot below).
The following results will come back to the screen:
Results of Example 4
Scroll across to see the entire record.
To return to the Query Page, <CLICK> on the "landbank" link and then the "select"
link.
If you wish to narrow your search, you can add a value to another field. The SQL search
then treats the condition as an "AND" comparison. For example, you could enter
00723115 for the PPN and Barber Ave for the Street_Name and get back records for
the result set for Barber Ave where PPN = 00723115.
This concludes the examples. Feel free to experiment with the other SQL operators like
>=, <=, BETWEEN, etc. in the “WHERE” Clause box. Or, use the "Plug-In" list to select
the fields and values for your specific requirements.
For more information on SQL, read SQL in a Nutshell: a desktop quick reference by Kevin Kline with
Daniel Kline. Copyright © 2001 O'Reilly & Associates, Inc. ISBN 1-56592-744-3 1.