Tạo database Quản Lý Sinh Viên bằng sql Server

 -- lấy thông tin cac db hệ thông

select * from sys.databases;

--kiểm tra nếu data ko có tao mới

if not exists(select name from sys.databases where name='QLSinhVien_080522')

-- TẠO MỚI DATA

create database QLSinhVien_080522;

--

go

use QLSinhVien_080522;

-- tạo bảng chuyên khoa

Create table ChuyenKhoa

(

MaKhoa varchar(10) primary key,

TenKhoa nvarchar(150)

 

);

Create table SinhVien

(

MaSV varchar(10) primary key,

HoTen nvarchar (30),

GioiTinh tinyint,

NgaySinh date,

DienThoai varchar(20),

Email varchar (50),

DiaChi nvarchar (250)

 

);

go

-- sửa bản thêm cột

alter table SinhVien

add MaKhoa varchar(50);

-- sửa bảng để sửa cột

alter table SinhVien

alter column Makhoa varchar(10);

-- sửa bảng xóa cột

alter table SinhVien

drop column MaKhoa;

-- thiết lập kiểu quan hệ giữa 2 bảng SinhVien và ChuyenKhoa

alter Table SinhVien

add constraint FK_ChuyenKhoa_Id foreign key(Makhoa)

references ChuyenKhoa(MaKhoa)

 

-- tao môn học

create table MonHoc

(

MaMH varchar (10) primary key,

TenMonHoc nvarchar (150),

SoTinChi int

 

);

-- Tạo Bảng phòng học

Create Table PhongHoc

 

(

Maphong int identity (1,1) primary key,

TenPhong nvarchar (100)

);

--BANG đIỂM

Create table DiemThi

(

SinhVienId varchar(10),

MonHocId varchar (10),

Diem float,

NgayThi date,

PhongHocId int,

constraint PK_DiemThi_Id primary key(SinhVienId, MonHocId)-- kết hơp 2 khóa chính

 

);

-- sửa bảng để xóa ràng buộc khóa chính

Alter table DiemThi

drop constraint PK_DiemThi_Id;

 

---- Thêm ràng buộc khóa chính

Alter table DiemThi

add constraint PK_DiemThi_Id primary key(SinhVienId, MonHocId);

---------

-- thiết lập quan hệ giữa 2 bảng DiemThi vad SinhVien

Alter table DiemThi

add constraint FK_SinhVien_Id foreign key (SinhVienId)

references SinhVien(MaSV);

 

---Thiết lập giữa bảng DiemThi và môn học

alter table DiemThi

add constraint FK_MonHoc_Id foreign key (MonHocId)

references MonHoc(MaMH);

 

-- xóa bảng

drop table PhongHoc

---- thiết lập số điên thoại là duy nhất

alter table SinhVien

add constraint UIQ_Dienthoi UNIQUE(DienThoai);-- sô điện thoại trùng sẽ báo

 

-- cách import dữ liệu từ databese khác vào databese phải cùng kiểu dữ liêu

insert into SinhVien(MaSV, HoTen, DienThoai, Email, DiaChi)

select MaSV, HoTen, DienThoai, Email, DiaChi from QLSinhVien_080522.dbo.NhanVien;

 

-- kiểm tra thông tin thoa mãn điều kiện sô tín chỉ ko dduccoc nho hơn 0

alter Table MonHoc

add constraint CHECK_MonHoc CHECK (soTinChi>0);

 

----thiết lập giới tính mặc định là năm

alter table SinhVien

add constraint DEFAULT_GioiTinh Default(0) for GioiTinh

--  Thống kê 1 lần duy nhất nếu trùng mã nhiều

select distinct MaKhoa from SinhVien

 

-- lấy lương từ khoảng nào đến khoảng đến khoảng nào

use CSDL2022DV

select * from NhanVien where luong between 3000000 and 15000000;

-- tương dương

select * from NhanVien where luong >= 3000000 and luong <=15000000;

-- tìm người có họ là nguyễn

