Tao Khoa Ngoai Tham Chieu Toi Khoa Chinh Cua Table Khac Trong Sql Server Thi Bao Loi!

[tqcuong]

Minh tao 2 table trong SQL SEVER:

CREATE TABLE A
(
MSA CHAR(2) NOT NULL,
TA VARCHAR(20) NOT NULL,
CONSTRAINT PK_A PRIMARY KEY(MSA)
)

CREATE TABLE B
(
MSB CHAR(2) NOT NULL,
TB VARCHAR(30) NOT NULL,
CONSTRAINT PK_B PRIMARY KEY(MSB),
CONSTRAINT FK_B_A FOREIGN KEY(MSA) REFERENCES A(MSA)
)

Thi khong tao duoc khoa ngoai MSA cua table B tham chieu len khoa chinh cua table A MSA

no bao loi nhu duoi day:
Foreign key 'FK_B_A' references invalid column 'MSA' in referencing table 'B'

ban nao giup minh voi

Tks!!!!!!!!!!!!!!!!!!!!!!!!

[NetBuiDoi]

trong table B không có column nào tên MSA hết cho nên không thể tạo khoá ngoại được, bạn sửa lại như sau:

CREATE TABLE B
(
MSB CHAR(2) NOT NULL,
TB VARCHAR(30) NOT NULL,
CONSTRAINT PK_B PRIMARY KEY(MSB),
CONSTRAINT FK_B_A FOREIGN KEY(MSB) REFERENCES A(MSA)
)

Mến.

[phatnq2003]

Lưu ý một chút, cái quan hệ này là dạng 1-1 đó.

[tranphiho]

'đầu tiên khởi tạo tên miền và tên như bên dưới'
create database sa
use sa
'tạo các bảng cần thiết'
'tạo bảng a'
CREATE TABLE A(
MSA CHAR(10),
TA VARCHAR(50),
CONSTRAINT pb_pk PRIMARY KEY(MSA))
'xem lại dữ liệu'
select *
from a
'tạo bảng b'
CREATE TABLE B(
MSB CHAR(10),
MSA CHAR(10),
TB VARCHAR(50),
PRIMARY KEY(MSB),
CONSTRAINT FK_B_A FOREIGN KEY(MSA) REFERENCES A(MSA),)
'xem lại dữ liệu'
select *
from b
'khóa ngoại nằm trong bảng b tham chiếu vào bảng a'
'bạn tham khảo nha'
'mai xuân cảnh bút ký'

[=========> Bổ sung bài viết <=========]

create database dulieu
use dulieu

create table tbl_Tinh(
ma char(10),
ten varchar(50),
constraint pb_pk primary key(ma),)

create table tbl_Danhba(
ma char(10),
sdt char(10),
hoten varchar(50),
dc nvarchar(50),
primary key (ma,sdt),
CONSTRAINT tbl_Danhba_tbl_Tinh_FK FOREIGN KEY(ma)REFERENCES tbl_Tinh (ma),)
'chen du lieu vao bang'
select *
from tbl_Tinh

insert into tbl_Tinh values ('0500','Dak Lak')
insert into tbl_Tinh values ('056','Binh Dinh')
insert into tbl_Tinh values ('057','Phu Yyen')
insert into tbl_Tinh values ('058','Khanh Hoa')
insert into tbl_Tinh values ('061','Dong nai')

select *
from tbl_Danhba

insert into tbl_Danhba values ('056','0977960263','mai xuan canh','binh dinh')
insert into tbl_Danhba values ('056','0977960264','tran ngoc hoai','binh dinh')
insert into tbl_Danhba values ('056','0977960265','tran ngoc ha','binh dinh')
insert into tbl_Danhba values ('056','0977960266','tran thi thu hang','binh dinh')
insert into tbl_Danhba values ('056','0977960267','tran thi thu hao','binh dinh')
insert into tbl_Danhba values ('056','0977960268','trinh thi phung','binh dinh')
insert into tbl_Danhba values ('056','0977960269','tran duong','binh dinh')
insert into tbl_Danhba values ('057','0977960270','le chi phat','phu yen')
insert into tbl_Danhba values ('057','0977960271','dang ngoc hanh','phu yen')
insert into tbl_Danhba values ('057','0977960272','bui the khoa','phu yen')
insert into tbl_Danhba values ('0500','0977960273','nguyen manh tien','dak lak')
insert into tbl_Danhba values ('0500','0977960274','nguyen van tien','dak lak')
insert into tbl_Danhba values ('058','0977960275','ngo van thuong','khanh hoa')
insert into tbl_Danhba values ('058','0977960276','mai hoa nhac','khanh hoa')
insert into tbl_Danhba values ('058','0977960277','mai hoa vang','khanh hoa')
insert into tbl_Danhba values ('058','0977960278','le tuan anh','khanh hoa')
insert into tbl_Danhba values ('058','0977960279','luong manh hai','khanh hoa')
insert into tbl_Danhba values ('061','0977960280','vo van duong','dong nai')
insert into tbl_Danhba values ('061','0977960281','le phi dong','dong nai')
'bạn tham khảo nha'

