Dữ liệu thi môn HQTCSDL I

Cơ sở dự liệu thi môn: Hệ Quản Trị Cơ Sở Dữ Liệu 1
Cho cơ sở dự liệu thể hiện các bản tin rao bán, mua … cho các sản phẩm bất động sản (BDS).

Quocgia (maquocgia char(3), tenquocgia char(50))

Thanhpho (mathanhpho char(3), tenthanhpho char(50), maquocgia char(3))

Loaitin (maloaitin char(1), ghichu char(50)
Ví dụ dữ liệu
maloaitin Ghichu
B: Tin rao bán
M: Tin rao cần mua

LoaiBDS (maloaibds char(3), ghichu char(50))
Chỉ các loại hình BDS, ví dụ như nhà biệt thự, đất dự án, …

Nguoidang (manguoi char(5), tennguoi char(50), mathanhpho_song char(3))
Dữ liệu của trường “mathanhpho_song” là các mã thành phố

TinBDS (matin int, noidung nvarchar(500), maloaitin char(1), maloaibds char(3), dientich int, gia int, mathanhpho_bds char(3), manguoi_dang char(5), ngaydangtin datetime)
Lưu các bản tin rao của các người đăng tin.
Giả sử giá được tính theo đơn vị triệu đồng (không lẻ).

Tham Khảo WEB: [You must be registered and logged in to see this link.]

Thanks

thanks!

Thank pạn nhìu nhìu ^^

Pà con ơi !
Có bạn học ở lớp tin học. Bạn nào có nghĩ ra Bài tập về : Truy Vấn hay Trigger nào dựa trên CSDL thầy cho để lớp mình tham khảo nhé. Vừa để mình tập làm quen với CSDL này và cũng là có tài liệu để vào phòng thi nhé ! ^^

Ví dụ vài câu:
1. Cho biết các tin đăng bán của ông A
2. Cho biết các tin đăng bán của ông A có giá cao nhất
3. Các tin rao ở Tp.hcm
4 Các tin đăng bán nhà biệt thự ở Tp.HCM có giá dưới 7000triệu tin đăng trong 1 tháng trở lại đây
5. List những người đang tin bán nhiều hơn tin mua
6. Thông kê tin đăng theo từng người và từng loại Mua/Bán
...

Trigger:
1. 1 người chỉ được đăng tối đa 10 tin
2. 1 người chỉ được đăng tối đa 10 tin trong vòng 1 tháng
3. 1 người không được bán tổng các tài sản vượt quá 10.000 triệu
4. 1 người không được đăng tin có liên quan đến 2 quốc gia trở lên

good luck to all :-)

Cam on nhe!

Oh may cau hoi rat hay!
Thank you very much!

1. Danh sách những người đăng tin tại sống tại tphcm
//
SELECT t.manguoi_dang, th.tennguoidang, tentp
From ((tinBDS t inner join Nguoidang n on t.manguoi_dang=n.manguoi) inner join Thanhpho th on n.mathanhpho_song=th.mathanhpho)
Where th.tenthanhpho=”hcm”.
2. Danh sách những tin bds ở việt nam
//
Select t.matin, t.noidungtin,..,q.tenquocgia
From ((tinBDS t inner join thanhpho th on t.mathanhpho_bds=th.mathanhpho)inner join quocgia q on th.maquocgia=q.maquocgia)
Where q.tenquocgia=”vietnam”
3. Thống kê có bao nhiêu bản tin mua và bán theo ma loai tin:
//
Select tinbds.maloaitin,count(*)
From tinbds
Group by tinbds.maloaitin
1. Viết trigger trên bảng Tinbds sao cho khi nhập giá hợp lý.
//
Create trigger check_gia
On tinbds
For insert,update, delete
As
Begin
Declare @gianhap int
Select @gianhap=gia from inserted
If (@gianhap< 0)
Rollback transaction
Else
Update tinbds set gia=@gianhap
End
Ví dụ vài câu:
1. Cho biết các tin đăng bán của ông A
Select *
From Tinbds t inner join nguoidang n on t.nguoi_dang=n.manguoi
Where n.tennguoi=”a”
2. Cho biết các tin đăng bán của ông A có giá cao nhất
Select *
From Tinbds t inner join nguoidang n on t.nguoi_dang=n.manguoi
Where n.tennguoi=”a” and t.gia=(select max(gia) from Tinbds t inner join nguoidang n on t.nguoi_dang=n.manguoi
Where n.tennguoi=”a”)

