2021-04-07 03:16:09

create database qlibaidoxe
use qlibaidoxe

create table baidoxe(
	tenbai nvarchar(50),
	id_baidoxe int,
	diachi nvarchar (50)

)

create table thongtinguii(
	tenxe nvarchar (50),
	biensoxe int,
	id_baidoxe int,
	ngayguixe datetime,
	ngaylayxe datetime,
	chiphi money,
	id_chusohuu int
)

create table chusohuu(
	id_chusohuu int,
	ten nvarchar(50),
	socmnd int,
	diachi nvarchar (50)
)



insert into baidoxe(tenbai,id_baidoxe,diachi)
values 
('A',001,'Mydinh'),
('B',002,'Giapbat'),
('C',003,'Gialam')


insert into thongtinguii(tenxe,biensoxe,id_baidoxe,ngayguixe,ngaylayxe,chiphi,id_chusohuu)
values
('Mercesdes',1234,001,'10-05-2020','11-11-2020',2000000000,1),
('Maybach',3456,002,'05-05-2020','06-06-2020',25000000000,2),
('Toyota',5678,003,'10-05-2020','11-11-2020',14000000000,3)


insert into chusohuu(id_chusohuu,ten,socmnd,diachi)
values
(1,'Huyen',001302015197,'Vancanh'),
(2,'Bong',00130208902,'Ditrach'),
(3,'Thuy',00130203859,'Kimhoang')



select * from baidoxe
select * from thongtinguii
select * from chusohuu

select chusohuu.socmnd,chusohuu.ten,baidoxe.tenbai,thongtinguii.biensoxe
	from chusohuu,baidoxe,thongtinguii
		where baidoxe.id_baidoxe=thongtinguii.id_baidoxe
		and thongtinguii.id_chusohuu=chusohuu.id_chusohuu
		

2021-04-07 03:14:08

create database KhuGuixe
use KhuGuixe

create table BaiDoXe(
	TenBai nvarchar(50),
	MaBaiDoXe nvarchar(50) primary key,
	DiaChi nvarchar(50)
)
create table ThongTinGui(
	TenXe nvarchar(50),
	BienSoXe nvarchar(50),
	MaBaiDoXe nvarchar(50) primary key,
	idChuXe nvarchar(50)
)

create table BangChuSoHuu(
	idChuXe nvarchar(50) primary key,
	TenChuXe nvarchar(50),
	CMTNN bigint,
	DiaChiChuXe nvarchar(50),
)
insert into BaiDoXe(TenBai, MaBaiDoXe, DiaChi)
values
('a', 'a1', 'ha noi'),
('b', 'a2', 'ha noi'),
('c', 'a3', 'ha noi'),
('d', 'a4', 'ha noi'),
('e', 'a5', 'ha noi')

insert into ThongTinGui(TenXe, BienSoXe, MaBaiDoXe, idChuXe)
values
('honda', '18-G1 45678', 'a1', '123456789'),
('honda', '1r9-G1 45678', 'a2', '1234567891'),
('yamaha', '10-G1 45678', 'a3', '1234567892'),
('kawazaki', '34-G1 45678', 'a4', '1234567893'),
('ducati', '35-G1 45678', 'a5', '1234567894')

insert into BangChuSoHuu(idChuXe, TenChuXe, CMTNN, DiaChiChuXe)
values
('123456789', 'A', '3247239872', 'ha noi'),
('1234567891', 'B', '0980978458201', 'ha noi'),
('1234567892', 'C', '0980978458202', 'ha noi'),
('1234567893', 'D', '0980978458203', 'ha noi'),
('1234567894', 'E', '0980978458204', 'ha noi')

alter table BaiDoXe
add constraint fk_MaBaiDoXe foreign key (MaBaiDoXe) references ThongTinGui (MaBaiDoXe)

alter table ThongTinGui
add constraint fk_idChuXe foreign key (idChuXe) references BangChuSoHuu (idChuXe)

select BangChuSoHuu.CMTNN, BangChuSoHuu.TenChuXe, BaiDoXe.TenBai, ThongTinGui.BienSoXe
	from BangChuSoHuu, ThongTinGui, BaiDoXe
		where BaiDoXe.MaBaiDoXe = ThongTinGui.MaBaiDoXe
			and ThongTinGui.idChuXe = BangChuSoHuu.idChuXe

