1. Use the SELECT statement for basic queries in the studentsdb database.
   (1) In the student_info table, query each student's school number, name, birth date information.

SELECT School Number,Full name,Date of birth
FROM `student_info`;

(2) Query the names and home addresses of students whose student_info form number is 0002.

SELECT Full name,Home Address
FROM `student_info`
WHERE School Number=0002;

(3) Query the names and dates of all female students born after 95 years in the student_info table.

SELECT Full name,Date of birth
FROM `student_info`
WHERE `Gender` = 'female' AND Date of birth >= '1995-12-12';

1. Conditional queries are made using the select statement.
   (1) Query the grade table for student numbers, course numbers and results with scores ranging from 70 to 80.

SELECT `School Number`,`Course Number`,`Fraction`
FROM `grade`
WHERE `Fraction` > 70 AND `Fraction` < 80;

(2) Query the grade table for the average scores of students whose course number is 0002.

SELECT AVG(`Fraction`)AS 'Average Scores'
FROM grade
WHERE `Course Number`='0002';

(3) Query the number of persons with the optional course number 0003 and the number of persons with achievements in the course in the grade table.

SELECT COUNT(*)Number of people FROM grade WHERE `Course Number`='0003'AND `Fraction`>0;

(4) Query the name and date of birth of student_info, and the results are sorted from largest to smallest.

SELECT `Full name`,`Date of birth`
FROM `student_info` ORDER BY `Date of birth`DESC;

(5) Query the school numbers and names of all the students whose names are "Zhang".

SELECT `Full name`,`School Number`
FROM student_info
WHERE `Full name`LIKE 'Zhang%';

1. For the student_info table, the student's school number, name, gender, birth date and home address are queried. The results are sorted from smallest to largest by gender, and from largest to smallest by gender.

SELECT School Number,Full name,Gender,Date of birth,Home Address from student_info
order by Gender,School Number desc;

1. Use the GROUP BY clause to query the average scores of each student in the grade table.

SELECT AVG(`Fraction`)as Average Scores
FROM grade
GROUP BY `Fraction`;

1. Use the UNION operator to pin the student number of the student named "Liu", the student's name and the student's name of the student named "Zhang" in the student_info table to return to one table.

SELECT `School Number`,`Full name` FROM student_info WHERE `Full name`LIKE'Liu%' UNION SELECT `School Number`, `Full name` FROM student_info WHERE `Full name`LIKE'Zhang%';

1. nested queries
   (1) Find in the student_info table the names and birth dates of all students of the same sex as "Liu Dongyang".

SELECT `Full name`,`Date of birth` FROM student_info WHERE `Gender` IN(
SELECT `Gender` FROM  student_info WHERE `Full name`='Invader Music');

(2) Use the IN subquery to find the student number, name and gender of the course number 0002,0005.

SELECT `School Number`,`Full name`,`Gender` FROM student_info WHERE `School Number`IN(
SELECT (`School Number`) FROM grade WHERE `Course Number`='0002'OR `Course Number`='0005');

(3) Use an ANY subquery to find the course number and score of a student with a school number of 0001 that is higher than the minimum score of a student with a school number of 0002.

SELECT `Course Number`,`Fraction` 
FROM grade  WHERE `School Number`='0001'AND `Fraction`>ANY
(SELECT MIN(`Fraction`) FROM grade WHERE `School Number`='0002');

(4) Use ALL subqueries to find students with school number 0001 with higher scores than those with highest scores of school number 0002.

SELECT `Course Number`,`Fraction` 
FROM grade  WHERE `School Number`='0001'AND `Fraction`>ALL
(SELECT MAX(`Fraction`) FROM grade WHERE `School Number`='0002');

1. join query
   (1) Query the number, name and score of students whose scores range from 80 to 90.

SELECT grade.`School Number`,`Full name`,`Fraction` FROM grade,student_info 
WHERE grade.`School Number`= student_info.`School Number` AND( `Fraction` BETWEEN 70 AND 80);

(2) Query the student number, name and score of the course "Database Principles and Applications".

SELECT grade.`School Number`,`Full name`,`Fraction` FROM grade,curriculum,student_info WHERE grade.`Course Number`=curriculum.`Course Number` AND grade.`School Number`=student_info.`School Number` AND (curriculum.`Course Number` IN (SELECT `Course Number` FROM curriculum WHERE `Course Name`='Principle and Application of Database' ));

(3) Query the highest scores of each student's selected course and ask for a list of the number, name and highest scores.

select grade.`School Number`,`Full name`,`Fraction`AS 'Best results'
from student_info,grade
where student_info.`School Number` = grade.`School Number` and `Fraction` in
(select max(`Fraction`) from grade group by `School Number`);

(4) Use the left outer join to query each student's total score, and ask for the number, name and total score. The total score of students who do not have an optional course is empty.

select student_info.School Number,Full name,Total results from student_info left outer join
(select School Number,sum(Fraction) as Total results from grade
group by School Number
)grade on student_info.School Number = grade.School Number

(5) Add data rows to the grade table: school number 0004, course number 0006, score 76.
Use the right outer join to query the elections for all courses, which requires a list of course numbers, course names, and number of elections. The value of the course column that is not in the curriculum table is empty.

INSERT INTO grade VALUES ('0004','0006',76);
select curriculum.Course Number,Course Name,Number of electives
from curriculum right outer join
(
select Course Number,COUNT(*) as Number of electives
from grade
group by Course Number)grade on curriculum.Course Number = grade.Course Number;