Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, etc. In this article, we will be using the Microsoft SQL Server.
Here we are going to see how to get the highest salary of each department. Here, we will first create a database named “geeks” then we will create a table “department” in that database. After, that we will execute our query on that table.
To use this database:
This is our table in the geeks database:
CREATE TABLE department( ID int, SALARY int, NAME Varchar(20), DEPT_ID Varchar(255));
To see the description of the table:
EXEC sp_columns department;
Add value into the table:
INSERT INTO department VALUES (1, 34000, 'ANURAG', 'UI DEVELOPERS'); INSERT INTO department VALUES (2, 33000, 'harsh', 'BACKEND DEVELOPERS'); INSERT INTO department VALUES (3, 36000, 'SUMIT', 'BACKEND DEVELOPERS'); INSERT INTO department VALUES (4, 36000, 'RUHI', 'UI DEVELOPERS'); INSERT INTO department VALUES (5, 37000, 'KAE', 'UI DEVELOPERS');
This is our data inside the table:
SELECT * FROM department;
Get the highest salary of each department on the table. Here our table contains a DEPT_ID and it has two different categories UI DEVELOPERS and BACKEND DEVELOPERS, and we will find out the highest salary of the column.
SELECT colunm_name, MAX(column_name) FROM table_name GROUP BY column_name;
SELECT DEPT_ID, MAX(SALARY) FROM department GROUP BY DEPT_ID;