TRANHOANGLONG, Sql Server

TRANHOANGLONG

CREATE DATABASE Baitap
USE Baitap
// TAO BANG
CREATE TABLE HANG (MH CHAR(10) NOT NULL, TENHANG CHAR(30) NOT NULL, DVT CHAR(15), NSX
CHAR(30), CONSTRAINT K10 PRIMARY KEY (MH))
CREATE TABLE NHAP (HDN CHAR(10) NOT NULL ,NGAYNHAP DATETIME NOT NULL,KH VARCHAR(100),
CONSTRAINT K20 PRIMARY KEY (HDN))
CREATE TABLE CTNHAP (HDN CHAR(10) NOT NULL FOREIGN KEY (HDN) REFERENCES NHAP(HDN), 
MH CHAR(10) NOT NULL FOREIGN KEY(MH) REFERENCES HANG(MH) , SLN FLOAT , DGN FLOAT,
CONSTRAINT K30 PRIMARY KEY(HDN,MH))
CREATE TABLE HUYNHAP (HDN CHAR(10) NOT NULL FOREIGN KEY (HDN) REFERENCES NHAP(HDN),
NGAYHUY DATETIME , LDHUY VARCHAR(100)) 
//NHAP DL
/1 HANG
INSERT INTO HANG (MH, TENHANG, DVT, NSX)
VALUES ('1000','Máy tính Dell Vostro 120', 'bộ', 'China')
INSERT INTO HANG (MH, TENHANG, DVT, NSX)
VALUES ('1001','Máy tinh hp 2450','bộ','Taiwan')
INSERT INTO HANG (MH,TENHANG,DVT,NSX)
VALUES ('1002','MÁY TÍNH LG 5200','BỘ','KOREA')
INSERT INTO HANG (MH,TENHANG,DVT,NSX)
VALUES ('1003','MÁY TÍNH IBM 8900','CÁI','USA')
INSERT INTO HANG (MH,TENHANG,DVT,NSX)
VALUES ('1004','SERVER AS8600','BỘ','USA')
/2 NHAP
SET DATEFORMAT DMY
INSERT INTO NHAP (HDN,NGAYNHAP,KH)
VALUES ('N0001','5/7/2019','PHAN THANH TRUNG')
INSERT INTO NHAP (HDN,NGAYNHAP,KH)
VALUES ('N0002','6/7/2019','NGO QUANG THANH')
/3 CT NHAP
INSERT INTO CTNHAP (HDN,MH,SLN,DGN)
VALUES ('N0001','1000','50','1000')
INSERT INTO CTNHAP (HDN,MH,SLN,DGN)
VALUES ('N0001','1001','20','1100')
INSERT INTO CTNHAP (HDN,MH,SLN,DGN)
VALUES ('N0001','1002','30','1200')
INSERT INTO CTNHAP (HDN,MH,SLN,DGN)
VALUES ('N0002','1001','20','1100')
INSERT INTO CTNHAP (HDN,MH,SLN,DGN)
VALUES ('N0002','1001','20','1200')
INSERT INTO CTNHAP (HDN,MH,SLN,DGN)
VALUES ('N0002','1004','50','20000')
/ 4 HUYNHAP
INSERT INTO HUYNHAP(HDN, NGAYHUY,LDHUY)
VALUES ('N0001','6/7/2019','CHUA BAN HET HANG')
// SUA DL
UPDATE HANG
SET NSX=('PR OF CHINA')
WHERE NSX= 'CHINA'

// XOA DL
DELETE FROM NHAP WHERE (HDN='N0001')

//BAITAP2