3. Các tin rao ở Tp.hcm
Select *
From tinbds t inner join thanhpho tp on t.mathanhphobds=tp.mathanhpho
Where tp.tentp=”ho chi minh”
4 Các tin đăng bán nhà biệt thự ở Tp.HCM có giá dưới 7000triệu va tin đăng trong 1 tháng trở lại đây
Select *
From ((tinbds t inner join thanhpho tp on t.mathanhphobds=tp.mathanhpho) inner join loaibds l on t.maloaibds=l.maloaibds)
Where t.maloaitin=’B’ and tp.tenthanhpho=’ho chi minh’ and t.gia<7000 and (hoi phuc tap)
4. List những người đang tin bán nhiều hơn tin mua
Select n.maguoi, n.tennguoi
From tinbds t inner join nguoidang n on t.manguoi_dang = n.manguoi
Where ((select count(maloaitin) from tinbds where tinbds.maloaitin=’B’)> (select count(maloaitin) from tinbds where tinbds.maloaitin=’M’))

6. Thông kê tin đăng theo từng người và từng loại Mua/Bán
Select t.manguoi_dang, n.tennguoi, count(t.loaibds) as Sl_mua
From tinbds t inner join nguoidang n on t.manguoi_dang=n.manguoi
Where t.loaibds=’M’
Group by t.manguoi_dang, n.tennguoi
Union
Select t.manguoi_dang, n.tennguoi, count(t.loaibds) as Sl_ban
From tinbds t inner join nguoidang n on t.manguoi_dang=n.manguoi
Where t.loaibds=’B’
Group by t.manguoi_dang, n.tennguoi
...

Trigger:
1. 1 người chỉ được đăng tối đa 10 tin
Create trigger cau1
On tinbds
For insert,update
As
Begin
Declare @so_tin int
Select @so_tin=count(*) from tinbds t, inserted i
where t.manguoi_dang=i.manguoi_dang
If( @so_tin >10 )
Rollback transaction
Else
Update tinbds
End

2. 1 người chỉ được đăng tối đa 10 tin trong vòng 1 tháng
Create trigger cau2
On tinbds
For insert,update
As
Begin
Declare @so_tin int
Select @so_tin=count(*) from tinbds t ,inserted i
where t.manguoi_dang=i.manguoi_dang
and month(inserted.ngaydangtin)=month(tinbds.ngaydangtin)
If( @so_tin >10 )
Rollback transaction
Else
Update tinbds
End


3. 1 người không được bán tổng các tài sản vượt quá 10.000 triệu
Create trigger cau3
On tinbds
For insert, update
As
Begin
Declare @tongts decimal(10,2)
Select @tongts = sum(dientich*gia) from tinbds, inserted
Where tinbds.manguoi_dang=inserted.manguoi and tinbds.loaibds=’B’
If ( @tongts>10000)
Rollback transaction
Else
Update tinbds
End

4. 1 người không được đăng tin có liên quan đến 2 quốc gia trở lên
create trigger cau4
On tinbds
For insert, update
As
Begin
Declare @quocgia int
Select @quocgia1=count(thanhpho.maquocgia) from tinbds,thanhpho,inserted where tinbds.mathanhpho=thanhpho.mathanhpho and tinbds.manguoi_dang=inserted.manguoi_dang and thanhpho.mathanhpho
=tinbds.mathanhpho group by thanhpho.maquocgia
If(@quocgia1>1)
Rollback transaction
Else
Update tinbds
end

hihi tạm ổn nhưng van xem lai chút, trong trigger dạng fọr thì dữ liệu đã vào DB rồi, lưu ý khi count, sum... là có cả dữ liệu vừa mới vào đó
mấy câu anh Sáu làm chưa chính xác lắm đâu nghen, anh Sáu test thử đi nhé
Câu này hay nè mọi người suy nghĩ nhé:
Tìm những người mà mục tin nào họ cũng có bài rao hết (phép chia)

Cám ơn thầy, thầy cho đáp án luôn đi thầy

Hi iii
Cảm ơn những câu hỏi và ý kiến đóng góp rất nhiệt tình cua thầy Thịnh nhen!
Các bạn nào có tạo DB rùi thì test thử mấy câu S làm lun nhen! Có j sửa lại lun nha!
Lưu ý là phần trigger ở đây mình giả định là chỉ cho 1 mẫu tin duy nhất được thêm vào và thao tác nói chung trên toàn mẫu tin( không có if update column) vì đề thi chỉ đòi hỏi bấy nhiêu thui nên mình chỉ làm cơ bản nhất!!
Chúc mọi người thi tốt nhé!!!.

