2021-04-20 10:20:57

create database CSDL_Quan_Ly_So_Thu

use CSDL_Quan_Ly_So_Thu

-- tao bang ROOm--
create table Room(
	id int primary key identity(1,1),
	name nvarchar(20),
	max int
)
-- tao bang Animal--
create table Animal(
	id int primary key identity(1,1),
	name nvarchar(50),
	age int,
	buy_at datetime,
	room_id int,
	constraint fk_room_id foreign key (room_id) references Room(id)
)
-- tao bang loai Food--
create table FoodType(
	id int primary key,
	name nvarchar(50),
	price float,
	amount float
)
-- tao bang FoodAnimal--
create table FoodAnimal(
	food_id int,
	animal_id int
	primary key( food_id, animal_id)
	constraint fk_food_id foreign key (food_id) references FoodType(id),
	constraint fk_animal_id foreign key (animal_id) references Animal(id)
)
--2. da tao nhu tren--

--3. them 5 ban ghi moi Bang--
insert into Room(name,max)
values
('Chuong 1', 20),
('Chuong 2', 10),
('Chuong 3',15),
('Chuong 4',25),
('Chuong 5',30)

select * from Room

insert into Animal(name,age,buy_at,room_id)
values
('Voi',11,'2021-4-5',1),
('Su Tu',12,'2021-4-5',3),
('Khi',13,'2021-4-5',5),
('Ngua Van',14,'2021-4-5',4),
('Huou Cao Co',15,'2021-4-5',2)

select * from Animal

insert into FoodType(id,name,price,amount)
values
(1,'Co 4 la',10000,11),
(2,'Thit Heo',50000,12),
(3,'Co 5 la',40000,13),
(4,'Co luck luck',20000,14),
(5,'Co Cao Co',30000,15)

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

--xem cac du lieu--
select *  from Room
select *  from Animal
select *  from FoodType
select *  from FoodAnimal

--4. xem thong tin gom cac Truong : ten chuong, ten dong vat, tuoi, ngay mua ve

select Room.name 'Ten Chuong' , Animal.name'Ten Dong Vat',Animal.age'Tuoi',Animal.buy_at'Ngay Mua Ve'
from Room,Animal
where Room.id = Animal.room_id

--5. Xem Thông tin những chuồng có số động vật đang lưu vượt quá max của chuồng--

select Room.name 'Ten Chuong', Room.name'Ten Chuong', count(Animal.room_id) 'So Luong Chuong'
from Room,Animal
where Room.id = Animal.id
group by Room.id, Room.name
having count(Animal.room_id) > 2

--6.Xem Thông tin những chuồng con co kha nang luu them dong vat--

select Room.name 'Ten Chuong', Room.name'Ten Chuong', count(Animal.room_id) 'So Luong Chuong'
from Room,Animal
where Room.id = Animal.id
group by Room.id, Room.name
having count(Animal.room_id) < 30


--7.Viet proc cho phep xem thong tin loai thuc an cua dong vat--
create proc Proc_FoodAnimal_check 
	@animalld int
as
begin
	select Animal.name,Animal.age,FoodType.name,Animal.id
	from Animal,FoodType,FoodAnimal
	where Animal.id = FoodAnimal.animal_id
		and FoodAnimal.food_id = FoodType.id
		and Animal.id =@animalld
end

exec Proc_FoodAnimal_check 3 

--8 .trigger --

create trigger trigger_inteadOfDelete_FoodType on FoodType
instead of delete
as
begin
	delete from FoodAnimal where food_id in (select id from deleted)
	delete from FoodType where id in (select id from deleted)
end

delete from FoodType where id = 1

2021-04-18 10:23:50

create database zootopia 

use zootopia

create table Room
(
ID int primary key identity(1,1),
name nvarchar(25),
chuong int,
check (chuong<10)
)

create table Animal
(
ID int primary key identity(1,1),
name nvarchar(50),
age int,
buy_at datetime,
room_id int
)

create table FoodType
(
ID int primary key identity(1,1),
name nvarchar(50),
price float,
amount float
)

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

drop table FoodAnimal
alter table Animal
add constraint FK_RID foreign key(room_id) references Room(ID)

