Employee Salaries and Departments
A company uses 2 data tables, Employee and Department, to store data about its employees and departments. Write a query to print the respective Employee Name, Salary, Department Name, and Location for each employee in the Employee table. Sort your results by descending Salary; if two or more employees have the same Salary, then sort those employees alphabetically by Name.
The Employee and Department tables are described as follows:
Employee
Field Data Type
ID Integer
NAME String
SALARY Integer
DEPT_ID Integer
where ID is the employee's ID number, NAME is the employee's name, SALARY is the employee's salary in dollars, and DEPT_ID is the ID number for the department where the employee works.
Department
Field Data Type
DEPT_ID Integer
NAME String
LOCATION STRING
where DEPT_ID is the department ID number, NAME is the department name (category), and LOCATION is the office location.
The output must contain 4 columns: "Employee Name", "Salary", "Department Name", and "location".
create table Department(dept_id int primary key not null, name varchar(30),location varchar(20));
insert into Department(dept_id,name,location) values(1,'Recruitment','Bangalore');
insert into Department(dept_id,name,location) values(2,'Sales','U.S.A');
create table Employee (id int, name varchar(20), salary int, dept_id int, foreign key(dept_id) references Department(dept_id));
insert into Employee(id, name, salary,dept_id) values (1, 'Hari', 5000,2);Employee Name Salary Department Name location
Hari 5000 Sales U.S.A
SELECT e.name AS "Employee Name", e.salary AS Salary, d.name AS "Department Name", d.location
FROM Employee e
INNER JOIN Department d ON e.dept_id=d.dept_id
ORDER BY e.salary DESC, e.name ASC;create table Department(dept_id int primary key not null, name varchar(30),location varchar(20));
insert into Department(dept_id,name,location) values(1,'Recruitment','Bangalore');
insert into Department(dept_id,name,location) values(2,'Sales','U.S.A');
insert into Department(dept_id,name,location) values(3,'Engineering','Kolkata');
insert into Department(dept_id,name,location) values(4,'Product','Bangalore');
insert into Department(dept_id,name,location) values(5,'Research and Development','Kolkata');
insert into Department(dept_id,name,location) values(6,'Operations','U.S.A');
insert into Department(dept_id,name,location) values(7,'Finance','U.S.A');
create table Employee (id int, name varchar(20), salary int, dept_id int, foreign key(dept_id) references Department(dept_id));
insert into Employee(id, name, salary,dept_id) values (1, 'Hari', 5000,2);
insert into Employee(id, name, salary,dept_id) values (3, 'Vivek', 6000,1);
insert into Employee(id, name, salary,dept_id) values (4, 'Rakesh', 1000,3);
insert into Employee(id, name, salary,dept_id) values (5, 'Abhimanyu', 600,3);
insert into Employee(id, name, salary,dept_id) values (6, 'Aakansha', 600,4);
insert into Employee(id, name, salary,dept_id) values (7, 'Scott', 1300,1);
insert into Employee(id, name, salary,dept_id) values (8, 'Jessica', 600,2);
insert into Employee(id, name, salary,dept_id) values (9, 'Stephinie', 400,4);
insert into Employee(id, name, salary,dept_id) values (10, 'Mike', 100,3);
insert into Employee(id, name, salary,dept_id) values (11, 'John', 250,3);
insert into Employee(id, name, salary,dept_id) values (12, 'Michael', 1134,3);
insert into Employee(id, name, salary,dept_id) values (13, 'Ronaldo', 1500,1);
insert into Employee(id, name, salary,dept_id) values (2, 'Heraldo', 1500,1);
insert into Employee(id, name, salary,dept_id) values (14, 'Abhilasha',600,2);
insert into Employee(id, name, salary,dept_id) values (15, 'Rachael', 1234,1);
insert into Employee(id, name, salary,dept_id) values (16, 'Dean', 3454,6);Employee Name Salary Department Name location
Vivek 6000 Recruitment Bangalore
Hari 5000 Sales U.S.A
Dean 3454 Operations U.S.A
Heraldo 1500 Recruitment Bangalore
Ronaldo 1500 Recruitment Bangalore
Scott 1300 Recruitment Bangalore
Rachael 1234 Recruitment Bangalore
Michael 1134 Engineering Kolkata
Rakesh 1000 Engineering Kolkata
Aakansha 600 Product Bangalore
Abhilasha 600 Sales U.S.A
Abhimanyu 600 Engineering Kolkata
Jessica 600 Sales U.S.A
Stephinie 400 Product Bangalore
John 250 Engineering Kolkata
Mike 100 Engineering Kolkata
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent