USE
ONLY SINGLE ROW FUNCTIONS :
1. LIST EMPLOYEES WHOSE NAME HAVING 4 CHARACTERS
SELECT ENAME
FROM EMP
WHERE LENGTH(ENAME)=4;
2. LIST EMPLOYEES WHOSE JOB IS HAVING 7 CHARACTERS
SELECT ENAME,JOB
FROM EMP
WHERE LENGTH(JOB)=7;
3. FIND OUT HOW MANY TIMES LETTER 'S' OCCURS IN 'QSPIDERS'
SELECT LENGTH(‘QSPIDERS’)-LENGTH(REPLACE(‘QSPIDERS’,’S’))
FROM DUAL;
4. LIST THE EMPLOYEES WHOSE JOB IS HAVING LAST 3 CHARACTERS AS 'MAN'
SELECT JOB
FROM EMP
WHERE SUBSTR(JOB,-3)=’MAN’;
5. LIST EMPLOYEES WHOSE JOB IS HAVING FIRST 3 CHARACTERS AS 'MAN'.
SELECT JOB
FROM EMP
WHERE SUBSTR(JOB,1,3)=’MAN’;
6. DISPLAY ALL THE NAMES WHOSE NAME IS HAVING EXACTLY 1 'L'
SELECT ENAME
FROM EMP
WHERE LENGTH(ENAME)-LENGTH(REPLACE(ENAME,'L'))=1
OR
SELECT ENAME
FROM EMP
WHERE INSTR(ENAME,’L’,1,1)>0 AND INSTR(ENAME,’L’,1,2)=0;
7. DISPLAY DEPT NAMES WHICH ARE HAVING LETTER 'O'
SELECT DNAME
FROM DEPT
WHERE INSTR(DNAME,'O',1)>0;
8. DISPLAY THE OUTPUT AS SHOWN BELOW,
SCOTT WORKING AS A CLERK EARNS 3000 IN DEPT 20
SELECT CONCAT(ENAME,CONCAT(' WORKING AS A ',CONCAT(' CLERK EARNS
3000 IN DEPTNO ',DEPTNO)))
FROM EMP
WHERE ENAME='SCOTT';
9. CALCULATE NUMBER OF L IN STRING 'HELLLLL'
SELECT LENGTH(HELLLLL)-LENGTH(REPLACE(HELLLLL,'L'))
FROM DUAL;
10. DISPLAY ALL THE EMPLOYEES WHOSE JOB HAS A STRING 'MAN'
SELECT JOB
FROM EMP
WHERE INSTR(JOB,'MAN',1)>0;
11. DISPLAY ALL THE EMPLOYEES WHOSE JOB STARTS WITH STRING 'MAN'
SELECT JOB
FROM EMP
WHERE SUBSTR(JOB,1,3)=’MAN’;
12. DISPLAY ALL THE EMPLOYEES WHOSE JOB ENDS WITH STRING 'MAN'
SELECT JOB
FROM EMP
WHERE SUBSTR(JOB,-3)=’MAN’;
13. DISPLAY FIRST 3 CHARACTERS OF ENAME IN LOWER CASE AND REST
EVERYTHING IN UPPER CASE.
IF ENAME IS 'QSPIDERS' THEN DISPLAY THIS AS ‘qspIDERS’
SELECT CONCAT(LOWER(SUBSTR('QSPIDERS',1,3)),
SUBSTR('QSPIDERS',4)
FROM DUAL;
14. DISPLAY THE RESULT FROM EMP TABLE AS BELOW.
SMITH IS A CLERK AND GETS SALARY 2000
SELECT CONCAT(ENAME,CONCAT(' IS A ' , CONCAT( JOB , CONCAT(' AND GETS A
SLARY OF ',2000))))
FROM EMP
WHERE ENAME='SMITH' AND JOB='CLERK';
[Link] THE EMPLOYEES HIRED ON A WEDNESDAY
SELECT ENAME,HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE,'DY')='WED';
[Link] THE EMPLOYEES HIRED ON A LEAP YEAR
SELECT ENAME
FROM EMP
WHERE MOD(TO_CHAR(HIREDATE,'YY'),4)=0;
[Link] THE EMPLOYEES HIRED ON A SUNDAY IN THE MONTH OF MAY
SELECT ENAME,HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE , 'DY')='SUN' AND
TO_CHAR(HIREDATE,'MON')='MAY';
[Link] HIREDATES IN US FORMAT .
SELECT TO_CHAR(HIREDATE,'MON-DD-YYYY')
FROM EMP;
19) DISPLAY FIRST HALF OF NAME IN LOWER CASE AND REST IN REVERSE.
SELECT
CONCAT(LOWER(SUBSTR(ENAME,1,LENGTH(ENAME)/2)),REVERSE(SUBSTR(ENAM
E,LENGTH(ENAME)/2+1)))
FROM EMP;
20)WAQTD LAST 3 CHAR OF EMP WHO ARE WORKING AS MANAGER
SELECT SUBSTR(ENAME,-3)
FROM EMP
WHERE JOB=’MANAGER’;
21) WAQTD NAME OF THE EMP WHOSE NAME ENDS WITH ‘S’
SELECT ENAME
FROM EMP
WHERE SUBSTR(ENAME,-1)=’S’;
22)WAQTD NAME OF THE EMP WHOSE NAME ENDS WITH S OR N
SELECT ENAME
FROM EMP
WHERE SUBSTR(ENAME,-1)IN (’S’,’N’);
23) WAQTD NAME OF EMP IF HIS NAME START WITH VOWEL
SELECT ENAME
FROM EMP
WHERE SUBSTR(ENAME,1,1)IN (’A’,’E’,’I’,’O’,’U’);
24) WAQTD 1ST HALF OF EMP NAME
SELECT ENAME, SUBSTR(ENAME,1,LENGTH(ENAME)/2)
FROM EMP;
25)WAQTD 2ND HALF OF EMP NAME
SELECT ENAME, SUBSTR(ENAME,LENGTH(ENAME)/2+1)
FROM EMP;
26)WAQTD NAME OF EMP WITHOUT 1ST AND LAST CHAR
SELECT ENAME, SUBSTR(ENAME,2,LENGTH(ENAME)-2)
FROM EMP;
27)WAQTD NAME OF THE EMP WITH CHAR ‘A’ IN NAME
SELECT ENAME
FROM EMP
WHERE INSTR(ENAME,’A’,1,1) >0;
28) WAQTD NAME OF THE EMP AND HIS DNAME IF CHAR ‘O’ IS PRESENT IN
EMP’S NAME AS WELL AS DNAME
SELECT ENAME,DNAME
FROM EMP E,DEPT D
WHERE [Link]=[Link] AND INSTR(ENAME,’O’,1)>0 AND
INSTR(DNAME,’O’,1)>0;
29)WAQT D EMPS NAME WHO ARE GETTING EVEN SAL
SELECT ENAME
FROM EMP
WHERE MOD(LENGTH(ENAME),2)=0;