alter table FoodAnimal
add constraint PK_A Primary key(food_id,animal_id)

alter table FoodAnimal
add constraint FK_Food foreign key(food_id) references FoodType(ID)


insert into Room(name,chuong)
values
('Chuong cho',9),
('Chuong bo',2),
('Chuong lon',5),
('Chuong ga',9),
('Chuong ngua',4)

insert into Animal(name,age,buy_at,room_id)
values
('Corgi',5,'2021-01-05 18:00:00',1),
('Belge',8,'2021-02-05 17:30:00',1),
('Doberman',2,'2021-02-05 15:25:00',1),
('MooMoo',10,'2021-03-05 07:00:00',2),
('COWard',11,'2021-03-06 08:00:00',2),
('Peppa',2,'2021-01-09 19:00:00',3),
('Egypt',5,'2021-03-05 14:00:00',4),
('Big Black Cock',1,'2021-01-06 06:00:00',4),
('EggMachine',1,'2021-01-06 09:00:00',4),
('Al',12,'2021-01-18 16:00:00',5)

insert into FoodType(name,price,amount)
values
('Gieng',5.000,3.4),
('Gung',10.000,5.4),
('Weed',500.000,2.5),
('SaOt',10.000,4.3)

insert into FoodAnimal(food_id,animal_id)
values
(1,1),
(1,2),
(1,3),
(2,7),
(2,8),
(2,9),
(3,4),
(3,5),
(4,6),
(4,10)

drop table FoodAnimal
select *from Animal
alter table FoodAnimal
add constraint FK_Fad foreign key(animal_id) references Animal(ID)

select Room.name 'Ten chuong', Animal.name 'Ten Dong Vat', Animal.age 'Tuoi', Animal.buy_at 'Ngay mua'
from Animal,Room
where Room.ID=Animal.room_id

select Room.ID 'Ma Chuong', Room.name 'Ten chuong', Count(Animal.room_id) 'So luong'
from Animal,Room
where Room.ID=Animal.room_id
group by Room.name,Room.ID
Having Count(Animal.room_id)>2

select Room.ID 'Ma Chuong', Room.name 'Ten chuong', Count(Animal.room_id) 'So luong'
from Animal,Room
where Room.ID=Animal.room_id
group by Room.name,Room.ID
Having Count(Animal.room_id)<10

create proc LTA 
@animalid int
as
select Animal.name 'Ten Vat Nuoi', FoodType.name 'Loai Thuc An'
from Animal,FoodAnimal,FoodType
where Animal.ID = FoodAnimal.animal_id
and FoodType.ID = FoodAnimal.food_id
and @animalid = FoodAnimal.animal_id

exec LTA 7

drop proc LTA

create trigger Xoa_Food on FoodType

instead of delete
as
begin
delete from FoodAnimal where food_id in (select id from deleted)
delete from FoodType where ID in (select id from deleted)
end

drop trigger Xoa_Food
delete from FoodType
where ID = 1


select*from Animal
Select*from Room
Select*from FoodType
Select*from FoodAnimal

2021-04-18 07:45:10

create database db0417_QLsoThu

use db0417_QLsoThu

create table Room (
	id int not null,
	name nvarchar(20),
	max int
)

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

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

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



alter table Room
add primary key (id)

alter table Animal
add primary key (id)

alter table FoodType
add primary key (id)

alter table FoodAnimal
add primary key (food_id, animal_id)



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

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

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



insert into Room
values
	(1, 'Chuong 1', 5),
	(2, 'Chuong 2', 1),
	(3, 'Chuong 3', 10),
	(4, 'Chuong 4', 12),
	(5, 'Chuong 5', 21)

insert into FoodType
values
	(21, 'Thit bo', 100000, 50),
	(22, 'Thit lon', 50000, 100),
	(23, 'Thit ga', 70000, 75),
	(24, 'Tao', 50000, 150),
	(25, 'Co', 5000, 250)
	
insert into Animal
values
	(31, 'Ho cai', 3, '2021-01-01 08:00:00', 2),
	(32, 'Ho duc', 5, '2021-03-12 08:35:00', 2),
	(33, 'Da dieu', 6, '2021-04-01 08:00:00', 3),
	(34, 'Huou', 4, '2021-04-10 16:00:00', 5),
	(35, 'Su tu cai', 1, '2021-04-15 08:00:00', 4)

