例程练习

数据库例程练习

• 3.49

  代码：

  SELECT student.*,sc.* FROM student,sc WHERE student.sno=sc.sno;
  

  运行结果
  

• 3.50

  代码：

  SELECT student.sno,sname,ssex,sage,sdept,cno,grade FROM student,sc where student.sno=sc.sno;
  

  运行结果
  

• 3.51

  代码：

  SELECT student.sno,sname FROM student,sc WHERE student.sno=sc.sno AND sc.cno='0202' AND sc.grade>90;SELECT student.sno,sname FROM student,sc WHERE student.sno=sc.sno AND sc.cno='0202' AND sc.grade>90;
  

  运行结果
  

• 3.52

  代码：

  SELECT FIRST.cno,SECOND.cpno FROM course FIRST,course SECOND WHERE FIRST.cpno=SECOND.cno;
  

  运行结果
  

• 3.53

  代码：

  SELECT student.sno,sname,ssex,sage,sdept,cno,grade FROM student LEFT OUTER JOIN sc ON (student.sno=sc.sno);
  

  运行结果
  

• 3.54

  代码：

  SELECT student.sno,sname,cname,grade FROM student,sc,course WHERE student.sno=sc.sno AND sc.cno=course.cno;
  

  运行结果
  

• 3.55

  代码：

  SELECT Sno,sname,sdept FROM student WHERE sdept IN (SELECT sdept FROM student WHERE sname='张成民');
  

  运行结果
  

• 3.56

  代码：

  SELECT sno,sname FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno IN (SELECT cno FROM course WHERE cname='数据结构'));
  

  运行结果
  

• 3.57

  代码：

  SELECT sno,cno FROM sc x WHERE grade >= (SELECT AVG(grade) FROM sc y WHERE y.sno=x.sno);
  

  运行结果
  

• 3.58

  代码：

  SELECT sname,sage FROM student WHERE sage<ANY(SELECT sage FROM student WHERE sdept='CS') AND sdept<>'CS';
  

  运行结果
  

• 3.59

  代码：

  SELECT sname,sage FROM student WHERE sage<(SELECT MAX(sage) FROM student WHERE sdept='CS')  AND sdept != 'CS';
  

  运行结果
  

• 3.60

  代码：

  SELECT sname FROM student WHERE EXISTS(SELECT * FROM sc WHERE sno=student.sno AND cno='0202');
  

  运行结果
  

• 3.61

  代码：

  SELECT sname FROM student WHERE NOT EXISTS (SELECT * FROM sc WHERE sno=student.sno AND cno='0202');
  

  运行结果
  

• 3.62

  代码：

  SELECT sname FROM student WHERE NOT EXISTS (SELECT * FROM course WHERE NOT EXISTS (SELECT * FROM sc WHERE sno=student.sno AND cno=course.cno));
  

  运行结果
  

• 3.63

  代码：

  SELECT DISTINCT sno FROM sc scx WHERE NOT EXISTS (SELECT * FROM sc scy WHERE scy.sno='200215126' AND NOT EXISTS (SELECT * FROM sc scz WHERE scz.sno=scx.sno AND scz.cno=scy.cno));
  

  运行结果
  

• 3.64

  代码：

  SELECT * FROM student WHERE sdept='CS' UNION SELECT * FROM student WHERE sage<=19;
  

  运行结果
  

• 3.65

  代码：

  SELECT sno FROM sc WHERE cno='0204' UNION SELECT sno FROM sc WHERE cno='0206';
  

  运行结果
  

• 3.66

  代码：

  SELECT * FROM student WHERE sdept='CS' INTERSECT SELECT * FROM student WHERE sage <= 19;
  

  运行结果
  

• 3.67

  代码：

  SELECT sno FROM SC WHERE cno='0202' INTERSECT SELECT sno FROM sc WHERE cno = '0204';
  

  运行结果
  

• 3.68

  代码：

  SELECT * FROM student WHERE sdept='CS' EXCEPT SELECT * FROM student WHERE sage <= 19;
  

  运行结果