< Previous Page | Home Page | Next Page >

SELECT¶

The select statement or SQL select query is used to fetch data from one or more than one tables. We can fetch few columns, few rows or entire table using SELECT Query based on the requirement.

Let's consider the following table of a firm's employees including the employee's name, age, salary and social security number (= primary key)

SSN	EMP_NAME	EMP_AGE	EMP_SALARY
101	Steve	23	9000.00
223	Peter	24	2550.00
388	Shubham	19	2444.00
499	Chaitanya	29	6588.00
589	Apoorv	21	1400.00
689	Rajat	24	8900.00
700	Ajeet	20	18300.00

Selecting one column

General Syntax:

SELECT column_name FROM table_name;

Applied to Example:

SELECT EMP_NAME FROM EMPLOYEES;

will return

EMP_NAME
Steve
Peter
Shubham
Chaitanya
Apoorv
Rajat
Ajeet

Selecting several columns

General Syntax:

SELECT column_name_1, column_name_2, ... FROM table_name;

Applied to example:

SELECT SSN, EMP_NAME FROM EMPLOYEES;

will return

SSN	EMP_NAME
101	Steve
223	Peter
388	Shubham
499	Chaitanya
589	Apoorv
689	Rajat
700	Ajeet

Selecting the entire table

General Syntax:

SELECT * FROM table_name;

Applied to example:

SELECT * FROM EMPLOYEES;

This will obviously just return the entire table.

SELECT TOP¶

When dealing with a large database and do not want to fetch all the rows from the database we use the SELECT TOP statement to just fetch the top n rows of the database.

The syntax looks as follows:

SELECT TOP n column_name1, column_name2 FROM table-name;

where n is the number of rows you want to retrieve.

In case we do not wish to return an exact number of rows but rather a specific percentage of the table, we can use n PERCENT instead. This will look as follows:

SELECT TOP n PERCENT column_name1, column_name2 FROM table_name;

SELECT DISTINCT¶

The SELECT DISTINCT Statement is used to fetch unique records from a table. It only returns distinct values in the result.

The general syntax looks as follows:

SELECT DISTINCT column FROM table

Let's apply this keyword the the example of a table STUDENT with the following records:

ROLL_NO	NAME	AGE	BRANCH	CITY
10001	Kate	22	CSE	Mumbai
10002	Richard	21	ECE	Delhi
10003	Rick	33	ME	Chennai
10004	Peter	26	CSE	Delhi
10005	Peter	22	CSE	Noida
10006	Mark	22	ECE	Jaipur
10007	Brad	23	ME	Rampur

Selecting one column

Let's try to select the unique “Branches” from the STUDENT table.

SELECT DISTINCT BRANCH FROM STUDENT;

Result:

BRANCH
CSE
ECE
ME

Selecting several columns

In the above example, we have retrieved the data of only one column from a table. Lets see what happens when we use the SELECT DISTINCT statement with multiple columns.

Consider this ORDER table:

CUSTOMER_NAME	BILL_AMOUNT	ORD_NUM
Rick	2000	1901
Rick	2000	1902
Rick	3000	1903
Rick	3000	1904
Rick	4500	1905
Steve	2000	1906

Alrighty, let's select all distinct customer names AND Bill amounts with the following statement

SELECT DISTINCT CUSTOMER_NAME, BILL_AMOUNT FROM ORDER;

Output: When we select multiple columns using SELECT DISTINCT statement, then the data of those columns combined is treated as a distinct value. As you can see in this example that Rick 2000 rows and Rick 3000 rows were present multiple times in the ORDER table so when we DISTINCT select these rows based on these columns, we got these rows only once in the output.

CUSTOMER_NAME	BILL_AMOUNT
Rick	2000
Rick	3000
Rick	4500
Steve	2000

SELECT Aggregate Functions (Count, Min, Max, AVG, SUM)¶

Aggregate Functions allow us to directly perform an operation on our selected values. the general syntax looks as follows

SELECT COUNT

The count() function returns the number of rows that matches the given condition.

The general syntax looks as follows:

SELECT COUNT(column_name) FROM table_name WHERE condition;

Counting specific columns with count(column_name)

Let's again consider our STUDENT table from above:

ROLL_NO	NAME	AGE	BRANCH	CITY
10001	Kate	22	CSE	Mumbai
10002	Richard	21	ECE	Delhi
10003	Rick	39	ME	Chennai
10004	Peter	26	CSE	Delhi
10005	Peter	22	CSE	Noida
10006	Mark	22	ECE	Jaipur
10007	Brad	23	ME	null

