一些SQL语句的实现

----------------------习题三-----------------------
------第一题------
--1.在数据库中创建三个表
create table students  (
SNO varchar2(10) primary key,
SNAME varchar2(10) not null,
SEX varchar2(2) check(sex in('男','女')),
AGE number
)--创建students表
create table course(
cno varchar2(20) primary key,
cname varchar2(20) not null,
credit number(20) not null
)--创建course表
create table sc (
sno varchar2(10) primary key,
cno varchar2(20),
grade number(5,2)
)--创建sc表
--3.创建一个视图test，视图的功能为：查询所有学生的姓名，所选课程名称和成绩
create view test  
as select sname,cname,grade from students,course,sc 
where students.sno=sc.sno 
and course.cno=sc.cno


----------------第二题----------------
create table student (
studentid varchar(10) primary key,
name varchar(10)  not null,
sex varchar(2) check(sex in('男','女')),
classid varchar(10),
depid varchar(10),
address varchar(20),
studate date not null
)--创建student表
create table sc
(studentid varchar(10) ,
courseid varchar(10),
grade number(5,2),
primary key (studentid,courseid),
constraint S_PK  foreign key(studentid) references student(studentid),
constraint C_PK foreign key(courseid) references course(courseid)
)--创建sc表
create table course(
courseid varchar(10) primary key,
studentid varchar2(20) 
coursename varchar(20),
teacher varchar(10)
)--创建course表
create table dep(
depid varchar(10) primary key,
depname varchar2(20)
)--创建dep表


--往student表里插入数据
insert into student values('011110','李建国','男','计0121','01','湖北武汉',to_date('1984-09-28','yyyy-mm-dd'))
insert into student values('011103','李宁','女','电0134','02','江西九江',to_date('1985-05-06','yyyy-mm-dd'))


--往sc表里插入数据
insert into sc values ('011110','01','50')
insert into sc values ('021204','02','70')
insert into sc values ('011103','03','90')


--往course表里插入数据
insert into course values('01','英语','刘江虎')
insert into course values('02','数学','李小则')
insert into course values('03','c语言','何晓敏')
insert into course values('04','数据库','张超')


--往dep表里插入数据
insert into dep values ('01','计算机系')
insert into dep values('02','机电系')
insert into dep values('03','英语系')


--1.查询计算机系所有学生的成绩，并按学号排序
select distinct * from student where depid in(select depid from dep where depname='计算机系')
order by student.studentid
--2.查询所有学生的学号、姓名、所选课程的课程名、所选课程的成绩
select distinct student.studentid,student.name,course.coursename,sc.grade 
from student,course,sc 
where student.studentid=sc.studentid 
and course.courseid=sc.courseid
--3.删除学生“李宁”的信息及选课记录
delete from sc where studentid in(select studentid from student where name='李宁')
delete  from student where name='李宁'
--4.插入新的学生信息(031259,张明，01)
insert into  student(studentid,name,depid,studate) values ('031259','张明','01',to_date('1990-02-06','yyyy-mm-dd'))
select * from student
--5.查询李宁所在系的所有学生信息
select distinct * from student where depid in(
select depid from student where name='李宁' 
)
alter table student modify studate null--修改student表里的字段类型
--6.更新‘李建国’的学号为021110
update student studentid set studentid='021110' where name='李建国'
--7.创建‘计算机系’的学生信息视图
create view J_student as select * from student where depid in(
select depid from dep where depname='计算机系'
)
select * from J_student
--8.按学生姓名创建索引
create index index_stuName on student(name)
--9.计算03号课程的最高分
select max(grade) from Sc_q
--10.统计选修03课程的学生人数

select count(courseid) as 人数 from Sc_q where courseid='03' group by courseid

--创建视图
create view bookwucview
as 
select * from bookwuc where bookwuc.bookid='Tp_2010-002'
--删除视图--
drop view bookwucview

declare
begin
exception
end
---------------------------------
select 'abc' || '123'from dual
---------------------------------
declare
x number;
y number;
z number;
pi constant number:=3.1415926;
begin
x:=100;
y:=200;
z:=x+y;
dbms_output.put_line(z);
dbms_output.put_line(pi);
end;
---------------------------------
declare
x number;
y number;
z number;
begin
x:=100;
y:=200;
z:=300;
if x>y and x>z then
dbms_output.put_line(x);
elsif  y>x and y>z then
dbms_output.put_line(y);
else
dbms_output.put_line(z);
end if;
end;




declare
i int;
begin
i:=7;
if i=1 then
dbms_output.put_line('吃青菜');
elsif i=2 then
dbms_output.put_line('吃鱼');
elsif i=3 then
dbms_output.put_line('吃地瓜');
elsif i=4 then
dbms_output.put_line('吃鸡');
elsif i=5 then
dbms_output.put_line('吃水饺');
elsif i=6 then
dbms_output.put_line('吃排骨');
else
dbms_output.put_line('吃猪蹄');
 end if;
 end;