查看: 50819|回复: 216

[Tips] ITPUB“盛拓传媒杯”SQL数据库编程大赛最后一题,答题结束,进入评审期!

 关闭 [复制链接]
招聘 : 数据库管理员
认证徽章
论坛徽章:
83
IT宝贝
日期:2013-11-15 18:40:242015年新春福章
日期:2015-03-06 11:57:31美羊羊
日期:2015-03-04 14:48:58马上加薪
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11
发表于 2011-3-25 17:07 | 显示全部楼层 |阅读模式
整40人参与了答题,答题参与人的名单罗列在本贴二楼,现在进入评审期,第三期的评审在清明假期后将会公布。第四期的评审预计于4月8日之前完成,完成后将尽快公布获奖人信息,好立即给大家派送礼品,以及发送架构师大会参会门票!


盛拓传媒是中国领先的互联网传媒集团,以传媒和互联网的力量,帮助消费者提高生活品质和工作效率是其使命。盛拓传媒略投资者是澳大利亚电信集团,旗下拥有四家享有盛誉的专业垂直网站,涉及IT和汽车两大消费领域(IT168泡泡网车168汽车之家)。集团目前有逾千名员工,在全国各个主要城市均设有分支机构,在IT和汽车领域广泛、精准地覆盖所有目标群体,有效用户数量最多、用户人群层次最丰富,拥有无可比拟的传播力和影响力,是中国最具价值的互联网专业传媒航母。

第一届“盛拓传媒杯”SQL数据库编程大赛宗旨并非考核脑筋急转弯或高深的算法考核各位puber的算法能力,而且主要以PL/SQL的技巧和技能,以及对SQL新功能的掌握能力,性能优化能力,并附带考核对数据库体系结构的综合理解。题目均以oracle及MS SQL Server数据库为平台。

本次大赛将分四期进行每轮为答题期为1周,对每期竞赛的参与者,评委将会公正的给予打分,累积四期大赛的得分最高的TOP10将赠予礼品一份、数据库技术大会参会门票一张及授予“ITPUB SQL数据库编程大师”的称号。具体奖项和礼品说明如下:
TOP1-3:赠予IPOD touch 8G一个、2011数据库技术大会B票一张、并授予“ITPUB SQL数据库编程大师”的称号,奖励专属徽章以标识身份;
TOP4-10:赠予东芝 500G移动硬盘一个,2011数据库技术大会B票一张、并授予“ITPUB SQL数据库编程大师”的称号,奖励专属徽章以标识身份;
参与奖:
每位参与的会员均可获赠有盛拓传媒的纪念品一份,所有参与的会员均可得到一枚参与徽章。
特别奖:此奖将从每期参与的puber中根据答题情况评选出2-3名不等的特别奖(例如:特别勤奋奖,特别创意奖等等,根据当期情况不同,奖项不同),赠予itpub技术丛书一册/itpub9周年保温杯一个(任选其一)。此奖项也将和以上奖项同时公布。

现在开始“盛拓传媒杯”SQL大赛最后一期 第四期答题,答题时间为:2011年3月25日-4月1日24:00结束,请大家在此时间内答题。

本期题目:
某项房产资源服务按天数、人数收费。收费标准与那一天的用户数有关,对于来自同一个房产经纪公司的用户,某天的总用户数越多则折扣越多。
  1. CREATE TABLE services ( -------- 各项服务
  2.        service_id    NUMBER PRIMARY KEY
  3.       ,service_name  VARCHAR2(20)
  4.       );
  5. CREATE TABLE companies ( -------- 使用服务的公司
  6.        company_id     NUMBER PRIMARY KEY
  7.       ,company_name   VARCHAR2(40)
  8.       );
  9.      
  10. CREATE TABLE service_users ( -------公司下的用户(房产经纪)
  11.        user_id     NUMBER PRIMARY KEY
  12.       ,company_id  NUMBER NOT NULL REFERENCES companies(company_id)
  13.       ,user_name   VARCHAR2(20)
  14.       );
  15. CREATE TABLE service_usage (
  16. ------- 使用情况,起止日为闭合区间(首尾包含),如果只用一天,则start_date=end_date
  17. ------  该表为大表,数据较多
  18.        usage_id    NUMBER PRIMARY KEY
  19.       ,user_id     NUMBER NOT NULL REFERENCES service_users(user_id)
  20.       ,service_id  NUMBER NOT NULL REFERENCES services(service_id)
  21.       ,start_date  DATE   NOT NULL -------- 数据只含日期,不含时间
  22.       ,end_date    DATE   NOT NULL -------- 数据只含日期,不含时间
  23.       ,CONSTRAINT check_dates CHECK (end_date>=start_date)
  24.       );
  25. CREATE TABLE service_rates ( -------收费标准,每个公司各不相同, 按人数段计费
  26.        service_id     NUMBER NOT NULL REFERENCES services(service_id)
  27.       ,company_id     NUMBER NOT NULL REFERENCES companies(company_id)
  28.       ,category_id    NUMBER NOT NULL ------ 1,2,3,4表示1档,2档,3档,4档
  29.       ,user_count_min NUMBER NOT NULL ------ 该档人数下限,1档从1开始
  30.       ,user_count_max NUMBER NOT NULL ------ 该档人数上限,同样是闭合区间。最后一档不妨设为99999999
  31.       ,rate           NUMBER NOT NULL ------ 该档的费率,乘以天数再乘以人数就是总费用
  32.       ,PRIMARY KEY (company_id,service_id,category_id)
  33.       );
  34. 输入:
  35. 起止日期(yyyymmdd字符串), 闭合区间(首尾包含), 如果只计算一天,则p_start_date=p_end_date
  36. 绑定变量:
  37. VAR p_start_date VARCHAR2(8);
  38. VAR p_end_date VARCHAR2(8);
  39. 输出:费用报表
  40. company_id,company_name,service_id,service_name,fee1,fee2,fee3,fee4,total_fee
  41. 其中:fee1,fee2,fee3,fee4分别表示1档,2档,3档,4档的费用,total_fee是上述4项的总和。若存在某档无数据,则总和为NULL。
  42. 按照company_id,service_id排序(升序)。
复制代码
测试数据:
  1. INSERT INTO services VALUES(1,'Service A');
  2. INSERT INTO services VALUES(2,'Service B');
  3. INSERT INTO services VALUES(3,'Service C');
  4. INSERT INTO companies VALUES(1,'company A');
  5. INSERT INTO companies VALUES(2,'company B');
  6. INSERT INTO companies VALUES(3,'company C');
  7. INSERT INTO service_users VALUES(101,1,'User 1 Comp A');
  8. INSERT INTO service_users VALUES(102,1,'User 2 Comp A');
  9. INSERT INTO service_users VALUES(103,1,'User 3 Comp A');
  10. INSERT INTO service_users VALUES(104,1,'User 4 Comp A');
  11. INSERT INTO service_users VALUES(201,2,'User 1 Comp B');
  12. INSERT INTO service_users VALUES(202,2,'User 2 Comp B');
  13. INSERT INTO service_users VALUES(203,2,'User 3 Comp B');
  14. INSERT INTO service_users VALUES(204,2,'User 4 Comp B');
  15. INSERT INTO service_users VALUES(301,3,'User 1 Comp C');
  16. INSERT INTO service_users VALUES(302,3,'User 2 Comp C');
  17. INSERT INTO service_users VALUES(303,3,'User 3 Comp C');
  18. insert into service_rates values (1,1,1,1,1,20);
  19. insert into service_rates values (1,1,2,2,2,15);
  20. insert into service_rates values (1,1,3,3,3,10);
  21. insert into service_rates values (1,1,4,4,99999999,5);
  22. insert into service_rates values (2,1,1,1,1,30);
  23. insert into service_rates values (2,1,2,2,2,25);
  24. insert into service_rates values (2,1,3,3,3,20);
  25. insert into service_rates values (2,1,4,4,99999999,15);
  26. insert into service_rates values (1,2,1,1,1,22);
  27. insert into service_rates values (1,2,2,2,2,17);
  28. insert into service_rates values (1,2,3,3,3,12);
  29. insert into service_rates values (1,2,4,4,99999999,8);
  30. insert into service_rates values (2,2,1,1,1,28);
  31. insert into service_rates values (2,2,2,2,2,21);
  32. insert into service_rates values (2,2,3,3,3,17);
  33. insert into service_rates values (2,2,4,4,99999999,12);
  34. INSERT INTO service_usage VALUES(1,101,1,DATE '2010-1-5' ,DATE '2010-1-10');
  35. INSERT INTO service_usage VALUES(2,102,1,DATE '2010-1-3' ,DATE '2010-1-11');
  36. INSERT INTO service_usage VALUES(3,103,1,DATE '2010-1-7' ,DATE '2010-1-10');
  37. INSERT INTO service_usage VALUES(4,104,1,DATE '2010-1-10',DATE '2010-1-26');
  38. INSERT INTO service_usage VALUES(11,101,2,DATE '2010-1-1' ,DATE '2010-1-10');
  39. INSERT INTO service_usage VALUES(12,102,2,DATE '2010-1-2' ,DATE '2010-1-8' );
  40. INSERT INTO service_usage VALUES(13,103,2,DATE '2010-1-7' ,DATE '2010-1-10');
  41. INSERT INTO service_usage VALUES(14,104,2,DATE '2010-1-5' ,DATE '2010-1-13');
  42. INSERT INTO service_usage VALUES(21,201,1,DATE '2010-1-2' ,DATE '2010-1-21');
  43. INSERT INTO service_usage VALUES(22,202,1,DATE '2010-1-1' ,DATE '2010-1-17');
  44. INSERT INTO service_usage VALUES(23,203,1,DATE '2010-1-7' ,DATE '2010-1-13');
  45. INSERT INTO service_usage VALUES(24,204,1,DATE '2010-1-3' ,DATE '2010-1-30');
  46. INSERT INTO service_usage VALUES(31,201,2,DATE '2010-1-1' ,DATE '2010-1-2');
  47. INSERT INTO service_usage VALUES(32,202,2,DATE '2010-1-2' ,DATE '2010-1-3');
  48. INSERT INTO service_usage VALUES(33,203,2,DATE '2010-1-4' ,DATE '2010-1-10');
  49. INSERT INTO service_usage VALUES(34,204,2,DATE '2010-1-11',DATE '2010-1-13');
  50. commit;
