BIT_XOR() function in MySQL is used to return the bitwise XOR of all bits in a given expression. It first converts all decimal values into binary values, and then perform bitwise xor operation on those binary values.
Syntax :
BIT_XOR(expr)
Parameter : This method accepts only one parameter.
- expr – Input expression on which we want to apply BIT_XOR function.
Returns : It returns bitwise XOR of all bits in a given expression.
Example-1 :
To demonstrate the working of BIT_XOR function we have to first create a table named EmployeeDetails.
CREATE TABLE EmployeeDetails(
Employee_Id INT AUTO_INCREMENT,
Employee_Name VARCHAR(100) NOT NULL,
Working_At VARCHAR(20) NOT NULL,
Work_Location VARCHAR(20) NOT NULL,
Joining_Date DATE NOT NULL,
Annual_Income INT NOT NULL,
PRIMARY KEY(Employee_Id)
);
Now, inserting the values into the table –
INSERT INTO EmployeeDetails (Employee_Name,
Working_At, Work_Location, Joining_Date, Annual_Income)
VALUES
('Amit Khan', 'XYZ Digital', 'Kolkata', '2019-10-06', 350000),
('Shreetama Pal', 'ABC Corp.', 'Kolkata', '2018-12-16', 500000),
('Aniket Sharma', 'PQR Soln.', 'Delhi', '2020-01-11', 300000),
('Maitree Jana', 'XYZ Digital', 'Kolkata', '2019-05-01', 400000),
('Priyanka Ojha', 'ABC Corp.', 'Delhi', '2019-02-13', 350000),
('Sayani Mitra', 'XYZ Digital', 'Kolkata', '2019-09-15', 320000),
('Nitin Dey', 'PQR Soln.', 'Delhi', '2019-10-06', 250000),
('Sujata Samanta', 'PQR Soln.', 'Kolkata', '2020-10-06', 350000),
('Sudip Majhi', 'ABC Corp.', 'Delhi', '2018-10-30', 600000),
('Sanjoy Kohli', 'XYZ Digital', 'Delhi', '2019-04-18', 450000);
To verify used the following command as follows –
Select * from EmployeeDetails;
Output :
EMPLOYEE_ID |
EMPLOYEE_NAME |
WORKING_AT |
WORK_LOCATION |
JOINING_DATE |
ANNUAL_INCOME |
1 |
Amit Khan |
XYZ Digital |
Kolkata |
2019-10-06 |
350000 |
2 |
Shreetama Pal |
ABC Corp. |
Kolkata |
2018-12-16 |
500000 |
3 |
Aniket Sharma |
PQR Soln. |
Delhi |
2020-01-11 |
300000 |
4 |
Maitree Jana |
XYZ Digital |
Kolkata |
2019-05-01 |
400000 |
5 |
Priyanka Ojha |
ABC Corp. |
Delhi |
2019-02-13 |
350000 |
6 |
Sayani Mitra |
XYZ Digital |
Kolkata |
2019-09-15 |
320000 |
7 |
Nitin Dey |
PQR Soln. |
Delhi |
2019-10-06 |
250000 |
8 |
Sujata Samanta |
PQR Soln. |
Kolkata |
2020-10-06 |
350000 |
9 |
Sudip Majhi |
ABC Corp. |
Delhi |
2018-10-30 |
600000 |
10 |
Sanjoy Kohli |
XYZ Digital |
Delhi |
2019-04-18 |
450000 |
Now we will find the BIT_XOR of annual income of all the employee company wise from EmployeeDetails table.
SELECT Working_At, BIT_XOR(Annual_Income) AS BITXORINCOME
FROM EmployeeDetails
Group By Working_At;
Output :
WORKING_AT |
BITXORINCOME |
XYZ Digital |
94816 |
ABC Corp. |
774608 |
PQR Soln. |
136256 |
Example-2 :
Now we will find the BIT_XOR of annual income of all the employee location wise from EmployeeDetails table.
SELECT Work_Location, BIT_XOR(Annual_Income) AS BITORINCOME
FROM EmployeeDetails
Group By Work_Location;
Output :
WORK_LOCATION |
BITXORINCOME |
Kolkata |
350624 |
Delhi |
912976 |
Example-3 :
Firstly creating a StudentMarks table –
CREATE TABLE StudentMarks (
StudentId INT AUTO_INCREMENT,
StudentName VARCHAR(100) NOT NULL,
Class VARCHAR(20) NOT NULL,
Roll INT NOT NULL,
Sub1Mark INT NOT NULL,
Sub2Mark INT NOT NULL,
Sub3Mark INT NOT NULL,
TotalMarks INT NOT NULL,
PRIMARY KEY(StudentId)
);
Inserting data into the table –
INSERT INTO StudentMarks
(StudentName, Class, Roll, Sub1Mark, Sub2Mark, Sub3Mark, TotalMarks)
VALUES
('Amit Jana', 'V', 10100, 85, 80, 95, 260),
('Labanya Mallick', 'VI', 11000, 81, 89, 95, 265),
('Virat Sharma', 'VI', 12000, 75, 83, 90, 248),
('Sayani Samanta', 'V', 13000, 95, 90, 99, 284),
('Riyanka Panda', 'V', 14000, 70, 87, 88, 245),
('Ritika Shah', 'VI', 15000, 78, 89, 90, 257);
To verify used the following command as follows –
SELECT * FROM StudentMarks;
Output :
STUDENTID |
STUDENTNAME |
CLASS |
ROLL |
SUB1MARK |
SUB2MARK |
SUB3MARK |
TOTALMARKS |
1 |
Amit Jana |
V |
10100 |
85 |
80 |
95 |
260 |
2 |
Labanya Mallick |
VI |
11000 |
81 |
89 |
95 |
265 |
3 |
Virat Sharma |
VI |
12000 |
75 |
83 |
90 |
248 |
4 |
Sayani Samanta |
V |
13000 |
95 |
90 |
99 |
284 |
5 |
Riyanka Panda |
V |
14000 |
70 |
87 |
88 |
245 |
6 |
Ritika Shah |
VI |
15000 |
78 |
89 |
90 |
257 |
Now we will find the BIT_XOR of total marks of all the student class wise from StudentMarks table.
SELECT Class, BIT_XOR(TotalMarks) AS BITXORMARKS
FROM StudentMarks
Group By Class;
Output :
CLASS |
BITXORMARKS |
V |
237 |
VI |
240 |
Share your thoughts in the comments
Please Login to comment...