15. I will use command describe to see how I defined the table hr
SQL> describe hr;
Name Null? Type
----------------------------------------- -------- ----------------------------
NOM NOT NULL NUMBER(5)
FNAME NOT NULL VARCHAR2(15)
LNAME VARCHAR2(15)
JOB VARCHAR2(10)
MGR NUMBER(5)
HIREDATE DATE
SAL NOT NULL NUMBER(7,2)
DEPTNO NOT NULL NUMBER(3)
SQL>
this give me the name of columns, null status of column (can be empty or not), type (number, char, LOB, date, etc.) and length
16. I want to examine one special table, named dual
SQL> desc dual
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL>
looks very strange, but i will get system date and time using select from this table
17. get system date and time
SQL> set head off
SQL> select to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS') from dual;
18-Mar-2010 19:34:42
SQL>
Here i set do not see head (set head off) information, only data
18. lets complicate our select and get only few columns and people with salary > 10000
SQL> set head on
SQL> select nom, fname, lname, deptno, sal from hr where sal > 10000;
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
201 Michael Hartstein 20 13000
205 Shelley Higgins 110 12008
100 Steven King 90 24000
101 Neena Kochhar 90 17000
102 Lex De Haan 90 17000
108 Nancy Greenberg 100 12008
114 Den Raphaely 30 11000
145 John Russell 80 14000
146 Karen Partners 80 13500
147 Alberto Errazuriz 80 12000
148 Gerald Cambrault 80 11000
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
149 Eleni Zlotkey 80 10500
162 Clara Vishney 80 10500
168 Lisa Ozer 80 11500
174 Ellen Abel 80 11000
15 rows selected.
SQL>
19. I will resort the result by salary and want to see the bigger salary on the top
SQL> select nom, fname, lname, deptno, sal from hr where sal > 10000 order by sal desc;
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
100 Steven King 90 24000
101 Neena Kochhar 90 17000
102 Lex De Haan 90 17000
145 John Russell 80 14000
146 Karen Partners 80 13500
201 Michael Hartstein 20 13000
108 Nancy Greenberg 100 12008
205 Shelley Higgins 110 12008
147 Alberto Errazuriz 80 12000
168 Lisa Ozer 80 11500
174 Ellen Abel 80 11000
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
148 Gerald Cambrault 80 11000
114 Den Raphaely 30 11000
149 Eleni Zlotkey 80 10500
162 Clara Vishney 80 10500
15 rows selected.
SQL>
20. let me get the average salary for people, who have salary >10000
SQL> select avg(sal) from hr where sal >10000;
AVG(SAL)
----------
13334.4
SQL>
21. And let me get average salary, but by department
SQL> select to_char( avg(sal), '99999.99') , deptno from hr group by deptno;
TO_CHAR(A DEPTNO
--------- ----------
8601.33 100
4150.00 30
9500.00 20
10000.00 70
19333.33 90
10154.00 110
3475.56 50
6500.00 40
8955.88 80
4400.00 10
5760.00 60
11 rows selected.
SQL>
No comments:
Post a Comment