###Title
Practice sql Download:
1. Query the number of students selected for each course;
2. Find out the student numbers and names of all students who have taken only one course;
3. Query the number of boys and girls;
4. Check the list of students surnamed "Zhang";
5. Query the list of students with the same name and surname, and count the number of students with the same name;
6. Query the average score of each course, and the results are arranged in ascending order according to the average score. If the average score is the same, it is arranged in descending order according to the course number;
7. Query the student number, name and average score of all students with an average score greater than 85;
8. Query the names and scores of students whose course name is "Mathematics" and whose score is less than 60;
9. Query the student number and name of students whose course number is 3 and whose course score is more than 80;
10. Find the number of students who have taken the course
11. Query the names and grades of the students with the highest scores among the students taking the courses taught by "Yang Yan";
12. Query each course and the corresponding number of electives;
13. Query the top two with the best scores in each course;
14. Retrieve the student number of at least two courses;
15. Query the course number and course name of the course that all students take;
16. Query the names of students who have not studied any course taught by "Ye Ping";
17. Query the student number and average score of students who have failed more than two courses;
18. Search the student numbers of the students whose scores of the courses with the number "4" are less than 60 in descending order;
19. Delete the grade with the course number of "1" for the student with the student number of "2";

###Answer

– 1. Query the number of students selected for each course;

SELECT course_id,COUNT(*) FROM score GROUP BY course_id;

– 2. Find out the student numbers and names of all students who have taken only one course;

SELECT student_id,sname FROM student 
LEFT JOIN score ON student_id = student.sid
GROUP BY student_id HAVING COUNT(*) = 1;

– 3. Query the number of boys and girls;

SELECT gender,COUNT(*) FROM student GROUP BY gender;

– 4. Check the list of students surnamed "Zhang";

SELECT * FROM student WHERE sname LIKE 'Zhang%';

– 5. Query the list of students with the same name and surname and count the number of students with the same name;

SELECT sname,COUNT(*) FROM student GROUP BY sname HAVING COUNT(*)>1;

– 6. Query the average score of each course, and the results are arranged in ascending order according to the average score. If the average score is the same, it is arranged in descending order according to the course number;

SELECT course_id,cname,AVG(result)FROM course 
LEFT JOIN score ON course_id = cid
GROUP BY course_id ORDER BY AVG(result),course_id DESC;

– 7. Query the student number, name and average score of all students with an average score greater than 85;

SELECT student_id,sname,AVG(result) FROM student 
LEFT JOIN score ON student_id = student.sid 
GROUP BY student_id HAVING AVG(result) > 85;

– 8. Query the names and scores of students whose course name is "Mathematics" and whose score is less than 60;

SELECT sname,result FROM score 
LEFT JOIN student ON student_id = student.sid
LEFT JOIN course ON course_id = cid
WHERE cname = 'mathematics' AND result < 60;

– 9. Query the student number and name of students whose course number is 3 and whose course score is more than 80;

SELECT student_id,sname,course_id,cname,result FROM score 
LEFT JOIN student ON student_id = student.sid
LEFT JOIN course ON course_id = cid
WHERE course_id = 3;

– 10. Number of students who have taken the course

SELECT COUNT(*) FROM (
	SELECT student_id FROM score GROUP BY student_id HAVING COUNT(*) > 0
) tmp;

– 11. Query the names and grades of the students with the highest scores among the students taking the courses taught by "Yang Yan";

SELECT sname,result FROM course 
LEFT JOIN teacher ON teacher_id = tid
LEFT JOIN(
	SELECT sname,result,course_id FROM student 
	LEFT JOIN score ON student_id = student.sid
)tmp ON tmp.course_id = cid 
WHERE tname = 'Yang Yan' ORDER BY result DESC LIMIT 1;

– 12. Query each course and the corresponding number of electives;

SELECT course_id,COUNT(*) FROM score GROUP BY course_id;

– 13. Query the top two with the best scores in each course;

SELECT course_id,student_id,result FROM score tmp
WHERE (
	SELECT COUNT(*) FROM score WHERE tmp.course_id = score.course_id
	AND tmp.result < score.result
) < 2 ORDER BY tmp.course_id, tmp.result DESC;

– 14. Search the student numbers of students taking at least two courses;

SELECT student_id FROM score GROUP BY student_id HAVING COUNT(*) >= 2;

– 15. Query the course number and course name of the courses that all students take;

SELECT course_id,cname FROM score 
LEFT JOIN course ON course_id = cid 
GROUP BY course_id 
HAVING COUNT(*) = (
	SELECT COUNT(*) FROM student
)

– 16. Query the names of students who have not studied any course taught by "Ye Ping";

SELECT sname FROM student 
LEFT JOIN score ON student.sid = student_id
WHERE student_id NOT IN (
	SELECT student_id FROM score WHERE course_id IN(
		SELECT cid FROM course 
		LEFT JOIN teacher ON teacher_id = tid
		WHERE tname = 'Ye Ping'
)
)

– 17. Check the student number and average score of students who have failed more than two courses;

SELECT student_id,AVG(result) FROM score 
WHERE result < 60 
GROUP BY student_id 
HAVING COUNT(*) >= 2

– 18. Search the student numbers of the students whose scores of the course with the number "4" are less than 60 and are arranged in descending order;

SELECT student_id,course_id,result FROM score 
WHERE course_id = 4 ORDER BY result DESC;

– 19. Delete the grade with the course number "1" of the student with the student number "2";

DELETE FROM score WHERE student_id = 2 AND course_id = 1