In this post we will discuss about select statement in SQL server 2016.
- The select statement is used to retrieve records from the one or more tables.
- To retrieve all column of the table use an asterisk (*).
Select * from <table_name>;
- In this statement we can assign our own alias to the expression that define the result .There are three ways to define alias. <expression> As <alias>, < expression > <alias>, < alias>=<expression>.
The general Syntax:
Select <DISTINCT>/<TOP(n)> <col1>,<col2>,…… from <table_name>
- The DISTINCT keyword is use to remove duplicates rows during query processing.
- With the TOP option we can filter a requested number or present of rows returned by a query.
- It works with order by
- The present option computes the ceiling of the resulting number of rows if it’s not whole.
- SQL provides option of using Where clause in a query to retrieve specific rows from the table that evaluates the specified condition to true.
- According to logical query processing Where clause is executed after the From clause.
Group by clause:
- Group by creates groups for output rows, according to a unique combination of values specified in the Group by clause.
- When we used Group by clause we have to use at-least one aggregate function.
- According to logical query processing Group by clause is executed after the Where clause.
- Having clause provides a search condition that each group must satisfy.
- According to logical query processing Having clause is process after Group by clause.
- The order by clause is used to sort result either ascending or descending order.
- It sort result in ascending order if the sorting order is not specified. For descending order we have to explicitly specify DESC after the column name.
- According to logical query processing Order by clause is process after select
- The OFFSET-FETCH is an extension to the order by clause.it has row skipping ability.
- This option is useful for ad-hoc paging purposes.
- The OFFSET clause indicates how many rows you want to skip(0 if you do not want to skip any row). Then optionally specify the FETCH clause this indicates how many rows you want to filter.
- This option requires that ORDER BY clause is present.
- You can use the NEXT keyword if you skip some rows to indicate how many rows to filter and FIRST keyword if you not skip any row.
- A FETCH clause requires an OFFSET clause, but the OFFSET clause not requires FETCH clause.
Retrieve all records from person table
select * from person;
Retrieve fname and lname from person table
select fname,lname from person;
Retrieve all person name that have salary greater than 10000
select fname,salary from person where salary> 10000;
Retrieve unique city name from person table
select DISTINCT city from person;
Retrieve salary in ascending order
select fname,salary from person order by salary;
Retrieve fname in descending order
select fname from person order by fname DESC;
Display top 3 fname by highest salary
select TOP(3) fname,salary from person order by salary DESC;
Retrieve the lowest 3 fname not including the lowest two
select fname,salary from person order by salary OFFSET 2 ROWS fetch next 3 rows only;
Group the person city
select city,SUM(salary)as city,salary from person group by city;
Keep Visiting TechwithR.com for more upcoming top technical articles.