EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ----------
7698 BLAKE MANAGER 7839 01-5月 -81 2850 855 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 450 30
7900 JAMES CLERK 7698 03-12月-81 950 285 30
已选择6行。
2、对HR用户的EMPLOYEES表中的每个雇员，查询显示雇员名字LAST_NAME，并计算每个雇员从被聘用起（HIRE_DATE）到今天的工作月数，对该列设置别名MONTH_WORKED。工作月数保留到整数位数，结果按工作月数升序排序。
SQL> select last_name,floor(months_between(sysdate,hire_date)) month_worked from employees order by month_worked asc;
LAST_NAME MONTH_WORKED ------------------------- ------------
Banda 137 Kumar 137 Ande 138 Markle 139 Lee 139 Geoni 140 Marvins 140 Zlotkey 140 Philtanker 140 Grant 140 Perkins 141
LAST_NAME MONTH_WORKED ------------------------- ------------
Johnson 141 Gee 141 Popp 142 Mourgos 142 Tuvault 142 Cambrault 143 Colmenares 146 Sullivan OConnell Grant Greene
LAST_NAME ------------------------- ------------
Olson Bates Jones Smith Lorentz Cabrio Landry Cambrault Himuro Sewall Mikkilineni
LAST_NAME ------------------------- ------------
Rogers Gates Vargas Dellinger McCain Feeney Livingston Walsh Patel Taylor Matos
LAST_NAME ------------------------- ------------
147 147 148 150 MONTH_WORKED 150 150 150 151 152 152 152 154 154 155 156 MONTH_WORKED 157 158 159 159 159 160 161 161 162 162 162 MONTH_WORKED
Olsen 162 Bloom 162 Seo 163 Urman 163 Fleaur 163 Pataballa 164 Fox 164 Taylor 164 Baida 165 Doran Vishney
LAST_NAME ------------------------- ------------
Stiles Atkinson Chen Vollman Sciarra Bissot Fay Dilly Hall Nayer Tobias
LAST_NAME ------------------------- ------------
Chung Austin Bernstein Hutton Fripp Ozer Errazuriz Smith Marlow Bull Everett
LAST_NAME ------------------------- ------------
Tucker Davies
165 166 MONTH_WORKED 167 167 168 168 168 169 169 169 169 170 170 MONTH_WORKED 171 171 174 174 174 174 175 175 175 175 175 MONTH_WORKED 176 176
Partners 177 Russell 180 McEwen 182 Weiss 182 Mallin 183 Abel 184 Sully 187 Hartstein 187 King 188
LAST_NAME MONTH_WORKED ------------------------- ------------
Sarchand 188 Bell 188 Rajs 191 Ladwig 194 Khoo 196 Kaufling 197 Raphaely 202 Greenberg 205 Faviet 205 Higgins 208 Baer 208
LAST_NAME MONTH_WORKED ------------------------- ------------
Mavris 208 Gietz 208 De Haan 224 Ernst 244 Hunold 261 Kochhar 264 Whalen 288 King 291
已选择107行。
3、从HR用户的EMPLOYEES表查询晚于Davies被聘用的雇员名字LAST_NAME和聘用日期HIRE_DATE。
SQL> select last_name,hire_date from employees where months_between(hire_date,(s elect hire_date from employees where last_name='Davies'))>0 order by hire_date a
sc;
LAST_NAME HIRE_DATE ------------------------- --------------
Tucker 30-1月 -97 Marlow 16-2月 -97 Bull 20-2月 -97 Everett 03-3月 -97 Smith 10-3月 -97 Errazuriz 10-3月 -97 Ozer 11-3月 -97 Hutton 19-3月 -97 Bernstein 24-3月 -97 Fripp 10-4月 -97 Chung 14-6月 -97
LAST_NAME HIRE_DATE ------------------------- --------------
Austin 25-6月 -97 Nayer 16-7月 -97 Tobias 24-7月 -97 Dilly 13-8月 -97 Fay 17-8月 -97 Bissot 20-8月 -97 Hall 20-8月 -97 Chen 28-9月 -97 Sciarra 30-9月 -97 Vollman 10-10月-97 Stiles 26-10月-97
LAST_NAME HIRE_DATE ------------------------- --------------
Atkinson 30-10月-97 Vishney 11-11月-97 Doran 15-12月-97 Baida 24-12月-97 Fox 24-1月 -98 Taylor 24-1月 -98 Pataballa 05-2月 -98 Seo 12-2月 -98 Fleaur 23-2月 -98 Urman 07-3月 -98 Matos 15-3月 -98