SELECT Statement in SQL Explained
As you know,
Structured Query Language (SQL) consists of various ways to define various
database components. Data Manipulation Language (DML) is one of the ways to view
the database components which is frequently used one. A DML statement is used
to view the data stored in tables, insert new records and update the existing
record values. In this post, let us discuss in clear about the SELECT
statement.
A DML
statement (also called as SQL expression) basic structure consists of at least
two clauses, namely, SELECT and FROM to retrieve some results. To pinpoint some
information in a table, we need one more clause called WHERE clause as part of
the basic SQL structure. To display the required information from one or more
specific tables in a required way, we could use these three basic clauses
differently.
Simple
structure of SQL statement;
SELECT parameters (*/list
of attributes/functions/…)
FROM parameters (table/set
of tables/views/…)
WHERE parameters (condition/set
of conditions);
See an
example SELECT statement;
SELECT * FROM
employee WHERE emp_id = ‘E101’;
The above SQL
query (here after mentioned as query) will display all the records whichever
satisfying the condition emp_id=’E101’ from employee table.
Every clause in
SQL is capable for representing something. Those are;
SELECT clause:
SELECT clause
uses
list of one or more attributes as its parameters. Using SELECT clause
one could mention what we would like to show as the result. That is,
- do you like to display complete table(s),
- do you like to display select attributes from table(s)
- do you like to perform some arithmetic operations
- do you like to display the result of application of functions etc.
As a result,
the SELECT clause is designed to mention all the above and more.
Parameters
|
Function/Result
|
Example Query
|
*
|
‘*’ means all the attributes of the table will be displayed as result
|
SELECT * FROM Employee;
|
Attribute/list of attributes
|
The attribute/list of attributes from the table will be shown in the
result. All other attributes of table will not be shown
|
SELECT Emp_ID, EName FROM Employee;
|
DISTINCT attribute_name
|
The unique values stored in the specified attribute will be shown as
result.
|
SELECT DISTINCT EName FROM Employee;
This will show only one Employee Name if more than one such names
exist.
|
Aggregate functions
(max(), min(), avg(), sum(), count())
|
According to the aggregate function used, the aggregated value will
be shown. Usually, the result will contain only one record in the result in
case of aggregate functions.
|
SELECT SUM(salary) FROM Employee;
|
Table 1 – List of parameters of SELECT clause
Detailed Example:
Emp_ID
|
EName
|
Department
|
Address
|
Salary
|
E101
|
Kumar
|
Finance
|
Chennai
|
15000
|
E102
|
Virat
|
Marketing
|
Bangalore
|
25000
|
E105
|
Sachin
|
Production
|
Mumbai
|
25000
|
E103
|
Gurucharan
|
Finance
|
Kolkata
|
30000
|
E110
|
Kumar
|
Maintenance
|
Chennai
|
14000
|
Table 2 - Employee
Result of the
queries given in the Example Query column of table 1 for the instance given in
table 2 are discussed below. (Note : note carefully the heading of results)
Query 1: SELECT * FROM Employee;
Result : Table 2 as it is.
Query 2: SELECT Emp_ID, EName FROM
Employee;
Result :
Emp_ID
|
EName
|
E101
|
Kumar
|
E102
|
Virat
|
E105
|
Sachin
|
E103
|
Gurucharan
|
E110
|
Kumar
|
Query 3: SELECT DISTINCT EName FROM
Employee;
Result :
DISTINCT EName
|
Kumar
|
Virat
|
Sachin
|
Gurucharan
|
Query 4: SELECT SUM(salary) FROM
Employee;
Result :
SUM(Salary)
|
109000
|
Query 5: SELECT Emp_ID, EName, salary*(10/100)
FROM Employee;
Result :
Emp_ID
|
EName
|
Salary*(10/100)
|
E101
|
Kumar
|
1500
|
E102
|
Virat
|
2500
|
E105
|
Sachin
|
2500
|
E103
|
Gurucharan
|
3000
|
E110
|
Kumar
|
1400
|
FROM clause:
The parameters
of the FROM clause are the list of one or more tables/list of one or more views/list
of both tables and views. You can refer to table 1 ‘Example Query’ column for
example usages of FROM clause for single table queries and single view queries.
For queries which involve multiple tables, you must understand the concept of
JOIN. We shall discuss about that later.
WHERE clause:
The parameters
of the WHERE clause are the list of
conditions written in the form,
(Attribute_Name θ
Value) Ф (Attribute_Name θ Value) Ф
(Attribute_Name θ Value)
Ф …
Here, θ is the
comparison operator [ >, <, ≥, ≤,
=, and <> (Not equal to) ],
and Ф is the logical
connective [and, or, not]
For example,
- Emp_ID = ‘E101’ (Character based values are provided within pair of apostrophes ‘ ’)
- Salary > 10000 (Number based attribute values can be provided without apostrophes)
- Emp_ID = ‘E101’ and Salary <5000
- Emp_ID = ‘E101’ and Salary <5000 or EName = ‘Kumar’
It is very
easy to pinpoint some information of any tables using WHERE conditions. Hence,
WHERE clause can be termed as Filtering Component of SQL query.
Let us see
the results of some queries with WHERE conditions; (Note : the satisfied
records are shown with Bolded and Italicized values.)
Query 1 :
SELECT * FROM Employee WHERE Emp_ID = ‘E110’;
Result :
Emp_ID
|
EName
|
Department
|
Address
|
Salary
|
E110
|
Kumar
|
Maintenance
|
Chennai
|
14000
|
The result
displays the table as it is because we used * in the SELECT clause.
Query 2 :
SELECT Emp_ID, EName FROM Employee WHERE EName = ‘Kumar’;
Result :
Emp_ID
|
EName
|
E101
|
Kumar
|
E110
|
Kumar
|
Query 3 : SELECT Emp_ID,
EName, Salary FROM Employee WHERE EName = ‘Kumar’ AND Salary > 14000;
Result :
Emp_ID
|
EName
|
Salary
|
E101
|
Kumar
|
15000
|
************************
Related links:
No comments:
Post a Comment