SQL笔试之盘古开天

本次会写一些关于SQL的笔试题，这也是我以前在实际中遇到过的。之所以叫盘古开天，是因为我觉得本次的内容不管是做DBA还是数据库开发的都应该会的，凡是都有第一次么，同时也是工作的敲门砖。

一 行转列问题

首先确保数据库的字符集为ZHS16GBK，以支持如下的中文实验。

create table t4(id int,name varchar2(10),COUNTRY varchar2(20),grade number);

insert into t4 values(1,'KINGBASE','中国',70);

insert into t4 values(2,'ORACLE','美国',90);

insert into t4 values(3,'MYSQL','欧盟',85);

insert into t4 values(4,'DB2','美国',65);

insert into t4 values(5,'MONGDB','中国',75);

insert into t4 values(6,'ORACLE','欧盟',60);

insert into t4 values(7,'KINGBASE','美国',60);

insert into t4 values(8,'MYSQL','中国',90);

insert into t4 values(9,'DB2','欧盟',80);

insert into t4 values(10,'MONGDB','美国',95);

insert into t4 values(11,'MONGDB','欧盟',80);

insert into t4 values(12,'DB2','中国',70);

insert into t4 values(13,'ORACLE','中国',90);

insert into t4 values(14,'MYSQL','美国',80);

insert into t4 values(15,'KINGBASE','欧盟',0);

commit;

结果如下：

SQL> select * from t4;

ID NAME COUNTRY GRADE
---------- ---------- -------------------- ----------
1 KINGBASE 中国 70
2ORACLE 美国 90
3MYSQL 欧盟 85
4DB2 美国 65
5 MONGDB 中国75
6 ORACLE 欧盟60
7 KINGBASE 美国60
8 MYSQL 中国 90
9 DB2 欧盟 80
10 MONGDB 美国 95
11 MONGDB 欧盟 80
12 DB2 中国 70
13 ORACLE 中国 90
14 MYSQL 美国 80
15 KINGBASE 欧盟 0

已选择15行。

select name,sum(case country when '中国' then grade else 0 end) "中国",sum(case country when '美国' then grade else 0 end) "美国",sum(case country when '欧盟' then grade else 0 end) "欧盟"

from t4group by name;

NAME 中国 美国 欧盟
---------- ---------- ---------- ----------
KINGBASE70 60 0
MONGDB 75 95 80
MYSQL90 80 85
ORACLE90 90 60
DB270 65 80

另一种写法：

select name,sum(decode(country,'中国',grade, 0) ) "中国",sum(decode(country,'美国',grade , 0) ) "美国",sum(decode(country,'欧盟',grade, 0)) "美国"
from t4 group by name;

NAME 中国 美国 欧盟
---------- ---------- ---------- ----------
KINGBASE70 60 0
MONGDB 75 95 80
MYSQL90 80 85
ORACLE90 90 60
DB270 65 80

二 多表查询问题

2.1 多表查询顾名思义就是将多个表放在一起查询：

等连接（n个连接必须有n－1个条件）

通过两个表具有相同意义的列,建立连接条件.

查询结果只显示两个列中的值是等值条件的行数据

表中同名列被选择时必须添加表名前缀进行修饰否则无法确定这一列是属于哪个表

SQL> select a.ename||'''s manager is '||b.ename sqltxt from emp a,emp b where b.empno=a.mgr and b.en

ame='KING';

SQLTXT

----------------------------------

BLAKE's manager is KING

JONES's manager is KING

CLARK's manager is KING

2.2 不等连接

A表中的某列数据和B表中一列或多列的关系是非等值关系,大于,小于,不等于,等条件都属于不等连接的范畴（ >,<,!=, <=, >=, between and）

SQL> select ename,sal,grade from emp a,salgrade b where sal between losal and hisal;

ENAME SAL GRADE

---------- ---------- ----------

SMITH 800 1

JAMES 950 1

ADAMS 1100 1

WARD 1250 2

MARTIN 1250 2

MILLER 1300 2

TURNER 1500 3

ALLEN 1600 3

CLARK 2450 4

BLAKE 2850 4

JONES 2975 4

SCOTT 3000 4

FORD 3000 4

KING 5000 5

已选择14行。

2.3 自连接

数据都来自一张表,所以在from字句中需要对表添加别名,添加表别名后才能合法化的引用表中的列名.

SQL> select deptno,ename from emp e where sal > (select avg(sal) from emp where deptno=e.deptno) order by 1; --查询每个部门大于平局工资的员工

DEPTNO ENAME

---------- ----------

10 KING

20 JONES

20 SCOTT

20 FORD

30 ALLEN

30 BLAKE

已选择6行。已选择6行。

2.4 全连接：oracle不提供但可实现（union集合）

集合操作 用于多条select语句合并结果

union 并集排序去重

union all 并集 不排序不去重

intersect 交集

minus 差集

行转列及多表查询，这两个知识点是笔试中经常出现的题目。会了基本可以进一家做BI外包项目的公司干些体力活力了。