sql常用语句（educoder实验）

1.1)

-- ********** 此处写“1、创建Student表”的SQL语句 ********** --
create table student
(sno char(10) primary key,
sname varchar(20),
ssex char(2),
sage smallint,
sdept varchar(20)
);

-- ********** 此处写“2、创建Course表”的SQL语句 ********** --
create table course
(
    cno char(10) primary key,
    cname varchar(20),
    cpno char(10),
    ccredit smallint
);

-- ********** 此处写“3、创建SC表”的SQL语句 ********** --
create table sc
(
    sno char(10),
    cno char(10),
    grade smallint,
    primary key(sno,cno)
    
);

1.2)

-- ********** 此处写“1、添加phone列”的SQL语句 ********** --
alter table student

-- ********** 此处写“2、删除Cpno列”的SQL语句 ********** --
alter table course drop column cpno;

-- ********** 此处写“3、修改sdept列”的SQL语句 ********** --
alter table student alter column sdept varchar(30);

1.3)

-- ********** 此处写“1、删除三张表”的SQL语句 ********** --
drop table sc ;
drop table student ;
drop table course ;

2.1）

-- ****** 此处写“1、为Student表插入两行”的SQL语句 ****** --
insert into student(sno,sname,ssex,sage,sdept)
values('001','Smith','m',18,'CS')
insert into student(sno,sname,ssex,sage,sdept)
values('002','Ketty','f',19,'MA')

-- ********** 此处写“2、为Course表插入两行”的SQL语句 ********** --

insert into COURSE(cno,cname,cpno,ccredit)

values('C01','DB','NULL',2)

insert into COURSE(cno,cname,cpno,ccredit)

values('C02','Oracle','C01',3)

-- ********** 此处写“3、为SC表插入3行”的SQL语句 ********** --

insert into sc(sno,cno,grade)

values('001','C01',70)

insert into sc(sno,cno,grade)

values('001','C02',82)

insert into sc(sno,cno,grade)

values('002','C01',86)

2.2）

-- ****** 此处写“1、将不及格的学生成绩加5分”的SQL语句 ****** --
update sc
set grade=grade+5
where grade<60;

-- ********** 此处写“2、将CS系男同学的年龄加1”的SQL语句 ********** --
update student
set sage=sage+1
where student.sdept='cs';

-- ********** 此处写“3、将学生的学号前加上‘S’(其中S要大写)”的SQL语句 ********** --
update student
set sno='S'+sno;

2.3）

-- ****** 此处写“1、将学生的学号前的‘S’删掉”的SQL语句 ****** --
update student
set sno=substring(sno,2,4);

-- ****** 此处写“2、在学生学号的后面加上‘S’”的SQL语句 ****** --
update student
set sno = substring(sno,1,4)+'S';

2.4）

-- ****** 此处写“1、在SC表中删除成绩为空的选课信息”的SQL语句 ****** --
Delete from SC 
where grade is null

-- ********** 此处写“2、删除年龄等于18岁的女同学”的SQL语句 ********** --
delete from student 
where sage='18' and ssex='f';

-- ********** 此处写“3、删除学分为3分的课程”的SQL语句 ********** --
delete from course
where ccredit='3'

3.1）

-- ********** 此处写“1、查询CS系男同学的学号，姓名，年龄”的SQL语句 ********** --
select sno,sname,sage
from student
where sdept='cs' and ssex='m'

-- ********** 此处写“2、查询不及格的学生选课信息，列出学号，课程号，成绩”的SQL语句 ********** --
select sno,cno,grade
from sc
where grade<='60'

-- ********** 此处写“3、查询先行课程不为空的课程（使用*表示查询结果）”的SQL语句 ********** --
select * 
from course
where cpno!='*'

-- ********** 此处写“4、查询姓名中带有'n'字母的学生的学号，姓名（使用like语句）”的SQL语句 ********** --
select sno,sname
from student
where sname like '%n%'

-- ********** 此处写“5、使用distinct关键字查询学生表中不同的系，列出系（去除重复元祖）”的SQL语句 ********** --
select distinct sdept from student

3.2

-- ********** 此处写“1、查询90分以上学生的选课信息，列出学号，姓名，课程号，成绩”的SQL语句 ********** --
select Student.sno,Student.sname,SC.cno,SC.grade
from Student,SC
where SC.Grade>90 and SC.Sno=Student.Sno;

-- ********** 此处写“2、查询‘DB’课程的选课情况，列出学号，成绩”的SQL语句 ********** --
select Student.sno,Sc.grade 
from Student,SC,Course 
where Student.sno = Sc.sno and SC.cno=Course.cno and cname = 'DB'

3.3

-- ********** 此处写“1、查询CS系的学生选择‘DB’课程的情况，列出学号，成绩”的SQL语句 ********** --
select Student.sno,SC.grade
from SC,course,Student
where Student.sno=SC.sno and sdept='cs' and SC.cno=course.cno and course.cname='DB'

-- ********** 此处写“2、查询女同学的选课情况，列出学号，课程号，课程名，成绩”的SQL语句 ********** --
select Student.sno,course.cno,course.cname,SC.grade
from Student,SC,course
where Ssex='f' and Student.sno=SC.sno and course.cno=SC.cno 

4.1)

-- ********** 此处写“1、查询CS系学生选择的课程，列出学号，课程号，成绩”的SQL语句 ********** --
select sno,cno,grade 
from Sc 
where Sno in(select Sno from Student where Sdept ='CS');

-- ********** 此处写“2、查询没有选C06（课程号）课程的同学的学号，姓名，性别”的SQL语句 ********** --
select sno,sname,ssex
from student
where Sno not in(select Sno from Sc where Cno='C06');

-- ********** 此处写“3、查询成绩最高的选课信息，列出学号，课程号和成绩”的SQL语句 ********** --
select sno,cno,grade
from sc
where grade in (select max(grade) from sc);

4.2)

-- ********** 此处写“1、查询CS系没有选择'DB'课程学生的姓名，列出学生姓名”的SQL语句 ********** --
select sname from Student 
where sdept='CS' and Sno not in(select Sno from Sc 
                where Cno in(select Cno from Course 
                                where Cname='DB'));

-- ********** 此处写“2、查询'DB'课程考最高分的选课信息。列出学号，课程号，成绩”的SQL语句 ********** --
select sno,cno,grade 
from SC 
where grade in (select max(grade) from SC
                 where cno in(select cno from Course where Cname='DB'))and 
                 cno in (select cno from Course where Cname='DB');

4.3)

- ********** 此处写“1、查询选修了先行课为'DB'的课程的学生，列出学生学号，姓名，性别，所在系”的SQL语句 ********** --
select sno,sname,ssex,sdept 
from Student 
where  Sno in (select Sno from SC 
               where Cno in (select  Cno  from Course 
                              where Cpno in (select Cno from Course  
                                               where Cname ='DB' )));

4.4)

-- ********** 此处写“1、将'DB'课程不及格的成绩加5分”的SQL语句 ********** --
update sc
set grade=grade+5
where grade<60 and cno in(select cno from course where cname='DB')

-- ********** 此处写“2、删除'English'（课程名）课程CS系学生的选课记录”的SQL语句 ********** --

delete from SC
where Cno in (select cno from Course where cname='English') and 
      sno in (select sno from Student where Sdept='CS');

-- ********** 此处写“3、为CS系添加必修课,课程号为C02”的SQL语句 ********** --
insert into SC 
  Select sno,'C02',null
  from Student 
  where Sdept='CS' and not exists 
         (select * from sc where sno=student.sno and cno='C02');