select *from NhanVien where HoTen like N'Nguyễn%'

-- tìm nhân viên có chứa tên lót thị

select *from NhanVien where HoTen like N'%thị%'

-- Hàm sô học

-- hàm làm tròn lấy 2 số sau dấu phảy

select round (5.6444353,2);

-- hàm lấy cận trên làm tròn lên

select CEILING (5.6444353);

-- hàm làm tròn xuống cận dưới

select Floor (5.6444353);

--- chuyển cùng chữ thương

select lower (N'Máy Chấm Công');

-- chuyển cùng chữ hoa

select upper (N'Máy Chấm Công');

--- Hàm ngày tháng

-- lấy ngày hiện tại

select getdate();

-- Hàm ngày tháng năm

select day(getdate()) as ngay, month (getdate()) as Thang, year (getdate()) as Nam;

 

-- thêm 1 ngày vào ngày hiện tại

select Dateadd(D,1, getdate());

-- thêm 1 tháng vào tháng hiện tại

select Dateadd(M,1, getdate());;

-- thêm 1 năm vào năm hiện tại

select Dateadd(yyyy,1,getdate());

 -- CHUYỂN ĐỔI TỪ CHUỖI SANG SỐ

 select cast ('023' as int )*3

 -- chuyển từ ngày tháng về chuỗi

 select convert (varchar, getdate(),103);

 -- chuyển ttuwfchuoi sang ngày tháng'

 select convert (date, '24/11/2022', 103);

 

  -- Đếm số lượng nhân viên' trong bảng: từ khóa as là hiện tên add vào qsl

  select count (*) as SoLuongNV from NhanVien;

  -- Tính tổng lương, trung bình, lớn nhất, nhỏ nhất, round,2 là tròn lấy 2 số

  -- Bai 17

  select sum(Luong) as TongLuong, round(avg(Luong),2) as LuongTB,

  MAX(Luong) as LuongLonNhat, Min(Luong) as LuongNhoNhat from NhanVien;

  -- thống kê số lượng nhân viên theo từng phòng ban

  select MaPhong, count(*) as SoLuongNV from NhanVien group by MaPhong;

 

  -- Thống kê số lượng nhân viên theo từng phòng ban

  select MaPhong, count (*) as SoLuongNV from NhanVien

  group by MaPhong;

  -- thống kê lương trung bình theo từng phòng ban

  select MaPhong, sum(luong) as TongLuong, avg(luong) as LuongTB from NhanVien

  group by Maphong;

 

  -- Thống kê số lượng nhân viên theo từng phòng ban với số lượng

  select MaPhong, count (*) as SoLuongNV from NhanVien

  group by MaPhong

  having count(*) >=5;

  select count (*) as SoNV from NhanVien;

  select * from NhanVien;

  -- Lấy thông tin 2 bản thõa mãn điều kiện khóa ngoại bằng khóa chính. nv chi định nhân viên đó

  select MaNV, HoTen, nv.DienThoai, nv.Email, nv.DiaChi, TenPhong from NhanVien nv inner join Phongban pb on  nv.MaPhong = pb.MaPhong;

  -- Lấy thông tin từ bảng trong đó ưu tiên lấy tất cả thông tin bảng bên trái'

  select MaNV, HoTen, nv.DienThoai, nv.Email, nv.DiaChi, TenPhong from NhanVien nv left join Phongban pb on  nv.MaPhong = pb.MaPhong;

  -- Lấy toàn bộ thông tin 2 bảng

  select MaNV, HoTen, nv.DienThoai, nv.Email, nv.DiaChi, TenPhong from NhanVien nv full join Phongban pb on  nv.MaPhong = pb.MaPhong;

  -- Nối 2 tập dữ liệu với nhau

  select MaNV, HoTen from NhanVien

  union

  select MaPhong, TenPhong from Phongban;

 

  -- Lấy tất cả nhân viên có lương lớn hơn lương trung bình trong hệ thống

  select * from NhanVien

  where Luong>(select avg(Luong) as LuongTB from NhanVien)