复制代码
答题注意:
1.选手必须按照题目给定的表结构答题,否则不得分
2.应提供总体思路和关键步骤的汉字注释
3.并提供样例数据对如下输入变量的输出结果,以便验证
  1. VAR p_start_date VARCHAR2(8);
  2. VAR p_end_date VARCHAR2(8);
  3. EXEC :p_start_date := '20100104';
  4. EXEC :p_end_date := '20100130';
复制代码
4.要求用一个SQL查询语句完成(可以包括子查询,但不能包括ddl和dml语句)

第四期活动评委:〇〇, newkid, dingjun123, bq_wang、lastwinner

参与规则:
每个参赛者以附件方式贴出自己的源代码,并将阅读权限设为11~100之间的数值,答案贴出后不允许编辑帖子。编辑过的帖子做无效处理。需要修改答案请发表新的回复,并以同样的方式上传新的答案。

附件名称以SQL大赛第N期(ID).txt来命名,通过rar压缩加密,推荐采取select sys_guid() from dual的方式产生密码,答题者需要在截止时间前将密码以论坛PM给论坛助理~贝贝~
pm链接:
http://www.itpub.net/pm.php?action=send&uid=93405

此次活动也非常欢迎新注册会员参与,但因新注册会员三天内均在临时用户组,故不能上传附件,所以请参与此次活动的新会员,把答题压缩包和解压密码直接发邮件给我即可:wangbei@it168.com,我会在收到后第一时间给予您回复的。

特别申明:在答题期间不准在论坛公开讨论与此题有关的解题思路!

活动流程:
1、发布本期活动题目及答题时间范围,大家可以在此期间根据以上参与规则上传自己的参赛程序文档。
2、结束答题,进入评审阶段,评审将在一周内对所有参赛程序进行评审,评审结束后将公布评审及作品点评。


评审规则:
1、由ITPUB论坛助理下载参赛者附件并解压后删除参赛者ID,将各参赛文档编入代号,并发给当期评委。
2、评委抽签获得序号并评审相应序号的答案。运行程序,将运行出正确答案的参赛文档选出。
3、所有评委独立对运行出正确答案的程序打分,把相应分数告知论坛助理。论坛助理取平均数为该答案最后得分。
注:出题者负责向评委讲解思路,并配合评委书写点评。

第三期的答题结果,预计于4月6日公布,评委们在此期间会抓紧时间对大家的答题进行评审和点评给予建议!现在请大家继续支持第四期的活动,参与答题吧,无论语句写的好坏,参与其中必然得到最多收获,期待更多的puber参与到活动中!
招聘 : 数据库管理员
认证徽章
论坛徽章:
83
IT宝贝
日期:2013-11-15 18:40:242015年新春福章
日期:2015-03-06 11:57:31美羊羊
日期:2015-03-04 14:48:58马上加薪
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11
发表于 2011-3-25 17:10 | 显示全部楼层
活动最新进展:
第三期活动评审:正在进行中,预计于4月6日公布评审结果

SQL大赛活动相关链接,欢迎大家查看并了解此次活动:
第三期SQL大赛活动链接:http://www.itpub.net/thread-1408182-1-1.html
第二期SQL大赛活动链接:http://www.itpub.net/thread-1403356-1-1.html
第一期SQL大赛活动链接:http://www.itpub.net/thread-1400067-1-1.html

