酒店管理系统数据库表和SQL

010.tydis
扩展：
物价赔偿表
采购表
员工表
数据库的表分别为：管理员信息表（operator），房间类型表（roomType）,
房间信息表（roomInfo）,客户类型表（customerType），客户信息表（customer），
入住信息表（liveIn），预订信息表（engage），结账表（account），
酒店简介表（companyIntroduce），酒店新闻表（news），天气信息表（temp），特惠信息表（tehui）。


create table roomType(
PK integer primary key,
roomTypeID char(1) check(roomTypeID in('A','B','C','D','E','F','G','H')) unique not null,
roomTypeName varchar2(15) not null,
roomPrice float not null,
roomBedNum int,
foregift float,
cl_room int,
cl_price float,
remark varchar2(40),
delmark int);
/

create table operator(
PK integer primary key,
userID varchar2(10) unique not null,
pwd varchar2(10) not null,
puis int,
in_time date,
login_num int
);
/

create table customer(
PK integer primary key,
c_ID varchar2(20) unique not null,
pwd varchar2(15) not null,
c_name varchar2(15) not null,
c_sex char(2) check(c_sex in('男','女')) not null,
zj_type varchar2(15) not null,
zj_no varchar2(20) not null,
c_addr varchar2(40),
c_tel varchar2(20) not null,
remark varchar2(40),
delmark int,
c_type_id char(1),
jifen int,
foreign key(c_type_id) references customerType(customerID)
);
/

create table customerType(
PK integer primary key,
customerID char(1) check(customerID in('A','B','C','D','E','F','G')) unique not null,
c_type_name varchar2(15) not null,
dis_attr varchar2(15),
discount float,
remark varchar2(40),
delmark int
);
/

create table roomInfo(
PK integer primary key,
roomID char(3) not null unique,
roomTypeID char(1) not null,
roomState char(1) check(RoomState in('0','1')) not null,
roomTel varchar2(10) not null,
roomLoc varchar2(10),
remark varchar2(40),
delmark int,
foreign key (roomTypeID) references roomType(roomTypeID)
);
/

create table liveIn(
PK integer primary key,
in_no varchar2(10) not null unique,
roomID char(3) not null,
c_ID varchar2(10),
c_name varchar2(15) not null,
c_sex char(2) check(c_sex in('男','女')) not null,
zj_type varchar2(15) not null,
zj_no varchar2(25) not null,
addr varchar2(50),
renshu int,
in_time date,
leave_time date,
delmark int,--客户离开
c_tel varchar2(20),
foreign key (roomID) references roomInfo(roomID)
);
/

create table engage(
PK integer primary key,
c_ID varchar2(10) not null,
roomID char(3) not null,
engage_time date,
in_time date,
leave_time date,
engage_mark int,
delmark int,
foreign key (roomID) references roomInfo(roomID),
foreign key (c_ID) references customer(c_ID)
);

/



create table account(

chk_ID varchar2(10) primary key,
in_no varchar2(10) not null,
chk_time date,
days int,
money float,
userID varchar2(15),
delmark int,
foreign key (in_no) references live

In(in_no),
foreign key(userID) references operator(userID)

);

/

create table record(
PK integer primary key,
userID varchar2(15) not null,
brief varchar2(20),
in_no varchar2(10) not null,
delmark int,
foreign key (userID) references operator(userID),
foreign key (in_no) references liveIn(in_no)
);
/



create table companyIntroduce(
PK integer primary key,
brief varchar2(2000)
);


create table news(
PK integer primary key,
type varchar2(40) not null,
title varchar2(40) not null,
in_time date,
brief varchar2(500)
);


create table temp(
PK integer primary key,
day date,
tempa int,
tianqi varchar2(30)
);



create table tehui(
PK integer primary key,
title varchar2(50) not null,
brif varchar2(500),
in_time date
);











--select deptno,max(sal) as max1,max(decode(t,2,sal)) as max2,min(sal) as max3 from
--(select empno,ename,sal,t,deptno from
--(select empno,ename,sal,row_number() over (partition by deptno order by sal desc) t,deptno
--from emp) e1
--where e1.t<=3)
--group by deptno

每个房间类型住的人次数 num_per_roomtype

select r.*,t.roomprice,t.roombednum from
(select t.roomTypeID,t.roomTypeName,count(*) as num
from roomType t,roomInfo r,liveIn i
where i.roomID=r.roomID and r.roomTypeID=t.roomTypeID and i.in_time>=to_date('2012-1-1','yyyy-mm-dd') and i.leave_time<=to_date('2012-11-1','yyyy-mm-dd')
group by t.roomTypeID,roomTypeName
order by roomTypeID,roomTypeName) r,roomType t
where r.roomTypeID=t.roomTypeID

select count(*) as num from livein i
where i.in_time>=to_date('2012-1-1','yyyy-mm-dd') and i.leave_time<=to_date('2012-6-1','yyyy-mm-dd')


--select count(a.O_LOG_ID) from por_log a where to_char(a.OPER_TIME,'yyyymm')>='201110'


一年的每个月收入总额 money_per_year
select sum(money) as moneytotle,to_char(chk_time,'yyyymm') month
from account
where to_char(chk_time,'yyyy')='2008'
group by to_char(chk_time,'yyyymm')
order by to_char(chk_time,'yyyymm')

那个个月的收入详细信息 money_per_month
select a.chk_ID,i.c_name,i.c_sex,i.c_tel,i.in_time,i.leave_time,a.days,a.money,r.roomID,t.roomTypeName
from account a,liveIn i,roomInfo r,roomType t
where to_char(a.chk_time,'yyyymm')=? and a.in_no=i.in_no
and i.roomID=r.roomID and r.roomTypeID=t.roomTypeID


某个时间段内消费前N高的客户 high_pay_customer


select distinct(l.c_sex),l.c_tel,l.zj_no,l.addr,e.*
from (select t.*
from (select i.c_name,sum(a.money) as xiaofei
from account a,liveIn i
where a.in_no=i.in_no and
to_char(a.chk_time,'yyyymm')>='201201' and to_char(a.chk_time,'yyyymm')<='201211'
group by i.c_name
order by sum(a.money) desc) t
where rownum<=4) e,livein l
where e.c_name=l.c_name
order by e.xiaofei desc


某个时间段内住宿次数前N高的客户
select distinct(l.c_sex),l.c_tel,l.zj_no,l.addr,e.*
from (select *
from(select count(*) as cishu,i.c_name
from account a,liveIn i
where a.in_no=i.i

n_no and
to_char(a.chk_time,'yyyymm')>='200801' and to_char(a.chk_time,'yyyymm')<='201211'
group by i.c_name
order by count(*) desc)
where rownum<=1) e,livein l
where e.c_name=l.c_name
order by cishu desc

日结账统计表
当日开房报表