create database QLNVien
use QLNVien
CREATE TABLE PhongBan
MaPhong CHAR(10) PRIMARY KEY,
TenPhong NVARCHAR(50)
);
CREATE TABLE NhanVien
MaNV CHAR(10) PRIMARY KEY,
HoTen NVARCHAR(50),
Luong DECIMAL(10, 2),
MaPhong CHAR(10) FOREIGN KEY REFERENCES PhongBan(MaPhong),
SoDienThoai CHAR(15),
NgayLamViec INT
);
Select*From PhongBan
INSERT INTO PhongBan VALUES ('P01', 'Phong Ke Toan')
INSERT INTO PhongBan VALUES ('P02', 'Phong Hanh Chinh')
INSERT INTO PhongBan VALUES ('P03', 'Phong IT')
INSERT INTO PhongBan VALUES ('P04', 'Phong GiamDoc')
Select*from NhanVien
INSERT INTO NhanVien VALUES ('NV01', 'Nguyen Van Anh', 12000, 'P02', '0123456789', 200)
INSERT INTO NhanVien VALUES ('NV02', 'Le Chi Bao', 9000, 'P01', '0987654321', 150)
INSERT INTO NhanVien VALUES ('NV03', 'Tran Van Chin', 4500, 'P03', 0124568769, 190)
INSERT INTO NhanVien VALUES ('NV08', 'Tu Hoa', 20000, 'P04', '0876543457', 100)
INSERT INTO NhanVien VALUES ('NV07', 'Dinh Hai', 30000, 'P02', '0868902344', 90)
INSERT INTO NhanVien VALUES ('NV06', 'Dinh Lam', 40000, 'P04', '0138902344', 290)
SELECT HoTen, Luong FROM NhanVien
SELECT * FROM NhanVien
WHERE Luong > 1000;
SELECT * FROM NhanVien
WHERE Luong BETWEEN 1000 AND 5000;
SELECT * FROM NhanVien
WHERE HoTen LIKE 'Tu%';
SELECT * FROM NhanVien
ORDER BY Luong ASC, NgayLamViec ASC;
SELECT PhongBan.TenPhong, AVG(NhanVien.Luong) AS LuongTrungBinh
FROM NhanVien
JOIN PhongBan ON NhanVien.MaPhong = PhongBan.MaPhong
GROUP BY PhongBan.TenPhong
HAVING AVG(NhanVien.Luong) > 10000;
SELECT * FROM NhanVien
WHERE MaPhong = 'P04';
DELETE FROM NhanVien
WHERE SoDienThoai IS NULL;
CREATE VIEW NhanVien_NgayLamViecCaoNhat AS
SELECT * FROM NhanVien
WHERE NgayLamViec = (SELECT MAX(NgayLamViec) FROM NhanVien);
Create proc sp_c3
@MaNV char(10)
As
Delete from NHANVIEN where MaNV = @MaNV
Exec sp_c3 @MaNV = 'NV06'
CREATE TABLE NhanVien
MaNV CHAR(10) PRIMARY KEY,
HoTen NVARCHAR(50),
Luong DECIMAL(10, 2),
MaPhong CHAR(10) FOREIGN KEY REFERENCES PhongBan(MaPhong),
SoDienThoai CHAR(15),
NgayLamViec INT
);
alter table NhanVien add quequan nvarchar (20)