insert into FoodAnimal
values
	(21, 31),
	(23, 32),
	(24, 33),
	(25, 34),
	(22, 35)



select Room.id 'IdChuong', Room.name 'TenChuong', Animal.name 'TenDongVat', Animal.age 'Tuoi', Animal.buy_at 'NgayMuaVe'
from Room join Animal on Room.id = Animal.room_id
order by Room.id

select Room.*, count(Animal.room_id) 'SoLuongCan', count(Animal.room_id) - Room.max 'SoLuongConThieu'
from Room join Animal on Room.id = Animal.room_id
group by Room.id, Room.name, Room.max
having count(Animal.room_id) > Room.max

select Room.*, count(Animal.room_id) 'SoLuongCan', Room.max - count(Animal.room_id) 'SoLuongConThua'
from Room join Animal on Room.id = Animal.room_id
group by Room.id, Room.name, Room.max
having count(Animal.room_id) < Room.max



create proc proc_view_foodType @animalID int
as
	select Animal.id 'Animal_ID', Animal.name 'Animal_Name', FoodType.*
	from FoodType join FoodAnimal on FoodType.id = FoodAnimal.food_id
	join Animal on FoodAnimal.animal_id = Animal.id
	where Animal.id = @animalID
go

exec proc_view_foodType '34'

create trigger delete_in_FoodType on FoodType
instead of delete
as
	delete from FoodAnimal where food_id in (select id from deleted)
	delete from FoodType where id in (select id from deleted)
go

select * from FoodType

delete from FoodType where id = 23

select * from FoodAnimal

2021-04-17 14:17:48

create table Room (
	RoomID int identity(1,1),
	RoomName nvarchar(20),
	Max int 
)

create table Animal (
	AnimalID int identity(1,1),
	AnimalName nvarchar(50),
	AnimalAge int,
	BuyDate datetime,
	RoomID int
)

create table FoodType (
	FoodID int identity(1,1),
	FoodName nvarchar(50),
	Price float,
	Amount float
)

create table FoodManagement (
	FoodID int,
	AnimalID int
)

alter table Room
add primary key (RoomID);

alter table Animal
add primary key (AnimalID);

alter table FoodType
add primary key (FoodID);

alter table Animal
add foreign key (RoomID) references Room(RoomID);

alter table FoodManagement
add foreign key (FoodID) references FoodType(FoodID);

alter table FoodManagement
add foreign key (AnimalID) references Animal(AnimalID);

insert into Room(RoomName, Max)
values
('Room A', 20),
('Room B', 40),
('Room C', 15),
('Room D', 35),
('Room E', 10)

select* from Room
select* from Animal
select* from FoodType
select* from FoodManagement

insert into Animal(AnimalName, AnimalAge, BuyDate, RoomID)
values
('Monkey', 4, '2020-10-10', 4),
('Cheetah', 6, '2020-11-20', 1),
('Flamingo', 1, '2020-04-18', 2),
('Rhino', 7, '2020-10-07', 5),
('Giraffe', 2, '2020-05-30', 3)

insert into FoodType(FoodName, Price, Amount)
values
('Banana', 4000000.500, 20),
('Meat', 2000000.500, 44),
('Seed', 100000.500, 22),
('Rats', 5000000.500, 40),
('Herbs', 500000.500, 35)


insert into FoodManagement(FoodID, AnimalID)
values
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5)

select Room.RoomName, Animal.AnimalName, Animal.AnimalAge, Animal.BuyDate
from Room, Animal
where Animal.RoomID = Room.RoomID

create proc Procedure_Check_FoodType
@animalID int
as
begin
	select Animal.AnimalName, FoodType.FoodName, FoodType.Price, FoodType.Amount
	from Animal, FoodType, FoodManagement
	where Animal.AnimalID = FoodManagement.AnimalID
		and FoodManagement.FoodID = FoodType.FoodID
		and Animal.AnimalID = @animalID
end

exec Procedure_Check_FoodType 3;