[=========> Bổ sung bài viết <=========]

create database QUANLYSINHVIEN
drop database QUANLYSINHVIEN
CREATE TABLE SINHVIEN(
STUDENT int,
NAMES VARCHAR(50),
AGE TINYINT,
GENDER BIT NULL
PRIMARY KEY (STUDENT),)

CREATE TABLE BOOKS(
BOOK INT,
NAMES VARCHAR(50),
TOTALPAGE INT,
TYPE VARCHAR(10),
QUANTITY INT,
PRIMARY KEY(BOOK),)


CREATE TABLE BORROWS(
BROOWS INT,
STUDENT INT,
BOOK INT,
BROROOWDATE DATETIME,
PRIMARY KEY (BROOWS),
CONSTRAINT BORROWS_STUDENT_FK FOREIGN KEY(STUDENT)REFERENCES SINHVIEN (STUDENT),
CONSTRAINT BORROWS_BOOKS_FK FOREIGN KEY (BOOK)REFERENCES BOOKS (BOOK),)





ALTER TABLE SINHVIEN
ALTER COLUMN GENDER BIT NULL



ALTER TABLE BORROWS
ALTER COLUMN BROROOWDATE datetime NOT NULL


insert into BORROWS VALUES(1,1,1,'10/29/2004')
insert into BORROWS VALUES(2,4,4,'10/26/2004')
insert into BORROWS VALUES(3,7,3,'10/20/2005')
insert into BORROWS VALUES(4,7,4,'02-01-2009')
insert into SINHVIEN values (1,'NHUYEN HOANH ANH',19,0);


-----------cau7--------
ALTER TABLE SINHVIEN
ALTER COLUMN NAMES VARCHAR(50) NOT NULL
-------------cau8------------

ALTER TABLE BOOKS
ALTER COLUMN NAMES VARCHAR(50) NOT NULL

---------cau9------------
SELECT NAMES,TOTALPAGE,TYPE,QUANTITY
FROM BOOKS
WHERE TOTALPAGE>50
ORDER BY TOTALPAGE

------------cau13------------------
SELECT NAMES,GENDER
FROM SINHVIEN
WHERE GENDER IS NULL

-------------------cau11-------------------
SELECT sinhvien.NAMES,books.NAMES
FROM SINHVIEN JOIN BOOKS on student=book

--------------11-------------------
SELECT SINHVIEN.NAMES,BOOKS.NAMES
FROM SINHVIEN,BOOKS
WHERE STUDENT=BOOK

--------------cau12-----------------
SELECT SINHVIEN.NAMES, COUNT(BORROWS.BOOK) AS NoB
FROM SINHVIEN join BORROWS ON BORROWS.STUDENT=SINHVIEN.STUDENT
GROUP BY SINHVIEN.NAMES


----------cau10---------

insert into BORROWS VALUES(6,1,1,'10/30/2005')
insert into BORROWS VALUES(7,2,4,'10/30/2005')
insert into BORROWS VALUES(8,2,3,'10/30/2005')

[=========> Bổ sung bài viết <=========]

create database qlbh
use qlbh
create table vattu(
mavt char(5)not null,
ten varchar(40),
mau varchar (15),
trluong float,
Tpho varchar (20),
constraint vattu_pk_mavt primary key (mavt)
)
create table NCC(
maNCC char (5) not null,
ten varchar (40),
heso int,
Tpho varchar (20),
constraint NCC_pk_maNCC primary key (maNCC)
)
create table Duan(
maDA char (5) not null,
ten varchar (40),
Tpho varchar (20),
constraint Duan_pk_maDA primary key (maDA)
)
create table CC(
maNCC char (5) not null,
mavt char (5) not null,
maDA char (5) not null,
Sluong int,
)
alter table CC add constraint FK_MaNCC foreign key (MaNCC) references NCC(MaNCC)
alter table CC add constraint FK_MaVT foreign key (MaVT) references vattu(MaVT)
alter table CC add constraint FK_MaDA foreign key (MaDA) references Duan(MaDA)
alter table CC add check(Sluong between 0 and 1000)
alter table Vattu add check(Trluong between 0 and 20.0)



insert into vattu values ('p1','dai oc','do','12','TPHCM')
insert into vattu values ('p2','bu long','xanh la','17','Ha Noi')
insert into vattu values ('p3','dinh vit','xanh duong','17','Hai phong')
insert into vattu values ('p4','dinh vit','do','14','TPHCM')
insert into vattu values ('p5','cam','xanh duong','12','Ha Noi')
insert into vattu values ('p6','banh rang','do','19','TPHCM')

insert into NCC values ('s1','son','20','TPHCM')
insert into NCC values ('s2','tran','10','Ha Noi')
insert into NCC values ('s3','bach','30','Ha Noi')
insert into NCC values ('s4','lap','20','TPHCM')
insert into NCC values ('s5','anh','30','Da Nang')

insert into Duan values('j1','may phan loai','Ha Noi')
insert into Duan values('j2','man hinh','Viet Tri')
insert into Duan values('j3','OCR','Da Nang')
insert into Duan values('j4','Bang dieu khien','Da Nang')
insert into Duan values('j5','Raid','TPHCM')
insert into Duan values('j6','EDS','Hai Phong')
insert into Duan values('j7','Bang Tu','TPHCM')

