Dr Stephane Nwolley wants to identify the students with a score greater than the average. Write a MySQL statement to identify these students. Display the student names (Join first name and last name and call it full_name), the score of the student, and their major.
Question
Dr Stephane Nwolley wants to identify the students with a score greater than the average. Write a MySQL statement to identify these students. Display the student names (Join first name and last name and call it full_name), the score of the student, and their major.
Solution
Sure, here is the MySQL statement to identify the students with a score greater than the average.
SELECT CONCAT(first_name, ' ', last_name) AS full_name, score, major
FROM students
WHERE score > (SELECT AVG(score) FROM students);
This statement works as follows:
-
SELECT CONCAT(first_name, ' ', last_name) AS full_name, score, major FROM students: This part of the query selects the full name (by joining the first name and last name), score, and major of all students from the students table. -
WHERE score > (SELECT AVG(score) FROM students): This is a subquery that calculates the average score of all students. The WHERE clause then filters out the students whose score is greater than this average score.
Similar Questions
You have a database that tracks students in the students table by student_id. The names of the semesters are in a semesters table where the primary key is semester_id. Each semester, students are assigned a grade_point_average which is stored in the grades table. The grades table lists the student_id and the semester_id in addition to the grade_point_average.You need to create a query that finds all the semesters where the student had a grade point average that was less than their average grade point average. You begin typing your query:What should you type in the blank space? (Please type your answer all in lower case. Example: select)
Write a MySQL statement to sort the students in descending order of the last name
Given 3 tables, students(id, name) , courses(id, name) , grades(id, course_id, student_id, grade), for each student, get all the courses that he/she is enrolled in along with the grade he/she scored for each course. Order the result by the student name in ascending order and if there is a tie, break it with the course name in ascending order and if there is a tie break it with the grade in ascending order. Your query should output a table with the following columns (name, course, grade).
The provost wants to see the number of students in each major. Write a MySQL statement to display this.
ou have a database that tracks students in the students table by student_id. Students may get warnings for low grades or poor behavior. The warnings are tracked in the warnings table where the student is referred to by student_id.You need to create a query that finds the first and last name of the student who has the most records in the warnings table. You begin typing your query:What should you type in the blank space? (Please type your answer all in lower case. Example: select)
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.