Now You Try It
Commonwealth Auto needs to be able to reach employees in case of emergency. The Human Resources department knows that not all employees have telephones. They need to have a list of telephone numbers for those employees who do have telephones.
Enter the SELECT statement to list all employees from the EMPLOYEE table who have a telephone. Display employee ID, first and last name, and telephone.
The result looks like this:
EMP_ID EMP_FNAME EMP_LNAME PHONE ------ --------- --------- ----- 4773 Janice Dexter 5083847566 3338 Mark White 6179238844 2246 Marylou Hamel 5083457789 1034 James Gallway 6172251178 2424 Ronald Wilder 5083347700 3767 Frank Lowe 5082844094 2898 Mary Umidy 6173458860 3449 Cynthia Taylor 5082684508 3082 John Brooks 5089273644 3341 Carl Smith 6179658099 4660 Bruce MacGregor 5092344620 2209 Michael Smith 6175563331 2894 William Griffin 5088449008 4001 Jason Thompson 5082649956 1765 David Alexander 5087394772 4456 Thomas Thompson 6179660089 2145 Martin Catlin 5087486625 3991 Fred Wilkins 5081840883 3778 Jane Ferndale 6173450099 4358 Judith Robinson 5087488011 4962 Peter White 6177732280 2180 Joan Albertini 5083145366 2106 Susan Widman 5083346364 3222 Louise Voltmer 6176635520 4002 Linda Roy 5088477701 2437 Henry Thompson 6179264105 2096 Thomas Carlson 6175553643 2004 Eleanor Johnson 5089253998 5103 Adele Ferguson 6176600684 5008 Timothy Fordman 6176642209 4321 George Bradley 5087463300 3764 Deborah Park 6179458377 2461 Alice Anderson 5083873664 2448 David Lynn 5082844736 1003 James Baldwin 6173295757 1234 Thomas Mills 6176646602 2466 Patricia Bennett 5089487709 4027 Cecile Courtney 5089445386 2174 Jonathan Zander 6176633854 2781 Joseph Thurston 6173286008 3704 Richard Moore 6177739440 3841 Michelle Cromwell 6173298763 3433 Herbert Crane 6178653440 3288 Ralph Sampson 6179654443 4703 Martin Halloran 6176648290 3294 Carolyn Johnson 6175567551 3118 Alan Wooding 5083766984 3769 Julie Donelson 5084850432 49 rows processed
If your results do not match what you see above, check Review Answers for Chapter 4 for the correct SQL syntax. Remember that result tables may be shortened in this guide.
BETWEEN Predicate
Use the BETWEEN predicate to specify a range of values you are searching for. BETWEEN selects all rows that have values in a specified column in between or equal to the starting or ending values of the specified range.
How It's Done
Human Resources is interested in obtaining a list of employees who have between 1 and 3 dependents being covered by their insurance plan.
To retrieve this data, enter:
select emp_id, num_dependents
from coverage
where num_dependents between 1 and 3;
The result looks like this:
EMP_ID NUM_DEPENDENTS ------ -------------- 2299 1 3411 3 3411 3 3338 2 2246 2 2246 2 3767 2 3767 2 3199 2 3199 2 2894 3 2894 3 5090 3 4456 1 1765 2 1765 2 4358 1 4358 1 3222 2 2437 2 2096 1 2096 3 2096 3 5103 1 5103 1 5008 2 5008 2 2598 1 2448 3 1003 3 1003 3 2781 2 4008 1 3704 3 3433 1 3433 1 3433 1 3288 1 3288 1 4703 1 4703 1 3118 1 42 rows processed
|
Copyright © 2014 CA.
All rights reserved.
|
|