Monday, March 3, 2014

SQL SELECT Statement



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

No comments:

Post a Comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery