SQL数据库试题（笔试题）(8)

c.newcode

where a.je>0 and fkcode<>'##' ) t

where je>0

order by t.zh,t.zhlb,t.fkcode

drop view v_a 解二：

select a.zh,a.zhlb,fkcode,jd1=isnull(a.je,0),jd0=isnull(b.je,0) into #1 from

(select zh,zhlb,fkcode,je=sum(je) from t_a where jdflag=1 group by zh,zhlb,fkcode)a left join

(select zh,zhlb,je=sum(je) from t_a where jdflag=0 group by zh,zhlb) b

on a.zh=b.zh and a.zhlb=b.zhlb

解法一利用视图，写法相对简单点，但是我的水平很次啊，要我写还真不一定能写出来，大家一起研究一下吧。 考题：现有两张表，以下为表结构

create table dep(

depid int identity primary key, --部门号 depname varchar(20) -- 部门名称 )

create table emp(

empid int identity primary key, --员工号 empname varchar(20), --员工姓名 salary money, --工资 depid int --部门号 ) 问题：

1、请用一条sql语句查询出各部门的平均工资。结果显示为如下形式：

部门号 平均工资 -------------------------- 1 2500.4521 2 2845.0121 这个比较简单，不过有几种方式。

正解1：

select depid 部门号, avg(salary) 平均工资 from emp

group by depid

正解2：

select t1.depid 部门号,avg(t2.salary) 平均工资 from dep t1

inner join emp t2 on t1.depid=t2.depid group by t1.depid

正解3：

select 部门号=depid,平均工资=avg(salary) from emp group by depid 2、请列出工资大于本部门平均工资的员工姓名和部门号以及统计此部门的人数。结果显示为如下形式：

EMPNAME DEPID 部门人数 --------------------------------- ZHANGSAN 1 15 LISI 2 10

这个有点难度，我试了半天都没解决。 正解1：

SELECT T1.empname,T1.depid,T2.部门人数 FROM dbo.emp T1 INNER JOIN

(select t1.depid, avg(t2.salary) AS salary, COUNT(empid) AS 部门人数 from dep t1

inner join dbo.emp t2 on t1.depid=t2.depid group by t1.depid) T2 ON T1.depid=T2.depid WHERE T1.salary>T2.salary

正解2：

select empname,emp.depid,s 部门人数 from emp Inner Join (select depid,avg(salary) a,count(1) s from emp group by depid) b on emp.depid=b.depid and salary>a 第二条sql语句相对简单些。。。。

Select empname,emp.depid,(select count(*) from emp as m where m.depid=emp.depid)as 部门人数 from emp where salary>(select avg(salary) from emp as s where emp.depid=s.depid)

sql server常用函数（面试必背！） 1，统计函数 avg, count, max, min, sum

多数聚会不统计值为null的行。可以与distinct一起使用去掉重复的行。可以与group by 来分组

2， 数学函数

SQRT

ceiling（n) 返回大于或者等于n的最小整数 floor(n), 返回小于或者是等于n的最大整数 round(m,n), 四舍五入,n是保留小数的位数 abs(n)

sign(n), 当n>0, 返回1，n=0,返回0，n<0, 返回-1 PI(), 3.1415....

rand(),rand(n), 返回0-1之间的一个随机数 3，字符串函数

ascii(), 将字符转换为ASCII码, ASCII('abc') = 97 char(), ASCII 码 转换为 字符 low()，upper()

str(a,b,c)转换数字为字符串。 a,是要转换的字符串。b是转换以后的长度，c是小数位数。str(123.456,8,2) = 123.46 ltrim(), rtrim() 去空格

left(n), right(n), substring(str, start,length) 截取字符串

charindex(子串，母串），查找是否包含。 返回第一次出现的位置，没有返回0 patindex('%pattern%', expression) 功能同上，可是使用通配符 replicate('char', rep_time), 重复字符串 reverse(char),颠倒字符串

replace(str, strold, strnew) 替换字符串 space(n), 产生n个空行

stuff(), SELECT STUFF('abcdef', 2, 3, 'ijklmn') ='aijklmnef', 2是开始位置，3是要从原来串中删除的字符长度，ijlmn是要插入的字符串。 3，类型转换函数:

cast, cast( expression as data_type), Example:

SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales FROM titles WHERE CAST(ytd_sales AS char(20)) LIKE '3%' convert(data_type, expression) 4,日期函数

day(), month(), year()

dateadd(datepart, number, date), datapart指定对那一部分加，number知道加多少，date指定在谁的基础上加。datepart的取值包括，year,quarter,

month,dayofyear,day,week,hour,minute,second,比如明天dateadd(day,1, getdate())

datediff(datepart,date1,date2). datapart和上面一样。整个函数结果是date2 - date1

datename(datepart, date) 取那一部分，返回字符串。 datepart(datepart, date) 取一部分，返回整数。 getdate()当前时间 5，系统函数

col_length('tablename','colname')

col_name, SELECT COL_NAME(OBJECT_ID('Employees'), 1) = EmployeeID datalength, example: datalenght('abc') =3, datalength(pub_name) --列名 db_id

在面试过程中多次碰到两道SQL查询的题目，一是查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，大家写写看了 ！ 答案：

select top 10 * from A where ID not in (select top 30 ID from A) 或者

select top 10 * from A where id > (select max(id) from (select top 30 id from A)as A)

面试必须要知道的SQL语法，语句

前言：书里有的、书里没的，统统在此可以一浏。

asc 按升序排列 desc 按降序排列

下列语句部分是Mssql语句，不可以在access中使用。

SQL分类：

DDL—数据定义语言(Create，Alter，Drop，DECLARE) DML—数据操纵语言(Select，Delete，Update，Insert)

DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

首先,简要介绍基础语句： 1、说明：创建数据库

Create DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建 备份数据的 device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:\\mssql7backup\\MyNwind_1.dat' --- 开始 备份

BACKUP DATABASE pubs TO testBack 4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表drop table tabname 6、说明：增加一个列

Alter table tabname add column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col)

8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!

排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1

求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1 11、说明：几个高级查询运算词 A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库SQL数据库试题（笔试题）(8)在线全文阅读。