MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

Time:2022-5-3

 MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)Personal homepage:Personal home page
MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)Series column:Mysql database

Ha ha, this is our teacher’s assignment. I wonder if I need to blog?

Finally, I thought about it and sent it out. Although it’s very simple, it can be practiced for those friends who have just learned database.Because there is no answer, I don’t know whether it’s right or not. If someone finds a mistake, please point it out.

Title:

  1. Check the student number, class and name of each student
  2. Query all the information of the course
  3. Which professional classes are there in the query database
  4. Query course information with class hours greater than 60
  5. Query the student number, name and date of birth of students born in 1986
  6. Query the student number and course number whose homework scores are more than 80 for three times
  7. Check the student number, name and professional class of the student surnamed Zhang
  8. Query the information of grade 05 boys
  9. Query the student number and course number without homework score

  10. Query the total score of homework 1 of students with student number 0538

  11. Query the number of students who have taken k001 course

  12. How many classes are there in the query database

  13. Query the student number and average score of homework 1, average score of homework 2 and average score of homework 3 # of students taking more than three courses

     

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter) MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

 

 

It doesn’t matter if the partners are 0 basic. Take a look at the following blog} and then do the question again.

2022 latest MySQL Foundation (write 4W words quickly for Bojun only)_ A little white blog who loves programming – blog

1. Create database

create database  if not exists  db2 ;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

OK, the creation is successful, and then we open the console

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter) 

2. Create table

1. Create student table

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

analysis:

Student number: character type

Name: character type

Gender: character gender is a word, so varchar (1)

Professional class: character type

Date of birth: time type date

Contact number: character type , 11 digits , varchar (11) is enough.

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

drop table if exists student;
create table student
(
    ID varchar (10) comment 'student number',
    Name (VaR name) not ',
    Gender (char) 1 ',
    Class varchar (20) comment 'professional class',
    Date date comment 'date of birth',
    IPhone varchar (11) comment 'contact number'
)
    Comment 'student form';

select * from student;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

2. Create Curriculum

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

 

drop table if exists student_course;
create table student_course
(
    course_ ID varchar (10) comment 'course number',
    course_ Name varchar (15) comment 'course name',
    course_ Number double unsigned comment 'academic score',
    student_ Time int unsigned comment 'class hours',
    Teacher '10
)
    Comment 'curriculum';
select *
from student_course;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

3. Student homework form

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

drop table if exists student_homework;
create table student_homework
(
    course_ ID varchar (10) comment 'course number',
    ID varchar (10) comment 'student number',
    homework_ 1 int comment 'Assignment 1 score',
    homework_ 2 int comment 'homework 2 grade',
    homework_ 3 int comment 'homework grade'

)
    Comment 'student homework sheet';
select *
from student_homework;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

3. Add data

According to the data on the picture, it’s typed one by one, woo woo

1. Student formMySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

insert into student
Values ('0433 ',' Zhang Yan ',' female ',' bio 04 ',' 1986-9-13 ', null),
       ('0496', 'Li Yue', 'male', 'electronic 04', '1984-2-23', '138129xxxx'),
       ('0529', 'Zhao Xin', 'male', 'accountant 05', '1984-1-27', '1350222xxxx'),
       ('0531', 'Zhang Zhiguo', 'male', 'biological 05', 'September 10, 1986', '1331256xxxx'),
       ('0538', 'Yu Lanlan', 'female', 'bio 05', '1984-2-20', '1331200xxx'),
       ('0591', 'Wang Lili', 'female', 'electronic 05', '1984-3-20', '1332080xxx'),
       ('0592', 'Wang Haiqiang', 'male', 'electronic 05', '1986-11-1', null);

Check:

select * from student;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

 

2. Curriculum

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

INSERT INTO student_course
Values ('k001 ',' computer graphics', 2.5, 40, 'Hu Jingjing'),
       ('K002', 'Fundamentals of computer application', 3, 48, 'Renquan'),
       ('K006', 'data structure', 4, 64, 'ma Yuexian'),
       ('M001', 'political economy', 4, 64, 'Kong fanxin'),
       ('s001 ',' Advanced Mathematics', 3, 48, 'Zhao Xiaochen');

Check:

select *
from student_course;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

3. Student homework form

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter) 

 

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:

select *
from student_homework;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

 

4. Start doing questions

1. Inquire the student number, class and name of each student

 

select id,class,name from student;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

2. Query all the information of the course

 

select *
from student_course;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

 

3. Query the professional classes in the database

 

select  class from student;

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

4. Query course information with class hours greater than 60

select course_id,course_name from student_course where student_time>60;

 MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

5. Inquire the student number, name and date of birth of students born in 1986

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

 MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

 

6. Query the student number and course number whose homework scores are more than 80 for three times

At first, I used this query:

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

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

I found that there was nothing, so I looked at the data and found that there was no such data for those who met the three homework scores of more than 80 points

Therefore, the title: “query the student number and course number whose homework scores are more than 80 points for three times” should beIncluding 80 points

So I modified it:

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

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

 

7. Inquire the student number, name and professional class of the student surnamed Zhang

Error demonstration:

Since I haven’t written SQL for hundreds of years, I wrote (slap myself):

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

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

 

 ​​​​​​​MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

8. Query the information of grade 05 boys

 

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

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

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

 

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

 ​​​​​​​MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

 

10. Query the total score of homework 1 of students with student number 0538

 

Select sum (home_1) 'total score' from student_ homework where id='0538';

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

11. Query the number of students who have taken k001 course

 

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

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

12. How many classes are there in the query database

 

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

MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

13. Query the student number and average score of homework 1, average score of homework 2 and average score of homework 3 # of students taking more than three courses

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;

 MySQL small exercise (only suitable for beginners, non beginners are not allowed to enter)

 

Recommended Today

A front-end developer's Vim is the same as an IDE

Here is my new configurationjaywcjlove/vim-webI've been grinding it, and it's basically ready to use. Take it out and cheat the star Install The latest version of Vim 7.4+ uses (brew install macvim) installation, vim version updatebrew install macvim –override-system-vim View configuration locations # Enter vim and enter the following characters :echo $MYVIMRC download vim-web Download […]