Shulou(Shulou.com)06/03 Report--

Tabular data

Select * from course

+-+

| | course.c_id | course.c_name | course.t_id | |

+-+

| | 01 | language | 02 | |

| | 02 | Mathematics | 01 |

| | 03 | English | 03 |

+-+

Select * from score

+-+

| | score.s_id | score.c_id | score.s_score | |

+-+

| | 01 | 01 | 80 | |

| | 01 | 02 | 90 | |

| | 01 | 03 | 99 | |

| | 02 | 01 | 70 | |

| | 02 | 02 | 60 | |

| | 02 | 03 | 80 | |

| | 03 | 01 | 80 | |

| | 03 | 02 | 80 | |

| | 03 | 03 | 80 | |

| | 04 | 01 | 50 | |

| | 04 | 02 | 30 |

| | 04 | 03 | 20 | |

| | 05 | 01 | 76 | |

| | 05 | 02 | 87 | |

| | 06 | 01 | 31 |

| | 06 | 03 | 34 | |

| | 07 | 02 | 89 | |

| | 07 | 03 | 98 | |

+-+

Select * from student

+-+

| | student.s_id | student.s_name | student.s_birth | student.s_sex | |

+-+

| | 01 | Zhao Lei | 1990-01-01 | male |

| | 02 | Qian Dian | 1990-12-21 | male |

| | 03 | Sun Feng | 1990-05-20 | male |

| | 04 | Li Yun | 1990-08-06 | male |

| | 05 | Zhou Mei | 1991-12-01 | female |

| | 06 | Wu Lan | 1992-03-01 | female |

| | 07 | Zheng Zhu | 1989-07-01 | female |

| | 08 | Wang Ju | 1990-01-20 | female |

+-+

Select * from teacher

+-+

| | teacher.t_id | teacher.t_name |

+-+

| | 01 | Zhang San |

| | 02 | Li Si |

| | 03 | Wang Wu |

+-+

-1. Query the information and course scores of students whose "01" course is higher than that of "02" course: with one as (select * from score where c_id = 01), two as (select * from score where c_id = 02), result as (select o.s_id, o.s_score 01 score score 02_score from one o join two t on o.s_id = t.s_id where o.s_score > t.s_score) select. 02_score from result r join student s on s.s_id = r.s_id

+-+

| | s.s_id | s.s_name | s.s_birth | s.s_sex | 01_score | 02_score | |

+-+

| | 02 | Qian Dian | 1990-12-21 | male | 70 | 60 |

| | 04 | Li Yun | 1990-08-06 | male | 50 | 30 |

+-+

2. Query the information and course scores of students whose scores in "01" course are lower than those in "02" course: * * with one as (select * from score where c_id = 01), two as (select * from score where c_id = 02), result as (select o.s_id, o.s_score 01 score score 02_score from one o join two t on o.s_id = t.s_id where o.s_score

< t.s_score )select s.*,01_score,02_score from result r join student s on s.s_id = r.s_id; +---------+-----------+-------------+----------+-----------+-----------+--+ | s.s_id | s.s_name | s.s_birth | s.s_sex | 01_score | 02_score | +---------+-----------+-------------+----------+-----------+-----------+--+ | 01 | 赵雷 | 1990-01-01 | 男 | 80 | 90 | | 05 | 周梅 | 1991-12-01 | 女 | 76 | 87 | +---------+-----------+-------------+----------+-----------+-----------+--+ - 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:**with avg_student as(select s_id , avg(s_score) avg_score from score group by s_id having avg_score >

= 60) select s.s_id id, s.s_name name, round (avg_score, 1) avg_score from avg_student a join student s on s.s_id = a.s_id

+-+

| | id | name | avg_score | |

+-+

| | 01 | Zhao Lei | 89.7 |

| | 02 | Qian Dian | 70.0 |

| | 03 | Sun Feng | 80.0 |

| | 05 | Zhou Mei | 81.5 |

| | 07 | Zheng Zhu | 93.5 | |

+-+

-4. Query the student numbers and student names and average scores of students whose average score is less than 60:-(both with and without grades) with avg_student as (select s_id, avg (s_score) avg_score from score group by s_id), result as (select s.s_id id, s.s_name name, nvl (round (avg_score,1)) 0) avg_score from student s left join avg_student an on s.s_id = a.s_id) select * from result where avg_score

< 60; +------------+--------------+-------------------+--+ | result.id | result.name | result.avg_score | +------------+--------------+-------------------+--+ | 04 | 李云 | 33.3 | | 06 | 吴兰 | 32.5 | | 08 | 王菊 | 0.0 | +------------+--------------+-------------------+--+ - 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:with t1 as(select s_id,count(*) course_sum, sum(s_score) sum_score from score group by s_id) select s.s_id id, s.s_name name, nvl(course_sum,0) course_sum,nvl(sum_score,0) sum_score from student s left join t1 on t1.s_id = s.s_id; +-----+-------+-------------+------------+--+ | id | name | course_sum | sum_score | +-----+-------+-------------+------------+--+ | 01 | 赵雷 | 3 | 269 | | 02 | 钱电 | 3 | 210 | | 03 | 孙风 | 3 | 240 | | 04 | 李云 | 3 | 100 | | 05 | 周梅 | 2 | 163 | | 06 | 吴兰 | 2 | 65 | | 07 | 郑竹 | 2 | 187 | | 08 | 王菊 | 0 | 0 | +-----+-------+-------------+------------+--+ - 6、查询"李"姓老师的数量:select count(1) num from teacher where t_name like "李%"; +------+--+ | num | +------+--+ | 1 | +------+--+ - 7、查询学过"张三"老师授课的同学的信息:with t as(select st.s_id from score s join course c on s.c_id = c.c_id join teacher t on t.t_id=c.t_id join student st on st.s_id = s.s_id where t_name="张三" group by st.s_id)select student.* from student join t on t.s_id = student.s_id; +---------------+-----------------+------------------+----------------+--+ | student.s_id | student.s_name | student.s_birth | student.s_sex | +---------------+-----------------+------------------+----------------+--+ | 01 | 赵雷 | 1990-01-01 | 男 | | 02 | 钱电 | 1990-12-21 | 男 | | 03 | 孙风 | 1990-05-20 | 男 | | 04 | 李云 | 1990-08-06 | 男 | | 05 | 周梅 | 1991-12-01 | 女 | | 07 | 郑竹 | 1989-07-01 | 女 | +---------------+-----------------+------------------+----------------+--+ - 8、查询没学过"张三"老师授课的同学的信息:with t as(select s.s_id from score s join course c on s.c_id = c.c_id join teacher t on t.t_id=c.t_id where t_name="张三" group by s.s_id)select student.* from student left join t on t.s_id = student.s_id where t.s_id is null; +---------------+-----------------+------------------+----------------+--+ | student.s_id | student.s_name | student.s_birth | student.s_sex | +---------------+-----------------+------------------+----------------+--+ | 06 | 吴兰 | 1992-03-01 | 女 | | 08 | 王菊 | 1990-01-20 | 女 | +---------------+-----------------+------------------+----------------+--+ - 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:with one as(select s_id,c_id from score where c_id = 01),two as(select s_id,c_id from score where c_id = 02)select s.* from student s join one o on s.s_id = o.s_id join two t on t.s_id = o.s_id; +---------+-----------+-------------+----------+--+ | s.s_id | s.s_name | s.s_birth | s.s_sex | +---------+-----------+-------------+----------+--+ | 01 | 赵雷 | 1990-01-01 | 男 | | 02 | 钱电 | 1990-12-21 | 男 | | 03 | 孙风 | 1990-05-20 | 男 | | 04 | 李云 | 1990-08-06 | 男 | | 05 | 周梅 | 1991-12-01 | 女 | +---------+-----------+-------------+----------+--+ - 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:with one as(select s_id,c_id from score where c_id = 01),two as(select s_id,c_id from score where c_id = 02)select s.* from student s join one o on s.s_id = o.s_id left join two t on t.s_id = o.s_id where t.s_id is null; +---------+-----------+-------------+----------+--+ | s.s_id | s.s_name | s.s_birth | s.s_sex | +---------+-----------+-------------+----------+--+ | 06 | 吴兰 | 1992-03-01 | 女 | +---------+-----------+-------------+----------+--+ - 11、查询没有学全所有课程的同学的信息:with t1 as(select count(c_id) course_num from course ),t2 as(select s_id, count(c_id) cour_num from score group by s_id)select s.* from student s left join t2 on t2.s_id=s.s_id join t1 where t2.s_id is null or t2.cour_num < t1.course_num; +---------+-----------+-------------+----------+--+ | s.s_id | s.s_name | s.s_birth | s.s_sex | +---------+-----------+-------------+----------+--+ | 05 | 周梅 | 1991-12-01 | 女 | | 06 | 吴兰 | 1992-03-01 | 女 | | 07 | 郑竹 | 1989-07-01 | 女 | | 08 | 王菊 | 1990-01-20 | 女 | +---------+-----------+-------------+----------+--+ - 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息:with t1 as(select c_id from score where s_id = 01),t2 as(select s_id from t1 join score s on s.c_id = t1.c_id group by s_id)select student.* from student join t2 on t2.s_id = student.s_id where t2.s_id != 01; +---------------+-----------------+------------------+----------------+--+ | student.s_id | student.s_name | student.s_birth | student.s_sex | +---------------+-----------------+------------------+----------------+--+ | 02 | 钱电 | 1990-12-21 | 男 | | 03 | 孙风 | 1990-05-20 | 男 | | 04 | 李云 | 1990-08-06 | 男 | | 05 | 周梅 | 1991-12-01 | 女 | | 06 | 吴兰 | 1992-03-01 | 女 | | 07 | 郑竹 | 1989-07-01 | 女 | +---------------+-----------------+------------------+----------------+--+ - 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息:with num as(select count(c_id) cour_num from score where s_id = 01),one as(select * from score where s_id = 01 ),t1 as(select s.s_id , count(s.c_id) c_num from one o join score s on o.c_id = s.c_id group by s.s_id),t2 as(select s_id from t1 join num where cour_num=t1.c_num and s_id != 01)select student.* from student join t2 on t2.s_id = student.s_id; +---------------+-----------------+------------------+----------------+--+ | student.s_id | student.s_name | student.s_birth | student.s_sex | +---------------+-----------------+------------------+----------------+--+ | 02 | 钱电 | 1990-12-21 | 男 | | 03 | 孙风 | 1990-05-20 | 男 | | 04 | 李云 | 1990-08-06 | 男 | +---------------+-----------------+------------------+----------------+--+ - 14、查询没学过"张三"老师讲授的任一门课程的学生姓名:with t as(select s.s_id from score s join course c on s.c_id = c.c_id join teacher t on t.t_id=c.t_id where t_name="张三" group by s.s_id)select student.* from student left join t on t.s_id = student.s_id where t.s_id is null; +---------------+-----------------+------------------+----------------+--+ | student.s_id | student.s_name | student.s_birth | student.s_sex | +---------------+-----------------+------------------+----------------+--+ | 06 | 吴兰 | 1992-03-01 | 女 | | 08 | 王菊 | 1990-01-20 | 女 | +---------------+-----------------+------------------+----------------+--+ - 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩:with t1 as(select s_id,count(c_id) c_num from score where s_score =2)select s.s_id,s.s_name,round(avg(s_score),1) avg_score from student s join t1 on t1.s_id = s.s_id join score sc on t1.s_id = sc.s_id group by s.s_id,s.s_name +---------+-----------+------------+--+ | s.s_id | s.s_name | avg_score | +---------+-----------+------------+--+ | 04 | 李云 | 33.3 | | 06 | 吴兰 | 32.5 | +---------+-----------+------------+--+ - 16、检索"01"课程分数小于60，按分数降序排列的学生信息:with t1 as(select * from score where c_id = 01 and s_score =60，中等为：70-80，优良为：80-90，优秀为：>

= 90

With T1 as (select c_id course_ID, max (s_score) max_score,min (s_score) min_score,round (avg (s_score), 2) avg_score, count (s_id) num_sid, sum (case when s_score > = 60 then 1 else 0 end) pa***ate, sum (case when s_score > = 70 and s_score = 80 and s_score = 90 then 1 else 0 end) excellenceRate from score group by c_id) select course_ID,c_name course_name,max_score Min_score, avg_score, round (pa***ate/num_sid,3) pa***ate,round (mediumRate/num_sid,3) mediumRate,round (goodRate/num_sid,3) goodRate, round (excellenceRate/num_sid,3) excellenceRate from T1 join course c on c.c_id = t1.course_ID

+-+

| | course_id | course_name | max_score | min_score | avg_score | pa***ate | mediumrate | goodrate | excellencerate |

+-+

| | 01 | language | 80 | 31 | 64.5 | 0.667 | 0.333 | 0.333 | 0.333 |

| | 02 | Mathematics | 90 | 30 | 72.67 | 0.833 | 0.833 | 0.167 |

| | 03 | English | 99 | 20 | 68.5 | 0.667 | 0.333 | 0.333 |

+-+

-19. Sort by subject scores, and show the ranking: select *, row_number () over (partition by c_id order by s_score desc) ranks from score

+-+

| | score.s_id | score.c_id | score.s_score | ranks | |

+-+

| | 03 | 01 | 80 | 1 |

| | 01 | 01 | 80 | 2 |

| | 05 | 01 | 76 | 3 |

| | 02 | 01 | 70 | 4 |

| | 04 | 01 | 50 | 5 | |

| | 06 | 01 | 31 | 6 |

| | 01 | 02 | 90 | 1 | |

| | 07 | 02 | 89 | 2 |

| | 05 | 02 | 87 | 3 |

| | 03 | 02 | 80 | 4 |

| | 02 | 02 | 60 | 5 |

| | 04 | 02 | 30 | 6 |

| | 01 | 03 | 99 | 1 |

| | 07 | 03 | 98 | 2 |

| | 02 | 03 | 80 | 3 |

| | 03 | 03 | 80 | 4 |

| | 06 | 03 | 34 | 5 | |

| | 04 | 03 | 20 | 6 | |

+-+

-20. Query the total scores of students and rank them: with T1 as (select over (s_score) sum_score from score group by s_id order by sum_score desc) select s.s_id id,s_name name, sum_score, row_number () over (order by sum_score desc) rank from student s join T1 on s.s_id=t1.s_id

+-+

| | score.s_id | s_name | sumscore | ranking | |

+-+

| | 01 | Zhao Lei | 269 | 1 | |

| | 03 | Sun Feng | 240 | 2 | |

| | 02 | Qian Dian | 210 | 3 | |

| | 07 | Zheng Zhu | 187 | 4 | |

| | 05 | Zhou Mei | 163 | 5 | |

| | 04 | Li Yun | 100 | 6 | |

| | 06 | Wu Lan | 65 | 7 |

+-+

-21. Query the average score of different courses taught by different teachers from high to low. Display: select t.t_name teacher_name,c.c_name course_name,round (avg (s.s_score), 2) avg_score from teacher t join course c on t.t_id = c.t_id join score s on s.c_id = c.c_id group by t.tactinamemum. Censor name order by avg_score desc.

+-+

| | teacher_name | course_name | avg_score | |

+-+

| | Zhang San | Mathematics | 72.67 |

| | Wang Wu | English | 68.5 |

| | Li Si | Chinese | 64.5 |

+-+

22. Check the student information from No.2 to No.3 of the total course scores and all course scores: with t as (select s_id, max (case when c_id = 01 then s_score else 0 end) chinese,max (case when c_id = 02 then s_score else 0 end) math,max (case when c_id = 03 then s_score else 0 end) english,sum (s_score) sum_score Row_number () over (order by sum (s_score) desc) rank from score group by s_id) select s.*, chinese,math,english, sum_score from student s join t on t.s_id = s.s_id where rank between 2 and 3

+-+

| | s.s_id | s.s_name | s.s_birth | s.s_sex | chinese | math | english | sum_score |

+-+

| | 03 | Sun Feng | 1990-05-20 | male | 80 | 80 | 80 | 240 |

| | 02 | Qian Dian | 1990-12-21 | male | 70 | 60 | 80 | 210 |

+-+

-23. Count the number of students in each score section of each subject: course number, course name, [100-85], [85-70], [70-60], [0-60] and the percentage with score_process as (select c_id, sum (case when s_score > = 85 and s_score=85 and s_score=70 and s_score=70 and s_score=60 and s_score=60 and s_score=0 and s_score=0 and slots) = 2

+-+

| | s_name | s_sex | num | |

+-+

+-+

-31. Check the list of students born in 1990: with t as (select *, year (s_birth) birth_year from student) select t.s_name name from t where birth_year = 1990

+-- +

| | name |

+-- +

| | Zhao Lei |

| | Qian Dian |

| | Sun Feng |

| | Li Yun |

| | Wang Ju |

+-- +

32. Query the average score of each course, and the results are arranged in descending order. When the average score is the same, it is arranged in ascending order of course number: select score.c_id,c_name, round (avg (s_score), 2) avg_score from score join course on score.c_id= course.c_id group by score.c_id,c_name order by avg_score desc,c_id asc.

+-+

| | score.c_id | c_name | avg_score | |

+-+

| | 02 | Mathematics | 72.67 |

| | 03 | English | 68.5 |

| | 01 | language | 64.5 |

+-+

-33. Query the student number, name and grade point average of all students whose average score is greater than or equal to 85: with t as (select avg_score from score group by s_id having avg_score (s_score) avg (s_score) > = 85) select s.s_id `student number `, s_name `name`, round (avg_score,2) `average score `from student s join t on t.s_id = s.s_id

+-+

| | Student number | name | APCge score | |

+-+

| | 01 | Zhao Lei | 89.67 |

| | 07 | Zheng Zhu | 93.5 | |

+-+

-34. Query the names and scores of students whose course name is "Mathematics" and whose score is less than 60: select s_name `Student name`, s_score `score `from course c join score s on c.c_id = s.c_id join student st on st.s_id = s.s_id where c.c_name = 'Mathematics' and s_score

< 60 ; +-------+-----+--+ | 学生姓名 | 分数 | +-------+-----+--+ | 李云 | 30 | +-------+-----+--+ - 35、查询所有学生的课程及分数情况:with t as(select s_id, max(if(c_id=01 ,s_score, 0 )) chinese,max(if(c_id=02 ,s_score, 0 )) math,max(if(c_id=03 ,s_score, 0 )) english,sum(s_score) sum_score from score group by s_id )select s_name name,chinese,math,english,sum_score from student s join t on t.s_id = s.s_id; +-------+----------+-------+----------+------------+--+ | name | chinese | math | english | sum_score | +-------+----------+-------+----------+------------+--+ | 赵雷 | 80 | 90 | 99 | 269 | | 钱电 | 70 | 60 | 80 | 210 | | 孙风 | 80 | 80 | 80 | 240 | | 李云 | 50 | 30 | 20 | 100 | | 周梅 | 76 | 87 | 0 | 163 | | 吴兰 | 31 | 0 | 34 | 65 | | 郑竹 | 0 | 89 | 98 | 187 | +-------+----------+-------+----------+------------+--+ - 36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:select s_name,c_name,s_score from course c join score s on c.c_id = s.c_id join student st on st.s_id = s.s_id where s_score >

70

+-+

| | s_name | c_name | s_score | |

+-+

| | Zhao Lei | Chinese | 80 | |

| | Zhao Lei | Mathematics | 90 |

| | Zhao Lei | English | 99 | |

| | Qian Dian | English | 80 | |

| | Sun Feng | Chinese | 80 | |

| | Sun Feng | Mathematics | 80 |

| | Sun Feng | English | 80 | |

| | Zhou Mei | Chinese | 76 | |

| | Zhou Mei | Mathematics | 87 |

| | Zheng Zhu | Mathematics | 89 |

| | Zheng Zhu | English | 98 | |

+-+

37. Query the name of the student who failed the course, course, score: select signornamememery cymnametemerentiary sprints score from course c join score s on c.c_id = s.c_id join student st on st.s_id = s.s_id where s_score

< 60 ; +---------+---------+----------+--+ | s_name | c_name | s_score | +---------+---------+----------+--+ | 李云 | 语文 | 50 | | 李云 | 数学 | 30 | | 李云 | 英语 | 20 | | 吴兰 | 语文 | 31 | | 吴兰 | 英语 | 34 | +---------+---------+----------+--+ -38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名:select s.s_id `学号`,s_name `姓名` from student st join score s on s.s_id = st.s_id where c_id=01 and s_score >

= 80

+-+

| | Student number | name | |

+-+

| | 01 | Zhao Lei |

| | 03 | Sun Feng |

+-+

-39. Ask for the number of students in each course: select clockname count (s.s_id) student_num from course c join score s on s.c_id = c.c_id group by c_name

+-+

| | c_name | student_num |

+-+

| | Mathematics | 6 |

| | English | 6 |

| | language | 6 |

+-+

40. Inquire about the students with the highest scores and their scores among the students taking the courses taught by teacher "Zhang San": select st.*,c_name,s_score from course c join score s on c.c_id = s.c_id join teacher t on t.t_id = c.t_id join student st on st.s_id=s.s_id where tactile names' Zhang San 'order by s_score desc limit 1

+-+

| | st.s_id | st.s_name | st.s_birth | st.s_sex | c_name | s_score | |

+-+

| | 01 | Zhao Lei | 1990-01-01 | male | Mathematics | 90 |

+-+

41. Query the student numbers, course numbers and scores of students with the same scores in different courses: select distinct s1.* from score S1 score s2 where s1.c_id s2.c_id and s1.s_score=s2.s_score

+-+

| | s1.s_id | s1.c_id | s1.s_score | |

+-+

| | 01 | 01 | 80 | |

| | 02 | 03 | 80 | |

| | 03 | 01 | 80 | |

| | 03 | 02 | 80 | |

| | 03 | 03 | 80 | |

+-+

42. Check the top three of the best scores in each course: with t as (select cymnametempere, row_number () over (partition by c_name order by s_score desc) rank from student s join score sc on sc.s_id=s.s_id join course c on c.c_id=sc.c_id) select * from t where rank between 1 and 3

+-+

| | t.c_name | t.s_name | t.s_score | t.rank | |

+-+

| | Mathematics | Zhao Lei | 90 | 1 | |

| | Mathematics | Zheng Zhu | 89 | 2 | |

| | Mathematics | Zhou Mei | 87 | 3 |

| | English | Zhao Lei | 99 | 1 | |

| | English | Zheng Zhu | 98 | 2 | |

| | English | Sun Feng | 80 | 3 | |

| | Chinese | Sun Feng | 80 | 1 | |

| | Chinese | Zhao Lei | 80 | 2 | |

| | Chinese | Zhou Mei | 76 | 3 | |

+-+

43. Statistics on the number of students taking each course (for courses with more than 5 students):

-the course number and the number of electives are required to be output. The query results are arranged in descending order. If the number is the same, it is arranged in ascending order of course number.

Select cantilever account (s_id) num from score group by c_id having num > 5 order by num desc, c_id asc

+-+

| | c_id | num |

+-+

| | 01 | 6 |

| | 02 | 6 |

| | 03 | 6 |

+-+

-44. Retrieve the student numbers of at least two courses: select signoridjournal count (c_id) course_num from score group by s_id having course_num > = 2

+-+

| | s_id | course_num |

+-+

| | 01 | 3 |

| | 02 | 3 |

| | 03 | 3 |

| | 04 | 3 |

| | 05 | 2 |

| | 06 | 2 |

| | 07 | 2 |

+-+

-45. Query the information of the students who have taken all the courses: with t as (select count (c_id) sum_course from course), T1 as (select as (c_id) course_num from score group by s_id) select s.* from T1 join t join student s on s.s_id=t1.s_id where sum_course=course_num

+-+

| | s.s_id | s.s_name | s.s_birth | s.s_sex | |

+-+

| | 01 | Zhao Lei | 1990-01-01 | male |

| | 02 | Qian Dian | 1990-12-21 | male |

| | 03 | Sun Feng | 1990-05-20 | male |

| | 04 | Li Yun | 1990-08-06 | male |

+-+

-46. Inquire about the age (first year) of each student: select *, floor ((datediff (current_date,s_birth)) / 365) age from student

+-- +

| | student.s_id | student.s_name | student.s_birth | student.s_sex | age | |

+-- +

| | 01 | Zhao Lei | 1990-01-01 | male | 29 |

| | 02 | Qian Dian | 1990-12-21 | male | 28 |

| | 03 | Sun Feng | 1990-05-20 | male | 29 |

| | 04 | Li Yun | 1990-08-06 | male | 29 |

| | 05 | Zhou Mei | 1991-12-01 | female | 28 |

| | 06 | Wu Lan | 1992-03-01 | female | 27 |

| | 07 | Zheng Zhu | 1989-07-01 | female | 30 |

| | 08 | Wang Ju | 1990-01-20 | female | 29 |

+-- +

-47. Query the students who celebrate their birthday this week: select * from student where weekofyear (s_birth) = weekofyear (current_date)

+-+

| | student.s_id | student.s_name | student.s_birth | student.s_sex | |

+-+

+-+

-48. Query the students who will celebrate their birthday next week: select * from student where weekofyear (s_birth) = weekofyear (current_date) + 1

+-+

| | student.s_id | student.s_name | student.s_birth | student.s_sex | |

+-+

| | 02 | Qian Dian | 1990-12-21 | male |

+-+

-49. Query the students who celebrate their birthday this month: select * from student where month (current_date) = month (s_birth)

+-+

| | student.s_id | student.s_name | student.s_birth | student.s_sex | |

+-+

| | 02 | Qian Dian | 1990-12-21 | male |

| | 05 | Zhou Mei | 1991-12-01 | female |

+-+

-50. Inquire about the students who celebrate their birthday in December: select * from student where month (s_birth) = 12

+-+

| | student.s_id | student.s_name | student.s_birth | student.s_sex | |

+-+

| | 02 | Qian Dian | 1990-12-21 | male |

| | 05 | Zhou Mei | 1991-12-01 | female |

+-+

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.