主题：SQL练习——旅游公司组团信息

下面是一家旅游公司的数据库，其中“operator”表为旅行业务员信息，他能够负责旅行线路的开发和组团，“cruise”表为旅行线路信息，每条旅行线路都由一名旅行业务员来负责，“cruise_book”表为每次实际安排的旅行线路具体预订情况，“customer”表为组团客户信息，具体说明了哪些客户预订了哪天的哪条旅行线路信息。

operator（业务员）表：

此主题相关图片如下：

cruise（旅游产品）表名：

此主题相关图片如下：

cruise_book（旅游预订）表名：

此主题相关图片如下：

customer（顾客）表名：

此主题相关图片如下：

表结构信息如下：

operator （oper_cd、 oper_nm、oper_add、oper_telno、oper_faxno、oper_email） 旅行业务员（旅行业务员代码、名字、 地址、 电话号码、 传真号码、 电子邮件地址）

cruise （cruise_cd、cruise_nm、oper_cd、 des_city、 country_nm、duration、 price、 airfare） 旅行线路（旅行代码、 旅行名称、 旅游业务员代码、目的地城市、国家名称、 旅行时间（按天计）、每位价格（按美元计）、飞机票价含在价格中（Y－是，N－否）

cruise_book （cruise_cd、start_dt、 tot_seats、seats_avail） 预订旅行线路（旅行代码、 行程开始日期、座位总数、 未预定座位数）

customer（cust_cd、 cust_nm、cust_add、tel_no、 e_mail、 cruise_cd、 start_dt、 no_of_per） 客户 （客户代码、姓名、 住址、 电话号码、电子邮件地址、所预定旅行代码、所预定旅行开始日期、预定人员数）

试进行以下查询：

1）查询旅行业务员Dream Tours有哪些旅游项目，各自的行程开始日期和座位总数 2）查询客户Ann Dull目前已经预订了哪些旅游项目及各自的未预定座位数 3）查询旅游项目中价格最高的负责人和预定客户姓名信息 4）查询目前预定数最少的旅游项目的空闲率（未预定座位数占总座位数的比率） 5）查询各旅游目的地各自的预定人数，并降序排序 6）查询目前哪些旅游项目没有人预定 7）查询旅行时间最短的项目的名称和时间

直接将文件解压，附加到sql server数据库中即可

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:

1) select oper_nm,cruise_nm,start_dt,tot_seats from OPERATOR inner join CRUISE on OPERATOR.oper_cd=CRUISE.oper_cd inner join cruise_book on CRUISE.cruise_cd=cruise_book.cruise_cd where OPERATOR.oper_nm='Dream Tours'

2) select customer.cust_nm,CRUISE.cruise_nm,cruise_book.seats_avail from customer inner join cruise_book on cruise_book.cruise_cd=customer.cruise_cd inner join CRUISE on CRUISE.cruise_cd=cruise_book.cruise_cd where customer.cust_nm='Ann Dull'

3) select oper_nm,cust_nm from customer inner join CRUISE on customer.cruise_cd=CRUISE.cruise_cd inner join OPERATOR on OPERATOR.oper_cd=CRUISE.oper_cd where CRUISE.price=(select max(price) from CRUISE)

4) select cruise_cd,convert(char(2),convert(float,seats_avail)/convert(float,tot_seats)*100)+'%' as '空闲率' from cruise_book where (tot_seats-seats_avail)=(select min(tot_seats-seats_avail) from cruise_book)

5) select des_city,sum(tot_seats-seats_avail) as sn from cruise_book inner join CRUISE on cruise_book.cruise_cd=CRUISE.cruise_cd group by CRUISE.des_city order by sn desc

6) select cruise_nm from CRUISE where cruise_cd not in (select cruise_cd from cruise_book)

7) select cruise_nm,duration from CRUISE where duration=(select min(duration) from CRUISE )

前面的3题都用到了3张表的连接，因为课堂上讲的都是2张表的连接，所以做的时候只考虑了子查询，但是第3题的子查询想不出来怎么做。

还有第4题没有考虑到convert(char(2),convert(float,seats_avail)/convert(float,tot_seats)*100)+'%'

我对第四个查询作一些修改：select cruise_nm,cruise.cruise_cd,convert(char(2),convert(float,seats_avail)/convert(float,tot_seats)*100)+''%'' as ''空闲率'' from cruise_book inner join cruise on cruise_book.cruise_cd=cruise.cruise_cd where (tot_seats-seats_avail)=(select min(tot_seats-seats_avail) from cruise_book)

因为题中提到旅游项目，我认为有必要查询旅游名称，需连接cruise表

我认为第六个查询是错误的，因为我认为两这的旅行代码肯定是相同的，假设把旅行预定路线中的一个未预定座位人数改成等于总座位人数，那么也就意味着该旅游项目无人预定，那么用以上查询是失败的，我认为还是连接两张表：select cruise_nm,cruise.cruise_cd from cruise inner join cruise_book on cruise.cruise_cd=cruise_book.cruise_cd where seats_avail=tot_seats

我想：第六个应该是正确的，因为表的名字是cruise_book (预订旅行线路)，即有预定的记录，如果未预定的座位=总座位就是没有预定，那这条记录就不会存在在这张表里了。

所以原查询还是可以执行的！

我认同这个~~