insert into CC values('s1','p1','j1','200')
insert into CC values('s1','p1','j4','700')
insert into CC values('s2','p3','j1','400')
insert into CC values('s2','p3','j2','200')
insert into CC values('s2','p3','j3','200')
insert into CC values('s2','p3','j4','500')
insert into CC values('s2','p3','j5','600')
insert into CC values('s2','p3','j6','400')
insert into CC values('s2','p3','j7','800')
insert into CC values('s2','p5','j2','100')
insert into CC values('s3','p3','j1','200')
insert into CC values('s3','p4','j2','500')
insert into CC values('s4','p6','j3','300')
insert into CC values('s4','p6','j7','300')
insert into CC values('s5','p2','j2','200')
insert into CC values('s5','p2','j4','100')
insert into CC values('s5','p5','j5','500')

declare @Tongsoluong int
set @Tongsoluong=500
select @tongsoluong = sum(Sluong)
from CC
print @tongsoluong
go
declare @soluongmax int
declare @soluongmin int
select @soluongmax = max(Sluong),@soluongmin=min(Sluong)
from CC
print @soluongmax
print @soluongmin
print @@fetch_status
go
select *
from CC
order by MaNCC
compute sum(Sluong) by maNCC

print @@version
print @@language
select * from CC
print @@rowcount
select * into #TT
from CC

/*cau10*/
select mavt,maDA,Sluong into #CCap
from CC
/*cau11*/
select mavt,maDA,Sluong into ##CCap
from CC
/*cau12*/
select ten,trluong,loaimau=
case mau
when 'xanh duong' then'Xanh nhat'
when 'xanh la' then'Xanh'
else 'mau khac'
end
from vattu
/*===============================cau*13*=========== ===========*/
select *,Ghichu=
case
when Sluong <100 then'It'
when Sluong between 200 and 400 then'Trung Binh'

when Sluong between 500 and 900 then'Cao'
else 'Chua biet'
end
from CC
/*cau 14*/
DECLARE @i INT
SET @i=7
print'day so la'
WHILE (@i<17)
BEGIN
SET @i = @i + 1
if (@i between 10 and 14)


continue
PRINT @i
END


/*cau 15*/
if (select count(*) from CC where Sluong>=400)>0
print 'Co mat hang voi so luong luong'
else
print'Chi co hang voi so luong it';


/*=============================*/
If exists (select * from CC where datename(dw,ngaynhap)='sunday')
begin
end
else
begin
end
create proc TinhTong
@TongSl char(10)
as
declare @Tong int
select @Tong=Sum(Sluong)
from CC
where @TongSL = maNCC
print @TongSL
Exec TinhTong @TongSL='s1'

drop proc TimSoLuong
/*-----------------------*/
create proc TimSoLuong
@tsodh char(4),
@tmaVT char(4),
@SoLuongdat int output
with recompile
as
If Not Exists (Select SoDH from DONHANG
where SoDH = @tsodh and MaVT = @tmaVT)
Begin
print 'Xem lai don dat hang'
return
end
select @SoLuongdat = SLDat from DONHANG
where SoDH = @tsodh and MaVT = @tmaVT
go
/*-----------++++++++++++++++++++*/
declare @SLDatKhac int
exec TimSoLuong @tsodh ='D002',@tmaVT='DD02',
@SoLuongdat = @SLDatKhac output
with recompile
print ' so luong la ' + cast(@SLDatKhac as varchar(20))




exec sp_helptext TimSoLuong
/*-------------------------*/

create alter proc TinhTong
as
declare @n int
select @n=Sum(SLuong)
from DONHANG
print 'Tong la: '+convert(varchar(20),@n)
go
create alter proc Goilai
as
declare @a int
set @a=50
print 'so a la: '+ convert(varchar(5),@a)
exec TinhTong
go
exec Goilai
create alter proc a
as
select @@Nestlevel as 'Capa'
go
create proc b
as
select @@Nestlevel as 'Capb'
exec a
go
create proc c
as
select @@Nestlevel as 'Capc'
exec b
go
exec c

create proc q
@giatri int
as
if Not Exists(select * from DONHANG
where SLuong=@giatri)
return 1
go


Declare @ketqua int
exec @ketqua= q @giatri= 40
If @ketqua = 1
print'Khong co'
else
print 'co'

/*--------------------------*/

create proc TongSL
@giatri int
as
if Not Exists(select * from DONHANG
where Sum(SLuong)=@giatri)
return 1
go


Declare @ketqua int
exec @ketqua= q @giatri= 40
If @ketqua = 1
print'Khong co'
else
print 'co'
/*+++++++++++++++++++++++++++++++++++++++*/
create alter proc timmax
as
declare @giatrimax int
select * into #BangTam
from CC
where maNCC='s1' or maNCC='s2'
select
@giatrimax=max(Sluong)
from #BangTam
print'Gia tri lon nhat la '+' '+convert(varchar(20),@giatrimax)
drop table #BangTam
go

