create database quanly

create table GIaovien(

maGV char(7) primary key,

tenGv nvarchar(20),

diachi nvarchar(20),

dienthoai char(11))

create table hocvi(

MaHV char(7) primary key,

tenHV nvarchar(10))

create table Chuyennganh(

MaCN char(7) primary key,

tenCN nvarchar(20))

create table GV_HV_CN(

MaGv char(7),

MaHV char(7),

maCN char(7),

Nam char(4),

constraint KC primary key (MaGV,MaHV,MaCN),

constraint Kn1 foreign key (maGv) references giaovien(MaGv),

constraint Kn2 foreign key (maHv) references Hocvi(MaHV),

Constraint Kn3 foreign key (MaCN) references chuyennganh(Macn))

insert into GIAOVIEN

values('gv01','huong','thinh dan','0976123455')

insert into GIAOVIEN

values('Gv02','hai','bac kan','0987654321')

insert into GIAOVIEN

values('Gv03','nhan','ho nui coc','0987654352')

insert into HOCVI

values('Hv01','tien si')

insert into HOCVI

values('hv02','giao su')

insert into CHUYENNGANH

values('cn01','kinh te')

insert into CHUYENNGANH

values('cn02','tin hoc')

insert into GV_HV_CN

values('gv01','hv01','cn01','2009')

insert into GV_HV_CN

values('gv02','hv02','cn02','2007')

insert into GV_HV_CN

values('gv03','hv02','cn01','2008')

delete from CHUYENNGANH where macn='cn 02'

select *

from giaovien

select *

from hocvi

select *

from chuyennganh

select *

from GV_Hv_CN

----2----------------

create view Tiensi

As

select *

from giaovien

where magv in(select Magv

from GV_HV_cn

where MaHv in (select MaHV

from hocvi

where tenhv='tien si'))

select *

from tiensi

---------3---------------

create view kinhte

As

select *

from giaovien

where magv in(select Magv

from GV_HV_cn

where MaCN in(select MaCN

from Chuyennganh

where tencn='kinh te'))

select *

from kinhte

---------4------------

create view Honuicoc

As

select *

from giaovien

where diachi='ho nui coc'

select *

from honuicoc

-----5---------------

create proc NAM

@NAm char(4)

As

select *

from giaovien

where MaGV in(select magv

from GV_HV_CN

where (nam=@nam) and (mahv in(select mahv

from Hocvi

where tenhv='tien si')))

nam '2009'

-------------7-----------

create proc ttgv1

@TenHV nvarchar(10)

As

select *

from giaovien

where Magv in(select magv

from GV_hv_cn

where mahv in(select mahv

from hocvi

where tenhv=@tenhv))

ttgv1 'tien si'