2021-03-04 09:11:17

create database QuanLyBaiDoXe

use QuanLyBaiDoXe

create table BaiDoXe(
	TenBai nvarchar(50),
	MaBai int primary key,
	DiaChi nvarchar(100)
)

create table ThongTinGui(
	TenXe nvarchar(50),
	BienSoXe nvarchar(50) primary key,
	MaBai int,
	NgayGui datetime,
	NgayLay datetime,
	ChiPhi money,
	IdChuSoHuu int
)

create table ChuSoHuu(
	IdChuSoHuu int primary key,
	Ten nvarchar(100),
	SoCmtnd nvarchar(20) unique,
	DiaChi nvarchar(100)
)

alter table ThongTinGui 
add foreign key (MaBai) references BaiDoXe(MaBai)

alter table ThongTinGui 
add foreign key (IdChuSoHuu) references ChuSoHuu(IdChuSoHuu)

insert into BaiDoXe(MaBai,TenBai,DiaChi)
values 
(1, 'Vin1','Thanh Xuan'),
(2, 'Vin2','Bach Mai'),
(3, 'Vin3','Giai Phong')

insert into ChuSoHuu(IdChuSoHuu, Ten, SoCmtnd, DiaChi)
values
(1, 'Nam', '202631526', 'Thanh Xuan'),
(2, 'Long', '212631524', 'Giai Phong'),
(3, 'Nhan', '222631525', 'Bach Mai')

insert into ThongTinGui(TenXe, BienSoXe, MaBai, NgayGui, NgayLay, ChiPhi, IdChuSoHuu)
values
('Vinfast', 'AB1-123-868', 3, '2021-03-03', '2021-03-10', 70000, 2),
('Toyota', 'AB1-123-989', 1, '2021-03-06', '2021-03-10', 40000, 1),
('Toyota', 'AB1-123-999', 1, '2021-03-05', '2021-03-10', 50000, 1),
('Vinfast', 'AB1-123-888', 3, '2021-03-06', '2021-03-10', 40000, 2),
('Toyota', 'AB1-123-777', 2, '2021-03-01', '2021-03-10', 90000, 3)

select ChuSoHuu.SoCmtnd, ChuSoHuu.Ten, BaiDoXe.TenBai, ThongTinGui.BienSoXe
from ChuSoHuu, BaiDoXe, ThongTinGui
where ThongTinGui.IdChuSoHuu=ChuSoHuu.IdChuSoHuu and ThongTinGui.MaBai=BaiDoXe.MaBai

create proc proc_DemSoLuotGui
		@IDchusohuu int
as
begin 
	select ChuSoHuu.IdChuSoHuu, ChuSoHuu.Ten, Count(ThongTinGui.IdChuSoHuu)as SoLanGui
	from ChuSoHuu, ThongTinGui
	where ChuSoHuu.IdChuSoHuu=ThongTinGui.IdChuSoHuu and ChuSoHuu.IdChuSoHuu=@IDchusohuu
	Group by ChuSoHuu.IdChuSoHuu, ChuSoHuu.Ten
end

exec proc_DemSoLuotGui 1

create proc proc_ChiPhi
	@IDchusohuu int
as
begin
	select ChuSoHuu.IdChuSoHuu, ChuSoHuu.Ten, Count(ThongTinGui.IdChuSoHuu)as SoLanGui, Sum(ThongTinGui.ChiPhi) as TongChiPhi
	from ChuSoHuu, ThongTinGui
	where ChuSoHuu.IdChuSoHuu=ThongTinGui.IdChuSoHuu and  ChuSoHuu.IdChuSoHuu=@IDchusohuu
	Group by ChuSoHuu.IdChuSoHuu, ChuSoHuu.Ten
end

exec proc_ChiPhi 1

create proc proc_check
	@IDchusohuu int
as
begin
	select ChuSoHuu.SoCmtnd, ChuSoHuu.Ten, BaiDoXe.TenBai, ThongTinGui.BienSoXe
	from ChuSoHuu, BaiDoXe, ThongTinGui
	where ThongTinGui.IdChuSoHuu=ChuSoHuu.IdChuSoHuu and ThongTinGui.MaBai=BaiDoXe.MaBai and GETDATE() between ThongTinGui.NgayGui and ThongTinGui.NgayLay and ChuSoHuu.IdChuSoHuu=@IDchusohuu
end

exec proc_check 2


create proc proc_History
	@IDchusohuu int
as
begin
	select ChuSoHuu.Ten, ChuSoHuu.IdChuSoHuu, BaiDoXe.TenBai, ThongTinGui.BienSoXe
	from ChuSoHuu, BaiDoXe, ThongTinGui
	where ThongTinGui.IdChuSoHuu=ChuSoHuu.IdChuSoHuu and ThongTinGui.MaBai=BaiDoXe.MaBai and ChuSoHuu.IdChuSoHuu=@IDchusohuu
end

exec proc_History 2

2021-03-04 08:41:35

#1825.sql

create database ParkingManager
use ParkingManager

create table BaiDoXe (
	TenBai nvarchar(20) not null,
	idBai int not null,
	DiaChi nvarchar(200) not null
)

create table ThongTinGui (
	TenXe nvarchar(50) not null,
	BSX nvarchar(20) not null,
	idBai int not null,
	NgayGui date not null,
	NgayLay date not null,
	Phi money not null,
	idChuXe int not null
)

create table ChuXe (
	idChuXe int not null,
	TenChuXe nvarchar(50) not null,
	CMND nvarchar(20) not null,
	DiaChiChuXe nvarchar(200) not null
)

alter table BaiDoXe
add constraint BaiDoXe_PK primary key (idBai)

alter table ThongTinGui
add constraint ThongTinGui_PK primary key (TenXe)

alter table ChuXe
add constraint ChuXe_PK primary key (idChuXe)

alter table ThongTinGui
add constraint ThongTinGui_ChuXe_FK foreign key (idChuXe) references ChuXe(idChuXe)

alter table ThongTinGui
add constraint ThongTinGui_BaiDoXe_FK foreign key (idBai) references BaiDoXe(idBai)

insert into BaiDoXe(TenBai, idBai, DiaChi)
values
('B1', 1, 'HN'),
('B2', 2, 'HCM'),
('B3', 3, 'DN')

insert into ChuXe(idChuXe, TenChuXe, CMND, DiaChiChuXe)
values
(1, 'Tran Van A', '2345636', 'DN'),
(2, 'Tran Van B', '3456377', 'HN'),
(3, 'Tran Van C', '4747747', 'HCM')

insert into ThongTinGui(TenXe, BSX, idBai, NgayGui, NgayLay, Phi, idChuXe)
values
('BMW', '74657474', 1, '2020/02/01', '2020/02/03', '2000050', 3),
('Audi', '34636346', 2, '2021/02/01', '2021/02/06', '2005000', 1),
('Audi', '34636346', 1, '2021/05/01', '2021/07/03', '20000340', 1),
('Toyota', '58588786', 1, '2020/12/01', '2021/2/03', '200000', 2),
('BMW', '45646456', 3, '2021/02/11', '2020/02/13', '2000006', 3),
('Mercedes', '90789696', 1, '2020/02/21', '2020/02/25', '2050000', 3),
('BMW', '74657474', 3, '2020/01/31', '2020/02/03', '200000', 3),
('Toyota', '58588786', 2, '2021/05/01', '2020/06/03', '2000200', 3),
('Mazda', '74657474', 2, '2020/02/01', '2020/02/13', '2000070', 2),
('Vinfast', '34636333', 1, '2019/02/01', '2020/02/03', '2070000', 3)

create proc proc_SoLanGuiXe
	@idChuXe int
as
begin
	select ThongTinGui.idChuXe, ChuXe.TenChuXe, count(ThongTinGui.idBai) 'SoLanGui'
	from ThongTinGui, ChuXe
	where ThongTinGui.idChuXe = ChuXe.idChuXe and ThongTinGui.idChuXe = @idChuXe
	group by ThongTinGui.idChuXe, ChuXe.TenChuXe
