As the length of the last article is too long, I'll practice it alone.
o by the way, it's better to use single quotation marks for the characters in mysql, otherwise an error may occur!

Textbook exercises

Suppose there is a database of students' course selection, which contains three relational patterns:

student

sno	sname	sex	birth	specialty
20101140101	Zhang San	male	1992-06-01	CS
20101140102	Li Si	female	1991-09-23	IS
20101140103	Wang Wu	male	1992-05-04	CS
20101140104	Li Yuanyuan	male	1992-08-09	MI

course

cno	cname	credit	category
001	C programming	5	Compulsory
002	Database system theory	4	Compulsory
003	ORACLE Foundation	3	Elective course

sc

sno	cno	grade
20101140101	001	85
20101140101	002	78
20101140101	003	90
20101140102	001	87
20101140102	002	89

Complete the following exercises:
1) Complete the definition of the three tables of the above database with SQL language, including main code, external code and user-defined constraints
2) Complete the record entry of each form
3) Query the basic information of students born in 1992
4) Query students surnamed "Li"
5) Query the student number and name of students not majoring in "CS" and "MA"
6) Query the average score of "001" course
7) Query the student number and name of the oldest student
8) Query the average score of each student's selected course
9) Query the information of boys majoring in "CS"
10) Query and "Wang Wu" are using the basic information of one major (realized by natural connection query and nested query)
11) Query the course number of each student exceeding his average score of elective courses
12) Query each student and their elective courses (it is required to use the left outer connection)
13) Query the student number of students who have taken at least "001" and "002"
14) Change the name of "002" course to "principle and application of database system"
15) Delete the course selection record of "Li Si"
16) Assuming that "Wang Wu" has taken all three courses, insert the student number of "Wang Wu" and the three course numbers ("001", "002", "003") into the SC table at one time
17) Create a view of student course selection_ C. Including student number, name, course number, course name and grade
18) In view s_ On the basis of C, query the course selection of students "Zhang San"

1) First, we build the table by, for example, the following code
2) Data entry OK

3)
Analysis: if you want to query the name of a person born in 1992, we can extract some information from this sentence: ① you need the student.birth column as the query basis; ② you need the data of the whole student table. So we found that the two required information are in the same table. Isn't that easy?

select *
from study.student
where student.birth like '1992%';

So we can query correctly!

4)
Analysis: query the students whose surname is "Li". It is similar to the previous question, but we can only output the column student.sname instead

select student.sname
from student
where student.sname like 'Lee%';

5)
Analysis: it's easy to query the student number and name of students who are not majoring in "CS" and "MA". Just change the where condition to not like and

select student.sno,student.sname
from student
where student.specialty not like 'CS' or 'MA';

Of course, you can also write that, because it's a logical problem

select student.sno,student.sname
from student
where (student.specialty not like 'CS') and (student.specialty not like 'MA');

select student.sno,student.sname
from student
where student.specialty not in ('CS','MA');

The answer remains the same

6)
be careful! be careful! If you use a foreign key, the data owned by the primary key can be referenced by the foreign key. If you add a foreign key casually, the data that does not exist in the primary key is invalid!
It's a little difficult to query the average score of "001" course, because it's going to call the function. First of all, let's analyze: we need to perform AVG operation on the scores of students selected for course 001, and then see what to output: ① select the row with the median value of sc.cno equal to '001', ② perform AVG calculation on the values of sc.grade column in the same table of these rows, and then output

select avg(sc.grade)
from sc
where sc.cno='001';

You can also use an alias to look better

7)
To query the student number and name of the oldest student, you need to use the aggregation function. Ideas: ① find the smallest student in student.birth; ② query and output his student number and name
Because the aggregation function can only be written in select and group, and we don't want to output this column, we use nested query to implement it

select student.sno, student.sname
from student
where student.birth = (
    select min(student.birth)
    from student
);

8)
Query the average score of each student's selected course, indicating that the output is sc.sno and sc.grade. Therefore, you can ① group sc.sno first, ② avg the sc.grade of each group, and ③ splice the final result with sc.sno

select sc.sno, avg(sc.grade) avg
from sc
group by sc.sno
having avg(sc.grade);

9)
To query the information of male students majoring in "CS", select CS in student.specialty and student.sex is male, and then output all the information in their table

select *
from student
where student.specialty='CS' and student.sex='male';

10)
Query the basic information of Wang Wu's study in the same major (realized by natural connection query and nested query), and select the row equal to Wang Wu's specialty,

Natural connection

nested queries

select distinct *
from student
where student.sname != 'Wang Wu'
  and student.specialty = (
    select student.specialty
    from student
    where student.sname = 'Wang Wu'
);

11)
Query the course number of each student exceeding the average score of his elective course, ① output the student number and course number ② sc.grade > AVG (sc.grade) ③
Typical self connection + nesting

select distinct first.sno, first.cno
from sc first
where first.grade >= (
    select avg(second.grade)
    from sc second
    where second.sno = first.sno
)

12)
Query each student and their elective courses (it is required to be implemented with the left outer connection) to a left join without a where statement

select distinct student.*,sc.cno
from student left join sc on student.sno = sc.sno;

13)
Query the student number of students who have taken at least "001" and "002" courses, at least 001002. The student number needs to be output, and two specified conditions need to be set by existence and union functions

select x.sno
from sc x
group by x.sno
having count(x.cno) >= 2
and exists(
    select y.sno
    from sc y
    where y.cno='001'
    union
    select y.sno
    from sc y
    where y.cno='002'
    )

14)
Change the name of "002" course to "principle and application of database system", and use the update function

UPDATE study.course t SET t.cname = 'Principle and application of database system' WHERE t.cno = '002';

15)
Delete the course selection record of "Li Si". Well, we can also delete nested query statements

delete from sc where sc.sno=(
    select student.sno
    from student
    where sname='Li Si'
    );

16)
Assuming that "Wang Wu" takes all three courses, insert the student number of "Wang Wu" and the three course numbers ("001", "002", "003") into the SC table at one time, separated by commas

insert into study.sc
values ('20101140103', '001', null),
       ('20101140103', '002', null),
       ('20101140103', '003', null);

17)
Create a view of student course selection_ C. Including student number, name, course number, course name and grade

create view study.S_C as
select student.sno, student.sname, sc.cno, course.cname, sc.grade
from sc,
     student,
     course
where student.sno=sc.sno and sc.cno=course.cno;

18)
In view s_ On the basis of C, query the course selection of students "Zhang San"

select s_c.sname,s_c.cname
from s_c
where s_c.sname='Zhang San';