Oracle SQL advanced

1. TOP clause
 
The TOP clause is used to specify the number of records to be returned.
 
The TOP clause is very useful for large tables with thousands of records.
 
Select top number | percent column_name (s)
FROM table_name
Example: select top 2 * FROM Persons
 
 
 
2. LIKE Operator
 
The LIKE operator is used to search for the specified mode in the WHERE clause.
 
SELECT column_name (s)
 
FROM table_name
 
WHERE column_name LIKE pattern
 
Example: SELECT * FROM PersonsWHERE City not like '% lon %'
 
SELECT * FROM PersonsWHERE City not like '% lon'
 
SELECT * FROM PersonsWHERE City not like 'lon %'
 
 
 
3. SQL wildcard
 
When searching for data in a database, the SQL wildcard can replace one or more characters.
 
The SQL wildcard must be used with the LIKE operator.
 

 

Wildcard
 
Description
 

%
 
Replace one or more characters
 

_
 
Replace only one character
 

[Charlist]
 
Any single character in the character Column
 

 

[^ Charlist]
 
Or
 
[! Charlist]
 
Any single character not in the character Column
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Example: SELECT * FROM PersonsWHERE FirstName LIKE '_ eorge'

SELECT * FROM PersonsWHERE City LIKE '[a1] %'
 
 
 
4. IN Operator
 
The IN operator allows us to specify multiple values IN the WHERE clause.
 
SELECT column_name (s) FROM table_nameWHERE column_name IN (value1, value2 ,...)
 
Example: SELECT * FROM PersonsWHERE LastName IN ('adams', 'cart ')
 
 
 
5. BETWEEN Operator
 
The BETWEEN operator... AND selects a data range BETWEEN two values. These values can be numerical values, text values, or dates.
 
SQL BETWEEN Syntax
 
SELECT column_name (s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
Example: SELECT * FROM PersonsWHERE LastNameBETWEEN 'adams' AND 'cart'
 
 
 
6. Alias
 
By using SQL, you can specify an Alias for the column name and table name (Alias ).
 
Table SQL Alias syntax
 
SELECT column_name (s)
FROM table_name
AS alias_name
 

Column SQL Alias syntax
 
SELECT column_name AS alias_name
FROM table_name
 
Example: SELECT po. OrderID, p. LastName, p. FirstNameFROM Persons AS p, Product_Orders AS poWHERE p. LastName = 'adams' AND p. FirstName = 'john'

1 2 3 4 Next Page