Now You Try It
Management needs a list of all employees assigned to each department. Enter a SELECT statement to show the department ID, last name, and employee ID from the EMPLOYEE table. Order the list by last name within each department. Use the table descriptions in Review Answers for Chapter 3 to find the correct column names.
The result looks like this:
DEPT_ID EMP_LNAME EMP_ID ------- --------- ------ 1100 Fordman 5008 1100 Hamel 2246 1100 Halloran 4703 1110 Widman 2106 1110 Alexander 1765 1120 White 3338 1120 Johnson 3294 1120 Johnson 2004 1120 Umidy 2898 2200 Moore 3704 2200 Lowe 3767 2200 Albertini 2180 2200 Lynn 2448 2200 MacGregor 4660 2210 Wills 5090 2210 White 4962 2210 Park 3764 2210 Thompson 4001 2210 Courtney 4027 2210 Clark 4008 2210 Parker 2010 2210 Wilkins 3991 3510 Dexter 4773 3510 Brooks 3082 3520 Donelson 3769 3530 Smith 3341 3530 Smith 2209 4500 Zander 2174 4500 Voltmer 3222 4500 Wooding 3118 4600 Thompson 2437 4600 Gallway 1034 4600 Crane 3433 4600 Thompson 4456 4600 Carlson 2096 4600 Spade 2299 4600 Loren 3199 4600 Wilder 2424 4600 Sampson 3288 5000 Ferguson 5103 5000 Taylor 3449 5000 Bennett 2466 5100 Ferndale 3778 5100 Jacobs 2598 5200 Griffin 2894 5200 Catlin 2145 5200 Thurston 2781 5200 Williams 3411 5200 Robinson 4358 6200 Cromwell 3841 6200 Mills 1234 6200 Anderson 2461 6200 Bradley 4321 6200 Roy 4002 6200 Baldwin 1003 55 rows processed
If your results do not match what you see above, check Review Answers for Chapter 3 for the correct SQL syntax. Remember that result tables may be shortened in this guide.
Identifying columns by number
Each column named after SELECT has an assumed number corresponding to the order in which it is named. You can use this number to identify columns in the ORDER BY clause:
select emp_id, emp_fname, emp_lname from employee
▲ ▲ ▲
│ │ │
│ │ │
1 2 3
order by 3;
How It's Done
Earlier you wrote a SELECT statement to display the skills available in the company in numeric order by skill ID. Modify the ORDER BY clause in that statement to identify the column by number rather than by name:
select skill_id, skill_name
from skill
order by 1;
The result looks like this:
SKILL_ID SKILL_NAME -------- ---------- 1000 Management 1030 Acct Mgt 3065 Electronics 3088 Brake work 3333 Bodywork 4250 Data Entry 4370 Filing 4410 Typing 4420 Telephone 4430 Interviewing 4444 Assembly 4490 Gen Ledger 5130 Basic Math 5160 Calculus 5180 Statistics 5200 Gen Acctng 5309 Appraising 5420 Writing 5430 Mktng Writing 5500 Gen Mktng 6470 Window Installation 6650 Diesel Engine Repair 6666 Billing 6670 Gas Engine Repair 6770 Purchasing 7000 Sales 26 rows processed
You must refer to a column by number when the column is a calculated column like salary_amount / 52.
You can also use a column number when you select all columns from a table with SELECT *.
|
Copyright © 2014 CA.
All rights reserved.
|
|