Display all employees number , last name , salary , department number and the avarage salary in thieir department for all employees

select e.employee_id , e.last_name , e.department_id , avg(d.salary)
from employees e , employees d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
group by e.employee_id , e.last_name , e.department_id
order by e.DEPARTMENT_ID

Show all employees who were on the day of the week on which the highest number employees wrere hired

select employee_id ,last_name , to_char(hire_date,'day') "day"
from employees
where to_char(hire_date,'day') = 
(select to_char(hire_date,'day')
  from employees
  group by to_char(hire_date,'day')
  having count(employee_id) = 
							 (select max(count(employee_id))
							   from employees
							 group by to_char(hire_date,'day')))
order by employee_id;

Create an anniversary overview based on the hire date of the employees . sort the anniversary in ascending order

select last_name, to_char(hire_date,'month dd') BIRTHDAY
from employees
order by to_char (hire_date , 'DDD');

تم تعديل 24 مارس 200817 سنة بواسطة أحمد السيد احمد

فقط ارجوا الدعاء الكثير فى الصلاة ممن يستفيد

what is a Role ? a role is a named of related privileges that can be granted to the user . thise method makes it easier to revoke and maintain privileges . A user can have access to several roles .and several users can be assigned the same role

* create role manager :
CREATE ROLE manager;
* grant peivileges to a role :
GRANT create table , create view TO manager;
* grant a role to users :
GRANT manager TO AHMED , MOHAMMED;

مشكووووووووووووووووووووووووووررررررررر

بارك الله فيك
ونفع بك المسلمين
الله يعطيك العافية
معلومات مفيدة

Update the job and the salary of employee 114 to match the job of employee 205 and salary of employee 168

Update employees
set job_id = ( select job_id
				  from employees
				  where employee = 205) ,
  salary = (select salary
				 from employees
				 where employee_id = 168)
where employee_id = 114;

select emp_id , hiredate , salary , mgr_id from employees for employee_id whose greater than 200 and inesrt these values into the sal_history and mgr_history tables using a multitable INSERT

INESRT ALL
	 into sal_history values (empid , hiredate , sal)
	 into mgr_history values (empid,mgr,sal)
select employee_id empid , hire_date hiredate , salary sal , manager_id mgr
from employees
where employee_id > 200;

EXAMPLE of the FLASHBACK VERSION QUERY

1- select salary from employees where employee_id = 107;   result	salary = 4200
2- update employees set salary = salary * 1.30
where employee_id = 107;
  commit;
3- select salary from employees 
   versions between scn minvalue and maxvalue
  where employee_id = 107; result 5460 and 4200

تم تعديل 26 مارس 200817 سنة بواسطة أحمد السيد احمد

CONDITIONAL INSERT FIRST .. EXAMPLE

INSERT FIRST
WHEN SAL>1000	 THEN
  INTO special_sal VALUES ( DEPTID , SAL )
WHEN HIREDATE LIKE ('%99%') THEN
  INTO hiredate_history_99 VALUES( DEPTID , HIREDATE )
ELSE
INTO hiredate_history VALUES (DEPTID,HIREDATE)
SELECT DEPARTMENT_ID  DEPTID , SUM(SALARY)  SAL , MAX(HIRE_DATE) HIREDATE
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

GROUP BY ROLLUP

جرب المثال ده وشوف الناتج وهتعرف بتعمل ايه
SELECT department_id,job_id,sum(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP (department_id,job_id);