Bài tập - Thiết kế CSDL quản lý sở thú - SQL Server

-- Tao CSDL
create database BT2209
go

-- Kich hoat CSDL
use BT2209
go

-- Tao tables
create table Room (
	id int not null,
	name nvarchar(20),
	max int default 0
)
go

create table Animal (
	id int not null,
	name nvarchar(50),
	age int,
	buy_at datetime,
	room_id int
)
go

create table FoodType (
	id int not null,
	name nvarchar(50),
	price float,
	amount float
)
go

create table FoodAnimal (
	food_id int not null,
	animal_id int not null
)
go

-- PK
alter table Room
add constraint pk_room primary key (id)
go

alter table Animal
add constraint pk_animal primary key (id)
go

alter table FoodType
add constraint pk_food_type primary key (id)
go

alter table FoodAnimal
add constraint pk_food_animal primary key (food_id, animal_id)
go

-- FK
alter table Animal
add constraint fk_room_animal foreign key (room_id) references Room(id)
go

alter table FoodAnimal
add constraint fk_food_foodanimal foreign key (food_id) references FoodType(id)
go

alter table FoodAnimal
add constraint fk_animal_foodanimal foreign key (animal_id) references Animal(id)
go

-- insert data
insert into Room (id, name, max)
values
(1, 'Room 1', 2),
(2, 'Room 2', 5),
(3, 'Room 3', 10)
go

insert into FoodType (id, name, amount, price)
values
(1, 'Food 1', 20, 1000),
(2, 'Food 2', 10, 2000),
(3, 'Food 3', 30, 3000),
(4, 'Food 4', 50, 4000),
(5, 'Food 5', 60, 5000)
go

insert into Animal (id, room_id, name, age, buy_at)
values
(1, 1, 'Dong Vat 1', 2, '2022-01-12'),
(2, 1, 'Dong Vat 2', 2, '2022-01-12'),
(3, 2, 'Dong Vat 3', 2, '2022-01-12'),
(4, 2, 'Dong Vat 4', 2, '2022-01-12'),
(5, 2, 'Dong Vat 5', 2, '2022-01-12')
go

insert into Animal (id, room_id, name, age, buy_at)
values
(6, 1, 'Dong Vat 1', 2, '2022-01-12')
go

insert into FoodAnimal (animal_id, food_id)
values
(1, 1),
(1, 2),
(2, 1),
(3, 1),
(3, 2)
go

-- tên chuồng (Room), tên động vật (Animal), tuổi (Animal), ngày mua về (animal)
select Room.name 'Room Name', Animal.name 'Animal Name', Animal.age, Animal.buy_at
from Room left join Animal on Room.id = Animal.room_id
go

select Room.name 'Room Name', Animal.name 'Animal Name', Animal.age, Animal.buy_at
from Room join Animal on Room.id = Animal.room_id
go

-- Xem thông tin những chuồng có số động vật đang lưu vượt quá max của chuồng đó
---- 1. Dem so dong van trong chuong
---- 2. Xem max cua Room
select Room.name, Room.max, count(Animal.id) 'Tong'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
go

select Room.name, Room.max, count(Animal.id) 'Tong'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
having count(Animal.id) > Room.max
go

-- Xem thông tin những chuồng còn có khả năng lưu thêm động vật vào
select Room.name, Room.max, count(Animal.id) 'Tong'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
having count(Animal.id) < Room.max
go

-- Viết proc có tham số là @animalId -> cho phép xem được thông tin loại thức ăn của động vật này.
select Animal.name 'Animal Name', FoodType.name 'Food Name'
from Animal, FoodType, FoodAnimal
where Animal.id = FoodAnimal.animal_id
	and FoodType.id = FoodAnimal.food_id
go

create proc sp_view_food_by_animal
	@animalId int
as
begin
	select Animal.name 'Animal Name', FoodType.name 'Food Name'
	from Animal, FoodType, FoodAnimal
	where Animal.id = FoodAnimal.animal_id
		and FoodType.id = FoodAnimal.food_id
		and Animal.id = @animalId
end

exec sp_view_food_by_animal 1
exec sp_view_food_by_animal 2
exec sp_view_food_by_animal 3