SQL查询练习1

准备数据：

CREATE TABLE `Student`(  
    Sno         INT(11) auto_increment PRIMARY KEY,  
    Sname       VARCHAR(20),  
    Ssex        VARCHAR(20),  
    Sage        INT(3),  
    Sdept       VARCHAR(20));  
  
CREATE TABLE `Course`(  
    Cno         INT(11) auto_increment PRIMARY KEY,  
    Cname       VARCHAR(20),  
    Cpno        INT(11) REFERENCES Course(Cno),  
    Ccredit INT(2));  
  
CREATE TABLE `SC`(  
    Sno     INT(11) REFERENCES Student(Sno),  
    Cno     INT(11) REFERENCES Course(Cno),  
    Grade   FLOAT(3),  
    PRIMARY KEY(Sno,Cno));  
      
INSERT INTO `Student` VALUES(95001,"李勇","男",20,"CS");  
INSERT INTO `Student` VALUES(95003,"王名","男",20,"MA");  
INSERT INTO `Student` VALUES(95002,"刘晨","男",19,"IS");  
INSERT INTO `Student` VALUES(95004,"张立","男",18,"IS");  
INSERT INTO `Student` VALUES(95006,"牛德华","女",20,"IS");  
INSERT INTO `Student` VALUES(95005,"聂小轩","男",28,"CS");  
INSERT INTO `Student` VALUES(95007,"张三三","男",18,"CS");  
INSERT INTO `Student` VALUES(95008,"李思思","女",16,"MA");  
INSERT INTO `Student` VALUES(95009,"张呜呜","男",19,"MA");  
  
INSERT INTO `Course` VALUES(1,"数据库",5,4);  
INSERT INTO `Course` VALUES(2,"数学",null,2);  
INSERT INTO `Course` VALUES(3,"信息系统",1,4);  
INSERT INTO `Course` VALUES(4,"操作系统",6,3);  
INSERT INTO `Course` VALUES(5,"数据结构",7,4);  
INSERT INTO `Course` VALUES(6,"数据处理",null,2);  
INSERT INTO `Course` VALUES(7,"PASCAL语言",6,4);  
INSERT INTO `Course` VALUES(8,"C_C++语言",null,4);  
  
INSERT INTO `SC` VALUES(95001,1,60);  
INSERT INTO `SC` VALUES(95002,1,90);  
INSERT INTO `SC` VALUES(95001,2,80);  
INSERT INTO `SC` VALUES(95002,2,99);  
INSERT INTO `SC` VALUES(95002,3,45);  
INSERT INTO `SC` VALUES(95001,4,40);  
INSERT INTO `SC` VALUES(95002,4,88);  
INSERT INTO `SC` VALUES(95004,4,null);  
INSERT INTO `SC` VALUES(95005,4,45);  
INSERT INTO `SC` VALUES(95002,5,88);  
INSERT INTO `SC` VALUES(95003,5,89);  
INSERT INTO `SC` VALUES(95005,5,54);  
INSERT INTO `SC` VALUES(95001,6,82);  
INSERT INTO `SC` VALUES(95004,6,null);  
INSERT INTO `SC` VALUES(95005,6,99.5);  
INSERT INTO `SC` VALUES(95003,7,99);  
INSERT INTO `SC` VALUES(95004,7,45);  
INSERT INTO `SC` VALUES(95005,7,75);  
  
INSERT INTO `SC` VALUES(95006,1,60);  
INSERT INTO `SC` VALUES(95006,4,90); 

练习题

# 查询与“刘晨”同一个系学习的学生  
SELECT sno,sname   
FROM student   
WHERE sdept = (SELECT sdept FROM student WHERE sname = "刘晨");  
  
  
SELECT s1.sno,s1.sname   
FROM student s1 JOIN student s2   
    ON  s1.Sdept = s2.Sdept  
WHERE s2.sname="刘晨";  
  
  
SELECT s1.sno,s1.sname   
FROM student s1 ,student s2   
WHERE      
    s1.Sdept = s2.Sdept  
    AND  
    s2.sname="刘晨";  
  
# 查询选修了课程名为‘信息系统’的学生学号和姓名  
  
SELECT student.sno,sname   
FROM student, sc, course   
WHERE student.sno = sc.sno   
    AND course.cno = sc.cno  
  AND course.cname = '信息系统';  
  
  
SELECT sno,sname   
FROM student   
WHERE sno IN   
    (SELECT sno FROM sc WHERE cno IN   
            (SELECT cno FROM course WHERE cname='信息系统'));  
  
# 查询其他系中比IS系任一学生年龄小的学生名单。  
SELECT sname,sage   
FROM student   
WHERE Sdept <> 'IS'   
    AND Sage < ANY(SELECT sage FROM student WHERE Sdept = 'IS');  
  
SELECT sname,sage   
FROM student   
WHERE Sdept <> 'IS'   
    AND Sage < (SELECT MAX(sage) FROM student WHERE Sdept = 'IS');  
  
# 查询其他系中比IS系所有学生年龄都小的学生名单。  
SELECT sname,sage  
FROM student   
WHERE Sdept <> 'IS'  
    AND Sage < ALL(SELECT sage FROM student WHERE Sdept = 'IS');  
  
  
# 组函数比ANY ALL要高效。  
SELECT sname,sage  
FROM student   
WHERE Sdept <> 'IS'  
    AND Sage < (Select MIN(Sage) FROM student WHERE Sdept = 'IS');

---[2013-05-10]---