Comprehensive practice of MySQL

Time:2020-8-11

Title Description

There is a database with four tables in it

The table structure is as follows:

Table 1_ Student form

Property name data type meaning
sno int Student number (primary key)
sname varchar Student name
ssex varchar Student gender
sbirthday date Date of birth
classid varchar Students’ class

 

Table 2_ Course

 

Property name data type meaning
cno varchar Course number (primary key)
cname varchar Course name
tno int Teacher number (foreign key)

Table 3_ Score

Property name data type meaning
sno int Student number (foreign key)
cno varchar Course number (foreign key)
sdegree float achievement

Table 4_ Teacher list

Property name data type meaning
tno int Number (primary key)
tname varchar Teacher’s name
tsex varchar Teacher gender
tbirthday date Teacher’s date of birth
professional varchar title
department varchar Teacher’s Department

2、 Create databases and tables

First create the database mysql_ Test, and set the encoding character set of this database to UTF-8.

 

Student table:

create table student(
    sno  int(10),
    sname varchar(10),
    ssex varchar(10),
    sbirthday date,
    classid varchar(10)
);

alter table student add primary key(sno);

 

Teacher table:

create table teacher(
    tno int(10),
    tname varchar(12),
    tsex varchar(10),
    tbirthday date,
    professional varchar(20),
    department varchar(20)
);

alter table teacher add primary key(tno);

 

Class Schedule Card:

create table course(
    cno varchar(10),
    cname varchar(20),
    tno int(10)
);

alter table course add primary key(cno);
alter table course add constraint fk_course_teacher foreign key(tno) references teacher(tno); 

 

Results:

create table score(
    sno int(10),
    cno varchar(10),
    sdegree float(5)
);

alter table score add constraint fk_score_student foreign key(sno) references student(sno);
alter table score add constraint fk_score_course foreign key(cno) references course(cno);

 

3、 Add data

insert into student (sno,sname,ssex,sbirthday,classid) 
Values (108, 'Zeng Hua', 'male', '1996-9-1','95033 '), (105,' Kuangming ',' male ',' 1995-10-2 ','95031'),
(107, 'Wang Li', 'female', '1996-1-23','95033 '), (101,' Li Jun ',' male ',' 1996-2-20 ','95033'),
(109, 'Wang Fang', 'female', '1995-2-10','95031 '), (103,' Lu Jun ',' male ',' 1994-6-3 ','95031');


insert into teacher(tno,tname,tsex,tbirthday,professional,department) 
Values (804, 'Li Cheng', 'male', '1978-12-2', 'associate professor', 'computer department'),
(856, 'Zhang Xu', 'male', '1979-3-12', 'lecturer', 'Department of electronic engineering'),
(825, 'Wang Ping', 'female', 'May 5, 1982', 'teaching assistant', 'computer department'),
(831, 'Liu Bing', 'female', '1984-8-14', 'teaching assistant', 'Department of electronic engineering');


insert into course(cno,cname,tno) 
Values ('3-105 ','introduction to computer', 825), ('3-245 ','operating system', 804),
('6-166 ','digital circuit', 856), ('9-888 ','advanced mathematics', 831);


insert into score(sno,cno,sdegree) values
(103,'3-245',86),
(105,'3-245',75),
(109,'3-245',68),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'3-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-166',79),
(108,'6-166',81);

Student table:

 

Teacher table:

 

Class Schedule Card:

 

 

Results:

4、 SQL topic

1. Query the names, gender and class numbers of all students

 

 

2. Query all the Department names of teachers (i.e. non duplicate Department column)

3. Query all records of student table

 

4. Query all records with scores between 60 and 80 in the score table

 

5. Query the score of 85, 86 or 88 in the score table

 

6. Query the record of students whose class number is “95031” or gender is “female”

 

7. Query all the records in the student table and arrange them in descending order of class number

 

8. Query all the records in the score table. First, they are sorted in the ascending order of CNO. If they are the same, they are arranged in descending order of sdegree

 

9. Query the number of students in class number “95031”

 

10. Query the student number and course number with the highest score in the score table

 

11. Query the average score of the course “3-105”

 

12. Query the average score of courses with at least 5 students taking courses starting with 3 in the score table

 

13. Query student numbers with scores between 70 and 90 in each subject

Note: having can only be followed by aggregate functions, such as AVG, min, Max, sum, etc

14. Query all students’ names, course numbers and grades

 

 

15. Query the student number, course name and grade of all students

 

 

16. Query the names of all students, the selected course name and grades

 

 

17. Query the average score of each course selected by “95033” students

 

 

18. Suppose you create a grade table using the following command:

CREATE TABLE GRADE(
LOW INT(3),
UPP INT(3),
RANK VARCHAR(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’);

– query the student number, course number and grade of all students (rank column)

 

19. Query the records of all students whose grades of “3-105” are higher than those of 109 students

 

20. For all students born after 1995, please check the course names and grades

 

 

 

21. Query the student numbers, names and birthdays of all the students born in the same year as the student number 108

 

 

22. Inquire about the student performance of “Zhang Xu” teacher

 

 

23. Query the names of teachers with more than 5 students taking a course

 

 

24. Query the records of all students in “95033” class and “95031” class

 

 

25. Query the course number with more than 85 scores

 

 

26. Find out the average score of each subject taught by “computer department” teachers

 

 

27. Query the names and titles of teachers with different professional titles in computer department and electronic engineering department

 

 

28. Query the names, genders and birthdays of all teachers and students

 

 

29. Query the names, genders and birthdays of all female teachers and students

 

 

30. Query the names and departments of all teachers

 

 

31. Query the names and departments of all teachers who have not lectured

 

 

32. Query the records of students who do not have the surname “Wang” in the student table

 

 

33. Query the name and age of each student in the student table

 

 

34. Query the maximum and minimum birthday values in the student table

 

 

35. Query all the records in the student table and arrange them in descending order according to the class number. If the class numbers are the same, they are arranged in descending order according to the birthday

 

 

36. Query all “male” teachers and their course names

 

 

37. Inquire about the information of the same-sex classmates of Li Jun

 

 

38. Query the scores of all male students who take the course of introduction to computer

 

 

39. Find out the student information of the courses with “3-245” and “3-105” at the same time

 

 

 

40. Query the information of students who have neither taken “3-245” course nor “3-105” course