CreateData

CREATE DATABASE AT9B
use AT9B

CREATE TABLE tblThongTinCongTy (
    TenCtyVN nvarchar(100),
    TenCtyEN varchar(100),
    TenVietTat varchar(100),
    TenGiamDoc nvarchar(100),
    SoDienThoai varchar(15),
    SoFax varchar(12),
    SoTaiKhoan varchar(20),
    DiaChiVN nvarchar(200),
    DiaChiEN nvarchar(200),
    MaSoThue varchar(20),
    NgayDangKy date,
    Logo image
)

CREATE TABLE tblNhanVien(
    -- Các thông tin chính
    MaNV char(6) primary key,
    HoDem nvarchar(100),
    Ten nvarchar(100),
    Nickname nvarchar(100),
    MaTrangThai smallint,
    MaBoPhan smallint,
    MaLoaiHopDong smallint,
    MaChucVu smallint,
    MaPhongBan smallint,
    NgayVao date,
    NgayBatDau date,
    GioiTinh bit,
    SDT varchar(15),
    EmailCty varchar(100),
    NgayKTthuViec date,
    HinhAnh image,
    -- Thông tin thêm
    NgaySinh date,
    NguyenQuan nvarchar(200),
    ThuongTru nvarchar(200),
    MaDanToc smallint,
    MaTonGiao smallint,
    MaQuocGia smallint,
    EmailRieng varchar(100),
    KetHon bit,
    TrinhDoVH varchar(50),
    DiaChi nvarchar(200),
    SoCMT varchar(11),
    NgayCap date,

    MaTinh smallint,
    SoTK varchar(20),
    MaNH smallint,
    NoiCap nvarchar(50),
    MaSoThue varchar(20)
)

CREATE TABLE tblGiaDinh(
    MaGiaDinh smallint PRIMARY KEY IDENTITY( 1, 1 ),
    MaNV char(6),
    MaMQH smallint,
    Ten nvarchar(100),
    NgaySinh date,
    NgheNghiep nvarchar(100),
    DiaChi nvarchar(200),
    SDT char(15)
)

CREATE TABLE tblSucKhoe(
    MaPhieuSK smallint PRIMARY KEY IDENTITY(1,1),
    MaNV char(6),
    NgayKham date,
    TenBenhVien nvarchar(100),
    ChieuCao smallint,
    CanNang smallint,
    ThiLuc varchar(10),
    MaNhomMau smallint,
    MaTinhTrangSK smallint,
    GhiChu nvarchar(300)
)

CREATE TABLE tblTaiLieu(
    MaTaiLieu smallint PRIMARY KEY IDENTITY(1,1),
    MaNV char(6),
    Name nvarchar(200),
    NgayLuu date,
    DuLieu varbinary(MAX),
    PhanMoRong varchar(5),
    MaLoaiTL smallint
)
CREATE TABLE tblKinhNghiemLV(
    MaKinhNghiemLV smallint PRIMARY KEY IDENTITY(1,1),
    MaNV char(6),
    TenCty nvarchar(100),
    ChucDanh nvarchar(50),
    CongViec nvarchar(50),
    TimeBatDau date,
    TimeKetThuc date,
    LiDo nvarchar(300),
    -- Thong tin ng lien he
    TenNguoiLienHe nvarchar(100),
    SDT varchar(15),
    Email varchar(100),
    ChucVu nvarchar(100)
)

CREATE TABLE tblThongTinKyNang(
    MaThongTinKyNang smallint PRIMARY KEY IDENTITY(1,1),
    MaNV char(6),
    MaKyNang smallint,
    MaXepLoaiKyNang smallint,
    GhiChu nvarchar(300),
    NgayHieuLuc date
)

create table tblThongTinHocVan(
    MaThongTinHocVan smallint primary key identity (1,1),
    MaNV char(6),
    MaLoaiHocVan smallint,
    MaLoaiTotNghiep smallint,
    TenKhoaHoc nvarchar(200),
    TenTruong nvarchar(200),
    TuNam smallint,
    DenNam smallint
)

CREATE TABLE tblDanToc (
    MaDanToc smallint primary key IDENTITY( 1, 1 ),
    TenDanToc nvarchar(100)
)

-- Bảng Tôn Giáo
CREATE TABLE tblTonGiao(
    MaTonGiao smallint primary key IDENTITY( 1, 1 ),
    TenTonGiao nvarchar(100)
)

