SELECT Statement and its Clauses in DBMS

DatabaseMCA

The select statement is used to get the required data from the database according to the conditions, if any. This data is returned in the form of a table.

The basic syntax of the select statement is −

Select column 1, column 2 ... column N
From table_name

An example of the select statement is −

<Student>

Student_Number
Student_Name
Student_Phone
Student_Marks
Student_Major
Subject
1
Andrew
6615927284
95
Literature
2
Sara
6583654865
65
Maths
3
Harry
4647567463
48
Literature
4
Sally
6537837084
30
Literature
5
Anne
7457337732
88
Maths

Query −

Select Student_Name
From Student

This query yields the following result −

Student_Name
Andrew
Sara
Harry
Sally
Anne


Clauses in Select statement

The example of select statement given above is quite simple and not that useful in practice. So, there are many other clauses associated with select statement that make it more meaningful. Some of these are −

Where

The where clause is used to filter out data i.e it returns information that satisfies a certain condition. For example −

Select Student_Name
From Student
Where Student_Marks >50

This query will return the following result:

Student_Name
Andrew
Sara
Anne


Group by

This is mostly used with aggregate functions to group the result set according to the value of a column. For example −

Select Count (Student_Number), Student_MajorSubject
From Student
Group by Student_MajorSubject

This query will return the following result −

Count (Student_number)
Student_MajorSubject
3
Literature
2
Maths


Having

This is used along with Group By clause because Where clause could not be used by aggregate functions. For Example − 

Select Count(Student_number), Student_MajorSubject
From Student
Group by Student_MajorSubject
Having Count(Student_Number) > 2

This query will return the following result −

Count (Student_Number)
Student_MajorSubject
3
Literature


Order by

The order by keyword is used to sort the results in ascending or descending order. By default, the order is assumed to be ascending. For Example −

Select Student_Name
From Student
Where Student_Marks>50
Order by Student_Marks

This query will return the following result −

Student_Name
Sara
Anne
Andrew
raja
Published on 27-Jul-2018 17:27:05
Advertisements