SQL: Select Statement
Today we’ll be learning about the SELECT statement in SQL.
Query the entire table
Using the ‘Select *‘ statement in the query will return the entire table (all the columns and all the rows). The query is as follows:
Select * from table_name;
Query some specific Columns
Suppose you want to query some of the columns not all. For that the query will be as follows and the result can be seen in the image.
Select Column1, column2,..... from table_name;
Above query will return the table with column1,column3 and column4 values.
Query based on specific values (WHERE clause)
Suppose you want to query with some specific column values. For this we’ll using WHERE clause and the query is as follows:
Select * from table_name where column_name='value'
OR
Select column1,coumn2.... from table_name where column_name='value'
Above query will return the table having column values equal to value or 5 as shown in the image.
Using OR Operator
Now we’ll learn to use OR operator in SQL.It will be used with WHERE clause so that we can filter more values. The query is as follows:
Select column1, column2,.... from table_name where column1= 'value1' OR column2='value1' OR column3='value1';
we can add as many or condition as we want.
AND Operator
AND Operator is same as OR operator and used for filtering values according to our need.
Select column1, column2,..from table_name where column1= 'value1' AND column2='value1' AND column3='value1';
Query Unique values
Now we’ll learn to remove the duplicates while querying the values with the use of DISTINCT statement.
Select distinct * from table_name;
Above query will return the unique values only.
Counting the number of rows based on the condition
To count the number of rows for the condition using COUNT() function.
Select count(*) from table_name;
Count function can be used with other condition using where,and,or operator.
Sorting the Table values
Now we’ll to use ORDER BY keyword in SQL.
Select * from table_name Order By column1 desc; for descending order
Select * from table_name Order By column1 asc; for ascending order
select * from table_name order by column1 asc,column2 desc; combining both the orders
Above query will the table values the ascending or descending order of column1 values.
BETWEEN Operator
BETWEEN operator is used to avoid more number of AND or OR operator. The query goes like this:
select * from new_table where column1 between 'value1' and 'value2' OR column2 between 'value1' and 'value2'
The above query will return the table values having column1 values between value1 and value2 Or having column2 values between value1 and value2
Query every Nth row
With the following trick we can query every Nth row.
Select * from table_name where column1%N=0
Select * from table_name where column1%2=0; For every 2nd row
Above query will work if we have any column like column1.
LIMIT and OFFSET
Using LIMIT we can limit the number of records and OFFSET helps to mention the starting point. In the image below the offset value is 3 so the result will start from the 4th row. The query for the LIMIT and OFFSET is as follows.
Select * from table_name limit VALUE;
Select * from table_name limit VALUE, OFFSET VALUE;
SELECT * from table_name limit VALUE,VALUE1; (VALUE1 is OFFSET value)
GROUP BY
Sometimes ‘Group by’ is used to group the aggregated function. As we can see in the image below, here we are counting the column2 records and grouping it by them.
Current Date and time
To see the current date and time, following query will be used.
select now();
So this tutorial will give the broader idea about SELECT Statement in SQL.