Solve All the Questions & Answers are Below.
Q 1) Display the details of all employees.
Q 2) Display the depart information from department table.
Q 3) Display the name and job for all the employees.
Q 4) Display the name and salary for all the employees.
Q5) Display the employee no and total salary for all the employees.
Q 6) Display the employee name and annual salary for all employees.
Q 7) Display the names of all the employees who are working in depart number 10.
Q 8) Display the names of all the employees who are working as clerks and drawing a salary more than 3000.
Q 9) Display the employee number and name who are earning comm.
Q 10) Display the employee number and name who do not earn any comm.
Q 11) Display the names of employees who are working as clerks, salesman or analyst and drawing a salary more than 3000.
Q 12) Display the names of the employees who are working in the company for the past 5 years.
Q 13) Display the list of employees who have joined the company before 30-JUN-90 or after 31-DEC-90.
Q 14) Display current Date.
Q 15) Display the list of all users in your database.
Q 16) Display the names of all tables from the current user;
Q 17) Display the name of the current user.
Q 18) Display the names of employees working in depart number 10 or 20 or employees working as CLERKS, SALESMAN or ANALYST.
Q 19) Display the names of employees whose name starts with alphabet S.
Q 20) Display the Employee names for employees whose name ends with alphabet S.
Q 21) Display the names of employees whose names have second alphabet A in their names.
Q 22) Select the names of the employee whose names is exactly five characters in length.
Q 23) Display the names of the employee who are not working as MANAGERS.
Q 24) Display the names of the employee who are not working as SALESMAN OR CLERK OR ANALYST.
Q 25) Display all rows from emp table.
Q 26) Display the total number of the employee working in the company.
Q 27) Display the total salary being paid to all employees.
Q 28) Display the maximum salary from emp table.
Q 29) Display the minimum salary from emp table.
Q 30) Display the average salary from emp table.
Q 31) Display the maximum salary being paid to CLERK.
Q 32) Display the maximum salary being paid to depart number 20.
Q 33) Display the minimum salary being paid to any SALESMAN.
Q 34) Display the average salary drawn by MANAGERS.
Q 35) Display the total salary drawn by ANALYST working in dept number 40.
Q 36) Display the names of the employee in order of salary i.e the name of the employee earning the lowest salary should salary appear first.
Q 37) Display the names of the employee in descending order of salary.
Q 38) Display the names of the employee in order of employee name.
Q 39) Display empno , ename , deptno , sal sort the output first base on the name and within name by deptno and within deptno by sal.
Q 40) Display the name of the employee along with their annual salary(sal*12). The name of the employee earning the highest annual salary should appear first.
Q 41) Display name,salary, hra ,pf,da,total salary for each employee. The output should be in the order of total salary, hra 15% of salary, da 10% of salary, pf 5% salary, total salary will be(salary+hra+da)-pf.
Q 42) Display depart numbers and the total number of employees working in each department.
Q 43) Display the various jobs and the total number of employees within each job group.
Q44) Display the depart numbers and total salary for each department.
Q 45) Display the depart numbers and max salary for each department.
Q 46) Display the various jobs and total salary for each job
Q 47) Display the various jobs and total salary for each job
Q 48) Display the depart numbers with more than three employees in each dept.
Q 49) Display the various jobs along with the total salary for each of the jobs where the total salary is greater than 40000.
Q 50) Display the various jobs along with the total number of employees in each job. The output should contain only those jobs with more than three employees.
Q 51) Display the name of the employee who earns the highest salary.
Q 52) Display the employee number and name for the employee working as clerk and earning highest salary among clerks.
Q 53) Display the names of the salesman who earns a salary more than the highest salary of any clerk.
Q 54) Display the names of clerks who earn a salary more than the lowest salary of any salesman.
Q 55) Display the names of employees who earn a salary more than that of Jones or that of salary greater than that of Scott.
Q 56) Display the names of the employees who earn the highest salary in their respective departments.
Q 57) Display the names of the employees who earn the highest salaries in their respective job groups.
Q 58) Display the employee names who are working in the accounting department.
Q 59) Display the employee names who are working in Chicago.
Q 60) Display the Job groups having a total salary greater than the maximum salary for managers.
Q 61) Display the names of employees from department number 10 with a salary greater than that of any employee working in other departments.
Q 62) Display the names of the employees from department number 10 with a salary greater than that of all employee working in other departments.
Q 63) Display the names of the employees in Uppercase.
Q 64) Display the names of the employees in Lowercase.
Q 65) Display the names of the employees in Proper case.
Q 66) Display the length of Your name using the appropriate function.
Q 67) Display the length of all the employee names.
Q 68) select the name of the employee to concatenate with an employee number.
Q 69) User appropriate function and extract 3 characters starting from 2 characters from the following string ‘Oracle’. i.e the out put should be ‘ac’.
Q 70) Find the First occurrence of character ‘a’ from the following string i.e ‘Computer Maintenance Corporation’.
Q 71) Display empno,ename,deptno from emp table.Instead of display department numbers display the related department name(Use decode function).
Q 72) Display your age in days.
Q 73) Display your age in months.
Q 74) Display the current date as 15th August Friday Nineteen Ninety Seven.
Q 75) Display the following output for each row from the emp table. scott has joined the company on Wednesday 13th August nineteen ninety.
Q 76) Find the date for nearest Saturday after current date.
Q 77) Display current time.
Q 78) Display the date three months Before the current date.
Q 79) Display the common jobs from department number 10 and 20
Q 80) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.
Q 81) Display the jobs which are unique to department 10
Q 82) Display the details of those who do not have any person working under them.
Q 83) Display the details of those employees who are in the sales department and grade is 3.
Q 84) Display those who are not managers and who are managers, anyone.
i)display the manager’s names
ii)display the who are not managers
Q 85) Display those employees whose name contains not less than 4 characters.
Q 86) Display those departments whose name starts with “S” while the location name ends with “K”.
Q 87) Display those employees whose manager name is JONES.
Q 88) Display those employees whose salary is more than 3000 after giving 20% increment.
Q 89) Display all employees while their dept names;
Q 90) Display ename who are working in the sales dept.
Q 91) Display employee name, dept name, salary, and comm for that sal in between 2000 to 5000 while the location in Chicago.
Q 92) Display those employees whose salary greater than his manager salary.
Q 93) Display those employees who are working in the same dept where his manager is work.
Q 94) Display those employees who are not working under any manager.
Q 95) Display grade and employees name for the dept no 10 or 30 but the grade is not 4 while joined the company before 31-Dec-82.
Q 96) Update the salary of each employee by 10% increment who are not eligible for commission.
Q 97) SELECT those employees who joined the company before 31-Dec-82 while their dept location is NewYork or Chicago.
Q 98) Display ename, job, Dname, loc for all who are working as a manager.
Q 99) Display those employees whose manager name is Jones & also display their manager name.
Q 100) Display name and salary of ford if his salary is equal to hisal of his grade.
Q 101) Display employee name, job, dname, manager name, his grade and make out an under department wise?
Q 102) List out all employees name, job, salary, grade and depart name for everyone in the company except ‘CLERK’.Sort on salary display the highest salary?
Q 103) Display the employee name, job, and his manager. Display also the employee who are without a manager?
Q 104) Find out the top 5 earners of the company?
Q 105) Display name of those employees who are getting the highest salary?
Q 106) Display those employees whose salary is equal to the average of maximum and minimum?
Q 107) Select count of an employee in each department where count greater than 3?
Q 108) Display dname where at least 3 are working and display only department name?
Q 109) Display name of those managers name whose salary is more than average salary of his company?
Q 110) Display those managers name whose salary is more than the average salary of his employee?
Q 111) Display employee name, sal, comm, and net pay for those employees whose net pay is greater than or equal to any other employee salary of the company?
Q 112) Display all employees names with a total salary of the company with each employee name?
Q 113) Find out last 5(least)earners of the company.
Q 114) Find out the number of employees whose salary is greater than their manager salary?
Q 115) Display those departments where no employee working?
Q 116) Display those employees whose salary is the ODD value?
Q 117) Display those employees whose salary contains at least 3 digits?
Q 118) Display those employees who joined in the company in the month of Dec?
Q 119) Display those employees whose name contains “A”?
Q 120) Display those employees whose deptno is available in salary?
Q 121) Display those employees whose first 2 characters from hire date -last 2 characters of salary?
Q 122) Display those employees whose 10% of salary is equal to the year of joining?
Q 123) Display those employees who are working in sales or research?
Q 124) Display the grade of jones?
Q 125) Display those employees who joined the company before 15 of the month?
Q 126) Display those employees who have joined before 15th of the month.
Q 127) Delete those records where no of employees in a particular department is less than 3.
Q 128) Display the name of the department where no employee working.
Q 129) Display those employees who are working as a manager.
Q 130) Display those employees whose grade is equal to any number of sal but not equal to the first number of sal?
Q 131) Print the details of all the employees who are Sub-ordinate to BLAKE?
Q 132) Display employee name and his salary whose salary is greater than the highest average department number?
Q 133) Display the 10th record of emp table(without using rowid)
Q 134) Display the half of the ename’s in upper case and remaining lowercase?
Q 135) Display the 10th record of emp table without using group by and rowid?
Q 136) Delete the 10th record of emp table.
Q 137) Create a copy of the emp table.
Q 138) Select ename if ename exists more than once.
Q 139) Display all enames in reverse order? (SMITH: HTIMS).
Q 140) Display those employees whose joining of month and grade is equal.
Q 141) Display those employees whose joining DATE is available in deptno.
Q 142) Display those employees name as follows
A ALLEN
B BLAKE
Q 143) List out the employees ename, sal, PF(20% OF SAL) from emp;
Q 144) Create table emp with only one column empno;
Q 145) Add this column to emp table ename vrachar2(20).
Q 146) Oops I forgot to give the primary key constraint. Add in now.
Q 147) Now increase the length of the ename column to 30 characters.
Q 148) Add salary column to emp table.
Q 149) I want to give a validation saying that salary cannot be greater 10,000 (note give a name to this constraint)
Q150) For the time being I have decided that I will not impose this validation. My boss has agreed to pay more than 10,000.
Q 151) My boss has changed his mind. Now he doesn’t want to pay more than 10,000.so revoke that salary constraint.
Q 152) Add column called as mgr to your emp table;
Q 153) Oh! This column should be related to empno. Give a command to add this constraint.
Q 154) Add deptno column to your emp table;
Q 155) This deptno column should be related to the deptno column of dept table;
Q 156) Give the command to add the constraint.
Q 157) Create a table called as new emp. Using single command create this table as well as get data into this table(use create table as);
Q 158) Create a table called as new emp1. This table should contain only empno, ename, dname.
Q 159) Delete the rows of employees who are working in the company for more than 2 years.
Q 160) Provide a commission(10% Comm Of Sal) to employees who are not earning any commission.
Q 161) If any employee has commission his commission should be incremented by 10% of his salary.
Q 162) Display employee name and department name for each employee.
Q 163) Display employee number, name, and location of the department in which he is working.
Q 164) Display ename, dname even if there are no employees working in a particular department(use outer join).
Q 165) Display employee name and his manager name.
Q 166) Display the department name and the total number of employees in each department.
Q 167) Display the department name along with total salary in each department.
Q 168) Display item name and total sales amount for each item.
Q 169) Write a Query To Delete The Repeated Rows from emp table;
Q 170) To Display 5 To 7 rows from an emp table.
Q 171) Display top n rows from emp table.
Q 172) Display Top 3 salaries from emp.
Q 173) Display 9th salary from the emp table.
Answers
Answer 1:- Select * from emp;
Answer 2:- Select * from dept;
Answer 3:- Select ename, job from emp;
Answer 4:- Select ename, sal from emp;
Answer 5:- Select empno,ename,sal,comm, sal+nvl(comm,0) as”total salary” from emp;
Answer 6:- Select ename, 12*(sal+nvl(comm,0)) as “annual Sal” from emp
Answer 7: – Select emame from emp where deptno=10;
Answer 8:- Select ename from emp where job=’CLERK’ and sal>3000;
Answer 9:- Select empno,ename from emp where comm is not null;
Answer 10: – Select empno,ename from emp where comm is null;
Answer 11:- Select ename from emp where job=’CLERK’ OR JOB=’SALESMAN’ OR JOB=’ANALYST’ AND SAL>3000;
Answer 12:- Select ename from emp where to_char(sysdate,’YYYY’)to_char(hiredate,’YYYY’)>=5;
Answer 13:- Select ename from emp where hiredate < ’30-JUN-1990′ or hiredate >’31-DEC-90′;
Answer 14:- Select sysdate from dual;
Answer 15:- Select username from all_users;
Answer 16:- Select tname from tab;
Answer 17:- Show user
Answer 18:- Select ename from emp where deptno in(10,20) or job in(‘CLERKS’,’SALESMAN’,’ANALYST’);
Answer 19: – Select ename from emp where ename like ‘S%’;
Answer 20: -Select ename from emp where ename like ‘%S’;
Answer 21:- Select ename from emp where ename like ‘_A%’;
Answer 22:- Select ename from emp where length(ename)=5;
Answer 23:- Select ename from emp where job not in(‘MANAGER’);
Answer 24:- Select ename from emp where job not in(‘SALESMAN’,’CLERK’,’ANALYST’);
Answer 25:- Select * from emp;
Answer 26:- Select count(*) from emp;
Answer 27:- Select sum(sal) from emp;
Answer 28:- Select max(sal) from emp;
Answer 29:- Select min(sal) from emp;
Answer 30:- select avg(sal) from emp;
Answer 31:-Select max(sal) from emp where job=’CLERK’;
Answer 32:-Select max(sal) from emp where deptno=20;
Answer 33:-Select min(sal) from emp where job=’SALESMAN’;
Answer 34:-Select avg(sal) from emp where job=’MANAGER’;
Answer 35:-Select sum(sal) from emp where job=’ANALYST’ and deptno=40;
Answer 36:- Select ename from emp order by sal;
Answer 37:- Select ename from emp order by sal desc;
Answer 38:- Select ename from emp order by ename;
Answer 39:- Select empno,ename,deptno,sal from emp order by
Answer 40:- Select ename,sal*12 from emp order by sal desc;
Answer 41:-Select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as da, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;
Answer 42:- Select deptno,count(deptno)from emp group by deptno;
Answer 43:- Select job,count(job)from emp group by job;
Answer 44:- Select deptno,sum(sal) from emp group by deptno;
Answer 45:- Select deptno,max(sal) from emp group by deptno;
Answer 46:- Select job,sum(sal) from emp group by job;
Answer 47:- Select job,min(sal) from emp group by job;
Answer 48:- Select deptno,count(deptno) from emp group by deptno having
count(*)>3;
Answer 49:- Select job,sum(sal) from emp group by job having sum(sal)>40000;
Answer 50:- Select job,count(empno) from emp group by job having count(job)>3;
Answer 51:- Select ename from emp where sal=(select max(sal) from emp);
Answer 52:- Select empno,ename from emp where where job=’CLERK’ and sal=(select max(sal) from emp where job=’CLERK’);
Answer 53:- Select ename,sal from emp where job=’SALESMAN’ and sal>(select max(sal) from emp where job=’CLERK’);
Answer 54:- Select ename from emp where job=’CLERK’ and sal>(select min(sal) from emp where job=’SALESMAN’);
Answer 55:- Select ename,sal from emp where sal> (select sal from emp where ename=’JONES’)and sal> (select sal from emp where ename=’SCOTT’);
Answer 56:- Select ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno);
Answer 57:- Select ename,sal,job from emp where sal in(select max(sal) from emp group by job)
Answer 58:- Select ename from emp where deptno=(select deptno from dept where dname=’ACCOUNTING’)
Answer 59:- Select ename from emp where deptno=(select deptno from dept where LOC=’CHICAGO’)
Answer 60:- Select Job, Sum(sal) from emp group by job having sum (sal) > (select max(sal) from emp where job=’MANAGER’);
Answer 61:- Select ename from emp where deptno=10 and sal>any(select sal from emp where deptno not in 10);
Answer 62:- Select ename from emp where deptno=10 and sal>all(select sal fromemp where deptno not in 10);
Answer 63:- Select upper(ename)from emp;
Answer 64:- Select lower(ename)from emp;
Answer 65:- Select initcap(ename)from emp;
Answer 66:- Select length(‘name’) from dual;
Answer 67:- Select length(ename) from emp;
Answer 68:- Select ename||empno from emp;
Answer 69:- Select substr(‘oracle’,3,2) from dual;
Answer 70:-Select instr(‘Computer Maintenance Corporation’,’a’,1) from dual;
Answer 71:- Select empno, ename, decode (deptno,10, ‘ACCOUNTING’ ,20, ‘RESEARCH’ ,30, ‘SALES’,40,’OPRATIONS’) from emp;
Answer 72:- Select to_date(sysdate)-to_date(’10-sep-77′)from dual
Answer 73:- Select months_between(sysdate,’10-sep-77′) from dual
Answer 74:- Select to_char(sysdate,’ddth Month day year’) from dual
Answer 75:- Select ename||’ has joined the company on ‘||to_char(HIREDATE,’day ddth Month year’) from emp;
Answer 76:- Select next_day(sysdate,’saturday’) from dual;
Answer 77:- Select to_char(sysdate,’hh:MM:ss’) from dual.
Answer 78:- Select add_months(sysdate,3) from dual;
Answer 79:- Select job from emp where deptno=10 and job in(select job from emp where deptno=20);
Answer 80:- Select distinct(job) from emp where deptno=10 or deptno=20;
(or)
Select distinct(job) from emp where deptno in(10,20);
Answer 81:- Select distinct(job) from emp where deptno=10
Answer 82:- Select e.ename from emp,emp e where emp.mgr=e.empno group by e.ename having count(*)=1;
Answer 83:- Select * from emp where deptno=(select deptno from dept where dname = ‘SALES’)and sal between(select losal from salgrade where grade=3) and (select hisal from salgrade where grade=3);
Answer 84:- i)display the managers names
Select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;
ii) display the who are not managers
Select ename from emp where ename not in(select distinct(m.ename) from emp e,emp m where m.empno=e.mgr);
Answer 85:- Select ename from emp where length(ename)>4;
Answer 86:- Select dname from dept where dname like ‘S%’ and loc like ‘%K’;
Answer 87:- Select p.ename from emp e,emp p where e.empno=p.mgr and e.ename=’JONES’;
Answer 88:- Select ename,sal from emp where (sal+sal*.2)>3000;
Answer 89:- Select ename,dname from emp,dept where emp.deptno=dept.deptno
Answer 90:- Select ename from emp where deptno=(select deptno from dept where dname=’SALES’);
Answer 91:- Select ename,dname,sal,comm from emp,dept where sal between 2000 and 5000 and loc=’CHICAGO’ and emp.deptno=dept.deptno;
Answer 92:- Select p.ename from emp e,emp p where e.empno=p.mgr and p.sal>e.sal;
Answer 93:- Select p.ename from emp e,emp p where e.empno=p.mgr and
p.deptno=e.deptno;
Answer 94:- Select ename from emp where mgr is null
Answer 95:- Select ename,grade from emp,salgrade where sal between losal and hisal and deptno in(10,30) and grade<>4 and hiredate<’31-DEC-82′;
Answer 96:- Update emp set sal=sal+sal*10/100 where comm is null;
Answer 97:- SELECT EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPT WHERE (EMP.DEPTNO=DEPT.DEPTNO) AND HIREDATE <’31-DEC-82′ AND DEPT.LOC IN(‘CHICAGO’,’NEW YORK’);
Answer 98:- Select ename,JOB,DNAME,LOCATION from emp,DEPT where mgr is not null;
Answer 99:- SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND E.ENAME=’JONES’;
Answer 100:- Select ename,sal,grade from emp,salgrade where sal between losal and hisal and ename =’FORD’ AND HISAL=SAL;
Answer 101:- SELECT E.ENAME,E.JOB,DNAME,EMP.ENAME,GRADE FROM EMP,EMP E,SALGRADE,DEPT WHERE EMP.SAL BETWEEN LOSAL AND HISAL AND EMP.EMPNO=E.MGR AND EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME
Answer 102:- SELECT ENAME,JOB,DNAME,SAL,GRADE FROM EMP,SALGRADE,DEPT WHERE
SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO AND JOB NOT IN(‘CLERK’)ORDER BY SAL ASC;
Answer 103:- select e.ename, e.job, emp.ename AS Manager from emp , emp e where emp.empno(+)=e.mgr
Answer 104:- SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE A.SAL>=E.SAL)ORDER BY SAL DESC;
Answer 105:- Select ename from emp where sal=(select max(sal) from emp);
Answer 106:-Select ename from emp where sal=(select max(sal)+min(sal)/2 from emp);
Answer 107:- Select count(*) from emp group by deptno having count(deptno)>3
Answer 108:- Select distinct d.dname from dept d,emp e where d.deptno=e.deptno and 3>any (select count(deptno) from emp group by deptno)
Answer 109:- SELECT E.ENAME,EMP.ENAME FROM EMP,EMP E WHERE EMP.EMPNO=E.MGR AND E.SAL>(SELECT AVG(SAL) FROM EMP);
Answer 110:- SELECT DISTINCT EMP.ENAME FROM EMP,EMP E WHERE E.SAL <(SELECT AVG(EMP.SAL) FROM EMP WHERE EMP.EMPNO=E.MGR GROUP BY EMP.ENAME) AND EMP.EMPNO=E.MGR;
Answer 111:- Select ename,sal,comm,sal+nvl(comm,0) as NetPay from emp where sal+nvl(comm,0) >any (select sal from emp)
Answer 112:- SELECT ENAME,(SELECT SUM(SAL) FROM EMP) FROM EMP;
Answer 113:- SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE A.SAL<=E.SAL) ORDER BY SAL DESC;
Answer 114:- SELECT E.ENAME FROM EMP ,EMP E WHERE EMP.EMPNO=E.MGR AND EMP.SAL<E.SAL
Answer 115:- Select dname from emp,dept where emp.deptno not in(emp.deptno)
Answer 116:- Select * from emp where sal<0;
Answer 117:- Select * from emp where length(sal)>=3;
Answer 118:- Select ename from emp where to_char(hiredate,’MON’)=’DEC’;
Answer 119:- Select ename from emp where instr(ename,’A’)>0;
or
Select ename from emp where ename like(‘%A%’);
Answer 120:- Select emp.ename from emp, emp e where emp.sal=e.deptno;
Answer 121:- Select ename,SUBSTR(hiredate,1,2)||ENAME||substr(sal,-2,2) from emp;
Answer 122:- Select ename from emp where to_char(hiredate,’YY’)=sal*0.1;
Answer 123:- SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME IN(‘SALES’,’RESEARCH’));
Answer 124:- SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND Ename=’JONES’;
Answer 125:- Select ename from emp where to_char(hiredate,’DD’)<15;
Answer 126:- Select ename from emp where to_char(hiredate,’DD’)<15;
Answer 127:- Delete from emp where deptno=(select deptno from emp group by deptno having count(deptno)<3);
Answer 128:- SELECT E.ENAME,E.JOB,M.ENAME,M.JOB FROM EMP E,EMP M WHERE E.MGR=M.EMPNO;
Answer 129:- SELECT M.ENAME MANAGER FROM EMP M ,EMP E
WHERE E.MGR=M.EMPNO GROUP BY M.ENAME;
Answer 130:- SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE GRADE NOT IN(SELECT SUBSTR(SAL,0,1)FROM EMP);
Answer 131:- Select emp.ename from emp, emp e where emp.mgr=e.empno and e.ename=’BLAKE’;
Answer 132:- SELECT SAL FROM EMP WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO);
Answer 133:- SELECT * FROM EMP WHERE ROWNUM<11
MINUS
SELECT * FROM EMP WHERE ROWNUM<10;
Answer 134:- Select Substr (Lower(ename),1,3)||substr(upper(ename),3,lenght(ename)) from emp;
Answer 135:- SELECT * FROM EMP WHERE ROWNUM<11
MINUS
SELECT * FROM EMP WHERE ROWNUM<10;
Answer 136:- Delete from emp where empno=(Select empno from emp where rownum<11
Minus
Select empno from emp where rownum<10);
Answer 137:- Create table new_table as select * from emp where 1=2;
Answer 138:- Select ename from emp e group by ename having count(*)>1;
Answer 139:-Select reverse(ename) from emp;
Answer 140:-SELECT ENAME FROM EMP WHERE SAL BETWEEN (SELECT LOSAL FROM SALGRADE WHERE GRADE=TO_CHAR(HIREDATE,’MM’)) AND (SELECT HISAL FROM SALGRADE WHERE GRADE=TO_CHAR(HIREDATE,’MM’));
Answer 141:- SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE,’DD’)=DEPTNO
Answer 142:- SELECT SUBSTR(ENAME,1,1),ENAME FROM EMP;
Answer 143:- SELECT ENAME,SAL,SAL*.2 AS PF FROM EMP;
Answer 144:- Create table emp as select empno from emp where 1=2;
Answer 145:- Alter table emp add(ename varchar2(20));
Answer 146:- Alter table emp add primary key(empno);
Answer 147:- Alter table emp modify(ename varchar2(30));
Answer 148:- Alter table emp add(sal number(10));
Answer 149:- Alter table emp add constraint chk_001 check(sal<=10000)
Answer 150:- Again alter the table or drop constraint with alter table emp drop constraint chk_001 (or)Disable the constraint by using alter table emp modify constraint chk_001 disable;
Answer 151:- Alter table emp modify constraint chk_001 enable;
Answer 152:- Alter table emp add(mgr number(5));
Answer 153:- ALTER TABLE EMP ADD CONSTRAINT MGR_DEPT FOREIGN KEY(MGR) REFERENCES EMP(EMPNO);
Answer 154:- Alter table emp add(deptno number(5));
Answer 155:- Alter table emp add constraint dept_001 foreign key(deptno) reference dept(deptno) ; [deptno should be primary key]
Answer 156:- Alter table <table_name) add constraint <constraint_name> <constraint type>;
Answer 157:- Create table newemp as select * from emp;
Answer 158:- Create table newemp as select empno,ename,dname from emp,dept where 1=2;
Answer 159:- Delete from emp where (sysdate-hiredate)/365>2;
Answer 160:- Select sal*0.1 from emp where comm is null;
Answer 161:- Update emp set comm=sal*.1 where comm is not null;
Answer 162:- Select empno,dname from emp,dept where emp.deptno=dept.deptno
Answer 163:- Select empno,ename,loc,dname from emp,dept where emp.deptno=dept.deptno;
Answer 164:-Select ename,dname from emp,dept where emp.deptno=dept.deptno(+);
Answer 165:- Select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;
Answer 166:- Select dname,count(ename) from emp,dept where emp.deptno=dept.deptno group by dname;
Answer 167:-Select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno group by dname;
Answer 168:- Select itemname,sum(amount) from item group by itemname;
Answer 169:- Delete from emp where rowid not in(select min(rowid)from emp group by ename);
Answer 170:- Select ename from emp where rowid in(select rowid from emp where rownum<=7
minus
select rowid from empi where rownum<5);
Answer 171:- SELECT * FROM (SELECT * FROM EMP ORDER BY ENAME DESC) WHERE ROWNUM <10;
Answer 172:- SELECT SAL FROM ( SELECT * FROM EMP ORDER BY SAL DESC ) WHERE ROWNUM <4
Answer 173:-SELECT ENAME FROM EMP WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10
MINUS
SELECT ROWID FROM EMP WHERE ROWNUM <10)
