oradata1.sql - select from emp select from...

select * from emp;
select * from dept;
-----subqueries----------
select * from emp where sal >(select avg(sal)from emp);
select * from emp where deptno=(select deptno from dept where dname='sales')re
e.
select ename,dname from emp e,dept d where e. dept d.deptno. and
d.deptno=(select deptno from dept where dname='sales');
select Avg(nvl(comm,0))from emp;
select sum(sal)from emp;
select DISTINCT(deptno)from emp;
select count(distinct(deptno))from emp;
---group by clause---
select deptno,count(*)from emp group by deptno;
select job,count(*)from emp group by job;
select deptno,min(sal),max(sal)from emp group by deptno;
select deptno from emp group by deptno;
select deptno,sum(sal)job from emp group by deptno,job;
select deptno,sum(sal),job from emp group by deptno,job;
select detpno
from emp
group by deptno, job,
select dname,sum(sal)from emp e,dept d
where e.deptno=d.deptno
group by dname;
------joins--------
1)
select d.dname,count(*)as no_of_emps
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
2)
select d.loc,to_cahr(e.hiredate,'YYYY')as year,count(*)
from emp e,dept d
where e.deptno =d.deptno,
group by d.loc,to_cahr(e.hireda,'YYYY')
order by year;
select * from emp;
select * from dept;
select deptno,dname
from dept;
select ename, emp.deptno,dname
from emp,dept
where emp.EMPNO=dept.DEPTNO
AND ename='ALLEN'
select * from emp;
select e.ename,e job,e salgrade
from emp e, dept d
where e.sal
between e low_sal = e.high_sal ;
select e.empno,e.ename,d.dname,d.deptno

from emp e, dept d
where e.deptno= d.deptno;
=========== outer join=======
select e.ename, d.dname
from emp e, dept d
where e.deptno =d.deptno(+);
=========================
<<<<<CROSS JOIN >>>>>
SELECT ENAME,DNAME
FROM EMP
CROSS JOIN DEPT;
SELECT ENAME ,