You are here

Select Statement

select

SELECT STATEMENT

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 (*).

      Syntax:

      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>

<where>

<Group by>

<Having>

<order by>

<OFFSET-FETCH>;

DISTINCT:

  • The DISTINCT keyword is use to remove duplicates rows during query processing.

TOP:

  • 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.

Where clause:

  • 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:

  • Having clause provides a search condition that each group must satisfy.
  • According to logical query processing Having clause is process after Group by  clause.

Order By:

  • 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

OFFSET-FETCH :

  • 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.

Example:

Table: person

id

fname

lname

city

Salary

1

Riya patel vvnagar 20000
2 Nikunj patel vvnagar

10000

3

Chirag Joshi anand 15000

4

Karan Shah nadiad

80000

5 Anika Panchal bakrol

50000

6

Jahnvi Joshi anand

40000

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.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

 

One thought on “Select Statement

Leave a Reply

Top