My 2 Cents What Is The Common Retrieving Sql Query Used For Database?

Swati Mishra

Content Writer
Based on pattern matching


String operator:

For match the specified portion of character string we used the LIKE keyword. The LIKE keyword permits for wildcard characters. These are the wildcard characters are using as an expression .The like operator is not case sensitive. These are the wildcard are describing below:

% represents any string of zero or more characters
(-) Represents a single character
[] Represents single character within specified range
[^] Represents any single character not within specified range.


Some of the examples are describe below by using like operator.

Code:
SELECT * FROM publishers
 
WHERE Country LIKE’ab_’


From above example returns all rows from publisher table in which the country name is start with ab and third character can be anything.


Code:
SELECT   title_id FROM Titles
 
WHERE title_id LIKE’A[bc]%’

From above example returns title_id from Titles table in which title_id starts with the A character and contain b or c in the second position.


Wild card characters search using LIKE keyword:


Code:
LIKE ‘ab%’		  all names start with “ab”
 
LIKE’%mno’		all names end with “mno”
 
LIKE’%rt%’		  all names shows the word which have letter “rt”
 
LIKE’_bd’			 all three letter names ending with ‘bd’
 
LIKE’[FK]%’		 All name start with “F” or “K”



Display rows by using NULL or NOT NULL:

NULL is a value for which data is not available. By using the IS NULL keyword we can retrieve the null values from the table. The NULL value always shows the result is always NULL, because the calculation is performing on NULL value is NULL. We cannot compare two NULL values.

Syntax of IS NULL:


Code:
SELECT col_ list
 
FROM tb_name
 
WHERE col_ name IS NULL
Where col_list is column list; col_name is column name and tb_name is table name.

Eample of IS NULL:


Code:
SELECT emp_name , sales
 
FROM employee
 
WHERE sales IS NULL
Above example retrieve only that row which sales is NULL.

Code:
Syntax of IS NOT NULL:
 
SELECT col_ list
 
FROM tb_name
 
WHERE col_ name IS NOT NULL

Where col_list is column list; col_name is column name and tb_name is table name

Example of IS NOT NULL:

SELECT emp_name , sales
FROM employee
WHERE sales IS NOT NULL

Above example retrieve only that row which sales is NOT NULL.

Displaying data by using Order by clause:

Order by is used to display data in specific order like ascending or descending order.

Syntax:

Code:
SELECT select_list
 
FROM table_name
 
ORDER BY column_name / select_list_number/expression [ASC/DSC]
Example:
Code:
SELECT emp_code, description
 
FROM employee
 
ORDER BY description ASC

Above example display all rows by ascending order.

Display the top few rows:

The TOP keyword:
THE TOP clause limits the number of row that come from the result and it is always necessary to use SELECT statement with the top clause.

Syntax:

SELECT[TOP n[PERCENT]] col_name
FROM tb_name
where n is the number of rows and PERCENT is the keyword used for return the n percent of rows

example:


SELECT TOP 20
Above example display the top 20 rows of result set.

The DISTINCT keyword:
THE DISTINCT keyword removes the duplicate rows from the table. The DISTINCT
Keyword also displays the limited result.

Syntax:

Code:
 SELECT [ALL/DISTINCT] col_name
 
FROM tb_name
 
WHERE search_condition
Where search condition is condition on which row is to be display.
 

krishatg

Content Writer
The most common data retrieving query used in sql is "Selelct ".

Syntax:
select <condition> from <table> where clause;

EXAMPLE: Suppose we have employee table and we want to view all the records, then the query used is:

Select * from emp;

It will display all the records stored in employee table.

Example2:- select command with where clause.

Select * from emp where empno=101;

It will display the record of employee having employee number as 101.
 
Top