SQL数据库编程大赛第二期评分及所有答题公布链接:http://www.itpub.net/thread-1411980-1-1.html
SQL数据库编程大赛第一期评分及所有答题公布链接:http://www.itpub.net/thread-1407072-1-1.html
从SQL比赛第一期解答中引出的问题:http://www.itpub.net/thread-1407195-1-2.html
TKV5-ITPUB SQL大赛第一期赏析:http://www.itpub.net/thread-1407361-1-2.html
继续挑战: 被〇〇毙掉的题目!:http://www.itpub.net/thread-1407879-1-1.html
[随便谈谈]ITPUB第一届SQL大赛第一期的共性问题:http://www.itpub.net/thread-1406578-1-1.html

第四期参与者名单,不定时更新如下,目前已有41位puber提交了答题:
qqjue
jvkojvko
myfriend2010
renxiao2003
ai2ming
Arsenal-8
xgghxkhuang
yzsind
old_voldo
lk-2010
wenhuiqiao
junsansi
6666444
szusunny
oracle_chang
wangkxxe
jiqing1004
fnghost
cui6548
solomon_007
tang870
accelerator
mychary
lxddxl
AC米兰
yu_dongyang
luoyoumou
regonly1
victor666666
todayz
jxmarvel
yaots
hongyehongye1
rollingpig
northmoon
3833020
ruideliang
Nalternative
yangtingkun
my_legend
bavol2010

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有房
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31
发表于 2011-3-25 17:52 | 显示全部楼层
嗯,答疑楼

使用道具 举报

回复
论坛徽章:
1
SQL大赛参与纪念
日期:2011-04-13 12:08:17
发表于 2011-3-25 17:52 | 显示全部楼层
看看!

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:31:22SQL大赛参与纪念
日期:2011-04-13 12:08:17ITPUB十周年纪念徽章
日期:2011-11-01 16:25:222013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09秀才
日期:2016-03-24 09:10:24秀才
日期:2016-12-05 11:16:06
发表于 2011-3-25 18:44 | 显示全部楼层
费率是按公司,服务决定,某一天的费率是按这天这个公司的总人数算,还是这天这个公司这项服务的总人数算的.
有的人一天有两项服务,是不是这个人要算两项服务的钱?

[ 本帖最后由 qqjue 于 2011-3-25 19:02 编辑 ]

使用道具 举报

回复
论坛徽章:
8
玉兔
日期:2015-11-16 10:18:00铁扇公主
日期:2015-10-27 21:47:42九尾狐狸
日期:2015-12-11 22:31:15
发表于 2011-3-25 19:02 | 显示全部楼层
fee1,fee2,fee3,fee4分别表示1档,2档,3档,4档的费用,total_fee是上述4项的总和。若存在某档无数据,则总和为NULL。

“则总和为NULL” 这个是说 total_fee 是 null还是 fee1-fee4是null?

使用道具 举报

回复
论坛徽章:
8
玉兔
日期:2015-11-16 10:18:00铁扇公主
日期:2015-10-27 21:47:42九尾狐狸
日期:2015-12-11 22:31:15
发表于 2011-3-25 19:04 | 显示全部楼层
给个T-SQL的建表脚本和测试数据吧,这题目好像没什么好发挥的了...

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2011-3-25 19:08 | 显示全部楼层
mssql把number修改为bigint

使用道具 举报

回复
论坛徽章:
10000
地主之星
日期:2015-07-20 17:15:36地主之星
日期:2015-09-01 14:14:25地主之星
日期:2015-09-01 17:59:09地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58
发表于 2011-3-25 19:37 | 显示全部楼层
顶!观望!

使用道具 举报

回复
论坛徽章:
531
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2011-3-25 19:47 | 显示全部楼层
费率是按公司,服务决定,某一天的费率是按这天这个公司的总人数算,还是这天这个公司这项服务的总人数算的.


按使用该项服务的人数算。在这一天没有使用该服务的其他人,不予收费当然也不能参与计算折扣。

有的人一天有两项服务,是不是这个人要算两项服务的钱?


是的,每项服务独立收费。

fee1,fee2,fee3,fee4分别表示1档,2档,3档,4档的费用,total_fee是上述4项的总和。若存在某档无数据,则总和为NULL。

“则总和为NULL” 这个是说 total_fee 是 null还是 fee1-fee4是null?


仅仅在该档次为NULL; 如果fee1-fee4全部为NULL则TOTAL_FEE为零。(纠正:如果四项服务全未使用过则这一行不需要输出,而不是TOTAL_FEE=0)

[ 本帖最后由 newkid 于 2011-3-25 21:46 编辑 ]

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表