数据库基础知识

id varchar2(20)name varchar2(20)
addr varchar2(50)
score number

create table test(id varchar2(20),name varchar2(20),
addr varchar2(50),
score number)

alter table test add constraint pk_id primary key(id)

select * from test where score>60 order by score desc

insert into test(id,name,addr,score) values ('001','zhansgan','shenzhen','80')

update test set score=60 where name='zhangsan'

delete from test where name='zhangsan'

Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表

select s#,avg(score) from sc where (select avg(score) from sc)>60  group by s# ;

select s#,avg(score) from sc where   group by s#  having avg(score)>60;

select Student.s#,sname,count(C#),sum(score) from Student inner join sc on Student.s#=sc.s# group by Student.s#,sname

select count(*) from teacher where tname like'李%'

select S#,Sname from Student where s# in (select S# from sc where c# not in (select c# from Course where T#=(select T# from teacher where Tname='叶平' ))

select  S#,Sname from   Student where s# not in (select S# from sc where score>60)