SQL for Beginners Series - Session 9

GROUP BY | HAVING | DISTINCT in SQL

Please click here for you tube video Answer

--**********************************************************************************

-- Session 9 - GROUP BY / HAVING / DISTINCT data from a table (Microsoft SQL Server)

--**********************************************************************************

DROP TABLE IF EXISTS tblEmployee

CREATE TABLE tblEmployee ( -- Drop table tblEmployee

Empid INT NOT NULL,

EmpName Varchar(20) NULL,

EmpLocation Varchar(20),

DOJ DATE,

Salary INT)

INSERT INTO tblEmployee (Empid,EmpName,EmpLocation,DOJ,Salary) 

Values (1,'Smith','India', '2020-01-02',20000),(1,'Smith','India', '2020-01-02',20000),(1,'Smith','India', '2020-01-02',20000)

INSERT INTO tblEmployee (Empid,EmpName,EmpLocation,DOJ,Salary) 

Values (2,'George Mark','US', '2019-04-28',50000),(2,'George Mark','US', '2019-04-28',50000)

INSERT INTO tblEmployee (Empid,EmpName,EmpLocation,DOJ,Salary) 

Values (3,'Emp3','UK', '2018-04-28',25000),(4,'Emp4','UK', '2016-04-25',60000), (3,'Emp3','UK', '2018-04-28',25000),(4,'Emp4','UK', '2016-04-25',60000)

--> DISTINCT - eliminated the duplicate rows

SELECT * FROM tblEmployee ORDER BY Empid 

SELECT DISTINCT Empid,EmpName,EmpLocation,DOJ,Salary FROM tblEmployee ORDER BY Empid 

By using Distinct we can get the distinct combination of rows based on the Column list specified

--> GROUP BY / HAVING

SELECT Empid,EmpName,EmpLocation,DOJ,Salary, COUNT(*) AS Dups_Cnt  FROM tblEmployee 

GROUP BY Empid,EmpName,EmpLocation,DOJ,Salary

HAVING COUNT(*) > 2  --> Apply after the grouping

ORDER BY Empid 

In the above query it will provide the Unique combination of records and how many times they are repeated.

Having would filter the grouped rows. In the above query it will get the records which are duplicated more than twice.

SELECT * FROM tblEmployee ORDER BY Empid 

SELECT Empid,EmpName,EmpLocation,DOJ,Salary, COUNT(*) AS Dups_Cnt  FROM tblEmployee 

WHERE EmpLocation <> 'US' --> Apply before grouping

GROUP BY Empid,EmpName,EmpLocation,DOJ,Salary

HAVING COUNT(*) > 1  --> Apply after the grouping

ORDER BY Empid 

--> WHERE Clause filters before grouping 

--> HAVING Clause filters after grouping 

-- Logical processing of SELECT Statement 

For this please watch my you tube video to know the order of execution of select statement

SELECT Empid,EmpName,EmpLocation,DOJ,Salary, COUNT(*) AS Dups_Cnt  FROM tblEmployee 

WHERE EmpLocation <> 'US' --> Apply before grouping

GROUP BY Empid,EmpName,EmpLocation,DOJ,Salary

HAVING COUNT(*) > 1  --> Apply after the grouping

ORDER BY Empid 

 

Can we update a view in SQL

   Part - 18

Click here for you tube video answer:

--*************************************

-- Q18 -- Can we update a view in SQL?

--*************************************

Scenario 1: A plain view built on single base table

Yes, we can update a plain view build on top of a single base table    

DROP TABLE IF EXISTS tblEmployee 

create table tblEmployee (Empid INT, EMpname varchar(20), Deptid INT)

INSERT tblEmployee Values (1,'emp1',1),(2,'emp2',2), (3,'emp3',3)

select * from tblEmployee

Create view Employee_View AS

SELECT Empid,EMpname,Deptid FROM  tblEmployee

select * from tblEmployee

select * from Employee_View

select * from tblEmployee

Update Employee_View SET Deptid = 11 WHERE Empid = 1

select * from tblEmployee

Scenario 2: A view built on top of multiple base tables

           Yes, we can update a view built on top of multiple base tables if the Updating column set belongs to a single table 

DROP TABLE IF EXISTS tblEmployee 

create table tblEmployee (Empid INT, EMpname varchar(20), Deptid INT)

DROP TABLE IF EXISTS Dept 

Create table Dept (Deptid INT, DeptName Varchar(20))

INSERT tblEmployee Values (1,'emp1',1),(2,'emp2',2), (3,'emp3',1)

INSERT Dept Values (1,'HR'), (2,'IT')

-- Drop table Dept

SELECT * FROM tblEmployee

SELECT * FROM Dept

DROP VIEW IF EXISTS Employee_Dept;

Create view Employee_Dept AS

SELECT Empid,EMpname,DeptName FROM  tblEmployee E

JOIN Dept D ON E.Deptid = D.Deptid;

SELECT * FROM tblEmployee

SELECT * FROM Dept

SELECT * FROM Employee_Dept

           No, we can not update a view built on top of multiple base tables if the Updating column set belongs to multiple base tables  

UPDATE Employee_Dept SET EMpname = 'Emp11', DeptName = 'Human Resource'

WHERE Empid = 1

The above statement will throw below error:

SELECT * FROM tblEmployee 

UPDATE Employee_Dept SET EMpname = 'Emp11' WHERE Empid = 1

SELECT * FROM tblEmployee 

SELECT * FROM Employee_Dept -- view

SELECT * FROM tblEmployee -- base table

SELECT * FROM Dept

UPDATE Employee_Dept SET DeptName = 'Human Resource' WHERE Empid = 1

SELECT * FROM Dept

The above update statements updates the base tables as the update column list belongs to single base table