ORACLE SQL FAQ - Answers

-
Normalization:
Its a process of organizing data in database. This includes creating tables and establishing relationship between these tables.
The result in database consistency, flexible and reduce redundancy.

  • First Normal Form : Eliminate repeating group
  • Second Normal Form: Eliminate redundant data, related table with foreign key
  • Third Normal Form: Eliminate fields that do not depend on the key


Employee & Department table scenario:

DEPT (DEPTNO, DNAME, LOC)
EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

DDL Create Scripts:
create table dept(  
  deptno     number(2,0),  
  dname      varchar2(14),  
  loc        varchar2(13),  
  constraint pk_dept primary key (deptno)  
);

create table emp(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);

create table associate as 
select * 
  from emp
 where empno <> mgr 
   and mgr is not null;

create table manager as 
select * 
  from emp
 where job='MANAGER';


INSERT Statements:
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES(40, 'OPERATIONS', 'BOSTON');

INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-11-1981','DD-MM-YYYY'),  5000, NULL, 10);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-5-1981','DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839, TO_DATE('9-6-1981','DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-4-1981','DD-MM-YYYY'),2975, NULL, 20);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('13-JUL-87','DD-MM-RR') - 85,3000, NULL, 20);
INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-12-1981','DD-MM-YYYY'),3000, NULL, 20);
INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-12-1980','DD-MM-YYYY'),800, NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-2-1981','DD-MM-YYYY'),1600, 300, 30);
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-2-1981','DD-MM-YYYY'),1250, 500, 30);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-9-1981','DD-MM-YYYY'),1250, 1400, 30);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-9-1981','DD-MM-YYYY'),1500, 0, 30);
INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('13-JUL-87', 'DD-MM-RR') - 51,1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-12-1981','DD-MM-YYYY'),950, NULL, 30);
INSERT INTO EMPV ALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-1-1982','DD-MM-YYYY'),1300, NULL, 10);


1. Find out the detail of Top 3 earners of the company.
select e.empno,e.ename,e.job,e.sal 
  from emp e 
 where (select count(*) 
          from emp 
         where sal > e.sal)< 3 
order by sal desc;


2. Display those manager names whose salary is more than average salary of his employees.
select manager.ename 
  from associate, manager
where manager.sal > (select avg(sal) 
                       from emp 
                      where associate.mgr=manager.empno);


3. Display those employees who joined the company before 15th of the month.
select empno,ename,hiredate 
  from emp 
 where extract(day from hiredate) < 15;


4. Display the manager who is having maximum number of employees working under him.
select manager.ename, count(associate.empno) 
  from manager, associate
 where associate.mgr=manager.empno 
group by manager.ename 
having count(associate.empno)= (select max(count(associate.empno))                                      from manager, associate
                                 where manager.empno=associate.mgr                                     group by manager.ename);


5. Print a list of employees displaying “Less Salary” if less than 1500 if exactly 1500 Display as “Exact Salary” and if greater than 1500 display “more salary”.
select ename, sal, 
(Case when sal<1500 then 'Less Salary' 
      when sal=1500 then 'Exact Salary' 
      when sal>1500 then 'More Salary' 
 else 'No Salary' end) 
from emp;


6.Update the employee salary by 15%, whose experience is greater than 10 years.
update emp 
   set sal=sal+(15/100)*sal 
 where extract(year from sysdate)- extract(year from hiredate)>10;


7. Delete the employees, who completed 30 years of service.
delete 
  from emp1 
 where extract(year from sysdate)-extract(year from hiredate)>=30;


8. Determine the minimum salary of an employee and his details who joined on the same year.
select * 
  from emp 
 where extract(year from hiredate)= (select extract(year 
                                                    from hiredate) 
                                        from emp 
                                       where sal=(select min(sal) from emp));


9.Determine the count of employees who are taking commission.
select count(*) "emp getting comm" 
  from emp 
 where comm is not null 
   and comm <> 0;


10. Create a view which contains employee names and their manager names working in sales department.
create view sales_emp as 
select associate.ename "AENAME",manager.ename "MENAME" 
  from associate,manager 
 where associate.mgr=manager.empno 
   and associate.deptno = (select deptno 
                             from dept
                            where dname='SALES');

select * from sales_emp;


11. Determine the names of employee who earn more than their managers.
select associate.ename, associate.sal, manager.sal 
from associate, manager
where associate.mgr = manager.empno 
  and associate.sal > manager.sal;


12. Determine the name of employees, who take the highest salary in their departments.
select ename,deptno,sal 
  from emp 
 where sal in(select max(sal) 
                from emp 
              group by deptno);


13. Determine the employees, who located at the same place.
select emp.ename,emp.job,emp.deptno,dept.dname,dept.loc 
  from emp ,dept 
 where emp.deptno = dept.deptno 
order by dept.loc


14. Determine the employees whose total salary is like the minimum salary of any department.
select * 
  from emp 
where sal*12 in (select max(sal) 
                   from emp 
                group by deptno);


