من يستطيع حل هذان التمرينان مع العلم أنهما مأخوذان من كتاب
introduction to sql and pl/sql
التمرين الأول
-------------------
create a query that will display the total
number of employees and of that total the
number who were hired in 1980.1981.1982
and 1983 give appropriate column headings

total 1980 1981 1982 1983
----- ----- ----- ----- -----
14 1 10 2 1

التمرين الثاني
----------------------
create a matrix query to display the job.
the salary for that job based on department
number and the total salary for that job for
all departments,giving each column an appropriate

Job Dept 10 Dept 20 Dept 30 Total
--------- ------- ------- ------- ------
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 3275
PRESIDENT 5000 5000
SALESMAN 5600 5600

you can use case or decode to solve this probleam so easy
khalid

select count(*) total,
sum(decode(to_char(hire_date,'yyyy'),1995,1,0)) "1995",
sum(decode(to_char(hire_date,'yyyy'),1996,1,0)) "1996",
sum(decode(to_char(hire_date,'yyyy'),1997,1,0)) "1997",
sum(decode(to_char(hire_date,'yyyy'),1998,1,0)) "1998"
from employees;


B

)

في ملحق خلف الكتاب فيه كل حلول التمارين للكتاب ..

select count(*),count(decode(to_char(hire_date,'yy'),'95',to_char(hire_date,'yy'))) "1995"
                      ,count(decode(to_char(hiredate,'yy'),'96',to_char(hiredate,'yy'))) "1996"
                      ,count(decode(to_char(hiredate,'yy'),'97',to_char(hiredate,'yy'))) "1997"
                      ,count(decode(to_char(hiredate,'yy'),'98',to_char(hiredate,'yy'))) "1998"
from emp

http://www.shbab1.com/2minutes.htm

تم تعديل 26 يوليو 200817 سنة بواسطة Amgad