4.1. SELECT
a) SELECT statement is used to retrieve records from one or more tables in a SQL Server database.
QUERY :- SELECT * FROM tbname;
EXAMPLE :- SELECT * FROM employee;
This display all data from employee.
b) WHERE :
The WHERE clause is a filter that defines the conditions each row in the source tables must meet to qualify for the SELECT. Only rows that meet the conditions contribute data to the result set. Data from rows that do not meet the conditions is not used.
EXAMPLE: - SELECT * FROM employee where salary >= 20000;
c) ORDER BY :
- If you want to display data in ascending order then write this query. it’s display all employee data in ascending order of emp_id column
QUERY: - SELECT * FROM tbname order by column_name;
(by default ascending order)
EXAMPLE :- SELECT * FROM employee order by emp_id;
- If you want to display data in decreasing order then write this query. It display all employee data in decreasing order of emp_id column.
QUERY :-SELECT * FROM tbname order by column_name desc;
(data are in descending order)
EXAMPLE :- SELECT * FROM employee order by emp_id desc;
- If you want to display data with comparison then write this query. It display all data if conditions is true.
- In below example display those data where the employee salary is greater than 20000.
d) CONDITION :
- If you want to display is some range then write this query. It display all data in between both value.
QUERY :-SELECT * FROM tbname where column_name(comparison oprator) value ;
EXAMPLE :- SELECT * FROM employee where salary >= 20000;
- If you want to display is some range then write this query. It’s display all data in between both value.
- In below example display those data which salary is grater or equal to 2000 and less or equal to 6000.
QUERY:-SELECT * FROM tbname WHERE column_name between VALUE 1 and VALUE 2;
(its include value1and value 2 )
EXAMPLE: - SELECT * FROM employee where salary between 2000 and 6000;
e) LIKE :
This query display the string which have first character ’H’ in f_name column.
QUERY :-SELECT * FROM tbname where column_name like ‘H%’;
EXAMPLE :- SELECT * FROM employee where f_name like ‘H%’;
This query display the string which have last character ’H’ in f_name column.
QUERY :-SELECT * FROM tbname where column_name like ‘%H’;
EXAMPLE :- SELECT * FROM employee where f_name like ‘%H’;
This query display the string that we don’t know exact location of ’H’ in f_name column.
QUERY :-SELECT * FROM tbname where column_name like ‘%H%’;
EXAMPLE :- SELECT * FROM employee where f_name like ‘%H%’;
This query display the string which have second character is ’H’ in f_name column.
QUERY :-SELECT * FROM tbname where column_name like ‘_H%’;
EXAMPLE :- SELECT * FROM employee where f_name like ‘_H%’;
This query display the string which have second last character is ’H’ in f_name column.
QUERY :-SELECT * FROM tbname where column_name like ‘%H_’;
EXAMPLE :- SELECT * FROM employee where f_name like ‘%H_’;
This query display the string which have second character ’H’ in f_name column and the total size of string is 5.
QUERY :-SELECT * FROM tbname where column_name like ‘_H_ _ _’;
EXAMPLE :- SELECT * FROM employee where f_name like ‘_H_ _ _’;
f) IN AND NOT IN :
This query display all data which f_name are Vinay and Raj
QUERY :-SELECT * FROM tbname where column_namein (‘data1’ , ’data2’…) ; (gives specific data only)
EXAMPLE :- SELECT * FROM employee where f_name in(‘Vinay’,’Raj’);
This query display all data which have not f_name are Vinay and Raj.
QUERY :-SELECT * FROM tbname where column_name
NOT in (‘data1’ , ’data2’…) ; (gives specific data only)
EXAMPLE :- SELECT * FROM employee where f_name NOT in(‘Vinay’,’Raj’);