end

create proc proc_TongPhi
	@idChuXe int
as
begin
	select ThongTinGui.idChuXe, ChuXe.TenChuXe, sum(ThongTinGui.Phi) 'TongPhi'
	from ThongTinGui, ChuXe
	where ThongTinGui.idChuXe = ChuXe.idChuXe and ThongTinGui.idChuXe = @idChuXe
	group by ThongTinGui.idChuXe, ChuXe.TenChuXe
end

create proc proc_Check
	@idChuXe int
as
begin
	select ChuXe.CMND, ChuXe.TenChuXe, BaiDoXe.TenBai, ThongTinGui.BSX
	from ChuXe, BaiDoXe, ThongTinGui
	where ChuXe.idChuXe = ThongTinGui.idChuXe and ThongTinGui.idBai = BaiDoXe.idBai and ThongTinGui.idChuXe = @idChuXe
end

create proc proc_ThongTinXeDaGui
	@idChuXe int
as
begin
	select ThongTinGui.idChuXe, BaiDoXe.TenBai, ThongTinGui.BSX
	from BaiDoXe, ThongTinGui
	where BaiDoXe.idBai = ThongTinGui.idBai and ThongTinGui.idChuXe = @idChuXe
end

exec proc_SoLanGuiXe 3
exec proc_TongPhi 3
exec proc_Check 3
exec proc_ThongTinXeDaGui 3
select * from BaiDoXe
select * from ThongTinGui
select * from ChuXe

2020-12-15 14:52:15

create database baidoxe1825
use baidoxe1825
create table baidoxe(
	id int primary key identity(1,1),
	name nvarchar(100),
	address nvarchar(200)
)
create table custumer(
	id int primary key identity(1,1),
	name nvarchar(50),
	cmtnd nvarchar(25),
	address nvarchar(100)
)

create table info(
	id_baidoxe int references baidoxe(id),
	id_custumer int references custumer(id),
	name_car nvarchar(50),
	number_car nvarchar(25),
	date_in date,
	date_out date,
	chiphi money,
)

insert into baidoxe(name, address)
values
('Chuong Duong', 'Ha Noi'),
('Thanh Xuan', 'Ha Noi'),
('Cau Giay', 'Ha Noi'),
('Tu Liem', 'Ha Noi'),
('Hoan Kiem', 'Ha Noi')

insert into custumer(name, cmtnd, address)
values
('Nguyen Van A','00232323','Ha Noi'),
('Nguyen Van B','0343423','Hung Yen'),
('Nguyen Van C','023233','Ha Nam'),
('Nguyen Van D','0324243','Ha Tinh'),
('Nguyen Van E','03424243','Ha Tay'),
('Nguyen Van F','0434343','Ha Giang')

insert into info(id_baidoxe, id_custumer, name_car, number_car, date_in, date_out, chiphi)
values
(1,2,'Vision','29X3-22622','2020-09-20','2020-09-22', 10000),
(2,3,'Vision2','29X3-22622','2020-09-20','2020-09-22', 10000),
(3,3,'Vision3','29X3-22622','2020-09-20','2020-09-22', 10000),
(2,2,'Vision2','29X3-22622','2020-09-20','2020-09-22', 10000),
(3,2,'Vision1','29X3-22622','2020-09-20','2020-09-22', 10000)

select * from info

--Hien thi người gửi xe (số cmtnd, tên, tên bãi đỗ xe, biển số xe)
select custumer.cmtnd, custumer.name, baidoxe.name, info.number_car
from custumer, baidoxe, info
where custumer.id = info.id_custumer and baidoxe.id = info.id_baidoxe

---> Đếm số lượt gửi xe của 1 khách hàng theo id_chusohuu ==proc

create proc num_customer_id_customer
	@number_id int
as
begin
	select count(id_custumer) from info where info.id_custumer = @number_id
end

exec num_customer_id_customer 1

---> Tính tổng chi phí gửi xe của 1 khách hàng theo id chủ sở hữu

create proc chiphitheo_id_custumer2
	@id_custumer int
as
begin
	select sum(chiphi) 'Chi phi gui xe' from info where info.id_custumer = @id_custumer
