SQL SELECT
SELECT statement is most commonly used SQL command.SQL SELECT statement is used to query or retrieve data from a table in the database.
Let’s see the syntax of select statement.
SELECT expressions FROM tables WHERE conditions; Here expression is the one or more columns from which data is retrieved.
Tables is the name of the table from which the information is retrieved.
You can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement.
[WHERE Clause] : It specifies which rows to retrieve.
[GROUP BY Clause] : Groups rows that share a property so that the aggregate function can be applied to each group.
[HAVING Clause] : It selects among the groups defined by the GROUP BY clause.
[ORDER BY Clause] : It specifies an order in which to return the rows.
| ID | First_name | Last_name | Age | Subject |
| 1 | Ankush | Mehta | 19 | Commerce |
| 2 | Nilesh | Das | 21 | Science |
| 3 | Kushal | Kumar | 20 | Maths |
For example, let a database table: student_details;
From the above example, select the first name of all the students. To do so, query should be like this:
SELECT first_name FROM student_details; Note: the SQL commands are not case sensitive.We can also write the above SELECT statement as:
select first_name from student_details; You will get the Output:
| Ankush |
| Nilesh |
| Kushal |
We can also get data from more than one column.
For example,
SELECT first_name, last_name FROM student_details; You will get the Output:
| Ankush | Mehta |
| Nilesh | Das |
| Kushal | Kumar |
We can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement.
Here a point is notable that only SELECT and FROM statements are necessary in SQL SELECT statements. Other clauses like WHERE, GROUP BY, ORDER BY, HAVING may be optional.
SQL SELECT DISTINCT
To retrieve only distinct or unique data the SQL DISTINCT command is used with SELECT key word.
In a table, there may be a chance to exist a duplicate value and sometimes we want to retrieve only unique values. In such case, SQL SELECT DISTINCT statement is used.
Note: SQL SELECT UNIQUE and SQL SELECT DISTINCT statements are same.
Syntax of select distinct statement.
SELECT DISTINCT column_name ,column_name
ROM table_name; Let’s try to understand it by the table given below:
| Student_Name | Gender | Mobile_Number | HOME_TOWN |
| Ramesh Patel | Male | 7503896532 | Lucknow |
| Dinkar Mishra | Female | 9270568893 | Varanasi |
| Soham SHARMA | Male | 9990449935 | Lucknow |
Here is a table of students from where we want to retrieve distinct information For example: distinct home-town.
SELECT DISTINCT home_town
FROM students ;Now, it will return two rows.
| HOME_TOWN |
| Lucknow |
| Varanasi |
SQL SELECT COUNT
If you want to return the number of rows in a query the SQL COUNT() function is used.
The COUNT() function is very useful to count the number of rows in a table having large data.
For example: If you have a record of the voters in selected area and want to count the number of voters then it is very difficult to do it manually but you can do it easily by using the SQL SELECT COUNT query.
Let’s see the syntax of SQL COUNT statement.
SELECT COUNT (expression)
FROM tables
WHERE conditions; Let’s see the examples of sql select count function.
SQL SELECT COUNT(column_name)
SELECT COUNT(name) FROM employee_table;It will return the total number of names of employee_table. But null fields will not be counted.
SQL SELECT COUNT(*)
SELECT COUNT(*) FROM employee_table; The “select count(*) from table” is used to return the number of records in table.
SQL SELECT COUNT(DISTINCT column_name)
SELECT COUNT(DISTINCT name) FROM employee_table; It will return the total distinct names of employee_table.
SQL SELECT RANDOM
The SQL SELECT RANDOM() function returns the random row.
For Example: It can be used in online exam to display the random questions.
There are a lot of ways to select a random record or row from a database table. Each database server needs different SQL syntax.
If you want to select a random row with MY SQL:
SELECT column FROM table
ORDER BY RAND ( )
LIMIT 1 With Microsoft SQL server:
SELECT TOP 1 column FROM table
ORDER BY NEW ID() With ORACLE:
SELECT column FROM
(SELECT column FROM table
ORDER BY dbms_random.value)
WHERE rownum =1 SQL SELECT AS
SQL AS is used to assign a new name to a table column temporarily.
The example of select as:
Let us take a table named orders, it contains:2
| Day_of_order | Customer | Product | Quantity |
| 1-05-2014 | Ajinkya | Mobile | 20 |
| 13-08-2014 | Mayur | Laptop | 2 |
| 6-12-2015 | Balakrishana | Water cannon | 32 |
After applying this SQL AS example syntax
SELECT day_of_order AS "Date"
Customer As "Client",
Product,
Quantity,
FROM orders; Output will be shown as this table:
| Date | Client | Product | Quantity |
| 1-05-2014 | Ajinkya | Mobile | 20 |
| 13-08-2014 | Mayur | Laptop | 2 |
| 6-12-2015 | Balakrishana | Water cannon | 32 |
Note: SQL AS is same as SQL ALIAS.
SQL SELECT IN
The IN command allows you to specify multiple values in a WHERE clause.
SQL IN is an operator used in a SQL query to help reduce the need to use multiple SQL “OR” conditions.
It is used in SELECT, INSERT, UPDATE or DELETE statement and it minimizes the use of SQL OR operator.
Syntax for SQL IN:
Expression IN (value 1, value 2 ... value n); Take an example with character values.
SELECT *
FROM students
WHERE students_name IN ( Amrut , Rajeev, Ram) SQL SELECT DATE
If you want to find a particular date from a database, you can use SQL SELECT DATE to retrieve a date from a database.
For example: let’s see the query to get all the records after ‘2012-11-10’.
SELECT * FROM
table-name WHERE your date-column >= '2012-11-10' Let’s see the another query to get all the records after ‘2012-11-10’ and before ‘2013-12-13’ date.
SELECT* FROM
table-name where your date-column < '2013-12-13' and your date-
column >= '2012-11-10' If you want to compare the dates within the query, you should use BETWEEN operator to compare the dates.
SELECT * FROM
table_name WHERE yourdate BETWEEN '2011-11-11' and '2012-11-10' Or if you are looking for one date in particular you can use. You should change the date parameter into the acceptable form.
SELECT* FROM
table_name WHERE cast(datediff(day, 0, yourdate) as datetime)='2011-11-11' SQL SELECT NULL
SQL NULL value represents a blank value in a table. NULL value is used for identifying any missing entries in a table.
There can be two conditions:
- Where SQL is NULL
- Where SQL is NOT NULL
Note: we should not compare null value with 0. They are not equivalent.
Where SQL is NULL:
How to select records with null values only? There is an example of student table:
| SIR_NAME | NAME | MARKS |
| SHARMA | AMAR | |
| TYAGI | SEEMA | 5.6 |
| SINGH | RAMAN | |
| SHARMA | AMAR | 6.5 |
Let’s see the query to get all the records where marks is NULL:
SELECT SIR_NAME, NAME, MARKS FROM STUDENTS
WHERE MARKS IS NULL It will return the following records:
| SIR_NAME | NAME | MARKS |
| SINGH | RAMAN | |
| SHARMA | AMAR |
Where SQL is NOT NULL:
How to select records with no null values(in marks column)? Let’s see the query to get all the records where marks is NOT NULL
SELECT SIR_NAME, FIRSTNAME, MARKS FROM STUDENTS
WHERE MARKS IS NOT NULL | SIR_NAME | NAME | MARKS |
| TYAGI | SEEMA | 5.6 |
| SHARMA | AMAR | 6.5 |