exec timmax
/*----------------------------------*/

/*----------------------------------*/

/*----------------------------------*/
/*----------------------------------*/

/*----------------------------------*/

/*----------------------------------*/

/*----------------------------------*/

/*----------------------------------*/

/*----------------------------------*/

/*----------------------------------*/

/*----------------------------------*/

/*----------------------------------*/

/*cau 3*/
alter table vattu add ngaynhap datetime
/* cau 4*/
create proc TongSL
as
declare @Tong int
select @Tong=Sum(Sluong) from CC
print 'tong la: '+ convert(varchar(20),@Tong)
go
exec TongSL
/*--------------------*/
/*cau 5*/
create alter proc NhapMaCC
@Nhap char(10)
as
declare @Tong int
select @Tong=Sum(Sluong)
from CC
where @Nhap = maNCC
print 'Tong cua '+ convert(varchar(2),@Nhap)+' la: '+ convert(varchar(10),@Tong)
go
Exec NhapMaCC @Nhap='s1'
/* cau 6*/
create proc NhapMaVT
@Nhap char(3)
as
declare @TP varchar(20)
select @TP=Tpho
from vattu
where @Nhap = mavt
print 'Thanh pho cua '+ '"'+convert(varchar(2),@Nhap)+'"'+' la: '+@TP
go
Exec NhapMaVT @Nhap='p1'
/* cau 7 */
create proc TimMaVTSL
@NhapNCC char(3),
@NhapDA char(3)
as
declare @VT varchar(3)
declare @SL int
select @VT=mavt,@SL=Sluong
from CC
where @NhapNCC = maNCC and @NhapDA = maDA
print 'Ma NCC = '+ '"'+@NhapNCC +'"'+' va MaDA ' +'"'
+ @NhapDA+'" '+ 'co MaVT= '+'"'+@VT+'" '+ 'voi so luong la '+ convert(varchar(5),@SL)
go
Exec TimMaVTSL @NhapNCC='s2',@NhapDA='j1'
/*--------- cau 8*/
drop proc TimMaVTSL2
/*----------------------------------*/

/******************************/
create proc TimMaVTSL1
@NhapNCC char(3),
@NhapDA char(3)
as
declare @VT varchar(3)
declare @SL int
if not Exists (select maNCC,maDA
from CC
where @NhapNCC = maNCC)
begin
print'Xem lai MaNCC'
return
end
else
begin
select @VT=mavt,@SL=Sluong
from CC
where @NhapNCC = maNCC and @NhapDA = maDA
print 'Ma NCC = '+ '"'+@NhapNCC +'"'+' va MaDA ' +'"'+ @NhapDA+'" '+ 'co MaVT= '+'"'+@VT+'" '+ 'voi so luong la '+ convert(varchar(5),@SL)
end
go
Exec TimMaVTSL1 @NhapNCC='s2',@NhapDA='p1'

/*************cau 9******************/
create proc TimMaVTSL2
@NhapNCC char(3),
@NhapDA char(3)
/* cau 10*/
With Encryption
as
declare @VT varchar(3)
declare @SL int
if not Exists (select maNCC,maDA
from CC
where @NhapNCC = maNCC and @NhapDA = maDA)
begin
print'Xem lai MaNCC hoac maDA '
return
end
else
begin
select @VT=mavt,@SL=Sluong
from CC
where @NhapNCC = maNCC and @NhapDA = maDA
print 'Ma NCC = '+ '"'+@NhapNCC +'"'+' va MaDA ' +'"'+ @NhapDA+'" '+ 'co MaVT= '+'"'+@VT+'" '+ 'voi so luong la '+ convert(varchar(5),@SL)
end
go
Exec TimMaVTSL2 @NhapNCC='s2',@NhapDA='f1'

/* cau 10*/
create proc TimMaVTSL3
@NhapNCC char(3),
@NhapDA char(3)

With Encryption
as
declare @VT varchar(3)
declare @SL int
if not Exists (select maNCC,maDA
from CC
where @NhapNCC = maNCC and @NhapDA = maDA)
begin
print'Xem lai MaNCC hoac maDA '
return
end
else
begin
select @VT=mavt,@SL=Sluong
from CC
where @NhapNCC = maNCC and @NhapDA = maDA
print 'Ma NCC = '+ '"'+@NhapNCC +'"'+' va MaDA ' +'"'+ @NhapDA+'" '+ 'co MaVT= '+'"'+@VT+'" '+ 'voi so luong la '+ convert(varchar(5),@SL)
end
go
Exec TimMaVTSL2 @NhapNCC='s2',@NhapDA='f1'

/* cau 11*/
create proc NhapMaVT1
@Nhap char(3)
With recompile
as
declare @TP varchar(20)
select @TP=Tpho
from vattu
where @Nhap = mavt
print 'Thanh pho cua '+ '"'+convert(varchar(2),@Nhap)+'"'+' la: '+@TP
go
Exec NhapMaVT @Nhap='p1'


/***********cau 12**************/
alter proc TMax
as
declare @giatrimax int
select @giatrimax=max(TrLuong)
from vattu
where convert(varchar(7),ngaynhap,21) = '2009-01'
print'Gia tri lon nhat la '+' '+convert(varchar(20),@giatrimax)
go