create trigger Foodtype_Delete 
on FoodType
instead of delete
as 
begin
	delete from 
end

2021-03-09 09:46:42

create database QuanLySoThu

use QuanLySoThu

create table Room(

Id int not null,

Name nvarchar(20),

Max int

)

create table Animal(

Id int not null,

Name nvarchar(50),

Age int,

Buy_At date,

Room_Id int

)

create table FoodType(

Id int not null,

Name nvarchar(50),

Price float,

Amount float

)

create table FoodAnimal(

Food_Id int not null,

Animal_Id int not null

)

alter table Room

add constraint PK_Room primary key (Id)

alter table Animal

add constraint PK_Animal primary key (Id)

alter table FoodType

add constraint PK_Food_Type primary key (Id)

alter table Animal

add constraint FK_Animal foreign key (Room_Id) references Room(Id)

alter table FoodAnimal

add constraint Fk_Food_Id foreign key (Food_Id) references FoodType(Id)

alter table FoodAnimal

add constraint FK_Animal_Id foreign key (Animal_Id) references Animal(Id)

insert into Room(Id,Name,Max)

values

(1,'Cho',10),

(2,'Meo',15),

(3,'Ngua',5),

(4,'Ho',3),

(5,'Voi',7)

insert into Animal(Id, Name, Age,Buy_At, Room_Id)

values

(1, 'A', 5, '2020-10-10 10:00:00', 3),

(2, 'B', 3, '2021-03-02 11:00:00', 1),

(3, 'C', 10, '2020-12-12 05:00:00', 5),

(4, 'D', 4, '2021-02-05 16:30:00', 4),

(5, 'E', 7, '2020-05-06 18:42:06', 2)

insert into FoodType(Id, Name, Price, Amount)

values 

(3, 'Thuc an cho', 100000, 3428),

(2, 'Thuc an meo', 200000, 584),

(1, 'Thuc an ngua', 150000, 3648),

(5, 'Thuc an ho', 250000, 8694),

(4, 'Thuc an voi', 50000, 3578)

insert into FoodAnimal(Food_Id, Animal_Id)

values

(1, 3),

(2, 2),

(3, 1),

(4, 5),

(5, 4)

select room.Name as RoomName, Animal.Name as AnimalName, Animal.Age, animal.Buy_At 

from Room, Animal

where Animal.Room_Id = room.id

select room.Id, Room.Max, Room.Name, COUNT(Animal.Room_Id) as SoDongVat

from Animal, Room 

where Animal.Room_Id = room.id

group by room.Id, Room.Max, Room.Name having COUNT(Animal.Room_Id) > max(room.Max)

select room.Id, Room.Max, Room.Name, COUNT(Animal.Room_Id) as SoDongVat

from Animal, Room 

where Animal.Room_Id = room.id

group by room.Id, Room.Max, Room.Name having COUNT(Animal.Room_Id) < max(room.Max)

create proc Proc_Thong_Tin_Thuc_An

@animalId int

as

begin

select * from FoodType, FoodAnimal

where FoodType.Id = FoodAnimal.Food_Id and @animalId = FoodType.Id

end

exec Proc_Thong_Tin_Thuc_An 4

create trigger trigger_delete_food on FoodType

instead of delete

as

begin

delete from foodanimal where food_id in (select id from deleted)

delete from FoodType where id in (select id from deleted)

end 

delete from FoodType where id = 1

2021-03-09 09:45:46

create table room(
	id int not null,
	name nvarchar(20),
	max int
)

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

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

create table foodanimal(
	food_id int not null,
	animal_id int not null
)

alter table room
add primary key (id)

alter table animal
add primary key (id)

alter table foodtype
add primary key (id)

alter table foodanimal
add primary key (food_id, animal_id)

alter table animal
add foreign key (room_id) references room(id)

alter table foodanimal
add foreign key (food_id) references foodtype(id)

alter table foodanimal
add foreign key (animal_id) references animal(id)

insert into room(id,name,max)
values
(1, 'C1', 10),
(2, 'C2', 10),
(3, 'C3', 10),
(4, 'C4', 10),
(5, 'C5', 10)

