Single-Table Query

1 USE student_course
2   -- -001查询全体学生的学号与姓名-------------------------------------------------------------
3 SELECT Sno,Sname FROM student;
4 -- -002查询全体学生的姓名、学号及所在系。-----------------------------------------------------
5 SELECT Sname,Sno,Sdept FROM Student;
6
7 -- -003查询全体学生的详细记录---------------------------------------------------------------
8 SELECT * FROM Student;
9
10 -- -004查询全体学生的姓名及其出生年份--------------------------------------------------------
11 -- -说明：在实验一时已经修改过学生年龄
12 SELECT Sname, 2010 - Sage AS 出生年份 FROM Student;
13
14 -- -005查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有-------------------------------
15 SELECT Sname, ' year of birth ' AS Describe , 2010 - Sage birth_year, LOWER (Sdept) department
16 FROM Student;
17
18 -- -006查询选修了课程的学生学号-------------------------------------------------------------
19 SELECT Sno FROM SC; /* 方法一 */
20 SELECT ALL Sno FROM SC; /* 方法二 */
21
22 SELECT DISTINCT Sno FROM SC; /* 用关键字DISTINCT去掉重复行 */
23
24 -- -007查询计算机系全体学生的名单（仅列出姓名）--------------------------------------------------
25 SELECT Sname FROM Student WHERE Sdept = ' CS ' ;
26
27 -- -008查询所有年龄在岁以下的学生姓名及其年龄--------------------------------------------------
28 SELECT Sname,Sage FROM Student WHERE Sage <= 20 ;
29
30 -- -009查询考试成绩有不及格的学生的学号-------------------------------------------------------
31 INSERT INTO SC(Sno,Cno,Grade) VALUES ( ' 200215125 ' , ' 1 ' , 56 );
32 INSERT INTO SC(Sno,Cno,Grade) VALUES ( ' 200215126 ' , ' 2 ' , 45 );
33 SELECT * FROM SC;
34 SELECT Sno FROM SC WHERE Grade < 60 ;
35
36 -- -010查询年龄在-23岁（包括与岁）之间的学生的姓名、系别和年龄--------------------------------------
37 SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23 ; SELECT * FROM STUDENT ;
38
39 -- -011查询年龄不在-23岁（包括与岁）之间的学生的姓名、系别和年龄------------------------------------
40 SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 30 ;
41
42 -- -012查询信息系（IS）、数学系（MA）、和计算机科学系（CS）学生的姓名和性别------------------------
43 UPDATE Student
44 SET Sdept = ' IS '
45 SELECT * FROM SC WHERE Sno = ' 200215125 ' ; /* 把原TS修改为IS */
46 SELECT Sname,Ssex FROM Student WHERE Sdept IN ( ' IS ' , ' MA ' , ' CS ' );
47
48 -- -013查询既不是信息系（IS）、数学系（MA）、也不是计算机科学系（CS）学生的姓名和性---------------------
49 SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ( ' IS ' , ' MA ' , ' CS ' );
50
51 -- -014查询学号为的学生的详细情况------------------------------------------------------------
52 SELECT * FROM Student WHERE Sno = ' 200215122 ' ;
53 SELECT * FROM Student WHERE Sno LIKE ' 200215122 ' ;
54
55 -- -015查询所有姓刘学生的姓名、学号和性别------------------------------------------------------
56 SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE ' 刘% ' ;
57
58 -- -016查询姓‘欧阳’并全名为三个汉字的学生的姓名------------------------------------------------
59 INSERT INTO Student VALUES ( ' 200215127 ' , ' 欧阳峰 ' , ' 男 ' , 28 , ' CS ' );
60 SELECT * FROM Student;
61 SELECT Sname FROM Student WHERE Sname LIKE ' 欧阳_ ' ;
62
63
64 -- -017查询名字中第二个字为“阳”字的学生的姓名和学号--------------------------------------------
65 SELECT Sname,Sno FROM Student WHERE Sname LIKE ' _阳% ' ;
66
67 -- -018查询所有不姓刘的学生姓名-------------------------------------------------------------
68 SELECT Sname FROM Student WHERE Sname NOT LIKE ' 刘% ' ;
69
70 -- -019查询DB—Design课程的课程号和学分------------------------------------------------------
71 INSERT INTO Course VALUES ( ' 8 ' , ' DB_Design ' , ' 7 ' , 4 );
72 SELECT * FROM Course;
73 SELECT Cno,Ccredit FROM Course WHERE Cname LIKE ' DB\_Design ' ESCAPE ' \ ' ;
74
75 -- -020查询以DB—开头，且倒数第三个字符为i的课程的详细情况--------------------------------------
76 SELECT * FROM Course;
77 SELECT *
78 FROM Course
79 WHERE Cname LIKE ' DB\_%i__ ' ESCAPE ' \ ' ; /* ESCAPE ‘\’表示"\"转换为码字符 */
80 DELETE FROM Course WHERE Cname = ' DB_Design ' ;
81
82 -- -021某些学生选修课程后没有参加考试，所以有选课记录在案，但没有考试成绩。-------------------------
83 -- -查询缺少成绩的学生的学号和相应的课程号
84 INSERT INTO SC(Sno,Cno) VALUES ( ' 200215123 ' , ' 1 ' );
85 INSERT INTO SC(Sno,Cno) VALUES ( ' 200215125 ' , ' 2 ' );
86 SELECT * FROM SC;
87 SELECT Sno,Cno FROM SC WHERE Grade IS NULL ;
88
89 -- -022查询所有有成绩的学生学号和课程号------------------------------------------------------
90 SELECT * FROM SC ;
91 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL ;
92 DELETE FROM SC WHERE Sno = ' 200215123 ' OR Sno = ' 200215125 ' ;
93
94 -- -023查询计算机系年龄在岁以25下的学生姓名---------------------------------------------------
95 SELECT Sname FROM Student WHERE Sdept = ' IS ' AND Sage < 25 ;
96
97 -- -024查询选择修了3号课程的学生的学号及其成绩，查询结果按分数降序排列------------------------------
98 SELECT * FROM SC ;
99 SELECT Sno,Grade FROM SC WHERE Cno = ' 3 ' ORDER BY Grade DESC ;
100
101 -- -025查询全体学生情况，查询结果按所在系的系号升序排列-----------------------------------------
102 SELECT * FROM Student;
103 SELECT * FROM Student ORDER BY Sdept ASC ,Sage DESC ;
104
105 -- -026查询学生总人数----------------------------------------------------------------------
106 SELECT * FROM Student ;
107 SELECT COUNT ( * ) AS 学生总人数FROM Student ;
108
109 -- -027查询选修了课程的学生人数-------------------------------------------------------------
110 SELECT * FROM SC ;
111 SELECT COUNT ( DISTINCT Sno) AS 选修了课程的学生人数总和FROM SC ;
112
113 -- -028计算号课程的学生平均成绩-------------------------------------------------------------
114 INSERT INTO SC VALUES ( ' 200215122 ' , ' 1 ' , 28 );
115 SELECT * FROM SC ;
116 SELECT AVG (Grade) AS 第号课程的学生平均成绩FROM SC WHERE Cno = ' 1 ' ;
117
118 -- -029查询选修号课程的学生最高分数---------------------------------------------------------
119 SELECT * FROM SC ;
120 SELECT MAX (Grade) AS 第号课程的学生最高分数 FROM SC WHERE Cno = ' 1 ' ;
121
122 -- -030查询学生选修课程的总学分数------------------------------------------------------------
123 SELECT * FROM Course ;
124 SELECT * FROM SC ;
125 SELECT SUM (Ccredit) AS 学生选修课程的总学分数
126 FROM Course,SC
127 WHERE Sno = ' 200215121 ' AND Course.Cno = SC.Cno;
128
129 -- -031求各个课程号及相应的选课人数---------------------------------------------------------
130 SELECT * FROM SC ;
131 SELECT Cno, COUNT (Sno) AS 各个课程号及相应的选课人数FROM SC GROUP BY Cno;
132
133 -- -032查询选修了门以上课程的学生学号--------------------------------------------------------
134 SELECT * FROM SC ;
135 SELECT Sno FROM SC ;
136 SELECT Sno FROM SC GROUP BY Sno;
137 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT ( * ) >= 3 ;
138

 

转载于:https://www.cnblogs.com/FYSUIT/archive/2010/11/27/1890003.html