Sql Queries 713f6j
This document was ed by and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this report form. Report 3b7i
Overview 3e4r5l
& View Sql Queries as PDF for free.
More details w3441
- Words: 3,243
- Pages: 30
SQL QUERRIES Date:27/08/07
Q:1 Write a query to display name, job, hiredate and employee number for each employee with employee number appearing first. SQL> SELECT empno,ename,job,hiredate 2 FROM emp; EMPNO ENAME JOB HIREDATE ---------- ---------- --------- --------7369 SMITH CLERK 17-DEC-80 7499 ALLEN SALESMAN 20-FEB-81 7521 WARD SALESMAN 22-FEB-81 7566 JONES MANAGER 02-APR-81 7654 MARTIN SALESMAN 28-SEP-81 7698 BLAKE MANAGER 01-MAY-81 7782 CLARK MANAGER 09-JUN-81 7788 SCOTT ANALYST 19-APR-87 7839 KING PRESIDENT 17-NOV-81 7844 TURNER SALESMAN 08-SEP-81 7876 ADAMS CLERK 23-MAY-87 7900 JAMES CLERK 03-DEC-81 7902 FORD ANALYST 03-DEC-81 7934 MILLER CLERK 23-JAN-82 14 rows selected.
Q2: Write a query to display unique jobs from the employee table. SQL> SELECT DISTINCT(job) 2 FROM emp; JOB --------CLERK SALESMAN PRESIDENT MANAGER ANALYST
Q3: Write a query to display the name concatenated by job seprated by comma. SQL> SELECT ename || ‘,’ || job 2 FROM emp; ENAME||','||JOB -------------------SMITH,CLERK ALLEN,SALESMAN WARD,SALESMAN JONES,MANAGER MARTIN,SALESMAN BLAKE,MANAGER CLARK,MANAGER SCOTT,ANALYST KING,PRESIDENT TURNER,SALESMAN ADAMS,CLERK JAMES,CLERK FORD,ANALYST MILLER,CLERK 14 rows selected.
Q4: Write a query to display all data from employee table separate each column by a comma and name the column THE_OUTPUT. SQL> SELECTempno||','||ename||','||job||','||mgr||','|| hiredate||','||comm||','||deptno 2 THE_OUTPUT 3 FROM emp; THE_OUTPUT -------------------------------------------------------------------------------7369,SMITH,CLERK,7902,17-DEC-80,,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,300,30 7521,WARD,SALESMAN,7698,22-FEB-81,500,30 7566,JONES,MANAGER,7839,02-APR-81,,20 7654,MARTIN,SALESMAN,7698,28-SEP-81,1400,30 7698,BLAKE,MANAGER,7839,01-MAY-81,,30 7782,CLARK,MANAGER,7839,09-JUN-81,,10 7788,SCOTT,ANALYST,7566,19-APR-87,,20 7839,KING,PRESIDENT,,17-NOV-81,,10 7844,TURNER,SALESMAN,7698,08-SEP-81,0,30 7876,ADAMS,CLERK,7788,23-MAY-87,,20 7900,JAMES,CLERK,7698,03-DEC-81,,30 7902,FORD,ANALYST,7566,03-DEC-81,,20 7934,MILLER,CLERK,7782,23-JAN-82,,10
Q 5: Write a query to display the name and salary of employees earning more than $2850. SQL> SELECT ename,sal 2 FROM emp 3 WHERE sal>2850; ENAME SAL ---------- ---------JONES 2975 SCOTT 3000 KING 5000 FORD 3000
Q6: Write a query to display the name and department number for employee number 7900. SQL> SELECT ename,deptno 2 FROM emp 3 WHERE empno = 7900; ENAME DEPTNO ---------- ---------JAMES 30
Q7: Write a query to display the name and the salary for all employees whose salary is not in the range of $1500 and $2850. SQL> SELECT ename,sal 2 FROM emp 3 WHERE sal NOT BETWEEN 1500 AND 2850; ENAME SAL ---------- ---------SMITH 800 WARD 1250 JONES 2975 MARTIN 1250 SCOTT 3000 KING 5000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 10 rows selected.
Q8: Write a query to display the employee name, job,hiredate of employees hired between Feb 20,1981 and May 1,1981. SQL> SELECT ename,job,hiredate 2 FROM emp 3 WHERE hiredate BETWEEN '20-feb-81' AND '01-may-81' 4 ORDER BY hiredate ENAME JOB HIREDATE ---------- --------- --------ALLEN SALESMAN 20-FEB-81 WARD SALESMAN 22-FEB-81 JONES MANAGER 02-APR-81 BLAKE MANAGER 01-MAY-81
Q9: Write a query to display employee name and department number of all employees in the department 10 and 30 in alphabetical order by name SQL> SELECT ename,deptno 2 FROM emp 3 WHERE deptno IN(10,30) 4 ORDER BY ename; ENAME DEPTNO ---------- ---------ALLEN 30 BLAKE 30 CLARK 10 JAMES 30 KING 10 MARTIN 30 MILLER 10 TURNER 30 WARD 30
9 rows selected.
Q10: Write a query to display the name and salary of employees who earned more than $1500 and are in the department 10 or 30. SQL> SELECT ename,sal 2 FROM emp 3 WHERE sal>1500 AND deptno in(10,30); ENAME SAL ---------- ---------ALLEN 1600 BLAKE 2850 CLARK 2450 KING 5000
Q11: Write a query to display the name and hiredate of every employee who was hired in 1981. SQL> SELECT ename,hiredate 2 FROM emp 3 WHERE hiredate LIKE '%81'; ENAME HIREDATE ---------- --------ALLEN 20-FEB-81 WARD 22-FEB-81 JONES 02-APR-81 MARTIN 28-SEP-81 BLAKE 01-MAY-81 CLARK 09-JUN-81 KING 17-NOV-81 TURNER 08-SEP-81 JAMES 03-DEC-81 FORD 03-DEC-81 10 rows selected.
Q12: Write a query to display the name and job of all employees who do not have a manger. SQL> SELECT ename ,job 2 FROM emp 3 WHERE mgr IS NULL; ENAME JOB ---------- --------KING PRESIDENT
Q13: Write a query to display the name,salary and commission for all employees who earn commission. Sort the data in descending order. SQL> SELECT ename,sal,comm 2 FROM emp 3 WHERE comm IS NOT NULL 4 ORDER BY sal DESC,comm DESC; ENAME SAL ---------- ---------- ---------ALLEN 1600 TURNER 1500 MARTIN 1250 WARD 1250
COMM 300 0 1400 500
Q14: Write a query to display the name of all employees where the third letter of their name is A. SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE '__A%'; ENAME ---------BLAKE CLARK ADAMS
Q15 : Write a query to display the names of all employees that has 2 R’s or A’s in their name and are in department number 30 or their manger is 7788. SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE '%R%R%' OR ename LIKE '%A%A%' AND (deptno=30 OR mgr=7788); ENAME ---------TURNER ADAMS
Q16: Write a query to display the name ,job and salary of all employees where job is Clerk or Analyst and their salary are not equal to 1000,3000 or 5000. SQL> SELECT ename,job,sal 2 FROM emp 3 WHERE job IN('CLERK','ANALYST') AND sal NOT IN(1000,3000,5000); ENAME JOB SAL ---------- --------- ---------SMITH CLERK 800 ADAMS CLERK 1100 JAMES CLERK 950 MILLER CLERK 1300
Q17: Write a query to display the name salary and commission for all employees whose commission amount is greater than their salary increased by 5%. SQL> SELECT ename,sal,comm 2 FROM emp 3 WHERE comm>(sal+sal*0.05); ENAME SAL COMM ---------- ---------- ---------MARTIN 1250 1400
Q18: Write a query to display the current date. SQL> SELECT sysdate 2 FROM dual; SYSDATE
--------27-AUG-07
Q19:Write a query to display the employee number, name, salary salary increased by 15% expressed as whole number. SQL> SELECT empno,ename,sal, ROUND(sal+sal*.15) increased_sal 2 FROM emp; EMPNO ENAME SAL INCREASED_SAL --------- ---------- ---------- ------------7369 SMITH 800 920 7499 ALLEN 1600 1840 7521 WARD 1250 1438 7566 JONES 2975 3421 7654 MARTIN 1250 1438 7698 BLAKE 2850 3278 7782 CLARK 2450 2818 7788 SCOTT 3000 3450 7839 KING 5000 5750 7844 TURNER 1500 1725 7876 ADAMS 1100 1265 7900 JAMES 950 1093 7902 FORD 3000 3450 7934 MILLER 1300 1495 14 rows selected.
Q20: Write a query to display the employee number, name, salary salary increased by 15% expressed as whole number and the increase in salary.
SQL> SELECT empno,ename,sal, ROUND(sal+sal*.15) inc_sal, sal*0.15 increase_in_sal 2 FROM emp; EMPNO ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934
ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
SAL INC_SAL INCREASE_IN_SAL ---------- -----------------------800 920 120 1600 1840 240 1250 1438 187.5 2975 3421 446.25 1250 1438 187.5 2850 3278 427.5 2450 2818 367.5 3000 3450 450 5000 5750 750 1500 1725 225 1100 1265 165 950 1093 142.5 3000 3450 450 1300 1495 195
14 rows selected.
Q21: Write a query to display the employee name, hiredate and salary review date which is the first Monday after six months of salary. SQL> SELECT ename,hiredate,next_day(add_months(hiredate,6),'monday') sal_review 2 FROM emp; ENAME HIREDATE -----------------SMITH 17-DEC-80 ALLEN 20-FEB-81 WARD 22-FEB-81 JONES 02-APR-81 MARTIN 28-SEP-81
SAL_REVIE --------22-JUN-81 24-AUG-81 24-AUG-81 05-OCT-81 29-MAR-82
BLAKE 01-MAY-81 CLARK 09-JUN-81 SCOTT 19-APR-87 KING 17-NOV-81 TURNER 08-SEP-81 ADAMS 23-MAY-87 JAMES 03-DEC-81 FORD 03-DEC-81 MILLER 23-JAN-82
02-NOV-81 14-DEC-81 26-OCT-87 24-MAY-82 15-MAR-82 30-NOV-87 07-JUN-82 07-JUN-82 26-JUL-82
14 rows selected.
Q22: Write a query to display the employee name and calculate the number of months between today and the date the employee was hired. SQL> SELECT ename,round(months_between(sysdate,hiredate)) Mnthsbet 2 FROM emp; ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
MNTHSBET ---------320 318 318 317 311 316 315 244 309 312 243 309 309 307
14 rows selected.
Q23: Write a query to display the following for each employee :<ename> earns <salary> monthly but wants <3 times salary> label the column as Dream Salary. SQL> SELECT ename||' earns '||sal||' monthly but wants '||3*sal Dream_Salary 2 FROM emp; DREAM_SALARY -------------------------------------------------------------------------------SMITH earns 800 monthly but wants 2400 ALLEN earns 1600 monthly but wants 4800 WARD earns 1250 monthly but wants 3750 JONES earns 2975 monthly but wants 8925 MARTIN earns 1250 monthly but wants 3750 BLAKE earns 2850 monthly but wants 8550 CLARK earns 2450 monthly but wants 7350 SCOTT earns 3000 monthly but wants 9000 KING earns 5000 monthly but wants 15000 TURNER earns 1500 monthly but wants 4500 ADAMS earns 1100 monthly but wants 3300 JAMES earns 950 monthly but wants 2850 FORD earns 3000 monthly but wants 9000 MILLER earns 1300 monthly but wants 3900 14 rows selected.
Q24: Write a query to display the name and salary for all employees. Format the salary to be 15 character long, left padded with $ sign.
SQL> SELECT INITCAP(ename) ename,LENGTH(ename) length 2 FROM emp 3 WHERE ename LIKE 'J%' OR ename LIKE 'A%' OR ename LIKE 'M %'; ENAME LENGTH ------------------Allen 5 Jones 5 Martin 6 Adams 5 James 5 Miller 6 6 rows selected.
Q25: Write a query to display the employees name with the first letter capitalized and all other letters lower case and length of their name for all employees whose name start with J,A and M. SQL> SELECT INITCAP(ename) ename,LENGTH(ename) length 2 FROM emp 3 WHERE ename LIKE 'J%' OR ename LIKE 'A%' OR ename LIKE 'M %'; ENAME ---------Allen Jones Martin Adams James Miller
LENGTH ---------5 5 6 5 5 6
6 rows selected.
Q26: Write a query to display the name, hiredate and day of the week on which the employee started. SQL> SELECT ename,hiredate, TO_CHAR(hiredate,'DAY') startday 2 FROM emp;
ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
HIREDATE STARTDAY ----------------17-DEC-80 WEDNESDAY 20-FEB-81 FRIDAY 22-FEB-81 SUNDAY 02-APR-81 THURSDAY 28-SEP-81 MONDAY 01-MAY-81 FRIDAY 09-JUN-81 TUESDAY 19-APR-87 SUNDAY 17-NOV-81 TUESDAY 08-SEP-81 TUESDAY 23-MAY-87 SATURDAY 03-DEC-81 THURSDAY 03-DEC-81 THURSDAY 23-JAN-82 SATURDAY
Q27: Write a query to display the employee name and commission amount . If the employee doesnot earn commission put ‘No Commission’. SQL>SELECT ename,DECODE(comm,0,'No Commission',NULL,'No Commission',comm) "COMM" 2 FROM emp; ENAME ---------SMITH
COMM -----------------------No Commission
ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS
300 500 No Commission 1400 No Commission No Commission No Commission No Commission No Commission No Commission
ENAME COMM ---------------------------------JAMES No Commission FORD No Commission MILLER No Commission 14 rows selected.
Q28: Write a query to display the name, department name and department number of all employees. SQL> SELECT ename,e.deptno,dname 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno; ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD
DEPTNO DNAME ----------------------20 RESEARCH 30 SALES 30 SALES 20 RESEARCH 30 SALES 30 SALES 10 ING 20 RESEARCH 10 ING 30 SALES 20 RESEARCH 30 SALES 20 RESEARCH
MILLER
10 ING
14 rows selected.
Q29: Write a querry to display unique listing of all jobs that are in department number 30. SQL> SELECT DISTINCT(job) 2 FROM emp 3 WHERE deptno=30; JOB --------SALESMAN CLERK MANAGER
Q30: Write a querry to display the employee name, department name and location for all employees who earn a commission. SQL> SELECT ename,dname,loc 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno AND comm IS NOT NULL; ENAME DNAME LOC ---------- -------------- ------------ALLEN SALES CHICAGO WARD SALES CHICAGO MARTIN SALES CHICAGO
TURNER
SALES
CHICAGO
Q31: Write a querry to display the employees name and department name of all employees who have ‘A ‘ in their name. SQL> SELECT ename,dname 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno AND ename LIKE '%A%'; ENAME DNAME ---------- -------------ALLEN SALES WARD SALES MARTIN SALES BLAKE SALES CLARK ING ADAMS RESEARCH JAMES SALES 7 rows selected.
Q32: Write a querry to display the name, job, department number for all employees who work at location DALLAS. SQL> SELECT ename,job,e.deptno,dname 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno AND loc='DALLAS'; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------SMITH CLERK 20 RESEARCH JONES MANAGER 20 RESEARCH SCOTT ANALYST 20 RESEARCH ADAMS CLERK 20 RESEARCH FORD ANALYST 20 RESEARCH
Q33: Write a query to display the employee name and employee number along with their manager’s name and manager’s number. SQL> SELECT e.ename,e.empno,m.ename mname,m.empno mgrno 2 FROM emp e,emp m 3 WHERE e.mgr=m.empno; ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT TURNER ADAMS JAMES FORD MILLER
EMPNO MNAME ------------------7369 FORD 7499 BLAKE 7521 BLAKE 7566 KING 7654 BLAKE 7698 KING 7782 KING 7788 JONES 7844 BLAKE 7876 SCOTT 7900 BLAKE 7902 JONES 7934 CLARK
MGRNO ---------7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782
13 rows selected. Q.34 Write a query to display the employee name& employee number along with their manager's name,manager's number along with the employees who donot have a manager? SQL> SELECT E.ENAME,E.EMPNO,M.ENAME,E.MGR 2 FROM EMP E LEFT OUTER EMP M 3 ON(E.MGR=M.EMPNO); ENAME EMPNO ENAME MGR ---------- ---------- ---------- ---------FORD 7902 JONES 7566
SCOTT JAMES TURNER MARTIN WARD ALLEN MILLER ADAMS CLARK BLAKE
7788 JONES 7900 BLAKE 7844 BLAKE 7654 BLAKE 7521 BLAKE 7499 BLAKE 7934 CLARK 7876 SCOTT 7782 KING 7698 KING
7566 7698 7698 7698 7698 7698 7782 7788 7839 7839
ENAME EMPNO ENAME MGR ---------- ---------- ---------- ---------JONES 7566 KING 7839 SMITH 7369 FORD 7902 KING 7839 14 rows selected.
Q35: Write a querry to display the employee name , department number and all the employee that work in the same department as the given employee. SQL> SELECT e1.ename,e1.deptno,e2.ename 2 FROM emp e1,emp e2 3 WHERE e1.deptno=e2.deptno 4 AND e1.empno>e2.empno; ENAME ---------JONES SCOTT ADAMS FORD WARD MARTIN BLAKE TURNER JAMES MARTIN BLAKE
DEPTNO ---------20 20 20 20 30 30 30 30 30 30 30
ENAME ---------SMITH SMITH SMITH SMITH ALLEN ALLEN ALLEN ALLEN ALLEN WARD WARD
ENAME ---------TURNER JAMES SCOTT ADAMS FORD BLAKE TURNER JAMES TURNER JAMES KING
DEPTNO ENAME ------------------30 WARD 30 WARD 20 JONES 20 JONES 20 JONES 30 MARTIN 30 MARTIN 30 MARTIN 30 BLAKE 30 BLAKE 10 CLARK
ENAME ---------MILLER ADAMS FORD MILLER JAMES FORD
DEPTNO ENAME ------------------10 CLARK 20 SCOTT 20 SCOTT 10 KING 30 TURNER 20 ADAMS
28 rows selected.
Q36: Write a query to display the name , job , department name, salary and grade for all employees. SQL>SELECT ename,job,dname,sal,grade 2 FROM emp e,dept d,salgrade s 3 WHERE e.deptno=d.deptno AND( sal BETWEEN losal AND hisal); ENAME JOB DNAME SAL GRADE ---------- --------- -------------- ---------- ---------SMITH CLERK RESEARCH 800 1 ADAMS CLERK RESEARCH 1100 1 JAMES CLERK SALES 950 1 WARD SALESMAN SALES 1250 2 MARTIN SALESMAN SALES 1250 2 MILLER CLERK ING 1300 2 ALLEN SALESMAN SALES 1600 3 TURNER SALESMAN SALES 1500 3 JONES MANAGER RESEARCH 2975 4
BLAKE CLARK
MANAGER SALES MANAGER ING
2850 2450
4 4
ENAME JOB DNAME SAL GRADE ---------- --------- -------------- ---------- ---------SCOTT ANALYST RESEARCH 3000 4 FORD ANALYST RESEARCH 3000 4 KING PRESIDENT ING 5000 5 14 rows selected. Q37 SQL> SELECT ename,hiredate 2 FROM emp 3 WHERE hiredate> 4 (SELECT hiredate 5 FROM emp 6 WHERE ename='BLAKE'); ENAME HIREDATE ---------- --------MARTIN 28-SEP-81 CLARK 09-JUN-81 SCOTT 19-APR-87 KING 17-NOV-81 TURNER 08-SEP-81 ADAMS 23-MAY-87 JAMES 03-DEC-81 FORD 03-DEC-81 MILLER 23-JAN-82 9 rows selected. Q38 SQL>SELECT e.ename e_name,e.hiredate e_hiredate,m.ename m_name,m.hiredate m_hiredate 2 FROM emp e,emp m 3 WHERE m.empno=e.mgr AND MONTHS_BETWEEN(e.hiredate,m.hiredate)<0; E_NAME E_HIREDAT M_NAME M_HIREDAT ---------- --------- ---------- --------SMITH 17-DEC-80 FORD 03-DEC-81 ALLEN 20-FEB-81 BLAKE 01-MAY-81 WARD 22-FEB-81 BLAKE 01-MAY-81
JONES BLAKE CLARK
02-APR-81 KING 01-MAY-81 KING 09-JUN-81 KING
17-NOV-81 17-NOV-81 17-NOV-81
6 rows selected. Q.39 Write a query to display the employees' name& the amount of the salaries of the employees are indicated through asterisks.Each asterisk signifies a hundred dollars? SQL>SELECT EMPNO||' '||RPAD('*',(ROUND(SAL/100)),'*') 2 "EMPLOYEE AND THEIR SAL" FROM EMP 3 ORDER BY SAL DESC; EMPLOYEE AND THEIR SAL -------------------------------------------------------------------------------7839 ************************************************** 7788 ****************************** 7902 ****************************** 7566 ****************************** 7698 ***************************** 7782 ************************* 7499 **************** 7844 *************** 7934 ************* 7521 ************* 7654 ************* EMPLOYEE AND THEIR SAL -------------------------------------------------------------------------------7876 *********** 7900 ********** 7369 ******** 14 rows selected.
Q40 SQL>SELECT MAX(sal) high_sal,MIN(sal) low_sal,SUM(sal) sum_sal,AVG(sal) avg_sal 2 FROM emp;
HIGH_SAL LOW_SAL SUM_SAL AVG_SAL ---------- ---------- ---------- ---------5000 800 29025 2073.21429 Q41 SQL>SELECT job,MAX(sal) max_sal,MIN(sal) min_sal,SUM(sal) sum_sal,ROUND(AVG(sal)) avg_sal 2 FROM emp 3 GROUP BY job; JOB MAX_SAL MIN_SAL SUM_SAL AVG_SAL --------- ---------- ---------- ---------- ---------ANALYST 3000 3000 6000 3000 CLERK 1300 800 4150 1038 MANAGER 2975 2450 8275 2758 PRESIDENT 5000 5000 5000 5000 SALESMAN 1600 1250 5600 1400 Q42 SQL> SELECT job,COUNT(EMPNO) employees 2 FROM emp 3 GROUP BY job; JOB EMPLOYEES --------- -----------ANALYST 2 CLERK 4 MANAGER 3 PRESIDENT 1 SALESMAN 4 Q43 SQL> SELECT COUNT (DISTINCT mgr) managers 2 FROM emp; MANAGERS ---------6 Q44 SQL> SELECT MAX(sal)-MIN(sal) diff_sal 2 FROM emp;
DIFF_SAL ---------4200 Q45 SQL> SELECT mgr,MIN(sal) m_sal 2 FROM emp 3 GROUP BY mgr 4 HAVING MIN(sal)>1000 AND mgr IS NOT NULL; MGR M_SAL ---------- ---------7566 3000 7782 1300 7788 1100 7839 2450 Q.46 Write a query to display the department name,location name,number of employees and the average salary for all employees in that department? SQL> SELECT dname,loc,COUNT(*) "Count", AVG(sal) "Avg_sal" 2 FROM DEPT d,EMP e 3 WHERE e.deptno=d.deptno 4 GROUP BY dname,loc; DNAME LOC Count Avg_sal -------------- ------------- ---------- ---------ING NEW YORK 3 2916.66667 RESEARCH DALLAS 5 2175 SALES CHICAGO 6 1566.66667
Q47 SQL> SELECT ename,hiredate 2 FROM emp 3 WHERE deptno = 4 (SELECT deptno 5 FROM emp 6 WHERE ename='BLAKE'); ENAME HIREDATE ---------- ---------
ALLEN 20-FEB-81 WARD 22-FEB-81 MARTIN 28-SEP-81 BLAKE 01-MAY-81 TURNER 08-SEP-81 JAMES 03-DEC-81 6 rows selected. Q48 SQL> SELECT empno,ename 2 FROM emp 3 WHERE sal > 4 (SELECT AVG(sal) 5 FROM emp); EMPNO ENAME ---------- ---------7566 JONES 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7902 FORD 6 rows selected. Q49 SQL> SELECT empno,ename 2 FROM emp 3 WHERE deptno = ANY 4 (SELECT deptno 5 FROM emp 6 WHERE ename LIKE '%T%'); EMPNO ENAME ---------- ---------7369 SMITH 7876 ADAMS 7902 FORD 7788 SCOTT 7566 JONES 7499 ALLEN 7698 BLAKE
7654 7900 7844 7521
MARTIN JAMES TURNER WARD
11 rows selected.
Q50 SQL> SELECT ename,sal 2 FROM emp 3 WHERE mgr = 4 (SELECT empno 5 FROM emp 6 WHERE ename='KING'); ENAME SAL ---------- ---------JONES 2975 BLAKE 2850 CLARK 2450 Q51 SQL> SELECT e.deptno,ename,job 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno AND dname='SALES'; DEPTNO ENAME JOB ---------- ---------- --------30 ALLEN SALESMAN 30 WARD SALESMAN 30 MARTIN SALESMAN 30 BLAKE MANAGER 30 TURNER SALESMAN 30 JAMES CLERK 6 rows selected. Q52 SQL>SELECT empno,ename,sal 2 FROM emp 3 WHERE sal > 4 (SELECT AVG(sal)
5 FROM emp) 6 AND deptno = ANY 7 (SELECT deptno 8 FROM emp 9 WHERE ename LIKE '%T%'); EMPNO ENAME SAL ---------- ---------- ---------7902 FORD 3000 7788 SCOTT 3000 7566 JONES 2975 7698 BLAKE 2850 Q53 SQL>SELECT ename,deptno,sal 2 FROM emp 3 WHERE deptno = ANY 4 (SELECT deptno 5 FROM emp 6 WHERE comm <>0) 7 AND 8 sal = ANY 9 (SELECT sal 10 FROM emp 11 WHERE comm <>0); ENAME DEPTNO SAL ---------- ---------- ---------MARTIN 30 1250 WARD 30 1250 ALLEN 30 1600 Q54 SQL> SELECT * 2 FROM emp 3 WHERE sal > ANY 4 (SELECT sal 5 FROM emp 6 WHERE job = 'CLERK'); EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------DEPTNO ----------
SAL
COMM
7499 ALLEN 30
SALESMAN
7698 20-FEB-81
1600
300
7521 WARD 30
SALESMAN
7698 22-FEB-81
1250
500
7566 JONES 20
MANAGER
7839 02-APR-81
EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------DEPTNO ---------7654 MARTIN SALESMAN 7698 28-SEP-81 1400 30 7698 BLAKE 30
MANAGER
7839 01-MAY-81
7782 CLARK 10
MANAGER
7839 09-JUN-81
EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------DEPTNO ---------7788 SCOTT ANALYST 7566 19-APR-87 20 7839 KING 10 7844 TURNER 30
PRESIDENT SALESMAN
17-NOV-81 7698 08-SEP-81
EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------* DEPTNO ---------7876 ADAMS CLERK 7788 23-MAY-87 20
2975
SAL
COMM
1250
2850 2450
SAL
COMM
3000 5000 1500
SAL
1100
0
COMM
7900 JAMES 30
CLERK
7902 FORD 20
ANALYST
7698 03-DEC-81 7566 03-DEC-81
950 3000
EMPNO ENAME JOB MGR HIREDATE SAL ---------- ---------- --------- ---------- --------- ---------- ---------DEPTNO ---------7934 MILLER CLERK 7782 23-JAN-82 1300 10 13 rows selected.
COMM
Q:1 Write a query to display name, job, hiredate and employee number for each employee with employee number appearing first. SQL> SELECT empno,ename,job,hiredate 2 FROM emp; EMPNO ENAME JOB HIREDATE ---------- ---------- --------- --------7369 SMITH CLERK 17-DEC-80 7499 ALLEN SALESMAN 20-FEB-81 7521 WARD SALESMAN 22-FEB-81 7566 JONES MANAGER 02-APR-81 7654 MARTIN SALESMAN 28-SEP-81 7698 BLAKE MANAGER 01-MAY-81 7782 CLARK MANAGER 09-JUN-81 7788 SCOTT ANALYST 19-APR-87 7839 KING PRESIDENT 17-NOV-81 7844 TURNER SALESMAN 08-SEP-81 7876 ADAMS CLERK 23-MAY-87 7900 JAMES CLERK 03-DEC-81 7902 FORD ANALYST 03-DEC-81 7934 MILLER CLERK 23-JAN-82 14 rows selected.
Q2: Write a query to display unique jobs from the employee table. SQL> SELECT DISTINCT(job) 2 FROM emp; JOB --------CLERK SALESMAN PRESIDENT MANAGER ANALYST
Q3: Write a query to display the name concatenated by job seprated by comma. SQL> SELECT ename || ‘,’ || job 2 FROM emp; ENAME||','||JOB -------------------SMITH,CLERK ALLEN,SALESMAN WARD,SALESMAN JONES,MANAGER MARTIN,SALESMAN BLAKE,MANAGER CLARK,MANAGER SCOTT,ANALYST KING,PRESIDENT TURNER,SALESMAN ADAMS,CLERK JAMES,CLERK FORD,ANALYST MILLER,CLERK 14 rows selected.
Q4: Write a query to display all data from employee table separate each column by a comma and name the column THE_OUTPUT. SQL> SELECTempno||','||ename||','||job||','||mgr||','|| hiredate||','||comm||','||deptno 2 THE_OUTPUT 3 FROM emp; THE_OUTPUT -------------------------------------------------------------------------------7369,SMITH,CLERK,7902,17-DEC-80,,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,300,30 7521,WARD,SALESMAN,7698,22-FEB-81,500,30 7566,JONES,MANAGER,7839,02-APR-81,,20 7654,MARTIN,SALESMAN,7698,28-SEP-81,1400,30 7698,BLAKE,MANAGER,7839,01-MAY-81,,30 7782,CLARK,MANAGER,7839,09-JUN-81,,10 7788,SCOTT,ANALYST,7566,19-APR-87,,20 7839,KING,PRESIDENT,,17-NOV-81,,10 7844,TURNER,SALESMAN,7698,08-SEP-81,0,30 7876,ADAMS,CLERK,7788,23-MAY-87,,20 7900,JAMES,CLERK,7698,03-DEC-81,,30 7902,FORD,ANALYST,7566,03-DEC-81,,20 7934,MILLER,CLERK,7782,23-JAN-82,,10
Q 5: Write a query to display the name and salary of employees earning more than $2850. SQL> SELECT ename,sal 2 FROM emp 3 WHERE sal>2850; ENAME SAL ---------- ---------JONES 2975 SCOTT 3000 KING 5000 FORD 3000
Q6: Write a query to display the name and department number for employee number 7900. SQL> SELECT ename,deptno 2 FROM emp 3 WHERE empno = 7900; ENAME DEPTNO ---------- ---------JAMES 30
Q7: Write a query to display the name and the salary for all employees whose salary is not in the range of $1500 and $2850. SQL> SELECT ename,sal 2 FROM emp 3 WHERE sal NOT BETWEEN 1500 AND 2850; ENAME SAL ---------- ---------SMITH 800 WARD 1250 JONES 2975 MARTIN 1250 SCOTT 3000 KING 5000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 10 rows selected.
Q8: Write a query to display the employee name, job,hiredate of employees hired between Feb 20,1981 and May 1,1981. SQL> SELECT ename,job,hiredate 2 FROM emp 3 WHERE hiredate BETWEEN '20-feb-81' AND '01-may-81' 4 ORDER BY hiredate ENAME JOB HIREDATE ---------- --------- --------ALLEN SALESMAN 20-FEB-81 WARD SALESMAN 22-FEB-81 JONES MANAGER 02-APR-81 BLAKE MANAGER 01-MAY-81
Q9: Write a query to display employee name and department number of all employees in the department 10 and 30 in alphabetical order by name SQL> SELECT ename,deptno 2 FROM emp 3 WHERE deptno IN(10,30) 4 ORDER BY ename; ENAME DEPTNO ---------- ---------ALLEN 30 BLAKE 30 CLARK 10 JAMES 30 KING 10 MARTIN 30 MILLER 10 TURNER 30 WARD 30
9 rows selected.
Q10: Write a query to display the name and salary of employees who earned more than $1500 and are in the department 10 or 30. SQL> SELECT ename,sal 2 FROM emp 3 WHERE sal>1500 AND deptno in(10,30); ENAME SAL ---------- ---------ALLEN 1600 BLAKE 2850 CLARK 2450 KING 5000
Q11: Write a query to display the name and hiredate of every employee who was hired in 1981. SQL> SELECT ename,hiredate 2 FROM emp 3 WHERE hiredate LIKE '%81'; ENAME HIREDATE ---------- --------ALLEN 20-FEB-81 WARD 22-FEB-81 JONES 02-APR-81 MARTIN 28-SEP-81 BLAKE 01-MAY-81 CLARK 09-JUN-81 KING 17-NOV-81 TURNER 08-SEP-81 JAMES 03-DEC-81 FORD 03-DEC-81 10 rows selected.
Q12: Write a query to display the name and job of all employees who do not have a manger. SQL> SELECT ename ,job 2 FROM emp 3 WHERE mgr IS NULL; ENAME JOB ---------- --------KING PRESIDENT
Q13: Write a query to display the name,salary and commission for all employees who earn commission. Sort the data in descending order. SQL> SELECT ename,sal,comm 2 FROM emp 3 WHERE comm IS NOT NULL 4 ORDER BY sal DESC,comm DESC; ENAME SAL ---------- ---------- ---------ALLEN 1600 TURNER 1500 MARTIN 1250 WARD 1250
COMM 300 0 1400 500
Q14: Write a query to display the name of all employees where the third letter of their name is A. SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE '__A%'; ENAME ---------BLAKE CLARK ADAMS
Q15 : Write a query to display the names of all employees that has 2 R’s or A’s in their name and are in department number 30 or their manger is 7788. SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE '%R%R%' OR ename LIKE '%A%A%' AND (deptno=30 OR mgr=7788); ENAME ---------TURNER ADAMS
Q16: Write a query to display the name ,job and salary of all employees where job is Clerk or Analyst and their salary are not equal to 1000,3000 or 5000. SQL> SELECT ename,job,sal 2 FROM emp 3 WHERE job IN('CLERK','ANALYST') AND sal NOT IN(1000,3000,5000); ENAME JOB SAL ---------- --------- ---------SMITH CLERK 800 ADAMS CLERK 1100 JAMES CLERK 950 MILLER CLERK 1300
Q17: Write a query to display the name salary and commission for all employees whose commission amount is greater than their salary increased by 5%. SQL> SELECT ename,sal,comm 2 FROM emp 3 WHERE comm>(sal+sal*0.05); ENAME SAL COMM ---------- ---------- ---------MARTIN 1250 1400
Q18: Write a query to display the current date. SQL> SELECT sysdate 2 FROM dual; SYSDATE
--------27-AUG-07
Q19:Write a query to display the employee number, name, salary salary increased by 15% expressed as whole number. SQL> SELECT empno,ename,sal, ROUND(sal+sal*.15) increased_sal 2 FROM emp; EMPNO ENAME SAL INCREASED_SAL --------- ---------- ---------- ------------7369 SMITH 800 920 7499 ALLEN 1600 1840 7521 WARD 1250 1438 7566 JONES 2975 3421 7654 MARTIN 1250 1438 7698 BLAKE 2850 3278 7782 CLARK 2450 2818 7788 SCOTT 3000 3450 7839 KING 5000 5750 7844 TURNER 1500 1725 7876 ADAMS 1100 1265 7900 JAMES 950 1093 7902 FORD 3000 3450 7934 MILLER 1300 1495 14 rows selected.
Q20: Write a query to display the employee number, name, salary salary increased by 15% expressed as whole number and the increase in salary.
SQL> SELECT empno,ename,sal, ROUND(sal+sal*.15) inc_sal, sal*0.15 increase_in_sal 2 FROM emp; EMPNO ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934
ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
SAL INC_SAL INCREASE_IN_SAL ---------- -----------------------800 920 120 1600 1840 240 1250 1438 187.5 2975 3421 446.25 1250 1438 187.5 2850 3278 427.5 2450 2818 367.5 3000 3450 450 5000 5750 750 1500 1725 225 1100 1265 165 950 1093 142.5 3000 3450 450 1300 1495 195
14 rows selected.
Q21: Write a query to display the employee name, hiredate and salary review date which is the first Monday after six months of salary. SQL> SELECT ename,hiredate,next_day(add_months(hiredate,6),'monday') sal_review 2 FROM emp; ENAME HIREDATE -----------------SMITH 17-DEC-80 ALLEN 20-FEB-81 WARD 22-FEB-81 JONES 02-APR-81 MARTIN 28-SEP-81
SAL_REVIE --------22-JUN-81 24-AUG-81 24-AUG-81 05-OCT-81 29-MAR-82
BLAKE 01-MAY-81 CLARK 09-JUN-81 SCOTT 19-APR-87 KING 17-NOV-81 TURNER 08-SEP-81 ADAMS 23-MAY-87 JAMES 03-DEC-81 FORD 03-DEC-81 MILLER 23-JAN-82
02-NOV-81 14-DEC-81 26-OCT-87 24-MAY-82 15-MAR-82 30-NOV-87 07-JUN-82 07-JUN-82 26-JUL-82
14 rows selected.
Q22: Write a query to display the employee name and calculate the number of months between today and the date the employee was hired. SQL> SELECT ename,round(months_between(sysdate,hiredate)) Mnthsbet 2 FROM emp; ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
MNTHSBET ---------320 318 318 317 311 316 315 244 309 312 243 309 309 307
14 rows selected.
Q23: Write a query to display the following for each employee :<ename> earns <salary> monthly but wants <3 times salary> label the column as Dream Salary. SQL> SELECT ename||' earns '||sal||' monthly but wants '||3*sal Dream_Salary 2 FROM emp; DREAM_SALARY -------------------------------------------------------------------------------SMITH earns 800 monthly but wants 2400 ALLEN earns 1600 monthly but wants 4800 WARD earns 1250 monthly but wants 3750 JONES earns 2975 monthly but wants 8925 MARTIN earns 1250 monthly but wants 3750 BLAKE earns 2850 monthly but wants 8550 CLARK earns 2450 monthly but wants 7350 SCOTT earns 3000 monthly but wants 9000 KING earns 5000 monthly but wants 15000 TURNER earns 1500 monthly but wants 4500 ADAMS earns 1100 monthly but wants 3300 JAMES earns 950 monthly but wants 2850 FORD earns 3000 monthly but wants 9000 MILLER earns 1300 monthly but wants 3900 14 rows selected.
Q24: Write a query to display the name and salary for all employees. Format the salary to be 15 character long, left padded with $ sign.
SQL> SELECT INITCAP(ename) ename,LENGTH(ename) length 2 FROM emp 3 WHERE ename LIKE 'J%' OR ename LIKE 'A%' OR ename LIKE 'M %'; ENAME LENGTH ------------------Allen 5 Jones 5 Martin 6 Adams 5 James 5 Miller 6 6 rows selected.
Q25: Write a query to display the employees name with the first letter capitalized and all other letters lower case and length of their name for all employees whose name start with J,A and M. SQL> SELECT INITCAP(ename) ename,LENGTH(ename) length 2 FROM emp 3 WHERE ename LIKE 'J%' OR ename LIKE 'A%' OR ename LIKE 'M %'; ENAME ---------Allen Jones Martin Adams James Miller
LENGTH ---------5 5 6 5 5 6
6 rows selected.
Q26: Write a query to display the name, hiredate and day of the week on which the employee started. SQL> SELECT ename,hiredate, TO_CHAR(hiredate,'DAY') startday 2 FROM emp;
ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
HIREDATE STARTDAY ----------------17-DEC-80 WEDNESDAY 20-FEB-81 FRIDAY 22-FEB-81 SUNDAY 02-APR-81 THURSDAY 28-SEP-81 MONDAY 01-MAY-81 FRIDAY 09-JUN-81 TUESDAY 19-APR-87 SUNDAY 17-NOV-81 TUESDAY 08-SEP-81 TUESDAY 23-MAY-87 SATURDAY 03-DEC-81 THURSDAY 03-DEC-81 THURSDAY 23-JAN-82 SATURDAY
Q27: Write a query to display the employee name and commission amount . If the employee doesnot earn commission put ‘No Commission’. SQL>SELECT ename,DECODE(comm,0,'No Commission',NULL,'No Commission',comm) "COMM" 2 FROM emp; ENAME ---------SMITH
COMM -----------------------No Commission
ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS
300 500 No Commission 1400 No Commission No Commission No Commission No Commission No Commission No Commission
ENAME COMM ---------------------------------JAMES No Commission FORD No Commission MILLER No Commission 14 rows selected.
Q28: Write a query to display the name, department name and department number of all employees. SQL> SELECT ename,e.deptno,dname 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno; ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD
DEPTNO DNAME ----------------------20 RESEARCH 30 SALES 30 SALES 20 RESEARCH 30 SALES 30 SALES 10 ING 20 RESEARCH 10 ING 30 SALES 20 RESEARCH 30 SALES 20 RESEARCH
MILLER
10 ING
14 rows selected.
Q29: Write a querry to display unique listing of all jobs that are in department number 30. SQL> SELECT DISTINCT(job) 2 FROM emp 3 WHERE deptno=30; JOB --------SALESMAN CLERK MANAGER
Q30: Write a querry to display the employee name, department name and location for all employees who earn a commission. SQL> SELECT ename,dname,loc 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno AND comm IS NOT NULL; ENAME DNAME LOC ---------- -------------- ------------ALLEN SALES CHICAGO WARD SALES CHICAGO MARTIN SALES CHICAGO
TURNER
SALES
CHICAGO
Q31: Write a querry to display the employees name and department name of all employees who have ‘A ‘ in their name. SQL> SELECT ename,dname 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno AND ename LIKE '%A%'; ENAME DNAME ---------- -------------ALLEN SALES WARD SALES MARTIN SALES BLAKE SALES CLARK ING ADAMS RESEARCH JAMES SALES 7 rows selected.
Q32: Write a querry to display the name, job, department number for all employees who work at location DALLAS. SQL> SELECT ename,job,e.deptno,dname 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno AND loc='DALLAS'; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------SMITH CLERK 20 RESEARCH JONES MANAGER 20 RESEARCH SCOTT ANALYST 20 RESEARCH ADAMS CLERK 20 RESEARCH FORD ANALYST 20 RESEARCH
Q33: Write a query to display the employee name and employee number along with their manager’s name and manager’s number. SQL> SELECT e.ename,e.empno,m.ename mname,m.empno mgrno 2 FROM emp e,emp m 3 WHERE e.mgr=m.empno; ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT TURNER ADAMS JAMES FORD MILLER
EMPNO MNAME ------------------7369 FORD 7499 BLAKE 7521 BLAKE 7566 KING 7654 BLAKE 7698 KING 7782 KING 7788 JONES 7844 BLAKE 7876 SCOTT 7900 BLAKE 7902 JONES 7934 CLARK
MGRNO ---------7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782
13 rows selected. Q.34 Write a query to display the employee name& employee number along with their manager's name,manager's number along with the employees who donot have a manager? SQL> SELECT E.ENAME,E.EMPNO,M.ENAME,E.MGR 2 FROM EMP E LEFT OUTER EMP M 3 ON(E.MGR=M.EMPNO); ENAME EMPNO ENAME MGR ---------- ---------- ---------- ---------FORD 7902 JONES 7566
SCOTT JAMES TURNER MARTIN WARD ALLEN MILLER ADAMS CLARK BLAKE
7788 JONES 7900 BLAKE 7844 BLAKE 7654 BLAKE 7521 BLAKE 7499 BLAKE 7934 CLARK 7876 SCOTT 7782 KING 7698 KING
7566 7698 7698 7698 7698 7698 7782 7788 7839 7839
ENAME EMPNO ENAME MGR ---------- ---------- ---------- ---------JONES 7566 KING 7839 SMITH 7369 FORD 7902 KING 7839 14 rows selected.
Q35: Write a querry to display the employee name , department number and all the employee that work in the same department as the given employee. SQL> SELECT e1.ename,e1.deptno,e2.ename 2 FROM emp e1,emp e2 3 WHERE e1.deptno=e2.deptno 4 AND e1.empno>e2.empno; ENAME ---------JONES SCOTT ADAMS FORD WARD MARTIN BLAKE TURNER JAMES MARTIN BLAKE
DEPTNO ---------20 20 20 20 30 30 30 30 30 30 30
ENAME ---------SMITH SMITH SMITH SMITH ALLEN ALLEN ALLEN ALLEN ALLEN WARD WARD
ENAME ---------TURNER JAMES SCOTT ADAMS FORD BLAKE TURNER JAMES TURNER JAMES KING
DEPTNO ENAME ------------------30 WARD 30 WARD 20 JONES 20 JONES 20 JONES 30 MARTIN 30 MARTIN 30 MARTIN 30 BLAKE 30 BLAKE 10 CLARK
ENAME ---------MILLER ADAMS FORD MILLER JAMES FORD
DEPTNO ENAME ------------------10 CLARK 20 SCOTT 20 SCOTT 10 KING 30 TURNER 20 ADAMS
28 rows selected.
Q36: Write a query to display the name , job , department name, salary and grade for all employees. SQL>SELECT ename,job,dname,sal,grade 2 FROM emp e,dept d,salgrade s 3 WHERE e.deptno=d.deptno AND( sal BETWEEN losal AND hisal); ENAME JOB DNAME SAL GRADE ---------- --------- -------------- ---------- ---------SMITH CLERK RESEARCH 800 1 ADAMS CLERK RESEARCH 1100 1 JAMES CLERK SALES 950 1 WARD SALESMAN SALES 1250 2 MARTIN SALESMAN SALES 1250 2 MILLER CLERK ING 1300 2 ALLEN SALESMAN SALES 1600 3 TURNER SALESMAN SALES 1500 3 JONES MANAGER RESEARCH 2975 4
BLAKE CLARK
MANAGER SALES MANAGER ING
2850 2450
4 4
ENAME JOB DNAME SAL GRADE ---------- --------- -------------- ---------- ---------SCOTT ANALYST RESEARCH 3000 4 FORD ANALYST RESEARCH 3000 4 KING PRESIDENT ING 5000 5 14 rows selected. Q37 SQL> SELECT ename,hiredate 2 FROM emp 3 WHERE hiredate> 4 (SELECT hiredate 5 FROM emp 6 WHERE ename='BLAKE'); ENAME HIREDATE ---------- --------MARTIN 28-SEP-81 CLARK 09-JUN-81 SCOTT 19-APR-87 KING 17-NOV-81 TURNER 08-SEP-81 ADAMS 23-MAY-87 JAMES 03-DEC-81 FORD 03-DEC-81 MILLER 23-JAN-82 9 rows selected. Q38 SQL>SELECT e.ename e_name,e.hiredate e_hiredate,m.ename m_name,m.hiredate m_hiredate 2 FROM emp e,emp m 3 WHERE m.empno=e.mgr AND MONTHS_BETWEEN(e.hiredate,m.hiredate)<0; E_NAME E_HIREDAT M_NAME M_HIREDAT ---------- --------- ---------- --------SMITH 17-DEC-80 FORD 03-DEC-81 ALLEN 20-FEB-81 BLAKE 01-MAY-81 WARD 22-FEB-81 BLAKE 01-MAY-81
JONES BLAKE CLARK
02-APR-81 KING 01-MAY-81 KING 09-JUN-81 KING
17-NOV-81 17-NOV-81 17-NOV-81
6 rows selected. Q.39 Write a query to display the employees' name& the amount of the salaries of the employees are indicated through asterisks.Each asterisk signifies a hundred dollars? SQL>SELECT EMPNO||' '||RPAD('*',(ROUND(SAL/100)),'*') 2 "EMPLOYEE AND THEIR SAL" FROM EMP 3 ORDER BY SAL DESC; EMPLOYEE AND THEIR SAL -------------------------------------------------------------------------------7839 ************************************************** 7788 ****************************** 7902 ****************************** 7566 ****************************** 7698 ***************************** 7782 ************************* 7499 **************** 7844 *************** 7934 ************* 7521 ************* 7654 ************* EMPLOYEE AND THEIR SAL -------------------------------------------------------------------------------7876 *********** 7900 ********** 7369 ******** 14 rows selected.
Q40 SQL>SELECT MAX(sal) high_sal,MIN(sal) low_sal,SUM(sal) sum_sal,AVG(sal) avg_sal 2 FROM emp;
HIGH_SAL LOW_SAL SUM_SAL AVG_SAL ---------- ---------- ---------- ---------5000 800 29025 2073.21429 Q41 SQL>SELECT job,MAX(sal) max_sal,MIN(sal) min_sal,SUM(sal) sum_sal,ROUND(AVG(sal)) avg_sal 2 FROM emp 3 GROUP BY job; JOB MAX_SAL MIN_SAL SUM_SAL AVG_SAL --------- ---------- ---------- ---------- ---------ANALYST 3000 3000 6000 3000 CLERK 1300 800 4150 1038 MANAGER 2975 2450 8275 2758 PRESIDENT 5000 5000 5000 5000 SALESMAN 1600 1250 5600 1400 Q42 SQL> SELECT job,COUNT(EMPNO) employees 2 FROM emp 3 GROUP BY job; JOB EMPLOYEES --------- -----------ANALYST 2 CLERK 4 MANAGER 3 PRESIDENT 1 SALESMAN 4 Q43 SQL> SELECT COUNT (DISTINCT mgr) managers 2 FROM emp; MANAGERS ---------6 Q44 SQL> SELECT MAX(sal)-MIN(sal) diff_sal 2 FROM emp;
DIFF_SAL ---------4200 Q45 SQL> SELECT mgr,MIN(sal) m_sal 2 FROM emp 3 GROUP BY mgr 4 HAVING MIN(sal)>1000 AND mgr IS NOT NULL; MGR M_SAL ---------- ---------7566 3000 7782 1300 7788 1100 7839 2450 Q.46 Write a query to display the department name,location name,number of employees and the average salary for all employees in that department? SQL> SELECT dname,loc,COUNT(*) "Count", AVG(sal) "Avg_sal" 2 FROM DEPT d,EMP e 3 WHERE e.deptno=d.deptno 4 GROUP BY dname,loc; DNAME LOC Count Avg_sal -------------- ------------- ---------- ---------ING NEW YORK 3 2916.66667 RESEARCH DALLAS 5 2175 SALES CHICAGO 6 1566.66667
Q47 SQL> SELECT ename,hiredate 2 FROM emp 3 WHERE deptno = 4 (SELECT deptno 5 FROM emp 6 WHERE ename='BLAKE'); ENAME HIREDATE ---------- ---------
ALLEN 20-FEB-81 WARD 22-FEB-81 MARTIN 28-SEP-81 BLAKE 01-MAY-81 TURNER 08-SEP-81 JAMES 03-DEC-81 6 rows selected. Q48 SQL> SELECT empno,ename 2 FROM emp 3 WHERE sal > 4 (SELECT AVG(sal) 5 FROM emp); EMPNO ENAME ---------- ---------7566 JONES 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7902 FORD 6 rows selected. Q49 SQL> SELECT empno,ename 2 FROM emp 3 WHERE deptno = ANY 4 (SELECT deptno 5 FROM emp 6 WHERE ename LIKE '%T%'); EMPNO ENAME ---------- ---------7369 SMITH 7876 ADAMS 7902 FORD 7788 SCOTT 7566 JONES 7499 ALLEN 7698 BLAKE
7654 7900 7844 7521
MARTIN JAMES TURNER WARD
11 rows selected.
Q50 SQL> SELECT ename,sal 2 FROM emp 3 WHERE mgr = 4 (SELECT empno 5 FROM emp 6 WHERE ename='KING'); ENAME SAL ---------- ---------JONES 2975 BLAKE 2850 CLARK 2450 Q51 SQL> SELECT e.deptno,ename,job 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno AND dname='SALES'; DEPTNO ENAME JOB ---------- ---------- --------30 ALLEN SALESMAN 30 WARD SALESMAN 30 MARTIN SALESMAN 30 BLAKE MANAGER 30 TURNER SALESMAN 30 JAMES CLERK 6 rows selected. Q52 SQL>SELECT empno,ename,sal 2 FROM emp 3 WHERE sal > 4 (SELECT AVG(sal)
5 FROM emp) 6 AND deptno = ANY 7 (SELECT deptno 8 FROM emp 9 WHERE ename LIKE '%T%'); EMPNO ENAME SAL ---------- ---------- ---------7902 FORD 3000 7788 SCOTT 3000 7566 JONES 2975 7698 BLAKE 2850 Q53 SQL>SELECT ename,deptno,sal 2 FROM emp 3 WHERE deptno = ANY 4 (SELECT deptno 5 FROM emp 6 WHERE comm <>0) 7 AND 8 sal = ANY 9 (SELECT sal 10 FROM emp 11 WHERE comm <>0); ENAME DEPTNO SAL ---------- ---------- ---------MARTIN 30 1250 WARD 30 1250 ALLEN 30 1600 Q54 SQL> SELECT * 2 FROM emp 3 WHERE sal > ANY 4 (SELECT sal 5 FROM emp 6 WHERE job = 'CLERK'); EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------DEPTNO ----------
SAL
COMM
7499 ALLEN 30
SALESMAN
7698 20-FEB-81
1600
300
7521 WARD 30
SALESMAN
7698 22-FEB-81
1250
500
7566 JONES 20
MANAGER
7839 02-APR-81
EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------DEPTNO ---------7654 MARTIN SALESMAN 7698 28-SEP-81 1400 30 7698 BLAKE 30
MANAGER
7839 01-MAY-81
7782 CLARK 10
MANAGER
7839 09-JUN-81
EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------DEPTNO ---------7788 SCOTT ANALYST 7566 19-APR-87 20 7839 KING 10 7844 TURNER 30
PRESIDENT SALESMAN
17-NOV-81 7698 08-SEP-81
EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------* DEPTNO ---------7876 ADAMS CLERK 7788 23-MAY-87 20
2975
SAL
COMM
1250
2850 2450
SAL
COMM
3000 5000 1500
SAL
1100
0
COMM
7900 JAMES 30
CLERK
7902 FORD 20
ANALYST
7698 03-DEC-81 7566 03-DEC-81
950 3000
EMPNO ENAME JOB MGR HIREDATE SAL ---------- ---------- --------- ---------- --------- ---------- ---------DEPTNO ---------7934 MILLER CLERK 7782 23-JAN-82 1300 10 13 rows selected.
COMM
Related Documents 3m3m1z
Sql Queries 713f6j
June 2020 7
Sql Queries 713f6j
June 2020 4
Sql Queries 713f6j
June 2020 4
Apps Sql Queries 4l5073
December 2019 77
Useful Scom Sql Queries 59u2a
November 2019 72
Sql Sub Queries 4p6y5a
November 2019 51More Documents from "Navneet" u166l
24 Led Dancing Light 615y64
October 2019 66
Fundamentals Of Electrical Drives 215j42
January 2022 0
Cbse Sample Papers For Class 7 Science Sa2.pdf 2p605s
February 2022 0
November 2019 63
Six Sigma Green Belt Examination h6p5s
August 2021 0