50 examples of SQL query tips and query statements

Study the

1, the inquiry "001" course is higher than "002" of all students of the school number;

2, the query average score is more than 60 points of the student's number and average score;

3, inquiry all students of the school number, name, number of courses selected, total;

4, inquire the surname "Li" the number of teachers;

5, the inquiry did not learn "cotyledons" teacher class students of the school number, name;

6, the inquiry learned "001" and also learned the number "002" course of the students ' study number, name;

7, the inquiry has learned "Cotyledons" the teacher teaches all classes The student's school number, the name;

8, inquires the course number "002" The result is lower than the course number "001" The student's school number, the name;

9, check all the course results are less than 60 points of the student's school number, name;

10, the inquiry did not learn all the class student's school number, the name;

11, the inquiry has at least one course and the school number for "1001" students learn the same student number and name;

12, inquiry at least to learn the number of "001" Students all the other students of the class number and name;

13, the "SC" table in the "cotyledons" teacher taught the results of the course are changed to the average performance of the curriculum;

14, inquires and "1002" number of students to study the course of the same class of other students and the name of the school;

15, delete learning "cotyledons" the SC table record of the teacher class;

16, insert some records into the SC table, these records are required to meet the following conditions: No number "003" course of the student number, 2, the average grade of the class;

17, according to the average score from high to low display all students "database", "Enterprise Management", "English" the course results, as shown in the following form: Student ID, database, business management, English, effective course number, effective average score

18. Check the highest and lowest score of each section: show the following form: Course ID, highest score, lowest score

19, according to the average grades from low to high and the percentage of passing rate from high to low order

20. Check the percentage of average and pass rate for the following courses (shown with "1 lines"): Enterprise Management (001), Marx (002), OO&UML (003), Database (004)

21, query different teachers teach different courses average from high to low display

22, query The following course results 3rd to 6th Students Transcript: Enterprise Management (001), Marx (002), UML (003), Database (004)
[Student id],[student name], Enterprise management, Marx, UML, database, average score

23, the statistics print each section result, each score segment number: Course ID, course name, [100-85],[85-70],[70-60],[<60]

24, check the average grade of students and their rankings

25. Check the records of the top three grades of each section: (regardless of the performance of the situation)

26. Check the number of students who have been enrolled in each course

27. Find out the number and name of all students who have only one course of study

28. Query the number of boys and girls

29. Check the list of students with the surname "Zhang"

30, check the same-name list of same-sex students, and statistics of the same number

31.1981-Born Student list (note: The type of sage column in the student table is datetime)

32, 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.

33. The number, name and average scores of all students who have average scores greater than 85

34. Check the name and score of the student whose class is "database" with a score below 60

35. Check the course selection of all students;

36. Inquire about the name, course name and score of any course score above 70 points;

37, the examination of the course of failure, and according to the course number from large to small arrangement

38. The number and name of the student whose course number is 003 and the course score is more than 80;

39. Number of students selected for the course

40. The name of the student with the highest achievement and the results of the students who have enrolled in the course of the "cotyledons" teacher

41. Check each course and the corresponding number of elective

42. Check the student's number, course number and student achievement of the same students with different course grades.

43. Check the top two of the best results for each door

44. The number of students enrolled in each course (more than 10 participants are counted). Require the output of the course number and the number of elective, query results in descending order of numbers, query results in descending order of numbers, if the number is the same, in ascending order by course number

45. Retrieve the student number of at least two elective courses

46. Check the course number and course name of all the students who have enrolled in the course

47, inquiry did not learn the "cotyledons" teacher taught the name of any course students

48. Check the number of students with two or more failed courses and their average scores

49, search "004" course score is less than 60, in descending order by the number of students

50, delete the "002" Students of the "001" The results of the course

Http://m.jb51.net/article/67932.htm

50 examples of SQL query tips and query statements