Mình có cái câu số 5:
lười quá, nên vừa nghĩ ra là viết lại thôi. các bạn test giùm mình với.

create table #TAM (Nguoi char(5), TinBan numeric, TinMua Numeric)
insert #TAM (Nguoi,TinBan,TinMua)
select manguoi,0 Tinban, 0 TinMua from Nguoidang
declare _mycur cursor for select Nguoi from #TAM
declare @sMaNguoi char(5)
Open _mycur
fetch next from _mycur into @sMaNguoi
while(@@fetch_status =0)
begin
declare @sTinBan numeric
select @sTinBan=count(matin)
from tinbds
where maloaitin='B' and manguoi_dang=@sMaNguoi


Update #TAM set Tinban=@sTinBan
where Nguoi=@sMaNguoi

declare @sTinMua numeric
select @sTinMua=count(matin)
from tinbds
where maloaitin='M' and manguoi_dang=@sMaNguoi


Update #TAM set TinMua=@sTinMua
where Nguoi=@sMaNguoi

fetch next from _mycur into @sMaNguoi
end
close _mycur
deallocate _mycur
select * from #TAM A inner join nguoidang B on A.Nguoi=B.manguoi
where tinban>tinmua
drop table #TAM

Hoặc một cách khác nhưng vẫn theo cách suy nghĩ cũ.

with tblTruy (MaNguoi,TenNguoi,SoTinBan,SoTinMua)
as (
select MaNguoi_dang,
TenNguoi,
sum(tinban) as SoTinBan,
sum(tinmua) as SoTinBan
from (
select manguoi_dang,count(matin) tinban,'' tinmua
from tinbds
where maloaitin='B'
group by manguoi_dang
union
select manguoi_dang,'' tinban,count(matin) tinmua
from tinbds
where maloaitin='M'
group by manguoi_dang
) H
inner join NguoiDang N on H.Manguoi_dang=N.manguoi
group by Manguoi_dang,TenNguoi
)
select * from tblTruy
where SoTinBan>SoTinMua

4.List những người đang tin bán nhiều hơn tin mua

Code:

Select n.maguoi, n.tennguoi
From tinbds t inner join nguoidang n on t.manguoi_dang = n.manguoi
Where ((select count(maloaitin) from tinbds where tinbds.maloaitin=’B’)> (select count(maloaitin) from tinbds where tinbds.maloaitin=’M’))

Câu này có vấn đề òi tác giả ^^! Theo mình biết thì phép count phải dùng group by điều kiện sau group by thì cho xuống phần Having. Bạn nào biết thì fix câu này giúp mọi người nhé. Thx for sharing

5. List những người đang tin bán nhiều hơn tin mua
WITH BANGHOP AS
(
SELECT MANGUOI_DANG, COUNT(MALOAITIN) AS TSBAN, 0 AS TSMUA
FROM TINBDS
WHERE MALOAITIN = 'B'
GROUP BY MANGUOI_DANG
UNION
SELECT MANGUOI_DANG, 0 AS TSBAN, COUNT(MALOAITIN) AS TSMUA
FROM TINBDS
WHERE MALOAITIN = 'M'
GROUP BY MANGUOI_DANG
)
SELECT MANGUOI_DANG, SUM(TSBAN) AS BAN, SUM(TSMUA) AS MUA
FROM BANGHOP
GROUP BY MANGUOI_DANG
HAVING SUM(TSBAN) > SUM(TSMUA)


6. Thông kê tin đăng theo từng người và từng loại Mua/Bán
SELECT MANGUOI_DANG, MALOAITIN, COUNT(MALOAITIN) AS TONGSOTIN
FROM TINBDS
GROUP BY MANGUOI_DANG, MALOAITIN

Bài của bạn huynhtantien ^^! Nếu thầy chấp nhận thì chính xác. Trong thực tế cũng dùng bảng tạm cho các câu này hihihihi theo mình nghĩ là dzị.

Hix.
Hôm nay thi rồi nhé.
Chúc các bạn thành công nak.

*PS: mà cũng công nhận cái forum của mình lìu tìu quá.

Tớ mới check xong câu này^^

// Một người chỉ được đăng tối đa 10 tin

Code:

create trigger gioihandang
on TinBDS
for insert, update
as
   begin
      declare @manguoidang char(5)
      declare @dem int
      select @manguoidang = manguoi_dang from inserted
      select @dem = count(*) from TinBDS where manguoi_dang = @manguoidang group by manguoi_dang
      if(@dem>10)
            rollback transaction

   end