SQL Queries

1) SQL Query to write ‘ORACLE’ string vertically

Query:

SELECT SUBSTR(‘ORACLE’, ROWNUM, 1)FROM DUAL CONNECT BY LEVEL <= LENGTH(‘ORACLE’)

2) Display 1 to 10 numbers using DUAL table

Query:

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= 10

3) Display 10 to 5 numbers

Query:

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= 10

MINUS

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= 4

ORDER BY 1 DESC

4) Display employee names where ename start with ‘s’ character

Query:

SELECT * FROM EMP WHERE ENAME LIKE ‘S%’

5) Display employee names where ename start with ‘s’ character, write a query by using sub-string function

Query:

 

SELECT * FROM EMP WHERE SUBSTR(ENAME, 1, 1) = ‘S’

 

SUBSTRING( string, start_position, length )

 

String -> source string to extract from

start_position -> position to start extraction from .. The first position in the string is always 1

length -> number of characters to extract.

 

6) Display employee names where ename start with ‘s’ character, write a query by using INSTRING function

SELECT * FROM EMP WHERE INSTR(ENAME, S,1,1) = 1

 

INSTR (string, pattern, [starting position, [nth location]])

 

String -> string to search.,string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Pattern -> The pattern to search for in string. pattern can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

starting position -> Optional. The position in string where the search will start. If omitted, it defaults to 1, The first position in the string is 1.

If the start_position is negative, the INSTR function counts back start_position number of characters from the end of string and then searches towards the beginning of string.

nth location -> It is optional, nth appearance of substring. If omitted, it defaults to 1.

 

7) display first character of enames from EMP TABLE.

SELECT SUBSTR(ENAME, 1, 1) FROM EMP

8) Display last character of enames from EMP TABLE

SELECT SUBSTR(ENAME, -1, 1) FROM EMP

(OR)

SELECT SUBSTR(ENAME, LENGTH(ENAME), 1) FROM EMP

 

9) Display employee details where ENAME has min one ‘S’ character

 

SELECT * FROM EMP WHERE ENAME LIKE ‘%S%’

10) Display employee details where ENAME has min one ‘S’ character, By using INSTR function.

SELECT * FROM EMP WHERE INSTR(ENAME, S,1,1) <> 0

11) Display employee details where ENAME has min one ‘S’ character, By using REPLACE function

SELECT * FROM EMP WHERE ENAME <> REPLACE(ENAME, S, X)

12) Write a SQL query to remove numbers in the string ‘ORA94X74’

SELECT TRANSLATE(‘ORA94X74’,’X0123456789’,’X’) FROM DUAL

 

TRANSLATE Function:

TRANSLATE( string, string_to_replace, replacement_string )

String -> string to replace a sequence of characters with another set of characters

string_to_replace ->string that will be searched for in string

replacement_string-> All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

13) What is result of DECODE(null,null,1,1)

Generally we can not compare null values, but DECODE function can compare the ‘null’ values.

Hence : the answer is – 1

14)write a query to return value as ‘YES’ if given string has numbers, else return the value as ‘NO’

SELECT DECODE(‘ORA95X’,TRANSLATE(‘ORA95X’,’Z012345678’,’Z’),’NO’,’YES’) FROM DUAL

15)display DEPTNO and average sal from emp table

SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO

16) display second highest salary from EMP table

SELECT * FROM(SELECT E.* DENSE.RANK() OVER(ORDER BY SAL DESC) SLNUM FROM EMP E) WHERE SLNUM = 2

17) display data as below format

 

Deptno                 ename

10                           scott

Tiger

Eric

20                           john

Andy

Joakim

 

SELECT DECODE(ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL),1,E.DEPTNO, NULL) D, ENAME FROM EMP E

CheapSexCams
You can leave a response, or trackback from your own site.

Leave a Reply

Powered by k2schools
%d bloggers like this: