数据库语句练习

作业3：某大学研究生院有若干研究生导师，包括职工编号、姓名、职称、研究方向，其中每个导师的职工编号是唯一的。
若干研究生，包括学号、姓名、性别、入学日期，其中每个研究生的学号是唯一的。
每个导师可以带若干研究生，但每个研究生只能有一个导师。
请设计一个数据库，要求可以正确体现导师和研究生之间的关系。
设计完毕之后，请插入一定量的数据，并验证你设计的数据库是否满足要求。

在你设计的表中插入若干数据。

1.请查出每个导师所带研究生的姓名。
2.清查出特定姓名的导师所带研究生的姓名。
3.请查出每个导师所带研究生的数量。
4.请查出每个导师所带的男研究生的数量。
5.请找出选择哪个研究方向的导师最多。
6.请统计不同职称的导师的个数。

-- 老师表
CREATE TABLE teachers(
teacher_id int PRIMARY key,
name VARCHAR(20),
title VARCHAR(20),
research_dir VARCHAR(20)
);


-- 学生表
CREATE TABLE students(
stu_id int PRIMARY key,
name VARCHAR(20),
gender VARCHAR(20),
admission_date date,
t_id int
);

INSERT into teachers VALUES(1,"周波","教授","人工智能");
INSERT into teachers VALUES(2,"杨建刚","讲师","数据挖掘");
INSERT into teachers VALUES(3,"朴勇","副教授","分布式计算");
INSERT into teachers VALUES(4,"于红","副教授","数字媒体网络");
INSERT into teachers VALUES(5,"吴春明","教授","数据库");
INSERT into teachers VALUES(6,"李丽丽","研究员","图像处理");
INSERT into teachers VALUES(7,"潘云鹤","教授","人工智能");



INSERT into students VALUES(202101,"小红","女","2021-9-11",9);
INSERT into students VALUES(202102,"明明","男","2021-9-1",1);
INSERT into students VALUES(202103,"月月","女","2021-9-10",3);
INSERT into students VALUES(202104,"李华","男","2021-9-3",1);
INSERT into students VALUES(202105,"夏丽","女","2021-9-1",5);
INSERT into students VALUES(202106,"刘星","男","2021-9-12",7);
INSERT into students VALUES(202107,"朵拉","女","2021-9-9",6);
INSERT into students VALUES(202107,"朵拉","女","2021-9-9",5);
INSERT into students VALUES(202107,"朵拉","女","2021-9-9",1);

-- 1.请查出每个导师所带研究生的姓名。
select t.name as t_name,s.name as s_name
from teachers t
left join students s
on t.teacher_id=s.t_id;

-- 2.清查出特定姓名的导师所带研究生的姓名。
select s.name
from students s
where s.t_id=(select t.teacher_id
							from teachers t
							where t.name="周波");
							
-- 3.请查出每个导师所带研究生的数量。
select t.name as t_name,count(s.stu_id)
from teachers t
left join students s
on t.teacher_id=s.t_id
group by t.teacher_id

-- 4.请查出每个导师所带的男研究生的数量。
select t.`name`,count(table1.stu_id)
from teachers as t 
LEFT JOIN (select * 
					 from students
					 where gender="男") as table1
on t.teacher_id=table1.t_id
GROUP BY t.teacher_id

-- 5.请找出选择哪个研究方向的导师最多。
select research_dir,count(teacher_id) as num
from teachers
GROUP BY research_dir
ORDER BY num desc
LIMIT 0,1

-- 6.请统计不同职称的导师的个数。
select title,count(teacher_id)
from teachers
GROUP BY title