The Full Fledged SQL Select Statement

The Select statement is used to retrieve data from a database.

Syntax: Shown below is the syntax of a full fledged Select statement.

SELECT <COLUMNS>
FROM <TABLES>
WHERE <CONDITION>
GROUP BY <COLUMNS>
HAVING <GROUP CONDITION>
ORDER BY <COLUMNS> <ASC|DESC>

Selection and Projection

Select statement is used to perform two types of operations - Selection and Projection.

  • Selection refers to choosing rows of the table from which data is needed to be extracted
  • Projection refers to choosing columns of the table to extract the data
Example:
SELECT NAME, AGE, ADDRESS FROM EMPLOYEE WHERE SALARY > 50000

In the above example:

  • 'name, age, address' is the projection part, it selects the columns from which data is to be extracted
  • 'salary > 50000' is the selection part, it selects those rows where salary is greater than 50000

Distinct Keyword

The DISTINCT keyword is used to display unique (distinct) values.

Example:
SELECT DISTINCT DEPTNO
FROM EMP;

The above statement will produce D20, D20 & D10 as output as shown in the image below:

SQL Distinct Keyword

Where Clause

WHERE clause is used for row restriction. Row restriction is a concept through which data can be extracted condition-wise. It is also called as row filtration.

SQL provides different operators that can used to perform row restriction. They are:

  • Logical operator (AND, OR, NOT)
  • Relational operator (<, >, <=, >=, <>, =)
  • Arithmetic operator (+, -, *, /)
Examples:

//Display those records where salary is greater than 2000
SELECT * FROM EMP 
WHERE SAL>2000;

//Display those records where job is manager or salesman
SELECT * FROM EMP
WHERE JOB='MANAGER' OR JOB='SALESMAN';

//Display those records where job is not manager
SELECT * FROM EMP
WHERE JOB<>'MANAGER';

More Operators

Apart from the conventional operations, SQL provides 4 exclusive operators to perform row restriction. They are:

  • BETWEEN AND
  • IN
  • LIKE
  • IS NULL

BETWEEN AND Operator:

BETWEEN AND operator is an extension of > and < operator. It is responsible to extract data from a table depending on a given range. It can operate on two types of datatypes: number and date.

Examples:
SELECT * FROM EMP 
WHERE SAL BETWEEN 2000 AND 3000;

SELECT * FROM EMP 
WHERE JOINING_DATE BETWEEN 02-OCT-2013 AND 05-MAR-2014;

IN Operator:

IN operator is extension of the OR operator. It can operate on any datatype.

Examples:
//Display records where job is either manager, salesman or peon
SELECT * FROM EMP
WHERE JOB IN (‘MANAGER’, ‘SALESMAN’, ‘PEON’);

//Display records where salary is either 2000, 3000 or 5000
SELECT * FROM EMP
WHERE SAL IN (2000, 3000, 5000);

//Display those records where job is neither manager nor salesman
SELECT * FROM EMP
WHERE JOB NOT IN (‘MANAGER’, SALESMAN’);

The first examples displays records where job is either manager, salesman or peon, second example displays records where salary is either 2000, 3000 or 5000. The third example uses NOT operator along with the IN operator, so it displays those records where job is neither manager nor salesman.

LIKE Operator:

Like operator is a powerful query operator that is capable of extracting data from a table depending on the position of a character. It can operate on two datatypes - character and date. LIKE operator provides two types of wild characters:

  • Underscore (_)  ? It represents a single character
  • Percentile (%)  ? It represents multiple characters
Examples:
//Display those records where name starts with 'S'
SELECT * FROM EMP
WHERE ENAME LIKE 'S%';

//Display those records where name contains 'A' in any position
SELECT * FROM EMP
WHERE ENAME LIKE '%A%';

//Display these records where name contains 'F' in 3rd position and 'N' in last position
SELECT * FROM EMP
WHERE ENAME LIKE '__F%N';

//Display those records where hire date is in the year 2010
SELECT * FROM EMP
WHERE HIREDATE LIKE '%2010';

//Display those records where hire date is in the month June
SELECT * FROM EMP
WHERE HIREDATE LIKE '%JUN%';

IS NULL Operator:

