Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 −
| Name | Regno | Branch | Age |
|---|---|---|---|
| Hari | 100 | CSE | 15 |
| Pinky | 101 | CSE | 17 |
| Bob | 102 | CSE | 14 |
| Bhanu | 103 | CSE | 18 |
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 −
| Name | Regno | Branch | Age |
|---|---|---|---|
| Pinky | 101 | CSE | 17 |
| Bhanu | 103 | CSE | 18 |
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.
| Name | Regno | Branch | Age |
|---|---|---|---|
| Bob | 102 | CSE | 14 |
| Bhanu | 103 | CSE | 18 |
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;
7K+ Views
