在SM数据库中进行以下操作:
- 查询选修了数据库的学生的学号;
select sc.Sno from sc,course where sc.Cno = Course.Cno and CName='数据库'
- 查询数据库成绩不及格的学生名单(输出学号、姓名、成绩);
select sc.Sno,sc.Score,Student.SName from sc,Student,course where sc.SNo = Student.SNo and sc.Cno = Course.CNo and CName = '数据库' and Score < 60
- 使用内连接查询每个学生及其选修课程情况;
select * from Student inner join sc on Student.SNo = sc.SNo
- 使用左连接查询查询每个学生及其选修课程情况;
select * from Student left join sc on Student.Sno = sc.Sno
- 使用右连接查询查询每个学生及其选修课程情况;
select * from Student right join sc on Student.Sno = sc.Sno
- 在表Teacher中,查询职工张三的上级领导信息;
select tno=mgr,tname=Tname from Teacher where Tname='张三'
- 使用IN查询修读课程名为数据库的所有学生的学号和姓名;
select Student.Sno,Student.SName from Student where Student.Sno in (select sc.Sno from sc where sc.Cno in (select course.Cno from course where CName = '数据库'))
- 使用EXISTS查询所有选修了001号课程的学生的姓名;
select Student.SName from Student where exists (select sc.Cno from sc where sc.Sno=Student.Sno and Cno='001')
- 使用NOT EXISTS查询未选修了001号课程的学生的姓名;
select Student.SName from Student where not exists (select * from sc where sc.Sno=Student.Sno and sc.Cno='001')
- 使用NOT EXISTS查询选修了课程学分为4的全部课程的学生的姓名;
select SName from Student where not exists (select * from course where Credits = '4' and not exists (select * from sc where course.Cno=sc.Cno and sc.Sno=Student.Sno))
- 统计教职工的总人数、最高工资、最少工资、平均工资;
select count(*) as 总人数,Max(sal) as 最大工资,Min(sal) as 最少工资,avg(sal) as 平均工资 from Teacher
- 查询选修两门课程以上的学生学号;
select Sno from sc group by Sno having count(Cno)>=2
- 查询选修了三门课程的学生学号;
select Sno from sc group by Sno having count(Cno)=3
- 使用分组查询选修了所有课程的学生学号、姓名。
select Sno,SName from Student where not exists (select * from course where not exists (select * from sc where sc.Sno=Student.Sno and sc.Cno=course.Cno))
- 在选修课001中,使用ALL查询比学号00001和学号00004的成绩都低的学生学号;
select Sno from sc where Cno = '001' and Score < all (select Score from sc where Cno = '001' and (Sno='00001' or Sno='00004'))
- 在选修课001中,使用ANY查询比学号00001或学号00004的成绩低的学生学号;
select Sno from sc where Cno = '001' and Score < any (select Score from sc where Cno='001' and (Sno='00001' or Sno='00004'))
- 在选修课001中,查询比平均成绩低的学生的学号;
select Sno from sc where Score < (select AVG(Score) from sc) and Cno = '001'
- 给出所有课程(不包括001这门课程)都及格的学生的平均成绩,按平均成绩降序排序;
select Sno,AVG(Score) from sc where Cno<>'001' group by Sno having MIN(Score) >= 60 order by AVG(Score) DESC
- 更新表student中的年龄,年龄为当前年份减去出生年份(提示,使用year());
update Student set SAge = 2022-year(SBir)
- 更新表SC中的成绩,把选修了“数据库”的成绩加1;
update sc set Score = Score + 1 where Cno = (select Cno from course where CName = '数据库')
- 在表student中删除学号为00001的学生信息;
exec sp_helpconstraint sc alter table sc drop constraint FK_sc_Student alteer table sc add foreign key(Sno) references Student(Sno) on delete cascade delete from Student where Student.Sno = '00001'
- 在表course中删除“数据库”课程的信息;
exec sp_helpconstraint sc alter table sc drop constraint FK__sc__CNo__22AA2996 alter table sc add foreign key(CNo) references course (CNo) on delete cascade delete from course where CName = '数据库'
- 在表SC中删除男同学的信息;
delete from sc where SNo in (select SNo from Student where SSex = '男')
- 建立一个系号为03的学生表(学号,姓名),要求把student的系号为03的学生的学号和姓名插入该表(分别使用insert into和select into两种方法创建)
create table 学生表( sdept char(15) default '003', 学号 char(5) not null , 姓名 varchar(8) not null ) insert into 学生表 (学号,姓名) select SNo ,SName from Student
- 快速删除上题建立的表的数据。
TRUNCATE TABLE 学生表
- 建立一个表结构和course一样的表newcourse,表newcourse中不含任何数据。
select * into newcourse from course where 1 = 0