-- Bảng quốc gia
CREATE TABLE tblQuocGia(
    MaQuocGia smallint identity (1,1),
    TenQuocGia nvarchar(100)
)

-- Bảng mã tỉnh
CREATE TABLE tblTinh(
    MaTinh smallint primary key IDENTITY( 1, 1 ),
    TenTinh nvarchar(100)
)

-- Bảng mã ngân hàng
CREATE TABLE tblNganHang(
    MaNH smallint primary key IDENTITY( 1, 1 ),
    TenNH nvarchar(100)
)

-- Bảng Mối Quan Hệ
CREATE TABLE tblMoiQuanHe(
    MaMQH smallint PRIMARY KEY IDENTITY(1,1),
    TenMQH nvarchar(100)
)

-- Bảng Nhóm Máu
CREATE TABLE tblNhomMau(
    MaNhomMau smallint PRIMARY KEY IDENTITY(1,1),
    TenNhomMau varchar(10)
)

-- Bảng TinhTrangSK
CREATE TABLE tblTinhTrangSK(
    MaTinhTrangSK smallint PRIMARY KEY IDENTITY(1,1),
    TenTinhTrang nvarchar(100)
)

-- Bảng Kỹ năng
CREATE TABLE tblKyNang(
    MaKyNang smallint IDENTITY(1,1),
    TenKyNang nvarchar(100)
)

-- Bảng Xếp Loại Kỹ Năng
CREATE TABLE tblXepLoaiKyNang(
    MaXepLoaiKyNang smallint PRIMARY KEY IDENTITY(1,1),
    TenXepLoai nvarchar(100)
)

CREATE TABLE tblLoaiTaiLieu(
    MaLoaiTL smallint PRIMARY KEY IDENTITY(1,1),
    TenLoaiTL nvarchar(100)
)

CREATE TABLE tblLoaiHopDong(
    MaLoaiHopDong smallint primary key IDENTITY(1,1),
    TenLoaiHopDong nvarchar(100),
    SoThang smallint,
    DongBHXH bit,
    TiLeDong real,
    MauHopDong varbinary(MAX),
    PhanMoRong varchar(5)
)

CREATE TABLE tblPhongBan(
    MaPhongBan smallint primary key IDENTITY( 1, 1 ),
    TenPhongBan nvarchar(30),
    SoPhong varchar(100)
)

CREATE TABLE tblBoPhan (
    MaBoPhan smallint primary key IDENTITY(1,1),
    TenBoPhan nvarchar(100),
    MaPhongBan smallint
)

CREATE TABLE tblChucVu(
    MaChucVu smallint primary key IDENTITY(1,1),
    TenChucVu nvarchar(100)
)

create table tblLoaiHocVan(
    MaLoaiHocVan smallint primary key identity (1,1),
    TenLoaiHocVan nvarchar(100)
)

create table tblLoaiTotNghiep(
    MaLoaiTotNghiep smallint primary key identity (1,1),
    TenLoaiTotNghiep nvarchar(100)
)

create table tblCongDoan(
    MaNV char(6) primary key,
    NgayThamGia date,
    ThamGia bit,
)

CREATE TABLE tblTrangThaiLV (
    MaTrangThai smallint primary key IDENTITY(1,1),
    TenTrangThai nvarchar(100)
)
Create TRIGGER insert_NV ON tblNhanVien
AFTER INSERT AS
BEGIN
            SET NOCOUNT ON;
            INSERT INTO tblCongDoan(MaNV,NgayThamGia,ThamGia)
            SELECT inserted.MaNV,null,0
      FROM inserted;
End


SELECT * FROM tblBoPhan
SELECT * FROM tblPhongBan

INSERT INTO tblBoPhan VALUES(N'Hệ thống',1)
INSERT INTO tblBoPhan VALUES(N'Quản trị mạng',1)
INSERT INTO tblBoPhan VALUES(N'Tính lương',2)
INSERT INTO tblBoPhan VALUES(N'Tính Bảo hiểm xã hội',2)
INSERT INTO tblBoPhan VALUES(N'Cho nghỉ việc',3)
SELECT * FROM tblPhongBan

INSERT INTO tblPhongBan VALUES(N'Phòng Nhân Sự','201')
INSERT INTO tblPhongBan VALUES(N'Phòng Kế Toán','301')
INSERT INTO tblPhongBan VALUES(N'Phòng Kỹ Thuật','601')
INSERT INTO tblPhongBan VALUES(N'Phòng Hành Chính','601')

