(1) distinguish between having and where:
The HAVING clause enables you to specify a filter condition that controls which groups in the query result can appear in the final result. The WHERE clause imposes conditions on the selected column, while the HAVING clause imposes conditions on the groups produced by the GROUP BY clause.
The following SQL statements are based on the MySQL5.6.30 version.
1. Check the number of all students who "1" course is higher than "2"
Select A.sno from (select Sno,score from SC where cno=1) as a, (select Sno,score from SC where cno=2) as B where A.SCORE&G T;b.score and A.sno=b.sno;
2, query average score is greater than 60 points of the students and the average score
SELECT Sno,avg (Score) from Scgroup by Sno have AVG (score) >60;
3, check all students of the student number, name, number of courses selected, total
SELECT Student.sno,student.sname,count (SC. Cno), SUM (Sc.score) from Student to OUTER JOIN SC on STUDENT.SNO=SC. Snogroup by Student.sno,sname;
Group BY IS grouped on the basis of a left outer join. Note is to query all students, use the following will use the inner connection, if some students no results will not show. Not in accordance with the topic
4. The number of teachers who queried the surname "Li"
SELECT COUNT (DISTINCT (Tname)) from teacherwhere tname like ' li% ';
5, the inquiry did not learn "Li Xiaofeng" teacher class students of the school number, name
Select Student.sno,student.snamefrom Student WHERE Sno not in ( select DISTINCT (SC. Sno) from Sc,course,teacher WHERE SC. Cno=course.cno and Teacher.tno=course.tno and Teacher.tname= ' Li Xiaofeng ');
6, the inquiry learned "1" and also learned the number "2" course of the student's number, name
SELECT student.sno,student.sname from student,scwhere student.sno=sc. Sno and SC. Cno=1and EXISTS (SELECT * from SC as sc_2 WHERE sc_2.sno=sc. Sno and sc_2.cno=2)--query has also learned 1, 2, 3 courses--and EXISTS (SELECT * from SC as Sc_3 WHERE sc_3.sno=sc. Sno and Sc_3.cno=3)
7, the inquiry has learned "Li Xiaofeng" teacher teaches "all" class student's school number, the name
Select Sno,snamefrom studentwhere Sno in ( select Sno from sc,course, Teacher WHERE SC. Cno=course.cno and Teacher.tno=course.tno and Teacher.tname= ' Li Xiaofeng ' GROUP by Sno have COUNT (SC. Cno) = ( SELECT COUNT (Cno) from course,teacher WHERE teacher.tno=course.tno and tname= ' Li Xiaofeng ' ));
8, check all the course scores of less than 60 students of the student's number, name (no selection of courses without results will also show);
Select Sno,snamefrom studentwhere Sno not in ( select Student.sno from Student,sc WHERE student.sno=sc. Sno and score>60 );
9, the inquiry did not learn all the class student's school number, the name;
SELECT student.sno,student.snamefrom student,scwhere student.sno=sc. Sno GROUP by Student.sno,student.sname has COUNT (SC. Cno) < (SELECT COUNT (COURSE.CNO) from Course);
10, the inquiry has at least one course and the school number for "1" students learn the same student number and name;
Select S.sno,s.sname from Student s GROUP by S.sno,s.sname have S.sno in (SELECT course.cno from COURSE,SC WHERE Sc.sno =1) SELECT s.sno,s.sname from Student s,sc SC WHERE s.sno=sc. Sno and SC. Cno in (SELECT course.cno from Course,sc WHERE SC. sno=1) GROUP by S.sno
11, the "SC" table in the "Lili" teacher taught the results of the course are changed to the average performance of the curriculum;
UPDATE sc,course,teacher SET sc.score= (select AVG (Sc_2.score) from (SELECT * from SC) as sc_2 WHERE SC_2.CNO=SC. Cno) WHERE COURSE.CNO=SC. Cno and Course.tno=teacher.tno and Teacher.tname= ' Lili ';
12, Inquires and "2" number of students to study the course of the same class of other students and the name of the school;
Select Sno from SC where Cno in (select Cno from SC where sno=2) GROUP by Sno have COUNT (*) = (select count (*) from SC WHERE sno=2);
13, delete learning "Lili" the SC table record of the teacher class;
DELETE SC from course,teacher,sc WHERE course.cno=sc. Cno and Course.tno=teacher.tno and Tname= ' Lili ';
14, insert some records into the SC table, these records are required to meet the following conditions: There is no number "3" course of the students study number, 2nd, the average score;
INSERT SC Select sno,2, ( select AVG (score) from SC where cno=2 ) from Student where Sno not in (Selec T Sno from SC WHERE cno=3);
15, according to the average score from high to low display all students "high number", "C language", "Java Advanced Programming" of the course scores, shown as follows: Student ID, high number, C language, Java Advanced Program design, effective course number, effective average score
Select Sno as Student ID , (select Score from SC WHERE SC. Sno=t.sno and cno=4) as high number , (SELECT score from SC WHERE SC. Sno=t.sno and Cno=1) as C language , (SELECT score from SC WHERE SC. Sno=t.sno and Cno=6) as Java program Advanced Design , COUNT (*) as effective course count, avg (T.score) as average score from SC as T GROUP by Sno OR DER by AVG (T.score)
16. Check the highest and lowest score of each section: show the following form: Course ID, highest score, lowest score
SELECT SC. Cno,max (Score), MIN (score) from SC GROUP by Cno
17, according to the average grades from low to high and the percentage of passing rate from high to low order
SELECT T.cno As course number, MAX (c.cname) As course name, ifnull (avg (T.score), 0) as average, * SUM ( case when Ifnull (t.score,0) >=60 then 1 ELSE 0 END)/count (*) as passing percent from SC t,course cwhere T.cno=c.cnogroup by T.cnoorder by Pass Hundred Fractional DESC
18, query different teachers teach different courses average from high to low display
SELECT MAX (Z.tno) as Teacher Id,max (Z.tname) as faculty name, C.cno as course id,c.cname as course name, AVG (score) as average score from SC as t,course as C, Teacher as Z WHERE t.cno=c.cno and C.tno=z.tno GROUP by C.cno ORDER by average score DESC
19, query The following course results 3rd to 6th student Transcripts: High number (1), C (2), Java Advanced Programming (3), data structure and algorithm (4) Student ID, student name, high number, C language, Java Advanced Program design, data structure and algorithm, average score
SELECT SC. Sno as student number, student.sname as student name, T1.score as high number, T2.score as C language, T3.score as Java advanced Programming, T4.score as data structure and algorithm, ifnull (t1.score,0) +ifnull (t2.score,0) +ifnull (t3.score,0) +ifnull (t4.score,0) as total score From STUDENT,SC left joins SC as T1 on SC. Sno = T1. Sno and T1. Cno =1 left joins SC as T2 on SC. Sno = T2. Sno and T2. Cno =2 left joins SC as T3 on SC. Sno = T3. Sno and T3. Cno =3 left joins SC as T4 on SC. Sno = T4. Sno and T4. Cno =4 WHERE student.sno=sc. Sno GROUP by SC. Sno ORDER by Total DESC LIMIT 3,3
20, the Statistics column prints each section result, each score segment number: Course ID, course name, [100-85],[85-70],[70-60],[<60]
SELECT SC. Cno as Course ID, Cname as course name , sum (case "score between" and "1 ELSE 0 END) as ' [100-85] ' , sum (case WH EN score between and 1 else 0 end) as ' [85-70] ' , SUM (case when score between and 1 else 0 end) As ' [70-60] ' , SUM (case is score <60 then 1 ELSE 0 END) as ' ["] ' from sc,course WHERE SC. Cno=course.cno
21, check the average grade of students and their rankings
Select 1+ (select COUNT (DISTINCT average score) from (select Sno,avg (score) as average score from SC GROUP by Sno) as T1 WHERE average Performance > T2. Average score ) as rank, Sno as student number, average score from (SELECT Sno,avg (score) as average score from SC
22. Check the records of the top three grades of each section: (regardless of the performance of the situation)
SELECT t1. Sno as student id,t1. Cno as courses Id,score as fractions from SC T1 where score in ( SELECT score from SC where T1. Cno=cno ORDER by score DESC
The use of the Limit keyword in subqueries is not supported, and the error is as follows:
This version of MySQL doesn ' t yet support ' LIMIT & in/all/any/some subquery '
23. Check the top two of each course score
24. Find out the number and name of all students who have only one course of study
SELECT SC. Sno,student.sname,count (Cno) As course number from SC, Student WHERE SC. Sno=student.sno
25.1981-Born Student list (note: The type of sage column in the student table is datetime)
SELECT sname,cast (date_format (sbirthday, '%y-%m-%d ') as CHAR) as birthday from Studentwhere
26, the average score of each course is queried, the results are arranged in ascending order of average grade, and the average result is the same, descending by the course number.
SELECT Cno,avg (Score) from SC GROUP by Cno ORDER by AVG (score), Cno DESC;
27. The name of the student with the highest achievement and the results of the students who have enrolled in the course of the "Li Xiaofeng" teacher
SELECT Student.sname,score from Student,sc,course c,teacher WHERE student.sno=sc. Sno and SC. Cno=c.cno and C.tno=teacher.tno and Teacher.tname= ' Li Xiaofeng ' and sc.score= (SELECT MAX (score) from SC WHERE cno=c.cno);--The following statement Is the wrong select Student.sname,score from Student,sc,course c,teacher WHERE student.sno=sc. Sno and SC. Cno=c.cno and C.tno=teacher.tno and Teacher.tname= ' Li Xiaofeng ' ORDER by Sc.score DESC LIMIT 1
Second statement when the Li Xiaofeng teacher teaches multiple courses, the results of the query should be more than one.
28. Sno and sname of students with at least two courses
Select Std. Sno,std. Sname from student Std,sc SC where std. Sno=sc. Sno GROUP BY Std. Sno has count (Distinct (SC. Cno)) >2
The distinct () function can be used in count ()
Sword refers to the architect series-mysql Common SQL statements