1. SQL> SELECT empno, ename FROM emp WHERE Length(ename) = 4;
2. SQL> SELECT empno, ename, job FROM emp where Length(job)=7;
3. SQL> SELECT Length('qspiders') - Length(replace('qspiders','s','')) FROM dual;
4. SQL> SELECT empno, ename, job FROM emp WHERE Instr(job,'MAN') >0;
5. SQL> SELECT empno, ename, job FROM emp WHERE Instr(job, 'MAN') =1;
6. SQL> SELECT empno, ename, job FROM emp WHERE (Length(ename) - Length(Replace(ename, 'L',''))) = 1;
7. SQL> SELECT * FROM dept WHERE Instr(dname,'O') > 0;
8. SQL> SELECT Concat(ename,' working as a ') || Concat(job, ' earns ') || Concat(sal, ' in ') || Conc
at('dept ',deptno) AS text from emp;
OR
SQL> SELECT Concat(Concat(Concat(Concat(Concat(Concat(Concat(ename,' working as a '), job),' earns '), sal),' in '),'dept '), deptno) AS text FROM emp;
9. SQL> SELECT empno, ename, sal FROM emp WHERE mod(sal,2) > 0;
Comments
and also answers for qspider questions,,,,,
i hope this will work for the 4th question
for the 4th question how could you find the job_id from emp table
there is no job_id in EMP table.hope for the next answer
u try job insted of job_id...
select * from empwhere substr(job,-3,3)='MAN';
select count(empno) from emp where comm is null;
select sum(sal),sum(comm) from emp where deptno=30;
select deptno,count(empno) no_of_clerks from emp where job='CLERK' and deptno in(20,30) group by deptno;
select deptno,sum(sal) total_salary from emp group by deptno;
select deptno,sum(sal) total_salary from emp
group by deptno
having sum(sal)>3000;
select job,sum(sal) total_sal from emp where deptno not in 30 group by job having sum(sal)>5000;
select job,max(sal) from emp where deptno in(10,20) and job in('MANAGER','CLERK','SALESMAN') group by job order by max(sal) desc;
select job,sum(sal) total_salary,count(empno) no_of_employees from emp group by job having sum(sal)>5000;
select deptno,count(empno) no_of_employees from emp group by deptno having count(empno)>=4;
select distinct deptno from emp where deptno in (select deptno from emp group by deptno having count(distinct job)=1) and job='SALESMAN';
select length('HELLLLL')-length(replace('HELLLLL','L')) no_of_L from dual;
select * from emp where instr(job,'MAN',1)>0;
select * from emp where substr(job,1,3)='MAN';
select * from emp where substr(job,-3,3)='MAN';
select ename,lower(substr(ename,1,3))||upper(substr(ename,4)) from emp;
select ename || 'is a' || job || 'and gets salary' || sal from emp;
(or)
you can also use the word "concat"
THANKS
SELECT * FROM EMP
WHERE SUBSTR(JOB,1,3)='MAN';
--will select all the employees who are mangers
SELECT * FROM EMP
WHERE SUBSTR(JOB,1)='MAN';
--says no rows selected
why is it so
select * from emp where substr(job,-3,3)='MAN';
select * from emp where substr(job,1,3)='MAN';
select * from emp where length(ename)-length(replace(ename,'L'))=1;
select *from dept where instr(dname,'O')>0;
set pages 234 lines 239
select concat(ename,concat(' working as a ',concat(job,concat(' earns ',concat(sal,concat(' in dept ',deptno)))))) from emp;
select lower(substr(ename,1,3))||substr(ename,4) from emp;
for 26th correct query is (works on oracle sql plus)
select concat(ename,concat(' is a ',concat(job,concat(' and get salary ',sal)))) from emp;