SQL server (oracle)语句练习案例

create table Teacher(
Tno integer ,
Tname char(6) ,
Title char(6),
Dept char(10));
create table Teacher(
Tno integer Primary Key,
Tname char(6) not null,
Title char(6),
Dept char(10));

insert into Teacher
values
(101,'李华','讲师','计算机');
insert into Teacher
values
(102,'张丽','讲师','通信');
insert into Teacher
values
(103,'刘力伟','助教','计算机');
insert into Teacher(Tno,Tname,Dept)
values
(104,'李春生','计算机');
insert into Teacher(Tno,Tname,Dept)
values
(105,'王华英','自动化');

/*select * from teacher;*/
/*select * from teacher where dept='通信';*/
/*select distinct dept from teacher;*/
/*select count(*) from teacher;*/
/*select count(distinct dept) from teacher;*/
/*select * from teacher aa,teacher bb where aa.tno=bb.tno;*/

create Table Course(
Cno integer not null,
Tno integer not null,
Cname char(10) not null,
credit numeric(3,1) not null,
Primary key(cno,tno));

insert into Course
values(1,101,'数据库',3.5);
insert into Course
values(1,103,'数据库',3.5);
insert into Course
values(2,102,'网络',3);
insert into Course
values(2,101,'网络',3);
insert into Course
values(3,103,'操作系统',3);

select * 
from teacher,course;
select * 
from teacher,course
where teacher.tno=course.tno;

//select cname from course ;
//select distinct cname from course;
select * from teacher;

//update teacher
  //set dept='通信工程'
  //where dept='通信';

//delete from teacher where dept='计算机';

//select * from course where credit >3;
//select * from course where credit between 2 and 3;
//select * from teacher where dept in('计算机','自动化' ) ;
//select * from teacher where dept not in('计算机') ;
//select * from teacher where tname like '李%' ;
//select * from teacher where title is null ;
//select * from teacher order by tno desc ;
//select * from teacher order by title ;
//select count(*) from teacher;
//select count(distinct cname) from course;
select * from course aa, course bb
where aa.tno=bb.tno;

//select * from course
//    where Tno in ( select Tno 
//                    from Teacher
//                     where Tname='李华');
//
//
//select * from teacher,course
//   where (teacher.tno=course.tno) and Tname='李华';
//
//select * from course
//    where Tno in ( select Tno 
//                    from Teacher
//                     where Title='讲师');
select * from teacher,course
   where (teacher.tno=course.tno) and Title='讲师';

select Distinct Tno from course
  where 2<=(select count(*) from Course aa
                where aa.Tno=course.tno);
//select count(*) from Course aa
//                where Tno=102;
//

create view v_t_c
   as 
      select Teacher.Tno,Tname,Title,Dept,Cno,Cname
        from Teacher,course
          where Teacher.Tno=course.Tno;

Select * from v_t_c;
Select * from v_t_c where Tno=101;

create table Teacher(
Tno integer Primary Key,
Tname char(6) not null,
Title char(6),
Dept char(10));
insert into Teacher
values
(901,'李华','讲师','计算机');
insert into Teacher
values
(902,'张丽','讲师','通信');
insert into Teacher
values
(903,'刘力伟','助教','计算机');
insert into Teacher
values
(904,'赵莺',null,'计算机');
insert into Teacher
values
(905,'张大军',null,null);
select * from teacher;
Select * from teacher where title is null;
select * from teacher where dept is not null;

create table father_t
(Cno integer primary key,
 Cname char(10) not null,
 Credit numeric(3,1) );
insert into father_t
values
(1,'数据库',2);
insert into father_t
values
(2,'网络',3);

create table son_t
(st_no integer primary key,
 fk_cno integer,
 grade integer,
 foreign key(fk_cno)
 references father_t(Cno));
insert into son_t
values
(101,2,86);
insert into son_t
values
(102,5,78);

select * from teacher;
select title,count(*) from teacher group by title ;
select title,count(*) from teacher group by title having count(*)>1;

create table Teacher(
Tno integer Primary Key,
Tname char(6) not null,
Title char(6),
Dept char(10));
insert into Teacher
values
(101,'李华','讲师','计算机');
insert into Teacher
values
(102,'张丽','讲师','通信');
insert into Teacher
values
(103,'刘力伟','助教','计算机');
insert into Teacher(Tno,Tname,Dept)
values
(104,'李春生','计算机');
insert into Teacher(Tno,Tname,Dept)
values
(105,'王华英','自动化');
create Table Course(
Cno integer not null,
Tno integer not null,
Cname char(10) not null,
credit numeric(3,1) not null,
Primary key(cno,tno));
insert into Course
values(1,101,'数据库',3.5);
insert into Course
values(1,103,'数据库',3.5);
insert into Course
values(2,102,'网络',3);
insert into Course
values(2,101,'网络',3);
insert into Course
values(3,103,'操作系统',3);

delete from teacher where tno=101; 
select * from teacher;
select * from course;

create trigger trig_demo1
after delete on teacher
for each row
begin
  delete course
     where course.tno=:old.tno;
end;

create trigger trig_demo1
  on teacher
   for delete
as
  delete course
    from course,deleted
        where course.tno=deleted.tno

select * from teacher;
select * from course; 
update teacher
  set tno=110
where tno=103;
select * from teacher;
select * from course;

create trigger trig_demo2
   after update on teacher
   for each row
   begin
        update course
        set course.Tno=:new.Tno
     where course.Tno=:old.Tno;
   end;

create trigger trig_demo2
   on teacher
   for update
   as
   if update(Tno)
   begin
      Declare @old_Tno integer,@new_Tno integer
      select @old_Tno=Tno
         from deleted;
      select @new_Tno=Tno
         from inserted;
       update course
        set course.Tno=@new_Tno
     where course.Tno=@old_Tno;
   end;

begin transaction
select * from teacher;
update teacher
  set title=null
     where tno=101;
select * from teacher;
rollback;
select * from teacher;