Select:-Things to know about select statement in SQL 2019

---

• Identity Column & Cascading referential Integrity
• Constraints In SQL By Sagar Jaybhay
• About Tables In DataBase By Sagar Jaybhay

---

SELECT Statement in SQL

The select clause can retrieve 0 or more rows from one or more tables from the database or it can retrieve rows from views also.

The select statement is used to select data from a table or database. The data which is our of our select query is called a result set. The select is a commonly used statement in SQL. Means for fetching the data from the database we use Select statement.

We can retrieve all rows or a selected row means this is according to condition. If you want to specify a selected column or we want specific columns you can specify in a select clause.

Select statement optional clauses

1. Where: it specifies a condition to which rows to retrieve
2. Group By: used to group similar items based on conditions
3. Having: select the rows among the group
4. Order by: it will specify the order
5. As: it is used for an alias

Select Syntax:

Select column1, column2,…. From table_name;

Ex

Select * from Person;

The above query will fetch all the data from the table along with all columns.

Distinct Clause in Select Statement

To select a distinct value from column value you can use distinct.

General Syntax

Select distinct column_name from table_name;

Ex

SELECT distinct [genederID] FROM [dbo].[Person]

When you specify multiple columns in a distinct keyword you tell SQL server to check and get distinct value from that number of column you provides.

General Syntax:

Select distinct column1, column2 from table_name;

Ex

SELECT distinct [genederID],[email] FROM [dbo].[Person];

How to Filter Values in Select statement?

You can filter the value by using where clause.

Where Clause in Select Statement

The where clause is used to filter records. By using where clause you can extract only those records which fulfilled our condition.

The where clause not only used in a select statement but also used in Update, Delete also.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Ex

SELECT * FROM [dbo].[Person] where email='[email protected]'

The Operators used in Where clause: –

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

Select Query Evaluation :

select g.*
from users u inner join groups g on g.Userid = u.Userid
where u.LastName = 'Jaybhay'
and u.FirstName = 'Sagar'

1. In the above query from clause is evaluated after that cross join or cartesian join is produced for these 2 tables and this from clause produced a virtual table might call as Vtable1.
2. After this on clause is evaluated for Vtable1 and it checks to join condition g. Userid =u.userid, then the records which met these conditions or full fill these conditions are inserted into another Vtable2.
3. If you specify outer join then the rest or remaining records from Vtable2 are inserted into Vtable3.
4. After this where clause is applied and the lastname=’Jaybhay’  and firstname=’sagar’ are verified or taken and put it into Vtable4.
5. After this select list is evaluated and return Vtable4.

Group by clause in Select Statement

It is an SQL command which is used to group rows that have the same values. It is used only in the select statement.

A group by clause is used to group a selected set of rows into a set of summary rows by using a value of one pr more columns expression. Mostly group by clause is used in conjunction with one or more aggregate functions, Like Count, Max, Min, Sum, Avg.

The main purpose of a group by clause is to arrange identical or similar data into groups it means rows have different value but one column is identical.

Create table person syntax
USE [temp]
GO

/****** Object:  Table [dbo].[Person]    Script Date: 11/20/2019 12:12:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Person](
	[ID] [int] NOT NULL,
	[name] [varchar](100) NULL,
	[email] [varchar](100) NULL,
	[genederID] [int] NULL,
	[age] [int] NULL,
	[salary] [int] NULL,
	[city] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Person] ADD  CONSTRAINT [df_value]  DEFAULT ((3)) FOR [genederID]
GO

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [tbl_person_geneder_id_fk] FOREIGN KEY([genederID])
REFERENCES [dbo].[tbGeneder] ([genederID])
GO

ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [tbl_person_geneder_id_fk]
GO

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [chk_age] CHECK  (([age]>(0) AND [age]<(150)))
GO

ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [chk_age]
GO

Demo data generated site :- https://mockaroo.com/

Group by query

select age,count(age) 'no of person',avg(salary) 'avg salary' from Person group by age

The above query has only one column in a group by clause

Another query of an aggregate function

select age, sum(salary) from Person

the above query will result following

Msg 8120, Level 16, State 1, Line 25 Column ‘Person.age’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

It clearly tells that if we want to age and sum of salary then we need to use group by function.

Group by multiple columns

select age,city,count(age) 'no of person',avg(salary) 'avg salary' from Person group by age,city

in this query, we use multiple columns in the group by clause age and city.

Filtering Groups:

We can use where clause to filter rows before aggregation and Having clause is used to filter groups after aggregations.

select age, sum(salary) as 'Total salary' from person where age  between 30 and 50 group by age;

 select age, sum(salary) as 'Total salary' from Person group by age having age between 30 and 50;

these are 2 queries which produce the same result but in one query we use where clause and in the second query we use having clause both having the same functionality but having is used only with the group by clause and where is used with any clause.

What is the difference between where and having clause?

1. When you used where clause it will filter the rows based on conditions and after this group by applied.
2. If you use having then the first group is created and aggregation is done before any filter and after this, having condition is applied.
3. Where clause can be used with select, insert, update and delete statement where having is used only with a select statement.
4. Where filters row before aggregation(grouping) where having clause filters a group and after the aggregation is performed.
5. Aggregate functions can not be used in the where clause. But if it is in the subquery you can use this. But in having a clause you can use an aggregate function.

select age, email, sum(salary) as 'Total salary' from Person group by age having age between 30 and 50;

In the above query, we use email in the select clause but the query will result in an error why because when you use a group by clause and want to select element the column need to present in group by filter.

Msg 8120, Level 16, State 1, Line 30

Column ‘Person.email’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This error we got.