其它的规则都是一样的, 3、查询课程名以

作者: 前端  发布:2019-09-08

H5游戏开荒:消灭星星

2018/01/25 · HTML5 · 游戏

原作出处: 坑坑洼洼实验室   

「消灭星星」是一款很雅观的「化解类游戏」,它的游戏的方法相当粗略:解决相连通的同色砖块。

图片 1

H5游戏开荒:一笔画

2017/11/07 · HTML5 · 游戏

初稿出处: 坑坑洼洼实验室   

图片 2

SQL 演练题答案

Last updated December 5, 2012.

1. 游戏法则

「消灭星星」存在五个版本,然则它们的准则除了「关卡分值」某个出入外,另外的平整都以大同小异的。作者介绍的版本的游戏准绳整理如下:

1. 色砖分布

  • 10 x 10 的表格
  • 5种颜色 —— 红、绿、蓝,黄,紫
  • 每类色砖个数在钦定区间内放肆
  • 5类色砖在 10 x 10 表格中私下布满

2. 解除准则

八个或多少个以上同色砖块相连通便是可被免去的砖块。

3. 分值法则

  • 扫除总分值 = n * n * 5
  • 奖赏总分值 = 3000 – n * n * 20

「n」表示砖块数量。上面是「总」分值的准则,还大概有「单」个砖块的分值法则:

  • 撤销砖块得分值 = 10 * i 5
  • 剩余砖块扣分值 = 40 * i 20

「i」表示砖块的索引值(从 0 最初)。轻易地说,单个砖块「得分值」和「扣分值」是贰个等差数列。

4. 关卡分值

关卡分值 = 一千 (level – 1) * 三千;「level」即当前关卡数。

5. 及格条件

  • 可祛除色块不设有
  • 一齐分值 >= 当前关卡分值

地点五个规范还要创建游戏才具够过得去。

H5游戏开采:一笔画

by leeenx on 2017-11-02

一笔画是图论[科普](https://zh.wikipedia.org/wiki/图论)中多少个著名的难题,它源点于柯基加利堡七桥难点[科普](https://zh.wikipedia.org/wiki/柯尼斯堡七桥问题)。物艺术学家欧拉在他1736年登出的舆论《柯多哥洛美堡的七桥》中不仅减轻了七桥主题材料,也建议了一笔画定理,顺带消除了一笔画难点。用图论的术语来讲,对于二个加以的连通图[科普](https://zh.wikipedia.org/wiki/连通图)存在一条恰好含有所无线段而且未有重新的不二秘籍,这条渠道正是「一笔画」。

招来连通图那条门路的历程正是「一笔画」的游艺进程,如下:

图片 3

 

Try searching this page for keywords like 'segmentation' or 'PLY'.

2. MVC 设计形式

小编这一次又是行使了 MVC 方式来写「消灭星星」。星星「砖块」的数据结构与各类气象由 Model 完结,游戏的主干在 Model 中达成;View 映射 Model 的变化并做出相应的作为,它的天职至关心珍贵若是显得动画;顾客与娱乐的互相由 Control 完结。

从逻辑规划上看,Model 相当的重而View 与 Control 相当的轻,然而,从代码量上看,View 十分重而 Model 与 Control 相对十分轻。

游戏的贯彻

「一笔画」的兑现不复杂,笔者把贯彻进程分成两步:

  1. 底图绘制
  2. 相互绘制

「底图绘制」把连通图以「点线」的花样显得在画布上,是娱乐最轻松达成的一些;「交互绘制」是顾客绘制解题路线的长河,这几个历程会重假若管理点与点动态成线的逻辑。

一、补充作业一、

 

设有三个关系:

               S(SNO, SNAME, AGE, SEX,Sdept)

               SC(SNO, CNO, GRADE)

               C(CNO, CNAME, TEACHER)

试用关系代数表达式表示下列查询:

 

1、查询学号为S3学生所学课程的课程名与任课教师名。

  

2、查询至少选修LIU老师所教课程中一门课的女生姓名。

3、查询WANG同学不学的课程的课程号。

4、查询至少选修两门课程的学生学号。

5、查询选修课程中包含LIU老师所教全部课程的学生学号。

补充作业二、

 

三个关系同上,试用SQL语言表示下列查询:

 

1、  查询门门课程都及格的学生的学号

方法1:

提示:根据学号分组,就得到每个学生所有的课程成绩,在某个学生这一组成绩里,如果他所有的课程成绩都大于60分则输出该组学生的学号

Select sno frome sc group by sno having(min(grade)>=60)

 

2、查询既有课程大于90分又有课程不及格的学生的学号

自身连接:

Select sno from sc where grade >90 and sno in (select sno from sc where grade<60)

 

3、查询平均分不及格的课程号和平均成绩

Select cno , avg(GRADE) from sc group by cno having avg(grade)<60

查询平均分及格的课程号和课程名

Select C.cno , Cname from SC,C where C.cno=SC.cno group by C.cno having avg(grade)>=60

 

4、找出至少选修了2号学生选修过的全部课程的学生号

提示:不存在这样的课程y,学生2选修了y,而学生x没有选。

SELECT DISTINCT Sno

   FROM SC as SCX

   WHERE NOT EXISTS

      (SELECT *

       FROM SC as SCY

       WHERE SCY.Sno =‘2’AND NOT EXISTS

                               (SELECT *

                                  FROM SC SCZ

                          WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno))



5、求各门课程去掉一个最高分和最低分后的平均分

第一步,求所有成绩的平均分(去掉一个最高分和最低分)

select   avg(GRADE)   from   SC       where   GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE)     and     GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE   desc)  

第二步,将所有成绩按各门课程的课程号CNO分组

SELECT CNO avg(GRADE)   from   SC       where   GRADE   not   in (select   top  1  GRADE   from   SC order   by   GRADE)     and     GRADE   not   in (select   top  1  GRADE   from   SC order   by   GRADE   desc) group by CNO

If you would like to contribute links, please e-mail them torms@dgp.toronto.edu.

3. Model

10 x 10 的报表用长度为 100 的数组可全面映射游戏的少数「砖块」。

[ R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P ]

1
2
3
4
5
6
7
8
9
10
11
12
[
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P
]

凯雷德 – 米白,G – 玉石白,B – 浅橙,Y – 紫水晶色,P – 青黑。Model 的大旨职务是以下多个:

  • 变迁砖墙
  • 排除砖块 (生成砖块分值)
  • 狠抓砖墙
  • 破除残砖 (生成奖赏分值)

底图绘制

「一笔画」是多关卡的16日游方式,笔者决定把关卡(连通图)的定制以贰个配置接口的样式对外揭发。对外暴露关卡接口须要有一套描述连通图形状的正规,而在笔者眼下有八个挑选:

  • 点记法
  • 线记法

举个连通图 —— 五角星为例来讲一下那三个选取。

图片 4

点记法如下:

JavaScript