end
exec chiphitheo_id_custumer2 3

---> Hiển thị tất cả các xe mà id chủ sở hữu đã từng gửi xe -> In Bãi đỗ xe, biển số xe.
select baidoxe.name, info.number_car, custumer.name
from baidoxe, info, custumer
where custumer.id = info.id_custumer and baidoxe.id = info.id_baidoxe
group by baidoxe.name, info.number_car, custumer.name

2020-12-08 20:39:07

create database baidoxe
use baidoxe

create table baidoxe(
	id int primary key identity(1,1),
	name nvarchar(100),
	address nvarchar(100)
)

insert into baidoxe(name, address)
values
('ABC', 'nguyen phan dong'),
('DEF', 'Lang Ha Long '),
('GHI', 'Hoang Dao Thuy hang')
select * from baidoxe

create table thongtingui(
	bienso nvarchar(20) primary key,
	car_name nvarchar(100),
	baidoxe_id int references baidoxe(id),
	ngaygui date,
	ngaylay date,
	price money,
	id_owner int references chusohuu(id)
)

insert into thongtingui
values
('30A12345', 'Toyota', 1, '2020-02-01', '2020-02-02', '900000', 2),
('30B23456', 'Hyundai', 3, '2020-11-01', '2020-12-01', '3000000', 3),
('30C34567', 'BMW', 2, '2020-11-01', '2020-11-05', '800000', 1)
select * from thongtingui

create table chusohuu(
	id int primary key identity(1,1),
	name nvarchar(100),
	cmnd nvarchar(20),
	address nvarchar(100)
)

insert into chusohuu(name, cmnd, address)
values
('Nguyen A', '001123456789', 'Thai Ha'),
('Nguyen B', '001987654321', 'Lieu Giai'),
('Nguyen C', '001456123789', 'Le Van Luong')
select * from chusohuu

create view info_nguoigui
as 
select cmnd, chusohuu.name as 'Chu Xe', baidoxe.name as 'Bai Do Xe', thongtingui.bienso
from chusohuu, baidoxe, thongtingui
where chusohuu.id=thongtingui.id_owner and baidoxe.id=thongtingui.baidoxe_id

select * from info_nguoigui

create proc soluotguixe
	@owner_id int
as
begin
	select id_owner, count(price) as 'So luot gui' 
	from thongtingui  
	where id_owner = @owner_id
	group by id_owner
end

exec soluotguixe 1

create proc phiguixe
	@owner_id int
as
begin
	select id_owner, sum(price) as 'Tong chi phi gui xe' 
	from thongtingui  
	where id_owner = @owner_id
	group by id_owner
end

exec phiguixe 1

create proc kiemtra
	@owner_id int
as
begin
	select id_owner, chusohuu.name as 'owner name', baidoxe.name as 'ten bai do', thongtingui.bienso
	from thongtingui, chusohuu, baidoxe
	where chusohuu.id=thongtingui.id_owner and baidoxe.id=thongtingui.baidoxe_id and id_owner = @owner_id
	group by id_owner, chusohuu.name, baidoxe.name, thongtingui.bienso
end

exec kiemtra 1

create proc kiemtra_xe
	@owner_id int
as
begin
	select id_owner, baidoxe.name as 'ten bai do', thongtingui.bienso
	from thongtingui, baidoxe
	where baidoxe.id=thongtingui.baidoxe_id and id_owner = @owner_id
	group by id_owner,  baidoxe.name, thongtingui.bienso
end

exec kiemtra_xe 1

2020-12-08 07:33:27

create database baidoxe
use baidoxe

create table baidoxe(
	id int primary key identity(1,1),
	name nvarchar(100),
	address nvarchar(100)
)

insert into baidoxe(name, address)
values
('ABC', 'Nguyen Chi Thanh'),
('DEF', 'Lang Ha'),
('GHI', 'Hoang Dao Thuy')
select * from baidoxe

create table thongtingui(
	bienso nvarchar(20) primary key,
	car_name nvarchar(100),
	baidoxe_id int references baidoxe(id),
	ngaygui date,
	ngaylay date,
	price money,
	id_owner int references chusohuu(id)
)

