Q: 01-20
- Display all the information from emp table. Ans: select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION 10 accounting new york 20 research dalles 30 sales chicago 40 operations boston Ans: select * from employees;
-
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7369 SMITH CLERK 7902 12/17/1980 800 – 20 7499 ALLEN SALESMAN 7698 02/20/1981 1600 300 30 7521 WARD SALESMAN 7698 02/22/1981 1250 500 30 7566 JONES MANAGER 7839 04/02/1981 2975 – 20 7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30 7698 BLAKE MANAGER 7839 05/01/1981 2850 – 30 7782 CLARK MANAGER 7839 06/09/1981 2450 – 10 7788 SCOTT ANALYST 7566 04/19/1987 3000 – 20 7839 KING PRESIDENT – 11/17/1981 5000 – 10 7844 TURNER SALESMAN 7698 09/08/1981 1500 0 30 - Display unique jobs from EMP table. Ans: select job from employees;
-
JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN - List the details of the employees in ascending order of their salaries. Ans: select * from employees order by salary asc;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7369 SMITH CLERK 7902 12/17/1980 800 – 20 7900 JAMES CLERK 7698 12/03/1981 950 – 30 7876 ADAMS CLERK 7788 05/23/1987 1100 – 20 7521 WARD SALESMAN 7698 02/22/1981 1250 500 30 7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30 7934 MILLER CLERK 7782 01/23/1982 1300 – 10 7844 TURNER SALESMAN 7698 09/08/1981 1500 0 30 7499 ALLEN SALESMAN 7698 02/20/1981 1600 300 30 7782 CLARK MANAGER 7839 06/09/1981 2450 – 10 7698 BLAKE MANAGER 7839 05/01/1981 2850 – 30 - List the details of the employees in ascending order of the Departments_id and descending of Jobs. Ans: select * from employees order by department_id asc,job desc;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7839 KING PRESIDENT – 11/17/1981 5000 – 10 7782 CLARK MANAGER 7839 06/09/1981 2450 – 10 7934 MILLER CLERK 7782 01/23/1982 1300 – 10 7566 JONES MANAGER 7839 04/02/1981 2975 – 20 7876 ADAMS CLERK 7788 05/23/1987 1100 – 20 7369 SMITH CLERK 7902 12/17/1980 800 – 20 7788 SCOTT ANALYST 7566 04/19/1987 3000 – 20 7902 FORD ANALYST 7566 12/03/1981 3000 – 20 7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30 7844 TURNER SALESMAN 7698 09/08/1981 1500 0 30 - Display all the unique job groups within the descending order Ans: select unique job from employees order by job desc;
JOB SALESMAN PRESIDENT MANAGER CLERK ANALYST - Display all the details of all ‘Mgrs’ Ans: select * from employees where job = ‘MANAGER’;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7566 JONES MANAGER 7839 04/02/1981 2975 – 20 7698 BLAKE MANAGER 7839 05/01/1981 2850 – 30 7782 CLARK MANAGER 7839 06/09/1981 2450 – 10 - List the employees who joined before 1981. Ans : select * from employees where hiredate < ’01-01-1981′;
MPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7369 SMITH CLERK 7902 12/17/1980 800 – 20 - List the EmpID, Ename, Sal, Daily Sal of all employees in the Ascending order of AnnSal. Ans: SELECT employee_id, employee_name,salary,Salary/30 DailySalary FROM Employees ORDER BY Salary*12 ;
EMPLOYEE_ID EMPLOYEE_NAME SALARY DAILYSALARY 7369 SMITH 800 26.6666666666666666666666666666666666667 7900 JAMES 950 31.6666666666666666666666666666666666667 7876 ADAMS 1100 36.6666666666666666666666666666666666667 7521 WARD 1250 41.6666666666666666666666666666666666667 7654 MARTIN 1250 41.6666666666666666666666666666666666667 7934 MILLER 1300 43.3333333333333333333333333333333333333 7844 TURNER 1500 50 7499 ALLEN 1600 53.3333333333333333333333333333333333333 7782 CLARK 2450 81.6666666666666666666666666666666666667 7698 BLAKE 2850 95 - Display the empno , ename, job, hiredate, exp of all Mgrs from EMP table Ans:select employee_id,employee_name,salary,months_between(sysdate,hiredate)/12 Exp from employees where job = ‘MANAGER’;
EMPLOYEE_ID EMPLOYEE_NAME SALARY EXP 7566 JONES 2975 41.5666999265730784547988849064117881323 7698 BLAKE 2850 41.4860547652827558741537236160892074871 7782 CLARK 2450 41.3812160556053365193150139386698526483 - List the empno, ename, sal, exp of all employees working for Mgr 7839. Ans:select employee_id, employee_name, salary,months_between(sysdate,hiredate)/12 Exp from employees where manager_id = 7839 ;
EMPLOYEE_ID EMPLOYEE_NAME SALARY EXP 7566 JONES 2975 41.5799974798387096774193548387096774193 7698 BLAKE 2850 41.4993523185483870967741935483870967742 7782 CLARK 2450 41.3945136088709677419354838709677419355 - Display the details of the employees whose Comm. Is more than their sal. Ans: select * from employees where commission > salary;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30 - List the employees in the ascending order of Designations. Ans:select * from employees order by job ;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7788 SCOTT ANALYST 7566 04/19/1987 3000 – 20 7902 FORD ANALYST 7566 12/03/1981 3000 – 20 7934 MILLER CLERK 7782 01/23/1982 1300 – 10 7900 JAMES CLERK 7698 12/03/1981 950 – 30 7369 SMITH CLERK 7902 12/17/1980 800 – 20 7876 ADAMS CLERK 7788 05/23/1987 1100 – 20 7698 BLAKE MANAGER 7839 05/01/1981 2850 – 30 7566 JONES MANAGER 7839 04/02/1981 2975 – 20 7782 CLARK MANAGER 7839 06/09/1981 2450 – 10 7839 KING PRESIDENT – 11/17/1981 5000 – 10 - List the employees along with their experience and daily salary is more than USD 100. Ans:Select employees.*,months_between(sysdate,hiredate)/12 Exp from employees where salary/30 > 100 ;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID EXP 7839 KING PRESIDENT – 11/17/1981 5000 – 10 40.9751902877339705296694544006371963361 - List the employees who are either ‘CLERK’ or ‘ANALYST’ in the descending order Ans: Select * from employees where job in (‘CLERK’,’ANALYSt’) order by job desc ;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7369 SMITH CLERK 7902 12/17/1980 800 – 20 7934 MILLER CLERK 7782 01/23/1982 1300 – 10 7900 JAMES CLERK 7698 12/03/1981 950 – 30 7876 ADAMS CLERK 7788 05/23/1987 1100 – 20 - List the employees who joined on 1May81,31Dec81, 17Dec81, 19Jan80 in ascending order of seniority. Ans: Select employee_name,hiredate,months_between(sysdate,hiredate)/12 EXP from employees where hiredate like ’01-MAY-81′ or hiredate like ’31-DEC-81′ or hiredate like ’17-DEC-81′ or hiredate like ’19-JAN-80′ order by hiredatedesc; EMPLOYEE_N HIREDATE EXP
———- ——— ———-
BLAKE 01-MAY-81 41.5182138 - List the employees who are working for the depart no 10 or 20. Ans:select * from employees where department_id in (10,20) ;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7369 SMITH CLERK 7902 12/17/1980 800 – 20 7566 JONES MANAGER 7839 04/02/1981 2975 – 20 7782 CLARK MANAGER 7839 06/09/1981 2450 – 10 7788 SCOTT ANALYST 7566 04/19/1987 3000 – 20 7839 KING PRESIDENT – 11/17/1981 5000 – 10 7876 ADAMS CLERK 7788 05/23/1987 1100 – 20 7902 FORD ANALYST 7566 12/03/1981 3000 – 20 7934 MILLER CLERK 7782 01/23/1982 1300 – 10 - List the employees whose joining year is 1981. Ans:select * from employees where hiredate like ‘%81’;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7499 ALLEN SALESMAN 7698 02/20/1981 1600 300 30 7521 WARD SALESMAN 7698 02/22/1981 1250 500 30 7566 JONES MANAGER 7839 04/02/1981 2975 – 20 7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30 7698 BLAKE MANAGER 7839 05/01/1981 2850 – 30 7782 CLARK MANAGER 7839 06/09/1981 2450 – 10 7839 KING PRESIDENT – 11/17/1981 5000 – 10 7844 TURNER SALESMAN 7698 09/08/1981 1500 0 30 7900 JAMES CLERK 7698 12/03/1981 950 – 30 7902 FORD ANALYST 7566 12/03/1981 3000 – 20 - List the employees who are joined in the month of August 1980. Ans: no rows selected.
- List the employees whose annual salary ranging from 22000 and 45000. Ans:Select * from employees where salary*12 between 22000 and 45000;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7566 JONES MANAGER 7839 04/02/1981 2975 – 20 7698 BLAKE MANAGER 7839 05/01/1981 2850 – 30 7782 CLARK MANAGER 7839 06/09/1981 2450 – 10 7788 SCOTT ANALYST 7566 04/19/1987 3000 – 20 7902 FORD ANALYST 7566 12/03/1981 3000 – 20 - List the employees those are having five characters in their names. Ans:select * from employees where employee_name like ‘_____’;
EMPLOYEE_ID EMPLOYEE_NAME JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID 7369 SMITH CLERK 7902 12/17/1980 800 – 20 7499 ALLEN SALESMAN 7698 02/20/1981 1600 300 30 7566 JONES MANAGER 7839 04/02/1981 2975 – 20 7698 BLAKE MANAGER 7839 05/01/1981 2850 – 30 7782 CLARK MANAGER 7839 06/09/1981 2450 – 10 7788 SCOTT ANALYST 7566 04/19/1987 3000 – 20 7876 ADAMS CLERK 7788 05/23/1987 1100 – 20 7900 JAMES CLERK 7698 12/03/1981 950 – 30
Questions
 Visit Today : 245 |
 Visit Yesterday : 88 |
 This Month : 1324 |
 This Year : 25539 |