Professor names and Salaries HackerRank solution

In SQL, we need to find out the department-wise information from the given table containing information about employees. One such data is the name of the department having the highest average salary of employees working in it. We shall use the TOP, AVG, ORDER BY, AS, GROUP BY, and DESC clauses to achieve this. This is illustrated below. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

Output:

Step 3: Create a table COMPANY inside the database GeeksForGeeks. This table has 4 columns namely EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_NAME, and SALARY containing the id, name, department, and the salary of various employees.

Query:

Output:

Step 4: Describe the structure of the table COMPANY.

Query:

Output:

Step 5: Insert 5 rows into the COMPANY table.

Query:

Output:

Step 6: Display all the rows of the COMPANY table.

Query:

Output:

Step 7: Display the name of the department having the highest average salary obtained by the employees working in that department. We will use the aggregate function AVG here to calculate the average(mean) salary of each department. The department-wise average is obtained with the help of the GROUP BY clause which groups all the salaries of a specific department together and then calculates the average. Then these averages are sorted in descending order using ORDER BY and DESC clauses. Then the topmost row is selected using the TOP clause followed 1. 1 here indicates that the query returns only 1 row. We also name the new column of the department-wise salary as MAX_AVG_SALARY using the AS clause which creates kind of an alias.

Syntax:

Query:

Note: This query returns only 1 row i.e. the topmost row among the returned sorted rows.

Output:

Article Tags :

Closed. This question is off-topic. It is not currently accepting answers.

Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background).

• Student (id integer, name string, address string, status string)
• Course (crsCode string, deptID string, crsName string, descr string)
• Transcript (studentID integer, crsCode string, semester string, numericGrade double)
• Teaching (profID integer, crsCode string, semester string)
• Department (deptID string, name string, budget integer)
• Professor (id integer, name string, deptID string)

I don't know why, but for some reason this SQL query is not working. Please help me if you can.

Mechanical Electrical Engineering Civil Engineering Chemical Engineering Electronics and Communication Engineering Mathematics Physics Chemistry Software Works/ Computer Science Other Subjects

Expert Tutors 100% Correct Solutions 24/7 Availability One stop destination for all subject Cost Effective Solved on Time Plagiarism Free Solutions Confidentiality

Q1. Write a query against the professors table that can output the following in the result: "Chong works in the Science department"

SELECT last_name || ' works in the ' || department || ' department' FROM professors

Q2. Write a SQL query against the professors table that would return the following result:

# "It is false that professor Chong is highly paid" # "It is true that professor Brown is highly paid" # "It is false that professor Jones is highly paid" # "It is true that professor Wilson is highly paid" # "It is false that professor Miller is highly paid" # "It is true that professor Williams is highly paid"

• NOTE: A professor is highly paid if they make greater than 95000.

SELECT 'It is ' || (salary > 95000) || ' that professor ' || last_name || ' is highly paid ' FROM professors

Q3. Write a query that returns all of the records and columns from the professors table but shortens the department names to only the first three characters in upper case.

SELECT last_name, UPPER(SUBSTRING(department, 1, 3)) as department, salary, hire_date FROM professors

Q4. Write a query that returns the highest and lowest salary from the professors table excluding the professor named 'Wilson'.

SELECT MAX(salary), MIN(salary)
FROM professors WHERE last_name != 'Wilson'

Q5. Write a query that will display the hire date of the professor that has been teaching the longest.

SELECT MIN(hire_date) FROM professors

Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.

Input Format

The Employee table containing employee data for a company is described as follows:

Sample Input

Sample Output

SELECT name FROM Employee ORDER BY name

Employee Salaries

Problem

Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than $2000 per month who have been employees for less than 10 months. Sort your result by ascending employee_id.

Sample Output

Explanation

Angela has been an employee for 1 month and earns $3443 per month.
Michael has been an employee for 6 months and earns $2017 per month.
Todd has been an employee for 5 months and earns $3396 per month.
Joe has been an employee for 9 months and earns $3573 per month.
We order our output by ascending employee_id.

SELECT name FROM Employee WHERE salary > 2000 AND months < 10 ORDER BY employee_id