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');