15.Determine the department does not contain any employees.
select dname 
  from dept 
 where deptno not in(select deptno from emp);

select *
from emp, dept  
where emp.deptno(+) = dept.deptno  
order by ename

select *
from emp,dept
WHERE emp.deptno =  dept.deptno (+)
order by 2 DESC

SELECT COUNT(empno),nvl(dept.dname,'NOR BELONG TO ANY DEPT') dname
FROM emp 
LEFT JOIN dept ON emp.deptno = dept.deptno
GROUP BY dname


16.Employee list with Average salary greater than salary of that department.
---Method:1
with tt as 
(
select avg(emp.sal) avg_sal , emp.deptno
from emp
inner join dept on dept.deptno = emp.deptno
group BY dept.deptno
)
select emp.* 
from tt 
inner join emp on tt.deptno = emp.deptno
where emp.sal > tt.avg_sal

---Method:2
SELECT * 
FROM EMP E1
WHERE E1.SAL >(SELECT AVG(E2.SAL) 
                 FROM EMP E2 
                WHERE E1.DEPTNO = E2.DEPTNO)

---Method:3 (Using Aggregate function)
SELECT E.* FROM 
(SELECT E2.*, AVG(E2.SAL) over(PARTITION BY E2.DEPTNO) AS AVGSAL 
   FROM EMP E2) E                                     
WHERE E.SAL > E.AVGSAL

---Method:4 (Subquery)
select * 
from emp, (
select avg(emp.sal) avg_sal , dept.deptno
from emp
inner join dept on dept.deptno = emp.deptno
group BY dept.deptno
) t where emp.deptno = t.deptno
AND t.avg_sal > emp.sal


17. Third highest salary for specific department.
SELECT * 
 FROM (SELECT EMP.*,
DENSE_RANK() OVER (PARTITION BY EMP.DEPTNO ORDER BY EMP.SAL DESC)    AS DENSE_RANK, 
RANK() OVER (PARTITION BY EMP.DEPTNO ORDER BY EMP.SAL DESC) AS RANK
FROM EMP --THIRD HIGHEST SAL
) T 
WHERE T.DEPTNO = 20 AND T.DENSE_RANK > 3

Comments

Post a Comment

Popular posts from this blog

PUTTY - The server's host key is not cached in the registry cache

-
Error: "The server's host key is not cached in the registry. You have no guarantee that the server is the computer you think it is. The server's rsa2 key fingerprint is: [SSH KEY FINGERPRINT] If you trust this host, enter "y" to add the key to PuTTY's cache and carry on connecting. If you want to carry on connecting just once, without adding the key to the cache, enter "n". If you do not trust this host, press Return to abandon the connection. Store key in cache? (y/n) y" Answer: Simply run this command in your windows preferred terminal (cmd.exe etc) and agree to accept the host and store the cache: C:\Program Files (x86)\PuTTY\plink.exe -ssh [SSH USERNAME]@[SSH HOST] -P [SSH PORT] echo y | pscp -i /path/to/key/file user@remote:/tmp/file  . echo y | plink -i /path/to/key/file scripts.sh Usually you would hit “y” here, assuming the host key is correct, in order to store it in future connection.  The storage of this goes into the Registry under ...
Read more

OIM-12c Installation - FMW - SOA - IDM

-
Image
Oracle Identity Manager (OIM) 12c Installation Prerequisites: The Oracle Identity Manager software includes the following: 1] Oracle's Java Development Kit (JDK) 1.8 (jdk-8u311-linux-x64.tar.gz) 2]  Fusion Middleware 12.2.1.4.0 3]  SOA Suite 12.2.1.4.0 4] Oracle Identity and Access Management  12.2.1.4.0 What Do You Need? For completing Oracle IDM Setup you will need, Environment as below: Oracle Enterprise Linux 6 or similar, with access to the Internet At least 8GB RAM (16GB recommended) An Oracle Database Enterprise Edition 12 or superior installed and running. Note: Installing a Database for Identity and Access Management (IDM) Suite provides instructions on how to install an Oracle Database that is suitable for Oracle Identity and Access Management Suite. oracle and root user credentials SYS database user credentials Basic understanding of Linux An Oracle Technology Network (OTN) account OIM-11g PS3 Installation Steps: Just for reference https://www.oracle.com/webfol...
Read more

SAML & OAuth 2.0

-
Both SAML (Security Assertion Markup Language) and OAuth 2.0 are widely used protocols for authentication and authorization in web applications, but they serve different purposes. SAML (Security Assertion Markup Language) Purpose: Primarily designed for authentication and single sign-on (SSO) . How it works: A user attempts to access a service provider (SP). The SP redirects the user to an identity provider (IdP). The IdP authenticates the user and sends a SAML assertion to the SP. The SP validates the assertion and grants access to the user. Common use cases: Enterprise applications, government services, and federated identity management systems. OAuth 2.0 Purpose: Designed for authorization , allowing users to grant third-party applications access to their data on a specific service. How it works: A user attempts to access a resource on a service provider. The SP redirects the user to the authorization server. The user grants permission to the application. The authorization s...
Read more