SQL

001. List all the clerks and analysts

002. List the employees in dept 10 & 20

LIKE Operator

The LIKE operator in Oracle SQL is used to search for a specified pattern in a column. It’s commonly used with character data types (CHAR, VARCHAR2, etc.) in WHERE clause.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

Wildcard Characters

Percent Sign (%)
- Represents zero, one, or multiple characters
- Example: 'S%' matches any value that starts with “S”
Underscore (_)
- Represents a single character
- Example: '_M%' matches any value that has “M” as the second character

Common Pattern Examples:

Pattern	Description	Example Matches
`'S%'`	Starts with “S”	“Smith”, “Sales”, “S”
`'%S'`	Ends with “S”	“Jones”, “Sales”, “S”
`'%S%'`	Contains “S” anywhere	“Smith”, “Jones”, “Sales”
`'S_M%'`	Starts with “S”, third character is “M”	“Smith”, “Smooth”
`'_A%'`	Second character is “A”	“James”, “David”
`'S__%'`	Starts with “S” and at least 3 characters long	“Smith”, “Scott”

003. Find employees whose names start with 'S'

004. Find employees whose names end with 'S'

005. Find employees whose names contain 'LL' anywhere

006. Find employees whose names have 'A' as the second character

007. Find employees with job titles starting with 'SALES'

008. Find employees whose names are exactly 5 characters long

009. Find employees whose names start with 'J' and end with 'S'

010. Find employees whose names does not start with 'S'

011. List the employees whose name is having letter ‘E’ as the last but one character

012. List all the employees whose name is having letter ‘R’ in the 3rd position

Functions

Function Name	Description	Syntax	Example	Output
1. LOWER()	Converts a string to lowercase.	`LOWER(string)`	`SELECT LOWER('ORACLE SQL') FROM dual;`	`oracle sql`
2. UPPER()	Converts a string to uppercase.	`UPPER(string)`	`SELECT UPPER('oracle sql') FROM dual;`	`ORACLE SQL`
3. INITCAP()	Converts the first letter of each word to uppercase and the rest to lowercase.	`INITCAP(string)`	`SELECT INITCAP('oracle database') FROM dual;`	`Oracle Database`
4. LENGTH()	Returns the number of characters in a string.	`LENGTH(string)`	`SELECT LENGTH('Oracle') FROM dual;`	`6`
5. SUBSTR()	Returns a substring from a string starting at a specified position.	`SUBSTR(string, start_position, [length])`	`SELECT SUBSTR('Database', 2, 4) FROM dual;`	`ATAB`
6. INSTR()	Returns the position of a substring in a string.	`INSTR(string, substring [, start_position [, occurrence]])`	`SELECT INSTR('DATABASE', 'A', 2, 1) FROM dual;`	`4`
7. LPAD()	Pads the left side of a string with a specific character to a specified length.	`LPAD(string, total_length, pad_char)`	`SELECT LPAD('SQL', 6, '*') FROM dual;`	`***SQL`
8. RPAD()	Pads the right side of a string with a specific character to a specified length.	`RPAD(string, total_length, pad_char)`	`SELECT RPAD('SQL', 6, '*') FROM dual;`	`SQL***`
9. LTRIM()	Removes specified characters (or spaces by default) from the left of a string.	`LTRIM(string [, trim_chars])`	`SELECT LTRIM(' Oracle') FROM dual;`	`Oracle`
10. RTRIM()	Removes specified characters (or spaces by default) from the right of a string.	`RTRIM(string [, trim_chars])`	`SELECT RTRIM('Oracle ') FROM dual;`	`Oracle`
11. TRIM()	Removes specified characters or spaces from both ends of a string.	`TRIM([LEADING	TRAILING	BOTH] trim_char FROM string)`
12. REPLACE()	Replaces occurrences of a substring with another substring.	`REPLACE(string, search_string [, replace_string])`	`SELECT REPLACE('SQL Tutorial', 'SQL', 'PL/SQL') FROM dual;`	`PL/SQL Tutorial`
13. TRANSLATE()	Replaces each character in a string with a corresponding character in another string.	`TRANSLATE(string, from_string, to_string)`	`SELECT TRANSLATE('12345', '123', 'ABC') FROM dual;`	`ABC45`
14. CONCAT()	Joins two strings together.	`CONCAT(string1, string2)`	`SELECT CONCAT('Oracle', ' SQL') FROM dual;`	`Oracle SQL`
15. \|\|		(Concatenation Operator)	Concatenates two or more strings.	`string1
16. ASCII()	Returns the ASCII value of the first character of a string.	`ASCII(string)`	`SELECT ASCII('A') FROM dual;`	`65`
17. CHR()	Returns the character for a given ASCII value.	`CHR(ascii_number)`	`SELECT CHR(65) FROM dual;`	`A`
18. REVERSE() (Oracle 21c+)	Returns the reverse of a string.	`REVERSE(string)`	`SELECT REVERSE('Oracle') FROM dual;`	`elcarO`
19. SOUNDEX()	Returns a phonetic representation of a string for comparison.	`SOUNDEX(string)`	`SELECT SOUNDEX('Smith') FROM dual;`	`S530`
20. DUMP()	Returns the internal representation (datatype code, length, and byte values) of a string.	`DUMP(string [, return_format [, start_position [, length]]])`	`SELECT DUMP('A') FROM dual;`	`Typ=96 Len=1: 65`