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.SdeptANDs2.sname="刘晨";# 查询选修了课程名为‘信息系统’的学生学号和姓名SELECT student.sno,sname
FROM student, sc, course
WHERE student.sno = sc.sno AND course.cno = sc.cnoAND 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');
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！