Day-15) Where,Order by and Having clauses in sql server with interview questions in hindi
| SQL training| SQL Tutorial for beginners| Intellipaat
Where Clause in SQL Server
What are clauses and their need in SQL Server?
If you want to provide the SQL Query with some additional functionalities such as filtering the records, sorting the records, fetching the records, and grouping the records then you need to use the SQL Server Clauses along with the SQL Query. So, in simple words, we can say that SQL Server clauses are used to provide some additional functionalities.
Types of Clauses in SQL Server:
The SQL Server supports the following clauses
1) Where (Filtering the records in a table)
2) Order by clause (sorting the records in ascending or descending order)
3) Top n clause (Fetching top n records)
4) Group by clause (Grouping a set of rows)
5) Having Clause (Filtering the data like where clause)
--Create Gender table
CREATE TABLE Gender
(
ID INT PRIMARY KEY IDENTITY(1,1),
Gender VARCHAR(50)
)
--Create Department table
CREATE TABLE Department
(
ID INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR(100)
)
-- Create Employee table
CREATE TABLE Employee
(
ID INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR(100),
EmailID VARCHAR(100),
GenderID INT,
DepartmentID INT,
Salary INT,
Age INT,
CITY VARCHAR(100)
)
--Add the foreign key for GenderID Column
ALTER TABLE Employee
ADD CONSTRAINT Employee_GenderID_FK FOREIGN KEY (GenderID)
REFERENCES Gender(ID)
--Add foreign key for DepartmentID Column
ALTER TABLE Employee
ADD CONSTRAINT Employee_DepartmentID_FK FOREIGN KEY (DepartmentID)
REFERENCES Department(ID)
--Insert data to Gender table
INSERT INTO Gender VALUES('Male')
INSERT INTO Gender VALUES('Female')
INSERT INTO Gender VALUES('Unknown')
--Insert data to Department table
INSERT INTO Department VALUES('IT')
INSERT INTO Department VALUES('HR')
INSERT INTO Department VALUES('Payroll')
GO
--Insert data into Employee table
INSERT INTO Employee VALUES('PRANAYA','[email protected]',1, 1, 25000, 30,'MUMBAI')
INSERT INTO Employee VALUES('TARUN','[email protected]',1, 2, 30000, 27,'ODISHA')
INSERT INTO Employee VALUES('PRIYANKA','[email protected]',2, 3, 27000, 25,'BANGALORE')
INSERT INTO Employee VALUES('PREETY','[email protected]',2, 3, 35000, 26,'BANGALORE')
INSERT INTO Employee VALUES('RAMESH','[email protected]',3,2, 26000, 27,'MUMBAI')
INSERT INTO Employee VALUES('PRAMOD','[email protected]',1, 1, 29000, 28,'ODISHA')
INSERT INTO Employee VALUES('ANURAG','[email protected]',1, 3, 27000, 26,'ODISHA')
INSERT INTO Employee VALUES('HINA','[email protected]',2,2, 26000, 30,'MUMBAI')
INSERT INTO Employee VALUES('SAMBIT','[email protected]',1, 1, 30000, 25,'ODISHA’)
Example of Where clause with a Single condition
SELECT * FROM Employee WHERE CITY = ‘MUMBAI’;
SELECT * FROM Employee WHERE GenderID = 1 AND Salary )= 27000;
Order By Clause in SQL Server
What is Order By Clause and its need in SQL Server?
The Order By Clause in SQL Server is used for sorting the data either in ascending or descending order of a query based on a specified column or list of columns.
Syntax:
SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ASC | DESC];
By default, the Order By Clause in SQL Server will sort the data in ascending order.
Examples:
SELECT * FROM Employee ORDER BY Name;
SELECT * FROM Employee WHERE Gender = ‘Male’ ORDER BY Name DESC;
SELECT Name, EmailID, Salary FROM Employee WHERE Salary ) 26000 ORDER BY Name DESC;
Group By Clause in SQL Server
Group By Clause in SQL Server
The Group by Clause in SQL Server is used to divide similar types of records or data as a group and then return. If we use group by clause in the query then we should use grouping/aggregate function such as count(), sum(), max(), min(), and avg() functions.
When we implement group by clause first the data of the table will be divided into the separate group as per the column and later aggregate function will execute on each group data to get the result. That means first Group By clause is used to divide similar types of data as a group and then an aggregate function is applied to each group to get the required results.
Syntax of Group By Clause in SQL Server
SELECT expression1, expression2, expression_n, aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, expression_n;
WAQ to find total employees in the organization.
SELECT COUNT(*) AS TotalEmployee FROM Employee
WAQ to find the number of employees working in each department in the company.
Here, we need to group the employees by department, and then we need to apply the count function to each group. The following SQL query exactly does the same.
SELECT Department, COUNT(*) AS TotalEmployee
FROM Employee
GROUP BY Department
WAQ to find the total salary in each department of the organization.
SELECT Department, TotalSalary = SUM(Salary)
FROM Employee
GROUP BY Department
WAQ to find the highest salary in each department in the organization.
SELECT Department, MaxSalary = MAX(SALARY)
FROM Employee
GROUP BY Department