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
)
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

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

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

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

alter table Animal
add constraint fk_room_id foreign key (room_id) references Room (id)
go

-- Insert Data
insert into Room (id, name, max)
values
(1, 'Room 01', 2),
(2, 'Room 02', 5),
(3, 'Room 03', 3)
go

insert into FoodType (id, name, price, amount)
values
(1, 'Food 01', 1, 5),
(2, 'Food 02', 3, 2),
(3, 'Food 03', 5, 50)
go

select * from FoodType

insert into Animal (id, room_id, name, age, buy_at)
values
(1, 1, 'Animal 01', 2, '2020-02-19 08:00:00'),
(2, 1, 'Animal 02', 2, '2021-02-19 08:00:00'),
(3, 2, 'Animal 03', 5, '2021-02-19 08:00:00'),
(4, 2, 'Animal 04', 2, '2022-02-19 08:00:00'),
(5, 2, 'Animal 05', 3, '2022-02-19 08:00:00'),
(6, 2, 'Animal 06', 2, '2021-02-19 08:00:00'),
(7, 3, 'Animal 07', 2, '2020-02-19 08:00:00')
go

insert into Animal (id, room_id, name, age, buy_at)
values
(8, 1, 'Animal 08', 2, '2020-02-19 08:00:00')
go

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

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, tên động vật, tuổi, ngày mua về
select Room.name 'room_name', Animal.name 'animal_name', 
	Animal.age, Animal.buy_at
from Animal left join Room on Animal.room_id = Room.id
go

-- Thong ke so dong trong tung chuong: id, name, max, count
select room.id, room.name, Room.max, count(Animal.id) 'Count'
from Room left join Animal on Room.id = Animal.room_id
group by room.id, room.name, Room.max
having count(Animal.id) > Room.max
go

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

-- Viet truy van xem thong tin an: Dong vat + Food
select Animal.id, Animal.name, FoodType.name 'Food Name'
from Animal, FoodType, FoodAnimal
where Animal.id = FoodAnimal.animal_id
	and FoodType.id = FoodAnimal.food_id
go

select Animal.id, Animal.name, 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_view_type
	@animalId int
as
begin
	select Animal.id, Animal.name, FoodType.name 'Food Name'
	from Animal left join FoodAnimal on Animal.id = FoodAnimal.animal_id
		left join FoodType on FoodType.id = FoodAnimal.food_id
	where Animal.id = @animalId
end

exec proc_view_type 1
exec proc_view_type 5
exec proc_view_type 8