Using the following statement:

SELECT COUNT(CITY) FROM STUDENT;

we get 6

While the total number of CITY values in above table are 7, one of them is null. This is because, without any condition, count(column_name) only counts non-null values of the given column.

Counting all columns with count(*)

SELECT COUNT(*) counts the number of rows in the table. Consider the same STUDENT table that we have seen in the above example.

Query:

SELECT COUNT(*) FROM STUDENT;

Output: 7

SELECT MIN & SELECT MAX

The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.

So SELECT MIN(AGE) FROM STUDENT;would return 21 whereas MAX(AGE)would return 39

SELECT AVG & SELECT SUM

The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.

So SELECT SUM(AGE) FROM STUDENT;would return 175 whereas AVG(AGE)would return 25

ORDER BY Clause¶

The SELECT Statement returns the rows in no particular order. With the help of the ORDER BY clause, we can order the rows in a desired order.

The Syntax looks as follows:

SELECT 
  column_name1, 
  column_name2, .... 
FROM 
  table_name
WHERE 
  Condition 
ORDER BY 
  column_name1, column_name2, .... (ASC OR DESC);

Here ASC is used for sorting in ascending order and DESC for descending order.

ORDER BY with one input

Lets consider our STUDENT table once again:

ROLL_NO	NAME	AGE	BRANCH	CITY
10001	Kate	22	CSE	Mumbai
10002	Richard	21	ECE	Delhi
10003	Rick	33	ME	Chennai
10004	Peter	26	CSE	Delhi
10005	Peter	22	CSE	Noida
10006	Mark	22	ECE	Jaipur
10007	Brad	23	ME	Rampur

The following SQL statement will fetch all the records from the table “STUDENT” and the returned rows will be in ascending order of student age.

SELECT * 
FROM 
  STUDENT
ORDER BY 
  AGE ASC;

Note: The default order of sorting is ascending so the rows would have been sorted in an ascending order, even if we had not used the ASC keyword.

Output:

ROLL_NO	NAME	AGE	BRANCH	CITY
10002	Richard	21	ECE	Delhi
10001	Kate	22	CSE	Mumbai
10005	Peter	22	CSE	Noida
10006	Mark	22	ECE	Jaipur
10007	Brad	23	ME	Rampur
10004	Peter	26	CSE	Delhi
10003	Rick	33	ME	Chennai

The following SQL statement will fetch the student names from the table “STUDENT” and the returned names will be sorted in descending order.

SELECT NAME 
   FROM STUDENT
ORDER BY 
  NAME DESC;

Result:

NAME
Rick
Richard
Peter
Peter
Mark
Kate
Brad

ORDER BY with Multiple Columns

In the above examples, we have done the sorting based on a single column. Obviously, we can also use multiple columns in an ORDER BY clause.

In case you want to sort the result set by multiple columns, you use a comma (,) to separate two columns. The ORDER BY clause sorts rows using columns or expressions from left to right. In other words, the ORDER BY clause sorts the rows using the first column in the list. Then, it sorts the sorted rows using the second column, and so on.

Let's sort the STUDENT table based on the city first and then by age within each city:

SELECT * 
   FROM STUDENT
ORDER BY 
  CITY ASC, 
  AGE ASC;

ROLL_NO	NAME	AGE	BRANCH	CITY
10003	Rick	33	ME	Chennai
10002	Richard	21	ECE	Delhi
10004	Peter	26	CSE	Delhi
10006	Mark	22	ECE	Jaipur
10001	Kate	22	CSE	Mumbai
10005	Peter	22	CSE	Noida
10007	Brad	23	ME	Rampur

Sorting Nulls

Sorting NULLs A NULL value denotes that the information is missing or the data is not applicable.

NULL is special because you cannot compare it with another value. Simply put, if two pieces of information are unknown, you cannot compare them.

When it comes to sorting, SQLite considers NULL to be smaller than any other value.

It means that NULLs will appear at the beginning of the result set if you use ASC or at the end of the result set when you use DESC.

SQLite 3.30.0 added the NULLS FIRST and NULLS LAST options to the ORDER BY clause. The NULLS FIRST option specifies that the NULLs will appear at the beginning of the result set while the NULLS LAST option place NULLs at the end of the result set.

< Previous Page | Home Page | Next Page >