اللي عنده فكرة عن هالموضوع
يفيدني
ويفك لي هالطلاسم
elect FIRST_NAME,SALARy,salary+30 from EMPLOYEES
select FIRST_NAME,SALARy,12*salary from EMPLOYEES
select*
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
MANAGER_ID
DEPARTMENT_ID
-------------------------------------------------------------------------
select* from DEPARTMENTs
select DEPARTMENT_ID from EMPLOYEEs
describe
where EMPLOYEE_ID = 105
select FIRST_NAME,SALARy from EMPLOYEES where last_name = 'King'
-------------------------------------------------------------------------
select LAST_NAME,JOB_ID,SALARY
from EMPLOYEES
where
(JOB_ID= 'SA_REP'
Or
JOB_ID= 'AD_PRES')
and salary>15000
-------------------------------------------------------------
select EMPLOYEE_ID ,concat (FIRST_NAME ,LAST_NAME ) as
name , JOB_ID, length( LAST_NAME ),
Instr(LAST_NAME , 'a') "contains 'a' ?"
from EMPLOYEES
where substr( job_id,4) = 'REP'
select sys from dual
select round(215.926,2)trunk(45.962,2)mod(1600,300)from dual
-----------------------------------------------------------------
select LAST_NAME, JOB_ID , SALARY,
DECODE (JOB_ID , 'IT_PROG' ,1.10*salary,
'ST_CLERK' ,1.15*salary,
'SA_REP' ,1.20*salary, SALARY)
"REVISED_SALARY"
from EMPLOYEES
having
---------------------------------------
select JOB_ID , sum (SALARY) PAYROL
from EMPLOYEEs
where JOB_ID not like '%REP%'
Group by JOB_ID
having sum(salary)>10000
order by sum (salary);
--------------------------------------------
select DEPARTMENT_ID, DEPARTMENT_NAME,
LOCATION_ID CITY
FROM DEPARTMENTS
NATURAL JOIN LOCATIONS
--------------------------------------------
select E.EMPLOYEE_ID , E.LAST_NAME,
D.LOCATION_ID, DEPARTMENT_ID
FROM EMPLOYEES E JOIN DEPARTMENTS D
USING (DEPARTMENT_ID);
--------------------------------------------------
select LAST_NAME ,JOB_ID ,SALARY
FROM EMPLOYEES
where JOB_ID =(select JOB_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 104)
AND SALARY>
(select SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 143)
---------------------------------------------------------
select E.EMPLOYEE_ID , E.LAST_NAME, E.DEPARTMENT_ID,
D.LOCATION_ID, D.DEPARTMENT_ID
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID=D.DEPARTMENT_ID);
----------------------------------------------------
select LAST_NAME
FROM EMPLOYEES
where SALARY >(select SALARY
FROM EMPLOYEES
WHERE LAST_NAME = 'Abel')
-------------------------------------------------------
select LAST_NAME ,JOB_ID ,SALARY
FROM EMPLOYEES
WHERE SALARY = (select MIN (SALARY)
FROM EMPLOYEES);
---------------------------------------------------------
select EMPLOYEE_ID , LAST_NAME, JOB_ID, SALARY
from EMPLOYEES
WHERE SALARY< ALL
(select SALARY
from EMPLOYEES
WHERE JOB_ID = 'IT_PROG')
AND JOB_ID<>'IT_PROG' ;
--------------------------------------------------------------
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID ,DEPARTMENT_NAME,
MANAGER_ID ,LOCATION_ID)
VALUES (280, 'PuBLIC' , 100 ,1700);
--------------------------------------------------
select *FROM DEPARTMENTS
--------------------------------------------------
insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID)
VALUES(&DEPARTMENT_NUMBER,'&DEPARTMENT_NAME',&LOCA TION_NUMBER);
-----------------------------------
create table emp_info
(empno number (6),
empname varchar2(20),
hire_date date,
constraint emp_empno_pk primary key(empno),
constraint emp_empname_uk unique (empname));
---------------------------------------------------------------
create table DEPT_info
(DEPTNO number (5)
constraint debt_deptno_pk primary key,
deptname varchar2(20),
loc varchar2(20));
--------------------------------------------------------------------
create table emp_info
(empno number (6),
empname varchar2(20),
hire_date date,
deptno number (5),
constraint emp_empno_pk primary key(empno),
constraint emp_empno_fk foreign key (deptno)
references dept_info (deptno));
-----------------------------------------------------
create table emp_sal
(empno number (6),
salary number (5,3),
Job varchar2(10)
constraint emp1_job_nn not null,
constraint emp_sal_pk primary key(empno),
constraint emp_sal_ck check(salary>500))
-----------------------------------------------------------
create table dept80
select EMPLOYEE_ID , LAST_NAME, SALARY*12 annual, hire-DATE
from EMPLOYEES
WHERE DEPARTMENT_ID=80
------
select * from dept80
-------------------------------------------------------
create view empvu80
as
select EMPLOYEE_ID ,LAST_NAME, SALARY
from EMPLOYEES
WHERE EMPLOYEE_ID =80 ;
---------------------------------------
create view empvu80
as
select EMPLOYEE_ID ,LAST_NAME, SALARY
from EMPLOYEES
WHERE DEPARTMENT_ID=80;
-------------------------------------------------
create or replace view empvu80
as
select employee_id , last_name , salary
from employees
where department_id=80;
select * from empvu80;
-----------------------------------------------------
create or replace view empvu20
as
select * from employees
where department_id=20
with check option constraint empvu20_ck;
------------------------------------------
create or replace view empvu10
(employee_NUMBER ,employee_name ,JOB_TITLE)
AS
select employee_id , last_name , JOB_ID
from employees
where department_id=10
WHITH READ ONLY
--------------------------------------------------
with check option constraint empvu20_ck;
-------------------------------------------------------
variable emp_salary number
begin
select salary into :emp_salary
from employees where employee_id = 178;
end;
---------------------------------------------------------
set serveroutput on
declare
v_fname varchar2(25);
begin
select first_name into v_fname
from employees where employee_id = 200;
dbms_output.put_line(' first_name is' || v_fname);
end;
/
----------------------------------------------------------
set serveroutput on
declare
v_emp_hiredate employees.hire_date%type;
v_emp_salary employees.salary%type;
begin
select hire_date,salary
into v_emp_hiredate,v_emp_salary
from employees
where employee_id = 200;
dbms_output.put_line (v_emp_hiredate || v_emp_salary);
end;
/
--------------------------------------------------------------
declare
sal_increase employees.salary%type:=800;
begin
update employees
set salary = salary+ sal_increase
where job_id = 'ST_CLERK' ;
end;
/
--------------------------------------------------------
create or replace procedure query_emp
(p_id in employees.employee_id%type,
p_name out employees.last_name%type,
p_salary out employees.salary%type)
is
begin
select last_name,salary into
p_name ,p_salary
from employees
where employee_id=p_id ;
end query_emp;
/
show errors;
---------------------------------------------------------
set serveroutput on
declare
v_name employees.last_name%type;
v_salary employees.salary%type;
begin
query_emp(171,v_name,v_salary);
dbms_output.put_line (v_name|| ' his salary is'|| v_salary);
end;
----------------------------------------
create or replace function get_sal
(p_id in employees.employee_id%type) return
number is v_sal employees.salary%type:=0;
begin
select salary into v_sal
from employees
where employee_id=p_id ;
return v_sal ;
end get_sal;
/
show errors;
-------------------------------------------------------
create or replace trigger restrict_sal
before insert or update of salary on employees
for each row
begin
if not (:new.job_id in ('ad_pres','ad_vb'))
and :new.salary>15000 then
rais_application_error(-20202,'employee can't earn more than $15,000.');
end if;
end;
-----------------------------------------------------------------
UPDATE employees
set salary=15000
where last_name = 'Russell';