insert into thongtingui
values
('30A12345', 'Toyota', 1, '2020-12-01', '2020-12-12', '600000', 2),
('30B23456', 'Hyundai', 3, '2020-11-01', '2020-12-01', '1500000', 3),
('30C34567', 'BMW', 2, '2020-12-01', '2020-12-05', '400000', 1)
select * from thongtingui

create table chusohuu(
	id int primary key identity(1,1),
	name nvarchar(100),
	cmnd nvarchar(20),
	address nvarchar(100)
)

insert into chusohuu(name, cmnd, address)
values
('Nguyen A', '001123456789', 'Thai Ha'),
('Nguyen B', '001987654321', 'Lieu Giai'),
('Nguyen C', '001456123789', 'Le Van Luong')
select * from chusohuu

create view info_nguoigui
as 
select cmnd, chusohuu.name as 'Chu Xe', baidoxe.name as 'Bai Do Xe', thongtingui.bienso
from chusohuu, baidoxe, thongtingui
where chusohuu.id=thongtingui.id_owner and baidoxe.id=thongtingui.baidoxe_id

select * from info_nguoigui

create proc soluotguixe
	@owner_id int
as
begin
	select id_owner, count(price) as 'So luot gui' 
	from thongtingui  
	where id_owner = @owner_id
	group by id_owner
end

exec soluotguixe 1

create proc phiguixe
	@owner_id int
as
begin
	select id_owner, sum(price) as 'Tong chi phi gui xe' 
	from thongtingui  
	where id_owner = @owner_id
	group by id_owner
end

exec phiguixe 1

create proc kiemtra
	@owner_id int
as
begin
	select id_owner, chusohuu.name as 'owner name', baidoxe.name as 'ten bai do', thongtingui.bienso
	from thongtingui, chusohuu, baidoxe
	where chusohuu.id=thongtingui.id_owner and baidoxe.id=thongtingui.baidoxe_id and id_owner = @owner_id
	group by id_owner, chusohuu.name, baidoxe.name, thongtingui.bienso
end

exec kiemtra 1

create proc kiemtra_xe
	@owner_id int
as
begin
	select id_owner, baidoxe.name as 'ten bai do', thongtingui.bienso
	from thongtingui, baidoxe
	where baidoxe.id=thongtingui.baidoxe_id and id_owner = @owner_id
	group by id_owner,  baidoxe.name, thongtingui.bienso
end

exec kiemtra_xe 1

2020-12-07 06:47:32

create DATABASE QUANLY_BAIDOXE
use QUANLY_BAIDOXE

create table BaiDoXe(
TenBaiDoXe nvarchar(100),
BaiDoXe_ID int identity(1,1),
DiachiBaiDoXe nvarchar(200),
constraint PK_BaiDoXe primary key (BaiDoXe_ID)
)

create table ChuSoHuu(
ChuSoHuu_ID int identity(1,1),
TenChuSoHuu nvarchar(100),
CMT_ChuSoHuu nvarchar(200),
DiachiChuSoHuu nvarchar(200),
constraint PK_ChuSoHuu primary key (ChuSoHuu_ID)
)

create table ThongTinGui(
TenXe nvarchar(50),
BienSoXe nvarchar(50),
BaiDoXe_ID int,
NgayGuiXe date,
NgayLayXe date,
ChiPhiGuiXe money,
ChuSoHuu_ID int,
constraint PK_ThongTinGui primary key (BienSoXe)
)

--ADD du lieu 
insert into BaiDoXe(TenBaiDoXe,DiachiBaiDoXe)
values
('Bai do so 1','So 1, duong Xuan Thuy'),
('Bai do so 2','So 3, duong Nguyen Khanh Toan'),
('Bai do so 3','So 5, duong Quan Hoa')


insert into ChuSoHuu(TenChuSoHuu,CMT_ChuSoHuu,DiachiChuSoHuu)
values
('Do Trung Duc','012793988', 'Dich Vong Hau, Cau Giay'),
('Tran Van Diep','033451388', 'QuanHoa, Cau Giay'),
('Nguyen Tuan Anh','0125963543', 'NghiaDo, Cau Giay')

