Query to find Second Highest Salary of Employee?
SQL> SELECT DISTINCT SAL
2 FROM EMP E1
3 WHERE 2 = (SELECT COUNT (DISTINCT sal)
4 FROM emp E2
5 WHERE E1.sal <= E2.sal);
SAL
----------
3000
SQL> SELECT DISTINCT SAL
2 FROM (SELECT SAL, DENSE_RANK () OVER (ORDER BY SAL DESC) RNK FROM EMP)
3 WHERE RNK = 2;
SAL
----------
3000
SQL> SELECT DISTINCT SAL
2 FROM (SELECT SAL, ROW_NUMBER () OVER (ORDER BY SAL DESC) RNK FROM EMP)
3 WHERE RNK = 2;
SAL
----------
3000
SQL> SELECT MAX (E1.SAL) AS SAL
2 FROM EMP E1
3 WHERE SAL < (SELECT MAX (SAL)
4 FROM EMP E2);
SAL
----------
3000
What is the Query to fetch last record from the table?
SQL> SELECT * FROM EMP WHERE ROWID=(SELECT MAX(ROWID) FROM EMP);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT E.*
2 FROM EMP E,
3 (SELECT EMPNO, ROW_NUMBER () OVER (ORDER BY ROWID DESC) RNK FROM EMP)
4 R
5 WHERE E.EMPNO = R.EMPNO AND R.RNK = 1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
What is Query to display first 5 Records from Employee table?
SQL> SELECT * FROM EMP WHERE ROWNUM <= 5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
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
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
What is Query to display last 5 Records from Employee table?
SQL> SELECT * FROM (SELECT * FROM EMP E ORDER BY ROWID DESC) WHERE ROWNUM <=5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
What is Query to display Nth Record from Employee table? ( in below example displaying 6th record)
SQL> SELECT E.*
2 FROM EMP E,
3 (SELECT EMPNO, ROW_NUMBER () OVER (ORDER BY ROWID) RNK FROM EMP)
4 R
5 WHERE E.EMPNO = R.EMPNO AND R.RNK = 6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
How to Display Even rows in Employee table?
SQL> SELECT * FROM (SELECT ROWNUM AS RN,E.* FROM EMP E) WHERE MOD(RN,2)=0;
RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
4 7566 JONES MANAGER 7839 02-APR-81 2975 20
6 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
8 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
12 7900 JAMES CLERK 7698 03-DEC-81 950 30
14 7934 MILLER CLERK 7782 23-JAN-82 1300 10
7 rows selected.
How to Display Odd rows in Employee table?
SQL> SELECT * FROM (SELECT ROWNUM AS RN,E.* FROM EMP E) WHERE MOD(RN,2)=1;
RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1 7369 SMITH CLERK 7902 17-DEC-80 800 20
3 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
5 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
9 7839 KING PRESIDENT 17-NOV-81 5000 10
11 7876 ADAMS CLERK 7788 12-JAN-83 1100 20
13 7902 FORD ANALYST 7566 03-DEC-81 3000 20
7 rows selected.
How Can i create table with same structure with data of Employee table?
SQL> CREATE TABLE EMP_COPY AS SELECT * FROM EMP;
Table created.
SQL> SELECT COUNT(*) FROM EMP;
COUNT(*)
----------
14
SQL> SELECT COUNT(*) FROM EMP_COPY;
COUNT(*)
----------
14
How Can i create table with same structure without data of Employee table?
SQL> CREATE TABLE EMP_COPY2 AS SELECT * FROM EMP WHERE 1=2;
Table created.
SQL> SELECT COUNT(*) FROM EMP_COPY2;
COUNT(*)
----------
0
Display first 50% records from Employee table?
SQL> SELECT * FROM EMP WHERE ROWNUM<=(SELECT COUNT(*)/2 FROM EMP);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
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
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
7 rows selected.
Display last 50% records from Employee table?
SQL> SELECT ROWNUM,E.* FROM EMP E
2 MINUS
3 SELECT ROWNUM,E.* FROM EMP E WHERE ROWNUM<=(SELECT COUNT(*)/2 FROM EMP);
ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
8 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
9 7839 KING PRESIDENT 17-NOV-81 5000 10
10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
11 7876 ADAMS CLERK 7788 12-JAN-83 1100 20
12 7900 JAMES CLERK 7698 03-DEC-81 950 30
13 7902 FORD ANALYST 7566 03-DEC-81 3000 20
14 7934 MILLER CLERK 7782 23-JAN-82 1300 10
7 rows selected.
How to get distinct records (jobs) from the table (EMP)
SQL> SELECT DISTINCT JOB FROM EMP;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
How to get distinct records (jobs) from the table (EMP) without using distinct keyword.
SQL> SELECT JOB
2 FROM EMP a
3 WHERE ROWID = (SELECT MAX (ROWID)
4 FROM EMP b
5 WHERE a.JOB = b.JOB);
JOB
---------
MANAGER
PRESIDENT
SALESMAN
ANALYST
CLERK
how to write sql query for the below scenario
I/p:ORACLE
O/p:
O
R
A
C
L
E
i.e, splitting into multiple columns a string using sql.
SQL> SELECT SUBSTR ('ORACLE', LEVEL, 1) AS OUT
2 FROM DUAL
3 CONNECT BY LEVEL <= LENGTH ('ORACLE');
OUT
----
O
R
A
C
L
E
6 rows selected.
How to find count of duplicate rows?
SQL> SELECT EMPNO FROM EMP_COPY WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP_COPY GROUP BY SAL);
EMPNO
----------
7788
7521
SQL> SELECT A.EMPNO FROM EMP_COPY A WHERE ROWID <> (SELECT MAX(ROWID) FROM EMP_COPY B WHERE A.SAL=B.SAL);
EMPNO
----------
7521
7788
How to remove duplicate rows from table?
SQL> DELETE FROM EMP_COPY WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP_COPY GROUP BY SAL);
2 rows deleted.
SQL> ROLLBACK;
Rollback complete.
SQL> DELETE FROM EMP_COPY A WHERE ROWID <> (SELECT MAX(ROWID) FROM EMP_COPY B WHERE A.SAL=B.SAL);
2 rows deleted.
Imagine a single column in a table that is populated with either a single digit (0-9) or a single character (a-z,A-Z).Write a SQL query to print 'Fizz' for numeric value or 'Buzz' for alphabitical values for all values in that column
SQL> WITH vals
2 AS (SELECT 'd' str FROM DUAL
3 UNION ALL
4 SELECT 'x' str FROM DUAL
5 UNION ALL
6 SELECT 't' str FROM DUAL
7 UNION ALL
8 SELECT '8' str FROM DUAL
9 UNION ALL
10 SELECT 'a' str FROM DUAL
11 UNION ALL
12 SELECT '9' str FROM DUAL
13 UNION ALL
14 SELECT '6' str FROM DUAL
15 UNION ALL
16 SELECT '2' str FROM DUAL
17 UNION ALL
18 SELECT 'V' str FROM DUAL
19 )
20 SELECT str,
21 CASE
22 WHEN REGEXP_LIKE (str, '^[^a-zA-Z]*$') THEN 'Fizz'
23 ELSE 'Buzz'
24 END
25 typ
26 FROM vals;
S TYP
- ----
d Buzz
x Buzz
t Buzz
8 Fizz
a Buzz
9 Fizz
6 Fizz
2 Fizz
V Buzz
9 rows selected.
SQL>
SQL> DROP TABLE FRUIT;
Table dropped.
SQL> CREATE TABLE FRUIT(ID NUMBER);
Table created.
SQL> INSERT INTO FRUIT VALUES(1);
1 row created.
SQL> INSERT INTO FRUIT VALUES(2);
1 row created.
SQL> INSERT INTO FRUIT VALUES(3);
1 row created.
SQL> INSERT INTO FRUIT VALUES(NULL);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> DROP TABLE FRUIT_DETAILS;
Table dropped.
SQL> CREATE TABLE FRUIT_DETAILS(ID NUMBER,NAME VARCHAR2(10));
Table created.
SQL> INSERT INTO FRUIT_DETAILS VALUES(1,'ORANGE');
1 row created.
SQL> INSERT INTO FRUIT_DETAILS VALUES(2,'APPLE');
1 row created.
SQL> INSERT INTO FRUIT_DETAILS VALUES(2,'GRAPE');
1 row created.
SQL> INSERT INTO FRUIT_DETAILS VALUES(3,'BANANA');
1 row created.
SQL> INSERT INTO FRUIT_DETAILS VALUES(NULL,'AVOCADO');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM FRUIT;
ID
----------
1
2
3
SQL> SELECT * FROM FRUIT_DETAILS;
ID NAME
---------- ----------
1 ORANGE
2 APPLE
2 GRAPE
3 BANANA
AVOCADO
SQL> SELECT * FROM FRUIT F LEFT JOIN FRUIT_DETAILS FD ON F.ID=FD.ID;
ID ID NAME
---------- ---------- ----------
1 1 ORANGE
2 2 APPLE
2 2 GRAPE
3 3 BANANA
No comments:
Post a Comment