SELECT * FROM tblChucVu
INSERT INTO tblChucVu VALUES(N'Nhân viên')
INSERT INTO tblChucVu VALUES(N'Phó phòng')
INSERT INTO tblChucVu VALUES(N'Trưởng phòng')
INSERT INTO tblChucVu VALUES(N'Giám đốc')

SELECT * FROM tblDanToc
INSERT INTO tblDanToc VALUES(N'Kinh')
INSERT INTO tblDanToc VALUES(N'Tày')
INSERT INTO tblDanToc VALUES(N'Mường')

SELECT * FROM tblNhomMau
INSERT INTO tblNhomMau VALUES('A')
INSERT INTO tblNhomMau VALUES('B')
INSERT INTO tblNhomMau VALUES('O')
INSERT INTO tblNhomMau VALUES('AB')

SELECT * FROM tblQuocGia
INSERT INTO tblQuocGia VALUES(N'Việt Nam')
INSERT INTO tblQuocGia VALUES(N'Thái Lan')
INSERT INTO tblQuocGia VALUES(N'Nhật Bản')

SELECT * FROM tblTinh
INSERT INTO tblTinh VALUES(N'Hà Nội')
INSERT INTO tblTinh VALUES(N'Hải Dương')
INSERT INTO tblTinh VALUES(N'Hải Phòng')

SELECT * FROM tblTonGiao
INSERT INTO tblTonGiao VALUES(N'Không')
INSERT INTO tblTonGiao VALUES(N'Phật Giáo')
INSERT INTO tblTonGiao VALUES(N'Thiên Chúa Giáo')
INSERT INTO tblTonGiao VALUES(N'Đạo Tin Lành')

SELECT * FROM tblTrangThaiLV
INSERT INTO tblTrangThaiLV VALUES(N'Đang làm việc');
INSERT INTO tblTrangThaiLV VALUES(N'Nghỉ ốm');
INSERT INTO tblTrangThaiLV VALUES(N'Nghỉ thai sản');
INSERT INTO tblTrangThaiLV VALUES(N'Nghỉ không lương');


use AT9B
SELECT * FROM tblNhanVien
INSERT INTO tblNhanVien VALUES
('NV0002',N'Đỗ Mạnh','Duy','duydm',2,1,2,3,1,'01-01-2015','02-02-2015',1,'0988310608',
'duydm@hungdh.me',null,null,'02-02-1994',N'Hải Dương',N'Hà Nội',1,1,1,'duydm@gmail.com',
0,'12/12',N'Hà Nội',null,null,1,'',-1,'','')

SELECT TOP 1000 nv.MaNV,nv.HoDem, nv.Ten,GioiTinh,NgaySinh,NgayBatDau,bp.TenBoPhan,
    pb.TenPhongBan, cv.TenChucVu, SDT, EmailCty, DiaChi
FROM tblNhanVien AS nv INNER JOIN tblBoPhan AS bp ON nv.MaBoPhan = bp.MaBoPhan
    INNER JOIN tblPhongBan AS pb ON nv.MaPhongBan = pb.MaPhongBan
    INNER JOIN tblChucVu AS cv ON nv.MaChucVu = cv.MaChucVu
ORDER BY nv.Ten, nv.HoDem ASC


SELECT TOP 10 * FROM tblCongDoan
UPDATE tblCongDoan
SET NgayThamGia = '12/02/2015', CoThamGia=1
WHERE MaNV = 'NV0001'
use AT9B

SELECT * FROM tblCongDoan
DELETE tblCongDoan

SELECT nv.MaNV, nv.HoDem ,nv.Ten, tt.TenTrangThai,pb.TenPhongBan,cv.TenChucVu, cd.NgayThamGia, cd.CoThamGia
FROM tblNhanVien as nv INNER JOIN tblCongDoan cd ON nv.MaNV = cd.MaNV
INNER JOIN tblChucVu AS cv ON cv.MaChucVu = nv.MaChucVu
INNER JOIN tblTrangThaiLV AS tt ON tt.MaTrangThai = nv.MaTrangThai
INNER JOIN tblPhongBan AS pb ON nv.MaPhongBan = pb.MaPhongBan
ORDER BY nv.Ten, nv.HoDem ASC