- 浏览: 407561 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (318)
- js (20)
- JQuery (2)
- Java (46)
- Oracle (4)
- mysql (21)
- ExtJs (17)
- Excel (2)
- Linux (8)
- Sql (8)
- Jsp (2)
- hibernate (12)
- jbpm (17)
- eclipse (8)
- 名博收藏 (1)
- Junit (2)
- 应用集成 (3)
- web (10)
- jboss (3)
- Rest (3)
- 其它 (7)
- 磁盘分区管理 (1)
- spring (18)
- SSO (4)
- tomcat (4)
- CSS (7)
- MemCached (6)
- EhCache (4)
- weblogic (1)
- apache (6)
- Exception design (1)
- db (1)
- 分析模式 (1)
- jstl (1)
- jsf (0)
- firefox (2)
- MongoDB (4)
- androidpn (1)
- hadoop (1)
- cvs (1)
- 微信公众号 (2)
- 高并发 (4)
- 技术论坛 (1)
- CDN (1)
- JVM (16)
- 加密 (4)
- maven (2)
- jenkins (1)
- hessian (1)
- 大数据处理 (2)
- NIO (0)
- netty (1)
- redis (1)
- git (1)
- Elastic Job (0)
最新评论
-
zgw06629:
或者<pre>aaaabbbbcccc</p ...
javaDoc注释换行 -
ddnzero:
...
StringBuffer换行 -
maosijun:
。。。。
EXT CExt.form.ComboBox选择一次后只剩一个选项 -
ysa198584:
你这有问题,当我的代码出现User.class的时候,反编绎的 ...
java的class文件批量反编译 -
dongj0325:
看到您的博客,很受启发,但还有关于jbpm4.4 timer使 ...
JBPM定时器(Timer)之Repeat属性不能使用变量
SQL经典面试题集(二)
第十一题:
有表students(name,class,grade),请用标准sql语句完成
name class grade
张三 数学 81
李四 语文 70
王五 数学 90
张三 语文 60
李四 数学 100
王五 语文 90
王五 英语 81
要求: 用sql语句输出各门功课都大于80分的同学姓名?
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('张三','语文',20)
insert into students values ('张三','数学',90)
insert into students values ('张三','英语',50)
insert into students values ('李四','语文',81)
insert into students values ('李四','数学',60)
insert into students values ('李四','英语',90)
insert into students values ('王二','数学',81)
insert into students values ('王二','英语',90)
insert into students values ('李五','数学',83)
insert into students values ('李五','英语',90)
insert into students values ('李五','化学',90)
---选出所有成绩大于80分的学生姓名-----
------解法一------
select name from students group by name having min(grade)>80
------解法二------
select distinct Name from students where grade >80 and Name not in (select Name from students where grade <80)
------解法三------
select distinct name from students where name not in (select name from students where grade <=80 group by name )
-----解法四-------
select name from students group by name having name not in (select name from students where grade<=80)
第十二题:
已知一个表的结构为:
姓名 科目 成绩
张三 语文 20
张三 数学 30
张三 英语 50
李四 语文 70
李四 数学 60
李四 英语 90
怎样通过select语句把他变成以下结构:
姓名 语文 数学 英语
张三 20 30 50
李四 70 60 90
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('张三','语文',20)
insert into students values ('张三','数学',90)
insert into students values ('张三','英语',50)
insert into students values ('李四','语文',81)
insert into students values ('李四','数学',60)
insert into students values ('李四','英语',90)
--解答:
select A.Name,A.grade as 语文,B.grade as 数学,C.grade as 英语
from students A,students B,students C
where A.Name=B.Name and B.Name=C.Name
and A.class='语文' and B.class='数学'
and C.class='英语'
第十三题:
我现在有两张表个表
create table userinfo
(
id int,
username varchar(32),
u_id int
)
create table checkinfo
(
id int,
checktype varchar(32) --出勤的类型(正常,病假,事假)
u_id int
)
两张表通过u_id关联的
怎么查询出每个用户的某个月出勤的情况:
比如说,1月份,正常出勤多少天,事假多少天,病假多少天?
例如:
username 病假(天数) 事假(天数) 病假(天数)
张三 15 5 2
create table userinfo
(
id int,
username varchar(32),
u_id int
)
create table checkinfo
(
id int,
checktype varchar(32), --出勤的类型(正常,病假,事假)
u_id int
)
delete from userinfo
insert into userinfo values(1,'user1',1)
insert into userinfo values(2,'user2',2)
insert into userinfo values(3,'user3',3)
insert into userinfo values(4,'user4',4)
insert into checkinfo values(1,'正常',1)
insert into checkinfo values(2,'正常',1)
insert into checkinfo values(3,'病假',1)
insert into checkinfo values(4,'正常',2)
insert into checkinfo values(5,'事假',2)
insert into checkinfo values(6,'病假',2)
insert into checkinfo values(7,'正常',2)
insert into checkinfo values(8,'病假',2)
insert into checkinfo values(9,'正常',3)
insert into checkinfo values(10,'事假',3)
insert into checkinfo values(11,'病假',3)
insert into checkinfo values(12,'正常',3)
insert into checkinfo values(13,'正常',3)
insert into checkinfo values(14,'正常',3)
insert into checkinfo values(15,'正常',3)
insert into checkinfo values(16,'病假',3)
insert into checkinfo values(17,'正常',4)
insert into checkinfo values(18,'事假',4)
insert into checkinfo values(19,'病假',4)
insert into checkinfo values(20,'正常',4)
insert into checkinfo values(21,'事假',4)
insert into checkinfo values(22,'病假',4)
insert into checkinfo values(23,'事假',4)
insert into checkinfo values(24,'病假',4)
---解法一:
select b.*,m.正常,m.事假,m.病假
from userinfo b
join
(select a.u_id,
count(case when a.checktype='病假' then '1' end ) 病假 ,
count(case when a.checktype='正常' then '1' end ) 正常 ,
count(case when a.checktype='事假' then '1' end ) 事假
from checkinfo a group by a.u_id) m
on m.u_id=b.u_id
---解法二:
select b.* ,m1.正常,m2.病假,m3.事假 from userinfo b
left join (select a.u_id, count(a.checktype) 正常 from checkinfo a where a.checktype='正常' group by a.u_id ) m1 on b.u_id=m1.u_id
left join (select a.u_id, count(a.checktype) 病假 from checkinfo a where a.checktype='病假' group by a.u_id ) m2 on b.u_id=m2.u_id
left join (select a.u_id, count(a.checktype) 事假 from checkinfo a where a.checktype='事假' group by a.u_id ) m3 on b.u_id=m3.u_id
第十四题:
产品 颜色 数量
产品1 红色 100
产品1 蓝色 80
产品2 蓝色 103
产品2 红色 NULL
产品2 红色 89
产品1 红色 100
1:按产品分类,仅列出各类商品中红色多于蓝色的商品名称及差额数量
create table products(
name varchar(20),
color char(20),
quantities int
)
insert into products values('产品1','红色',100)
insert into products values('产品1','蓝色',80)
insert into products values('产品2','红色',null)
insert into products values('产品2','蓝色',103)
insert into products values('产品2','红色',89)
insert into products values('产品1','红色',100)
-----解答:
---第一步:查询出每种商品中兰色和红色数量及产品名称
--红色:
select name,sum(quantities) from products where color='红色' group by name
--蓝色:
select name,sum(quantities) from products where color='蓝色' group by name
---第二步:查询出要求的结果:
select t1.name,t1.x-t2.x as balance
from
(select name,sum(quantities) as x from products where color='红色' group by name) t1,
(select name,sum(quantities) as x from products where color='蓝色' group by name) t2
where t1.x >t2.x and t1.name=t2.name
第十五题:
--查询学生表中,选修课超过5门的名字!
create table students (
id int IDENTITY,
name varchar(20),
elective_course varchar(20)
)
insert into students values('student1','course1')
insert into students values('student1','course2')
insert into students values('student1','course3')
insert into students values('student1','course4')
insert into students values('student1','course6')
insert into students values('student1','course6')
insert into students values('student2','course1')
insert into students values('student2','course2')
insert into students values('student2','course3')
insert into students values('student2','course4')
insert into students values('student2','course5')
insert into students values('student3','course1')
insert into students values('student3','course2')
insert into students values('student3','course3')
insert into students values('student3','course4')
insert into students values('student4','course1')
insert into students values('student4','course2')
insert into students values('student4','course3')
insert into students values('student4','course4')
insert into students values('student4','course5')
insert into students values('student4','course6')
insert into students values('student4','course7')
insert into students values('student5','course2')
insert into students values('student5','course3')
insert into students values('student5','course4')
insert into students values('student5','course5')
insert into students values('student5','course6')
insert into students values('student5','course7')
insert into students values('student5','course8')
insert into students values('student5','course9')
insert into students values('student6','course7')
insert into students values('student6','course8')
insert into students values('student6','course9')
--解答:
select name from students group by name having count(elective_course)>=5
第十六题:
DbTable表有三列,id,name,data,其中name列里每行都含有'{data}',如第一行里为'aa{data}bb',第二行为'abc{data}cd',要求用对应data列的
数据替换掉'{data}',sql怎么写?
create table DbTable (
ID int IDENTITY,
name varchar(20),
data varchar(10)
)
insert into DbTable values ('a1{data}bb','1')
insert into DbTable values ('a2{data}bb','2')
insert into DbTable values ('a3{data}bb','3')
insert into DbTable values ('a4{data}bb','4')
insert into DbTable values ('a5{data}bb','5')
--解答:
update DbTable set name=replace(name,'{data}',data)
select * from DbTable
第十七题:
存在表table(FID,FCLASS,FSSCORE),三字段分别代表姓名、班级、成绩。用最高效、最简单的SQL语句列出人数大于30的各班最高成绩的列表,显示
班级、成绩两个字段。
create table F3 (
FID varchar(20),
FLASS varchar(20),
FSSCORE int
)
insert into F3 values ('S_Name1','Class1',67)
insert into F3 values ('S_Name2','Class1',57)
insert into F3 values ('S_Name3','Class1',27)
insert into F3 values ('S_Name4','Class1',37)
insert into F3 values ('S_Name5','Class1',97)
insert into F3 values ('S_Name6','Class2',67)
insert into F3 values ('S_Name7','Class2',57)
insert into F3 values ('S_Name8','Class2',27)
insert into F3 values ('S_Name9','Class2',37)
insert into F3 values ('S_Name10','Class2',97)
insert into F3 values ('S_Name11','Class2',37)
insert into F3 values ('S_Name112','Class2',97)
insert into F3 values ('S_Name17','Class3',57)
insert into F3 values ('S_Name18','Class3',27)
insert into F3 values ('S_Name19','Class3',37)
insert into F3 values ('S_Name110','Class3',88)
insert into F3 values ('S_Name111','Class3',37)
insert into F3 values ('S_Name1112','Class3',67)
insert into F3 values ('S_Name117','Class4',57)
insert into F3 values ('S_Name118','Class4',27)
insert into F3 values ('S_Name119','Class4',37)
insert into F3 values ('S_Name1110','Class4',82)
insert into F3 values ('S_Name1111','Class4',37)
insert into F3 values ('S_Name11112','Class4',67)
insert into F3 values ('S_Name11111','Class5',37)
insert into F3 values ('S_Name111112','Class5',67)
---解答:为了便于组装测试数据,这里一以5为人数
--解法一:
select F3.FLASS, Max(FSSCORE) from F3 group by FLASS having count(*) >=5
--解法二:
--第一步:查询出人数大于5的班级--
select FLASS ,count(*) as Total from F3 group by FLASS having count(*) >= 5
--第二步:查询出所有人数大于5的班级的所有学生记录--
select * from F3 where FLASS in (select FLASS from F3 group by FLASS having count(*) >= 5 )
--第三步:通过对第二步的记录根据FCLASS分组查询--
select FLASS, Max(FSSCORE) from F3 where FLASS in (select FLASS from F3 group by FLASS having count(*) >= 5 ) group by FLASS
--解法三:
select FLASS,max(fsscore) from
(
select * from F3
where FLASS in (select FLASS from F3 group by FLASS having count(*)>=5)
) T group by FLASS
第十八题:
有一张老师表Teachers,字段是T_ID,T_NAME;有一张学生表Students,字段是S_ID,S_NAME;还有一张班级表Classes,字段是T_ID,S_ID,C_NAME,其中
C_NAME的取值只有‘大班’和‘小班’,请查询出符合条件的老师的名字,条件是老师在大班中带的学生数大于此老师在小班中带的学生数。
create table Teachers (
T_ID int,
T_NAME varchar(20)
)
create table Students (
S_ID int,
S_NAME varchar(20)
)
create table Classes (
T_ID int,
S_ID int,
C_NAME varchar(20)
)
insert into Teachers values(1,'T1')
insert into Teachers values(2,'T2')
insert into Teachers values(3,'T3')
insert into Teachers values(4,'T4')
insert into Teachers values(5,'T5')
insert into Students values(1,'S1')
insert into Students values(2,'S1')
insert into Students values(3,'S1')
insert into Students values(4,'S1')
insert into Students values(5,'S1')
insert into Students values(6,'S1')
insert into Students values(7,'S1')
insert into Students values(8,'S1')
insert into Students values(9,'S1')
insert into Students values(10,'S1')
insert into Students values(11,'S1')
insert into Students values(12,'S1')
insert into Students values(13,'S1')
insert into Students values(14,'S1')
insert into Students values(15,'S1')
insert into Students values(16,'S1')
insert into Classes values(1,1,'大班')
insert into Classes values(1,2,'大班')
insert into Classes values(1,3,'小班')
insert into Classes values(1,4,'大班')
insert into Classes values(1,13,'大班')
insert into Classes values(1,14,'大班')
insert into Classes values(1,15,'小班')
insert into Classes values(1,16,'大班')
insert into Classes values(2,1,'大班')
insert into Classes values(2,2,'小班')
insert into Classes values(2,3,'大班')
insert into Classes values(2,4,'大班')
insert into Classes values(2,16,'小班')
insert into Classes values(2,15,'小班')
insert into Classes values(2,14,'小班')
insert into Classes values(3,5,'大班')
insert into Classes values(3,6,'小班')
insert into Classes values(3,7,'大班')
insert into Classes values(4,4,'大班')
insert into Classes values(4,5,'大班')
insert into Classes values(4,6,'小班')
insert into Classes values(4,7,'小班')
insert into Classes values(4,8,'小班')
insert into Classes values(5,9,'大班')
insert into Classes values(5,10,'小班')
insert into Classes values(5,11,'小班')
insert into Classes values(5,12,'小班')
--第一步:查询出每个老师所带的小班的人数--------
select T_ID,count(*) as x from Classes where C_Name='小班' group by T_ID
--第二步:查询出每个老师所带的大班的人数--------
select T_ID,count(*) as x from Classes where C_Name='大班' group by T_ID
--第三步:在上面一二步的基础上查询出大班人数大于小班人数的老师------------
select T_NAME
from Teachers t,
(select T_ID,count(*) as x from Classes where C_Name='小班' group by T_ID) T1,
(select T_ID,count(*) as x from Classes where C_Name='大班' group by T_ID) T2
where T1.x<T2.x
and T1.T_ID=T2.T_ID and t.T_ID=T1.T_ID
--考察要点:1.分组查询. 2.把查询出来的某些结果作为表来连接查询出相关结果.
第十九题:
前提:a 部门表 b 员工表
a表字段(
id --部门编号
departmentName-部门名称
)
b表字段(
id--部门编号
employee- 员工名称
)
问题:如何一条sql语句查询出每个部门共有多少人?
*/
create table departments(
ID int IDENTITY,
Name varchar (20),
)
create table employees(
ID int,
Name varchar (20)
)
insert into departments values ('DeparmentA')
insert into departments values ('DeparmentB')
insert into departments values ('DeparmentC')
insert into departments values ('DeparmentD')
insert into departments values ('DeparmentE')
insert into employees values (1,'Zhang3')
insert into employees values (1,'Zhang4')
insert into employees values (1,'Zhang5')
insert into employees values (2,'Li3')
insert into employees values (2,'Li4')
insert into employees values (2,'Li5')
insert into employees values (2,'Li6')
insert into employees values (3,'Zhao3')
insert into employees values (3,'Zhao4')
insert into employees values (3,'Zhao5')
insert into employees values (4,'Chen4')
insert into employees values (4,'Chen5')
insert into employees values (5,'Zhu4')
--解法一----
select b.id,a.Name,count(b.id)as employeecount from departments a left join employees b on a.id=b.id group by b.id,a.Name
---或
select b.id,a.Name,count(*)as employeecount from departments a left join employees b on a.id=b.id group by b.id,a.Name
---解法二---
select t.id as 'Id', t.name as 'Name',count (*) as 'EmployeeCount' from (select d.id,d.name from departments as d inner join employees as e on d.id=e.id) t group by t.id,t.name
--解法三----
select a.id,a.Name,count(b.id)as employeecount from departments a left join employees b on a.id=b.id group by a.id,a.Name
第二十题:
在Oracle数据库中有一张表A
编号 名称
1 a
2 b
3 c
4 d
如何写一条SQL语句,显示以下结果
ab,ac,ad,bc,cd
drop table B select b.id,b.name from B b
create table B (
id int IDENTITY,
name varchar (20)
)
insert into B values ('a')
insert into B values ('b')
insert into B values ('c')
insert into B values ('d')
--SQL Server:
select a.name+b.name,a.name+c.name,a.name+d.name,b.name+c.name,c.name+d.name from B a, B b,B c,B d where a.id=1 and b.id=2 and c.id=3 and d.id=4
--Oracle:
select distinct a.name||b.name||','||a.name||c.name||','||a.name||d.name||','||b.name||c.name||','||c.name||d.name
from B a,B b,B c,B d
where a.number=1 and b.number=2 and c.number=3 and d.number=4
--其它参考:如果要求你在同一列显示呢?
ab
ac
ad
bc
bd
cd
--参考答案:
select a.name+b.name from B a,B b
where a.id<b.id group by a.name+b.name
发表评论
-
Mysql/postgreSQL
2013-11-04 10:12 708分类: Mysql/postgreSQL ... -
MySQL 两个经纬度之间的距离由近及远排序
2013-06-18 19:52 3668select *,(2 * 6378.137* ASIN( ... -
SQL连接
2010-06-10 13:45 639SQL中大概有这么几种JOIN: cross join i ... -
sql性能优化
2010-02-04 11:21 692http://www.cnblogs.com/zhangzh/ ... -
简洁、明晰!数据库设计三大范式应用实例剖析
2009-12-05 19:20 831简洁、明晰!数据库设 ... -
union和union all 的区别
2009-12-05 19:18 3399Union和Union all 的区别 在数据库中 ... -
SQL经典面试题集(一)
2009-12-05 19:09 2517...
相关推荐
电脑应用_SQL经典面试题集共13页.pdf.zip
。。。
列举几个典型实用的例子: 实例37 读取随机文件 ...SQL面试题大全.pdf 开发面试75条.pdf java 面试题 数据库方面.pdf 如何写出高性能的SQL.pdf 线程编程方面,jsp,jdbc,xml,servlet.pdf 面试题集合.pdf
数据挖掘分析面试题 数据挖掘分析面试题全文共16页,当前为第1页。数据挖掘分析面试题全文共16页,当前为第1页。2011Alibaba数据分析师(实习)试题解析 数据挖掘分析面试题全文共16页,当前为第1页。 数据挖掘分析...
mybatis面试题(经典问答) 1、JDBC有多少步? JDBC可以分为六个步骤 负载驱动程序 获取数据库连接 创建语句对象 操作数据库进行增删改查 获取结果集 关闭资源 2.什么是mybatis? 如果你在面试中被问到,只需说以下...
大公司的Java面试题集.doc 就业相关java 广州传智播客JavaEE工程师测试题.doc 广州传智播客JavaEE工程师测试题(带答案的).doc 应聘时最漂亮的回答.docx 当面试官问「你有什么要问我的吗」时,应该问什么?.docx ...
│ SQL面试题_心灵深处.htm │ Struts+Hibernate+Spring轻量级J2EE企业应用实战.pdf │ Struts中文手册.pdf │ Struts配置文件详解.txt │ 上海税友.txt │ 上海税友软件 面试题.doc │ 公司培训文档-混淆的基本概念...
大公司的Java面试题集.doc 就业相关java 广州传智播客JavaEE工程师测试题.doc 广州传智播客JavaEE工程师测试题(带答案的).doc 应聘时最漂亮的回答.docx 当面试官问「你有什么要问我的吗」时,应该问什么?.docx ...
大公司的Java面试题集.doc 就业相关java 广州传智播客JavaEE工程师测试题.doc 广州传智播客JavaEE工程师测试题(带答案的).doc 应聘时最漂亮的回答.docx 当面试官问「你有什么要问我的吗」时,应该问什么?.docx ...
│ SQL面试题_心灵深处.htm │ Struts+Hibernate+Spring轻量级J2EE企业应用实战.pdf │ Struts中文手册.pdf │ Struts配置文件详解.txt │ 上海税友.txt │ 上海税友软件 面试题.doc │ 公司培训文档-混淆的基本概念...
│ SQL面试题_心灵深处.htm │ Struts+Hibernate+Spring轻量级J2EE企业应用实战.pdf │ Struts中文手册.pdf │ Struts配置文件详解.txt │ 上海税友.txt │ 上海税友软件 面试题.doc │ 公司培训文档-混淆的基本概念...
二、MySQL运维基础知识面试问答题 要求:4人一组,一个人提问,一个人答,其他人补充,争取全部口头用自己的语言描述出来 1.请解释关系型数据库概念及主要特点? 2.请说出关系型数据库的典型产品、特点及应用场景? 3....
│ SQL面试题_心灵深处.htm │ Struts+Hibernate+Spring轻量级J2EE企业应用实战.pdf │ Struts中文手册.pdf │ Struts配置文件详解.txt │ 上海税友.txt │ 上海税友软件 面试题.doc │ 公司培训文档-混淆的基本概念...
面试题 经典算法 C# .NET SQL 简单易懂!
│ SQL面试题_心灵深处.htm │ Struts+Hibernate+Spring轻量级J2EE企业应用实战.pdf │ Struts中文手册.pdf │ Struts配置文件详解.txt │ 上海税友.txt │ 上海税友软件 面试题.doc │ 公司培训文档-混淆的基本概念...
散文集 1 source 1 ss vc 1 入门 2 1 面试题 1 delphi 1 java 1 经典 1 基础 1 全集 1 windows 2 1 管理 1 设计 1 数据库 1 数据结构 1 1800例 1 电子书 1 c++沉思录 1 答案 1 server 1 sql 1 edition 1 3rd 1 ...
20. xxx公司的sql面试 108 21、注册Jdbc驱动程序的三种方式 109 22、用JDBC如何调用存储过程 109 23、JDBC中的PreparedStatement相比Statement的好处 110 24. 写一个用jdbc连接并访问oracle数据的程序代码 111 25、...
详细解释的 Java面试题 最小函数依赖 在数据库中实现base64编码和解码 java servlet总结 java的时间操作 画Web流程图的一点心得 深入Java核心 Java内存分配原理精讲 JAVA调用存储过程详解 经典SQL语句大全 使用...
JAVA资料大全,包括面试资料(面试试题、面试技巧、简历资料)、工作技巧、JAVA资料(软件测试、资料集),其中资料集有大量的开发技巧Struts、Spring、Hibernate等框架,Sql经典语句,Mysql常用命令、JSF、POJO、EJB、...