sql 经典练习题

最新推荐文章于 2022-05-18 09:05:57 发布

LzwGlory

最新推荐文章于 2022-05-18 09:05:57 发布

阅读量699

点赞数

分类专栏： sql 面试文章标签： sql

本文链接：https://blog.csdn.net/lzwglory/article/details/50771379

版权

面试同时被 2 个专栏收录

13 篇文章 1 订阅

订阅专栏

sql

11 篇文章 0 订阅

订阅专栏

学生表：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(128) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `ssex` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `student`(`id`,`sname`,`sage`,`ssex`) values (1,'小明',16,'男'),(2,'小张',15,'男'),(3,'小美',16,'女'),(4,'小丽',15,'女'),(5,'小李',17,'男'),(6,'小刘',18,'男');

课程表：

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(128) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `course`(`id`,`cname`,`t_id`) values (1,'语文',1),(2,'数学',2),(3,'英语',3),(4,'物理',4),(5,'化学',5);

成绩表：

CREATE TABLE `sc` (
  `s_id` int(11) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `source` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `sc`(`s_id`,`c_id`,`source`) values (1,2,70),(1,3,80),(1,5,75),(2,1,60),(2,3,75),(2,5,90),(3,1,95),(3,2,80),(3,3,60),(3,5,40),(4,2,48),(4,3,65),(4,4,70),(4,5,60),(5,2,40),(5,3,50),(5,4,69),(5,5,79),(4,1,96),(5,1,60);

教师表：

CREATE TABLE `teacher` (
  `t_id` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `teacher`(`t_id`,`tname`) values (1,'王老师'),(2,'李老师'),(3,'张老师'),(4,'闫老师'),(5,'刘老师');

1）查询平均成绩大于60分的同学的学号和平均成绩；

SELECT sc_obj.s_id,AVG(sc_obj.source) FROM sc sc_obj  
GROUP BY sc_obj.s_id HAVING AVG(sc_obj.source)>60

2）查询所有同学的学号、姓名、选课数、总成绩；

SELECT stu_obj.id,stu_obj.sname,COUNT(sc_obj.c_id),SUM(sc_obj.source) FROM student stu_obj LEFT JOIN sc sc_obj ON stu_obj.id=sc_obj.s_id 
GROUP BY stu_obj.id

3）查询没学过“闫老师”老师课的同学的学号、姓名；

SELECT * FROM student stu_obj WHERE stu_obj.id 
NOT IN(SELECT DISTINCT(sc_obj.s_id) FROM teacher tea_obj,course cou_obj,sc sc_obj
WHERE tea_obj.tname='闫老师' AND tea_obj.t_id=cou_obj.t_id AND cou_obj.id=sc_obj.c_id)

4）查询学过课程编号为‘1’并且也学过课程编号为“2”课程的同学的学号、姓名；

SELECT stu_obj.id,stu_obj.sname FROM sc sc_obj,student stu_obj WHERE stu_obj.id=sc_obj.s_id AND sc_obj.c_id=1 AND EXISTS 
(SELECT 1 FROM sc sc_obj2 WHERE sc_obj2.s_id=sc_obj.s_id AND sc_obj2.c_id=2)

参考地址： http://www.jobui.com/mianshiti/it/shujuku/5838/