insert into ThongTinGui(TenXe,BienSoXe,BaiDoXe_ID,NgayGuiXe,NgayLayXe,ChiPhiGuiXe,ChuSoHuu_ID)
values
('Fortuner', '30K36523', 1 , '2020-12-01', '2020-12-05', 40, 2), 
('Mazda6', '30K35534', 2 , '2020-11-01', '2020-12-05', 300, 3),
('BWVX5', '30K12339', 3 , '2020-11-15', '2020-12-05', 180, 1)

select * from ChuSoHuu
select * from BaiDoXe
select * from ThongTinGui

--Hien thong tin nguoi gui xe
select ChuSoHuu.TenChuSoHuu, ChuSoHuu.CMT_ChuSoHuu, ThongTinGui.TenXe, ThongTinGui.BienSoXe
FROM ChuSoHuu inner join ThongTinGui on ChuSoHuu.ChuSoHuu_ID = ThongTinGui.ChuSoHuu_ID

--Tao Store
----Đếm số lượt gửi xe của 1 khách hàng theo id_chusohuu
create PROC demsoluotguixe_theoChuSoHuuID 
	@ChuSoHuuID int
AS
BEGIN
	select  count(ChiphiGuiXe) from ThongTinGui where ChuSoHuu_ID = @ChuSoHuuID
END

alter PROC demsoluotguixe_theoChuSoHuuID 
	@ChuSoHuuID int
AS
BEGIN
	select ChuSoHuu_ID, count(ChiphiGuiXe) as 'TongSoLuotGui' from ThongTinGui  where ChuSoHuu_ID = @ChuSoHuuID
	group by  ChuSoHuu_ID
END

EXEC demsoluotguixe_theoChuSoHuuID '1'

----Tinh tong chi phi gui xe của 1 khách hàng theo id_chusohuu

create PROC tinhtongchiphi
	@ChuSoHuuID int
AS
BEGIN
	select ChuSoHuu_ID, sum(ChiphiGuiXe) as 'Tongchiphi' from ThongTinGui  where ChuSoHuu_ID = @ChuSoHuuID
	group by  ChuSoHuu_ID
END

EXEC tinhtongchiphi '1'

----Kiểm tra chủ sở hữu xe có đang gửi xe hay không -> In thông tin -> số CMTND, tên, tên bãi đỗ xe, biển số xe
select * from ChuSoHuu
select * from BaiDoXe
select * from ThongTinGui

create PROC kiemtrathongtin
	@ChuSoHuu_ID int
AS
BEGIN
	select ChuSoHuu.CMT_ChuSoHuu, ChuSoHuu.TenChuSoHuu, BaiDoXe.TenBaiDoXe, ThongTinGui.BienSoXe
	from ThongTinGui inner join BaiDoXe on ThongTinGui.BaiDoXe_ID = BaiDoXe.BaiDoXe_ID inner join ChuSoHuu on ThongTinGui.ChuSoHuu_ID = ChuSoHuu.ChuSoHuu_ID
	where ThongTinGui.ChuSoHuu_ID = @ChuSoHuu_ID
END

EXEc kiemtrathongtin 1
EXEc kiemtrathongtin 4

----Hiển thị tất cả các xe mà id chủ sở hữu đã từng gửi xe -> In Bãi đỗ xe, biển số xe.

create PROC Hienthitatcaxedagui 
	@ChuSoHuu_ID int
AS
BEGIN
	select ThongTinGui.BienSoXe, BaiDoXe.DiachiBaiDoXe 
	from ThongTinGui, BaiDoXe 
	where ThongTinGui.BaiDoXe_ID = BaiDoXe.BaiDoXe_ID and ThongTinGui.ChuSoHuu_ID =  @ChuSoHuu_ID
END

--Sua lai truong khoa chinh cua bang Thong tin gui 
alter table ThongTinGui
drop constraint PK_ThongTinGui

alter table ThongTinGui
add ThongTinGui_ID int identity (1,1)

alter table ThongTinGui
Add constraint PK_ThongTinGui  primary key (ThongTinGui_ID)


