SQL Select statement

SQL Select statement:

Sql select statement is used to select and display data to users there are different conditions on which select statement is executed. Here we will see some examples.

We have table doctor on which we will perform operations

doctor_id	name	fee	timings
D101	John	10000	8-4
D102	Peter	20000	4-8
D103	Nash	30000	12-8
D104	Andrew	25000	6-12
D105	Smith	15000	6-12

1.To select all rows and all columns from a table.

select * from doctor

Whole table data is selected. When you use * it means you are selecting all the columns.

doctor_id	name	fee	timings
D101	John	10000	8-4
D102	Peter	20000	4-8
D103	Nash	30000	12-8
D104	Andrew	25000	6-12
D105	Smith	15000	6-12

2.To select only columns we want we will specify columns name.

Select doctor_id,name,fee from doctor

Only three columns are selected

doctor_id	name	fee
D101	John	10000
D102	Peter	20000
D103	Nash	30000
D104	Andrew	25000
D105	Smith	15000

3.To change column name

Select doctor_id as doc_id ,name as doc_name,fee as doc_fee from doctor

doc_id	doc_name	doc_fee
D101	John	10000
D102	Peter	20000
D103	Nash	30000
D104	Andrew	25000
D105	Smith	15000

4.To concatenate two columns together.

For concatenating two columns ‘+’operator is used

select doctor_id+' '+name as Doctor_IDandName from doctor

Doctor_id and name colimnn values are conactenated with a space.

Doctor_IDandName

D101 John

D102 Peter

D103 Nash

D104 Andrew

D105 Smith

5.For selecting records on the basis of starting word of a column.

When you use where clause it means you are selecting some rows based on condition after where clause.

select * from doctor where name like 'a%'

Only those records will be selected in which name starts with letter A.

doctor_id	name	fee	timings
D104	Andrew	25000	6-12

6.For selecting the records on the basis of ending word of a column.

select * from doctor where name like '%h'

Only those records will be selected in which name ends with letter H.

doctor_id	name	fee	timings
D103	Nash	30000	12-8
D105	Smith	15000	6-12

7.Order By Clause

Order by clause selects records on basis of a column either asecending or descending

select * from doctor order by doctor_id desc 

This will select records on basis of doctor_id in descending order

doctor_id	name	fee	timings
D105	Smith	15000	6-12
D104	Andrew	25000	6-12
D103	Nash	30000	12-8
D102	Peter	20000	4-8
D101	John	10000	8-4

8.By default records are selected in asecending order.

select * from doctor order by doctor_id

This will select records in asecending order 

doctor_id	name	fee	timings
D101	John	10000	8-4
D102	Peter	20000	4-8
D103	Nash	30000	12-8
D104	Andrew	25000	6-12
D105	Smith	15000	6-12

9.To select different values for a column

select distinct timings from doctor

This will select different timings from table for name column repeating                                                      values for timings column will be ignored

timings

12-8

4-8

6-12

8-4

10.To select name by in statement.

select * from doctor where name in('nash','john')

This will select name where name will equal to nash or smith

doctor_id	name	fee	timings
D101	John	10000	8-4
D103	Nash	30000	12-8

11.To group rows using group by clause

select timings,count(timings) as Total_Doctors from

doctor group by timings

This will group rows on the basis of timings where two or more rows have same value will be grouped together and will show total rows against them.Here this is showing that how many doctors are available at a specific time.

timings	Total_Doctors
12-8	1
4-8	1
6-12	2
8-4	1