امثلة Sql بسيطة ومفيده ان شاء الله

[أحمد السيد احمد]

بيانات الموظفين اللى ولدو بعد عام 1997 و JOB_ID = ST_CLERK .

select * from employees where job_id = 'ST_CLERK' and hire_date > '31-dec-1997';

[أحمد السيد احمد]

HR DEPARTMENT NEED AREPORT OF EMPLOYEES WHO EARN COMMISSION .SHOW THE LAST NAME ,JOB, SALARY , AND COMISSION OF THOSE EMPLOYEES . SORT THE DATA BY SALARY IN DESCENDING ORDER :

select last_name , job_id , salary , commission_pct
from employees
where commission_pct is not null
order by salary desc;

[أحمد السيد احمد]

DISPLAY THE EMPLOYEES WHO HAVE NO COMMISSIOM BUT HAVE RAISE A 10% IN SALARY .

select distinct'the salary of '||first_name||' after a 10% is '|| (salary+(salary/10)) new_salary
from employees
where commission_pct is null;

[أحمد السيد احمد]

Dsplay the last_name of all employees with the number of completed years that thay life and completed months

SELECT last_name,
	 trunc(months_between(sysdate,hire_date)/12)  years,
	 trunc(mod(months_between(sysdate,hire_date),12)  months
from employees
order by years desc, months desc;

[أحمد السيد احمد]

يا شباب انتظروا المزيد وان شاء الله ستبلغ الامثلة 100 واكثر بإذن الله اسالكم الدعاء .

[أحمد السيد احمد]

show those employees who have a last _name starting with J , K, L , M

select * 
from employees
where  substr(last_name,1,1) IN ('J','K','L','M');

[أحمد السيد احمد]

display all employees and indicate with the words YES or NO whother the receive are a commission . use DECODE expression in u'r query

select last_name , salary ,
decode (commission_pct,
				   null ,'no' ,'yes'
	) COMMISSIOM  
from employees;

[أحمد السيد احمد]

There are four codng error in thise sql statment

select empoyee_id , last_name
sal x 12 ANNUAL SALARY
from employees;

1- مفيش عمود فى employees اسمه sal العمود اسمه salary
2- علامه الضرب * مش x
3- الفاصلة بعد last_name مش موجوده
4- ال alias لازم من غير فراغ وإلا يوضع بين " " لو لازم فراغ

[أحمد السيد احمد]

Display the department_name,location,last_name,job_title and salary of those employees who work in specific location... LET THE USER DETERMINE

select d.department_name,d.location_id,e.last_name,e.job_id,e.salary
from departments d,employees e
where 
d.DEPARTMENT_ID = e.DEPARTMENT_ID
and 
d.LOCATION_ID = &inter_loc_id;

[أحمد السيد احمد]

Find the number of employees who have a last_name ends with letter n . Creat two solutions

select count(employee_id) 
from employees 
where last_name like '%n';

or

select count(*)
from employees 
where substr(last_name,-1) = 'n';

[أحمد السيد احمد]

Show the name , location , and number of employees for each department

select d.department_id,d.department_name,d.location_id,count(e.employee_id)  "count"
from departments d , employees e
where d.DEPARTMENT_ID = e.DEPARTMENT_ID
group by d.department_id,d.department_name,d.location_id 
order by d.department_id;

[أحمد السيد احمد]

Find the job_title in departments 10 and 20

select distinct job_id
from employees
where department_id in (10,20);

[أحمد السيد احمد]

Display the jobs that are found in Administration and Executive department . Also display the # of employees for these jobs . Show the job with the highest number of employees first

select distinct e.job_id , count(employee_id) FREQUENCY
from employees e,departments d 
where
e.department_id = d.department_id
and
department_name in ('Administration','Executive')
group by e.JOB_ID
order by count(employee_id) desc;

[أحمد السيد احمد]

show last_name , hire_date for each employee whose hired befor 16th of the month first half of the month

select  last_name,hire_date , to_char(hire_date,'DD') day
from employees
where to_char(hire_date,'DD') < 16
order by day desc;

[أحمد السيد احمد]

Display last_name,salary and salary expressed in terms of thousands of dollars

select last_name , salary , trunc(salary/1000) THOUSANDS
from employees

[ابن لبحر]

يرجع الاسم و الراتب للأسماء التي تبدأ ب ST

SELECT last_name,salary
FROM employees WHERE UPPER(last_name  ) LIKE 'STE%';

[أحمد السيد احمد]

Display all employees aho have manager thise managers have salary larg than 1500 .

select e.employee_id  ,e.last_name "employee_name", a.LAST_NAME "manager_name", a.salary "manager_salary"
from employees e , employees a
where a.EMPLOYEE_ID = e.MANAGER_ID
and e.MANAGER_ID is not null
and a.salary > 1500

[sameh bakkar]

مشاركه رائعه من عضو رائع .... شكرا على مجهودك

[أحمد السيد احمد]

Emp who have manager and those managers have salary large than 15000 and display grade_level

select e.employee_id  ,e.last_name "employee_name", a.LAST_NAME "manager_name", a.salary "manager_salary" , j.GRADE_LEVEL
from employees e , employees a , job_grades j
where a.EMPLOYEE_ID = e.MANAGER_ID
and e.MANAGER_ID is not null
and a.salary > 15000
and ( a.salary > lowest_sal and a.salary < highest_sal );

[أحمد السيد احمد]

Show the department_id , name , number of employees , and avarage salary 4 all departments together with the names , salaries , and job of all employees working in each department

select d.DEPARTMENT_ID ,d.DEPARTMENT_NAME , count(e1.EMPLOYEE_ID) employees,
   nvl(to_char(avg(e1.salary),'99999.99'),'no avg') avg_sal , e2.LAST_NAME , e2.SALARY , e2.JOB_ID
from departments d right outer join employees e1
on d.DEPARTMENT_ID = e1.DEPARTMENT_ID
right outer join employees e2
on d.DEPARTMENT_ID = e2.DEPARTMENT_ID
group by d.DEPARTMENT_ID , d.DEPARTMENT_NAME , e2.LAST_NAME , e2.SALARY , e2.JOB_ID
order by d.DEPARTMENT_ID , employees;

[أحمد السيد احمد]

Display the department number and lowest salary of the department who have the highest average salary

select DEPARTMENT_ID , min(salary) min_sal
from employees
group by DEPARTMENT_ID
having avg(salary) = ( select max(AVG(salary))
				   from employees 
				  group by DEPARTMENT_ID);

[أحمد السيد احمد]

Display departments where no sales representative work . Include the department number , dpartment name , and location in the output

select * 
from departments
where department_id not in (select department_id 
						from employees
						where job_id = 'SA_REP'
						and department_id is not null)

تم تعديل 21 مارس 2008 بواسطة أحمد السيد احمد

[أحمد السيد احمد]

Display department number , department name , and the number of employees for departments who have less than 3 employees

select d.DEPARTMENT_ID , d.DEPARTMENT_NAME , count(e.EMPLOYEE_ID) "count"
from departments d , employees e
where d.DEPARTMENT_ID = e.DEPARTMENT_ID
having count(e.EMPLOYEE_ID) < 3
group by d.DEPARTMENT_ID , d.DEPARTMENT_NAME 
order by d.DEPARTMENT_ID;

[أحمد السيد احمد]

Display department number , department name , count of employees for the department who have highest number of employees

select d.DEPARTMENT_ID , d.DEPARTMENT_NAME , count(e.EMPLOYEE_ID) "count"
from departments d , employees e
where d.DEPARTMENT_ID = e.DEPARTMENT_ID
having count(e.employee_id)  = (select max(count(e.employee_id)) from employees  group by department_id )
group by d.DEPARTMENT_ID , d.DEPARTMENT_NAME 
order by d.DEPARTMENT_ID;

[Wise]

جزاك الله خير اخي الكريم
استمر في الامثلة الاكثر من رائعة