Personal home page ： Personal home page
​  Series column ：MySQL database

ha-ha , This is our teacher's assignment , I was thinking about whether to blog or not ？

Finally, I thought about , Let's send it out , Although it's very simple , But you can practice it for those who have just learned database . Because there is no answer , I don't know , If a big man finds a mistake , Please point out .

  subject ：

1. Check the student number of each student 、 Class and name
2. Query all the information of the course
3. Query the professional classes in the database
4. Query class hours greater than 60 Course information for
5. Check the date of birth 1986 The student number of the student born in 、 Name and date of birth
6. The results of three inquiries are in 80 Student numbers with scores above 、 Course no.
7. Check the student number of the student surnamed Zhang 、 Name and major class
8. Inquire about 05 Class boy information

9. Query the student number and course number without homework score  

10. The student ID is 0538 Students' homework 1 Total score

11. Check the elective course K001 Number of students in the course  

12. How many databases are there in the class

13. Query more than three elective courses （ contain 3 door ) The student number and homework of the students in the course 1 average , Homework 2 average , Homework 3  average
     

 

 

 

 

 

If the partners are 0 Basics It doesn't matter , Take a look at this blog    Then I'll do the question .

 2022 newest MySQL Basics （ speed one's pen 4w word Two eyes, Bo Jun ）_ A little white blog who loves programming -CSDN Blog

1. Create database

create database  if not exists  db2 ;

 

Well, it was created successfully , Then we open the console  

 

2. Create table

1. Create student table

analysis ：

Student number ： Character

full name ： Character

Gender ： Character Gender is a word therefore varchar(1)

Professional class ： Character

Date of birth ： Time type date 

contact number ： Character     Phone number 11 position   varchar(11) that will do .

 

 

drop table if exists student;
create table student
(
    id     varchar(10) comment ' Student number ',
    name   varchar(10) NOT NULL comment ' full name ',
    gender char(1) comment ' Gender ',
    class  varchar(20) comment ' Professional class ',
    date   date comment ' Date of birth ',
    iphone varchar(11) comment ' contact number '
)
    comment ' Student list ';

select * from student;

 

2. Create a curriculum

 

 

drop table if exists student_course;
create table student_course
(
    course_id     varchar(10)  comment ' Course no. ',
    course_name   varchar(15) comment ' Course name ',
    course_number double unsigned comment ' Grades ',
    student_time  int unsigned comment ' Class hours ',
    teacher       varchar(10) comment ' The teacher '
)
    comment ' The curriculum ';
select *
from student_course;

 

3. Student homework sheet

 

drop table if exists student_homework;
create table student_homework
(
    course_id  varchar(10) comment ' Course no. ',
    id      varchar(10)    comment ' Student number ',
    homework_1 int comment ' Homework 1 achievement ',
    homework_2 int comment ' Homework 2 achievement ',
    homework_3 int comment ' Homework 3 achievement '

)
    comment ' Student homework sheet ';
select *
from student_homework;

 

3. Add data

Print out one by one according to the data on the picture , Purring

 1. Student list

insert into student
values ('0433', ' Zhang Yan ', ' Woman ', ' biological 04', '1986-9-13', null),
       ('0496', ' Li Yue ', ' male ', ' Electronics 04', '1984-2-23', '1381290xxxx'),
       ('0529', ' Zhao Xin ', ' male ', ' accounting 05', '1984-1-27', '1350222xxxx'),
       ('0531', ' Zhang Zhiguo ', ' male ', ' biological 05', '1986-9-10', '1331256xxxx'),
       ('0538', ' Yu Lanlan ', ' Woman ', ' biological 05', '1984-2-20', '1331200xxxx'),
       ('0591', ' Wang Lili ', ' Woman ', ' Electronics 05', '1984-3-20', '1332080xxxx'),
       ('0592', ' Wang Haiqiang ', ' male ', ' Electronics 05', '1986-11-1', null);

  Check it out. ：

select * from student;

 

 

2. The curriculum

 

INSERT INTO student_course
values ('K001', ' Computer graphics ', 2.5, 40, ' Hu Jingjing '),
       ('K002', ' Fundamentals of computer application ', 3, 48, ' Quan Ren '),
       ('K006', ' data structure ', 4, 64, ' Ma Yuexian '),
       ('M001', ' Political economy ', 4, 64, ' Kong fanxin '),
       ('S001', ' Advanced mathematics ', 3, 48, ' Zhao Xiaochen ');

Check it out. ：

select *
from student_course;

 

3. Student homework sheet

 

 

insert into student_homework values
('K001','0433',60,75,75),
('K001','0529',70,70,60),
('K001','0531',70,80,80),
('K001','0591',80,90,90),
('K002','0496',80,80,90),
('K002','0529',70,70,85),
('K002','0531',80,80,80),
('K002','0538',65,75,85),
('K002','0592',75,85,85),
('K006','0531',80,80,90),
('K006','0591',80,80,80),
('M001','0496',70,70,80),
('M001','0591',65,75,75),
('S001','0531',80,80,80),
('S001','0538',60,null,80);

Check it out. ：

select *
from student_homework;

 

 

4. Start to work on the questions

1. Check the student number of each student 、 Class and name

 

select id,class,name from student;

 

2. Query all the information of the course

 

select *
from student_course;

 

 

3. Query the professional classes in the database

 

select  class from student;

 4. Query class hours greater than 60 Course information for

select course_id,course_name from student_course where student_time>60;

 

 5. Check the date of birth 1986 The student number of the student born in 、 Name and date of birth

select id,name,date from student where date>=('1986-1-1') AND date<('1987-1-1');

 

 

 

 6. The results of three inquiries are in 80 Student numbers with scores above 、 Course no.

  At first, I used this to query ：

select * from student_homework where homework_1>80 and homework_2>80 and homework_3>80;

Found nothing , So I looked at the data   It is found that the results of three assignments are 80 Score more than There is no such data

Therefore, this topic ：“ The results of three inquiries are in 80 Student numbers with scores above 、 Course no. ” should Include 80 branch

therefore , I'll change it ：

select * from student_homework where homework_1>=80 and homework_2>=80 and homework_3>=80;

 

 

7. Check the student number of the student surnamed Zhang 、 Name and major class

  Error model ：

Because I haven't written for hundreds of years SQL 了 , I wrote （ Give yourself a slap ）：

select id,name,class from  student where name = ' Zhang %';

select id,name,class from  student where name like ' Zhang %';

 

 ​​​​​​​

 

 8. Inquire about 05 Class boy information

 

select * from student where class like '%05' and gender=' male ';

 

9. Query the student number and course number without homework score  

 

select id,course_id from student_homework where homework_1 is null or homework_2 is null or homework_3 is null ;

 ​​​​​​​

 

 

 10. The student ID is 0538 Students' homework 1 Total score

 

select sum(homework_1) ' Total score ' from student_homework where id='0538';

11. Check the elective course K001 Number of students in the course  

 

select count(*) from student_homework where course_id='K001';

 

12. How many databases are there in the class

 

select count(*) from student where class is not null ;

 

13. Query more than three elective courses （ contain 3 door ) The student number and homework of the students in the course 1 average , Homework 2 average , Homework 3  average

select student.id, avg(homework_1), avg(homework_2), avg(homework_3)
from student
         left join student_homework on student.id = student_homework.id
group by student.id
having count(course_id) >= 3;