CREATE DATABASE BAI2
USE BAI2
// TAO BANG 
CREATE TABLE DMDONVI(MADV CHAR(10) NOT NULL,TENDV NVARCHAR(50) NOT NULL
CONSTRAINT K1 PRIMARY KEY (MADV))
CREATE TABLE DMCHUCVU(MACV CHAR(10) NOT NULL,TENCV NVARCHAR(50) NOT NULL
CONSTRAINT K2 PRIMARY KEY (MACV))
CREATE TABLE HSCANBO(MACB CHAR(10) NOT NULL,HO NVARCHAR (30),TEN NVARCHAR(20) NOT NULL,NS DATETIME,
PHAI BIT,DC VARCHAR(50),TINHTP NVARCHAR(30),TDHV NVARCHAR(30),TDCM NVARCHAR(30),
CONSTRAINT K3 PRIMARY KEY (MACB))
CREATE TABLE QTCONGTAC (MACB CHAR(10) NOT NULL FOREIGN KEY (MACB) REFERENCES HSCANBO(MACB), 
MADV CHAR(10) NOT NULL FOREIGN KEY(MADV) REFERENCES DMDONVI(MADV),
MACV CHAR(10) NOT NULL FOREIGN KEY(MACV) REFERENCES DMCHUCVU(MACV)) 
//NHAP DMDONVI
INSERT INTO DMDONVI (MADV,TENDV)
VALUES ('T001','KHU CN TÂN TẠ0')
INSERT INTO DMDONVI (MADV,TENDV)
VALUES ('T002','KHU CN BIÊN HÒA')
INSERT INTO DMDONVI (MADV,TENDV)
VALUES ('T003','KHU CN THỦ ĐỨC')
INSERT INTO DMDONVI (MADV,TENDV)
VALUES ('T004','KHU CN NHÀ BÈ')
INSERT INTO DMDONVI (MADV,TENDV)
VALUES ('T006','KHU CN BÌNH DƯƠNG')
INSERT INTO DMDONVI (MADV,TENDV)
VALUES ('T005','KHU CN NHƠN TRẠCH')
//NHAP DMCHUCVU
INSERT INTO DMCHUCVU(MACV,TENCV)
VALUES ('001','CHUYÊN VIÊN CA')
INSERT INTO DMCHUCVU(MACV,TENCV)
VALUES ('002','CHUYÊN VIÊN')
INSERT INTO DMCHUCVU(MACV,TENCV)
VALUES ('003','THANH TRA')
INSERT INTO DMCHUCVU(MACV,TENCV)
VALUES ('004','GIÁM SÁT AN TOÀN')
INSERT INTO DMCHUCVU(MACV,TENCV)
VALUES ('005','GIÁM SÁT VỆ SINH')
INSERT INTO DMCHUCVU(MACV,TENCV)
VALUES ('006','GIÁM SÁT TÀI CHÍNH')
INSERT INTO DMCHUCVU(MACV,TENCV)
VALUES ('007','THỐNG KÊ')
INSERT INTO DMCHUCVU(MACV,TENCV)
VALUES ('008','KIỂM ĐỊNH')

SET DATEFORMAT YMD
//NHAP HSCANBO
INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM)
VALUES ('C001','Phan Thanh','Hùng','1979-03-01','True','100 Đinh Tiên','Tp HCM','Đại học','Cao cấp')
INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM)
VALUES ('C002','Phạm Thị','Lệ','1978-05-03','False','32 Võ Thị Sáu','Tp HCM','Đại học','Cao cấp')
INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM)
VALUES ('C003','Nguyễn Văn','Tiên','1980-05-10','True','39 Trần Hưng','Tp HCM','Cao học','Cao cấp')
INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM)
VALUES ('C004','Ngô Bá','Hùng','1989-08-10','True','','Long An','Trung học','Sơ cấp')
INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM)
VALUES ('C005','Lê Thị','Bảy','1988-05-06','False','','','Đại học','Trung cấp')
INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM)
    VALUES ('C006','Hồ Thanh','Minh','1986-07-12','True','123 Lê Thị Riêng','Tp HCM','Trung học','Sơ cấp')
//NHAP QTCONGTAC
INSERT INTO QTCONGTAC(MACB,MADV,MACV,NDCONGTAC,NGABD,NGAYKT)
VALUES ('C001','T001',003,'Thống kê tài sản','2019-03-01','2019-03-03')
INSERT INTO QTCONGTAC(MACB,MADV,MACV,NDCONGTAC,NGABD,NGAYKT)
VALUES ('C001','T005',006,'Thống kê tài chính','2019-03-05','2019-03-09')
INSERT INTO QTCONGTAC(MACB,MADV,MACV,NDCONGTAC,NGABD,NGAYKT)
VALUES ('C002','T002',001,'Đánh giá hoạt động','2019-03-01','2019-03-07')
INSERT INTO QTCONGTAC(MACB,MADV,MACV,NDCONGTAC,NGABD,NGAYKT)
VALUES ('C003','T004',003,'Thnah tra đánh giá','2019-03-02','2019-03-04')

/*
3/. Sửa DL: Đổi tên TPHCM thành Sài Gòn
*/
UPDATE HSCANBO
SET TINHTP='Sài Gòn'
WHERE TINHTP='Tp HCM'
/*
4/. Xóa DL: Xóa QTCONGTAC của cán bộ có MACB là C001.
*/
DELETE FROM QTCONGTAC WHERE (MACB='C001')


run \| edit \| history \| help	0