# data[].attributes.attachment_link.readable_text
﻿ create database QLND ON(
 NAME=QLND,
 FILENAME='D:\Document\Hệ QTCSDL\BaiTap3\QLND.mdf'
 )
 --caau1
 create table User_DangNhap(
	TAIKHOAN nvarchar(10) NOT NULL primary key,
	MATKHAU nvarchar(10) NOT NULL,
 )

--câu 2
declare @Taikhoan nvarchar(10);
declare @Matkhau nvarchar(50);
set @Taikhoan = '001'
set @Matkhau ='1233'
if exists(select * from User_Dangnhap where @Taikhoan = TAIKHOAN and @Matkhau = MATKHAU)
    print 'Dang nhap thanh cong'
else
    print 'Dang nhap that bai'

--câu 3
declare @Taikhoan2 nvarchar(10);
declare @Mkhaucu nvarchar(50);
declare @Mkhaumoi nvarchar(50);
set @Taikhoan2 ='001'
set @Mkhaucu = '123'
set @Mkhaumoi = '456'
if exists(select * from User_Dangnhap where @Taikhoan2 = Taikhoan and @Mkhaucu = Matkhau)
    begin
        update User_Dangnhap set Matkhau = @Mkhaumoi
        where Taikhoan = @Taikhoan2
        print N'Đổi mật khẩu thành công'
    end
else
    print N'Không trùng mật khẩu cũ'

--câu 4
alter table SINHVIEN
    add Matkhau nvarchar(50), Email nvarchar(50)
--câu 5
--CÂU 6
UPDATE SINHVIEN
SET EMAIL=MASV + '@GMAIL.COM'
select * from SINHVIEN

--CÂU 7
DECLARE @MASV NCHAR(10)
DECLARE @MAMH NCHAR(10)
DECLARE @DIEMLAN1 FLOAT
SET @MASV='SV01'
SET @MAMH='CSDL'
SET @DIEMLAN1=7
IF EXISTS (select * from SINHVIEN where @MASV=MASV)
if exists (select * from MONHOC WHERE @MAMH=MAMH)
if exists (select * from DIEM WHERE @MASV=MASV AND @MAMH=MAMH)
    BEGIN
    UPDATE DIEM
    SET DIEMLAN1=@DIEMLAN1
    WHERE @MASV=MASV AND @MAMH=MAMH
    END
ELSE 
    INSERT INTO DIEM VALUES (@MASV,@MAMH,@DIEMLAN1,8)
ELSE PRINT'KHÔNG CÓ'

SELECT *FROM DIEM


--CÂU 8
DELETE FROM DIEM
WHERE MASV='SV01'
DELETE FROM SINHVIEN
WHERE MASV='SV01'
--câu 9
alter table SINHVIEN
--câu 10
create view dsKHOA
as
select l.MALOP,l.TENLOP, count(s.masv) as SOLUONG
from LOP l, SINHVIEN s
where l.MALOP=s.MALOP
group by l.MALOP,l.TENLOP

select case left(MALOP,3) when 'K19' then N'Khóa 19'
 when 'K20' then N'Khóa 20' end AS N'Khóa',sum(SOLUONG) as
SOLUONG
from dsKHOA
group by left(MALOP,3)
--câu 11
create view dsNGANH
as
select l.MALOP,l.TENLOP ,count(s.masv) as SOLUONG
from LOP l,SINHVIEN s
where l.MALOP=s.MALOP
group by l.MALOP,l.TENLOP 

select case SUBSTRING(MALOP, 4, 3) when 'QTM' then N'Ngành Quản trị mạng'
when 'TPM' then N'Ngành kỹ thuật phần mềm' 
when 'TTT' then N'Ngành Hệ thống thông tin' end as N'Ngành',sum(SOLUONG) as SOLUONG
from dsNGANH
group by SUBSTRING(MALOP, 4, 3)
--câu 12
CREATE VIEW DS_DIEM_KHOA AS 
select LEFT(s.MALOP,3) AS KHOA,(sum(d.DIEMLAN1*mh.SOTC)/sum(mh.SOTC)) as DTB
from SINHVIEN s,DIEM d,MONHOC mh
where s.MASV = d.MASV and d.MAMH = mh.MAMH
group by s.MALOP

select top 1 * from DS_DIEM_KHOA as d1
where DTB = (select max(DTB) from DS_DIEM_KHOA d2 where d1.KHOA = d2.KHOA)
Order by DTB desc

--CÂU 13
CREATE VIEW DS_DIEM_NGANH AS 
select case SUBSTRING(MALOP, 4, 3) when 'QTM' then N'Ngành Quản trị mạng'
when 'TPM' then N'Ngành kỹ thuật phần mềm' 
when 'HTT' then N'Ngành Hệ thống thông tin' end as NGANH ,(sum(d.DIEMLAN1*mh.SOTC)/sum(mh.SOTC)) as DTB
from SINHVIEN s,DIEM d,MONHOC mh
where s.MASV = d.MASV and d.MAMH = mh.MAMH
group by s.MALOP

select top 1 * from DS_DIEM_NGANH as d1
where DTB = (select max(DTB) from DS_DIEM_NGANH d2 where d1.NGANH = d2.NGANH)
Order by DTB desc
--câu14

 