exec TMax
/*---------------cau 13---------------------*/
alter proc TMax
as
declare @giatrimax int
If not exists( select * from vattu
where convert(varchar(7),Ngaynhap,21) = '2009-01'
begin
print ' Trong thang 1/2000 khong nhap vat tu nao ca'
return
end
else
select @giatrimax=max(TrLuong)
from vattu
where convert(varchar(7),Ngaynhap,21) = '2009-01'
print'Gia tri lon nhat la '+' '+convert(varchar(20),@giatrimax)
go

exec TMax
/***********cau 14**************/
CREATE proc TMin
as
declare @giatrimin int
select @giatrimin=min(TrLuong)
from vattu
where month(Ngaynhap) = 01 and year(Ngaynhap)=2000

print'Gia tri nho nhat la '+' '+convert(varchar(20),@giatrimin)


exec TMin

/***********cau 15**************/
alter proc TMin15
as
declare @giatrimin int
If not exists( select * from vattu
where month(Ngaynhap) = 01 and year(Ngaynhap)=2009)
begin
print ' Trong thang 1/2009 khong nhap vat tu nao ca'
return
end
else
select @giatrimin=min(TrLuong)
from vattu
where month(Ngaynhap) = 01 and year(Ngaynhap)=2009

print'Gia tri nho nhat la '+' '+convert(varchar(20),@giatrimin)
exec TMin15
/***********cau 16**************/
create alter proc TimMinMax
as
declare @Max varchar(20)
exec @Max= TMax

exec @Max= TMin

go
exec TimMinMax


/*****************cau 17*****************/
create proc CapThuTuc
as
exec TimMinMax
select @@Nestlevel as 'Cap TimMinMax'
go

exec CapThuTuc

/******************cau 18***********************/
drop proc TongSL

/**************************cau 19********************************/
create proc TongSL
@NhapNCC char(3),
@NhapVT char(3)
as
declare @VT varchar(3)
declare @SL int
if not Exists (select maNCC,mavt
from CC
where @NhapNCC = maNCC and @NhapVT = mavt)
begin
print'Xem lai MaNCC hoac maDA '
return
end
else
begin
select @VT=mavt,@SL=Sum(Sluong)
from CC

where @NhapNCC = maNCC and @NhapVT = mavt
group by mavt
print 'Ma NCC = '+ '"'+@NhapNCC +'"'+' va MaDA ' +'"'+ @NhapVT+'" '+ 'co MaVT= '+'"'+@VT+'" '+ 'voi so luong la '+ convert(varchar(5),@SL)
end
go
Exec TongSL @NhapNCC='s2',@NhapVT='f1'

[=========> Bổ sung bài viết <=========]

create database QL_NHANSU_TIENLUONG
use

create table CONGVIEC(
MaCv varchar(10),
TenCV varchar(50),
MoTaCV varchar(50),
constraint pk_congviec primary key(MaCV),
);


insert into congviec values('GD','GIAM DOC','QUANG LY CHUNG')
insert into congviec values('TLGD','TRO LY','TRO LY GIAM DOC')
insert into congviec values('BV','BAO VE','BAO VE AN TOAN')
insert into congviec values('TP','TPHONG','TIM HUONG PHAT TRIEN CT')


create table CHITIETNV(
CMND varchar(12),
HoTen varchar(50),
Ngaysinh datetime,
GioiTinh Varchar(5),
HoKhau varchar(50)
constraint pk_chitietnv primary key (CMND),
);

INSERT INTO CHITIETNV VALUES('271596376','PHAN VAN QUY','1984/07/10','NAM','DONG NAI')
INSERT INTO CHITIETNV VALUES('271596377','LE CHI PHAT','1986/02/15','NAM','PHU YEN')
INSERT INTO CHITIETNV VALUES('271596378','VO VAN LUOM','1984/02/13','NAM','BEN TRE')
INSERT INTO CHITIETNV VALUES('271596379','PHAN TI TUE','1989/04/13','NAM','DONG NAI')


CREATE TABLE NHANVIEN(
MaNV varchar(10),
DienThoai varchar(11),
Luong decimal,
TrinhDo varchar(50),
Chuyenmon varchar(50),
DiaChi varchar(50),
ViTriNV varchar (50),
Ngayvaolan datetime,
CMND varchar(12),
MaCV varchar(10),
CONSTRAINT PK_NHANVIEN primary key(MaNV),
constraint FK_NHANVIEN_CHITETNV FOREIGN KEY (CMND)references CHITIETNV(CMND),
constraint FK_NHANVIEN_CONGVIEC foreign key(MaCV)references CONGVIEC(MaCV)


);
INSERT INTO NHANVIEN VALUES('001','01655444187',1000000,'DAI HOC','QUAN LY','LONG KHANH-DONG NAI','','2003/10/10','271596376','GD')
INSERT INTO NHANVIEN VALUES('002','01655444181',700000,'TRUNG CAP','TRO LY ','TUY HOA PHU YEN','','2003/10/13','271596377','TLGD')
INSERT INTO NHANVIEN VALUES('003','01655444182',500000,'12/12','CONG TAC AN NINH','BIEN HOA-DONG NAI','','2003/02/11','271596378','BV')
INSERT INTO NHANVIEN VALUES('004','01655444183',800000,'CAO DANG','PHAT TRIEN CONG TY','GIONG TROM BENTRE','','2003/10/20','271596379','TP')

create table CHAMCONG(
MaNV varchar(10),
NgayBD DATETIME,
GioCong int,
GioTangCa int,
constraint pk_chamcong primary key(MaNV),
constraint FK_CHAMCONG_NHANVIEN foreign key(MaNV)references NHANVIEN(MaNV),
);

INSERT INTO CHAMCONG VALUES('001','2003/10/10',200,0)
INSERT INTO CHAMCONG VALUES('002','2003/10/13',200,10)
INSERT INTO CHAMCONG VALUES('003','2003/02/11',208,20)
INSERT INTO CHAMCONG VALUES('004','2003/10/20',200,5)

create table LUONG(
MaNV varchar (10),
MaCV varchar(10),
HeSoLuong int,
Luongcb decimal,
PhuCap decimal,
Thuong decimal,
PhucLoi decimal,
TongLuong decimal,
constraint PK_LUONG primary key(MaCV,MaNV),
constraint FK_LUONG_CONGVIEC FOREIGN KEY(MaCV)references CONGVIEC(MaCV),

);

INSERT INTO LUONG VALUES('001','GD',5,2000000,500000,1000000,400000, 3900000)
INSERT INTO LUONG VALUES('002','TLGD',3,1500000,300000,600000,200000 ,2600000)
INSERT INTO LUONG VALUES('003','BV',2,1000000,200000,40000,100000,19 00000)
INSERT INTO LUONG VALUES('004','TP',4,1600000,400000,800000,300000,2 100000)

create table PHONG(
MaPhong varchar(10),
TenPhong varchar(50),
MaNV varchar(10),
constraint PK_PHONG primary key(MaPhong),
constraint FK_PHONG_NHANVIEN foreign key(MaNV)references NHANVIEN(MaNV)
);

INSERT INTO PHONG VALUES('01','PHONG HANH CHINH','001')
INSERT INTO PHONG VALUES('02','PHONG THIET KE','002')
INSERT INTO PHONG VALUES('03','PHONG XUAT NHAP KHAU','003')
INSERT INTO PHONG VALUES('04','PHONG QUANG LY NHAN SU','004')

----------cau1:Nhap vao manv de xem thong tin cua nha vien----------

create PROC SP_TENNHANVIEN
@MANV VARCHAR(10)
AS
BEGIN
SELECT manv,hoten,gioitinh,ngayvaolan,macv
FROM CHITIETNV JOIN NHANVIEN ON CHITIETNV.CMND=NHANVIEN.CMND
WHERE MANV=@MANV
END
GO

EXEC SP_TENNHANVIEN '002'

--------cau 2:tao thu tuc noi tai co ten tongnv de tinh ra tong so nhan vien trong ban nhan vien----------

create proc tongnv
as
declare @tong int
select @tong=count(Manv)
from nhanvien
print 'Tong so nhan vien : '+convert(varchar(20),@tong)
go

exec tongnv

----------cau 3:Tao thu tuc noi tai co tenkiemtra_manv va nhap vao manv ,neu nhap khong dung ma thi bao manv khong dung------------------


alter proc kiemtra_manv
@manv varchar(10)
as
begin
if not exists (select manv from NHANVIEN where @manv=manv)
print 'Ma nhan vien khong dung'
else
SELECT *
FROM CHITIETNV JOIN NHANVIEN ON CHITIETNV.CMND=NHANVIEN.CMND
WHERE @MANV=MANV
end
go

exec kiemtra_manv '001'


------cau 4:tao thu tuc noi tai co ten thoigiantb de tinh ra thoi gian cua cac nhan vien trong ban cham cong---

alter proc thoigiantb
as
declare @tongtg int
select @tongtg=avg(giocong)
from chamcong
print ' Thoi gian trung : '+convert(varchar(10),@tongtg)
go

exec thoigiantb

-----------cau 5:tao thu tuc noi tai co ten nhap manv_macv de nhap vao ma nhan vien va ma cong viec tra ra hesoluong thong qua thamso dau ra--



alter proc Nnhap_manv_macv
@manv varchar(10),
@macv varchar(10),
@hesoluong varchar(10) output
as
begin
select @hesoluong = hesoluong
from luong
where @manv = manv and @macv=macv



print ' He so luong : ' +convert(varchar(10),@hesoluong)
end
go

exec nnhap_manv_macv @manv= '001',@macv='gd',@hesoluong=''

----------------phan 2 triger-----------------

/*cau 1 tao trigger co ten thaotaccapnhat de lay mau tin trong ban inserted
khi co thao tac cap nhap du lieu tren ban congviec */

ALTER trigger thaotaccapnhat
on congviec
after INSERT
as
begin

select * from inserted

end
go
insert into congviec values('TGD','T G DOC','DIEU HANH T DOAN')
select* from congviec

select * from CONGVIEC


---CAU 2:trigger ktra tuoi------


create trigger ktratuoi
on chitietnv
after insert
as
if(select year(getdate())- year(ngaysinh) from inserted)<18
begin
raiserror('nhan vien chua du 18 tuoi',16,1)
rollback transaction
end
go

INSERT INTO CHITIETNV VALUES('2754556380','HAN THI TUYET BANG','1992/07/10','NU','DA LAT')


---cau3:-----------

---lam trigger mat hieu luc---
alter table chitietnv disable trigger all

---CAU 4----
lam trigger co hieu luc tro lai
alter table chitietnv enable trigger all

---cau 5:----
TRIGGER THONG BAO DA CAP NHAT DLIEU
ALTER trigger thongbao
on luong after update
as
if update(LUONGCB)
BEGIN
RAISERROR('DA CAP NHAT DU LIEU MOI',16,1 )
END

GO
UPDATE LUONG SET LUONGCB=2000000,TONGLUONG=4000000
WHERE MANV='001'

--------CAC HAM TRONG CSDL---
CREATE FUNCTION THONGTIN ( @MAPHONG VARCHAR(10))
RETURNS VARCHAR(10) as
BEGIN
RETURN(SELECT * FROM PHONG WHERE @MAPHONG=MAPHONG)
END
go

--------HAM-------

CREATE FUNCTION GIAITHUA (@X BIGINT)
RETURNS BIGINT
AS
BEGIN
DECLARE @I BIGINT
IF(@X >20 OR @X IS NULL)
SET @I =NULL
ELSE
IF(@X<2)
SET
@I=@X
ELSE SET
@I=@X*dbo.giaithua(@x -1)
return @i
end
go

select dbo.giaithua(3)

----xay dung udf voi yeu cau nhap tham so dau vao manv va phan tram tang luong.tra ve cac thong tin trong bang luong------

create function tangluong(@manv varchar(10),@phantram numeric)
returns @bangluong table
(
manv1 varchar(10),
macv1 varchar(10),
hesoluong1 int,
luongcoban1 decimal,
phucap1 decimal,
thuong1 decimal,
phucloi1 decimal,
tongluong1 decimal
)
as
begin
insert into @bangluong(manv1,macv1,hesoluong1,luongcoban1,phuc ap1,thuong1,phucloi1,tongluong1)
select *
from luong
where manv=@manv
update @bangluong
set tongluong1=tongluong1 +(tongluong1* @phantram)/100
return
end
go

select * from dbo.tangluong('001',10)

------------xay dung udf voi noi dung ham se tra ve tongsoluong lon nhat duoc luu trong ban luong------

alter function tongsoluongmax()
returns decimal as
begin
return (select max(tongluong)
from luong)
end


select dbo.tongsoluongmax()


alter function tongnhanvien(@manvmoi varchar (10))
returns varchar (50) as
begin
return (select diachi
from nhanvien
where manv=@manvmoi)
end
select dbo.tongnhanvien('001')

[=========> Bổ sung bài viết <=========]

create database CsdlCongty
use CsdlCongty
create table NHANVIEN(
Ho varchar(15),
Dem varchar(20),
Ten varchar(15),
MaNV char(9) not null,
NgSinh datetime not null,
Diachi varchar(70),
GTinh char,
Luong int,
MaGSat char(9),
MaPhong int,
constraint PK_MaNV Primary key(MaNV),
constraint FK_NHANVIEN foreign key(MaGSat) references NHANVIEN(MaNV),
constraint ChK_NHANVIEN check(GTinh in (' m ','M','f','F') )
)
insert into NHANVIEN values('Tran','Van','Hai',005,'1984-03-09','Long binh tan','M',500,002,7)
insert into NHANVIEN values('Nguyen','Van','Nghiem',001,'1984-09-09','Long binh-DN','M',200,001,5)
insert into NHANVIEN values('Hoang','Van','Thon',002,'1982-10-11','Long binh-DN','M',200,002,5)
delete from NHANVIEN where MaNV = 3
update NHANVIEN
set Luong = 500
where Ten='Nghiem'
drop table NHANVIEN

alter table NHANVIEN add manhanvien varchar(20)
alter table NHANVIEN alter column thu int
alter table NHANVIEN alter column GTinh int
alter table NHANVIEN drop constraint ChK_NHANVIEN
alter table NHANVIEN drop column thu
alter table NHANVIEN add constraint Luong_NHANVIEN check(Luong>20)

create domain Diachi as varchar(100)

delete from NHANVIEN where MaNV=1
delete from NHANVIEN

Update NHANVIEN
set MaPhong = 4
where MaNV = 002

select *
From NHANVIEN
where MaPhong = 4

select Ho,Ten,GTinh,Luong
From NHANVIEN

(select MaNV as MaNV1
from NHANVIEN
where MaNV=1)
union all
(select MaNV as MANV2
from NHANVIEN
)

(select MaNV as MaNV1
from NHANVIEN
where MaNV=1)
except
(select MaNV as MANV2
from NHANVIEN
where MaNV=1
)

select Ho as Honv,Dem,Ten as Tencuanhanvien,MaNV,Luong
from NHANVIEN
order by Manv

[tranphiho]

create database dulieu
use dulieu

'tao bang nhan vien'
create table t_nhanvien(
MaNV char(10),
TenNV nvarchar(50),
DiaChi nvarchar(50),
ChucVu Nvarchar(50),
constraint pb_pk primary key(MaNV),)

'kiem tra bang nhanvien'

select *
from t_nhanvien

'tao bang khach hang'

create table t_khachhang(
MaKH char(10),
TenKH nvarchar(50),
SoDT Char(11),
DiaChi nvarchar(50),
primary key(MaKH),)

'kiem tra bang khach hang'

select *
from t_khachhang

'tao bang bangxe'

create table t_bangxe(
MaSX char(10),
loai nvarchar(50),
SLXe int,
primary key(MaSX),)

'kiem tra bang bangxe'

select *
from t_bangxe

'tao bang dang ky'

create table t_dangky(
MaDK char(10),
MaKH char(10),
MaNV char(10),
NgayDK datetime,
primary key(MaDK),)

'kiem tra bang dang ky'

select *
from t_dangky

create table t_chitietdangky(
MaDK Char(10),
MaSX char(10),
TGKH char(10),
SLXe int,
primary key(MaDK,MaSX),)

'kiem tra bang chi tiet dang ky'

select *
from t_chitietdangky

'tao quan he'


'chen du lieu'
'chen du lieu bang bangxe'

select *
from t_bangxe

insert into t_bangxe values ('MaSX01','Loai 1','10')
insert into t_bangxe values ('MaSX02','loai 2','20')
insert into t_bangxe values ('MaSX03','loai 3','30')
insert into t_bangxe values ('MaSX04','loai 4','40')
insert into t_bangxe values ('MaSX05','loai 5','50')
insert into t_bangxe values ('MaSX06','loai 6','60')
insert into t_bangxe values ('MaSX07','loai 7','70')

'chen du lieu'
'chen du lieu bang khachhang'

select *
from t_khachhang

insert into t_khachhang values ('MaKH01','mai xuan canh','0977960261','bimh dinh')
insert into t_khachhang values ('MaKH02','tran ngoc hoai','0977960262','bimh dinh')
insert into t_khachhang values ('MaKH03','tran ngoc ha','0977960263','bimh dinh')
insert into t_khachhang values ('MaKH04','tran thi thu hang','0977960264','bimh dinh')
insert into t_khachhang values ('MaKH05','tran thi thu hao','0977960265','bimh dinh')
insert into t_khachhang values ('MaKH06','le chi phat','0977960266','bimh dinh')
insert into t_khachhang values ('MaKH07','vo van luom','0977960267','bimh dinh')

'chen du lieu'
'chen du lieu bang nhan vien'

select *
from t_nhanvien

insert into t_nhanvien values ('MaNV01','NhanVien01','Binh Dinh','NhanVien')
insert into t_nhanvien values ('MaNV02','NhanVien02','Binh Dinh','NhanVien')
insert into t_nhanvien values ('MaNV03','NhanVien03','Binh Dinh','NhanVien')
insert into t_nhanvien values ('MaNV04','NhanVien04','Binh Dinh','NhanVien')
insert into t_nhanvien values ('MaNV05','NhanVien05','Binh Dinh','NhanVien')
insert into t_nhanvien values ('MaNV06','NhanVien06','Binh Dinh','NhanVien')
insert into t_nhanvien values ('MaNV07','NhanVien07','Binh Dinh','NhanVien')


'chen du lieu'
'chen du lieu bang dang ky'

select *
from t_dangky

insert into t_dangky values ('MaDK01','MaKH01','MaNV01','1981-01-01')
insert into t_dangky values ('MaDK02','MaKH02','MaNV02','1982-02-02')
insert into t_dangky values ('MaDK03','MaKH03','MaNV03','1983-03-03')
insert into t_dangky values ('MaDK04','MaKH04','MaNV04','1984-04-04')
insert into t_dangky values ('MaDK05','MaKH05','MaNV05','1985-05-05')
insert into t_dangky values ('MaDK06','MaKH06','MaNV06','1986-06-06')
insert into t_dangky values ('MaDK07','MaKH07','MaNV07','1987-07-07')

'chen du lieu'
'chen du lieu bang dang ky'

select *
from t_chitietdangky

insert into t_chitietdangky values('MaDK01','MaSX01','10','10')
insert into t_chitietdangky values('MaDK02','MaSX02','20','20')
insert into t_chitietdangky values('MaDK03','MaSX03','30','30')
insert into t_chitietdangky values('MaDK04','MaSX04','40','40')
insert into t_chitietdangky values('MaDK05','MaSX05','50','50')
insert into t_chitietdangky values('MaDK06','MaSX06','60','60')
insert into t_chitietdangky values('MaDK07','MaSX07','70','70')

[SweatDream]

Các huynh giúp em làm bài này xíu nhé

http://docs.google.com/View?id=d7tj9gd_4fq2bgsgg

Thanks!