例程练习2

openGauss使用练习

代码及运行结果

• 3.69

  代码：

  INSERT INTO student(sno,sname,ssex,sage,sdept) VALUES ('200215149','陈冬东','男',23,'CS');
  

  运行结果
  

• 3.70

  代码：

  INSERT INTO student VALUES ('200215142','陈冬','男',22,'CT');
  

  运行结果
  

• 3.71

  代码：

  INSERT INTO sc(sno,cno) VALUES ('200215149','0209');
  

  运行结果
  

• 3.72

  代码：

  INSERT INTO dept_age (Sdept,avg_age) SELECT Sdept,AVG(Sage) FROM student GROUP BY sdept;
  

  运行结果
  
  

• 3.73

  代码：

  UPDATE student SET sage=33 WHERE sno='200215137';
  

  运行结果
  

• 3.74

  代码：

  UPDATE student set sage=sage+1;
  

  运行结果
  

• 3.75

  代码：

  UPDATE sc SET grade=0 WHERE sno IN (SELECT sno FROM student WHERE sdept='IS');
  

  运行结果
  

• 3.76

  代码：

  DELETE from student WHERE sno='200213147';
  

  运行结果
  

• 3.77

  代码：

  DELETE FROM dept_age;
  

  运行结果
  

• 3.78

  代码：

  DELETE from sc WHERE sno IN (SELECT sno FROM student WHERE sdept='IS');
  

  运行结果
  

• 3.79

  代码：

  INSERT into sc (sno,cno,grade) VALUES('200213151','0223',NULL);
  

  运行结果
  

• 3.80

  代码：

  UPDATE student SET sdept = NULL WHERE sno='200215137'; 
  

  运行结果
  

• 3.81

  代码：

  SELECT * FROM student WHERE sname IS NULL OR sno IS NULL OR ssex IS NULL OR sdept IS NULL;
  

  运行结果
  

• 3.82

  代码：

  SELECT sno FROM sc WHERE grade<60 AND cno='0207';
  

  运行结果
  

• 3.83

  代码：

  SELECT sno FROM sc WHERE cno='0207' AND (grade<60 OR grade is NULL);
  

  运行结果
  

• 3.84

  代码：

  CREATE VIEW IS_student AS SELECT sno,sname,sage FROM student where sdept='IS';
  

  运行结果
  

• 3.85

  代码：

  CREATE VIEW IS_student1 AS SELECT sno,sname,sage FROM student WHERE sdept='IS' WITH CHECK OPTION;
  

  运行结果
  
  说明：openGauss不支持‘WITH CHECK OPTION’子句。

• 3.86

  代码：

  CREATE VIEW IS_s1 AS SELECT student.sno,sname,grade FROM student,sc WHERE sdept='IS' AND student.sno=sc.sno AND sc.cno='0204';
  

  运行结果
  

• 3.87

  代码：

  CREATE VIEW IS_s2 AS SELECT sno,sname,grade FROM IS_s1 WHERE grade>=90;
  

  运行结果
  

• 3.88

  代码：

  CREATE VIEW BT_s(sno,sname,sbirth) AS SELECT sno,sname,2014-sage FROM student;
  

  运行结果
  

• 3.89

  代码：

  CREATE VIEW s_G(sno,Gavg) AS SELECT sno,AVG(grade) FROM sc GROUP BY sno;
  

  运行结果
  

• 3.90

  代码：

  CREATE VIEW F_student(F_sno,name,sex,age,dept) AS SELECT * FROM student WHERE ssex='女';
  

  运行结果
  

• 3.91

  代码：

  DROP VIEW is_s1 CASCADE;
  

  运行结果
  

• 3.92

  代码：

  SELECT F_sno,age FROM f_student WHERE age<20;
  

  运行结果
  

• 3.93

  代码：

  SELECT cs_student.sno,sname FROM cs_student,sc WHERE cs_student.sno=sc.sno AND sc.cno='0204';
  

  运行结果
  

• 3.94

  代码：

  SELECT Sno,AVG(grade) FROM sc GROUP BY Sno HAVING AVG(grade) >= 90;
  

  运行结果
  

• 3.95

  代码：

  UPDATE student SET sname='欧阳娜拉' WHERE sno='200213157' AND ssex='女' ;
  

  运行结果
  

• 3.96

  代码：

  INSERT INTO student VALUES('200215199','张三','男',88,'CS');
  

  运行结果