Cold Fusion Workshop: SQL examples

Cold Fusion Workshop:
SQL examples

1. The most basic query possible

Query:

SELECT * FROM Contacts

Results:

Contact ID	First Name	Last Name	Email	Organization Name	phone number	Fax Number	Address	City Name	County	State Code	Postal Code
1	Maureen	Koplow	mkoplow@ix.netcom.com	Activists of Delaware Valley Animal Network Calendar of Events	(609) 853-1847	(609) 853-1847	476 Warwick Road	Deptford	Gloucester County	NJ	08096-
2	Adam	Weissman	luckyfuzz@eworld.com	Advocates for Forgotten Wildlife	(201) 930-9026		68 Mill Road Extension	Woodcliff Lake	Bergen County	NJ	07675-
3	Joseph	Augystyn		Alaimo Group, The	(609) 267-8310	(609) 267-7452	200 High Street	Mount Holly	Burlington County	NJ	08060-
4	Fred	Bach	livingocean@online96.com	Alliance for a Living Ocean, North Beach Haven	(609) 492-0222		2007 Long Beach Boulevard	North Beach Haven	Ocean County	NJ	08008-
5	Carol	Elliot	livingocean@online96.com	Alliance for a Living Ocean, North Beach Haven			P.O. Box 95	Ship Bottom	Ocean County	NJ	08008-
6	Ilona	Gray		Alliance for Environmental Concerns	(973) 595-7172	(201) 942-1406	P.O. Box 3692	Wayne	Passaic County	NJ	07474-
7	Frank	Gallager		Alliance for New Jersey Environmental Education	(201) 766-5787	(201) 766-7775	11 Hardscrabble Road, P.O. Box 693	Bernardsville	Somerset County	NJ	07924-
8	D.W.	Bennett	amlitsoc@halcyon.com	American Littoral Society	(732) 291-0055	(732) 872-8041	Sandy Hook Marine Laboratory Building #18	Highlands	Monmouth County	NJ	07732-
9	Thomas	Randall	trandall@pseg.com	American Nuclear Society - Northern New Jersey Section	(973) 761-1398	(973) 761-1015	P.O. Box 254	Mountain Lakes	Morris County	NJ	07046-
10	Stefanie	Wright		Animal Welfare Association	(609) 424-2288	(609) 424-8318	509 Marlton-Gibbsboro Road	Voorhees	Camden County	NJ	08043-
11	Lois	Leibowitz		Artzenvironment	(732) 591-2362	(732) 591-2373	522 US Highway 9, Suite 298	Manalapan	Monmouth County	NJ	07726-
12	Ellen	Glubinsky		Association of Environmental Authorities	(609) 584-1877	(609) 584-8271	2333 Whitehorse-Mercerville Road Suite 4	Mercerville	Mercer County	NJ	08619-

etc.

2. Get selected fields only

Query:

SELECT firstname, lastname FROM Contacts

Results:

First Name	Last Name
Maureen	Koplow
Adam	Weissman
Joseph	Augystyn
Fred	Bach
Carol	Elliot
Ilona	Gray
Frank	Gallager
D.W.	Bennett
Thomas	Randall
Stefanie	Wright
Lois	Leibowitz
Ellen	Glubinsky

etc.

3. Get selected records only

3.a. Exact match on character strings:

Query:

SELECT * FROM Contacts
WHERE Lastname = 'Gray'

Results:

Contact ID	First Name	Last Name	Email	Organization Name	Institution Name	phone number	Fax Number	Address	City Name	County	State Code	Postal Code
6	Ilona	Gray		Alliance for Environmental Concerns		(973) 595-7172	(201) 942-1406	P.O. Box 3692	Wayne	Passaic County	NJ	07474-

3.b. Pattern matching on character strings:

SELECT * FROM Contacts
WHERE Lastname like 'G%'

Contact ID	First Name	Last Name	Organization Name	phone number	Fax Number	Address	City Name	County	State Code	Postal Code
6	Ilona	Gray	Alliance for Environmental Concerns	(973) 595-7172	(201) 942-1406	P.O. Box 3692	Wayne	Passaic County	NJ	07474-
7	Frank	Gallager	Alliance for New Jersey Environmental Education	(201) 766-5787	(201) 766-7775	11 Hardscrabble Road, P.O. Box 693	Bernardsville	Somerset County	NJ	07924-
12	Ellen	Glubinsky	Association of Environmental Authorities	(609) 584-1877	(609) 584-8271	2333 Whitehorse-Mercerville Road Suite 4	Mercerville	Mercer County	NJ	08619-

3.c. Selection on numbers:

Query:

SELECT * FROM Contacts
WHERE YearOfBirth > 1968

3.d. Complex query with boolean operator:

Query:

SELECT * FROM Contacts
WHERE Lastname = 'Gray'
AND Firstname = 'Ilona'

Results:

Contact ID	First Name	Last Name	Email	Organization Name	Institution Name	phone number	Fax Number	Address	City Name	County	State Code	Postal Code
6	Ilona	Gray		Alliance for Environmental Concerns		(973) 595-7172	(201) 942-1406	P.O. Box 3692	Wayne	Passaic County	NJ	07474-

7. Sort the results

Query:

SELECT * FROM Contacts
WHERE Lastname LIKE 'G%'
ORDER BY Lastname

Results:

Contact ID	First Name	Last Name	Organization Name	phone number	Fax Number	Address	City Name	County	State Code	Postal Code
7	Frank	Gallager	Alliance for New Jersey Environmental Education	(201) 766-5787	(201) 766-7775	11 Hardscrabble Road, P.O. Box 693	Bernardsville	Somerset County	NJ	07924-
12	Ellen	Glubinsky	Association of Environmental Authorities	(609) 584-1877	(609) 584-8271	2333 Whitehorse-Mercerville Road Suite 4	Mercerville	Mercer County	NJ	08619-
6	Ilona	Gray	Alliance for Environmental Concerns	(973) 595-7172	(201) 942-1406	P.O. Box 3692	Wayne	Passaic County	NJ	07474-

End Note

SQL is a very powerful query language and you can do much more with it than what has been shown here.
SELECT statements can be much more sophisticated. It is also possible to create other types of statements like INSERT and DELETE, which allow respectively to add and delete records.