• No results found

SQL Queries

N/A
N/A
Info
Download
Protected

Academic year: 2021

Share "SQL Queries"

Copied!
30
0
0

Loading.... (view fulltext now)

Download now ( 30 Page )

Full text

(1)

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.

(2)

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.

(3)

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

(4)

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.

(5)

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

(6)

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.

(7)

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 COMM

---ALLEN 1600 300 TURNER 1500 0 MARTIN 1250 1400 WARD 1250 500

(8)

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

(9)

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;

(10)

---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

(11)

SQL> SELECT empno,ename,sal, ROUND(sal+sal*.15) inc_sal, sal*0.15 increase_in_sal

2 FROM emp;

EMPNO ENAME SAL INC_SAL INCREASE_IN_SAL --- --- --- --- 7369 SMITH 800 920 120 7499 ALLEN 1600 1840 240 7521 WARD 1250 1438 187.5 7566 JONES 2975 3421 446.25 7654 MARTIN 1250 1438 187.5 7698 BLAKE 2850 3278 427.5 7782 CLARK 2450 2818 367.5 7788 SCOTT 3000 3450 450 7839 KING 5000 5750 750 7844 TURNER 1500 1725 225 7876 ADAMS 1100 1265 165 7900 JAMES 950 1093 142.5 7902 FORD 3000 3450 450 7934 MILLER 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 SAL_REVIE - ---SMITH 17-DEC-80 22-JUN-81 ALLEN 20-FEB-81 24-AUG-81 WARD 22-FEB-81 24-AUG-81 JONES 02-APR-81 05-OCT-81 MARTIN 28-SEP-81 29-MAR-82

(12)

BLAKE 01-MAY-81 02-NOV-81 CLARK 09-JUN-81 14-DEC-81 SCOTT 19-APR-87 26-OCT-87 KING 17-NOV-81 24-MAY-82 TURNER 08-SEP-81 15-MAR-82 ADAMS 23-MAY-87 30-NOV-87 JAMES 03-DEC-81 07-JUN-82 FORD 03-DEC-81 07-JUN-82 MILLER 23-JAN-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 MNTHSBET ---SMITH 320 ALLEN 318 WARD 318 JONES 317 MARTIN 311 BLAKE 316 CLARK 315 SCOTT 244 KING 309 TURNER 312 ADAMS 243 JAMES 309 FORD 309 MILLER 307 14 rows selected.

(13)

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

(14)

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 LENGTH ---Allen 5 Jones 5 Martin 6 Adams 5 James 5 Miller 6 6 rows selected.

(15)

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 HIREDATE STARTDAY - ---SMITH 17-DEC-80 WEDNESDAY ALLEN 20-FEB-81 FRIDAY WARD 22-FEB-81 SUNDAY JONES 02-APR-81 THURSDAY MARTIN 28-SEP-81 MONDAY BLAKE 01-MAY-81 FRIDAY CLARK 09-JUN-81 TUESDAY SCOTT 19-APR-87 SUNDAY KING 17-NOV-81 TUESDAY TURNER 08-SEP-81 TUESDAY ADAMS 23-MAY-87 SATURDAY JAMES 03-DEC-81 THURSDAY FORD 03-DEC-81 THURSDAY MILLER 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 COMM

--- ---SMITH No Commission

(16)

ALLEN 300 WARD 500 JONES No Commission MARTIN 1400 BLAKE No Commission CLARK No Commission SCOTT No Commission KING No Commission TURNER No Commission ADAMS 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 DEPTNO DNAME --- --- ---SMITH 20 RESEARCH ALLEN 30 SALES WARD 30 SALES JONES 20 RESEARCH MARTIN 30 SALES BLAKE 30 SALES CLARK 10 ACCOUNTING SCOTT 20 RESEARCH KING 10 ACCOUNTING TURNER 30 SALES ADAMS 20 RESEARCH JAMES 30 SALES FORD 20 RESEARCH

(17)

MILLER 10 ACCOUNTING 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

(18)

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 ACCOUNTING 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

(19)

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 EMPNO MNAME MGRNO ---SMITH 7369 FORD 7902 ALLEN 7499 BLAKE 7698 WARD 7521 BLAKE 7698 JONES 7566 KING 7839 MARTIN 7654 BLAKE 7698 BLAKE 7698 KING 7839 CLARK 7782 KING 7839 SCOTT 7788 JONES 7566 TURNER 7844 BLAKE 7698 ADAMS 7876 SCOTT 7788 JAMES 7900 BLAKE 7698 FORD 7902 JONES 7566 MILLER 7934 CLARK 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 JOIN EMP M 3 ON(E.MGR=M.EMPNO); ENAME EMPNO ENAME MGR ---FORD 7902 JONES 7566

(20)

SCOTT 7788 JONES 7566 JAMES 7900 BLAKE 7698 TURNER 7844 BLAKE 7698 MARTIN 7654 BLAKE 7698 WARD 7521 BLAKE 7698 ALLEN 7499 BLAKE 7698 MILLER 7934 CLARK 7782 ADAMS 7876 SCOTT 7788 CLARK 7782 KING 7839 BLAKE 7698 KING 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 DEPTNO ENAME --- ---JONES 20 SMITH SCOTT 20 SMITH ADAMS 20 SMITH FORD 20 SMITH WARD 30 ALLEN MARTIN 30 ALLEN BLAKE 30 ALLEN TURNER 30 ALLEN JAMES 30 ALLEN MARTIN 30 WARD BLAKE 30 WARD

(21)

ENAME DEPTNO ENAME ---TURNER 30 WARD JAMES 30 WARD SCOTT 20 JONES ADAMS 20 JONES FORD 20 JONES BLAKE 30 MARTIN TURNER 30 MARTIN JAMES 30 MARTIN TURNER 30 BLAKE JAMES 30 BLAKE KING 10 CLARK ENAME DEPTNO ENAME ---MILLER 10 CLARK ADAMS 20 SCOTT FORD 20 SCOTT MILLER 10 KING JAMES 30 TURNER FORD 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 ACCOUNTING 1300 2

ALLEN SALESMAN SALES 1600 3

TURNER SALESMAN SALES 1500 3

(22)

BLAKE MANAGER SALES 2850 4 CLARK MANAGER ACCOUNTING 2450 4 ENAME JOB DNAME SAL GRADE --- ----

---SCOTT ANALYST RESEARCH 3000 4 FORD ANALYST RESEARCH 3000 4 KING PRESIDENT ACCOUNTING 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

(23)

JONES 02-APR-81 KING 17-NOV-81 BLAKE 01-MAY-81 KING 17-NOV-81 CLARK 09-JUN-81 KING 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

(24)

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;

(25)

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 ---- ---

---ACCOUNTING 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 -

(26)

---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

(27)

7654 MARTIN 7900 JAMES 7844 TURNER 7521 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)

(28)

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 SAL COMM --- ---

DEPTNO

(29)

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM --- ---

DEPTNO

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

EMPNO ENAME JOB MGR HIREDATE SAL COMM --- ---

DEPTNO

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM --- ---

---* DEPTNO

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

(30)

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM --- ---

DEPTNO

7934 MILLER CLERK 7782 23-JAN-82 1300 10

References

Download now ( PDF - 30 Page - 107.50 KB )

Related documents