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

-- Tao CSDL BT2209
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 int
)
go

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

-- primary key
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

-- foregin key
alter table Animal
add constraint fk_animal_room foreign key (room_id) references Room (id)
go

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

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

-- insert data
insert into Room (id, name, max)
values
(1, 'Room 01', 3),
(2, 'Room 02', 2),
(3, 'Room 03', 5),
(4, 'Room 04', 10),
(5, 'Room 05', 6)
go

insert into FoodType (id, name, price, amount)
values
(1, 'Food 01', 1000, 3),
(2, 'Food 02', 5000, 30),
(3, 'Food 03', 2000, 32),
(4, 'Food 04', 1500, 13),
(5, 'Food 05', 3200, 23)
go

insert into Animal (id, name, age, buy_at, room_id)
values
(1, 'Animal 01', 2, '2020-02-16', 1),
(2, 'Animal 02', 1, '2021-03-16', 1),
(3, 'Animal 03', 5, '2015-05-16', 1),
(4, 'Animal 04', 3, '2018-04-16', 1),
(5, 'Animal 05', 8, '2010-08-16', 2),
(6, 'Animal 06', 1, '2021-01-16', 2),
(7, 'Animal 07', 2, '2020-08-16', 3)
go

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

-- Show Data
select * from Room
select * from Animal
select * from FoodType
select * from FoodAnimal

-- Xem thông tin động vật gồm các trường sau: tên chuồng (Room), tên động vật (Animal), tuổi (ANimal), ngày mua về (Animal)
select Room.name, Animal.name 'AnimalName', Animal.age, Animal.buy_at
from Room left 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 đó
---- Room Name, Count(*) So Dong Van Trong Chuong
select Room.name, Room.max 'Max', count(Animal.id) 'So Dong'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
go

-- Tim chuong -> so dong vat > max
---- Tat cac dieu kien: count, max, min, sum, avg -> having
select Room.name, Room.max 'Max', count(Animal.id) 'So Dong'
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 so khả năng lưu thêm động vật vào
select Room.name, Room.max 'Max', count(Animal.id) 'So Dong'
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.
---- Ten Dong Vat (Animal), age (Animal), Food Name (FoodType)
select Animal.name, Animal.age, FoodType.name 'Food Name'
from Animal, FoodAnimal, FoodType
where Animal.id = FoodAnimal.animal_id
	and FoodAnimal.food_id = FoodType.id
go

-- chuyen sang dung inner join
select Animal.name, Animal.age, FoodType.name 'Food Name'
from Animal inner join FoodAnimal on Animal.id = FoodAnimal.animal_id
	inner join FoodType on FoodType.id = FoodAnimal.food_id
go

-- Chuyen sang dung left join
select Animal.name, Animal.age, FoodType.name 'Food Name'
from Animal left join FoodAnimal on Animal.id = FoodAnimal.animal_id
	left join FoodType on FoodType.id = FoodAnimal.food_id
go

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

exec proc_find_food 1
exec proc_find_food 2
exec proc_find_food 3