Sword refers to the architect series-mysql Common SQL statements

(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