例程练习1

openGauss使用练习

代码及运行结果

• 3.16

  代码:

  SELECT Sno,Sname FROM student; 
  

  运行结果：
  

• 3.17

  代码:

  SELECT Sname,Sno,Sdept FROM student; 
  

  运行结果：
  

• 3.18

  代码:

  SELECT * FROM student; 
  

  运行结果：
  

• 3.19

  代码:

  SELECT Sname,2014-Sage FROM student; 
  

  运行结果：
  

• 3.20

  代码:

  SELECT Sname,'Year of Birth',2014-Sage,LOWER(Sdept) FROM student; 
  

  运行结果：
  
  代码:

  SELECT Sname NAME,'Year of Birth' BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT FROM student;
  

  运行结果：
  

• 3.21

  代码:

  SELECT Sno FROM sc;
  

  运行结果：
  
  代码:

  SELECT DISTINCT Sno FROM sc;
  

  运行结果：
  

• 3.22

  代码:

  SELECT Sname FROM student WHERE Sdept='CS';
  

  运行结果：
  

• 3.23

  代码:

  SELECT Sname,Sage FROM student WHERE Sage<20;
  

  运行结果：
  

• 3.24

  代码:

  SELECT DISTINCT Sno FROM SC WHERE Grade<60;
  

  运行结果：
  

• 3.25

  代码:

  SELECT Sname,Sdept,Sage FROM student WHERE Sage NOT BETWEEN 20 AND 23;
  

  运行结果：
  

• 3.26

  代码:

  SELECT Sname,Sdept,Sage FROM student WHERE Sage  BETWEEN 20 AND 23;
  

  运行结果：
  

• 3.27

  代码:

  SELECT Sname,SSex FROM student WHERE Sdept IN ('CS','MA','IS');
  

  运行结果：
  

• 3.28

  代码:

  SELECT Sname,SSex FROM student WHERE Sdept NOT IN ('CS','MA','IS');
  

  运行结果：
  

• 3.29

  代码:

  SELECT * FROM student WHERE Sno LIKE '200215137';
  SELECT * FROM student WHERE Sno='200215137';
  

  运行结果：
  

• 3.30

  代码:

  SELECT Sname,Sno,SSex FROM student WHERE Sname LIKE'刘%';
  

  运行结果：
  

• 3.31

  代码:

  SELECT Sname FROM student WHERE sname LIKE '欧阳____________';
  

  运行结果：
  

• 3.32

  代码:

  SELECT Sname,sno FROM student WHERE sname LIKE '_阳%';
  

  运行结果：
  

• 3.33

  代码:

  SELECT Sname,sno,ssex FROM student WHERE sname NOT LIKE '刘%';
  

  运行结果：
  

• 3.34

  代码:

  SELECT cno,ccredit FROM course WHERE cname LIKE '数据库\_设计%' ESCAPE'\';
  

  运行结果：
  

• 3.35

  代码:

  SELECT * FROM course WHERE cname LIKE 'db\_%i__%' ESCAPE'\';
  

  运行结果：
  

• 3.36

  代码:

  SELECT Sno,Cno FROM sc WHERE grade IS NULL;
  

  运行结果：
  

• 3.37

  代码:

  SELECT Sno,Cno FROM sc WHERE grade IS NOT NULL;
  

  运行结果：
  

• 3.38

  代码:

  SELECT sname FROM student WHERE sdept='CS' AND Sage<20;
  

  运行结果：
  

• 3.39

  代码:

  SELECT sno,grade FROM sc WHERE cno='0204' ORDER BY grade DESC;
  

  运行结果：
  

• 3.40

  代码:

  SELECT * FROM student ORDER BY sdept,sage DESC;
  

  运行结果：
  

• 3.41

  代码:

  SELECT COUNT(*) FROM student;
  

  运行结果：
  

• 3.42

  代码:

  SELECT COUNT(DISTINCT sno) FROM sc;
  

  运行结果：
  

• 3.43

  代码:

  SELECT AVG(grade) FROM sc WHERE cno='0202';
  

  运行结果：
  

• 3.44

  代码:

  SELECT MAX(grade) FROM sc WHERE cno='0202';
  

  运行结果：
  

• 3.45

  代码:

  SELECT SUM(ccredit) FROM sc,course WHERE sno='200215126' AND sc.cno = course.cno;
  

  运行结果：
  

• 3.46

  代码:

  SELECT cno,COUNT(sno) FROM sc GROUP BY cno;
  

  运行结果：
  

• 3.47

  代码:

  SELECT sno FROM sc GROUP BY sno HAVING COUNT(*)>3;
  

  运行结果：
  

• 3.48

  代码:

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

  运行结果：