SQL – Select – Explanations, Examples and Syntax

SQL SELECT is used for displaying specific information from a database table. It indicates that some data has to be fetched from the table.

Basic Select Statements

Previously we discussed about how to create database and tables and how to insert/update data in those tables now to use that data in our application we have to use the Select statement.
SELECT is used to retrieve records from the database table.

The general syntax is:

 
SELECT expressions
FROM tables
WHERE [conditions];
 

Consider an example table “codingeek”:

Ref_noNameCodeCourse
 132 caty 03 java
 456 paul 01 c
747 john02 python
 831 smita 04 .net
 296 jeny 02 python:

Now we will see the basic queries using select command to fetch the required information,

Selecting all rows and columns

If you want to select all the rows and columns from a table and display it, follow as below:

Syntax:

 
SELECT * FROM table;

Example:

 
SELECT * FROM codingeek;
Ref_noNameCodeCourse
 132 caty 03 java
 456 paul 01 c
747 john02 python
 831 smita 04 .net
 296 jeny 02 python:

Hence all the rows and columns of a table are selected and displayed.

Selecting only specific rows

If you do not want the entire data and only specific piece of information is required, select can be used as follows:

 
--SYNTAX
SELECT * FROM table_name
WHERE condition;

--EXAMPLE
SELECT * FROM codingeek
WHERE Ref_no = 831;
 

Output:

Ref_noNameCodeCourse
 831 smita 04 .net

Only the selected rows based on the condition is displayed.

Selecting specific data

When you don’t want all the column information, you can go for this:

 
--SYNTAX
SELECT columnnames
FROM table
WHERE condition;

--EXAMPLE
SELECT Name, Code
FROM codingeek
WHERE Course='python';
 

Output:

Ref_noNameCodeCourse
747 john02 python
 296 jeny 02 python

As you can see, only the selected rows and columns are displayed.

Selecting only specific columns

If you want to select and display all the rows and only specific columns then you can use:

 
--SYNTAX
SELECT column_names
FROM table;

--EXAMPLE
SELECT Ref_no, Name
FROM codingeek;

 

Output:

Ref_noName
 132 caty
 456 paul
747 john
 831 smita
 296 jeny

Only the selected columns are displayed. Note that in the absence of condition, all the rows will be displayed.


‘Select Into’ Statement

“SELECT Into” in SQL is used to copy data from one table to another table. Either all or some data can be copied and inserted into another table.

As you have seen all the basic statements, SELECT Into also follows similar syntax adding “INTO tablename” in these statements which former displayed the selected information, now it will copy the selected data into another table.

Example:

Consider an empty table for understanding purpose:

“practice”:

ID_noS.nameIndex_noCourse

Let’s insert all the values from “codingeek” table into “practice” table as follows:

 
SELECT * INTO practice
FROM codingeek;

Output:

 
SELECT * FROM practice;
ID_noS.nameIndex_noCourse
 132 caty 03 java
 456 paul 01 c
747 john02 python
 831 smita 04 .net
 296 jeny 02 python:

All the rows and columns are successfully inserted into “practice” table.

Another example:

Let’s take another example which inserts only specific rows based on the condition,

 
SELECT *
INTO practice
FROM codingeek
WHERE Ref_no > 400;

Output:

ID_noS.nameIndex_noCourse
 456 paul 01 c
747 john02 python
 831 smita 04 .net

Select Top/Limit

Select Top is used to limit the select query with some fixed value or percentage. Limit is a synonym of Top and is used for similar purpose.

Example:

 
SELECT TOP 4 * FROM codingeek;

or

 
SELECT * FROM codingeek
LIMIT 4; 

This will retrieve Top 4 records from the table as:

Ref_noNameCodeCourse
 132 caty 03 java
 456 paul 01 c
747 john02 python
 831 smita 04 .net

Thus only the top 4 records are fetched and displayed.

TOP with percentage:

The value of Top can also be given in terms of percentage. It will retrieve only those data based on the appropiate percentage.

Example:

 
SELECT TOP(20) PERCENT
Name, Course
FROM codingeek;
 

As per the example, only first 20% data is fetched and inserted into another table. Here, only two columns are selected.

Output:

NameCourse
 caty java
 paul c

20% of the rows that is top 2 rows are selected and copied with the selected columns.

LIMIT with offset:

Offset is used along with the LIMIT which allows us to offset first record.

For example,

 
SELECT Name, Course
FROM codingeek
LIMIT 3 OFFSET 1;
 

Output:

NameCourse
paulc
johnpython
smita.net

The result shows that the OFFSET value of 1 means the first record is skipped and from the remaining records, the top 3 data is fetched and inserted.


Select distinct:

Select distinct is used to eliminate duplicate rows from the selected columns in the table. It retrieves only distinct values.

It uses similar syntax as that of basic select statements where you can select distinct values from all columns or selected columns based on the requirement.

Example:

 
SELECT DISTINCT Ref_no, Name 
    SELECT DISTINCT Ref_no, Name  FROM codingeek;

Output:

Ref_noName
 132 caty
 456 paul
747 john
 831 smita

Only the distinct rows from the selected columns Ref_no, Name are selected and displayed.

Another example:

 
SELECT DISTINCT * FROM codingeek;

Output:

Ref_noNameCodeCourse
 132 caty 03 java
 456 paul 01 c
747 john02 python
 831 smita 04 .net

All the rows are selected after eliminating the duplicate values.


Learning never exhausts the mind!!

Do share and subscribe and comment below for any suggestion, query or opinion.
Keep Coding! Happy Coding 🙂

Recommended -