Write a SELECT statement that answers this question:Which instructors have an annual salary that’s greater than the average annual salary for all instructors?Display the Last Name, First Name, and Annual Salary columns for each Instructor. Order in descending Annual Salary.
Question
Write a SELECT statement that answers this question:Which instructors have an annual salary that’s greater than the average annual salary for all instructors?Display the Last Name, First Name, and Annual Salary columns for each Instructor. Order in descending Annual Salary.
Solution
Sure, here is the SQL statement that would answer your question:
SELECT LastName, FirstName, AnnualSalary
FROM Instructors
WHERE AnnualSalary > (SELECT AVG(AnnualSalary) FROM Instructors)
ORDER BY AnnualSalary DESC;
Here's the step by step breakdown:
-
SELECT LastName, FirstName, AnnualSalary FROM Instructors: This part of the query selects the columns LastName, FirstName, and AnnualSalary from the table Instructors. -
WHERE AnnualSalary > (SELECT AVG(AnnualSalary) FROM Instructors): This is a subquery that selects the average annual salary from the Instructors table. The WHERE clause in the main query then filters out the instructors whose annual salary is greater than this average. -
ORDER BY AnnualSalary DESC;: This part of the query orders the result set in descending order by the AnnualSalary column.
Similar Questions
Write a query to find employees who have the highest salary in each of the departments.Input format :The input tables are already prepopulated, as given in the problem statement.Output format :The output display the name of each department along with the name of the employee who has the highest salary within that department and their respective salary. Order the output by department name as shown below.Refer to the sample output for the column headers.
SELECT last_name, first_nameFROM employeeWHERE salary IN(SELECT salaryFROM employeeWHERE dept_no = 3 OR dept_no = 5);Which values are displayed?Select one:a.last name and first name of only the employees whose salary falls in the range of salaries from department 3 or 5b.last name and first name of only the employees in department number 3 or 5c.last name and first name of all employees except those working in department 3 or 5d.last name and first name of all employees with the same salary as employees in department 3 or 5
The provost wants to see the number of students in each major. Write a MySQL statement to display this.
Select ID, name, dept name, salary * 1.1 where instructor;The query given below will not give an error. Which one of the following has to be replaced to get the desired output?*Salary*1.1IDWhereInstructor
Write a query to display the coursename of the courses having the coursefees above average fees of all the courses. Display the records sorted in ascending order based on coursename.Note:1. Display only the coursename of the courses having the coursefees above average fees of all the courses.2. Order by is mandatory.Sample Output:
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.