Single-Table Query

USE student_course
 ---001查询全体学生的学号与姓名-------------------------------------------------------------
SELECT Sno,Sname    FROM student;
---002查询全体学生的姓名、学号及所在系。-----------------------------------------------------
SELECT Sname,Sno,Sdept    FROM Student;
 
---003查询全体学生的详细记录---------------------------------------------------------------
SELECT *    FROM Student;
 
---004查询全体学生的姓名及其出生年份--------------------------------------------------------
---说明：在实验一时已经修改过学生年龄
SELECT Sname,2010-Sage AS 出生年份    FROM Student;
 
---005查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有-------------------------------
SELECT Sname,'year of birth' AS Describe ,2010-Sage birth_year,LOWER (Sdept) department
FROM Student;
 
---006查询选修了课程的学生学号-------------------------------------------------------------
SELECT Sno FROM SC;/*方法一*/
SELECT ALL Sno FROM SC; /*方法二*/
 
SELECT  DISTINCT  Sno FROM SC; /*用关键字DISTINCT去掉重复行*/
 
---007查询计算机系全体学生的名单（仅列出姓名）--------------------------------------------------
SELECT Sname FROM Student WHERE Sdept='CS';
 
---008查询所有年龄在岁以下的学生姓名及其年龄--------------------------------------------------
SELECT Sname,Sage FROM Student WHERE Sage<=20;
 
---009查询考试成绩有不及格的学生的学号-------------------------------------------------------
INSERT INTO SC(Sno,Cno,Grade)VALUES('200215125','1',56);
INSERT INTO SC(Sno,Cno,Grade)VALUES('200215126','2',45);
SELECT * FROM SC;
SELECT Sno FROM SC WHERE Grade<60;
 
---010查询年龄在-23岁（包括与岁）之间的学生的姓名、系别和年龄--------------------------------------
SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23; SELECT * FROM STUDENT ;
 
---011查询年龄不在-23岁（包括与岁）之间的学生的姓名、系别和年龄------------------------------------
SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 30;
 
---012查询信息系（IS）、数学系（MA）、和计算机科学系（CS）学生的姓名和性别------------------------
UPDATE Student
SET Sdept='IS'
SELECT * FROM SC WHERE Sno='200215125';/*把原TS修改为IS*/
SELECT Sname,Ssex FROM Student WHERE Sdept IN ('IS','MA','CS');
 
---013查询既不是信息系（IS）、数学系（MA）、也不是计算机科学系（CS）学生的姓名和性---------------------
SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ('IS','MA','CS');
 
---014查询学号为的学生的详细情况------------------------------------------------------------
SELECT * FROM Student WHERE Sno='200215122';
SELECT * FROM Student WHERE Sno LIKE '200215122';
 
---015查询所有姓刘学生的姓名、学号和性别------------------------------------------------------
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';
 
---016查询姓‘欧阳’并全名为三个汉字的学生的姓名------------------------------------------------
INSERT INTO Student VALUES('200215127','欧阳峰','男',28,'CS');
SELECT * FROM Student;
SELECT Sname FROM Student WHERE Sname LIKE '欧阳_' ;
 
 
---017查询名字中第二个字为“阳”字的学生的姓名和学号--------------------------------------------
SELECT Sname,Sno FROM Student WHERE Sname LIKE '_阳%';
 
---018查询所有不姓刘的学生姓名-------------------------------------------------------------
SELECT Sname    FROM Student    WHERE Sname NOT LIKE '刘%';
 
---019查询DB—Design课程的课程号和学分------------------------------------------------------
INSERT INTO Course VALUES('8','DB_Design','7',4);
SELECT * FROM Course;
SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
 
---020查询以DB—开头，且倒数第三个字符为i的课程的详细情况--------------------------------------
SELECT * FROM Course;
SELECT * 
FROM Course 
WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';/*ESCAPE ‘\’表示"\"转换为码字符*/
DELETE FROM Course WHERE Cname ='DB_Design';
 
---021某些学生选修课程后没有参加考试，所以有选课记录在案，但没有考试成绩。-------------------------
---查询缺少成绩的学生的学号和相应的课程号
INSERT INTO SC(Sno,Cno) VALUES ('200215123','1');
INSERT INTO SC(Sno,Cno) VALUES ('200215125','2');
SELECT * FROM SC;
SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
 
---022查询所有有成绩的学生学号和课程号------------------------------------------------------
SELECT * FROM SC ;
SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;
DELETE FROM SC WHERE Sno='200215123' OR Sno='200215125';
 
---023查询计算机系年龄在岁以25下的学生姓名---------------------------------------------------
SELECT Sname FROM Student WHERE Sdept='IS' AND Sage<25;
 
---024查询选择修了3号课程的学生的学号及其成绩，查询结果按分数降序排列------------------------------
SELECT * FROM SC ;
SELECT Sno,Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;
 
---025查询全体学生情况，查询结果按所在系的系号升序排列-----------------------------------------
SELECT * FROM Student;
SELECT * FROM Student ORDER BY Sdept ASC ,Sage DESC;
 
---026查询学生总人数----------------------------------------------------------------------
SELECT * FROM Student ;
SELECT COUNT(*) AS 学生总人数FROM Student ;
 
---027查询选修了课程的学生人数-------------------------------------------------------------
SELECT * FROM SC ;
SELECT COUNT(DISTINCT Sno)AS 选修了课程的学生人数总和FROM SC ;
 
---028计算号课程的学生平均成绩-------------------------------------------------------------
INSERT INTO SC VALUES ('200215122','1',28);
SELECT * FROM SC ;
SELECT AVG(Grade) AS 第号课程的学生平均成绩FROM SC WHERE Cno='1';
 
---029查询选修号课程的学生最高分数---------------------------------------------------------
SELECT * FROM SC ;
SELECT MAX(Grade)AS 第号课程的学生最高分数 FROM SC WHERE Cno='1';
 
---030查询学生选修课程的总学分数------------------------------------------------------------
SELECT * FROM Course ;
SELECT * FROM SC ;
SELECT SUM(Ccredit) AS 学生选修课程的总学分数 
FROM Course,SC 
WHERE Sno='200215121' AND Course.Cno=SC.Cno;
 
---031求各个课程号及相应的选课人数---------------------------------------------------------
SELECT * FROM SC ;
SELECT Cno,COUNT(Sno)AS 各个课程号及相应的选课人数FROM SC GROUP BY Cno;
 
---032查询选修了门以上课程的学生学号--------------------------------------------------------
SELECT * FROM SC ;
SELECT Sno FROM SC ;
SELECT Sno FROM SC GROUP BY Sno;
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>=3;