SQL query Exercise (i)

New Student Information Sheet students:sno, sname, Ssex, Birthday, Province, class

New Student Elective information sheet cclasses:sno, CNO, Grade

one, single-table query practice

1, inquiry < Student information Form, inquiry students "Zhang San" all the basic information

SELECT * from students where Sname= ' Zhang San ';

2, inquiry < Student information Form, inquiry students "Zhang San" and "WANGSI" basic information

SELECT * Form students where Sname= ' Zhang San ' and Sname= ' Wangsi ';

3, inquiry < Student information Form, query the surname "Zhang" student's basic information

SELECT * Form students where sname like ' Zhang% ';

4. Inquiry < student information Form; Check the basic information of students whose names contain the word "four"

SELECT * Form students where sname like '% four ';

5, inquiry < Student information Form, query name length of three words, surname "Li", and the last word is "strong" all the information

SELECT * Form students where sname like ' li _ strong ';

6, inquiry < Student information Form, query the surname "Zhang" or the surname "Li" Student's basic information

SELECT * Form students where sname like ' Zhang% ' or sname like ' li% ';

7, inquiry < Student information form, check the surname "Zhang" and "the province" is "Beijing" Student Information

SELECT * Form students where sname like ' Zhang% ' and province = ' Beijing ';

8, inquiry < Student information form, check the "province" is "Beijing", "Xinjiang", "Shandong" or "Shanghai" Students information

SELECT * Form students where province in (' Beijing ', ' Xinjiang ', ' Shandong ', ' Shanghai ');

9, inquiry < Student information form, check the surname "Zhang", but "the province" is not "Beijing" Student Information

SELECT * Form students where sname like ' Zhang% ' and province not in (' Beijing ');

10, inquiry < Student information Form, query all student information, and in accordance with the "gender" sort, the same gender in the case of the "province" sort, the province of the same situation, and then according to "class" sort

SELECT * Form students order by Ssex, Province, class;

11, inquiry < Student information Form, query the different provinces from which the existing students come from

Select distinct province form students;

12, inquiry < student elective information form, query the student who did not fill in the grade number, course number

Select Sno, CNO form classes where grade is null;

13, inquiries < students to take the information form, query all completed the student's elective information, and according to "score" from high to low to sort

SELECT * Form classes where grade is not NULL order by grade DESC;

second, the polymerization function exercises

1, Statistics < Student information table, statistics of the total number of students

Select COUNT (*) as the total number of students form students;

2. Statistics < Student information table, how many students with a statistical age greater than 20 years

Select COUNT (*) as student number form students where Borthday < 1997;

3. Statistics < Student information table, statistics on the number of students born from 1980 to 1982

Select COUNT (*) as student number form students where birthday between 1980 and 1982;

4, Statistics < students elective information table, the number of "S001" students of the average score

Select AVG (grade) Form classes where sno=s001;

5, Statistics < students elective information table, the number of "S001" students of the total

Select sum (grade) Form classes where sno=s001;

6, Statistics < student elective Information Form, inquiry course number "C001" the highest grade of the course

Select Max (garde) Form classes where cno=c001;

7. Statistics < student information Form; Check the maximum age of all students

Select min (birthday) Form students;

Third, group query practice

1, Statistics < student elective information table, statistics of the number of electives per course

Select CNO, COUNT (*) Form classes GROUP by CNO;

2, Statistics < students elective information table, statistics of each student's total

Select Sno, sum (grade) Form classes Group by Sno;

3. Statistics < Student information table, statistics on the number of students per gender in each class, sorted by class

Select Class, Ssex, COUNT (*) Form students GROUP by Ssex order by class;

4, Statistics < student elective information table, statistics on the average score per course, and in descending order of grades

Select CNO, avg (grade) Form Classes Group by CNO ORDER by AVG (grade) desc;

5. Statistics < student elective information table, showing the number of students who have failed in more than two courses, scores

Select Sno, Grade form classes where grade < GROUP by SNO have Count (*) >2;

6, Statistics < Student information table, statistics of the maximum age in each class is how much

Select class, Min (birthday) Form students group by class;

SQL query Exercise (i)