insert into animal(id,name,age,buy_at,room_id)
values
(1, 'ho', 4, '2020-15-10', 1),
(2, 'su tu', 2, '2020-15-10', 2),
(3, 'voi', 6, '2020-15-10', 3),
(4, 'khi', 10, '2020-15-10', 4),
(5, 'huou', 4, '2020-15-10', 5)

insert into foodtype(id, name, price, amount)
values
(1, 'apple', 100000, 20),
(2, 'meat', 200000, 20),
(3, 'weed', 1000000, 20),
(4, 'banana', 10000, 20),
(5, 'crack', 1000000, 20)

2021-03-09 09:45:45

create table Room(
	id int ,
	name nvarchar(20),
	max int
)
create table Animal1(
	id int not null,
	name nvarchar(50),
	age int,
	buy_at datetime,
	room_id int

)

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

)
create table FoodAnimal2(
	food_id int not null,
	animal_id int
)
alter table Room
add constraint Room_PK primary key (id)

alter table Animal1
add constraint Animal_PK primary key (id)

alter table FoodType1
add constraint FoodType_PK primary key (id)

alter table FoodAnimal2
add constraint FoodAnimal_PK primary key (food_id)


alter table Animal1
add constraint Animal_FoodType1_PK foreign key (id) references FoodType1 (id)

alter table Animal1
add constraint Animal_FoodAnimal2_PK foreign key (id) references FoodAnimal2 (food_id)

alter table FoodType1
add constraint FoodType1_FoodAnimal2_PK foreign key (id) references FoodAnimal2 (food_id)

alter table Room
add constraint Room_Animal1_PK foreign key (id) references Animal1 (id)

insert into Room(name, max)
values
('A1','50'),
('B1','50'),
('C1','50')
insert into Animal1(id,name, age, buy_at, room_id)
values
(1,'Ho','3','1990-12-02', 1),
(2,'Su Tu','3','1990-12-02', 2),
(3,'Khi','3','1990-12-02', 3)
insert into FoodType1(id,name, price, amount)
values
(1,'thit', 20000, 10),
(2,'thit', 20000, 10),
(3,'thit', 20000, 10)
insert into FoodAnimal2(food_id, animal_id)
values
('1', '1'),
('2', '2'),
('3', '3')

select Room.name , Animal1.name, Animal1.age, Animal1.buy_at
from Room, Animal1
where Room.id = Animal1.id

2021-03-09 09:24:37

create database Zoo

use Zoo

create table Room(
	id int not null,
	name nvarchar(20),
	max int
)

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

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

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

alter table Room 
add primary key (id)

alter table Animal
add primary key (id)

alter table FoodType
add primary key (id)

alter table FoodAnimal
add primary key (food_id, animal_id)

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

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

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

insert into Room(id, name, max)
values
(1, 'LionRoom', 4),
(2, 'PandaRoom', 6),
(3, 'TigerRoom', 3),
(4, 'ElephantRoom', 5),
(5, 'MonkeyRoom', 7)


insert into Animal(id, name, age, buy_at, room_id)
values
(1, 'Lion1', 6, '2015-03-02', 1),
(2, 'Tiger1', 6, '2015-03-02', 3),
(3, 'Tiger2', 5, '2016-06-04', 3),
(4, 'Tiger3', 4, '2017-03-08', 3),
(5, 'Tiger4', 4, '2017-07-02', 3),
(6, 'Elephant1', 6, '2015-03-02', 4),
(7, 'Monkey1', 6, '2015-03-02', 5),
(8, 'Panda1', 6, '2015-03-02', 2)

insert into FoodType(id, name, price, amount)
values
(1, 'Banana', 2000, 50),
(2, 'Blueberry', 3000, 30),
(3, 'Biscuits', 15000, 60),
(4, 'Eggs', 10000, 40),
(5, 'Meat', 50000, 20)

insert into FoodAnimal(food_id, animal_id)
values 
(3, 7),
(1, 7),
(2, 8),
(3, 6),
(5, 1),
(5, 2),
(5, 3),
(5, 4),
(5, 5)

select Room.name, Animal.name, Animal.age, Animal.buy_at
from Room, Animal
where Room.id =Animal.room_id