insert into ThongTinGui(TenXe,BienSoXe,BaiDoXe_ID,NgayGuiXe,NgayLayXe,ChiPhiGuiXe,ChuSoHuu_ID)
values
('Fortuner', '30K36523', 3 , '2020-12-07', '2020-12-10', 30, 2), 
('Mazda6', '30K35534', 1 , '2020-12-08', '2020-12-10', 20, 3),
('BWVX5', '30K12339', 1 , '2020-11-30', '2020-12-12', 120, 1)

EXEc Hienthitatcaxedagui 2

2020-12-04 10:05:42

create database Parking_management
use Parking_management

create table parking(
parking_id int identity (1,1) primary key,
parking_Name nvarchar(100),
parking_address nvarchar(200)
)

create table Information(
name_car nvarchar (100),
license_plates nvarchar (10),
parking_id  int ,
Parking_date date,
Pick_up_date date,
Price money,
owner_id int
)
alter table Information
add constraint fk_parking_id 
foreign key (parking_id) references parking(parking_id)

alter table Information
add constraint fk_owner_id 
foreign key (owner_id) references owner(owner_id)

create table owner (
owner_id int identity (1,1) primary key,
owner_name nvarchar (100),
cmtnd int,
address_of_owner nvarchar (200)
)

insert into  parking(parking_Name,parking_address)
values
('A','Ha Noi'),
('B','Hai Duong'),
('C','Hai Phong')

insert into Information(name_car,license_plates,parking_id,Parking_date,Pick_up_date,Price,owner_id)
values
('Car1','s11111','1','2020-11-04','2020-12-01','100','1'),
('Car2','s19991','2','2020-09-04','2020-09-09','100','2'),
('Car3','s22221','3','2020-03-04','2020-05-04','100','3')

insert into owner(owner_name,cmtnd,address_of_owner)
values
('aa','1121212','Ha Noi'),
('ss','2323232','Ha Noi'),
('dd','4343434','Hai Phong')



----Hiển thị thông tin người gửi xe (số cmtnd, tên, tên bãi đỗ xe, biển số xe
select owner.cmtnd,owner.owner_name,parking.parking_Name,Information.license_plates
from owner,parking,Information
where Information.owner_id = owner.owner_id and parking.parking_id = Information.parking_id

-----Đếm số lượt gửi xe của 1 khách hàng theo chu so huu
alter proc Dem_so_luot
@id_chusohuu int
as
begin
select owner.cmtnd,owner.owner_name,parking.parking_Name,Information.license_plates,count(owner.owner_id) as number_of_turns
from owner,parking,Information
where Information.owner_id = owner.owner_id and parking.parking_id = Information.parking_id and owner.owner_id = @id_chusohuu
group by owner.cmtnd,owner.owner_name,parking.parking_Name,Information.license_plates
end

exec Dem_so_luot 1

----Tính tổng chi phí gửi xe của 1 khách hàng theo id chủ sở hữu
create proc tong_chi_phi
@id_chusohuu int
as
begin
select owner.cmtnd,owner.owner_name,parking.parking_Name,Information.license_plates, sum(Information.Price)as tong_chi_phi
from owner,parking,Information
where Information.owner_id = owner.owner_id and parking.parking_id = Information.parking_id and owner.owner_id = @id_chusohuu
group by owner.cmtnd,owner.owner_name,parking.parking_Name,Information.license_plates
end

exec tong_chi_phi 1

create proc chu_so_huu
@ten nvarchar(100)
as
begin
select owner.cmtnd,owner.owner_name,parking.parking_Name,Information.license_plates
from owner,parking,Information
where Information.owner_id = owner.owner_id and parking.parking_id = Information.parking_id and owner.owner_name = @ten
group by owner.cmtnd,owner.owner_name,parking.parking_Name,Information.license_plates
end

exec chu_so_huu aa

create proc cac_xe_chu_so_huu_gui
@id int
as
begin
select parking.parking_Name,Information.license_plates
from owner,parking,Information
where Information.owner_id = owner.owner_id and parking.parking_id = Information.parking_id and owner.owner_id = @id 
end

exec cac_xe_chu_so_huu_gui 1

2020-08-25 12:53:35

lỗi hangout đây thầy