SQL-ANSWERS
1]
select * from emp where lower(ename) like '%a%a%'
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- --------- --------- --------- --------- ---------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
2]
select * from emp where sal > (select avg(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- --------- --------- --------- --------- ---------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
3]
select * from dept where deptno in (select deptno from emp group by deptno having avg(sal)>2000)
DEPTNO DNAME LOC
------- -------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
4]
select deptno,job , count(*) from emp where lower(job) in ('clerk', 'manager')
group by deptno, job having count(*)>3
no rows selected
5]
select * from emp where to_char(hiredate,'mm-yyyy') = '02-1991'
no rows selected
6]
select ename from emp where length(ename)>5 and length(ename)<8
ENAME
---------
MARTIN
TURNER
MILLER
7]
select deptno from emp group by deptno having count(*)>3;
DEPTNO
-------
20
30
8]
sql> select e1.ename employee , e2.ename manager from emp e1, emp e2 where e1.mgr=e2.empno;
EMPLOYEE MANAGER
---------- ----------
SCOTT JONES
FORD JONES
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
JONES KING
CLARK KING
BLAKE KING
SMITH FORD
9]
sql> select deptno from emp group by deptno having count (*) = (select max(count(*)) from emp group by deptno);
DEPTNO
---------
30
10]
select * from dept where deptno = (
select deptno from emp group by deptno having count (*) = (
select max(count(*)) from emp group by deptno));
DEPTNO DNAME LOC
------ -------------- -------------
30 SALES CHICAGO
11]
select to_char(hiredate,'yy') from emp group by to_char(hiredate,'yy')
having count(*) = (
select max(count(*)) from emp group by to_char(hiredate,'yy'));
to_char(hiredate,'yy')
-----------------------------
81
12]
select * from dept where deptno = (
select deptno from emp group by deptno having sum (sal) = (
select max(sum(sal)) from emp group by deptno));
DEPTNO DNAME LOC
------- -------------- -------------
20 RESEARCH DALLAS
13]
select ename from emp where deptno <> (select deptno from dept where lower(dname)= 'accounting');
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
SCOTT
TURNER
ADAMS
JAMES
FORD
14]
SQL> select ename from emp where lower(ename) like '%r';
ENAME
----------
TURNER
MILLER
15]
select ename, decode(deptno, 10, 'Finance', 20, 'Accounts', 30, 'MIS', 40, 'EDP') dname from emp;
ENAME DNAME
---------- --------
SMITH Accounts
ALLEN MIS
WARD MIS
JONES Accounts
MARTIN MIS
BLAKE MIS
CLARK Finance
SCOTT Accounts
KING Finance
TURNER MIS
ADAMS Accounts
JAMES MIS
FORD Accounts
MILLER Finance
16]
SQL> select deptno from dept minus select deptno from emp;
DEPTNO
---------
40
17]
select empno,ename,concat('$',sal),job from emp order by job desc;
SQL>
EMPNO ENAME CONCAT('$',SAL) JOB
--------- ---------- ----------------------------------------- ---------
7499 ALLEN $1600 SALESMAN
7521 WARD $1250 SALESMAN
7654 MARTIN $1250 SALESMAN
7844 TURNER $1500 SALESMAN
7839 KING $5000 PRESIDENT
7566 JONES $2975 MANAGER
7782 CLARK $2450 MANAGER
7698 BLAKE $2850 MANAGER
7369 SMITH $800 CLERK
7900 JAMES $950 CLERK
7876 ADAMS $1100 CLERK
7934 MILLER $1300 CLERK
7788 SCOTT $3000 ANALYST
7902 FORD $3000 ANALYST
18]
SELECT * FROM EMP E WHERE 3 = (SELECT COUNT(*) FROM EMP WHERE SAL>E.SAL)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- --------- --------- --------- --------- ---------
7566 JONES MANAGER 7839 02-APR-81 2975 20
19]
delete from emp where empno=(
select empno from emp group by empno having count(*)>1)
0 rows deleted.
20]
SQL> select deptno, count(*) from emp group by deptno;
DEPTNO COUNT(*)
--------- ---------
10 3
20 5
30 6
21]
SQL> select job, count(*) from emp group by job;
JOB COUNT(*)
--------- ---------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
22]
select ename, sal, deptno from emp where (deptno,sal) in ( select deptno, max(sal) from emp group by deptno)
ENAME SAL DEPTNO
---------- --------- ---------
KING 5000 10
SCOTT 3000 20
FORD 3000 20
BLAKE 2850 30
23]
SQL> select upper(ename) from emp;
UPPER(ENAM
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
24]
SQL> select lower(ename) from emp;
LOWER(ENAM
----------
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller
25]
SQL> select initcap(ename) from emp;
INITCAP(EN
----------
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams
James
Ford
Miller
26]
SQL> select substr('Oracle',2,3) from dual;
SUB
---
rac
27]
SQL> select translate('Allens','A','B') from dual;
TRANSL
------
Bllens
28]
select ename,sal from emp where ename='FORD' and sal=(
select max(sal) from emp where job = (
select job from emp where ename='FORD'))
ENAME SAL
---------- ---------
FORD 3000
29]
select e1.ename employee , e1.job, e2.ename manager from emp e1, emp e2 where e1.mgr=e2.empno (+);
EMPLOYEE JOB MANAGER
---------- --------- ----------
SCOTT ANALYST JONES
FORD ANALYST JONES
ALLEN SALESMAN BLAKE
WARD SALESMAN BLAKE
JAMES CLERK BLAKE
TURNER SALESMAN BLAKE
MARTIN SALESMAN BLAKE
MILLER CLERK CLARK
ADAMS CLERK SCOTT
JONES MANAGER KING
CLARK MANAGER KING
BLAKE MANAGER KING
SMITH CLERK FORD
KING PRESIDENT
30]
select job from emp where ename='JONES';
JOB
---------
MANAGER
31]
select * from emp where mgr = ( select empno from emp where ename='BLAKE');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- --------- --------- --------- --------- ---------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
32]
select * from emp where empno =(
select empno from(
select empno,rownum as r from emp) where r=10)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- --------- --------- --------- --------- ---------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
33]
SQL>create table emp1 as select * from emp;
SQL>select * from emp1;
note: this displays same records as that of emp
34]
select substr(ename,1,1),ename from emp order by ename;
S ENAME
- ---------
A ADAMS
A ALLEN
B BLAKE
C CLARK
F FORD
J JAMES
J JONES
K KING
M MARTIN
M MILLER
S SCOTT
S SMITH
T TURNER
W WARD
35]
delete from emp where months_between(hiredate,sysdate)/12 >2
36]
Update emp set sal = sal + sal*0.1 where comm is not null;
37]
Refer to Q.no: 8
38]
Refer to Q.no: 19
39]
Sql> set pause on
Sql> select * from emp;
40]
create table empy (empno number (5));
Table created.
41]
SQL> alter table empy add ename varchar2(20);
Table altered.
42]
SQL> alter table empy add constraint c1 primary key(empno);
Table altered.
SQL> desc empy;
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(5)
ENAME VARCHAR2(20)
43]
SQL> alter table empy modify ename varchar2(30);
Table altered.
SQL> desc empy;
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(5)
ENAME VARCHAR2(30)
44]
SQL> alter table empy add sal number(6,2);
Table altered.
SQL> desc empy;
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(5)
ENAME VARCHAR2(30)
SAL NUMBER(6,2)
45]
alter table empy add constraint c2 check (sal<=10000);
Table altered.
46]
alter table empy add mgr number(4) references empy(empno);
SQL> desc empy;
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(5)
ENAME VARCHAR2(30)
SAL NUMBER(6,2)
MGR NUMBER(4)
47]
SQL> create view empview as select * from emp;
View created.
48]
SQL> drop table emp;
SQL> drop view eview;
49]
SQL> select * from emp where ename like 'R___';
no rows selected
50]
SQL> CREATE SEQUENCE S1 MINVALUE 1 MAXVALUE 100 START WITH 1 INCREMENT BY 1
SQL> INSERT INTO STUDENT VALUES (S1.NEXTVAL,’RAMA’);
SQL> INSERT INTO STUDENT VALUES (S1.NEXTVAL,’ARUN’);
And, the values in the STUDENT table will be
SNO SNAME
------ --------
1 RAMA
2 ARUN
51]
CREATE SEQUENCE detno_seq MINVALUE 1 MAXVALUE 100 START WITH 50 INCREMENT BY 10
52]
select * from emp where empno in (
select empno from (select empno, rownum r from emp) where mod(r,2)=1 )
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- --------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
53]
select * from emp where empno in (
select empno from (select empno, rownum r from emp) where mod(r,2)=0 )
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- --------- --------- --------- --------- ---------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
