mysql第三次作业

作业三：

脚本：

3.select * from student;

4.select * from student limit 1,3;

5.select id,name,department from student;

6.select * from student where department='计算机系' or department='英语系';

7.select year(now())-birth from student where year(now())-birth >18 and year(now())-birth<22;

8.select department,count(*) from student group by department;

9.select c_name,max(grade) from score group by c_name;

10.select c_name,grade from score where stu_id=904;

11.select * from student join score on(student.id=score.stu_id);

12.select name as 姓名,sum(grade) as 总成绩 from student join score on(student.id=score.stu_id) group by name;

13.select avg(grade) as 平均成绩 from score group by c_name;

14.select * from student join score on(student.id=score.stu_id) where c_name='计算机' and grade < 95;

15.select * from student join score on(student.id=score.stu_id) where c_name='计算机' and c_name='英语';

16.select name,grade from student join score on(student.id=score.stu_id) where c_name='计算机' order by name desc;

17.select id from student union select stu_id from score;

18.select name,department,c_name,grade from student join score on(student.id=score.stu_id) where name like'张%' or name like'王%';

19.select name,department,c_name,grade from student join score on(student.id=score.stu_id) where address like'湖南%';

面试题：

学生表：
CREATE TABLE student (
    s_id INT PRIMARY KEY,
    s_nAme VARCHAR(50),
    s_birth DATE,
    s_sex chAr(1)
);
课程表；
creAte tAble course(
c_id int primAry key,
c_nAme vArchAr(50),
t_id int
);
教师表：
CREATE TABLE teAcher (
  t_id INT PRIMARY KEY,
  t_nAme VARCHAR(50)
);
创建成绩表
CREATE TABLE score (
  s_id INT,
  c_id INT,
  s_score DECIMAL(5, 2),
  PRIMARY KEY (s_id, c_id),
  FOREIGN KEY (s_id) REFERENCES student(s_id),
  FOREIGN KEY (c_id) REFERENCES course(c_id)
);

插入学生表数据
INSERT INTO student (s_id, s_name, s_birth, s_sex)
VALUES
  (1, '张三', '2000-01-01', '男'),
  (2, '李四', '1999-02-02', '女'),
  (3, '王五', '2001-03-03', '男'),
  (4, '赵六', '2002-04-04', '女'),
  (5, '刘七', '2003-05-05', '男');

插入课程表数据
INSERT INTO course (c_id, c_name, t_id)
VALUES
  (1, '数学', 101),
  (2, '英语', 102),
  (3, '物理', 103),
  (4, '化学', 104),
  (5, '历史', 105);

插入教师表数据
INSERT INTO teacher (t_id, t_name)
VALUES
  (101, '张老师'),
  (102, '李老师'),
  (103, '王老师'),
  (104, '赵老师'),
  (105, '刘老师');

插入成绩表数据
INSERT INTO score (s_id, c_id, s_score)
VALUES
  (1, 1, 85.5),
  (2, 1, 90.0),
  (3, 1, 78.5),
  (4, 1, 92.0),
  (5, 1, 88.5);

1.select
  student.s_id,
  student.s_name,
  COUNT(score.c_id) AS num_courses,
  SUM(score.s_score) AS total_score
from
  student LEFT join score ON student.s_id = score.s_id group by student.s_id, student.s_name;
2.select
  student.s_id,
  student.s_name
from
  student
join
  score ON student.s_id = score.s_id
join
  course ON score.c_id = course.c_id
join
  teacher ON course.t_id = teacher.t_id
where
  teacher.t_name = '张老师'
group by
  student.s_id, student.s_name;
3.select
  student.s_id,
  student.s_name
from
  student
join
  score ON student.s_id = score.s_id
where
  score.c_id IN (
    select
      c_id
    from
      score
    where
      s_id = 2
  )
group by
  student.s_id, student.s_name
having
  COUNT(DISTINCT score.c_id) = (
    select
      COUNT(DISTINCT c_id)
    from
      score
    where
      s_id = 2
  )
  and student.s_id != 2;
4.select
  course.c_id,
  course.c_name,
  SUM(CASE WHEN score >= 85 and score <= 100 THEN 1 ELSE 0 END) AS score_85_100,
  SUM(CASE WHEN score >= 70 and score < 85 THEN 1 ELSE 0 END) AS score_70_84,
  SUM(CASE WHEN score >= 60 and score < 70 THEN 1 ELSE 0 END) AS score_60_69,
  SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS score_below_60
from
  score
join
  course on score.c_id = course.c_id
group by
  course.c_id,
  course.c_name;