- 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
- Where: it specifies a condition to which rows to retrieve
- Group By: used to group similar items based on conditions
- Having: select the rows among the group
- Order by: it will specify the order
- As: it is used for an alias
Select column1, column2,…. From table_name;
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.
Select distinct column_name from table_name;
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.
Select distinct column1, column2 from table_name;
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;
SELECT * FROM [dbo].[Person] where email='[email protected]'
The Operators used in Where clause: –
|>=||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'
- 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.
- 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.
- If you specify outer join then the rest or remaining records from Vtable2 are inserted into Vtable3.
- After this where clause is applied and the lastname=’Jaybhay’ and firstname=’sagar’ are verified or taken and put it into Vtable4.
- 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.
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?
- When you used where clause it will filter the rows based on conditions and after this group by applied.
- If you use having then the first group is created and aggregation is done before any filter and after this, having condition is applied.
- Where clause can be used with select, insert, update and delete statement where having is used only with a select statement.
- Where filters row before aggregation(grouping) where having clause filters a group and after the aggregation is performed.
- 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.