use csdlnc_lab2

create table tapa

(

    id int primary key identity(1,1) not null,

    val1 nvarchar(10),

    val2 nvarchar(10)

)

create table tapb

(

    id int primary key identity(1,1) not null,

    val1 nvarchar(10),

    val2 nvarchar(10)

)

insert into tapb values('bon','nam')

select * from tapa

select * from tapb

--cau 1:

--a\b

select * from tapb t1

right outer join tapa t2

    on t1.valb1=t2.vala1

where t1.valb1 is null

--b\a

select * from tapa a

right outer join tapb b

    on a.vala1=b.valb1

where a.vala1 is null

--aUb

select * from tapa a

 full outer join tapb b

on a.vala1=b.valb1

--(aUb)\b

select * from tapa a

full outer join tapb b

    on a.vala1=b.valb1

where b.valb1 is null

--phan chung

select * from tapa a

inner join tapb b

    on a.vala1=b.valb1

--a xor b

select * from tapa a

full outer join tapb b

    on a.vala1=b.valb1

where a.vala1 is null or b.valb1 is null

--cau 2:

--tao bang sinh vien

create table sinhvien

(

    id int constraint k identity(1,1) primary key not null,

    hoten nvarchar(50) null,

    lop nvarchar(10) null

)

create proc cau2

(

    @id int output,

    @ht nvarchar(50),

    @l nvarchar(10),

    @k int

)

as

begin

    if @k=1

        begin

            insert into sinhvien(hoten,lop) values(@ht,@l)

            set @id=@@identity

        end

    else if @k=2

            begin

                update sinhvien

                set hoten=@ht, lop=@l

                where id=@id

            end

    else if @k=3

            begin

                delete sinhvien where id=@id

            end

    else

    begin

            print 'loi'

    end

end

exec cau2 4,'nguyen van d','ctk32',3

select * from sinhvien

--cau 3:viet ham thuc hien

--sinh so ngau nhien tu a den b

create function ngaunhien_a_b

(

    @seed  uniqueidentifier,

    @a int,

    @b int

)

returns int

as

begin

    set @a=(abs(checksum(@seed))%(@a-@b))+@a

    return @a

end

--chay thu

select dbo.ngaunhien_a_b (newid(),5,90)

--Sinh so ngau nhien bat ky khi chay seed

alter function ngaunhien

(

    @seed  uniqueidentifier

)

returns int

as

begin

    declare @a int

    set @a=abs(checksum(@seed))

    return @a

end

--chay thu

 select dbo.ngaunhien (newid())

--SINH RA NGAY NGAU NHIEN

alter function ngay_ngaunhien

(

    @seed  uniqueidentifier,

    @a datetime

)

returns datetime

as

begin

    declare @b int

    set @b=(checksum(@seed)

    set @a=dateadd(dd,@b%1000,@a)

    return @a

end

--chạy thử

select dbo.ngay_ngaunhien(newid(),getdate())

--Sinh ra 1 ký tự ngẫu nhiên

alter function chu_ngau_nhien

(

    @seed uniqueidentifier   

)

returns char

as

begin

    declare @b char(10)

    declare @a int

    set @a=(abs(checksum(@seed))%(65-90))+65

    set @b=char(@a)

    return @b

end

--Chay thu

select dbo.chu_ngau_nhien(newid())

--Sinh ra mot nhom bon ky tu ngau nhien

declare @i int

declare @c char

set @i=0

while @i<4

begin

    set @i=@i+1

    set @c=dbo.chu_ngau_nhien(newid())

    print @c

end

--Sinh ra mot nhom 5 ky tu ngau nhien va 1 so

declare @i int

declare @c char

declare @s int

set @i=0

while @i<5

begin

    set @i=@i+1

    set @c=dbo.chu_ngau_nhien(newid())

    print @c

end

set @s=dbo.ngaunhien(newid())

print @s