Explain Select command in DBMS

Select command is used to fetch the data in a set of records from a table, view or a group of tables, views by making use of SQL joins.

Retrieval of data using SQL statements can be done by using different predicates like −

• Where
• Group By
• Having
• Order By

The simplest example of a select statement where in a user wants to, retrieve all the records of a table, can be performed by using '*'.

First let's create and insert the data in table before retrieving the data using select command −

Step 1

create table student(name char(30), regno number(10), branch char(20), age char(10));

The output will be as follows: Table created

Step 2

insert into student values(‘hari’,100,’CSE’, 15);

The output will be as follows: 1 row created

Step 3

insert into student values(‘pinky’,101,’CSE’,17);

The output will be as follows: 1 row created

Step 4

insert into student values(‘bob’,102,’CSE’,14);

The output is given herewith: 1 row created

Step 5

insert into student values(‘bhanu’,103,’CSE’,18);

The output is given herewith: 1 row created

Step 6

Select * from student;

The output is as follows −

It displays all the records from the student table as shown above.

Where clause

Where clause is used with the data manipulation language (DML) statement to check for a condition being met in row.

Example 1

The query given below displays the students’ records whose age is in between 15 and 20.

SELECT * FROM student where age>15 and age<20;
(OR)
SELECT * FROM student where age between 15 and 20;

The output is as follows −

Example 2

Consider another example to know more about where clause −

SELECT *FROM student where name like B%;

The above query retrieves all names starting with character 'B.

Group By Clause

The Group By clause statement in the structured query language (SQL) is used for aggregation that means the returned result is based on a column aggregation.

Example

SELECT regno,sum(marks) FROM student

WHERE class=5

GROUP BY regno

Having clause

The having statement in SQL makes sure that an SQL SELECT statement should only return rows where the aggregate value matches the conditions that are stated.

Example

SELECT regno, sum(marks) from student WHERE admissionDate='01-Mar-2021'

GROUP BY regno

HAVING sum(marks)>600

Order By clause

The order by clause in SQL is used to set the sequence of the output in terms of being alphabetical, magnitude of size, and order of date. It may be accompanied by an 'asc' or 'desc' clause so as to specify whether the results are in ascending or descending order.

Note − If we didn't mention 'ans' or 'desc' default the order by clause takes ascending order.

Example

SELECT firstname, lastname from student ORDER BY firstname ASC;