Database exercises (2--simple query questions)

2, the structure of the table is as follows:

Student table (id,name,sex,class_id,address);

Score table (Id,student_id,course_id,score);

Class table (ID,NAME,GRADE_ID);

Grade table (Id,name);

Course table (id,name);

1) Check out all class information under "Senior"

Analysis: The table used has Grade,class

Methods: By combining grade and class through grade number, the class information of senior grade can be selected.

Statement:

Select s1.* from class S1,grade s2 where s1.grade_id = s2.id and s2.name= ' freshman year ';

2) Check out the student information in all classes under "senior year"

Analysis: The table used has grade,class,student

Methods: After the three tables were combined, the students ' information of the first grade was selected.

Statement:

Select s1.* from student S1,class s2,grade s3 where s1.id = s2.student_id and s2.grade_id = s3.id and s3.name = ' Freshman year ';

3) Find out the information of male students in all classes under "senior year"

Analysis: The tables used are Student,class,grade

Methods: The three tables were combined, then the students ' information could be found in the upper grade and gender, that is, adding a condition in the above statement s1.sex = ' Male '

Statement:

Slightly

4) Check out the student's name, gender, subject name, score of less than or equal to 90 points

Analysis: The table used has student--(name, gender), score--score, course--account name

Methods: The three tables were combined to find out the relevant information of students less than or equal to 90 points, score.score<=90

Statement:

Slightly

5) Query the student's number and name as well as the score of all students who have a math score of less than 90 sophomore grade

Analysis: The table used has student--number and name, score--score, grade--sophomore grade, course--Mathematics, class--through this table and grade table joint

Methods: After 5 tables were combined, the sophomore grade (Grade.name= ' sophomore grade ') was found from the grade table, the Math course (Course ' mathematics ') was found in Course.name=, and the score table was found to be less than (Score.score <90), then show the student's school number, name and score to

Statement: slightly

6) Number of students who have no grades in the enquiry form, name

Analysis: The table used has Student,score

Methods: First find out the student's number of grades, and then with the students in the table of the number of the comparison, to find no longer student table in the school number can be

Statement:

Select Id,name from student where ID not in (select student_id from score);

7) Statistics by grade group How many classes are in each grade

Analysis: The table used has class--statistics class number, grade--get grade Name

Methods: After two tables were combined, the grade_id in the class table were grouped and the number of classes per grade was counted.

Statement:

Select S2.name,count (*) as ' class number ' from class S1,grade s2 where s1.grade_id = S2.id GROUP by s2.id;

8) Statistics by grade group How many students are in each grade

Analysis: The table used has student--statistics of students, class--through this table by grade group, grade--find the name of grade

Methods: After the three tables were combined, the number of students was counted (count (*)) by grade group (group by grade).

Statement:

Select S3.name,count (*) from student S1,class S2,grade s3 where s1.class_id = s2.id and s2.grade_id = S3.id GROUP BY s3.id ;

3, the structure of the table is as follows:

Books (book ISBN, book name, author number, publishing house, publication date);

Author (author name, author number, age, gender);

Requirements: use SQL to query the author's name, book name, and publisher of the age less than the average age.

Analysis: The tables used are books and authors two tables

Methods: After combining the two tables, we found the information about AVG (age) less than the average age in the author's table.

Note : The average age here is because it is a dynamic value and cannot be compared directly with age <avg (age), and the average age must be obtained by "SELECT AVG (age) from author" before comparing

Statement:

Select S1. Author name, S2. Book name, S2. Publishing house

From author S1, book S2

where S1. Author number = S2. Author number

and Age < (select AVG (age) from author);

Database exercises (2--simple query questions)