mysql search exercise

Build a table

CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);

CREATE TABLE teacher(
    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    t_birthday DATETIME COMMENT'教师生日',
    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);

CREATE TABLE course(
    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',
    FOREIGN KEY(t_no) references teacher(t_no)
);

CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
        c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_id',
    sc_degree decimal,
    foreign key(s_no) references student(s_no),
    foreign key(c_no) references course(c_no),
    PRIMARY KEY(s_no,c_no)
);

Insert data

INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');


INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');

INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

Query
1. Query all units of the teacher but the unique t_depart column
distinct() function
select distinct(t_depart) from teacher;

**2.** Query all records with scores between 60 and 80 in the score table (sc_degree)
Note: BETWEEN... ADN... is the
select * from score where sc_degree between 60 and 80;

3. Query records with scores of 85, 86, or 88 in the score table (sc_degree)
select * from score where sc_degree=85 or sc_degree=86 or sc_degree=88;

in ()范围
select * from score where sc_degree=85 or sc_degree in(85,86,88);

4. Query all records in the student table in descending order of class
select * from student order by s_class DESC

5. Interpolate all the data in the score table in ascending order with c_no. Sc_degree in descending order. First, in ascending order
with c_no, if c_no is the same, in descending order with sc_degree

SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;

6. Query the number of students in the '95031' class
select count(s_no) from student where s_class=95031;
Note that if a null appears here, count cannot be found

select count(1) from student where s_class=95031;
select count(*) from student where s_class=95031;
可以无视null

The difference between count(*) and count(1) and count(column name) In terms of
execution effect:

count(*) includes all the columns, which is equivalent to the number of rows. When counting the results, the column value is not ignored.
count(1) includes ignoring all columns. 1 represents the code line. When counting the results, the column value is not ignored.
The count (column name) only includes the column name. When counting the results, the column value will be ignored (the empty string here is not just an empty string or 0, but represents a null) count, that is, a certain field value When NULL, no statistics.
In terms of execution efficiency:

The column name is the primary key, and count (column name) is faster than count(1).
The column name is not the primary key, count(1) will be faster than count(column name).
If the table has multiple columns and no primary key, the execution efficiency of count(1) is better than count( ).
If there is a primary key, the execution efficiency of select count (primary key) is optimal.
If the table has only one field, select count( ) is the best.

7. Query the student ID and course ID of the highest score in the score table. (Subquery or sort) It is difficult! ! ! !
Subquery
first check the highest score
select max(sc_degree) from score;
then check
select * from score where sc_degree=(select max(sc_degree) from score);

Sorting
Note that when there are multiple highest scores, there may be data problems
limit x, y (x: indicates starting from X data y: how many
items need to be found) select * from score order by sc_degree desc limit 0,1;

8. Query the average score of each course !!!
avg()
group by
select c_no, avg(sc_degree) from score group by c_no;

9. Querying the score table has at least 2 students elective, and the average score of courses starting with 3
can let us filter various data after grouping

 select avg(sc_degree) from score group by c_no having count(c_no)>3 where c_no like "%3";

Wrong
select avg(sc_degree) from score group by c_no having count(c_no)>3 This part is correct

select avg(sc_degree) from score group by c_no having count(c_no)>=2 and c_no like “3%”; just add the following part

9.Query the s_no column with a score greater than 70 but less than 90:
select s_no from score where sc_degree between 71 and 89;

10. Query all the students s_name, c_no, sc_degree column.
First, this contains two tables score and student, using joint check, Cartesian product principle

select s_name , c_no, sc_degree from score ,student where student.s_no =score.s_no;

11. Query the s_no, c_name, sc_degree columns of all students

select   s_no, c_name,sc_degree from course,student,score  where student.s_no=scroe.s_no and score.c_no =course.c_no ;

Error: 1052-Column's_no' in field list is ambiguous
because both student and score have s_no
changed to student.s_no

select student.s_no, c_name,sc_degree from student,course,score where student.s_no=score

12. The query class is the average score of each class of the students of the '95031' class
12.1 I think it is
first to check the class is the score of the 95031 students, pay attention to this sentence to find out is a table

select *  from score where s_no in (select s_no from student where s_class='95031');

In nested query

select avg(sc_degree) from (select *  from score where s_no in (select s_no from student where s_class='95031')) group by c_no;

Every derived table must have its own alias (each derived table must have its own alias) error

(select * from score where s_no in (select s_no from student where s_class='95031')) as a Give the derived table an alias of a

select avg(sc_degree) from
(select * from score where s_no in (select s_no from student where s_class=‘95031’ ) ) as a group by c_no;

12.2 Joint query
Combine student and score, find the student whose s_class is 95031, and then find the same
s_no SELECT sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC WHERE s.s_class = '95031' AND s.s_no = sc.s_no GROUP BY sc.c_no;

12.3 Left connection

select c_no,AVG(sc_degree) from score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = ‘95031’ GROUP BY sc.c_no;

12.4 进阶 加入课程名称
SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c WHERE s.s_class = ‘95031’ AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ;

  1. Query the records of all students whose scores of the elective course "3-105" are higher than the scores of classmates No. '109', '3-105'
    select s_no from score where c_no='3-105' and sc_degree >(select sc_degree from score where c_no ='3-105'and s_no='109'); Can't use it? ? ?

select s_no from score where c_no=‘3-105’ and sc_degree >(select sc_degree from score where c_no='3-105’and s_no=‘109’
);

14. Query the records of all students whose scores of the elective course "3-105" are higher than the scores of classmates '109', '3-105'
select * from student ,course, score where student.s_no in (select s_no from score where c_no ='3-105' and sc_degree >(select sc_degree from score where c_no='3-105'and s_no='109')) and student.s_no=score.s_no and course.c_no=score.c_no and sco
re. c_no='3-105';

Display
±-----±-------±------±--------------------±------ --±------±-----------±-----±-----±------±--------- -+
| s_no | s_name | s_sex | s_birthday | s_class | c_no | c_name | t_no | s_no | c_no | sc_degree |
±-----±-------±------±--- -----------------±--------±------±-----------±---- -±-----±------±----------+
| 103 | Wang Li | Female | 1976-01-23 00:00:00 | 95033 | 3-105 | Introduction to Computers | 825 | 103 | 3-105 | 92 |
| 105 | Wang Fang | Female | 1975-02-10 00:00:00 | 95031 | 3-105 | Introduction to Computers | 825 | 105 | 3-105 | 88 |
±-----±-------±------±--------------------±----- ---±------±-----------±-----±-----±------±-------- --+

There are 2 c_no and 2 s_no, I don’t know how to deal with it.

15. Query the s_no, s_name and s_birthday of all students born in the same year for all students whose student ID is 108.101

Note that this question is born in the same year.
Use the year() function to
find out the year of the date type .
Select year(s_birthday) from student where s_no in (108,101);

Nested

select s_no,s_name,s_birthday from student where year(s_birthday) in (select year(s_birthday) from student where s_no in  (108,101)

16. Query the name of the teacher with
more than 5 students in the elective course. Find the number of the course with more than 2 students
select c_no from score group by c_no having count(c_no)>=3;

Use the course table to find the teacher number
from course where c_no in(select c_no from score group by c_no having count(c_no)>=3);

17 Query the t_name and t_rof (title) of teachers with different titles in the'Computer Department' and'Electronic Engineering Department'
select t_rof from teacher where depart = "Computer Department";
select t_rof from teacher where depart = "Electronic Engineering Department";

select * from teacher where t_depart = "Computer Department" and t_rof not in (select t_rof from teacher where t_depart = "Electronic Engineering Department");

select * from teacher where t_depart = "Electronic Engineering Department" and t_rof not in (select t_rof from teacher where t_depart = "Computer Department" );

select * from teacher where t_depart ="计算机系" and t_rof not in (select t_rof from teacher where t_depart ="电子工程系")
union
select * from teacher where t_depart ="电子工程系" and t_rof not in (select t_rof from teacher where t_depart ="计算机系" );

18 . Elective query number "3-105" course and score at least higher than elective numbered '3-245' students
of c_no, s_no and sc_degree, and in accordance with sc_degree ordered from high order to the
above described at least == greater than wherein Any one == greater than the minimum value,
at least use any()

select * from score where c_no=“3-105”;

select * from score where c_no="3-105" and sc_degree > any(select sc_degree from score where c_no="3-245") order by sc_degree desc;

19. Query c_no.s_no and sc_degree for students whose elective number is "3-105" and whose grades are higher than the elective number "3-245"

Students whose grades are higher than the elective course number "3-245" = greater than all of the 3-245

select * from score where c_no="3-105" and sc_degree > all(select sc_degree from score where c_no="3-245");

Summary: ANY and ALL
ANY: means any one is fine, for example; the value in array A is greater than any one in array B, then as long as the smallest comparison in A and B is sufficient.
ALL: means that all must be compared, For example, if the value in array A is larger than all the numbers in array B, then A must be compared with the largest value in B.

20. Query the name, sex, birthday of all teachers and classmates. The
key point is to display them in the same table, using the as alias

select s_no,s_name,s_sex from student
union
select t_no as s_no , t_name as s_name,t_sex as s_sex
 from teacher;

21. Query the transcript of students whose grades are lower than the average grade of the course.
My writing

select score.c_no,
sc_degree from score ,(select c_no, avg(sc_degree) as sc from score group by c_no) as a where score.c_no=a.c_no and score.sc_degree < a.sc;

Tutorial answer!!!

SELECT * FROM score AS sc1 WHERE sc1.sc_degree < (SELECT  AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no);

This sql can be seen as a two-layer for loop,
traverse sc1 and
traverse sc2 to
find sc1.sc_degree<AVG(sc_degree)

22. Find out the class number of at least 2 boys!!!
Can’t write it out at first

select s_class from student where s_sex="男" group by class having count(*) >1; 

The execution process is to load the table from student to memory, where to filter, group by to divide the filtered table, execute select s_class once for each subtable, and then have, and then summarize the result.

23. Query the records of students whose surname is not "王" in the student table
SELECT * FROM student WHERE s_name NOT LIKE'王%';

  1. Query the name and age of each student in the student (current time-year of birth)
    SELECT s_name, YEAR(NOW())-YEAR(s_birthday) AS age FROM student;

25. Query the s_no, c_no and grade columns of all students.
Suppose a table is built

CREATE TABLE grade(
    low INT(3),
    upp INT(3),
    grade 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');

SELECT s_no, c_no , grade FROM score, grade WHERE sc_degree BETWEEN low and upp;