Select * from Student
Select * From Course
Select * from SC
--子查询 低于总平均成绩的女同学成绩
Select Grade
from Student, SC
Where Student.Sno = SC.Sno and Student.Sex = '女' and
Grade < (Select AVG(Grade) from SC)
--(1).把低于总平均成绩的女同学成绩提高5%
Update SC
SET Grade = Grade * (1+0.05)
From SC, Student
Where Student.Sno = SC.Sno and Student.Sex = '女' and
SC.Grade < (Select AVG(Grade) from SC)
Select * from SC
--(2).把SC复制为SC1
--先创建一个表,这里已经复制了,下面那个复制全部数据语句可以省略
Select *
Into SC1
From SC
--复制全部数据
Insert Into SC1
Select * From SC1
--(3)从SC1中删除刘晨所有选课记录
DELETE
FROM SC1
FROM Student
WHERE SC1.Sno = Student.Sno and Student.Sname = '刘晨'
Select *
From SC1
--(4).检索每一门课程成绩都大于等于80分的学生学号、姓名和性别,并把检索到的值送往
--另一个已存在的基本表s1(Sno,SNAME,SEX),如果s1不存在,自己创建
--先创建s1
Select * Into s1
FROM Student
--插入数据
INSERT INTO s1(Sno, SNAME, SEX)
Select distinct SC.Sno, Sname, Sex
From SC, Student
Where SC.Sno = Student.Sno and
SC.Sno NOT IN (Select Sno
From SC
Where SC.Grade < 80)
Select * from SC
--(5)创建选课数少于3门的学生的视图SC_3(sno,sname)
Create View SC_3(sno, sname)
AS
Select SC.Sno, Sname
From SC, Student
Group by SC.Sno, Student.Sname, Student.Sno
Having Count(*) < 3 and SC.Sno = Student.Sno
--子查询 选课数少于3门的学生
Select SC.Sno, Count(*) as 选课数
From SC
Group by SC.Sno
Having Count(*) < 3
select * From SC_3
--作业二
select * from Student select * from Course select * from SC --(1)取出没有选修‘操作系统’课程的学生姓名和年龄 Select distinct SC.Sno, Sname, Sage From Student, Course, SC Where Student.Sno = SC.Sno and Course.Cno = SC.Cno and SC.Sno not in( select distinct SC.Sno From Course, SC where Course.Cno = SC.Cno and Course.Cname = '操作系统') --(2)检索至少选修课程“数据结构”和“C语言”的学生学号。 Select distinct SC.Sno From Course, SC where SC.Cno = Course.Cno and Course.Cname in ('数据结构', 'C语言') --(3)检索和“刘晨”同性别并同系的同学的姓名。 select Sname from Student Where Sex = ( Select Sex From Student Where Sname = '刘晨') and Sdept = ( Select Sdept From Student Where Sname = '刘晨') --(4)求选修课程名为"数据结构"课程的学生的平均年龄; Select AVG(Sage) from Student, Course, SC Where Student.Sno = SC.Sno and Course.Cno = SC.Cno and Course.Cname = '数据结构' --(5)查询没有选课的学生的学号和姓名 Select Sno, Sname From Student Where Student.Sno not in ( select distinct SC.Sno From Student, SC where Student.Sno = SC.Sno )