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

************************

Related links:

• CREATE TABLE statement

• Data Manipulation Languge (DML)

• Database query languages

Closure of Set of Functional Dependencies

Closure / Closure of Set of Functional Dependencies / Different ways to identify set of functional dependencies that are holding in a relation / what is meant by the closure of a set of functional dependencies illustrate with an example

Introduction

Functional Dependencies are the important components in database design which would help us in identifying bad designs. Another important functionality of FDs is that they really express the facts about the database we are designing. Hence, it is very vital to identify all the possible set of functional dependencies hold in a table (relation).

What are the ways to identify functional dependencies?

1. Very simple way is to look for the dependency of one or more columns on the other columns using a sample data set.
The problem here is that the data set we are about to use is not complete. Hence, for some attributes we can anticipate the future set of values to be stored. But for most of the attributes, it is impossible. Especially, when a table is of more number of columns, it’s a kind of impossibility to express all the FDs holding by all the attributes. Hence, the set of functional dependencies which one could infer from the table directly is incomplete.

2. The second way is to identify the basic set of functional dependencies holding on a table which are easily visible and error free. Then, apply closure of set of Functional dependencies rules to derive the other hidden functional dependencies holding on the relation. We would mention those hidden functional dependencies as “functional dependencies that are logically implied”. When we are able to identify all the set of FDs holding by a relation, then we could say that the set F of FDs is complete.

In this post let us discuss about Closure of Set of Functional Dependencies.

Closure of Set of Functional Dependencies

"The closure of F, denoted as F+, is the set of all regular Functional Dependencies that can be derived from F".

This is used to discover some of the hidden functional dependencies so as to design a better database.

Consider the Armstrong's axioms developed by William W.Armstrong. Those axioms provide simpler technique to identify set of other functional dependencies (hidden). Let us list all the axioms along with the additional rules once again.

Reflexivity rule	If X is a set of attributes, and Y is subset of X, then we would say, X → Y.
Augmentation rule	If X → Y, and Z is another set of attributes, then we write XZ → XY.
Transitivity rule	If X → Y, and Y → Z, then X → Z is true.
Union rule	If X → Y and X → Z, then X → YZ is true.
Decomposition rule	Its reverse of Rule 4. If X → YZ, then X → Y, and X → Z are true
Pseudotransitivity rule	If X → Y and ZY → A are true, then XZ → A is also true.

Let us consider set F of functional dependencies hold on a relation R. We can derive additional functional dependencies from the set of given functional dependencies. But, still we may have some more hidden functional dependencies. We could derive some of the additional hidden functional dependencies from F on applying the above listed rules. In other words, we could deduce a new functional dependency from two or more functional dependencies of set F. Suppose, R is a relation with attributes (A, B, C, D, E, F) and with the identified set F of functional dependencies as follows;

F = { A → B, A → C, CD → E, B → E, CD → F }

Let us apply the above listed rules on every functional dependency of F to identify the member of F⁺ (the closure of functional dependency is termed as F⁺, i.e, set F added with new members resulting in F⁺).

1. A → E is logically implied. From our F we have two FDs A → B and B → E. By applying Transitivity rule, we could infer A → E. That is, if A can uniquely determine B and B can uniquely determine E then A can determine E (through B).

2. A → BC is logically implied. It can be inferred from the FDs A → B and A → C using Union rule.

3. CD → EF is logically implied by FDs CD → E and CD → F using Union rule.

4. AD → F is logically implied by FDs A → C and CD → F using Pseudotransitivity rule.

Like this, we can continue identifying the new members for F⁺. The procedure to find set F+ can be written as follows in pseudo code.

F+ = F Repeat                 For each functional dependency FD in F+                                 Apply reflexivity and augmentation rules on f                                 Add the result to F+                 For each pair of functional dependencies f1 and f2 in F+                                 If f1 and f2 can be combined using Transitivity                                                 Add the result to F+ Until F+ does not change any further.

This procedure does not show the additional rules Union, Decomposition, and Pseudotransitivity. The reason is, these additional rules are actually inferred from basic axioms. Also additional rules can be proved using Armstrong’s axioms. The procedure can be stopped when we started to get the functional dependencies already existing in F⁺.

For a relation with set of n attributes, there are 2ⁿ⁺¹ possible functional dependencies. For example, if R has 3 attributes, then 2³⁺¹ = 16 functional dependencies are possible.

Go to Normalization - Solved Exercises page

Go to How to find closure of an attribute? page