– 1. Query all records in the student table SELECT * FROM student;

– 2. Query the s'name, s'sex and s'class columns of all records in the student table

SELECT s_no,s_name,s_class FROM  student;

– 3. Query all the units of the teacher, but do not repeat the T "department" column

SELECT distinct (t_depart) FROM teacher; 

– 4. Query all records with score between 60-80 (sc_degree)

SELECT * FROM score WHERE sc_degree BETWEEN 61 AND 79;
SELECT * FROM score WHERE sc_degree < 80 AND sc_degree > 60 ; 

– 5. Query the records with scores of 85, 86, or 88 in the score table (sc_degree)

SELECT * FROM score WHERE sc_degree IN(85, 86, 88);

– 6. Query the student record of '95031' class or 'female' in the student table

SELECT * FROM student WHERE s_class = '95031' OR s_sex = 'female';

– 7. Query all records in student table in class descending order

SELECT * FROM student ORDER BY s_class desc;

– 8. Insert all data in the score table in ascending order of C ﹣ No. in descending order of SC ﹣ degree, first in ascending order of C ﹣ No. if C ﹣ no is the same, then in descending order of SC ﹣ degree

SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;

– 9. Query the number of students in class' 95031 '

SELECT COUNT(s_no) FROM student WHERE s_class = '95031';

– 10. query the student number and course number of the highest score in the score table. (sub query or sorting)

Sub query:

 --Query steps
 1. Find the highest score first
 Highest score: select max (SC ﹣ degree) from score;
2. Find the student number and course number we need from the score through the highest score we find
 Select C? No, s? No from score where SC? Degree = (highest score)
Final:
SELECT c_no, s_no FROM score WHERE sc_degree = (SELECT MAX(sc_degree) FROM score);

Sort:

There may be data problems when there are multiple highest ranking scores
 Limit x, y (X: starting from X pieces of data Y: how many pieces need to be found)
Select C? No, s? No from score order by SC? Degree desc limit 0,1; this is OK
 But let's insert another piece of data:
INSERT INTO score VALUES('101','9-888','92');
Then use the sorting method to find out:
+-------+------+
| c_no  | s_no |
+-------+------+
| 9-888 | 101  |
+-------+------+
There are two pieces of data, but only one is displayed. There is a problem

– 11. Query the average score of each course

SELECT c_no,AVG(sc_degree) FROM score GROUP BY c_no;

– 12. Check the average score of at least 2 students in the score table who take courses starting with 3

SELECT AVG(sc_degree),c_no  from score GROUP BY c_no HAVING COUNT(c_no)  >= 2 AND c_no like '3%'  ;

– 13. Query the s ﹣ no column whose score is greater than 70 but less than 90:

SELECT s_no,sc_degree  FROM score WHERE sc_degree BETWEEN 71 AND 89;

– 14. Query all the students' s name, C No, SC degree columns

SELECT s_name, c_no, sc_degree FROM student,score WHERE student.s_no = score.s_no;

– 15. Query the s no, C name, SC degree columns of all students

SELECT student.s_no, c_name, sc_degree FROM student, course,score WHERE student.s_no = score.s_no AND score.c_no = course.c_no ;

– 16. Query all students in the s ﹣ name, C ﹣ name, SC ﹣ degree column

SELECT s_name, c_name, sc_degree FROM student, course, score WHERE student.s_no = score.s_no AND score.c_no = course.c_no;

– 17. Query the average score of each class of class' 95031 '

select c_no,AVG(sc_degree) from score WHERE s_no IN (select s_no from student where s_class = '95031') GROUP BY c_no;

select c_no,AVG(sc_degree) from score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = '95031' GROUP BY sc.c_no;

select sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC  WHERE s.s_class = '95031' AND s.s_no = sc.s_no GROUP BY sc.c_no ;

Advanced, add course name:

SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c  WHERE s.s_class = '95031' AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ;

– 18. Query the records of all students who have taken "3-105" course and scored higher than "3-105" of classmate '109'

SELECT * FROM student AS s, score AS sc WHERE sc.c_no = '3-105' AND sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no;

– 19. Query all records of scores higher than those with student number '109' and course number '3-105'

SELECT * FROM score WHERE sc_degree >(SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105');

– 20. Query s ﹣ no, s ﹣ name and s ﹣ birthday of all students born in the same year with student number 108.101

SELECT * FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN('108','101'));