MySQL Common command instances

--Complete the SQL exercises in the project footage and save the written SQL files and runs, and then submit them as jobs.

--Design a student performance database that contains information about students, teachers, courses and accomplishments, and completes the following exercises (note the primary foreign key relationship).
--Students: School Number (SNO), name (SNAME), Gender (SSEX), Birthday (Sbirthday), Class (Class)
CREATE DATABASE IF not EXISTS sss;
Use SSS;
CREATE TABLE IF not EXISTS student (
ID TINYINT UNSIGNED auto_increment KEY,
Sno INT UNSIGNED not NULL,
Sname VARCHAR () not NULL,
Ssex ENUM (' Male ', ' female ', ' secret '),
Sbirthday DATE,
Class INT UNSIGNED not NULL
) ENGINE = INNODB;
--Course: Course Number (CNO), course name (CNAME), Instructor (TNO)
CREATE TABLE IF not EXISTS course (
ID TINYINT UNSIGNED auto_increment KEY,
CNO INT UNSIGNED UNIQUE not NULL,
CNAME VARCHAR (a) UNIQUE not NULL,
TNO VARCHAR () not NULL
) ENGINE = INNODB;
--Grades: Study number (SNO), course number (CNO), score (degree)
CREATE TABLE IF not EXISTS score (
ID TINYINT UNSIGNED auto_increment KEY,
Sno INT UNSIGNED UNIQUE not NULL,
CNO INT UNSIGNED not NULL,
Degree INT UNSIGNED
);
--Teacher: Teacher Number (TNO), teacher name (Tname), Gender (tssex), Birthday (Tbirthday), title (title), Unit Department (depart)
CREATE TABLE IF not EXISTS teacher (
ID TINYINT UNSIGNED auto_increment KEY,
TNO INT UNSIGNED UNIQUE,
Tname VARCHAR () not NULL,
Tssex ENUM (' Male ', ' female ', ' secret '),
Tbirthday DATE,
Title VARCHAR (20),
Depart VARCHAR (40)
);
--Requirements:

--one, each table uses SQL statements to insert at least 10 data
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (1, 1011, ' Zhang San ', ' Male ', ' 1991-10-10 ', 95301);
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (2,1031, ' John Doe ', ' Male ', ' 1992-11-11 ', 95303);
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (3,1021, ' King II ', ' female ', ' 1991-10-10 ', 95302);
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (4,1012, ' Li Lei ', ' Male ', ' 1991-10-10 ', 95301);
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (5,1032, ' Han Meimei ', ' female ', ' 1991-10-10 ', 95303);
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (6,1022, ' Wang Ming ', ' female ', ' 1991-10-10 ', 95302);
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (7,1013, ' poplar ', ' male ', ' 1991-10-10 ', 95301);
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (8,1033, ' Bear eight ', ' Male ', ' 1991-10-10 ', 95303);
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (9,1023, ' Yang Yi ', ' female ', ' 1991-10-10 ', 95302);
INSERT Student (Id,sno,sname,ssex,sbirthday,class) VALUES (10,1014, ' Hu Wu ', ' female ', ' 1991-10-10 ', 95301);

INSERT Course (cno,cname,tno) VALUES (3105, ' Introduction to computers ', ' Zhang Xu ');
INSERT Course (cno,cname,tno) VALUES (3106, ' language ', ' Tao Xiong ');
INSERT Course (cno,cname,tno) VALUES (3107, ' math ', ' Cannes ');
INSERT Course (cno,cname,tno) VALUES (3108, ' English ', ' Wang Bo ');
INSERT Course (CNO,CNAME,TNO) VALUES (1101, ' Advanced math ', ' Li Chen ');
INSERT Course (CNO,CNAME,TNO) VALUES (1102, ' Communication principle ', ' Huo Si Yan ');
INSERT Course (CNO,CNAME,TNO) VALUES (1103, ' university physics ', ' Avatar ');
INSERT Course (cno,cname,tno) VALUES (2101, ' calculus ', ' Einstein ');
INSERT Course (cno,cname,tno) VALUES (2102, ' data structure ', ' jobs ');
INSERT Course (cno,cname,tno) VALUES (2103, ' Circuit theory ', ' Edison ');
INSERT Course (cno,cname,tno) VALUES (2104, ' electromagnetic induction ', ' Edison ');

INSERT score (Sno,cno,degree) VALUES (1011,3105,85);
INSERT score (Sno,cno,degree) VALUES (1031,3105,86);
INSERT score (Sno,cno,degree) VALUES (1021,3105,88);
INSERT score (Sno,cno,degree) VALUES (1012,2102,65);
INSERT score (Sno,cno,degree) VALUES (1032,2102,70);
INSERT score (Sno,cno,degree) VALUES (1022,2102,75);
INSERT score (Sno,cno,degree) VALUES (1013,3105,90);
INSERT score (Sno,cno,degree) VALUES (1033,3105,55);
INSERT score (Sno,cno,degree) VALUES (1023,2104,45);
INSERT score (Sno,cno,degree) VALUES (1014,2104,45);

INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (01, ' Zhang Xu ', ' Male ', ' 1991-10-09 ', ' Professor ', ' 110 ');
INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (02, ' Tao Xiong ', ' female ', ' 1991-10-09 ', ' Professor ', ' 110 ');
INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (03, ' Cannes ', ' Male ', ' 1991-10-09 ', ' Professor ', ' 110 ');
INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (04, ' Wang Bo ', ' female ', ' 1991-10-09 ', ' Bachelor ', ' 111 ');
INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (05, ' Li Chen ', ' Male ', ' 1991-10-09 ', ' Bachelor ', ' 111 ');
INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (06, ' Huo Si Yan ', ' female ', ' 1991-10-09 ', ' Bachelor ', ' 111 ');
INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (07, ' Avatar ', ' Male ', ' 1991-10-09 ', ' Bachelor ', ' 111 ');
INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (08, ' Einstein ', ' female ', ' 1991-10-09 ', ' Scientist ', ' 112 ');
INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (09, ' Jobs ', ' male ', ' 1991-10-09 ', ' Scientist ', ' 112 ');
INSERT Teacher (Tno,tname,tssex,tbirthday,title,depart) VALUES (010, ' Edison ', ' Male ', ' 1991-10-09 ', ' Scientist ', ' 112 ');

--Two, complete the following query title:
-1, query the sname, Ssex, and class columns of all records in the student table.
SELECT sname,ssex,class from student;
--2, the query teacher all units that are not duplicated depart column.
SELECT DISTINCT depart from teacher;
--3, query all records of the student table.
SELECT * from student;
--4. Query all records in the score table for scores between 60 and 80.
SELECT degree from score WHERE degree between and 80;
--5, query the records of the score table with a score of 85, 86, or 88.
SELECT * from score WHERE degree in (85,86,88);
--6, Query the student table in the "95301" class or sex for "female" students record.
Select * FROM student WHERE ssex= ' female ' OR class= "95301";
--7, queries all records of the student table in descending order of class.
SELECT * FROM student ORDER by class Desc;
--8, CNO in ascending order, degree in descending order to query all records of the score table.
SELECT * from score ORDER by CNO Asc,degree desc;
--9. Check the number of students in the "95031" class.
Select COUNT (*) from student WHERE class= "95301";
--10. Query the student number and course number of the highest score in the score table.
Select * FROM score WHERE degree= (select MAX (degree) from score);
--11, check the average of the course ' 3-105 '.
SELECT AVG (degree) from score WHERE cno=3105;
--12. Check the average score of the course with at least 5 students in the score table and start with 3.

SELECT AVG (degree) from score WHERE cno like ' 3% '
Having COUNT (*) >=5;
--13, sname, CNO and degree for all students.
SELECT S.sname,sc.cno,sc.degree
From student as S
INNER JOIN Score as SC
On S.sno=sc.sno;

--14. Check the average of the courses selected in the "95303" class.
SELECT AVG (Sc.degree) from score as SC
INNER JOIN student as S
On S.sno=sc.sno
WHERE s.class=95303;
--15, assume that a grade table is created using the following command:
CREATE table grade (Low Int,upp Int,rank char (1));
Insert into grade values (90,100, ' A ');
Insert into grade values (80,89, ' B ');
Insert into grade values (70,79, ' C ');
Insert into grade values (60,69, ' D ');
Insert into grade values (0,59, ' E ');
Commit
--Sno, CNO and rank columns are now available for all students.
Select Sno,cno,rank from Score,grade where degree between low and UPP;
--16, query "Zhang Xu" teachers teaching students results.
SELECT S.sname, Sc.degree
From student as S
INNER JOIN Score as SC
On S.sno=sc.sno
INNER JOIN Course as C
On Sc.cno=c.cno
WHERE c.tno= ' Zhang Xu ';

--17. The name of the teacher who has more than 5 students who have enrolled in a course.
SELECT C.tno from course as C
INNER JOIN Score as SC
On C.cno=sc.cno
Having COUNT (*) >=5;

--18. Check the name, sex and birthday of all teachers and classmates.
Select Tname,tssex,tbirthday from teacher UNION SELECT sname,ssex,sbirthday from student;
--20. Check the class number of at least 2 boys.
SELECT class from student WHERE ssex= ' man '
Having COUNT (*) >=2;
---21, query the student table not the surname "Wang" the classmate record.
SELECT * FROM student WHERE sname don't like ' Wang% ';
--22. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course.
SELECT S.sname,sc.degree,c.cname from score as SC
INNER JOIN Course as C
On Sc.cno=c.cno
INNER JOIN student as S
On Sc.sno=s.sno
WHERE c.cname= ' Computer Introduction ' and s.ssex= ' man ';

MySQL Common command instances