IS NULL operator is used to match NULL value. It is used to extract data from a table depending on NULL value.

Example:
//Display those records where the bonus is NULL
SELECT * FROM EMP
WHERE BONUS IS NULL;

//Display those records where the bonus is not NULL
SELECT * FROM EMP
WHERE BONUS IS NOT NULL;

Group By Clause

Group By clause is used to logically segregate the data into different group.

Example: Consider the table below:

SQL Group By Example
//Display department-wise sum of salary, maximum salary and minimum salary
SELECT DEPTID, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL), COUNT(*)
FROM EMP
GROUP BY DEPTID;

The query above would produce the following result

SQL Group By Result

Note: Only the columns used in the Group By clause, are allowed to be used in the Select statement. Below statement is invalid, because NAME column is not used in the Group By clause.

SELECT NAME, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL)
FROM EMP GROUP BY DEPTID;

Count Function With Group By

Count can accept two types of values as parameter - * and column name.

  • * returns the the total number of records in a group
  • column name returns the number of values in the column that are not null

Having Clause

Having clause in SELECT statement is responsible to restrict the group depending on a condition.

The basic difference between where and having clause is, 'where' is meant for row restriction and 'having' is meant for group restriction.

Example: Consider the table below:

SQL Having Example
SELECT DEPTID, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL), COUNT(*)
FROM EMP
GROUP BY DEPTID
HAVING MAX(SAL) > 5000;

The above statement restricts those groups where maximum salary is less than 5000. Below is the result produced.

SQL Having Result

Order By Clause

The Order By clause is used to arrange the records returned by a SQL statement into a proper order, either ascending or descending. It has two attributes to define the order:

  • ASC - Ascending Order(Default)
  • DESC - Descending Order

Example: Consider the table below:

SQL Order By Example
SELECT * FROM EMP
ORDER BY SAL DESC;

SELECT * FROM EMP
ORDER BY SAL ASC;

The result of the above two statements are shown below:

SQL Order By Result
POPULAR ARTICLES

Creating Conditional Beans in Spring

The concept of condition beans enables Spring to restrict the creation of any bean depending on the evaluation of a condition. These beans get created only when a preset condition is evaluated as true

View Article

Accepting Request Param and Path Variable in Spring Controller

Spring MVC provides various ways through which a client browser can pass data to the Controller. In this article we will discuss about accepting Request Parameters and Path Variables in Spring Contr..

View Article

Generate Namespace & Schema Information using JAXB

Most xml documents used in enterprise applications makes use of namespace to avoid element name conflicts. This article talks about generating these namespace and schema information when marshaling...

View Article

Switching Database Profile using Spring Profiles

We are most likely to have separate db configuration for different environment like development and production environment. Spring profiles provide a convenient way to switch db profiles at runtime.

View Article

SQL and its Sub-Languages

SQL (Structured Query Language) is a language understood by most modern databases. It is an ANSI (American National Standard Institute) standard language which is used to manipulate databases.

View Article

Introducing JUnit Rule

Junit Rules allows developers to add additional functionalities that can applied to all test methods in a test class. It is similar to the concept of custom test runners but with reduced restrictions.

View Article

Addressing Ambiguity in Spring Autowiring

Spring autowiring is powerful concept, but we should be very cautious while using it. We may end up in creating ambiguity while autowiring beans, which will cause autowiring to fail.

View Article

Creating and Using Synonym in Oracle Database

Synonyms are database objects used to provide duplicate names to existing objects in the database. It is just an alternate name used to hide the original name of the object.

View Article

Creating and Using Sequence in Oracle Database

A sequence is used to auto-generate numbers in ascending or descending order which can serve as a primary key or a part of it (in case of composite key).

View Article

Creating and Manipulating Constraints in Oracle Database

Constraints are used to impose certain rules on columns to avoid invalid data entry into the table. If any of the constraint is violated the operation fails.

View Article

Integrating Log4J with Perf4J for Performance Logging

Perf4j is an open source logging framework used primarily for monitoring performance statistics in java applications. Log4j has the ability to integrate with perf4j to capture performance data.

View Article

Tagging in GIT

Tagging allows us to mark a specific point in the commit history or snapshot. A tag is typically used to mark a project release. This article shows how to create tags in Git.

View Article