levels: [ // 当前关卡 { name: "五角星", coords: [ {x: Ax, y: Ay}, {x: Bx, y: By}, {x: Cx, y: Cy}, {x: Dx, y: Dy}, {x: Ex, y: Ey}, {x: Ax, y: Ay} ] } ... ]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
levels: [
// 当前关卡
{
name: "五角星",
coords: [
{x: Ax, y: Ay},
{x: Bx, y: By},
{x: Cx, y: Cy},
{x: Dx, y: Dy},
{x: Ex, y: Ey},
{x: Ax, y: Ay}
]
}
...
]

线记法如下:

JavaScript

levels: [ // 当前关卡 { name: "五角星", lines: [ {x1: Ax, y1: Ay, x2: Bx, y2: By}, {x1: Bx, y1: By, x2: Cx, y2: Cy}, {x1: Cx, y1: Cy, x2: Dx, y2: Dy}, {x1: Dx, y1: Dy, x2: Ex, y2: Ey}, {x1: Ex, y1: Ey, x2: Ax, y2: Ay} ] } ]

1
2
3
4
5
6
7
8
9
10
11
12
13
levels: [
// 当前关卡
{
name: "五角星",
lines: [
{x1: Ax, y1: Ay, x2: Bx, y2: By},
{x1: Bx, y1: By, x2: Cx, y2: Cy},
{x1: Cx, y1: Cy, x2: Dx, y2: Dy},
{x1: Dx, y1: Dy, x2: Ex, y2: Ey},
{x1: Ex, y1: Ey, x2: Ax, y2: Ay}
]
}
]

「点记法」记录关卡通过海关的贰个答案,即端点要按自然的一一寄放到数组 coords中,它是有序性的记录。「线记法」通过两点描述连通图的线条,它是冬季的笔录。「点记法」最大的优势是显示更简洁,但它必需记录二个及格答案,小编只是关卡的搬运工不是关卡创制者,所以作者最后选项了「线记法」。:)

 

Papers & Archives

3.1 生成砖墙

砖墙分两步生成:

  • 色砖数量分配
  • 打散色砖

辩白上,能够将 100 个格子能够均分到 5 类颜色,可是小编玩过的「消灭星星」都不行使均分政策。通过剖析五款「消灭星星」,其实可以开掘贰个规律 —— 「色砖之间的数码差在叁个稳固的间距内」。

设若把守旧意义上的均分称作「完全均分」,那么「消灭星星」的分配是一种在均分线上下波动的「不完全均分」。

图片 5

我把地方的「不完全均分」称作「波动均分」,算法的有血有肉落到实处能够参见「内忧外患均分算法」。

「征服色砖」其实就是将数组乱序的长河,笔者推荐使用「 费雪耶兹乱序算法」。

以下是伪代码的贯彻:

JavaScript

// 波动均分色砖 waveaverage(5, 4, 4).forEach( // tiles 即色墙数组 (count, clr) => tiles.concat(generateTiles(count, clr)); ); // 打垮色砖 shuffle(tiles);

1
2
3
4
5
6
7
// 波动均分色砖
waveaverage(5, 4, 4).forEach(
// tiles 即色墙数组
(count, clr) => tiles.concat(generateTiles(count, clr));
);
// 打散色砖
shuffle(tiles);

相互绘制

在画布上制图路线,从视觉上实属「采纳或一而再连通图端点」的长河,那些进度须要消除2个难题:

  • 手指下是不是有端点
  • 入选点到待选中式茶食之间是或不是成线

募集连通图端点的坐标,再监听手指滑过的坐标可以知道「手指下是或不是有一些」。以下伪代码是收罗端点坐标:

JavaScript

// 端点坐标新闻 let coords = []; lines.forEach(({x1, y1, x2, y2}) => { // (x1, y1) 在 coords 数组不设有 if(!isExist(x1, y1)) coords.push([x1, y1]); // (x2, y2) 在 coords 数组空中楼阁if(!isExist(x2, y2)) coords.push([x2, y2]); });

1
2
3
4
5
6
7
8
// 端点坐标信息
let coords = [];
lines.forEach(({x1, y1, x2, y2}) => {
// (x1, y1) 在 coords 数组不存在
if(!isExist(x1, y1)) coords.push([x1, y1]);
// (x2, y2) 在 coords 数组不存在
if(!isExist(x2, y2)) coords.push([x2, y2]);
});

以下伪代码是监听手指滑动:

JavaScript

easel.addEventListener("touchmove", e => { let x0 = e.targetTouches[0].pageX, y0 = e.targetTouches[0].pageY; // 端点半径 ------ 取连通图端点半径的2倍,进步活动端体验 let r = radius * 2; for(let [x, y] of coords){ if(Math.sqrt(Math.pow(x - x0, 2) Math.pow(y - y0), 2) <= r){ // 手指下有端点,判别是或不是连线 if(canConnect(x, y)) { // todo } break; } } })

1
2
3
4
5
6
7
8
9
10
11
12
13
14
easel.addEventListener("touchmove", e => {
let x0 = e.targetTouches[0].pageX, y0 = e.targetTouches[0].pageY;
// 端点半径 ------ 取连通图端点半径的2倍,提升移动端体验
let r = radius * 2;
for(let [x, y] of coords){
if(Math.sqrt(Math.pow(x - x0, 2) Math.pow(y - y0), 2) <= r){
// 手指下有端点,判断能否连线
if(canConnect(x, y)) {
// todo
}
break;
}
}
})

在未绘制任何线段或端点以前,手指滑过的放肆端点都会被当做「一笔画」的开首点;在绘制了线段(或有选中式茶食)后,手指滑过的端点能或不能够与选中式点心串连成线段须要基于现有条件进行判别。

图片 6

上图,点A与点B可连日来成线段,而点A与点C无法一而再。小编把「能够与内定端点连接成线段的端点称作得力连接点」。连通图端点的卓有成效连接点从连通图的线条中提取:

JavaScript

coords.forEach(coord => { // 有效连接点(坐标)挂载在端点坐标下 coord.validCoords = []; lines.forEach(({x1, y1, x2, y2}) => { // 坐标是时下线段的起源 if(coord.x === x1 && coord.y === y1) { coord.validCoords.push([x2, y2]); } // 坐标是当前线段的终极 else if(coord.x === x2 && coord.y === y2) { coord.validCoords.push([x1, y1]); } }) })

1
2
3
4
5
6
7
8
9
10
11
12
13
14
coords.forEach(coord => {
// 有效连接点(坐标)挂载在端点坐标下
coord.validCoords = [];
lines.forEach(({x1, y1, x2, y2}) => {
// 坐标是当前线段的起点
if(coord.x === x1 && coord.y === y1) {
coord.validCoords.push([x2, y2]);
}
// 坐标是当前线段的终点
else if(coord.x === x2 && coord.y === y2) {
coord.validCoords.push([x1, y1]);
}
})
})

But…有效连接点只可以决断四个点是不是为底图的线条,这只是三个静态的参照,在实际上的「交互绘制」中,会超过以下情状:

图片 7
如上海教室,AB已串连成线段,当前选中式茶食B的得力连接点是 A 与 C。AB 已经一连成线,假设 BA 也串连成线段,那么线段就再一次了,所以那时候 BA 不能够成线,独有 AC 技艺成线。

对选中式茶食来说,它的立竿见影连接点有三种:

  • 与选中式茶食「成线的有效性连接点」
  • 与选中式茶食「未成线的管用连接点」

其间「未成线的实用连接点」才具参加「交互绘制」,并且它是动态的。

图片 8

回头本节内容开首提的多少个难题「手指下是还是不是有端点」 与 「选中式茶食到待选中点时期是还是不是成线」,其实可统一为二个难题:手指下是不是存在「未成线的得力连接点」。只须把监听手指滑动遍历的数组由连通图全体的端点坐标 coords 替换为近些日子选中式点心的「未成线的一蹴而就连接点」就可以。

迄今「一笔画」的注重成效已经达成。能够超越体验一下:

图片 9

 1、查询7号课程未有考试战绩的学习者学号。

Graphics Conference Paper Link Archive(Ke-Sen Huang)

3.2 化解砖块

「化解砖块」的条条框框相当粗略 —— 相邻相连通同样色就可以避防去

图片 10
前七个结合符合「相邻相连通同样色即能够清除」,所以它们得以被消除;第多少个结合即便「相邻一样色」但是不「相衔接」所以它不能够被破除。

「化解砖块」的同临时候有贰个珍视的义务:生成砖块对应的分值。在「游戏准绳」中,作者曾经提供了相应的数学公式:「解决砖块得分值 = 10 * i 5」。

「解决砖块」算法完毕如下:

JavaScript

function clean(tile) { let count = 1; let sameTiles = searchSameTiles(tile); if(sameTiles.length > 0) { deleteTile(tile); while(true) { let nextSameTiles = []; sameTiles.forEach(tile => { nextSameTiles.push(...searchSameTiles(tile)); makeScore( count * 10 5); // 标志当前分值 deleteTile(tile); // 删除砖块 }); // 清除完毕,跳出循环 if(next萨姆eTiles.length === 0) break; else { sameTiles = next萨姆eTiles; } } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
function clean(tile) {
let count = 1;
let sameTiles = searchSameTiles(tile);
if(sameTiles.length > 0) {
deleteTile(tile);
while(true) {
let nextSameTiles = [];
sameTiles.forEach(tile => {
nextSameTiles.push(...searchSameTiles(tile));
makeScore( count * 10 5); // 标记当前分值
deleteTile(tile); // 删除砖块
});
// 清除完成,跳出循环
if(nextSameTiles.length === 0) break;
else {
sameTiles = nextSameTiles;
}
}
}
}

破除的算法使用「递归」逻辑上会清晰一些,然而「递归」在浏览器上轻易「栈溢出」,所以小编未有行使「递归」达成。

机动识图

小编在录加入关贸总协定组织卡配置时,开掘一个7条边以上的衔接图很轻便录错或录重线段。小编在观念是或不是开辟一个自动识别图形的插件,毕竟「一笔画」的图片是有平整的几何图形。

图片 11

上边的卡子「底图」,一眼就足以识出几个颜色:

  • 白底
  • 端点颜色
  • 线条颜色

同临时间那三种颜色在「底图」的面积大小顺序是:白底 > 线段颜色 > 端点颜色。底图的「收罗色值表算法」很简短,如下伪代码:

JavaScript

let imageData = ctx.getImageData(); let data = imageData.data; // 色值表 let clrs = new Map(); for(let i = 0, len = data.length; i < len; i = 4) { let [r, g, b, a] = [data[i], data[i 1], data[i 2], data[i 3]]; let key = `rgba(${r}, ${g}, ${b}, ${a})`; let value = clrs.get(key) || {r, g, b, a, count: 0}; clrs.has(key) ? value.count : clrs.set(rgba, {r, g, b, a, count}); }

1
2
3
4
5
6
7
8
9
10
let imageData = ctx.getImageData();
let data = imageData.data;
// 色值表
let clrs = new Map();
for(let i = 0, len = data.length; i < len; i = 4) {
let [r, g, b, a] = [data[i], data[i 1], data[i 2], data[i 3]];
let key = `rgba(${r}, ${g}, ${b}, ${a})`;
let value = clrs.get(key) || {r, g, b, a, count: 0};
clrs.has(key) ? value.count : clrs.set(rgba, {r, g, b, a, count});
}

对于连通图来讲,只要把端点识别出来,连通图的概貌也就出去了。

    Select sno fromsc where cno='7' and grade is null

Reproducible Researcharchive (image processing, vision, machine learning) (Xin Li)

3.3 狠抓砖墙

砖墙在排除了一部分砖石后,会油但是生空洞,此时亟待对墙体实行抓好:

向下夯实 向左夯实 向左下夯实(先下后左)

一种高效的贯彻方案是,每一回「化解砖块」后一向遍历砖墙数组(10×10数组)再把空洞坚实,伪代码表示如下:

JavaScript

for(let row = 0; row < 10; row) { for(let col = 0; col < 10; col) { if(isEmpty(row, col)) { // 水平方向(向左)加强if(isEmptyCol(col)) { tampRow(col); } // 垂直方向(向下)抓好 else { tampCol(col); } break; } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
for(let row = 0; row < 10; row) {
for(let col = 0; col < 10; col) {
if(isEmpty(row, col)) {
// 水平方向(向左)夯实
if(isEmptyCol(col)) {
tampRow(col);
}
// 垂直方向(向下)夯实
else {
tampCol(col);
}
break;
}
}
}

But… 为了做实贰个华而不实对一张大数组实行全量遍历而不是一种高效的算法。在我看来影响「墙体做实」功效的要素有:

  1. 牢固空洞
  2. 砖块移动(抓牢)

扫描墙体数组的最首要目标是「定位空洞」,不过否不扫描墙体数组间接「定位空洞」?

墙体的「空洞」是出于「消除砖块」产生的,换种说法 —— 被解除的砖头留下来的坑位正是墙体的肤浅。在「消除砖块」的同不常候标志空洞的职位,那样就无须全量扫描墙体数组,伪代码如下:

JavaScript

function deleteTile(tile) { // 标志空洞 markHollow(tile.index); // 删除砖块逻辑 ... }

1
2
3
4
5
6
function deleteTile(tile) {
// 标记空洞
markHollow(tile.index);
// 删除砖块逻辑
...
}

在下面的坚实动图,其实能够观察它的做实进程如下:

  1. 空洞上方的砖块向下活动
  2. 空驶列车左侧的砖块向左移动

墙体在「加强」进程中,它的分界是实时在转变,假如「加强」不按实际边界举行围观,会生出多余的空域扫描:

图片 12

何以记录墙体的边际?
把墙体拆分成一个个独自的列,那么列最最上端的空白格片段就是墙体的「空白」,而别的非顶上部分的空白格片段即墙体的「空洞」。

图片 13

作者使用一组「列集结」来呈报墙体的界限并记下墙体的肤浅,它的模型如下:

JavaScript

/* @ count - 列砖块数 @ start - 最上端行索引 @ end - 尾部行索引 @ pitCount - 坑数 @ topPit - 最最上端的坑 @ bottomPit - 最尾部的坑 */ let wall = [ {count, start, end, pitCount, topPit, bottomPit}, {count, start, end, pitCount, topPit, bottomPit}, ... ];

1
2
3
4
5
6
7
8
9
10
11
12
13
/*
@ count - 列砖块数
@ start - 顶部行索引
@ end - 底部行索引
@ pitCount - 坑数
@ topPit - 最顶部的坑
@ bottomPit - 最底部的坑
*/
let wall = [
{count, start, end, pitCount, topPit, bottomPit},
{count, start, end, pitCount, topPit, bottomPit},
...
];

本条模型能够描述墙体的四个细节:

  • 空列
  • 列的总是空洞
  • 列的非接二连三空洞
JavaScript

// 空列 if(count === 0) { ... } // 连续空洞 else if(bottomPit -
topPit   1 === pitCount) { ... } // 非连续空洞 else { ... }

<table>
<colgroup>
<col style="width: 50%" />
<col style="width: 50%" />
</colgroup>
<tbody>
<tr class="odd">
<td><div class="crayon-nums-content" style="font-size: 13px !important; line-height: 15px !important;">
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-1">
1
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-2">
2
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-3">
3
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-4">
4
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-5">
5
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-6">
6
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-7">
7
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-8">
8
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-9">
9
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-10">
10
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-11">
11
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-12">
12
</div>
</div></td>
<td><div class="crayon-pre" style="font-size: 13px !important; line-height: 15px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;">
<div id="crayon-5b8f3d2c2df29914802382-1" class="crayon-line">
// 空列
</div>
<div id="crayon-5b8f3d2c2df29914802382-2" class="crayon-line crayon-striped-line">
if(count === 0) { 
</div>
<div id="crayon-5b8f3d2c2df29914802382-3" class="crayon-line">
 ...
</div>
<div id="crayon-5b8f3d2c2df29914802382-4" class="crayon-line crayon-striped-line">
}
</div>
<div id="crayon-5b8f3d2c2df29914802382-5" class="crayon-line">
// 连续空洞
</div>
<div id="crayon-5b8f3d2c2df29914802382-6" class="crayon-line crayon-striped-line">
else if(bottomPit - topPit   1 === pitCount) { 
</div>
<div id="crayon-5b8f3d2c2df29914802382-7" class="crayon-line">
 ...
</div>
<div id="crayon-5b8f3d2c2df29914802382-8" class="crayon-line crayon-striped-line">
}
</div>
<div id="crayon-5b8f3d2c2df29914802382-9" class="crayon-line">
// 非连续空洞
</div>
<div id="crayon-5b8f3d2c2df29914802382-10" class="crayon-line crayon-striped-line">
else {
</div>
<div id="crayon-5b8f3d2c2df29914802382-11" class="crayon-line">
 ...
</div>
<div id="crayon-5b8f3d2c2df29914802382-12" class="crayon-line crayon-striped-line">
}
</div>
</div></td>
</tr>
</tbody>
</table>

砖块在排除后,映射到单个列上的空洞会有三种布满形态 —— 接二连三与非一连。

图片 14

「接二连三空洞」与「非三番五次空洞」的坚实进程如下:

图片 15

骨子里「空驶列车」放大于墙体上,也可以有「空洞」类似的布满形态 —— 一而再与非三回九转。
图片 16

它的压实进程与指雁为羹类似,这里就不赘述了。

端点识别

商量上,通过收罗的「色值表」能够一贯把端点的坐标记别出来。小编设计的「端点识别算法」分以下2步:

  1. 按像素扫描底图直到遇到「端点颜色」的像素,步向第二步
  2. 从底图上清除端点并记录它的坐标,再次来到继续第一步

伪代码如下:

JavaScript

for(let i = 0, len = data.length; i < len; i = 4) { let [r, g, b, a] = [data[i], data[i 1], data[i 2], data[i 3]]; // 当前像素颜色属于端点 if(isBelongVertex(r, g, b, a)) { // 在 data 中清空端点 vertex = clearVertex(i); // 记录端点信息vertexes.push(vertext); } }

1
2
3
4
5
6
7
8
9
10
for(let i = 0, len = data.length; i < len; i = 4) {
let [r, g, b, a] = [data[i], data[i 1], data[i 2], data[i 3]];
// 当前像素颜色属于端点
if(isBelongVertex(r, g, b, a)) {
// 在 data 中清空端点
vertex = clearVertex(i);
// 记录端点信息
vertexes.push(vertext);
}
}

But… 上边的算法只好跑无损图。小编在采用了一张手提式有线电话机截屏做测量检验的时候发掘,采摘到的「色值表」长度为 五千 !这一向变成端点和线条的色值不可能间接获取。

因此深入分析,能够发掘「色值表」里好多色值都以相仿的,也正是在原本的「搜罗色值表算法」的底蕴上增添三个像样颜色过滤即能够寻觅端点和线条的主色。伪代码完毕如下:

JavaScript

let lineColor = vertexColor = {count: 0}; for(let clr of clrs) { // 与底色左近,跳过 if(isBelongBackground(clr)) continue; // 线段是数码第二多的颜色,端点是第三多的颜色 if(clr.count > lineColor.count) { [vertexColor, lineColor] = [lineColor, clr] } }

1
2
3
4
5
6
7
8
9
let lineColor = vertexColor = {count: 0};
for(let clr of clrs) {
// 与底色相近,跳过
if(isBelongBackground(clr)) continue;
// 线段是数量第二多的颜色,端点是第三多的颜色
if(clr.count > lineColor.count) {
[vertexColor, lineColor] = [lineColor, clr]
}
}

取到端点的主色后,再跑壹次「端点识别算法」后居识别出 203 个端点!那是为什么吧?

图片 17

上图是扩充5倍后的底图局地,浅墨绛红端点的方圆和在那之中充斥着多量噪点(杂色块)。事实上在「端点识别」进程中,由于噪点的存在,把原来的端点被分解成二十个或数13个小端点了,以下是跑过「端点识别算法」后的底图:

图片 18

经过上海图书馆,能够直观地搜查缴获一个结论:识别出来的小端点只在对象(大)端点上集聚分布,况且大端点范围内的小端点叠合交错。

假定把叠合交错的小端点归并成四个多边点,那么这些大端点将万分类似目的端点。小端点的集结伪代码如下:

JavaScript

for(let i = 0, len = vertexes.length; i < len - 1; i) { let vertexA = vertexes[i]; if(vertextA === undefined) continue; // 注意这里 j = 0 并非 j = i 1 for(let j = 0; j < len; j) { let vertexB = vertexes[j]; if(vertextB === undefined) continue; // 点A与点B有增大,点B合併到点A并剔除点B if(is克罗斯(vertexA, vertexB)) { vertexA = merge(vertexA, vertexB); delete vertexA; } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
for(let i = 0, len = vertexes.length; i < len - 1; i) {
let vertexA = vertexes[i];
if(vertextA === undefined) continue;
// 注意这里 j = 0 而不是 j = i 1
for(let j = 0; j < len; j) {
let vertexB = vertexes[j];
if(vertextB === undefined) continue;
// 点A与点B有叠加,点B合并到点A并删除点B
if(isCross(vertexA, vertexB)) {
vertexA = merge(vertexA, vertexB);
delete vertexA;
}
}
}

加了小端点归并算法后,「端点识别」的准确度就上来了。经小编本地质衡量试已经足以 百分之百 识别有损的联网图了。

 

Mesh Libraries and Tools

3.4 化解残砖

上一小节提到了「描述墙体的界限并记录墙体的架空」的「列集结」,小编是一向动用这几个「列群集」来祛除残砖的,伪代码如下:

JavaScript

function clearAll() { let count = 0; for(let col = 0, len = this.wall.length; col < len; col) { let colInfo = this.wall[col]; for(let row = colInfo.start; row <= colInfo.end; row) { let tile = this.grid[row * this.col col]; tile.score = -20 - 40 * count ; // 标志嘉勉分数 tile.removed = true; } } }

1
2
3
4
5
6
7
8
9
10
11
function clearAll() {
let count = 0;
for(let col = 0, len = this.wall.length;  col < len; col) {
let colInfo = this.wall[col];
for(let row = colInfo.start; row <= colInfo.end; row) {
let tile = this.grid[row * this.col col];
tile.score = -20 - 40 * count ; // 标记奖励分数
tile.removed = true;
}
}
}

线条识别

小编分八个步骤实现「线段识别」:

  1. 加以的八个端点连接成线,并访谈连线上N个「样本点」;
  2. 遍历样本点像素,若是像素色值不等于线段色值则表示这五个端点之间官样文章线段

怎么采摘「样式点」是个难题,太密集会影响属性;太疏松精准度无法担保。

在小编眼前有三个选项:N 是常量;N 是变量。
假设 N === 5。局地提取「样式点」如下:

图片 19

上航海用教室,会识别出三条线条:AB, BC 和 AC。而实际,AC无法成线,它只是因为 AB 和 BC 视觉上共一线的结果。当然把 N 值向上进步能够解决这几个难点,可是 N 作为常量的话,那一个常量的取量须要靠经验来判别,果然丢弃。

为了幸免 AB 与 BC 同处一贯线时 AC 被识别成线段,其实很轻松 —— 四个「样本点」的区间小于或等于端点直径
假设 N = S / (2 * R),S 表示两点的偏离,中华V代表端点半径。局地提取「样式点」如下:

图片 20

如上海图书馆,成功地绕过了 AC。「线段识别算法」的伪代码落成如下:

JavaScript

for(let i = 0, len = vertexes.length; i < len - 1; i) { let {x: x1, y: y1} = vertexes[i]; for(let j = i 1; j < len; j) { let {x: x2, y: y2} = vertexes[j]; let S = Math.sqrt(Math.pow(x1 - x2, 2) Math.pow(y1 - y2, 2)); let N = S / (R * 2); let stepX = (x1 - x2) / N, stepY = (y1 - y2) / n; while(--N) { // 样本点不是线段色 if(!isBelongLine(x1 N * stepX, y1 N * stepY)) break; } // 样本点都过关 ---- 表示两点成线,保存 if(0 === N) lines.push({x1, y1, x2, y2}) } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
for(let i = 0, len = vertexes.length; i < len - 1; i) {
let {x: x1, y: y1} = vertexes[i];
for(let j = i 1; j < len; j) {
let {x: x2, y: y2} = vertexes[j];
let S = Math.sqrt(Math.pow(x1 - x2, 2) Math.pow(y1 - y2, 2));
let N = S / (R * 2);
let stepX = (x1 - x2) / N, stepY = (y1 - y2) / n;
while(--N) {
// 样本点不是线段色
if(!isBelongLine(x1 N * stepX, y1 N * stepY)) break;
}
// 样本点都合格 ---- 表示两点成线,保存
if(0 === N) lines.push({x1, y1, x2, y2})
}
}

 2、查询7号课程战绩在90分以上或60分以下的上学的孩童学号。

Surface_Mesh(D. Sieger, M. Botsch)

4. View

View 首要的意义有七个:

  • UI 管理
  • 映射 Model 的变化(动画)

UI 处理主若是指「分界面绘制」与「财富加载管理」,这两项功用相比常见本文就一直略过了。View 的主体是「映射 Model 的扭转」并完毕对应的卡通。动画是复杂的,而映射的准则是粗略的,如下伪代码:

JavaScript

update({originIndex, index, clr, removed, score}) { // 还从未 originIndex 或从不色值,直接不处理 if(originIndex === undefined || clr === undefined) return ; let tile = this.tiles[originIndex]; // tile 存在,判定颜色是不是同样 if(tile.clr !== clr) { this.updateTileClr(tile, clr); } // 当前目录变化 ----- 表示地方也有生成 if(tile.index !== index) { this.updateTileIndex(tile, index); } // 设置分数 if(tile.score !== score) { tile.score = score; } if(tile.removed !== removed) { // 移除或丰盛当前节点 true === removed ? this.bomb(tile) : this.area.addChild(tile.sprite); tile.removed = removed; } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
update({originIndex, index, clr, removed, score}) {
// 还没有 originIndex 或没有色值,直接不处理
if(originIndex === undefined || clr === undefined) return ;
let tile = this.tiles[originIndex];
// tile 存在,判断颜色是否一样
if(tile.clr !== clr) {
this.updateTileClr(tile, clr);
}
// 当前索引变化 ----- 表示位置也有变化
if(tile.index !== index) {
this.updateTileIndex(tile, index);
}
// 设置分数
if(tile.score !== score) {
tile.score = score;
}
if(tile.removed !== removed) {
// 移除或添加当前节点
true === removed ? this.bomb(tile) : this.area.addChild(tile.sprite);
tile.removed = removed;
}
}

Model 的砖头每一回数据的改换都会打招呼到 View 的砖块,View 会依据对应的成形做相应的动作(动画)。

天性优化

鉴于「自动识图」需求对图像的的像素点进行围观,那么质量确实是个须求关切的主题材料。作者设计的「自动识图算法」,在辨认图像的长河中须要对图像的像素做三次扫描:「搜集色值表」 与 「采撷端点」。在围观次数上实在很难下落了,不过对于一张 750 * 1334 的底图来讲,「自动识图算法」需求遍历五回长度为 750 * 1334 * 4 = 4,002,000 的数组,压力依旧会有的。笔者是从压缩被围观数组的尺码来进步品质的。

被扫描数组的尺寸怎么削减?
小编直接通过减少画布的尺寸来完毕收缩被扫描数组尺寸的。伪代码如下:

JavaScript

// 要缩减的倍数 let resolution = 4; let [width, height] = [img.width / resolution >> 0, img.height / resolution >> 0]; ctx.drawImage(img, 0, 0, width, height); let imageData = ctx.getImageData(), data = imageData;

1
2
3
4
5
// 要压缩的倍数
let resolution = 4;
let [width, height] = [img.width / resolution >> 0, img.height / resolution >> 0];
ctx.drawImage(img, 0, 0, width, height);
let imageData = ctx.getImageData(), data = imageData;

把源图片裁减4倍后,获得的图片像素数组独有原本的 4^2 = 16倍。那在性质上是非常大的提拔。

Select sno from sc where cno='7' and grade not between 60and 90

GTS(2D dynamic/constrained Delaunay triangulation, robust geometric predicates, mesh boolean set operations, refinement/coarsening, view-independent continuous LOD, view-dependent LOD, AABB-trees, Kd-trees, graph partitioning, isosurfacing, area, volume, mean/gaussian/principal curvature, stripification)

5. Control

Control 要管理的政工非常多,如下:

  • 绑定 Model & View
  • 扭转通过海关分值
  • 看清通过海关条件
  • 对外交事务件
  • 顾客交互

初步化时,Control 把 Model 的砖块单向绑定到 View 的砖头了。如下:

Object.defineProperties(model.tile, { originIndex: { get() {...}, set(){ ... view.update({originIndex}) } }, index: { get() {...}, set() { ... view.update({index}) } }, clr: { get() {...}, set() { ... view.update({clr}) } }, removed: { get() {...}, set() { ... view.update({removed}) } }, score: { get() {...}, set() { ... view.update({score}) } } })

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Object.defineProperties(model.tile, {
    originIndex: {
        get() {...},
        set(){
            ...
            view.update({originIndex})
        }
    },  
    index: {
        get() {...},
        set() {
            ...
            view.update({index})
        }
    },
    clr: {
        get() {...},
        set() {
            ...
            view.update({clr})
        }
    },
    removed: {
        get() {...},
        set() {
            ...
            view.update({removed})
        }
    },  
    score: {
        get() {...},
        set() {
            ...
            view.update({score})
        }
    }
})
 

「通关分值」与「决断通过海关条件」那对逻辑在本文的「游戏准则」中有连带介绍,这里不再赘述。

对外交事务件规划如下:

name detail
pass 通关
pause 暂停
resume 恢复
gameover 游戏结束

客户交互 APIs 规划如下:

name type deltail
init method 初始化游戏
next method 进入下一关
enter method 进入指定关卡
pause method 暂停
resume method 恢复
destroy method 销毁游戏

应用「自动识图」的提议

就算作者在地面测量检验的时候能够把持有的「底图」识别出来,然而并无法保障别的开荒者上传的图形是或不是被很好的辨别出来。作者建议,能够把「自动识图」做为一个单独的工具使用。

作者写了一个「自动识图」的独自工具页面:
能够在这几个页不熟悉成对应的关卡配置。

 

trimesh2 mesh library(read PLY/OFF/3DS/OBJ, write PLY/OFF/OBJ, subdivision, smoothing, curvature estimation, triangle stripping, ICP, cleanup, decimation, basic shapes) (S. Rusinkiewicz)

6. 问题

在今日头条有八个关于「消灭星星」的话题:popstar关卡是何等统一图谋的?

这几个话题在最终建议了八个主题材料 —— 「不可能解决和最大得分不满意过关条件的矩阵」

图片 21

「无法清除的矩阵」其实正是最大得分为0的矩阵,本质上是「最大得分不满足过关条件的矩阵」。

最大得分不满足过关条件的矩阵
求「矩阵」的最大得分是八个「单肩包难题」,求解的算法简单:对现阶段矩阵用「递归」的花样把富有的消灭分支都施行三回,并取最高分值。可是javascript 的「递归」极易「栈溢出」导致算法不能实行。

实际上在和讯的话题中涉及三个实施方案:

互连网查到井井有理建议做个工具随便生成关卡,自动计算,把适合得分条件的关卡筛选出来

那些应用方案代价是昂贵的!我提供有源码并不曾解决那个标题,而是用三个相比取巧的主意:进去娱乐前检查是事为「十分的小概排除矩阵」,假如是重复生成关卡矩阵

瞩目:作者使用的取巧方案并未化解难题。

结语

下边是本文介绍的「一笔画」的线上 DEMO 的二维码:

图片 22

游玩的源码托管在:
中间玩耍完结的大旨代码在:
自行识图的代码在:

感激耐心阅读完本小说的读者。本文仅代表小编的个人观点,如有不妥之处请不吝赐教。

谢谢您的阅读,本文由 坑坑洼洼实验室 版权全数。假使转发,请表明出处:凹凸实验室()

1 赞 1 收藏 评论

图片 23

 3、查询课程名以“数据”两个字起先的有所科目标课程号和课程名。

OpenMesh(PLY/OBJ, halfedge mesh, decimation, Loop/Sqrt3 subdivision, view-dependent progressive meshes, stripification) (J. Möbius, M. Habbecke)

7. 结语

下边是本文介绍的「消灭星星」的线上 DEMO 的二维码:

图片 24

娱乐的源码托管在:

感谢耐心阅读完本文章的读者。本文仅表示小编的个人观点,如有不妥之处请不吝赐教。
假使对「H5游戏开辟」感兴趣,款待关心大家的专栏。

Select cno,cname from c where cname like '数据%'

CGAL(2D/3D halfedge mesh, tet meshes) [full packages list] (Authors)

仿照效法资料

  • Knapsack problem
  • NP-completeness
  • popstar关卡是何许设计的?
  • 费雪耶兹乱序算法
  • 不定均分算法

    1 赞 收藏 评论

图片 25

 

MeshLab(import PLY/STL/OFF/OBJ/3DS/COLLADA/PTX/V3D/PTS/APTS/XYZ/GTS/TRI/ASC/X3D/X3DV/VRML/ALN, export PLY/STL/OFF/OBJ/3DS/COLLADA/VRML/DXF/GTS/U3D/IDTF/X3D, selection/smoothing painting, linear measurements, export planar slices, mesh decimation/repair/optimization, mesh alignment) [All Filters] (Authors)

 4、查询每一个学员具备课程的平均战表,输出学生学号和平均成绩。

OpenFlipper(import/export OFF/OBJ/PLY/STL/STLA/STLB/OM, selection tools w/ surface/volume lasso and sphere brush, decimation, smoothing, edge/face editing) (J. Möbius)

    Select sno,avg(grade)from sc group by sno

mview(read/view PMesh/GTS/OFF/COFF/PLY/VRML/Shallo/VTK ASCII POLYDATA/OBJ) (H. Cantzler, T. Breckon)

 5、查询每门课程的选修人数,输出课程号和选修人数。

PLY Tools(read/write PLY)

    Selectcno,count(*) from sc group by cno

ply2vri(convert PLY mesh to signed-distance volumetric grid, VRI/PPM formats) (B.Allen)

 6、查询选修7号课程的上学的小孩子的学号、姓名、性别。

JMeshLib(read/write OFF/PLY/STL/VRML1/VRML2/OBJ/IV 2.1) (M. Attene)

    Selects.sno,sname,ssex from s,sc where s.sno=sc.sno and cno='7'

ReMESH(automatic manifold repair, isolated component removal, hole filling, handle removal, degenerate triangle removal, sharp feature recovery, defect detection and hilighting, manual repair tools) (M. Attenne)

    或: Select sno,sname,ssex from s where sno in

GPUmesh - Easy Cross-Plateform Cross-API Mesh Management for GPUs(S. Lefebvre)

              ( Select sno from sc where cno='7' )

A48: A Dynamic Adaptive Mesh Library based on Stellar Operators (L. Velho)

 7、查询选修7号课程的学员的平均年龄。

Volumetric Mesh: tetrahedral and cube volumetric 3D meshes (J. Barbic)

    Selectavg(sage) from s,sc where s.sno=sc.sno and cno='7'

3d-workspace(quadric mesh simplification, re-meshing, recursive/monte-carlo/sphere-packing/voxel sampling, skeleton extraction, Laplacian/scale-dependent/mean-curvature-flow smoothing, Sqrt3/Loop/modified-butterfly/longest-edge subdivision, minimum bounding box, mean value coordinates, Green coordinates, curvature (polynomial fitting, two other implementations), FFD, voxel deformation, skinning with dual quaternions, mesh voxelization, octree, kd-tree, colormap, mesh slicing) (Authors)

    或: Select avg(sage) from s where sno in

SimplexMesh: general non-manifold, non-regular simplicial mesh for mixed dimensions leq 3 (C. Batty)

              (Select sno from sc where cno='7' )

Shape Modeling

 8、查询有30名以上学生选修的课程号。

CGAL Subdivision Demo(Sqrt3, Quad-Triangle, Catmull-Clark, Doo-Sabin) (L.-J. Shiue, P. Alliez, R. Ursu, L. Kettner)

    Select cno fromsc group by cno having count(*)>30

interactive mesh deformation(S. Yoshizawa)

 9、查询于今未曾考试不如格的学习者学号。

manifold harmonics shape deformation(B. Vallet, B. Lévy)

    Select distinctsno from sc where sno not in

Graphite(Catmull-Clark subdivision) (Authors)

         ( Select sno from sc where grade<60 )

MeshLab(loop/butterfly/midpoint subdivision) (Authors)

    或: Select sno from sc group by sno havingmin(grade)>=60

3D Model Synthesis from examples(P. Merrell)

10、查询全体考试成绩的平分分一样的上学的儿童学号分组

ShapeShop: sketch-based hierarchical implicit surface ("BlobTree") modeling (and SurfaceTree)(R. Schmidt, B. Wyvill, K. Singh)

二、

Real-time implicit surface polygonizer(R. Schmidt)

 1、搜索选修课程号为C2的学员学号与成就。

As-rigid-as-possible 2D shape manipulation(R. Schmidt)

Select sno,grade from sc where cno='C2'

Similarity-Based Surface Modelling(S. Zelinka)

 

Mesh Modelling Using Curve Analogies(S. Zelinka)

 2、寻觅选修课程号为C4的学生学号与姓名。

2D laplacian curve editing(O. Sorkine)

    Selects.sno,sname from s,sc where s.sno=sc.sno and cno='C4'

Convolution Surfaces for Line Skeletons with Polynomial Weight Distributions(X. Jin, J. Feng, Q. Peng, C-L. Tai)

    注意本题也足以用嵌套做

CARVErobust boolean operations between arbitrary polygonal models (T. Sargeant)

寻思本题改为“寻找选修课程号为C4的学习者学号、姓名与成就”后还可以够用嵌套做吗?

GeoBrush: Interactive Mesh Geometry Cloning(discrete expmap, 3D Green coordinates deformation, GPU Poisson solver) (K. Takayama, R. Schmidt, K. Singh, T. Igarashi, T. Boubeker, O. Sorkine)

 

MeshFlow: Interactive Visualization of Mesh Construction Sequences(J. Denning, W. Kerr, F. Pellacini)

 3、找寻选修课程名称叫 Maths 的学习者学号与姓名。

3D Modeling with Silhouettes(A. Rivers, F. Durand, T. Igarashi)

    Selects.sno,sname from s,sc,c

Apparent Layer Operations for the Manipulation of Deformable Objects(T. Igarashi, J. Mitani)

    where  s.sno=sc.sno and c.cno=sc.cno andcname='Maths'

Mesh Snapping: Robust Interactive Mesh Cutting Using Fast Geodesic Curvature Flow(J. Zhang, C. Wu, J. Cai, J. Zheng, X-C. Tai)

瞩目本题也能够用嵌套做

Mixed Finite Elements for Variational Surface Modeling(A. Jacobson, E. Tosun, O. Sorkine, D. Zorin)

 

Bounded Biharmonc Weights for Real-Time Deformation(A. Jacobson, I. Baran, J. Popović, O. Sorkine)

 4、寻觅选修课程号为C2或C4的学生学号。

DT-Grid: data structure for high-resolution level sets(open and closed surfaces, dilation, CSG, texture coordinates, conversion from OBJ/PLY, to PLY) (M. Nielsen, K. Museth)

    Select distinctsno from sc where cno in ('C2','C4')

Shape Space Exploration of Constrained Meshes(Y-L. Yang, Y.-J. Yang, H. Pottmann, N. Mitra)

或: Select distinct sno from sc where cno='C2' or cno='C4'

Slices: A Shape-proxy Based on Planar Sections(J. McCrae, K. Singh, N. Mitra)

 

EXPLORATION OF CONTINUOUS VARIABILITY IN COLLECTIONS OF 3D SHAPES(M. Ovsjanikov, W. Li, L. Guibas, N. Mitra)

 5、搜索选修课程号为C2和C4的学习者学号。

GlobFit: Consistently Fitting Primitives by Discovering Global Relations(data) (Y. Li, X. Wu, Y. Chrysanthou, A. Sharf, D. Cohen-Or, N. Mitra)

    Select sno fromsc where cno='C2' and sno in

Detail-Replicating Shape Stretching(Implicit mesh fairing using curvature flow, patch-based texture synthesis, octree with ray tracing, bézier splines) (I. Alhashim)

         ( Select sno from sc where cno='C4' )

Volumetric Modeling with Diffusion Surfaces(K. Takayama, O. Sorkine, A. Nealen, T. Igarashi)

    注意本题也能够用一而再做

Metropolis Procedural Modeling(J. Talton, Y. Lou, S. Lesser, J. Duke, R. Mech, V. Koltun)

思考:Select distinct sno from sc where cno='C2' andcno='C4'正确吗?

siteplan: rapid architectural prototyping using procedural extrusions(T. Kelly, P. Wonka)

 

mesh-talent (gauss-newton solver, (unknown) graph-based mesh deformation) (Authors)

 6、寻觅不学C2课程的学员姓名和年龄。

Smooth Shape-Aware Functions with Controlled Extrema(A. Jacobson, T. Weinkauf, O. Sorkine)

    Selectsname,sage from s where sno not in

SESAME: Sketch, Extrude, Sculpt and Manipulate Easily(J. Y. Oh)

         ( Selectsno from sc where cno='C2' )

Virtual LEGO(J. Y. Oh)

    或: Select sname,sage from s where not exists

Easy Mesh Cutting(Z. Ji, L. Liu, Z. Chen, G. Wang)

              (Select * from sc where sno=s.sno and cno='C2' )

sculpt: simple level set sculpting program(multi-resolution point splatting) (R. Bridson)

 

Segmentation

 7、寻找选修了数据库课程的保有学员姓名。(同3)

efpisoft: hierarchical mesh segmentation based on fitting primitives(M. Attene)

    Select snamefrom s,sc,c

mesh segmentation benchmark database and viewer(X. Chen, A. Golovinskiy, T. Funkhouser)

where  s.sno=sc.snoand c.cno=sc.cno and cname='数据库'

Graphite(variational shape approximation,image vectorization) [documentation wiki] (Authors)

 

SegMatch: Shape Segmentation and Shape Matching from Point Cloud(T. Dey, S. Goswami)

 8、搜索数据库课程不如格的女孩子姓名。

ShapeAnnotatorsegmentation tool (fitting primitives, barycentric/height/integral geodesic Morse, Plumber, Lloyd clustering)(Authors)

    连接:Select sname from s,sc,c

Shape Diameter Function (SDF) segmentation tool(L. Shapira)

         where  s.sno=sc.sno andc.cno=sc.cno and cname='数据库'

Parameterization

                and grade<60 and ssex='女'

fast stretch-minimizing parameterization(includes shape-preserving/Tutte/harmonic parameterization, and natural conformal parameterization ) (S. Yoshizawa)

    嵌套:Select sname from s where ssex='女' and  sno in

Graphite(ABF, ABF , DPBF, LSCM, HLSCM, Barycentric, mean-value coordinates, L2 stretch,spectral conformal,Periodic Global Parameterization,Constrained texture mapping,texture atlas generation) [documentation wiki] (Authors)

               (Select sno from sc where grade<60 and cno in

CGAL(LSCM, discrete conformal/authalic, Floater mean-value, Tutte barycentric) [full packages list] (Authors)

                     ( Select cno from c where cname='数据库' )

linear discrete conformal parameterization(K. Crane)

               )

Discrete Exponential Map Demo(R. Schmidt)

 

Local/Global Approach to Mesh Parameterization(ARAP, ASAP) (L. Liu, L. Zhang, Y. Xu, G. Gotsman, S. Gortler)

 9、搜索各门课程的平均成绩,输出课程名和平均战表。

Mesh Processing

    Selectcname,avg(grade) from sc,c

mesh smoothing/denoising by averaging with similarity-based weights(S. Yoshizawa)

    wherec.cno=sc.cno  group by c.cno,cname

estimating curvature tensors on triangle meshes with CGAL(P. Alliez)

心想本题也得以用嵌套做吧?

Graphite(mesh curvature,uniform remeshing,mesh repair,volume meshing,manifold harmonics,appearance-preserving simplification,normal mapping) [documentation wiki] (Authors)

 

CGAL(mesh simplification, mesh ridges/umbilics, mesh curvature)[full packages list] (Authors)

10、寻觅各样学生的平均战表,输出学生姓名和平均成绩。

MeshLab(discrete curvature, monte-carlo/stratified/poisson-disk mesh sampling, Hausdorff distance between meshes, uniform remeshing, voronoi vertex clustering, laplacian/Taubin smoothing, mesh unsharp mask, geodesic distances) [All Filters] (Authors)

    Selectsname,avg(grade) from s,sc

Simplification Envelopesgenerate mesh level-of-detail hierarchies (J. Cohen, A. Varshney, G. Turk)

    wheres.sno=sc.sno group by s.sno,sname

Volfillfill holes in mesh using volumetric diffusion (S. Marschner, K. Berglund)

合计本题也得以用嵌套做呢?

trimeshinfo: compute mesh properties(manifold, genus, orientation, volume, self-intersection, boundaries, connected components, ...) (Authors)

 

Discrete Laplace Operator on Meshed Surfaces(J. Sun, M. Belkin, Y. Wang)

11、搜索最少有贰19个学生选修的学科名。

QualMesh: Delaunay meshing of surfaces and volumes(T. Dey, T. Ray)

    Select cnamefrom c where cno in

DelIso: delaunay meshing of isosurfaces(T. Dey, J. Levine)

         ( Selectcno from sc group by cno having count(*)>=30 )

SurfRemesh: Delaunay Remeshing of Polygonal Surfaces(T. Dey, T. Ray)

在意本题也得以用一连做

DelPSC: delaunay mesh generation for surfaces, volumes and complexes(T. Dey, J. Levine)

 

TriMesh2MT: convert polygon mesh to Multi-Triangulation(M. Attenne, E. Danovaro, P. Magillo)

12、找寻选修了相当的多于3门科目标学员姓名。

approximating gradients on meshes and point coulds via diffusion metric(C. Luo, I. Safa, Y. Wang)

    Select snamefrom s where sno in

geodesic: multiple source/target exact geodesic algorithm for triangular mesh(unknown)

         ( Selectsno from sc group by sno having count(*)>=3 )

IsoEx: Feature Sensitive Mesh Extraction(L. Kobbelt, M. Botsch, Schwanecke, H.P. Seidel)

只顾本题也能够用一而再做

discrete connections and smooth vector fields on triangle meshes(K. Crane, M. Desbrun, P. Schroder)

 

Scapeterrain simplification (M. Garland)

13、找寻各门课程的战表均非常的大于90分的学习者姓名。

Terraterrain simplification (M. Garland)

   Select snamefrom s,sc where s.sno=sc.sno

QSlimmesh simplification software (M. Garland)

         group bys.sno,sname having min(grade)>=90

Permission Gridmesh simplification (S. Zelinka)

方法二:

Topology-based Smoothing of 2D Scalar Fields with C1-Continuity[Page] (T. Weinkauf, Y. Gingold, O. Sorkine)

Select sname from s where sno not in

HanTun: computing handle and tunnel loops in 3D models(T. Dey, K. Li)

         ( Selectsno from sc where grade<90 )

Non-Iterative, Feature-Preserving Mesh Smoothing(also [Page]) (T. Jones)

借使有一门不低于90分就能输出该学生学号

Efficient Implementation of Marching Cubes’ Cases with Topological Guarantees(T. Lewiner, H. Lopes, A. Viera, G. Tavares)

 

Direct Spherical Harmonic Transform of a Triangulated Mesh(M. Mousa, R. Chaine, S. Akkouche)

14、寻找数据库课程战表不小于该门课程平均分的上学的小孩子姓名。

Normalizing for Anisotropy in Triangle Models(M. Kazhdan)

    Select snamefrom s,sc,c

Symmetry Descriptors and 3D Shape Matching(M. Kazhdan)

    where  s.sno=sc.sno and sc.cno=c.cno and cname='数据库' and grade>

Unconstrained Isosurface Extraction on Arbitrary Octrees(M. Kazhdan)

         ( Selectavg(grade) from sc,c

Fast Mean-Curvature Flow via Finite-Elements Tracking(M. Kazhdan)

           where sc.cno=c.cnoand cname='数据库'

Interactive and Anisotropic Geometry Processing Using the Screened Poisson Equation(M. Kazhdan)

         )

Blended Intrinsic Maps(V. Kim, Y. Lipman, T. Funkhouser)

15、找寻各种系科男女学生的平均年龄和人数。

Spin Transforms of Discrete Surfaces(K. Crane, U. Pinkall, P. Schröder)

    Selectsdept,ssex,avg(sage),count(*) from s group by sdept,ssex

shapeDNA: compute accurate eigenvalues and eigenfunctions of the Laplace Beltrami operator using higher order FEM with Dirichlet or Neumann boundary conditions (cubic FEM, global mesh refinement, tangential smoothing, PLY, SMF, OBJ, OFF, VTK, ASC3D, STL) (M. Reuter)

16、寻觅Computer系(JSJ)课程平均分最高的学员学号和人名。

Manifold Mesh Processing(non-manifold repair, smoothing, normals from point clouds, curvature calculation, local shape descriptors, Polymender manifold mesh from triangle soup, mesh simplification) (C. Grimm)

    Selects.sno,sname from s,sc where s.sno=sc.sno and sdept='JSJ'

GMSH: a three-dimensional finite element mesh generator with built-in pre- and post-processing facilities(procedural parameterized geometry, 1/2/3D simplicial finite element meshing, element size control, scalar/vector/tensor datasets) (C. Geuzaine, J.-F. Remacle)

    group bys.sno,sname

Skeleton Extraction by Mesh Contraction(O. Au, C.-L. Tai, H.-K. Chu, D. Cohen-Or, T.-Y. Lee)

    havingavg(grade) >=ALL

Polymender mesh repairer(water-tightness, closed surface repair, sharp features, signed volume generation) (T. Ju)

         ( Selectavg(grade) from s,sc

Algorithms to Automatically Quantify the Geometric Similarity of Anatomical Surfaces(D. Boyer, Y. Lipman, E. St Clair, J. Puente, B. Patel, T. Funkhouser, J. Jernvall, I. Daubechies)

           wheres.sno=sc.sno and sdept='JSJ'

Discrete Laplacians on General Polygonal Meshes(M. Alexa, M. Wardetzky)

           group bys.sno

meshfix(dilation, intersection test, ensure minimal distance, resolve overlaps, cut using shell, cleanup, (M. Attene)

         )

progressive meshesimplementation (H. Hao)

17、(补充)查询每门学科的及格率。

HKS: Computing Heat Kernel Signature(J. Sun, M. Ovsjanikov, L. Guibas)

    本题能够分三步做:

SDFGen: generate grid-based signed distance field (level set) from triangle meshes(C. Batty, R. Bridson)

   

mesh_query: robustly checking inside/outside and segment intersection with a mesh(R. Bridson)

    第1步:获得每门课的选修人数

Point Set Processing

     createview  v_all(cno,cnt)

QPoly: meshing scattered 3D points(Y. Ohtake)

         as selectcno, count(*) from sc group by cno

SLIM: sparse low-degree implicits(fitting, rendering, reflection lines, analytic curvature measures, crest lines, suggestive contours) (Y. Ohtake, A. Belyaev, M. Alexa)

    第2步:获得每门课及格人数

Multi-scale Compactly Supported Radial Basis Functions(Y. Ohtake)

     createview  v_pass(cno,cnt_pass)

MeshLab(ICP range-map alignment, ball pivoting, point-set normals, Robust Implicit MLS (RIMLS), Algebraic point-set surface (APSS), Poisson surface reconstruction) [All Filters] (Authors)

         as selectcno, count(*) from sc where grade>=60 group by cno

Vrip(convert aligned range images to volumetric format, extract mesh surface) (B. Curless)

    第3步:每门课的合格人数/每门课选修人数

Zipper(combine range images into polygonal mesh) (G. Turk, H. Ge, B. Curless)

     selectv_all.cno, cnt_pass*100/cnt  from  v_all, v_pass

Scanalyze(manual/ICP alignment and merging of range image data, fill holes, decimation, editing, PLY/SD formats) (Authors)

     where v_all.cno = v_pass.cno

QSplat(real-time multiresolution point-set rendering) (S. Rusinkiewicz, G. King)

 

Constructing Laplace Operator from Point Clouds in R^d(J. Sun, M. Belkin, Y. Wang)

18、查询平均分比不上格的学员的学号,姓名,平均分。

NormFet: Approximating Normals and Feature Sizes from Noisy Point Clouds(T. Dey, J. Sun, L. Molnar)

    Selectsc.sno,sname,avg(grade) from student,sc

AMLS for Smoothing Noisy Point Clouds(adaptive moving least squares) (T. Dey, J. Sun)

    wherestudent.sno=sc.sno

Cocone: Delaunay meshing of point sets with boundaries(T. Dey, J. Giesen)

    group bysc.sno,sname

Tight Cocone: water-tight Delaunay meshing of point sets using approximate medial axis(T. Dey, S. Goswami, W. Zhao)

    havingavg(grade)<60

SuperCocone: efficient Delaunay meshing of large point sets(T. Dey, J. Hudson)

思维本题也得以用嵌套做吗?

RobustCocone: Delaunay meshing of noisy point sets(T. Dey, S. Goswami)

 

Peel: isotropic reconstruction of surfaces from point sets with or without boundaries(T. Dey, K. Li)

19、查询平均分不如格的上学的儿童人数。

4-points Congruent Sets for Robust Surface Registration(D. Aiger, N. Mitra, D. Cohen-Or)

    Select count(*)from student

CGAL(Poisson reconstruction, spacing estimation, simplification, outlier removal, smoothing, normal estimation, orientation estimation)[full packages list] (Authors)

    where sno in

integral estimation on a k-manifold embedded in R-d(C. Luo, J. Sun, Y. Wang)

         ( selectsno from sc group by sno having avg(grade)<60 )

discrete Laplace-Beltrami operator on point clouds[Page] (C. Luo, I. Safa, Y. Wang)

    上边是一个超级的不当

FReg: detect approximate symmetries of point sets and B-Rep Models(Authors)

Select count(*) from sc group by sno havingavg(grade)<60

Normal Improvement for Point Rendering(bilateral normal filtering) [Page] (T. Jones)

那是每种学员有几门不如格的多寡

Reconstruction of Solid Models from Oriented Points Sets(M.l Kazhdan)

 

Poisson Surface Reconstruction(M. Kazhdan)

三、

Multilevel Streaming for Out-of-Core Surface Reconstruction(M. Bolitho, M. Kazhdan, R. Burns, H. Hoppe)

 1、查询报酬在1000到贰仟元以内的男人业务员的人名和办公编号。

CUDA-Based implementations of SoftAssign and EM-ICP(T. Tamaki, M. Abe, B. Raytchev, K. Kaneda, M. Slomp)

    SelectYname,Ono from YWY

Coherent Point Drift(rigid, affine, nonrigid N-D alignment and correspondence) (A. Myronenko)

    where Salarybetween 1000 and 3000 and Ysex='男'

PowerCrust(watertight polygonal meshing of point set, medial axis transform, simplified medial axis) (N. Amenta) [updated port]

 2、查询各类办公室的业务员人数,输出办公室编号和对应的食指。

Curves and Surfaces

    SelectOno,count(*) from YWY group by Ono

A Bidirectional Generating Algorithm for Rational Parametric Curves(Z. Li, L. Ma)

 3、查询每一种客商在二零零二年三月购销的总金额,输出顾客号和呼应的总金额。

Fast Detection of the Geometric Form of Two-Dimensional Cubic Bézier Curves(S. Vincent)

    SelectKno,sum(Fmoney) from FP

Exact Evaluation of Subdivision Surfaces(eigenstructures for Catmull-Clark and Loop schemes) (J. Stam)

    where Fdatebetween '2002.5.1' and '2002.5.31'

Exact Evaluation of Catmull-Clark Subdivision Surfaces near B-Spline Boundaries(D. Lacewell, B. Burley)

    group by Kno

Smooth Two-Dimensional Interpolations: A Recipe for All Polygons(E. Malsch, J. Lin, G. Dasgupta)

 4、查询2000年7月进货次数超过5次的享有客商号,且按顾客号升序排序。 

Normal Patches / PN-Triangles(R. Stimpson)

    Select Kno fromFP

Marching Cubes(.vol files) (R. Stimpson)

    where Fdatebetween '2002.5.1' and '2002.5.31'

Coons Patches(R. Stimpson)

    group by Kno

Exact Catmull-Clark Subdivision evaluation(and mean-curvature minimization) (F. Hecht)

    havingcount(*)>5

Laplacian Surface Editing(2D curve deformation) (O. Sorkine, D. Cohen-Or, Y. Lipman, M. Alexa, C. Roessl, H.-P. Seidel)

    order by KnoASC

Elasticurves: Exploiting Stroke Dynamics and Inertia for the Real-time Neatening of Sketched 2D Curves(Y. Thiel, K. Singh, R. Balakrishnan)

 5、查询各办公室男子和女人业务员的平均薪给。

Computational Geometry

    SelectOno,Ysex,avg(Salary) from YWY group by Ono,Ysex

2D voronoi diagrams with CGAL(P. Alliez)

 6、查询二零零四年11月早就在王海亮业务员手中进货过商品的顾客号、

TRIANGLE: 2D high-quality exact/constrained/conforming Delaunay triangulations(J. Shewchuk)

            客商姓名和联系电话。

Stellar: a tetrahedral mesh improvement program(B. Klingner, J. Shewchuk)

    SelectKno,Kname,Phone from KH where Kno in

TetGen: A Quality Tetrahedral Mesh Generator and a 3D Delaunay Triangulator (constrained/conforming Delaunay, Voronoi, boundary-conforming, quality/size control, adaptive mesh refinement, intersection testing) (H. Si)

         ( SelectKno from FP

manifold approximation of 3D medial axis(S. Yoshizawa)

           whereFdate between '2002.5.1' and '2002.5.31' and Yno in

CGAL(n-D geometry, 2D/3D spherical geometry, n-D convex hull, 2D/3D/spherical booleans, 2D minkowski sums, 2D polygon partition/offset/skeleton, 2D curve intersection, 2D/3D envelopes, 2D/3D triangulation, 2D/3D alpha shapes, 2D delaunay/voronoi/apollonius/conforming delaunay/conforming gabriel, 3D delaunay isosurfacing, 3D skin surface, n-D spatial data structures, bounding volumes, optimal distances, least-squares geometry fitting, 2D/surface function interpolation, kinetic data structures, AABB Tree, 3D periodic triangulations, tetrahedral meshing w/ implicit/volume/polyhedral boundaries) [full packages list] (Authors)

                      ( Select Yno from YWY where Yname='王海亮' )

METRO: measure differences between triangular meshes(Authors)

         )

BOOLEconvert CSG to Brep (curved solid primitives, polygonal solids) (T. Culver)

    注意本题也得以用三翻五次做

MAPC: exact manipulation of algebraic points and curves in the plane (D. Manocha, J. Keyser, T. Culver, M. Foskey, S. Krishnan)

 7、查询全数薪资比1538号业务员高的业务员的号码、姓名和工资。

Fast Polygon Triangulation based on Seidel's Algorithm(A. Narkhede, D. Manocha)

    SelectYno,Yname,Salary from YWY where Salary >

UNC GAMMA Collision Detection/Proximity Query Packages(penetration depth, intersection detection, tolerance verification, exact and approximate distance computation, separation distance, contact determination, Minkowski sums, Lin-Canny closest features, uniform grid spatial decomposition, OBB/Swept-Sphere/convex-hull bounding-volume hierarchies, polygon soups, rigid motion)

         ( SelectSalary from YWY where Yno='1538' )

DeformCD: collision detection for deformable models(M. Tang, D. Manocha)

 8、查询全体与1538号业务员在同贰个办公的其余业务员的号子和人名。

CurveSkel: 1D curve skeleton of 3D shape(T. Dey, J. Sun)

    SelectYno,Yname from YWY where Yno!='1538' and Ono in

Shortest paths on a polyhedral surface(B. Kaneva, J. O'Rourke)

         ( SelectOno from YWY where Yno='1538' )

Antiprism polyhedra library(convex hull, polar reciprocals (dual meshes), geodesic spheres, uniform polyhedra, uniform tilings on surfaces, lattices and grids, ring/spiral of points on sphere, equilibrium of points repelling on sphere, near-uniform sphere tessellation, many other polyhedra types, OFF conversion utilities) [A. Rossiter]

 9、查询出卖总金额最高的业务员的号码。

Nuages3D delaunay mesh reconstruction from parallel cross-sections (B. Geiger)

    Select Yno fromFP group by Yno having sum(Fmoney) >=ALL

ShortLoop: computing loops in a shortest homology basis(O. Busaryev, T. Dey, J. Sun, Y. Wang)

         ( Selectsum(Fmoney) from FP group by Yno )

Coordinate Free Geometric Programming(S. Mann, N. Litke, T. DeRose)

10、查询所有业务员的编号、姓名、薪俸以及薪资比她高的其他业务员的平均薪酬。

Calculation of Mappings between one and n-dimensional values using hilbert space-filling curve(J. K. Lawder)

    选取自连接

A Fast and Robust GJK Implementation for Collision Detection of Convex Objects(G. van den Bergen)

    SelectY1.Yno,Y1.Yname,Y1.Salary,avg(Y2.Salary)

Fast Distance Field and Generalized Voronoi Diagram Computation using Graphics Hardware(Authors)

    from   YWY Y1, YWY Y2

Efficient Collision Detection of Complex Deformable Models using AABB Trees(G. van den Bergen)

    where  Y1.Salary < Y2.Salary

M.E.S.H. : Measuring Error between Surfaces using the Hausdorff distance(N. Aspert, D. Santa-Cruz, T. Ebrahimi)

    group by  Y1.Yno  

HandleTunnel: computing handle and tunnel loops on surfaces(T. Dey, K. Li, J. Sun)

 

tunicate: robust computational geometry predicates in floating point(R. Bridson)

四、

Intersections and Distances

 1、寻觅每一种班级的班级代码、学生人数、平均战表。

Fast 3D Line Segment—Triangle Intersection Test(N. Chirkov)

    SelectBJDM,count(*),avg(CJ) from SC group by BJDM

An Efficient Ray-Quadrilateral Intersection Test(A. Lagae, P Dutre)

 2、寻找各样学员的班级代码、学生姓名、考试科目数、总成绩。

On Faster Sphere-Box Overlap Testing(T. Larsson, T. Akenine-Moller, E. Lengyel)

    SelectBJDM,XSXM,count(*),sum(CJ) from SC

Fast Tetrahedron-Tetrahedron Overlap Algorithm(F. Ganovelli, F. Ponchio, C. Rocchini)

    group byBJDM,BNXH,XSXM

Ray Bilinear Patch Intersections(S. Ramsey, C. Hansen, K. Potter)

 3、出口一张表格,每位学生对应一条记下,富含字段:

GPU-Based Tiled Ray Casting using Depth Peeling(F. Bernadon, C. Pagot, J. Comba, C. Silva)

          班级代码、学生姓名、语文成绩、数学成就、外语战表。

An Efficient and Robust ray-box intersection algorithm(A. Williams, S. Barrus, R. Morley, P. Shirley)

    SelectSC1.BJDM,SC1.XSXM,SC1.CJ,SC2.CJ,SC3.CJ

A Fast Triangle-Triangle Intersection Test(T. Moller)

    from  SC SC1, SC SC2, SC SC3

Fast and Robust Triangle-Triangle Overlap Test using Orientation Predicates(P. Guigue, O. Devillers)

    whereSC1.BJDM=SC2.BJDM and SC1.BNXH=SC2.BNXH and

A Fast Triangle-Triangle Overlap Test Using Signed Distances(H. Shen, Z. Tang)

         SC2.BJDM=SC3.BJDM and SC2.BNXH=SC3.BNXH and

Fast Ray-Axis Aligned Bounding Box Overlap Tests with Plücker Coordinates(J. Mahovsky, B. Wyvill)

          SC1.KM='语文' and SC2.KM='数学' and SC3.KM='外语'

Fast Ray/Axis-Aligned Bounding Box Overlap Tests using Ray Slopes(M. Eisemann, M. Magnor, T. Grotsch, S. Muller)

 4、输出一张表格,有实际业绩低于60分的各位学生对应一条记下,包含字段:

Fast, Minimum Storage Ray-Triangle Intersection(T. Moller, B. Trumbore)

          班级代码、学生姓名、最低战表。

Lightweight Bounding Volumes for Ray Tracing(D. Cline, K. Steele, P. Egbert)

    SelectBJDM,XSXM,min(CJ) from SC

Fast and Accurate Circle-Circle and Circle-Line 3D Distance Computation(D. Vranek)

    where  CJ<60 group by BJDM,BNXH,XSXM

A Shaft Culling Tool(E. Haines)

    或:  SelectBJDM,XSXM,min(CJ) from SC

Fast 3D Triangle-Box Overlap Testing(T. Akenine-Moller)

          group byBJDM,BNXH,XSXM

Intersection Test for Collision Detection in Particle Systems(E.-A. Karabassi, G. Papaioannou, T. Theoharis, A. Boehm)

          havingmin(CJ)<60

Techniques for Interactive Ray Tracing of Bézier Surfaces(C. Benthin, I. Wald, P. Slusallek)

 5、输出一张表格,有成绩低于60分的每位学生对应一条记下,包罗字段:

Photorealistic Rendering

          班级代码、学生姓名、最高成绩、平均成绩。

TAGL: software rasterizer(B. Levy)

    SelectBJDM,XSXM,max(CJ) from SC

ShadeVis: compute per-vertex ambient occlusion term(Authors)

    group byBJDM,BNXH,XSXM

MeshLab(vertex/face ambient occlusion) (Authors)

    havingmin(CJ)<60

LumosQuad(2D lighting simulation and rendering, quadtree conjugate gradient Poisson solver) (T. Kim, M. Lin)

    请思索下列做法是或不是科学:

OpenEXR(read/write/view OpenEXR HDR images)

          SelectBJDM,XSXM,max(CJ),avg(CJ) from SC

bv(BRDF browser, many analytic and empirical BRDF included) (S. Rusinkiewicz)

         where  CJ<60 group byBJDM,BNXH,XSXM

LightPack(light field authoring and rendering)

 6、输出一张表格,全体成绩都不低于60分的每人学生对应一条记下,包括字段:

aperture(light-field viewer)

          班级代码、学生姓名、平均成绩。

renderman shader to dump out grids(M. Pharr)

    SelectBJDM,XSXM,avg(CJ) from SC

skin shader(M. Pharr)

    group by BJDM,BNXH,XSXM

raytracing quaternion julia sets on the GPU(K. Crane)

    havingmin(CJ)>=60

radiosity renderers(progressive and wavelet radiosity, face cluster radiosity) (A. Willmott, P. Heckbert)

 7、输出一张表格,每一人学生对应一条记下,包涵字段:

Radiator radiosity system(matrix, progressive, wavelet, clustering) (A. Willmott)

          班级代码、学生姓名、去掉二个低于分后的平分成绩。

SunSky sun/sky model code(A. Willmott)

    SelectBJDM,XSXM,(sum(CJ)-min(CJ))/(count(*)-1) from SC

brdfview BRDF viewer(P. Heckbert)

    group byBJDM,BNXH,XSXM

SkyWorksreal-time cloud rendering (M. Harris)

 8、输出一张表格,每门科目对应一条记下,蕴含字段:

SHLIGHT: spherical harmonic lighting program (T. Huang, I. Lisle)

          科目、去掉两个最低分后的平均成绩。

glSoftShadowOpenGL soft shadows implementation (U. Borgenstam, J. Svensson)

    Select KM,(sum(CJ)-min(CJ))/(count(*)-1)from SC

Triangle Mesh Tangent Space Calculation(M. Mittring, Crytek)

    group by KM

MiniLight minimal global illumination renderer(monte-carlo path tracing, progressive refinement, Ward linear tone mapping, octree spatial index) (H. Ainsworth)

 

P3 ToneMapper(openEXR,RGBE in, PNG, PPM out, Ward tone mapping) (H. Ainsworth)

 

Perceptuumglobal illumination renderer (monte carlo ray tracing, motion blur, DOF, glossy scattering, generalized BRDFS, progressive refinement, photon mapping) (H. Ainsworth)

 

raytracing, path tracing, and distance-field rendering with CUDA(mxadd)

        实验携带中“八 SQL查询语句” 的答案

Volumetric Lighting and Shadowing NV32 Shader(S. Lefebvre, S. Guy)

 

Computing a View Frustum to Maximize an Object’s Image Area(K-L. Low, A. Ilie)

 1、查询年龄在19至贰14虚岁以内的女子的学号,姓名,年龄,按年龄从大到小排列。

BTFShop: Interactive Editing and Modeling of Bidirectional Texture Functions(J. Kautz, S. Boulos, F. Durand)

    Selectsno,sname,sage from student

Efficient Splatting Using Modern Graphics Hardware(D. Xue, R. Crawfis)

    where sagebetween 19 and 21 and ssex='女'

Parameter Estimation for Photographic Tone Reproduction(E. Reinhard)

    order by sagedesc

Textured Shadow Volumes(J. Hasselgren, T. Akenine-Moller)

 2、查询姓名中有“明”字的学习者人数。

Hardware-Accelerated Point Generation and Rendering of Point-Based Impostors(J. Baerentzen)

    Select count(*)from student

Reducing Noise in Image-Space Caustics with Variable-Sized Splatting(C. Wyman, C. Dachsbacher)

    where snamelike "%明%"

Robust Soft Shadow Mapping with Backprojection and Depth Peeling(L. Bavoil, S. Callahan, C. Silva)

 3、查询1001学科没有战表的上学的小孩子的学号。

Visibility Cuts shadows for Precomputed Radiance Transfer(also Ashikhmin-Shirley shader) (F. Hecht)

    Select sno fromsc where cno='1001' and grade is null

Frequency Analysis and Sheared Reconstruction for Rendering Motion Blur(K. Egan, Y.-T. Tseng, N. Holzschuch, F. Durand, R. Ramamoorthi)

 4、查询JSJ、SX、WL系的学员学号,姓名,结果按系及学号排列。

PCA-Based Relighting Framework(S. Enrique, R. Ramamoorthi)

    Selectsno,sname,sdept from student

Layered 3D: Tomographic Image Synthesis for Attenuation-based Light Field and High Dynamic Range Displays(G. Wetzstein, D. Lanman, W. Heidrich, R. Raskar)

    where sdept in( 'JSJ', 'SX', 'WL' )

Spark: Modular, Composable Shaders for Graphics Hardware(T. Foley, P. Hanrahan)

    order bysdept,sno

BRDF Displacement Interpolation Using Lagrangian Mass Transport(function/distribution interpolation, non-euclidean manifolds, BRDF/color/value/envmap/stippling, non-negative least squares) (N. Bonneel, M. van de Panne, S. Paris, W. Heidrich)

 5、总结每一门课的总分、平均分,最高分、最低分。

NonPhotorealistic Rendering

    Selectcno,sum(grade),avg(grade),max(grade),min(grade)

rtsc: suggestive contours/highlights, principal hilights, apparent ridges, etc(D. DeCarlo, A. Finkelstein, S. Rusinkiewicz, A. Santella, M. Burns, J. Klawe)

    from sc

fast and robust detection of crest lines on meshes(mesh denoising, mesh curvature) (S. Yoshizawa)

    group by cno

xshade: exaggerated shading rendering(S. Rusinkiewicz, M. Burns, D. DeCarlo)

 6、查询平均分超过90分的男学生学号及平均分。

jot: NPR library

    连接:

Weighted Voronoi Stippling(A. Secord)

    selectsc.sno,avg(grade) from student,sc

dpix: interactive rendering of 3D models using stylized lines(sketchup, self-similar line artmaps, coherent self-similar line styalization, high-quality line visibility, stylized focus)

    wherestudent.sno=sc.sno and ssex=’男’

Visualization

    group by sc.sno

Visualizing LIDAR in Google Earth(M. Isenburg, J. Shewchuck)

    havingavg(grade)>90

farthest-point seeding for placement of streamlines in 2D vector fields with CGAL(A. Mebarki)

    嵌套:

VolPack(fast volume rendering with shear/warp factorization) (P. Lacroute)

    selectsno,avg(grade) from sc

Simulation / Animation

    where sno in (select sno from student where ssex='男')

Inverse Kinematics for Serial Manipulators(D. Manocha, Y. Zhu)

    group by sno

El Topo(robust surface tracking with triangle meshes) (T. Brochu, R. Bridson)

    havingavg(grade)>90

CMU .ASF/.AMC/.V tools(J. McCann, C. Twigg, J. Barbic, ...?)

 7、查询选修课程超过2门的学生姓名。

Skinning Mesh Animations Demo(C. Twigg)

    select snamefrom student,sc

DANCE(physics-based character animation and simulation) (A. Shapiro, V. Ng-Thow-Hing, P. Faloutsos)

    where student.sno=sc.sno

Physically Based Simulation and Animation of Gaseous Phenomena in a Periodic Domain[Page] (A. Nealen)

    group bysc.sno,sname

2D Boiling Demo(CML coupled map atlas on GPU) (M. Harris, G. Coombe, T. Sheuermann, A. Lastra)

    havingcount(*)>2

Flo real-time 2D fluid simulation demo(Cg navier-stokes incompressible flow fixed no-slip boundaries) (G. James, M. Harris)

    本题也足以用嵌套做

Disease reaction diffuse demo(Cg, on surface) (G. James, M. Harris)

 8、查询 JSJ 系的学生选修的课程号。

GPU Ocean Surface Simulation[Page] (S. Lefebvre)

    Select distinctcno from student,sc

Practical Parameterization of Rotations Using the Exponential Map(F. Grassia)

    where  student.sno=sc.sno and sdept='JSJ'

A Simple Fluid Solver Based on the FFT(J. Stam)

    本题也得以用嵌套做

Computing the Moment of Inertia of a Solid Defined by a Triangle Mesh(M. Kallay)

 9、查询选修1002学科的上学的儿童的上学的儿童姓名(用一连和嵌套2种格局)

2D Polygon Moment of Intertia(C. Batty)

    连接:Select sname from student,sc

Fast and Accurate Computation of Polyhedral Mass Properties(B. Mirtich)

          wherestudent.sno=sc.sno and cno='1002'

Fast and Easy Reach-Cone Joint Limits(J. Wilhelms, A. Van Gelder)

    嵌套:Select sname from student where sno in

Asynchronous Contact Mechanics(D. Harmon, E. Vouga, B. Smith, R. Tamstorf, E. Grinspun)

              (select sno from sc where cno='1002' )

Generalized Biped Walking Control(S. Coros, P. Beaudoin, M. van de Panne)

10、查询学生姓名以及她选修课程的课程号及成绩。

Locomotion Skills for Simulated Quadrupeds(S. Coros, A. Karpathy, B. Jones, L. Reveret, M. van de Panne)

    Selectsname,cno,grade from student,sc

StVK: FEM Saint Venant-Kirchhoff deformable object library(J. Barbic)

    wherestudent.sno=sc.sno

Mass Spring System: A general 3D mass-spring system(J. Barbic)

    思索本题也足以用嵌套做呢?

Implicit Newmark and Central Differences Integratorsfor large deformation nonlinear FEM elasticity (J. Barbic)

11、查询选修“数据库原理”课且成绩 80 以上的学员姓名(用一连和嵌套2种艺术)

Large Modal Deformation Factory: Model reduction of StVK FEM deformable models (linear modes, arbitrary free boundary conditions, modal derivatives, mass and stiffness matrices, triangle mesh to cube volume mesh conversion) (J. Barbic)

    连接:Select sname from student,sc,course

harmonic oscillator: exact timestepping of 1D ODE (J. Barbic)

          wherestudent.sno=sc.sno and sc.cno=course.cno and

LQR: linear-quadratic regulator controller(solves Riccati differential equation) (J. Barbic)

               cname='数据库原理' and grade>80

Dynamics of a single rigid body(explicit Euler, symplectic Euler, time-varying external forces and torques, arbitrary inertia tensors) (J. Barbic)

    嵌套:Select sname from student where sno in 

A Level-set Method for Skinning Animated Particle Data(thin-plate energy, grid mean curvature/laplacian/biharmonic, kdtree, marching tetrahedra) (H. Bhattacharya, Y. Gao, A. Bargteil)

               (select sno from sc where grade>80 and cno in 

Sensitive Couture for Interactive Garment Editing and Modeling[2] (N. Umetani, D. Kaufman, T. Igarashi, E. Grinspun)

                    ( select cno from course where cname='数据库原理' )

DelFEM(FEM, Poisson, Diffusion, Advection-Diffusion, Linear Solid, St.Venant-Kirchhoff Material, incompressive hyperelastic material, Stokes Fluid, Navier-Stokes Fluid, Helmholtz, 3D rigid body, 6 types of constraint model, coupling analysis of rigid and elastic material, ILU (0) preconditioned CG, ILU (0) preconditioned BiCGSTAB) (N. Umetani)

               )

DT-Grid: data structure for high-resolution level sets(volume-conserving mean curvature flow, shape metamorphosis, advection, one-sided, second-order central, WENO finite difference) (M. Nielsen, K. Museth)

 

Exact CCD: Geometrically exact continuous collision detection for triangle meshes. (T. Brochu, E. Edwards, R. Bridson)

14、查询未有选修1002学科的学习者的学习者姓名。

Fluid3D: barebones 3D inviscid free-surface fluid simulator in irregular domains (C. Batty)

    Select snamefrom student

VariationalViscosity2D: 2D viscous free surface simulation (C. Batty)

    where sno notin ( select sno from sc where cno='1002')

free-surface 2D liquid solver with irregular static obstacles(C. Batty, F. Bertails, R. Bridson)

    或: select sname from student

Quadratic extrapolation of data in the normal direction(eg to extrapolate velocities from fluid to solid region) (C. Batty)

         where notexists

Texturing

              (select * from sc where cno='1002' and sno=student.sno)

FlexiStickers: Photogrammetric Texture Mapping using Casual Images(Y. Tzur, A. Tal)

    思索本题也得以用一般的连天做吧?

Texture filtering(bilinear, mipmap, elliptic-weighted average) [link to .h/.cpp at bottom of page] (M. Pharr)

15、查询平均分最高的学员学号及平均分。

3D Cellular Noise function(M. Pharr)

    Selectsno,avg(grade)

Jump Map Texture Synthesis(S. Zelinka)

    from sc

Hybrid Texture Synthesis[Page] (A. Nealen, M. Alexa)

    group by sno

Octree Textures on the GPU(S. Lefebvre)

    havingavg(grade) >=ALL ( Select avg(grade)

Parallel Controllable Texture Synthesis Tutorial(S. Lefebvre)

                              from sc

Image Quilting(T. Jones)

                              group by sno

Floating Textures(M. Eisemann)

                            )

Advanced Reaction-Diffusion Models for Texture Synthesis(A. Sanderson, R. Kirby, C. Johnson, L. Yang)

16、查询每门科目成绩都超越该门课程平均分的学员学号。

HWB - A More Intuitive Hue-Based Color Model(A. R. Smith, E. R. Lyons)

    能够先总计每门课程平均分

Efficient GPU-Based Texture Interpolation using Uniform B-Splines(D. Ruijters, B. ter Haar Romney, P. Suetens)

    create viewc_avg(cno,avg_grade)

Texture Stitching by Estimating the Laplace-Beltrami Operator by Restricting 3D Functions(M. Kazhdan)

         as selectcno,avg(grade) from sc group by cno

Filtering Solid Gabor Noise(A. Lagae, G. Drettakis)

    再查询

Repetition Maximization based Texture Rectification(D. Aiger, D. Cohen-Or, N. Mitra)

    Select distinctsno from sc

2D

    where sno notin ( Select sno from sc,c_avg

real-time gradient-domain image painting(multigrid GPU poisson solver) (J. McCann, N. Pollard)

                      where sc.cno=c_avg.cno and grade<avg_grade

mean-value coordinate image cloning(Z. Farbman, G. Hoffer, Y. Lipman, D. Cohen-Or, D. Lischinski)

                    )

GraphDraw: draw 2D graphs and export as postscript (K. Crane)

   ===========================================

Interactive Segmentation Tool-Box(lazy snapping, GrabCut interactive graph cut)

    SELECT DISTINCT Sno

3D Photo Pop-Up(D. Hoeim)

    FROM SC SC1

Anti-Grain Geometry Project(high-quality and efficient 2D software rasterizer: subpixel antialiasing, arbitrary polygons, gradients and gouraud shading, image filtering, bilinear, bicubic, spline16, spline32, sinc, blackman, strokes with joins and caps, dashed lines, head/tail markers, arrows, polygon clipping, alpha-masking, arbitrary images as line patterns, perspective and bilinear transformations, boolean polygon operations) (M. Shemanarev)

    WHERE SC1.SnoNOT IN

High Quality Hardware Line Antialiasing(R. Nelson)

          ( SELECT SC2.Sno

Color Compatibility from Large Datasets(P. O'Donovan, A. Agarwala, A. Hertzmann)

            FROM SC SC2

2.5D Cartoon Models(A. Rivers, T. Igarashi, F. Durand)

            WHERE SC2.Grade <=

GradientShop: A Gradient-Domain Optimization Framework for Image and Video Filtering(P. Bhat, L. Zitnick)

                 ( SELECT AVG(SC3.Grade)

Image and Video Processing

                   FROM SC SC3

Adaptive Thresholding Using the Integral Image[Also](https://link.jianshu.com?t=http://jgt.akpeters.com/papers/BradleyRoth07/)

                   WHERE SC3.Cno=SC2.Cno

A One-Pass Version of Two-Pass Image Resampling(N. Max)

                 )

PDE-Based Image Filtering(Nonlinear diffusion, Total-Variation denoising, Shock Filters) (G. Gilboa)

          )

ImageStack: a command line calculator for images(HDR image generation, bilateral/median/circular filter, dimensionality reduction, FFT, Poission, wavelet transforms, inpainting, andmany more) (A. Adams)

    或:

Fast High-Dimensional Filtering Using the Permutohedral Lattice(A. Adams, J. Baek, A. Davis)

    SELECT DISTINCTSno

Local Laplacian Filters: Edge-aware Image Processing with a Laplacian Pyramid(S. Paris, S. Hasinoff, J. Kautz)

    FROM SC SC1

Domain Transform for Edge-Aware Image and Video Processing(E. S. L. Gastal, M. M. Oliveira)

    WHERE NOTEXISTS

Real-time Edge Aware Image Processing with the Bilateral Grid(J. Chen, S. Paris, F. Durand)

          (SELECT *

Bilateral Filter(J. Chen)

            FROM SC SC2

Local Histogram Equalization using Bilateral Grid(J. Chen)

            WHERE SC2.Sno=SC1.Sno AND SC2.Grade <=

Streaming Multigrid for Gradient-Domain Operations on Large Images(M. Kazhdan)

                       (SELECT AVG(SC3.Grade)

Distributed Gradient-Domain Processing of Planar and Spherical Images(M. Kazhdan)

                         FROM SC SC3

Metric Aware Processing of Spherical Imagery(M. Kazhdan)

                         WHERE SC3.Cno=SC2.Cno

An Image Inpainting Technique Based on the Fast Marching Method(A. Telea)

                       )

MIRT: Medical Image Registration Toolbox(similarity, mutual information MI, residual complexity RC, sum square differences SSD, sum absolute differences SAD, correlation coefficient CC, CD2, MS, transformation models, parametric free form deformation FFD, non-parametric variational, implicit euler, curvature-based regularization, multiresolution) (A. Myronenko)

          )

FreeSurfer: reconstruction of the brain’s cortical surface from structural MRI data and overlay of functional MRI data (wiki) (contributors)

   

DotCount: count the number of dots in an image (M. Reuter)

 

FDim: compute fractal dimension of grayscale image (Capacity Dimension, Information Dimension, Correlation Dimension, Probability Dimension) (M. Reuter)

(3)检索至少选修LIU老师所授课程中一门课程的女上学的小孩子姓名。
    SELECT SNAME
    FROM S
    WHERE SEX=‘F’ AND S# IN
     (SELECT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=‘LIU’)

Robust Registration and Template Estimation of 3D MRI Images(unbiased inverse consistent registration, modality rigid registration, outlier detection robust statistics, symmetry, global intensity scaling, automatic sensitivity parameter estyimation, unbiased half-way space) (M. Reuter)

NOTICE:有两种写法,比方联接查询写法:
    SELECT SNAME
    FROM S,SC,C
    WHERE SEX=‘F’ AND SC.S#=S.S#
    AND SC.C#=C.C#
    AND TEACHER='LIU'
但上一种写法更加好一些。

PatchMatch: A Randomized Correspondence Algorithm for Structural Image Editing(C. Barnes, E. Schectman, A. Finkelstein, B. Goldman)

(4)检索WANG同学不学的学科的课程号。
    SELECT C#
    FROM C
    WHERE C# NOT IN
     (SELECT C#
     FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME='WANG'))

The Generalized PatchMatch Correspondence Algorithm(C. Barnes, E. Schectman, A. Finkelstein, B. Goldman)

(5)检索至少选修两门课程的学习者学号。
    SELECT DISTINCT X.SNO
    FROM SC X,SC Y
    WHERE X.SNO=Y.SNO AND X.CNO<>Y.CNO
Notice:对表SC进行自连接,X,Y是SC的四个外号。

Emerging Images Demo(N. Mitra, H.-K. Chu, T.-Y. Lee, L. Wolf, H. Yeshurun, D. Cohen-Or)

(6)招来全部上学的小孩子都选修的科目的科目号与学科名。
    SELECT C#,CNAME
    FROM C
    WHERE NOT EXISTS
     (SELECT *
     FROM S
     WHERE S# NOT IN
      (SELECT *
      FROM SC
      WHERE  SC.C#=C.C#))

GPU_KLT: A GPU-based Implementation of the Kanade-Lucas-Tomasi Feature Tracker(S. Sinha)

要从语义上分解:(1)选拔课程的科目号与学科名,一纸空文不选那门课的同桌。
   当中,“不选那门课的同窗”能够代表为:

Gain-Adaptive KLT Tracking and TV-L1 optical flow on the GPU(C. Zach)

SELECT *

 FROM  S

 WHERE  S# NOT IN

  (SELECT  *

      FROM SC  

      WHERE  SC.C#=C.C#)

 

或者

SELECT *

 FROM  S

 WHERE   NOT EXISTS

  (SELECT  *

      FROM SC  

      WHERE S.S#=C.S# AND 

SC.C#=C.C# )

 

SSBA: Simple Sparse Bundle Adjustment(C. Zach)

      

Bundler: Structure from Motion (SfM) for Unordered Image Collections(N. Snavely)

(7)探究选修课程富含LIU先生所教学的学员学号。    
     SELECT DISTINCT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER='LIU'))   

sba : A Generic Sparse Bundle Adjustment C/C Package Based on the Levenberg-Marquardt Algorithm(M. Lourakis)

3.3 设有八个核心表Highlander(A,B,C)和S(D,E,F),试用SQL查询语句表述下列关系代数表明式:
  (1)πA(R)(2)σB='17'(R)(3)R×S(4))πA,FC=D(R×S))
(1)SELECT A FROM R
(2)SELECT * FROM R WHERE B='17'
(3)SELECT A,B,C,D,E,F FROM R,S
(4)SELECT A,F FROM R,S WHERE R.C=S.D

homest: A C/C Library for Robust, Non-linear Homography Estimation(M. Lourakis)

3.43.4 设有多少个核心表路虎极光(A,B,C)和S(A,B,C)试用SQL查询语句表述下列关系代数表达式:
  (1)R∪S  (2)R∩S  (3)R-S  (4)πA,B(R)πB,C(S)

CMVS: Clustering Views for Multi-view Stereo(Y. Furukawa)

    (1)SELECT A,B,C
      FROM R
      UNION
      SELECT A,B,C
      FROM S

PMVS: Patch-based Multi-view Stereo Software(Y. Furukawa, J. Ponce)

    (2)SELECT A,B,C
      FROM R
      INTERSECT
      SELECT A,B,C
      FROM S

VisualSFM: A Visual Structure from Motion System(C. Wu)

    (3)SELECT A,B,C
      FROM R
      WHERE NOT EXISTS
       (SELECT A,B,C
       FROM S
       WHERE R.A=S.A AND R.B=S.B AND R.C=S.C)

SiftGPU: A GPU Implementation of Scale Invariant Feature Transform (SIFT)(C. Wu)

    (4)SELECT R.A,R.B,S.C
      FROM R,S
      WHERE R.B=S.B

Global Contrast based Salient Region Detection(M-M. Cheng, G-X. Zhang, N. Mitra, X. Huang, S-M. Hu)

3.5 试汇报SQL语言的关系代数特点和元组演算特点。
(P61-62)

Robust Image Retargeting via Axis-Aligned Deformation(D. Panozzo, O. Weber, O. Sorkine)

3.6 试用SQL查询语句表述下列对教学数据库中四个着力表S、SC、C的询问:

imageblending(Poisson Image Editing) (Authors)

(1)总计有上学的小孩子选修的课程门数。
    SELECT COUNT(DISTINCT C#) FROM SC

Image-Based Localization using Structure-from-Motion Models(T. Sattler, B. Leibe, L. Kobbelt)

(2)求选修C4科指标学员的平均年龄。
    SELECT AVG(AGE)
    FROM S
    WHERE S# IN
     (SELECT S#
     FROM SC
     WHERE C#='C4')
或者,
    SELECT AVG(AGE)
    FROM S,SC
    WHERE S.S#=SC.S# AND C#='004'

Sampling

(3)求LIU老师所授课程的每门课程的学平生均成绩。
   SELECT CNAME,AVG(GRADE)
   FROM SC ,C
   WHERE SC.C#=C.C# ANDTEACHER='LIU'
   GROUP BY C#   

Efficient Generation of Poisson-Disk Sampling Patterns(T. Jones)

(4)总计每门课程的学生选修人数(超过10人的科目才总括)。需要输出课程号和选修人数,查询结果按人头降序排列,若人数同样,按学科号升序排列。
    SELECT DISTINCT C#,COUNT(S#)
    FROM SC
    GROUP BY C#
    HAVING COUNT(S#)>10
    ORDER BY 2 DESC, C# ASC

Antimony(poisson-disk sample generation) (D. Dunbar, G. Humphreys)

(5)检索学号比WANG同学大,而年纪比他小的学生姓名。
    SELECT X.SNAME
    FROM S AS X, S AS Y
    WHERE Y.SNAME='WANG' AND X.S#>Y.S# AND X.AGE<Y.AGE

2D Poisson disk sampling with dart throwing(M. Pharr)

(6)检索姓名以WANG打头的兼具学员的人名和年龄。
    SELECT SNAME,AGE
    FROM S
    WHERE SNAME LIKE 'WANG%'

Random or Uniform points on a sphere[Page] (J. O'Rourke)

(7)在SC中搜索战表为空值的上学的小孩子学号和课程号。
    SELECT S#,C#
    FROM SC
    WHERE GRADE IS NULL

Sampling with Hammersley and Halton Points(T.-T. Wong, P.-A. Heng, W.-S. Luk)

(8)求年龄大于女子高校友平均年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX='男' AND X.AGE>(SELECT AVG(AGE)FROM S AS Y WHERE Y.SEX='女')

A Low Distortion Map Between Disk and Square(P. Shirley, K. Chiu)

(9)求年龄超越全部女子高校友年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX='男' AND X.AGE>ALL (SELECT AGE FROM S AS Y WHERE Y.SEX='女')
除法运算

2D Fast Object Distribution(Halton sequence computation) [Page] (A. Willmott)

3.7 试用SQL更新语句表明对教学数据库中多少个大旨表S、SC、C的逐个更新操作:
(1)往基本表S中插入八个学生元组(‘S9’,‘WU’,18)。
    INSERT INTO S(S#,SNAME,AGE) VALUES('59','WU',18)
(2)在中央表S中搜寻每一门学科成绩都超越等于80分的学员学号、姓名和性别,并把检索到的值送往另叁个已存在的基本表STUDENT(S#,SANME,SEX)。
    INSERT INTO STUDENT(S#,SNAME,SEX)
     SELECT S#,SNAME,SEX
     FROM S WHERE  NOT EXISTS
      (SELECT * FROM SC WHERE
       GRADE<80 AND S.S#=SC.S#)

Generalized Stratified Sampling Using the Hilbert Curve(M. Steigleder, M. McCool)

(3)在主旨表SC中去除尚无成绩的选课元组。
    DELETE FROM SC
     WHERE GRADE IS NULL

A Stratified Sampling Technique for an Ellipse(C.-M. Wang, N.-C. Hwang)

(4)把WANG同学的学习选课和作育全体刨除。
    DELETE FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME='WANG')

2D Hilbert Curve Generator(also precomputed C arrays for orders 1-10) (J. Chen)

(5)把选修MATHS课不如格的大成全改为空值。
    UPDATE SC
    SET GRADE=NULL
    WHERE GRADE<60 AND C# IN
      (SELECT C#
      FROM C
      WHERE CNAME='MATHS')

Accurate Multi-dimensional Poisson-disc Sampling(M. Gamito, S. Maddock)

(6)把低于总平均战绩的女子学校友成绩升高5%。
    UPDATE SC
    SET GRADE=GRADE*1.05
    WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S# FROM SWHERE SEX='F')

interpolate3d: A Natural Neighbour Interpolation program for 3D data(R. Hemsley)

(7)在主导表SC中期维修改C4课程的大成,若战绩小于等于75分时提升5%,若战绩当先75分时提升4%(用三个UPDATE语句达成)。
    UPDATE SC
     SET GRADE=GRADE*1.05
     WHERE C#='C4' AND GRADE<=75
    UPDATE SC
     SET GRADE=GRADE*1.04
     WHERE C#='C4' AND GRADE>75

Blue Noise through Optimal Transport(lloyd iteration, voronoi tessellation, arbitrary density functions) (F. de Goes, K. Breeden, V. Ostromoukhov, M. Desbrun)

3.8 在第1章例1.4中涉嫌“客栈管理”关系模型有四个涉及方式:
  零件 PART(P#,PNAME,COLOR,WEIGHT)
  项目 PROJECT(J#,JNAME,DATE)
  供应商 SUPPLIER(S#,SNAME,SADDR)
  供应 P_P(J#,P#,TOTOAL)
  采购 P_S(P#,S#,QUANTITY)

Capacity-Constrained Delaunay Triangulation for Point Distributions(Y. Xu, L. Liu, C. Gotsman, S. Gortler)

(1)试用SQLDDL语句定义上述七个基本表,并表明主键和外键。
    CREATE TABLE PART
    (P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,
    COLOR CHAR(10),WEIGHT REAL,
    PRIMARY KEY(P#))
    
    CREATE TABLE PROJECT
    (J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,
    DATE DATE,
    PRIMARY KEY(J#))
    
    CREATE TABLE SUPLIER
    (S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),
    PRIMARY KEY(S#))
    
    CREATE TABLE P_P
    (J# CHAR(4),P# CHAR(4),TOTAL INTEGER,
    PRIMARY KEY(J#,P#),
    FOREIGN KEY(J#) REFERENCE PROJECT(J#),
    FOREIGN KEY(P#) REFERENCE PART(P#))
    
    CREATE TABLE P_S
    (P# CHAR(4),S# CHAR(4),QUANTITY INTEGER,
    PRIMARY KEY(P#,S#),
    FOREIGN KEY(P#) REFERENCE PART(P#),
    FOREIGN KEY(S#) REFERENCE SUPLIER(S#))     

Data Structures and Compression

(2)试将PROGECT、P_P、PART八个基本表的本来联接定义为一个视图VIEW1,PART、P_S、SUPPLIE奥迪Q3多少个基本表的当然联接定义为多少个视图VIEW2。
    CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)
          AS SELECT PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL
          FROM PROJECT,PART,P_P
          WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J# 
     
    CREATE VIEW VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)
          AS SELECT PART.P#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITY
          FROM PART,P_S,SUPPLIER
          WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S#

Efficient Normal Compression in CryENGINE 3 [PPTX] [PDF] (texture and code in slides) [Page] (A. Kaplanyan)

(3)试在上述多少个视图的基础上开展数据查询:

Triangle Strip Compression(M. Isenburg)

    1)检索巴黎的供应商所供应的机件的号子和名字。
    SELECT P#,PNAME FROM VIEW2 WHERE SADDR='SHANGHAI'

Out-of-Core Compression for Gigantic Polygon Meshes(M. Isenburg, S. Gumhold)

    2)检索项目J4所用零件的承包商编号和名字。
    SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1 WHERE J#='J4')

Streaming Compression of Triangle Meshes(M. Isenburg, P. Lindstrom, J. Snoeyink)

3.9 对于教学数据库中基本表SC,已建构下列视图:
CREATEVIEWS_GRADE(S#,C_NUM,AVG_GRADE)
ASSELECTS#,COUNT(C#),AVG(GRADE)
FROMSC
GROUPBYS#
试推断下列查询和换代是不是同意实施。若允许,写出转换成主题表SC上的照管操作。
  (1) SELECT*
FROMS_GRADE
      允许
   SELECT S#,COUNT(C#),AVG(GRADE)FROM SC GROUP BY S#

Streaming Compression of Tetrahedral Volume Meshes(M. Isenburg, P. Lindstrom, S. Gumhold, J. Shewchuck)

  (2) SELECTS#,C_NUM
FROMS_GRADE
WHEREAVG_GRADE>80
    允许
   SELECT S#,COUNT(C#) FROM SC WHEREAVG(GRADE)>80

Streaming Compression of Delaunay Triangulations(M. Isenburg, Y. Liu, J. Shewchuck, J. Snoeyink)

  (3) SELECTS#,AVG_GRADE
FROMS_GRADE
WHEREC_NUM>(SELECTC_NUM
FROMS_GRADE
WHERES#=‘S4’)
    允许
   SELECT S#,AVG(GRADE)
   FROM SC AS X
   WHERE COUNT(X.C#)>(SELECTCOUNT(Y.C#) FROM SC AS Y WHERE Y.S#='S4')
   GROUP BY S#

LAStools: converting, viewing, and compressing LIDAR data in LAS format(M. Isenburg, J. Shewchuck)

  (4) UPDATES_GRADE
SETC_NUM=C_NUM+1
WHERES#=‘S4’
    不允许

Streaming Meshes(M. Isenburg, P. Lindstrom)

  (5) DELETEFROMS_GRADE
WHEREC_NUM>4
    不允许

OpenRACM: Random-Accessible Compressed Meshes(S.E. Yoon, P. Lindstrom)

3.10 预处理方式对于嵌入式SQL的达成有啥首要意义?
    预处理格局是先用预管理程序对源程序开展围观,识别出SQL语句,并拍卖成宿主语言的函数调用形式; 然后再用宿主语言的编写翻译程序把源程序编写翻译成指标程序。那样,不用扩张宿主语言的编写翻译程序, 就能够管理SQL语句。

An Improved Adjacency Data Structure for Fast Triangle Stripping(P. Reuter, J. Behr, M. Alexa)

3.11 在宿主语言的次第中应用SQL语句有何样规定?
在宿主语言的前后相继中运用SLQ语句有以下规定:
(1)在前后相继中要区分SQL语句与宿主语言说话
(2)允许嵌入的SQL语句援引宿主语言的次序变量(称为分享变量),但有两条规定:
   1)引用时,这个变量前必需加“:”作为前缀标志,以示与数据库中变量有分别。
   2)那几个变量由宿主语言的程序定义,并用SQL的DECLARE语句表达。
(3)SQL的联谊管理情势与宿主语言单记录管理情势之间要和煦。 供给利用游标机制,把集结操作调换来单记录管理情势。

Simple and Efficient Traversal Methods for Quadtrees and Octrees(S. Frisken, R. Perry)

3.12SQL的聚合管理格局与宿主语言单记录管理格局之间怎么和谐?
    由于SQL语句管理的是记录集结,而宿主语言语句一回只可以处理一个记下, 由此需求用游标(cousor)机制,把集合操作转换到单记录管理格局。

Octree implementation(H. Ainsworth)

2.13 嵌入式SQL语句曾几何时不必涉及到游标?曾几何时必需涉及到游标?
    (1)INSERT、DELETE、UPDATE语句,查询结果自然是单元组时的SELECT语句, 都可径直嵌入在主程序中采用,不必涉及到游标。
    (2)当SELECT语句询问结果是三个元组时,此时宿主语言程序不能利用, 必定要用游标机制把四个元组壹遍三个地传递给宿主语言管理。

Fast Generation of Pointerless Octree Duals(T. Lewiner, V. Mello, A. Peixoto, S. Pesco, H. Lopes)

 

Numerics and Optimization

 

CNC: CUDA sparse symmetric matrix solver(L. Buatois, G. Caumon, B. Levy)

 

OpenNL: Open Numerical Library(direct/iterative sparse solvers, preconditioners, Least-Squares Conformal Maps (LSCM) implementation) (Authors)

(电商)数据库原理及使用_一成不变考核武器试验题及仿效答案

CGAL(LP linear programming, QP quadratic programming) (Authors)

 

TAUCS[Package Mirror] (sparse preconditioned conjugate-gradient/minres, sparse Cholesky (sym-pos-semi-def), sparse LU, sparse LDLT) (S. Toledo, D. Chen, V. Rotkin)

    一、单选 (每空1分,共10分)

UMFPACK(unsymmetric sparse LU) (T. Davis)

    1.在Access数据库中,数据保存在(    )对象中。

newmat(C dense matrix library [documentation]) (R. Davies)

       A.窗体        B.查询          C.报表          D.表

GPUFFTW(GPU power-of-two FFT library) (N. Govindaraju, D. Manocha)

    2.假若某一字段数据型为文本型,字段大小为8,该字段中最多可输入(    )个汉字。

LUGPU(GPU dense-matrix LU decomposition) (N. Galoppo, N. Govindaraju, M. Henson, D. Manocha)

       A.8          B.4              C.16           D.32

CoMISo: Constrained Mixed Integer Solver(H. Zimmer, D. Bommes, L. Kobbelt)

    3.文本型字段最多能够贮存(    )个字符。

chag:ppCUDA parallel primitives library (reduction, prefix operations (scan), compaction, radix sort)

       A.250        B.252         C.254           D.255

Blossoming softwarefor analyzing polynomial functions (S. Mann, W. Liu)

    4.Access客户操作分界面由(    )部分构成。

Automatic Differentiation, C Templates, and Photogrammetry(D. Piponi)

       A.4           B.5          C.3           D.6

CMA-ES: Covariance Matrix Adaptation - Evolutionary Strategy(non-linear non-convex optimization in complex domains) (N. Hansen)

    5.下列(    )Logo是Access中表对象的申明。

ExpoKit: matrix exponential for small dense and large sparse matrices (R. B. Sidje)

       A.         B.         C.        D.

Modal Matrix: A class for modal matrix operations (assembly u=Uq, projection q = U^T u) (J. Barbic)

    6.在设计Access数据表时,“索引”属性有(    )取值。

Quaternions,MiniVector,Dense Matrix,Sparse Matrix(Conjugate Gradient, Jacobi Preconditioner, Gauss-Seidel ) (J. Barbic)

       A.1              B.2              C.3                D.4

Polar decomposition of a 3x3 matrix and derivatives of polar decomposition matrices(J. Barbic)

    7.Access中饱含有(    )种数据类型。

levmar : Levenberg-Marquardt nonlinear least squares algorithms in C/C (M. Lourakis)

       A.9              B.10             C.7                D.8

sparseLM : Sparse Levenberg-Marquardt nonlinear least squares in C/C (M. Lourakis)

    8.在贰个宏中要开辟八个表格,应该运用的操作是(    )。

HLBFGS(Preconditioned L-BFGS, Preconditioned Conjugate Gradient, Gradient Descent, Newton) (Y. Liu)

       A.OpenForm       B.OpenReport      C.OpenTable       D.OpenQuery

Conjugate Gradient with Modified Incomplete Cholesky (level zero) preconditioning(R. Bridson)

    9.得以经过Internet实行数量发布的对象是(    )。

KKTDirect: a direct solver package for saddle-point ( KKT ) matrices(R. Bridson)

       A.窗体         B.报表           C.查询          D.数据访谈页

MPCG: Multi-Preconditioned Conjugate Gradients(R. Bridson, C. Greif)

   10.模块窗口由(    )个部分组成。

Quern: A Sparse QR Library(R. Bridson)

       A.2            B.3             C.4              D.5

Data Sets

 

Digital 210 King(building scans, Revit models, photographs, Green Building XML (gbXML) models, of 210 King St East (Autodesk Toronto building))

    二、填空 (每空1分,共20分)

Unwrapped (planar-parameterized) ABF /LinABF Meshes(.OBJ format, includes normal maps and decimated meshes)

    1.在人工管理和文件管理阶段,程序设计__依赖于 ___数据表示。

AIM@SHAPE 3D model repository

    2.在文件系统中,存取数据的主干单位为___记录____,在数据库系统中,存取数据的骨干单位为___数据项_____。

DemoGraphite(bunny, horse, tiger-face, octaflower, rocker arm, engine volume, skull volume)

    3.若实体A和B是多对多的关联,实体B和C是1对1的牵连,则实体A和C是___多_____对___多_____的联系。

Georgia Tech Large Geometric Models Archive(Bunny,Turbine Blade,skeleton hand,dragon,buddha,horse,visible man,grand canyon,puget sound,angel)

    4.在二个提到中不允许出现重复的____元组____,也不允许出现具备同样名字的___属性_____。

Stanford 3D Scanning Repository(bunny, drill bit, buddha, dragon, armadillo, lucy, asian dragon, manuscript, thai statue)

    5.数据库系统中的四类客商分别为____数据库管理员、数据库设计员、应用程序员、终端顾客_____。

Stanford Volume Data Archive(CThead, MRBrain, CTbunny)

    6.在存取数据库的数额的长河中,使用了三个数据缓冲区,分别为___系统_____缓冲区和____用户 ____缓冲区。

Stanford Light Field Archive

    7.学生关系中的班级号属性与班级关系中的班级号主码属性相呼应,则____班级号____为学习者关系中的___外码___。

UNC Dynamic Mesh Datasets(per-frame meshes, exploding dragon and bunny, cloth simulation, N-body simulation, falling letters)

    8.设一个涉及A具有a1本性子和a2个元组,关系B具备b1个个性和b2个元组,则提到A´B具备___a1 b1____个属性和____ a2´b2 ____个元组。

Image-Based 3D Models(VRML/TRI formats, multiple resolutions, textured [mesh viewer]) (C. Esteban, F. Schmitt)

    9.设叁个学员关系为S(学生号,姓名),课程关系为C(课程号,课程名),选课关系为X(学生号,课程号,战绩),求出全数选课的学生新闻的运算表明式为_____Õ学生号(X)______与____S ____的自然连接。

Multi-frame 3D scans from video depthmaps(triangulated point clouds, OBJ format,100-2200 frames) (T. Weise, B. Leibe, L. Van Gool)

   10.在二个关系宝马X5中,若存在X→Y和X→Z,则存在_____ X→(Y,Z)_______,称此为函数重视的合併性准绳。

ETH Face Pose Range Image Data Set(10,000 640x480 range image point sets of 20 moving faces, read/write code) (M. Breitenstein, D. Kuettel, T. Weise, L. van Gool, H. Pfister)

 

Quadrangulations(fandisk,beetle,fertility,rockerarm) (H. Zimmer, D. Bommes, L. Kobbelt)

    三、填空 (每空1分,共20分)

Geometry Videos(H. Bricenno)

    1.若贰个关联的另外非主属性都不有的依附依赖于别的候选码,则称该关系达成____第二____范式。

PSU Near-Regular Texture Database(S. Lee, Y. Liu)

    2.在SQL中,列级完整性约束分为__6__种情景,表级完整性约束分为__4__种情况。

CMU Motion Capture Database

   3. 在SQL中,各个视图中的列能够来自不一致的___表___,它是在原有表的底蕴上____建立____的逻辑意义上的新涉及。

Skinning Mesh Animations data(horse, camel, elephant, chicken, snake ) (D. James, C. Twigg)

   4. 在SQL的查询语句中,group by选项完成____分组计算______效果与利益,order by选项达成对结果表的____排序_____功能。

Deformation Transfer for Triangle Meshes(horse, camel, cat, lion, faces, flamingo, elephant) (R. Sumner, J. Popovic)

    5.对此较复杂的系统,概念设计阶段的严重性职务是:首先依照系统的次第部分应用画出各自对应的____局部ER图______,然后再实行归纳和全体规划,画出_____整体ER图_____。

Mesh Ensemble Motion Graphs(D. James, C. Twigg, A. Cove, R. Wang)

    6.机器完成阶段的对象是在Computer种类中拿走四个知足______设计需求、功用完善、操作方便___的数据库应用种类。

Cornell Light Measurement Data(light source spectra, filter and lens transmission spectra, camera response spectra, reflectance data)

    7.Access的客户操作分界面由    标题栏、菜单栏、专门的学问区  、工具栏、状态栏等八个部分组成。

The Cornell Box

    8.Access“表”结构划虚拟计窗口中上半局地的“表设计器”是由      字段名称、数据类型、表达        等三列组成。

RenderPark(HDR, galerkin radiosity, stochastic jacobi radiosity, hierarchical, clustering, view-importance, higher-order approximations, random walk radiosity, photon mapping, stochastic ray tracing, bi-directional path tracing) (P. Bekaert,F. de Laet, P. Peers, V. Masselus, J. Prikryl, P. Dutre, O. Ceulemans)

    9.Access中的窗体由      页眉、主体      和页脚等四个部分构成。

RaVi(real-time raytracer) (M. Granz)

 

WinOSi(iterative two pass optical simulation raytracing) (M. Granz)

    四、填空 (每空1分,共20分)

Scopia.es 3D models(home furnishing objects, buildings, plants, characters, .3ds, .blend, .sh3d, .obj) (C. Folch, Scopia.es)

   1. 设五个关联为CR-V(A,B,C,D,E),它的矮小函数依赖集为FD={A→B,A→C,(C,D)→E},则该关系的候选码为_____(A,D)___,候选码函数决定E是___伪传递___性。

Crytek Sponza Model v2(increased complexity, 3ds, OBJ formats, textures) (F. Meinl, Crytek)

    2.设贰个关系为Murano(A,B,C,D,E),它的细微函数重视集为FD={A→B,A→C,(A,D)→E},该关系只满意___第一_____范式,若要标准化为高一流的范式,则将获得____2____个关系。

TrueMarble earth image dataset(multiple resolutions from 32km to 250m, PNG and geo-referenced GeoTIFF formats) (Unearthed Outdoors LLC)

    3.在骨子里的数据库管理连串中,对数据库的操作方式有_____指令交互、程序实行、窗口分界面______等三种。

Volume-Meshed Models(Heart, Eiffel Tower, Basket, Bridge, Dragon, Horse, Dinosaur, 3D Spring, Beam) (J. Barbic)

    4.在SQL中,主码约束的首要字为____ primary key________,外码约束的首要字为______foreignkey ______。

Volume-Meshed Models(Cube, TFire, Tire, Dragon, Cow, Sculpt, LetterP, House, StayPuft) (B. Klinger, J. Shewchuck)

    5.基本表属于全局情势中的表,它是____实表____,而视图则属于有个别情势中的表,它是____虚表 ____。

McGuire Graphics Data(San Miguel, Crytek Sponza, Dabrovic Sponza, Sibenik Cathedral, Conference Room, Chinese Dragon, Teapot, Cube, Happy Buddha, Hairball, Cornell Box, Rungholt, Lost Empire, Infinite Realities Head, textured OBJs)

   6. 在SQL新版的查询语句中,select选项达成投影运算,from选项实现____连接____运算,where选项实现____选择___运算。

Caltech Mesh Compendium(Ball Joint,Dinosaur Sculpture,Golf Club,Igea Artifact,Isis,Knee,Rocker Arm,Santa,Screwdriver,Teeth Casting,Horse,Skeleton Hand,Turbine Blade,Cactus,Cat,Cessna,Club,Cow,Distributor Cap,Engine,Fandisk,Gargoyle,Hypersheet,Knot,Mechanical Part,Oil Pump,Teapot,Tyrannosaurus Rex,Dragon,Feline Model,Femme,Happy Buddha,Headus Skull,Max Planck,Molecule,Rabbit Sculpture,Stanford Bunny,Zander,Zoë,Duck,Mummy,Bust of Nefertiti,Top,Totem,Vase #1,Vase #2,Chair,Dental Cast,Foot,Hand,female Mannequin Head,Pregnant Woman,kissing-children Sculpture,Shoe,Toilet,Cube,Hexagon,Icosahedron,Octahedron,Pentagon,Quadrilateral,Right Tetrahedron,Right Triangle,Armadillo Man,Dragon,Happy Buddha,Stanford Bunny,Cat Head,Face,Mannequin Head,Spocks Head,Stamped Part, Stegosaurus, Torus, Triceratops, Venus Torso)

    7.数据字典是对系统专业流程中____数据____和____处理____的描述。

Aachen photo dataset(photos, SIFT descriptors, 3D reconstruction) (T. Sattler)

    8.关全面据库系统中的全局情势由若干个基本表所结合,表与表之间的牵连是透过定义的____主码____和____外码____实现的。

MIT CSAIL Textured Models Database()

    9.在饭馆管理中,涉及到的基本表有多个,它们各自为客房表、止宿表、_____客人登记表、费用卡表____。

Vision

   10.在装置或裁撤数据库密码的进度中,密码对于字母     大小写     是乖巧的。

Stanford Calibration Grid Detector(V. Vaish)

 

Color CRT Calibration for perception research(E. Olds, W. Cowan, P. Jolicoeur)

    五、填空 (每空1分,共10分)

ASIFT: A New Framework for Fully Affine Invariant Comparison(J.-M. Morel, G. Yu)

    1.Access的客商操作分界面由   标题栏、菜单栏、职业区   、工具栏、状态栏等多少个部分组成。

SIFT (A. Vedaldi)

    2.Access“表”结构划设想计窗口中上半有的的“表设计器”是由     字段名称、数据类型   和申明等三列组成。

SIFT for Matlab(A. Vedaldi)

    3.Access中的窗体由     页眉、主体、页脚      等两个部分构成。

VLFeat(SIFT, MSER, k-means, hiearchical k-means, agglomerative information bottleneck (aib), quick shift) (A. Vedaldi, B. Fulkerson)

    4.在Access中模块分为   类模块       和       标准模块     三种等级次序。

LFDisplay: a real-time system for light field microscopy

 

FCam: FrankenCameracamera control library(Contributors)

    六、依照主教材第四章所给的商品库和教学库,或许依据下列所给的每条SQL查询语句写出相应的功力,恐怕依照下列所给的各种作用写出相应的SQL查询语句。(每小题4分,共20分)

Utilities for Structured Light Scanning(Contributors)

    在称呼为商品库的数据库中包括有商品表1和货品表2,它们的定义分别为:

face-off: Automatic Facial Feature Detection & Replacement(Authors)

       商品表1(商品代号 char(8),分类名 char(8),单价 float,数量 int)

Google Pose Optimizer: reconstruct pose of sensor based on sensor data (GPS position, doppler velocity, accelerometers, rate gyroscopes, wheel encoders, constraint models) (Authors)

       商品表2(商品代号 char(8),产地 char(6),牌子char(6),)

Machine Learning

    在称呼为教学库的数据库中含有有上学的小孩子、课程和选课四个表,它们的概念分别为:

Infer.Net- Bayesian Inference in Graphical Models (T. Minka, J. Winn, J. Guiver, A. Kannan)

       学生(学生号 char(7),姓名 char(6),性别 char(2),出出生之日期 datetime,

ANN- C approximate neareset-neighbours in dimensions ~(8,20) (D. Mount, S. Arya)

            专业 char(10),年级 int)

FLANN- Fast Library for Approximate Nearest Neighbours (M. Muja, D. Lowe)

       课程(课程号 char(4),课程名 char(10),课程学分 int

Basic Support Vector Machine (SVM) demo(linear and RBF kernel) (A. Vedaldi)

       选课(学生号 char(7),课程号 char(4),成绩 int)

Bits and Pieces

    1.select distinct 产地

HXA graphics library(halton sequence, sobol sequence, color space conversions, radiance RGBE IO, random number generators, 2D radial filters) (H. Ainsworth)

        from 商品表2

原稿链接:http://www.dgp.toronto.edu/~rms/links.html

       功用:从事商业品库中查询出全体商品的比不上产地。

 

    2.select *

        from 学生

        where 学生号 in (select 学生号

          from 选课

          group by 学生号 having count(*)=1

        )

    功用:从事教育工作学库中查询出只选修了一门学科的全部学生。

 

    3.select *

         from 学生

         where 学生号 in (select 学生号

           from 选课

           group by 学生号 having count(*)<=2   

         ) or not exists (select *

              from 选课

              where 学生.学生号=选课.学生号

         )

    作用: 从事教育工作学库中询问出最多选修了2门科目(含未选任何学科)的成套上学的小孩子。

 

    4.从事商业品库中询问出每类(即分类名一样)商品的万丈单价。

 select 分类名,max(单价) as 最高单价

        from 商品表1

        group by 分类名

 

    5.从事教育工作学库中查询出至少选修了人名称叫@m1学员所选课程中一门课的全套上学的儿童。

select distinct 学生.*

        from 学生,选课

        where 学生.学生号=选课.学生号 and 课程号=any(select 课程号

          from 学生,选课

          where 学生.学生号=选课.学生号 and 姓名=@m1

        )

 

 

 

 

 

1.   Student(S#,Sname,Sage,Ssex) 学生表 

2.   Course(C#,Cname,T#) 课程表 

3.   SC(S#,C#,score) 成绩表 

4.   Teacher(T#,Tname) 教师表 

5.    

6.   问题: 

7.   1、查询“001”课程比“002”课程战表高的具有学生的学号; 

8.     select a.S# from (select s#,score from SC where C#='001') a,(select s#,score 

9.     from SC where C#='002') b 

10.    where a.score>b.score and a.s#=b.s#; 

11.  2、查询平均成绩超越60分的同桌的学号和平均战绩; 

12.      select S#,avg(score) 

13.      from sc 

14.      group by S# having avg(score) >60; 

15.  3、查询全体同学的学号、姓名、选课数、总战绩; 

16.    select Student.S#,Student.Sname,count(SC.C#),sum(score) 

17.    from Student left Outer join SC on Student.S#=SC.S# 

18.    group by Student.S#,Sname 

19.  4、查询姓“李”的助教的个数; 

20.    select count(distinct(Tname)) 

21.    from Teacher 

22.    where Tname like '李%'; 

23.  5、查询没学过“叶平”老师课的同学的学号、姓名; 

24.      select Student.S#,Student.Sname 

25.      from Student  

26.      where S# not in (select distinct( SC.S#) from SC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平'); 

27.  6、查询学过“001”而且也学过数码“002”课程的同桌的学号、姓名; 

28.    select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 

29.  7、查询学过“叶平”老师所教的全数课的同窗的学号、姓名; 

30.    select S#,Sname 

31.    from Student 

32.    where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平')); 

33.  8、查询课程编号“002”的成就比课程编号“001”课程低的有着同学的学号、姓名; 

34.    Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 

35.    from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score; 

36.  9、查询全数课程战表小于60分的校友的学号、姓名; 

37.    select S#,Sname 

38.    from Student 

39.    where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 

40.  10、查询未有学全全体课的同学的学号、姓名; 

41.      select Student.S#,Student.Sname 

42.      from Student,SC 

43.      where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 

44.  11、查询至少有一门课与学号为“1001”的同桌所学一样的同桌的学号和姓名; 

45.      select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001'; 

46.  12、查询至少学过学号为“001”同学全体一门课的别的同学学号和姓名; 

47.      select distinct SC.S#,Sname 

48.      from Student,SC 

49.      where Student.S#=SC.S# and C# in (select C# from SC where S#='001'); 

50.  13、把“SC”表中“叶平”老师教的课的成绩都改成为此课程的平分成绩; 

51.      update SC set score=(select avg(SC_2.score) 

52.      from SC SC_2 

53.      where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平'); 

54.  14、查询和“1002”号的校友学习的课程千篇一律的其余同学学号和人名; 

55.      select S# from SC where C# in (select C# from SC where S#='1002') 

56.      group by S# having count(*)=(select count(*) from SC where S#='1002'); 

57.  15、删除学习“叶平”老师课的SC表记录; 

58.      Delect SC 

59.      from course ,Teacher  

60.      where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平'; 

61.  16、向SC表中插入一些记下,这个记录须要符合以下条件:未有上过编号“003”课程的同校学号、2、 

62.      号课的平分战表; 

63.      Insert SC select S#,'002',(Select avg(score) 

64.      from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002'); 

65.  17、按平均战绩从高到低展现全部学员的“数据库”、“集团管理”、“爱尔兰语”三门的学科成绩,按如下格局显示: 学生ID,,数据库,公司管理,阿拉伯语,有效课程数,有效平均分 

66.      SELECT S# as 学生ID 

67.          ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库 

68.          ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 公司管理 

69.          ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语 

70.          ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 

71.      FROM SC AS t 

72.      GROUP BY S# 

73.      ORDER BY avg(t.score)  

74.  18、查询各科成绩最高和压低的分:以如下形式呈现:课程ID,最高分,最低分 

75.      SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分 

76.      FROM SC L ,SC AS R 

77.      WHERE L.C# = R.C# and 

78.          L.score = (SELECT MAX(IL.score) 

79.                        FROM SC AS IL,Student AS IM 

80.                        WHERE L.C# = IL.C# and IM.S#=IL.S# 

81.                        GROUP BY IL.C#) 

82.          AND 

83.          R.Score = (SELECT MIN(IR.score) 

84.                        FROM SC AS IR 

85.                        WHERE R.C# = IR.C# 

86.                    GROUP BY IR.C# 

87.                      ); 

88.  19、按各科平均成绩从低到高和及格率的比重从高到低顺序 

89.      SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩 

90.          ,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 

91.      FROM SC T,Course 

92.      where t.C#=course.C# 

93.      GROUP BY t.C# 

94.      ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 

95.  20、查询如下课程平均成绩和及格率的比例(用"1行"显示): 企管(001),马克思(002),OO&UML (003),数据库(004) 

96.      SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分 

97.          ,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 公司管理及格百分数 

98.          ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分 

99.          ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数 

100.                ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 

101.                ,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数 

102.                ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 

103.                ,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 

104.          FROM SC 

105.        21、查询分化老师所教差异学科平均分从高到低显示 

106.          SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教授姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均战绩 

107.            FROM SC AS T,Course AS C ,Teacher AS Z 

108.            where T.C#=C.C# and C.T#=Z.T# 

109.          GROUP BY C.C# 

110.          ORDER BY AVG(Score) DESC 

111.        22、查询如下课程战表第 3 名到第 6 名的学生成绩单:集团管理(001),马克思(002),UML (003),数据库(004) 

112.            [学生ID],[学生姓名],公司管理,马克思,UML,数据库,平均战表 

113.            SELECT  DISTINCT top 3 

114.              SC.S# As 学生学号, 

115.                Student.Sname AS 学生姓名 , 

116.              T1.score AS 集团管理, 

117.              T2.score AS 马克思, 

118.              T3.score AS UML, 

119.              T4.score AS 数据库, 

120.              ISNULL(T1.score,0)   ISNULL(T2.score,0)   ISNULL(T3.score,0)   ISNULL(T4.score,0) as 总分 

121.              FROM Student,SC  LEFT JOIN SC AS T1 

122.                              ON SC.S# = T1.S# AND T1.C# = '001' 

123.                    LEFT JOIN SC AS T2 

124.                              ON SC.S# = T2.S# AND T2.C# = '002' 

125.                    LEFT JOIN SC AS T3 

126.                              ON SC.S# = T3.S# AND T3.C# = '003' 

127.                    LEFT JOIN SC AS T4 

128.                              ON SC.S# = T4.S# AND T4.C# = '004' 

129.              WHERE student.S#=SC.S# and 

130.              ISNULL(T1.score,0)   ISNULL(T2.score,0)   ISNULL(T3.score,0)   ISNULL(T4.score,0) 

131.              NOT IN 

132.              (SELECT 

133.                    DISTINCT 

134.                    TOP 15 WITH TIES 

135.                    ISNULL(T1.score,0)   ISNULL(T2.score,0)   ISNULL(T3.score,0)   ISNULL(T4.score,0) 

136.              FROM sc 

137.                    LEFT JOIN sc AS T1 

138.                              ON sc.S# = T1.S# AND T1.C# = 'k1' 

139.                    LEFT JOIN sc AS T2 

140.                              ON sc.S# = T2.S# AND T2.C# = 'k2' 

141.                    LEFT JOIN sc AS T3 

142.                              ON sc.S# = T3.S# AND T3.C# = 'k3' 

143.                    LEFT JOIN sc AS T4 

144.                              ON sc.S# = T4.S# AND T4.C# = 'k4' 

145.              ORDER BY ISNULL(T1.score,0)   ISNULL(T2.score,0)   ISNULL(T3.score,0)   ISNULL(T4.score,0) DESC); 

146.         

147.        23、总计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 

148.            SELECT SC.C# as 课程ID, Cname as 课程名称 

149.                ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85] 

150.                ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70] 

151.                ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60] 

152.                ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] 

153.            FROM SC,Course 

154.            where SC.C#=Course.C# 

155.            GROUP BY SC.C#,Cname; 

156.         

157.        24、查询学毕生均成绩及其排名 

158.              SELECT 1 (SELECT COUNT( distinct 平均战绩) 

159.                      FROM (SELECT S#,AVG(score) AS 平均成绩 

160.                              FROM SC 

161.                          GROUP BY S# 

162.                          ) AS T1 

163.                    WHERE 平均成绩 > T2.平分战绩) as 名次, 

164.              S# as 学生学号,平均战绩 

165.            FROM (SELECT S#,AVG(score) 平均战绩 

166.                    FROM SC 

167.                GROUP BY S# 

168.                ) AS T2 

169.            ORDE科雷傲 BY 平均成绩 desc; 

170.          

171.        25、查询各科成绩前三名的记录:(不思索成绩并列景况) 

172.              SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 

173.              FROM SC t1 

174.              WHERE score IN (SELECT TOP 3 score 

175.                      FROM SC 

176.                      WHERE t1.C#= C# 

177.                    ORDER BY score DESC 

178.                      ) 

179.              ORDER BY t1.C#; 

180.        26、查询每门课程被选修的上学的小孩子数 

181.          select c#,count(S#) from sc group by C#; 

182.        27、查询出只选修了一门学科的整整学生的学号和姓名 

183.          select SC.S#,Student.Sname,count(C#) AS 选课数 

184.          from SC ,Student 

185.          where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1; 

186.        28、查询男人、女孩子人数 

187.            Select count(Ssex) as 匹内人数 from Student group by Ssex having Ssex='男'; 

188.            Select count(Ssex) as 女孩子人数 from Student group by Ssex having Ssex='女'; 

189.        29、查询姓“张”的学生名单 

190.            SELECT Sname FROM Student WHERE Sname like '张%'; 

191.        30、查询同名同性学生名单,并总括同有名气的人数 

192.          select Sname,count(*) from Student group by Sname having  count(*)>1;; 

193.        31、一九八三年出生的学生名单(注:Student表中Sage列的品类是datetime) 

194.            select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age 

195.            from student 

196.            where  CONVERT(char(11),DATEPART(year,Sage))='1981'; 

197.        32、查询每门学科的平分战绩,结果按平均成绩升序排列,平均成绩同样时,按学科号降序排列 

198.            Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ; 

199.        33、查询平均成绩超越85的具备学生的学号、姓名和平均战表 

200.            select Sname,SC.S# ,avg(score) 

201.            from Student,SC 

202.            where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85; 

203.        34、查询课程名字为“数据库”,且分数低于60的学员姓名和分数 

204.            Select Sname,isnull(score,0) 

205.            from Student,SC,Course 

206.            where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname='数据库'and score <60; 

207.        35、查询全体学生的选课景况; 

208.            SELECT SC.S#,SC.C#,Sname,Cname 

209.            FROM SC,Student,Course 

210.            where SC.S#=Student.S# and SC.C#=Course.C# ; 

211.        36、查询任何一门学科战表在70分以上的全名、课程名称和分数; 

212.            SELECT  distinct student.S#,student.Sname,SC.C#,SC.score 

213.            FROM student,Sc 

214.            WHERE SC.score>=70 AND SC.S#=student.S#; 

215.        37、查询不比格的学科,并按学科号从大到小排列 

216.            select c# from sc where scor e <60 order by C# ; 

217.        38、查询课程编号为003且课程战绩在80分以上的学员的学号和姓名; 

218.            select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003'; 

219.        39、求选了课程的学员人数 

220.            select count(*) from sc; 

221.        40、查询选修“叶平”老师所授课程的学员中,成绩最高的学员姓名及其成绩 

222.            select Student.Sname,score 

223.            from Student,SC,Course C,Teacher 

224.            where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# ); 

225.        41、查询各种科目及相应的选修人数 

226.            select count(*) from sc group by C#; 

227.        42、查询差别学科成绩一样的学员的学号、课程号、学生成绩 

228.          select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# <>B.C# ; 

229.     43、查询每门功课战绩最佳的前两名 

230.            SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 

231.              FROM SC t1 

232.              WHERE score IN (SELECT TOP 2 score 

233.                      FROM SC 

234.                      WHERE t1.C#= C# 

235.                    ORDER BY score DESC 

236.                      ) 

237.              ORDER BY t1.C#; 

238.        44、总结每门课程的学员选修人数(当先10人的课程才总括)。要求输出课程号和选修人数,查询结果按人口降序排列,查询结果按人头降序排列,若人数一样,按学科号升序排列  

239.            select  C# as 课程号,count(*) as 人数 

240.            from  sc  

241.            group  by  C# 

242.            order  by  count(*) desc,c#  

243.        45、检索至少选修两门课程的学员学号 

244.            select  S#  

245.            from  sc  

246.            group  by  s# 

247.            having  count(*)  >  =  2 

248.        46、查询全部学生都选修的科目标课程号和学科名 

249.            select  C#,Cname  

250.            from  Course  

251.            where  C#  in  (select  c#  from  sc group  by  c#)  

252.        47、查询没学过“叶平”老师授课的任一门科指标学员姓名 

253.            select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平'); 

254.        48、查询两门以上比不上格课程的同班的学号及其平均战绩 

255.            select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#; 

256.        49、检索“004”课程分数小于60,按分数降序排列的同学学号 

257.            select S# from SC where C#='004'and score <60 order by score desc; 

258.        50、删除“002”同学的“001”课程的大成 

259.        delete from Sc where S#='001'and C#='001'; 

260.         

 

 

 

 

 

 

 

 

 

 

宪章考核武器试验题参照他事他说加以考察答案

 

    一、单选 (每空1分,共10分)

   1. D  2. B  3. D 4. B  5. C  6. C 7. B

  1. B 9. D  10. B

二、填空 (每空1分,共20分)

 

   1. 依赖于                 2. 记录、数据项

   3. 多、多                 4. 元组、属性

   5. 数据库管理员、数据库设计员、应用程序猿、终端客商(次序无先后)

   6. 系统、用户             7. 班级号、外码

   8. a1 b1、a2´b2           9. Õ学生号(X)、S  (次序无前后相继)

    10. X→(Y,Z)

三、填空 (每空1分,共20分)

 

   1. 第二                            2.6、4

   3. 表、塑造                        4. 分组总结、排序

   5. 局地E科雷傲图、整体EOdyssey图            6. 安顿须求、作用完善、操作便利

   7. 标题栏、菜单栏、工作区(次序无前后相继)

   8. 字段名称、数据类型、表明(次序无前后相继)

   9. 页眉、主体(次序无前后相继)

四、填空 (每空1分,共20分)

 

   1. (A,D)、伪传递                     

  1. 第一、2

   3. 命令交互、程序试行、窗口分界面(次序无前后相继)

   4. primary key、foreign key

   5. 实表、虚表                        

  1. 连接、选择

   7. 数据、管理(次序无前后相继)          

  1. 主码、外码(次序无前后相继)

   9. 旅客登记表、消费卡表(次序无前后相继)

  1. 大小写

五、填空 (每空1分,共10分)

 

   1. 标题栏、菜单栏、专门的职业区   (次序无前后相继)

   2. 字段名称、数据类型       (次序无先后)

   3. 页眉、主体、页脚         (次序无前后相继)

  1. 类模块、规范模块         (次序无前后相继)

 

    六、依据主教材第四章所给的商品库和教学库,大概遵照下列所给的每条SQL查询语句写出相应的作用,恐怕依据下列所给的各样成效写出相应的SQL查询语句。(每小题4分,共20分)

   1. 从事商业品库中查询出富有商品的不及产地。

   2. 从事教育工作学库中询问出只选修了一门课程的成套上学的儿童。

   3. 从事教育工作学库中查询出最多选修了2门学科(含未选任何学科)的总体学员。

   4. select 分类名,max(单价) as 最高单价

        from 商品表1

        group by 分类名

   5. select distinct 学生.*

        from 学生,选课

        where 学生.学生号=选课.学生号 and 课程号=any(select 课程号

          from 学生,选课

          where 学生.学生号=选课.学生号 and 姓名=@m1

        )

 

3.1 名词解释

(1)SQL模式:SQL形式是表和授权的静态定义。三个SQL形式定义为基本表的汇集。 八个由情势名和方式具有者的顾客名或账号来规定,并富含形式中每三个要素(基本表、视图、索引等)的概念。
(2)SQL数据库:SQL(Structured Query Language),即‘结构式查询语言’,采取丹麦语单词表示和结构式的语准则则。 二个SQL数据库是表的汇总,它用多少个或多少个SQL方式定义。
(3)基本表:在SQL中,把守旧的涉嫌模型中的关系格局称为基本表(Base Table)。 基本表是实在存储在数据库中的表,对应八个关乎。
(4)存款和储蓄文件:在SQL中,把古板的关系模型中的存款和储蓄格局称为存款和储蓄文件(Stored File)。 种种存款和储蓄文件与外表存款和储蓄器上两个物理文件对应。
(5)视图:在SQL中,把守旧的涉及模型中的子格局称为视图(View),视图是从若干主题表和(或)别的视图构造出来的表。
(6):在SQL中,把守旧的关联模型中的元组称为行(row)。
(7)列:在SQL中,把守旧的关联模型中的属性称为列(coloumn)。
(8)实表:基本表被称为“实表”,它是实际贮存在数据库中的表。
(9)虚表:视图被称之为“虚表”,创制二个视图时,只把视图的概念存款和储蓄在数码词典中,而不存款和储蓄视图所对应的数据。
(10)相关子查询:在嵌套查询中冒出的适合以下特点的子查询:子查询中查询条件依赖于外层查询中的有个别值, 所以子查询的管理不只一回,要一再求值,以供外层查询利用。
(11)接通查询:查询时先对表进行笛Carl积操作,然后再做等值联接、选取、投影等操作。 联接查询的作用比嵌套查询低。
(12)交互式SQL:在终点交互格局下行使的SQL语言称为交互式SQL。
(13)嵌入式SQL:嵌入在高级语言的次第中动用的SQL语言称为嵌入式SQL。
(14)分享变量:SQL和宿主语言的接口。分享变量有宿主语言程序定义,再用SQL的DECLARE语句表达, SQL语句就可援用这么些变量传递数据库音信。
(15)游标:游标是与某一查询结果相关联的符号名,用于把集合操作调换到单记录管理方式。
(16)卷游标:为了制伏游标在推动时不可能回去的困难,SQL2提供了卷游标技艺。 卷游标在力促时既能沿查询结果凉月组顺序彻头彻尾一行行推动,也能一行行重临。

3.2 对于教学数据库的三个基本表
  学生S(S#,SNAME,AGE,SEX)
  学习 SC(S#,C#,GRADE)
  课程C(C#,CNAME,TEACHER)
 试用SQL的查询语句表述下列查询:

(1)检索LIU先生所授课程的课程号和学科名。
    SELECT C#,CNAME
    FROM C
    WHERE TEACHER=‘LIU’

(2)检索年龄超越二十四周岁的男学生的学号和人名。
    SELECT S#,SNAME
    FROM S
    WHERE (AGE>23) AND (SEX=‘M’)

(3)检索至少选修LIU老师所授课程中一门学科的女上学的儿童姓名。
    SELECT SNAME
    FROM S
    WHERE SEX=‘F’ AND S# IN
     (SELECT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=‘LIU’)

NOTICE:有多样写法,比方联接查询写法:
    SELECT SNAME
    FROM S,SC,C
    WHERE SEX=‘F’ AND SC.S#=S.S#
    AND SC.C#=C.C#
    AND TEACHER='LIU'
但上一种写法更加好一些。

(4)检索WANG同学不学的科指标课程号。
    SELECT C#
    FROM C
    WHERE C# NOT IN
     (SELECT C#
     FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME='WANG'))

(5)检索至少选修两门学科的学生学号。
    SELECT DISTINCT X.SNO
    FROM SC X,SC Y
    WHERE X.SNO=Y.SNO AND X.CNO<>Y.CNO
Notice:对表SC进行自连接,X,Y是SC的多少个小名。

(6)检索全体学员都选修的教程的教程号与学科名。
    SELECT C#,CNAME
    FROM C
    WHERE NOT EXISTS
     (SELECT *
     FROM S
     WHERE S# NOT IN
      (SELECT *
      FROM SC
      WHERE  SC.C#=C.C#))

要从语义上分解:(1)选取课程的教程号与课程名,子虚乌有不选那门课的校友。
   个中,“不选那门课的同室”能够象征为:

SELECT *

 FROM  S

 WHERE  S# NOT IN

  (SELECT  *

      FROM SC  

      WHERE  SC.C#=C.C#)

 

或者

SELECT *

 FROM  S

 WHERE   NOT EXISTS

  (SELECT  *

      FROM SC  

      WHERE S.S#=C.S# AND 

SC.C#=C.C# )

 

      

(7)检索选修课程包罗LIU先生所教学的学习者学号。    
     SELECT DISTINCT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER='LIU'))   

3.3 设有七个为主表ENVISION(A,B,C)和S(D,E,F),试用SQL查询语句表述下列关系代数表明式:
  (1)πA(R)(2)σB='17'(R)(3)R×S(4))πA,FC=D(R×S))
(1)SELECT A FROM R
(2)SELECT * FROM R WHERE B='17'
(3)SELECT A,B,C,D,E,F FROM R,S
(4)SELECT A,F FROM R,S WHERE R.C=S.D

3.43.4 设有两个着力表翼虎(A,B,C)和S(A,B,C)试用SQL查询语句表述下列关系代数表明式:
  (1)R∪S  (2)R∩S  (3)R-S  (4)πA,B(R)πB,C(S)

    (1)SELECT A,B,C
      FROM R
      UNION
      SELECT A,B,C
      FROM S

    (2)SELECT A,B,C
      FROM R
      INTERSECT
      SELECT A,B,C
      FROM S

    (3)SELECT A,B,C
      FROM R
      WHERE NOT EXISTS
       (SELECT A,B,C
       FROM S
       WHERE R.A=S.A AND R.B=S.B AND R.C=S.C)

    (4)SELECT R.A,R.B,S.C
      FROM R,S
      WHERE R.B=S.B

3.5 试陈述SQL语言的涉及代数特点和元组演算特点。
(P61-62)

3.6 试用SQL查询语句表述下列对教学数据库中四个中央表S、SC、C的询问:

(1)总结有学生选修的学科门数。
    SELECT COUNT(DISTINCT C#) FROM SC

(2)求选修C4学科的学童的平均年龄。
    SELECT AVG(AGE)
    FROM S
    WHERE S# IN
     (SELECT S#
     FROM SC
     WHERE C#='C4')
或者,
    SELECT AVG(AGE)
    FROM S,SC
    WHERE S.S#=SC.S# AND C#='004'

(3)求LIU老师所授课程的每门学科的上学的小孩子平均战表。
   SELECT CNAME,AVG(GRADE)
   FROM SC ,C
   WHERE SC.C#=C.C# ANDTEACHER='LIU'
   GROUP BY C#   

(4)总计每门课程的上学的小孩子选修人数(当先10人的教程才总结)。须求输出课程号和选修人数,查询结果按人头降序排列,若人数同样,按学科号升序排列。
    SELECT DISTINCT C#,COUNT(S#)
    FROM SC
    GROUP BY C#
    HAVING COUNT(S#)>10
    ORDER BY 2 DESC, C# ASC

(5)检索学号比WANG同学大,而年纪比他小的学员姓名。
    SELECT X.SNAME
    FROM S AS X, S AS Y
    WHERE Y.SNAME='WANG' AND X.S#>Y.S# AND X.AGE<Y.AGE

(6)检索姓名以WANG打头的具有学生的全名和年龄。
    SELECT SNAME,AGE
    FROM S
    WHERE SNAME LIKE 'WANG%'

(7)在SC中检索成绩为空值的学生学号和课程号。
    SELECT S#,C#
    FROM SC
    WHERE GRADE IS NULL

(8)求年龄大于女子学校友平均年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX='男' AND X.AGE>(SELECT AVG(AGE)FROM S AS Y WHERE Y.SEX='女')

(9)求年龄大于全数女子高校友年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX='男' AND X.AGE>ALL (SELECT AGE FROM S AS Y WHERE Y.SEX='女')

3.7 试用SQL更新语句表明对教学数据库中几个着力表S、SC、C的依次更新操作:
(1)往基本表S中插入四个上学的小孩子元组(‘S9’,‘WU’,18)。
    INSERT INTO S(S#,SNAME,AGE) VALUES('59','WU',18)
(2)在主导表S中找出每一门科目成绩都不仅等于80分的上学的小孩子学号、姓名和性别,并把检索到的值送往另三个已存在的基本表STUDENT(S#,SANME,SEX)。
    INSERT INTO STUDENT(S#,SNAME,SEX)
     SELECT S#,SNAME,SEX
     FROM S WHERE  NOT EXISTS
      (SELECT * FROM SC WHERE
       GRADE<80 AND S.S#=SC.S#)

(3)在大旨表SC中除去尚无战绩的选课元组。
    DELETE FROM SC
     WHERE GRADE IS NULL

(4)把WANG同学的读书选课和培养全体剔除。
    DELETE FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME='WANG')

(5)把选修MATHS课不比格的成绩全改为空值。
    UPDATE SC
    SET GRADE=NULL
    WHERE GRADE<60 AND C# IN
      (SELECT C#
      FROM C
      WHERE CNAME='MATHS')

(6)把低于总平均战表的女子学校友战表升高5%。
    UPDATE SC
    SET GRADE=GRADE*1.05
    WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S# FROM SWHERE SEX='F')

(7)在宗旨表SC中期维修改C4科指标战绩,若战绩小于等于75分时提升5%,若成绩超乎75分时进步4%(用多个UPDATE语句达成)。
    UPDATE SC
     SET GRADE=GRADE*1.05
     WHERE C#='C4' AND GRADE<=75
    UPDATE SC
     SET GRADE=GRADE*1.04
     WHERE C#='C4' AND GRADE>75

3.8 在第1章例1.4中涉及“饭馆产和土地资金财产管理理”关系模型有四个事关格局:
  零件 PART(P#,PNAME,COLOR,WEIGHT)
  项目 PROJECT(J#,JNAME,DATE)
  供应商 SUPPLIER(S#,SNAME,SADDR)
  供应 P_P(J#,P#,TOTOAL)
  采购 P_S(P#,S#,QUANTITY)

(1)试用SQLDDL语句定义上述七个基本表,并证实主键和外键。
    CREATE TABLE PART
    (P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,
    COLOR CHAR(10),WEIGHT REAL,
    PRIMARY KEY(P#))
    
    CREATE TABLE PROJECT
    (J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,
    DATE DATE,
    PRIMARY KEY(J#))
    
    CREATE TABLE SUPLIER
    (S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),
    PRIMARY KEY(S#))
    
    CREATE TABLE P_P
    (J# CHAR(4),P# CHAR(4),TOTAL INTEGER,
    PRIMARY KEY(J#,P#),
    FOREIGN KEY(J#) REFERENCE PROJECT(J#),
    FOREIGN KEY(P#) REFERENCE PART(P#))
    
    CREATE TABLE P_S
    (P# CHAR(4),S# CHAR(4),QUANTITY INTEGER,
    PRIMARY KEY(P#,S#),
    FOREIGN KEY(P#) REFERENCE PART(P#),
    FOREIGN KEY(S#) REFERENCE SUPLIER(S#))     

(2)试将PROGECT、P_P、PART多个基本表的当然联接定义为二个视图VIEW1,PART、P_S、SUPPLIE奥迪Q7四个基本表的本来联接定义为二个视图VIEW2。
    CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)
          AS SELECT PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL
          FROM PROJECT,PART,P_P
          WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J# 
     
    CREATE VIEW VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)
          AS SELECT PART.P#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITY
          FROM PART,P_S,SUPPLIER
          WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S#

(3)试在上述七个视图的基本功上开展数量查询:

    1)检索Hong Kong的中间商所供应的组件的数码和名字。
    SELECT P#,PNAME FROM VIEW2 WHERE SADDR='SHANGHAI'

    2)检索项目J4所用零件的分销商编号和名字。
    SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1 WHERE J#='J4')

3.9 对于教学数据库中挑咸阳表SC,已成立下列视图:
CREATEVIEWS_GRADE(S#,C_NUM,AVG_GRADE)
ASSELECTS#,COUNT(C#),AVG(GRADE)
FROMSC
GROUPBYS#
试判定下列查询和翻新是不是同意执行。若允许,写出转变来骨干表SC上的对应操作。
  (1) SELECT*
FROMS_GRADE
      允许
   SELECT S#,COUNT(C#),AVG(GRADE)FROM SC GROUP BY S#

  (2) SELECTS#,C_NUM
FROMS_GRADE
WHEREAVG_GRADE>80
    允许
   SELECT S#,COUNT(C#) FROM SC WHEREAVG(GRADE)>80

  (3) SELECTS#,AVG_GRADE
FROMS_GRADE
WHEREC_NUM>(SELECTC_NUM
FROMS_GRADE
WHERES#=‘S4’)
    允许
   SELECT S#,AVG(GRADE)
   FROM SC AS X
   WHERE COUNT(X.C#)>(SELECTCOUNT(Y.C#) FROM SC AS Y WHERE Y.S#='S4')
   GROUP BY S#

  (4) UPDATES_GRADE
SETC_NUM=C_NUM+1
WHERES#=‘S4’
    不允许

  (5) DELETEFROMS_GRADE
WHEREC_NUM>4
    不允许

3.10 预管理格局对于嵌入式SQL的兑现有怎么着主要意义?
    预管理形式是先用预管理程序对源程序实行扫描,识别出SQL语句,并处理成宿主语言的函数调用情势; 然后再用宿主语言的编写翻译程序把源程序编写翻译成目的程序。那样,不用扩展宿主语言的编写翻译程序, 就能够处理SQL语句。

3.11 在宿主语言的程序中利用SQL语句有怎么样规定?
在宿主语言的主次中动用SLQ语句有以下规定:
(1)在先后中要差别SQL语句与宿主语言说话
(2)允许嵌入的SQL语句引用宿主语言的次第变量(称为共享变量),但有两条规定:
   1)援引时,那些变量前必需加“:”作为前缀标记,以示与数据库中变量有分别。
   2)那么些变量由宿主语言的程序定义,并用SQL的DECLARE语句表达。
(3)SQL的汇集管理格局与宿主语言单记录管理格局之间要和睦。 须要运用游标机制,把集合操作调换来单记录管理格局。

3.12SQL的聚众管理格局与宿主语言单记录处理格局之间什么和谐?
    由于SQL语句处理的是记录会集,而宿主语言语句一遍只可以管理贰个笔录, 因此须求用游标(cousor)机制,把会集操作调换到单记录管理方式。

2.13 嵌入式SQL语句哪天不必涉及到游标?曾几何时必须涉及到游标?
    (1)INSERT、DELETE、UPDATE语句,查询结果一定是单元组时的SELECT语句, 都可直接嵌入在主程序中应用,不必涉及到游标。
    (2)当SELECT语句询问结果是多少个元组时,此时宿主语言程序不只怕选用, 一定要用游标机制把八个元组一回三个地传递给宿主语言管理。

 

本文由9159.com发布于前端,转载请注明出处:其它的规则都是一样的, 3、查询课程名以

关键词: 9159.com