SQL面试题

本文参考自http://blog.csdn.net/lifetragedy/article/details/9935699，做了些修改

准备四张表及数据：

表student 学生
表course 课程
表student_course（学生与课程的分数mapping 表）
表teacher 教师

/*SQLyog Trial v11.01 (32 bit)MySQL - 5.1.45-community : Database - newtest**********************************************************************//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;CREATE DATABASE /*!32312 IF NOT EXISTS*/`newtest` /*!40100 DEFAULT CHARACTER SET latin1 */;USE `newtest`;/*Table structure for table `course` */DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (  `course_no` int(11) NOT NULL AUTO_INCREMENT,  `course_name` varchar(30) DEFAULT NULL,  `teacher_no` int(11) NOT NULL,  PRIMARY KEY (`course_no`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;/*Data for the table `course` */insert  into `course`(`course_no`,`course_name`,`teacher_no`) values (1,'Math',1),(2,'English',1),(3,'Chinese',1);/*Table structure for table `student` */DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `student_no` int(11) NOT NULL AUTO_INCREMENT,  `student_name` varchar(30) DEFAULT NULL,  `student_age` int(11) DEFAULT NULL,  `student_sex` varchar(10) DEFAULT NULL,  PRIMARY KEY (`student_no`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;/*Data for the table `student` */insert  into `student`(`student_no`,`student_name`,`student_age`,`student_sex`) values (1,'Jim',16,'Male'),(2,'Tom',15,'Male'),(3,'Kity',16,'Female'),(4,'Jerry',15,'Male'),(5,'Cathy',15,'Female'),(6,'Mongo',16,'Male');/*Table structure for table `student_course` */DROP TABLE IF EXISTS `student_course`;CREATE TABLE `student_course` (  `student_no` int(11) NOT NULL,  `course_no` int(11) DEFAULT NULL,  `score` int(11) DEFAULT NULL,  UNIQUE KEY `s_c_unique` (`student_no`,`course_no`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*Data for the table `student_course` */insert  into `student_course`(`student_no`,`course_no`,`score`) values (1,1,71),(1,2,86),(2,1,91),(2,2,81),(3,1,31),(5,2,56),(4,2,59),(2,3,88),(4,3,36),(3,3,66);/*Table structure for table `teacher` */DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (  `teacher_no` int(11) NOT NULL AUTO_INCREMENT,  `teacher_name` varchar(30) DEFAULT NULL,  PRIMARY KEY (`teacher_no`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;/*Data for the table `teacher` */insert  into `teacher`(`teacher_no`,`teacher_name`) values (1,'Li Lei'),(2,'Li Ming'),(3,'Deng Gong'),(4,'Wu Feng'),(5,'Li Zi'),(6,'Li Ming');/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- 1、查询编号“1”课程比“2”课程成绩高的所有学生的学号；SELECT sc.student_no FROM student_course sc INNER JOIN student_course sc2ON sc2.student_no = sc.student_noWHERE sc.course_no = 1 AND sc2.course_no = 2 AND sc.score > sc2.scoreSELECT a.student_no FROM (SELECT student_no,score FROM student_course WHERE course_no=1) a, (SELECT student_no,score  FROM student_course WHERE course_no=2) bWHERE a.score > b.score AND a.student_no = b.student_no-- 2、查询平均成绩大于60分的同学的学号和平均成绩；-- 利用HAVINGSELECT student_no, AVG(score) avgScore FROM student_courseGROUP BY student_no HAVING AVG(score) > 60-- 利用子句SELECT a.student_no, a.avgScore FROM (SELECT student_no, AVG(score) avgScore FROM student_courseGROUP BY student_no) a WHERE a.avgScore > 60--  3、查询所有同学的学号、姓名、选课数、总成绩SELECT s.student_no, s.student_name, COUNT(sc.course_no) course_count, SUM(sc.score) total_scoreFROM student s LEFT OUTER JOIN student_course sc ON s.student_no = sc.student_no GROUP BY s.student_no --  4、查询名字不重复，姓“Li”的老师的个数SELECT COUNT(DISTINCT(teacher_name)) FROM teacher WHERE teacher_name LIKE 'Li%'--  5、查询没学过“Li Lei”老师课的同学的学号、姓名SELECT s.student_no, s.student_name FROM student sWHERE s.student_no NOT IN (SELECT DISTINCT(s.student_no) FROM student sLEFT JOIN student_course sc ON s.student_no = sc.student_noLEFT JOIN course c ON sc.course_no = c.course_noLEFT JOIN teacher t ON c.teacher_no = t.teacher_noWHERE t.teacher_name = 'Li Lei')SELECT s.student_no, s.student_name FROM student sWHERE s.student_no NOT IN (SELECT DISTINCT(s.student_no) FROM student s, student_course sc, course c, teacher t WHERE s.student_no = sc.student_no AND sc.course_no = c.course_noAND c.teacher_no = t.teacher_noAND t.teacher_name = 'Li Lei')--  6、查询学过编号“1”并且也学过“2”课程的同学的学号、姓名SELECT s.student_no, s.student_name, COUNT(s.student_no) FROM student s, student_course scWHERE s.student_no = sc.student_no AND sc.course_no IN (1, 2) GROUP BY s.student_no HAVING COUNT(s.student_no) = 2SELECT s.student_no, s.student_name FROM student s, student_course sc WHERE s.student_no = sc.student_noAND sc.course_no = 1 AND EXISTS( SELECT * FROM student_course sc2 WHERE sc2.student_no = sc.student_no AND sc2.course_no = 2);--  7、查询学过“Li Lei”老师所教的所有课的同学的学号、姓名SELECT s.student_no, s.student_name FROM student s, student_course sc WHERE s.student_no = sc.student_noAND sc.course_no IN (SELECT c.course_no FROM course c, teacher t WHERE c.teacher_no = t.teacher_noAND t.teacher_name = 'Li Lei') GROUP BY s.student_no HAVING COUNT(sc.course_no)  = ( SELECT COUNT(c2.course_no) FROM course c2, teacher t2 WHERE c2.teacher_no = t2.teacher_noAND t2.teacher_name = 'Li Lei'    )--  8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；SELECT s.student_no, s.student_name FROM student s, student_course scWHERE s.student_no = sc.student_no AND sc.course_no = 1AND EXISTS (SELECT * FROM student_course sc2 WHERE sc2.student_no = s.student_no AND sc2.course_no = 2 AND sc.score > sc2.score); SELECT student_no,student_name FROM (SELECT s.student_no, s.student_name,sc.score , (SELECT sc2.score FROM student_course sc2 WHERE sc2.student_no = s.student_no AND sc2.course_no = 2) score2  FROM student s,student_course sc WHERE s.student_no = sc.student_no AND sc.course_no = 1) s2 WHERE score2 < score;--  9、查询所有课程成绩小于60分的同学的学号、姓名SELECT s.student_no, s.student_name FROM student sWHERE s.student_no NOT IN (SELECT s2.student_no FROM student s2, student_course sc WHERE s2.student_no = sc.student_no AND sc.score >= 60)SELECT s.student_no, s.student_name FROM student s INNER JOIN student_course scON s.student_no = sc.student_noWHERE NOT EXISTS (SELECT * FROM student_course sc2 WHERE s.student_no = sc2.student_no AND sc2.score >= 60)GROUP BY sc.student_no--  10、查询没有学全所有课的同学的学号、姓名SELECT s.student_no, s.student_name FROM student s LEFT JOIN student_course scON s.student_no = sc.student_noGROUP BY sc.student_no HAVING COUNT(sc.course_no) < (SELECT COUNT(*) FROM course)ORDER BY s.student_no--  11、查询至少有一门课与学号为“3”的同学所学相同的同学的学号和姓名；SELECT DISTINCT s.student_no, s.student_name FROM student sINNER JOIN student_course scON s.student_no = sc.student_noAND s.student_no != 3AND sc.course_no IN (SELECT sc2.course_no FROM student_course sc2 WHERE sc2.student_no = 3)-- 存储过程-- 不带参数DELIMITER &&  CREATE PROCEDURE query_test()BEGIN SELECT * FROM student_course sc WHERE sc.course_no = 1;END &&DELIMITER ;CALL query_test();-- 带输入输出参数DROP PROCEDURE query_avg_score;DELIMITER &&CREATE PROCEDURE query_avg_score(IN s_id INT, OUT avg_score DOUBLE)BEGIN  SELECT AVG(score) INTO avg_score FROM student_course   WHERE student_no = s_id;END &&DELIMITER ;  CALL query_avg_score(1, @avg_score);SELECT @avg_score;-- 定义局部变量并使用游标DROP PROCEDURE test3;DELIMITER &&CREATE PROCEDURE test3(OUT rs INT)BEGIN  DECLARE o INT;  -- 声明一个局部变量  DECLARE cursorTest CURSOR FOR SELECT course_no FROM course; OPEN cursorTest;  -- 打开游标   FETCH cursorTest INTO o; SELECT o INTO rs; CLOSE cursorTest;  -- 关闭游标END &&DELIMITER ;CALL test3(@rs);SELECT @rs;