select Room.name, Room.max, Count(Animal.room_id)as Amount
from Room, Animal
where Room.id =Animal.room_id
group by Room.name, Room.max
having Count(Animal.room_id) > Room.max

select Room.name, Room.max, Count(Animal.room_id)as Amount,(Room.max - Count(Animal.room_id)) as MoreAnimals
from Room, Animal
where Room.id =Animal.room_id
group by Room.name, Room.max
having Count(Animal.room_id) < Room.max

create proc proc_FoodAnimal
	@animalId int
as
begin
	select Animal.name,FoodType.name, FoodType.price, FoodType.amount
	from Animal, FoodType, FoodAnimal
	where FoodAnimal.food_id=FoodType.id and FoodAnimal.animal_id=Animal.id and Animal.id = @animalId 
end

exec proc_FoodAnimal 7

create trigger trigger_insteadof_foodtype on FoodType
instead of delete
as 
begin
	delete from FoodAnimal where food_id in (select id from deleted)
	delete from FoodType where id in (select id from deleted)
end 

2021-03-09 08:54:12

#2209.sql

create database QuanLySoThu
use QuanLySoThu

create table Room (
	id int not null,
	name nvarchar(20) not null,
	max int not null
)

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

create table FoodType (
	id int not null,
	price float not null,
	amount float not null
)

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

alter table Room
add constraint Room_PK primary key (id)

alter table Animal
add constraint Animal_PK primary key (id)

alter table FoodAnimal
add constraint FoodAnimal_PK primary key (food_id)

alter table FoodType
add constraint FoodType_PK primary key (id)

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

alter table FoodAnimal
add constraint FoodAnimal_Animal_FK foreign key (food_id) references Animal (id)

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

insert into Room(id, name, max)
values
(1, 'Chuong1', 17),
(2, 'Chuong2', 10),
(3, 'Chuong3', 21),
(4, 'Chuong4', 20),
(5, 'Chuong5', 15)

insert into Animal(id, name, age, buy_at, room_id)
values
(1, 'Voi', 5, '2020-12-15', 2),
(2, 'Khi', 4, '2021-02-25', 2),
(3, 'Cao', 9, '2015-02-15', 5),
(4, 'Rua', 10, '2014-01-15', 3),
(5, 'Chuot', 6, '2021-12-05', 1),
(6, 'Vuon', 1, '2020-02-25', 2),
(7, 'Than Lan', 14, '2011-02-25', 2),
(8, 'Cho', 4, '2016-05-25', 2),
(9, 'Soi', 6, '2021-05-25', 2),
(10, 'Huou', 3, '2021-05-26', 2),
(11, 'Nai', 5, '2021-06-25', 2),
(12, 'Meo', 4, '2021-07-25', 2),
(13, 'MeoRung', 1, '2021-07-25', 2),
(14, 'Su Tu', 15, '2010-12-15', 2)

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

insert into FoodType(id, price, amount)
values
(1, 12.450, 10),
(2, 17.050, 11),
(3, 52.000, 20),
(4, 22.000, 10),
(5, 19.000, 13)

select Room.name, Animal.name, Animal.age, Animal.buy_at
	from Room, Animal
	where Room.id = Animal.room_id
	order by Room.name

select Room.name, Room.max, count(Animal.room_id) as TongSo
	from Room, Animal
	where Room.id = Animal.room_id
	group by Room.name, Room.max
	having count(Animal.room_id) > Room.max

select Room.name, Room.max, count(Animal.room_id) as TongSo
	from Room, Animal
	where Room.id = Animal.room_id
	group by Room.name, Room.max
	having count(Animal.room_id) < Room.max

create proc proc_ThongTinThucAn
	@animalid int
as
begin
	select FoodAnimal.animal_id, FoodType.id, FoodType.price, FoodType.amount
		from FoodAnimal, FoodType
		where FoodAnimal.food_id = FoodType.id and FoodAnimal.animal_id = @animalid
end

create trigger FoodtypeDel on FoodType
instead of delete
as
begin
	delete FoodType where id in (select id from deleted)
end

delete FoodType where id = 1
exec proc_ThongTinThucAn 3
select * from Room
select * from Animal
select * from FoodAnimal
select * from FoodType