Explain

The difficulty of the basic exercises is fairly simple, but there are still some details to master.
The main purpose of this topic is to familiarize yourself with the basic queries of SQL statements, so you can understand the statements about table building, because in general development, tables are built graphically, while SQL statements are used less.
Although these topics are relatively simple, most of them are simple queries in the beginning, so we need to master them.

Table building requirements

1. There is a database consisting of four tables: Student, Course, Score and Teacher.The four tables are structured as Table 1-1 (1) - Table 4

             Table 1-1 Table structure of database 

Table (1) Student (Student Table)

Property Name	data type	Is it empty	Meaning
STU_ID	Char(3)	no	School Number (Primary Code)
STU_NAME	Char(8)	no	Student Name
STU_SEX	Char(2)	no	Student Gender
STU_BIRTH	datetime	can	Student's Birth Year Month
CLASSES	Char(5)	can	Student's Class

Table (2) Course (Course)

Property Name	data type	Is it empty	Meaning
COU_ID	Char(5)	no	Course Number (Main Code)
COU_NAME	Varchar(10)	no	Course Name
TEA_ID	Char(3)	no	Faculty Number (External Code)

Table (3) Score

Property Name	data type	Is it empty	Meaning
STU_ID	Char(3)	no	School Number (External Code)
COU_ID	Char(5)	no	Course Number (External Code)
DEGREE	Decimal(4,1)	can	achievement
Primary Code:	STU_ID+ COU_ID

Table (4) Teacher (Teacher Table)

Property Name	data type	Is it empty	Meaning
TEA_ID	Char(3)	no	Faculty Number
TEA_NAME	Char(4)	no	Faculty Name
TEA_SEX	Char(2)	no	Faculty Gender
TEA_BIRTH	datetime	can	Teacher's Birth Year Month
TEA_PROF	Char(6)	can	Title
DEPART	Varchar(10)	no	Faculty Department

Data in database

Student table data (student table)

score table data (report sheet)

Data from coursetable (course Table)

teacher table data (teacher table)

Table building SQL statement

-- Establish Student Table, Student Table
CREATE TABLE Student(
STU_ID CHAR(3) NOT NULL PRIMARY KEY,
STU_NAME CHAR(8) NOT NULL,
STU_SEX CHAR(2) NOT NULL,
STU_BIRTH DATETIME NULL,
CLASSES CHAR(5) NULL
)
-- Establish Teacher Table, Teacher Table
CREATE TABLE Teacher(
TEA_ID CHAR(3) NOT NULL PRIMARY KEY,
TEA_NAME CHAR(4) NOT NULL,
TEA_SEX CHAR(2) NOT NULL,
TEA_BIRTH DATETIME NULL,
TEA_PROF CHAR(6) NULL,
DEPART VARCHAR(10) NOT NULL
)
-- Establish Course Schedule, Course
CREATE TABLE Course(
COU_ID CHAR(5) NOT NULL PRIMARY KEY,
COU_NAME VARCHAR(10) NOT NULL,
TEA_ID CHAR(3) NOT NULL,
FOREIGN KEY(TEA_ID) REFERENCES Teacher(TEA_ID)
)
-- Establish Score Table, Achievement Sheet
CREATE TABLE Score(
STU_ID CHAR(3) NOT NULL,
COU_ID CHAR(5) NOT NULL,
DEGREE DECIMAL(4,1) NULL,
PRIMARY KEY(STU_ID,COU_ID),
FOREIGN KEY(STU_ID) REFERENCES Student(STU_ID),
FOREIGN KEY(COU_ID) REFERENCES Course(COU_ID)
)

SQL Query Statement Question

1. Query STU_for all records in the Student tableNAME, STU_SEX and CLASSEES columns.
2. Query the units owned by the teachers, that is, do not repeat the DEPART column.
3. Query all records in the Score table with scores between 60 and 80.
4. Query records with score 85, 86 or 88 in the Score table.
5. Query the records of "95031" class or classmates with gender "female" in the Student table.
6. With COU_ID ascending and DEGREE descending queries all records of the Score table.
7. Query the number of students in class 95031.
8. Query the student number and course number with the highest score in the Score table.
9. Query the average results of each course.
10. Query the average score of courses taken by at least five students in the Score table.
11. Query STU_of all studentsNAME, COU_ID and DEGREE columns.
12. Query STU_of all studentsNAME, COU_NAME and DEGREE columns.
13. Query the average score of students in class 95033.

Query Question Answers

-- 1, query Student All records in the table STU_NAME,STU_SEX and CLASSES Column.
SELECT STU_NAME,STU_SEX,CLASSES
FROM student
-- 2, Query all units owned by the teacher without repeating DEPART Column.
SELECT DISTINCT DEPART
FROM teacher
-- 3, query Score All records in the table with scores between 60 and 80.
SELECT *
FROM score
WHERE DEGREE >= 60 AND DEGREE <= 80
-- 4, query Score Records with scores of 85, 86 or 88 in the table.
SELECT *
FROM score
WHERE DEGREE IN ('85','86','88')
-- 5, query Student Class 95031 or female students'records in the table.
SELECT *
FROM student
WHERE CLASSES = '95031' OR STU_SEX = 'female'
-- 6, with COU_ID Ascending order, DEGREE Descending Query Score All records of the table.
SELECT *
FROM score
ORDER BY COU_ID ASC,DEGREE DESC
-- 7, Query the number of students in class 95031.
SELECT COUNT(CLASSES) AS 95031number
FROM student
GROUP BY CLASSES HAVING CLASSES='95031'
-- 8, query Score The student number and course number with the highest score in the table.
SELECT STU_ID,COU_ID
FROM score
ORDER BY DEGREE DESC LIMIT 1
-- 9, Query the average score for each course.
SELECT c.COU_NAME,C_AVG.DEGREE_AVG
FROM course AS c LEFT JOIN 
(SELECT COU_ID,AVG(DEGREE) AS DEGREE_AVG
FROM score
GROUP BY COU_ID) AS C_AVG
ON c.COU_ID=C_AVG.COU_ID
-- 10,query Score Average scores for courses taken by at least five students are shown in the table.
SELECT c.COU_NAME,`COUNT(STU_ID)` AS NUMBER,`AVG(DEGREE)` AS `AVG`
FROM course AS c INNER JOIN(
SELECT COU_ID,COUNT(STU_ID),AVG(DEGREE)
FROM score
GROUP BY COU_ID) AS s_avg
ON c.COU_ID=s_avg.COU_ID AND `COUNT(STU_ID)`>=5 
-- 11,Query all students STU_NAME,COU_ID and DEGREE Column.
SELECT STU_NAME,sc.COU_ID,DEGREE
FROM student st LEFT JOIN score sc
ON st.STU_ID=sc.STU_ID
-- 12,Query all students STU_NAME,COU_NAME and DEGREE Column.
SELECT STU_NAME,c.COU_NAME,DEGREE
FROM student st JOIN score sc JOIN course c
ON st.STU_ID=sc.STU_ID AND c.COU_ID=sc.COU_ID
ORDER BY STU_NAME
-- 13, Query the average score of the students in class 95033.
SELECT CLASSES,AVG(DEGREE),COU_ID
FROM student st  JOIN score sc
ON st.STU_ID=sc.STU_ID
GROUP BY COU_ID,CLASSES
HAVING CLASSES='95033'