select * from emp order by deptno, sal desc;
--Maximum Salary
select max(sal) from emp;
--3rd highest salary
select ename, sal, rank from (
select ename, sal, dense_rank() over(order by sal desc) as rank from emp)
where rank=3;
--more than avg of their own demartment
select * from emp e1,
(select deptno, avg(sal) as avg from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal>e2.avg;
--can we user case in where condition
SELECT *
FROM emp
WHERE
CASE
WHEN job = 'MANAGER' THEN 1
WHEN sal > 100000 THEN 2
ELSE 0
END = 2;
--Can we use decode in where condition
SELECT *
FROM emp
WHERE DECODE(deptno, 10, 'Y', 20, 'P', 'N') in ('P','N');
--Identify the Slow SQLs
SELECT *
FROM (
SELECT sql_id, executions, elapsed_time,executions avg_time, sql_text
FROM v$sql
WHERE elapsed_time
/*executions*/ IS NOT NULL
ORDER BY avg_time DESC
)
WHERE ROWNUM <= 10;
--Check if the Package is Still Running or Hung
SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.event, s.wait_class, s.seconds_in_wait
FROM v$session s
WHERE s.username = 'ODI_SRC'
AND s.status = 'ACTIVE';
--Check for Locks or Blocking Sessions
SELECT
a.sid blocker_sid,
b.sid blocked_sid,
a.username blocker_user,
b.username blocked_user,
b.event
FROM v$session a, v$session b
WHERE a.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
AND b.blocking_session = a.sid;
--Check for Recent SQL/PLSQL Errors
SELECT *
FROM dba_errors
WHERE name = 'GET_EMP'
AND owner = 'ODI_SRC';
--Check System Resource Usage
SELECT * FROM v$resource_limit
WHERE resource_name IN ('sessions', 'processes');
--Or
SELECT * FROM v$sysmetric_summary
WHERE metric_name LIKE '%CPU%' OR metric_name LIKE '%Memory%';
--Example: Materialized View
CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT deptno, COUNT(*) emp_count
FROM emp
GROUP BY deptno;
--How to Check If a View Is Updatable?
SELECT view_name, updatable
FROM user_updatable_columns
WHERE view_name = 'EMP_VIEW';
--Index may be missing or not used due to function or implicit conversion
create index ixd_hiredate on emp(hiredate);
explain plan for
select * from emp where to_char(HIREDATE)='23-12-80';
select * from table(dbms_xplan.display);
--Declared a procedure in the package body and it is not declared in the package specification. Will that package run or not?
CREATE OR REPLACE PACKAGE my_pkg AS
PROCEDURE proc_pub;
END my_pkg;
CREATE OR REPLACE PACKAGE BODY my_pkg AS
-- Public procedure (declared in spec)
PROCEDURE proc_pub IS
BEGIN
proc_priv; -- calling private proc
DBMS_OUTPUT.PUT_LINE('Public procedure');
END;
-- Private procedure (not in spec)
PROCEDURE proc_priv IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Private procedure');
END;
END my_pkg;
EXEC my_pkg.proc_pub;
drop PACKAGE my_pkg;
drop PACKAGE BODY my_pkg;
set serveroutput on;
--3rd highest salary
SELECT sal
FROM (
SELECT DISTINCT sal
FROM emp
ORDER BY sal DESC
)
WHERE ROWNUM =3;
--get employees whose salary is more than the average salary of their own department:
SELECT empno, ename, deptno, sal
FROM emp e
WHERE sal > (
SELECT AVG(sal)
FROM emp
WHERE deptno = e.deptno
);
--mutating table error
CREATE OR REPLACE TRIGGER trg_emp_update
AFTER UPDATE ON emp
--for each row -it cause mutating table error
declare
v_count number;
BEGIN
-- Valid: statement-level can query emp
select count(*) into v_count from emp;
dbms_output.put_line(v_count);
--INSERT INTO log_table SELECT * FROM emp WHERE deptno = 10;
END;
select * from emp;
update emp set sal=250000 where empno=8376;
