27 篇文章 0 订阅

# 1，面试题范例一

1.用一条SQL语句 查询出每门课都大于80分的学生姓名

name   kecheng   fenshu

A: select distinct name from table  where  name not in (select distinct name from table where fenshu<=80)

2.学生表 如下:

1        2005001  张三  0001      数学    69
2        2005002  李四  0001      数学    89
3        2005001  张三  0001      数学    69

A: delete tablename where 自动编号 not in(select min(自动编号) from tablename group by 学号,姓名,课程编号,课程名称,分数)

from team a, team b
where a.name < b.name

AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。

from TestDB a
,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur
************************************************************************************

year  month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4

year m1  m2  m3  m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

select year,
(select amount from  aaa m where month=1  and m.year=aaa.year) as m1,
(select amount from  aaa m where month=2  and m.year=aaa.year) as m2,
(select amount from  aaa m where month=3  and m.year=aaa.year) as m3,
(select amount from  aaa m where month=4  and m.year=aaa.year) as m4
from aaa  group by year

select * from (select name, year b1, lead(year) over
(partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over(
partition by name order by year) rk from t) where rk=1;
************************************************************************************

SQL: select * into b from a where 1<>1

SQL: insert into b(a, b, c) select d,e,f from b;

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

SQL:

delete from info where not exists ( select * from infobz where info.infid=infobz.infid )

SQL:

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE

FROM TABLE1,

(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE

FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,

(SELECT NUM, UPD_DATE, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,

WHERE X.NUM = Y.NUM （+）

AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B

WHERE A.NUM = B.NUM

SQL:

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩

SQL:

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC

FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration

FROM TELFEESTAND a, TELFEE b

WHERE a.tel = b.telfax) a

GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')

SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

SQL:

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID

FROM Handle

WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)

*******************************************************************************

update   b   set   b.value=(select   a.value   from   a   where   a.key=b.key)   where   b.id   in(select   b.id   from   b,a   where   b.key=a.key);
***************************************************************************

courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------

courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------

select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course

SQL> desc course_v
Name Null? Type
----------------------------------------- -------- ----------------------------
COURSEID NUMBER
COURSENAME VARCHAR2(10)
SCORE NUMBER

SQL> select * from course_v;

COURSEID COURSENAME SCORE
---------- ---------- ----------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80

SQL> select courseid, coursename ,score ,decode(sign(score-60),-1,'fail','pass') as mark from course_v;

COURSEID COURSENAME SCORE MARK
---------- ---------- ---------- ----
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
*******************************************************************************

id proid proname
1 1 M
1 2 F
2 1 N
2 2 G
3 1 B
3 2 A

id pro1 pro2
1 M F
2 N G
3 B A

sql求解

1 b
2 x
2 y
2 z

1 ab
2 xyz

create or replace type strings_table is table of varchar2(20);
/
create or replace function merge (pv in strings_table) return varchar2
is
ls varchar2(4000);
begin
for i in 1..pv.count loop
ls := ls || pv(i);
end loop;
return ls;
end;
/
create table t (id number,name varchar2(10));
insert into t values(1,'Joan');
insert into t values(1,'Jack');
insert into t values(1,'Tom');
insert into t values(2,'Rose');
insert into t values(2,'Jenny');

column names format a80;
select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names
from (select distinct id from t) t0;

drop type strings_table;
drop function merge;
drop table t;

Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky.

This example uses a max of 6, and would need more cut n pasting to do more than that.

SQL> select deptno, dname, emps
2 from (
3 select d.deptno, d.dname, rtrim(e.ename ||', '||
4 lead(e.ename,1) over (partition by d.deptno
5 order by e.ename) ||', '||
6 lead(e.ename,2) over (partition by d.deptno
7 order by e.ename) ||', '||
8 lead(e.ename,3) over (partition by d.deptno
9 order by e.ename) ||', '||
10 lead(e.ename,4) over (partition by d.deptno
11 order by e.ename) ||', '||
12 lead(e.ename,5) over (partition by d.deptno
13 order by e.ename),', ') emps,
14 row_number () over (partition by d.deptno
15 order by e.ename) x
16 from emp e, dept d
17 where d.deptno = e.deptno
18 )
19 where x = 1
20 /

DEPTNO DNAME EMPS
------- ----------- ------------------------------------------
10 ACCOUNTING CLARK, KING, MILLER
20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH
30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

also

create or replace function get_a2( tmp_a1 number)
return varchar2
is
Col_a2 varchar2(4000);
begin
Col_a2:='';
for cur in (select a2 from unite_a where a1=tmp_a1)
loop
Col_a2=Col_a2||cur.a2;
end loop;
return Col_a2;
end get_a2;

select distinct a1 ,get_a2(a1) from unite_a
1 ABC
2 EFG
3 KMN
*******************************************************************************

Table t1:

SELLER | NON_SELLER
----- -----

A B
A C
A D
B A
B C
B D
C A
C B
C D
D A
D B
D C

Table t2:

SELLER | COUPON | BAL
----- --------- ---------
A 9 100
B 9 200
C 9 300
D 9 400
A 9.5 100
B 9.5 20
A 10 80

NON-SELLER| COUPON | SUM(BAL) ------- --------
A 9 900
B 9 800
C 9 700
D 9 600
A 9.5 20
B 9.5 100
C 9.5 120
D 9.5 120
A 10 0
B 10 80
C 10 80
D 10 80

CREATE TABLE [dbo].[TABLE2] (
[username] [varchar] (50) NOT NULL , --用户名
[outdate] [datetime] NOT NULL , --日期
[cash] [float] NOT NULL --余额
) ON [PRIMARY

declare @i int
set @i=1
while @i<=100
begin
insert table2 values(convert(varchar(50),@i),'2001-10-1',100)
insert table2 values(convert(varchar(50),@i),'2001-11-1',50)
set @i=@i+1
end
insert table2 values(convert(varchar(50),@i),'2001-10-1',90)

select * from table2 order by outdate,convert(int,username)

2.组合查询语句：
a.我们必须返回一个从第一天开始到100天的纪录集：

from table2

outdate
------------------------------------------------------
2001-10-01 00:00:00.000
.........
2002-01-08 00:00:00.000

b.返回一个所有用户名的纪录集：

--------------------------------------------------
1
10
100
......
99

c.返回一个100天记录集和100个用户记录集的笛卡尔集合：
select * from
(
from table2
) as A
CROSS join
(
) as B

2001-10-01 00:00:00.000            1
......
2002-01-08 00:00:00.000            100

d.返回当前所有用户在数据库的有的纪录：
select outdate,username,min(cash) as cash from table2

2001-10-01 00:00:00.000            1          90
......
2002-01-08 00:00:00.000            100        50

e.将c中返回的笛卡尔集和d中返回的纪录做left join:
D.cash
from
(
select * from
(
from table2
) as A
CROSS join
(
) as B
) as C
left join
(
select outdate,username,min(cash) as cash from table2
) as D

2001-10-01 00:00:00.000            1          90
2001-10-01 00:00:00.000            2          100
......
2001-10-02 00:00:00.000            1          90
2001-10-02 00:00:00.000            2          NULL  <--注意这里
......

2002-01-08 00:00:00.000            100        50

f.好了，现在我们最后要做的就是，如果cash为NULL，我们要返回小于当前纪录日期的第一个用户余额(由于我们使用order by cash,所以返回top 1纪录即可，使用min应该也可以)，这个余额即为当前的余额：
case isnull(D.cash,0)
when 0 then
(
and datediff(d,C.outdate,table2.outdate)<0
order by table2.cash
)
else D.cash
end as cash

g.最后组合的完整语句就是
case isnull(D.cash,0)
when 0 then
(
and datediff(d,C.outdate,table2.outdate)<0
order by table2.cash
)
else D.cash
end as cash
from
(
select * from
(
from table2
) as A
CROSS join
(
) as B
) as C
left join
(
select outdate,username,min(cash) as cash from table2
) as D

2001-10-01 00:00:00.000    1                    90
2001-10-01 00:00:00.000    2                   100
......
2002-01-08 00:00:00.000    100                50
***********************************************************************************

*从数据表中取出第n条到第m条的记录*/

declare @m int
declare @n int
declare @sql varchar(800)
set @m=40
set @n=31
set @sql='select top '+str(@m-@n+1) + '* from idetail where autoid not in(
select top '+ str(@n-1) + 'autoid from idetail)'
exec(@sql)

select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id
--------------------------------------------------------------------------------
select top 10 * from t where id in (select top 40 id from t order by id) order by id desc

*******************************************************************************

c1 c2 A ----------1
---- ---- / /
A B B C --------2
A C / / /
B D D N E ------3
C E / / /
D F F K I ---4
E I
D K
C N

jd cs
----- ----
A 1
B 2
C 2
D 3
N 3
E 3
F 4
K 4
I 4

SQL> select c2, level + 1 lv
2 from test start
3 with c1 = 'A'
4 connect by c1 = prior c2
5 union
6 select 'A', 1 from dual
7 order by lv;

C2 LV
-- ----------
A 1
B 2
C 2
D 3
E 3
N 3
F 4
I 4
K 4



# 2，面试题范例二

--================================
-- ylb:表的高级查询-  多表连接和子查询
--    pubs库的练习
-- 17:18 2011/12/13
--================================
use pubs
go
select * from authors
select * from titles
--select * from titleauthor
select * from publishers
--1,查看出版社名称，书名称
go
--2,查看出版社名称,出版社出书预付款总额
select pub_name,SUM(advance) '预付款总额' from publishers p
inner join titles t on p.pub_id=t.pub_id
group by pub_name
go
--3,查看出版社名称，出版社出书预付款最大值，单价最小值
select pub_name,max(advance) '付款最大值',MIN(price) '单价最小值' from publishers p
inner join titles t on p.pub_id=t.pub_id
group by pub_name
go
--4,查看出版社编号，出版社名称，书名称，书单价，作者编号
select * from publishers p
inner join titles t on p.pub_id=t.pub_id
inner join titleauthor ta on t.title_id=ta.title_id
go
--5,查看出版社编号，书总价，书最高价格，作者总数
---5分析
--这本书(TC7777)有两个作者（472-27-2349,672-71-3249）
--这个作者（486-29-1786） 出了两本书（PC9999,PS7777）

--5_1,错的
select p.pub_id,SUM(price),MAX(price),count(title_id) from publishers p
inner join titles t on p.pub_id=t.pub_id
group by p.pub_id
go
--5_2,正确的
select p.pub_id,SUM(price),MAX(price),count(distinct au_id) from publishers p
inner join titles t on p.pub_id=t.pub_id
inner join titleauthor ta on t.title_id=ta.title_id
group by p.pub_id
go

--6,查看出版社编号，书总价，书最高价格，要求作者总数大于9的信息
select pub_id,SUM(price),MAX(price) from titles t
inner join titleauthor ta on t.title_id=ta.title_id
group by pub_id
having count(distinct au_id)>9
go

--7,查找书名称，书编号，作者编号

go
--8,查找书名称，作者数量
select title,COUNT(au_id) '作者数量' from titles t
inner join titleauthor ta on t.title_id=ta.title_id
group by title
go
--9,查找商业书的所有书名称，作者数量,
select title,COUNT(au_id) '作者数量' from titles t
inner join titleauthor ta on t.title_id=ta.title_id
group by title
go
--10,查找商业书的所有作者姓名，作者编号，作者城市
select * from titles t
inner join titleauthor ta on t.title_id=ta.title_id
inner join authors a on ta.au_id=a.au_id
go
--11,在CA州的作者出的书，书名，书单价总和，
--书最高预付款，书的最低价格。
inner join titleauthor ta on t.title_id=ta.title_id
inner join authors a on ta.au_id=a.au_id
where state='CA'
group by title
go
--12,和商店的同一州作者出的书，书名，书单价总和，
--书最高预付款，书的最低价格。
--12_1,
select au_id from authors a
where state in(select state from stores where state=a.state)
go
--12_2,
select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state))
go
--12_3,
select * from titles t
where title_id in(select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state)))
go
--12_4,结论
where title_id in(select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state)))
group by title
go
--13,和商店的同一州作者出的书，书名，书单价总和，
--书最高预付款，书的最低价格,要
--求书必须大于所有商业书的价格。
--13_1,
select * from titles
where price>(select max(price) from titles where type='business')
go
--13_2,总结
where title_id in(select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state)))
and price>(select max(price) from titles where type='business')
group by title
go
--SQL脚本总结
--1，如果有条件“先做条件”
--2，多列先链表，再做条件
--3, 先做条件后排序
--3，分组常和聚合函数在一起。
--4，
--5，子查询，如果有已知条件用嵌套子查询，否则就是相关子查询。

--前（where 列条件）group by 后（having 组条件）

select * from titles
order by title_id desc

--================================
-- ylb:表的高级查询-子查询
--    pubs库的练习
-- 12/12/2011
--================================
use pubs
go
select * from authors
select * from titleauthor
select * from titles
select * from publishers
select * from stores
go
--1. 查找和出版商同一州的作者姓名。
select * from authors a
where state in(select state from publishers where state=a.state)
go
select * from authors a
where exists(select * from publishers where state=a.state)
go
--2. 查找和商店同一州的作者姓名
select * from authors a
where state in(select state from stores where state=a.state)
go
--3. 查找和商店同一城市的出版社名称
select * from publishers p
where city in (select city from stores where city=p.city)
go
--4. 查找写商业书的作者名
select * from authors
select * from titleauthor
select * from titles
go
--4_1,
select title_id from titles
go
--4_2,
select au_id from titleauthor
where title_id in('BU1032','BU1111','BU2075','BU7832')
go
--4_3,
select * from authors
where au_id in('213-46-8915','267-41-2394')
go
--4,结论
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
go
--5. 查找美国出版社出版的所有书
select * from publishers
select * from titles
go
--5_1,
select pub_id from publishers
where country='USA'
go
--5_2,
select * from titles
where pub_id in('0877','0736')
go
--5结论
select * from titles
where pub_id in(select pub_id from publishers
where country='USA')
go
--6. 查找美国出版社出版书的作者姓名
--6_1,
select pub_id from publishers
where country='USA'
go
--6_2,
select title_id from titles
where pub_id in('0877','0736')
go
--6_3,
select au_id from titleauthor
where title_id in('BU2075','MC2222')
go
--6_4,
select * from authors
where au_id in('213-46-8915','712-45-1867')
go
--6总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where pub_id in(select pub_id from publishers
where country='USA')))
go
--7. 查找在CA州出版社所出版的商业书作者姓名
--7-1,
select pub_id from publishers
where state='CA'
go
--7-2,
select title_id from  titles
where pub_id in('1389')
go
--7-3,
select au_id from titleauthor
where title_id in('BU1032','BU1111')
go
--7-4,
select * from authors
where au_id in('213-46-8915','409-56-7008')
go
--7总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from  titles
where pub_id in(select pub_id from publishers
where state='CA')
go
--P:8. 查找和出版社在同一州的作者所写的书名
--8_1,
select au_id from authors a
where state in(select state from publishers where state=a.state)
go
--8-2,
select title_id from titleauthor
where au_id in(select au_id from authors a
where state in(select state from publishers where state=a.state))
go
--8-3,
select * from titles
where title_id in(select title_id from titleauthor)
go

--8 结论
select * from titles
where title_id in(select title_id from titleauthor
where au_id in(select au_id from authors a
where state in(select state from publishers where state=a.state)))
go
--9. 查找和作者在同一城市的出版社名称
select * from publishers p
where city in(select city from authors where city=p.city)
go
--10. 查找单价大于所有商业书的书，它的作者姓名

--方法一、
--10-1,
select MAX(price) from titles where type='business'
go
--10-2a,
select title_id from titles
where price >(select MAX(price) from titles where type='business')
go
--10-2b,
select title_id from titles
where price > all(select price from titles where type='business')
go
--10-3,
select au_id from titleauthor
where title_id in(select title_id from titles
where price >(select MAX(price) from titles where type='business'))
go
--10总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where price >(select MAX(price) from titles where type='business')))
go

--11.   查找（Algodata Infosystems）出版社所在州，出过商业书的作者姓名
--11_1,
select pub_id from publishers
where pub_name='Algodata Infosystems'
go
--11-2,
select title_id from titles
where type='business' and pub_id =(select pub_id from publishers
where pub_name='Algodata Infosystems')
go
--11-3,
select au_id from titleauthor
where title_id in(select title_id from titles
where pub_id =(select pub_id from publishers
where pub_name='Algodata Infosystems'))
go
--11-4,
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where type='business' and pub_id =(select pub_id from publishers
where pub_name='Algodata Infosystems')))

SQL Server 子查询（嵌套子查询）和子查询（相关子查询）。

 1，ylb：1，子查询（嵌套子查询）返回顶部
-- =============================================
-- ylb：本案例的目的是：“嵌套子查询”
-- 11:25 2011/12/9
-- =============================================
use pubs
go
--一、子查询
--1，嵌套子查询
---特点：in里面的查询语句可以独立运行。
--P1:查询图书名是“Net Etiquette”的作者是谁？
--分析
select title_id from titles where title='Net Etiquette'
--title_id='PC9999'
go
select au_id from titleauthor
where title_id='PC9999'
--au_id='486-29-1786'
go
--小结
select au_id from titleauthor
where title_id in(select title_id from titles where title='Net Etiquette')
go
select * from authors
where au_id='486-29-1786'
--总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles where title='Net Etiquette'))

--测试1，
--P2:查看图书编号是‘PC9999’的出版社名称是？
select pub_id from titles
where title_id='PC9999'
go
select pub_name from publishers
where pub_id='1389'
go
--结论
select pub_name from publishers
where pub_id in(select pub_id from titles
where title_id='PC9999')
go
 1，ylb：2，子查询（相关子查询）返回顶部
-- =============================================
-- ylb：本案例探讨的是：“相关子查询”
-- 11:25 2011/12/9
-- =============================================
use pubs
go
--一、相关子查询
--P1:查询出版社和商店在同一个州的商店名称？
go
select * from publishers
select * from stores
go
--结论
select stor_name from stores s
where state in(select state from publishers where state=s.state)

go
--P2:查询出版社和作者在同一个的作者姓名？

select * from publishers
select * from authors
go
--结论
select * from authors a
where state in (select state from publishers where state=a.state)

SQL Server 多表的连接与练习（第三方关联表的应用）。

 1，多表的连接与练习（第三方关联表的应用）返回顶部
-- =============================================
-- ylb:
-- ylb目的：多表之间的连接，第三方关系的运用
--       练习和使用
-- 13:13 2011/12/7
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'db'
)
DROP DATABASE db
GO

CREATE DATABASE db
GO
go
use db
go
--1,课程表
create table course
(
c_no int identity(1,1) primary key, --编号
c_name varchar(20) not null,  --课程名称
c_time int check(c_time>=0)  --课时
)
go
--2，学生表
create table stu
(
s_no int identity(1,1) primary key, --编号
s_name varchar(10) not null,  --姓名
age int check(age>=15 and age<=80) --年龄
)
go
--3，成绩表
create table sco
(
s_no int not null references stu(s_no), --学生编号
c_no int not null references course(c_no), --课程编号
sco int     --成绩
)
go
--联合主键
alter table sco add constraint pk_s_c primary key(s_no,c_no)
go
insert into course values('计算机软件基础',16)
insert into course values('C语言程序设计',64)
insert into course values('网站设计综合技术',56)
insert into course values('SQL Server',32)
insert into course values('ASP',64)
insert into course values('JAVA',100)
insert into course values('高级SQL Server',16)
insert into course values('ASP DoNet',100)
select * from course
go
insert into stu values('张三',29)
insert into stu values('李司',39)
insert into stu values('王二',22)
insert into stu values('麻子',21)
insert into stu values('赵钱',25)
insert into stu values('孙李',24)
insert into stu values('周吴',20)
insert into stu values('郑王',20)
insert into stu values('合乎',29)
insert into stu values('人五',21)
insert into stu values('人六',17)
go
select * from course
select * from stu
go
insert into sco values(1,1,70)
insert into sco values(1,2,74)
insert into sco values(1,3,60)
insert into sco values(1,4,69)
go
insert into sco values(2,1,60)
insert into sco values(2,2,71)
insert into sco values(2,3,56)
insert into sco values(2,4,78)
go
insert into sco values(3,1,50)
insert into sco values(3,2,57)
insert into sco values(3,3,74)
insert into sco values(3,4,72)
go
insert into sco values(4,1,55)
insert into sco values(4,2,45)
insert into sco values(4,3,65)
insert into sco values(4,4,72)
go
insert into sco values(5,6,75)
insert into sco values(6,6,85)
insert into sco values(7,6,82)
go
select * from course
select * from stu
select * from sco
go
--一、单表的查询
go
--1、查询年龄最大的三个同学的信息；
--2、在成绩表中，查询课程编号为1号的所有记录

--3、在成绩表中，查询课程编号为1号的纪录，并按成绩降序排序

--4、在成绩表中，查询1号学生的总分，平均分，最高分和最低分，

--5、在成绩表中，查询单科成绩最高的学号，课程编号和成绩

--6、在成绩表中，查询每个学生参加考试的总分及参加考试的数量

--7、在成绩表中，查询每个学生的总分，平均分，最高分，最低分，并在总分降序排序

--8、在成绩表中，查询总分最高的学生的学号，总分

--9、在成绩表中，查询学号为2号的学生的学号，总分，平均分

--10、在成绩表中，查询每一门课程的考试人数，总分，最高分，并按总分升序排序

--二、多表连接查询

--1、查询所有举行考试的课程名称；
select * from course
select * from sco
go
select * from course c inner join sco s
on c.c_no=s.c_no
go
--结果
select distinct c_name from course c inner join sco s
on c.c_no=s.c_no
go
--2、查询所有举行考试的课程名称以及对应的学号和分数；
select c_name,s_no,sco from course c inner join sco s
on c.c_no=s.c_no
go
--3、查询所有参加考试的学生姓名以及其对应的课程编号和分数；
select s_name,c_no,sco from stu st inner join sco s
on st.s_no=s.s_no
go
--4、查询每个参加考试的学生的姓名，考试科目数量，总分，平均分；
select s_name,COUNT(*) '考试科目数量',SUM(sco) '总分',AVG(sco) '平均分'
from stu inner join sco
on stu.s_no=sco.s_no
group by s_name
go
--5、查询总分最高的同学的姓名，和总分
select top 1 s_name,SUM(sco) from stu inner join sco
on stu.s_no=sco.s_no
group by s_name
order by SUM(sco) desc

go
--6、查询总分前三名的同学的姓名
select top 3 s_name from stu inner join sco
on stu.s_no=sco.s_no
group by s_name
order by SUM(sco) desc
go
--7、查询参加考试的同学的姓名，科目名称及分数；
select s_name,c_name,sco from stu inner join sco
on stu.s_no=sco.s_no
inner join course
on sco.c_no=course.c_no
go
--8、查询 张三同学 的姓名，年龄，课程编号，成绩，及对应的课程名称
select s_name,age,sco.c_no,sco from stu inner join sco
on stu.s_no=sco.s_no
inner join course
on sco.c_no=course.c_no
where s_name='张三'
go
--9、查询 张三同学的姓名参加考试的总分，平均分以及参加考试的数量
select SUM(sco) '总分',AVG(sco) '平均分',COUNT(*) '考试数量' from stu
inner join sco
on stu.s_no=sco.s_no
inner join course
on sco.c_no=course.c_no
where s_name='张三'
go
--10、查询所有学生的姓名，以及其对应的分数；
select s_name,sco from stu
inner join sco on stu.s_no=sco.s_no
go
--11、查询所有的课程名称，以及其对应的学号和成绩；
select c_name,s_no,sco from course
inner join sco on course.c_no=sco.c_no
go
--12、查所有的同学的姓名，其对应的成绩，以及所有课程名称；
select s_name,sco,c_name from stu
inner join sco on stu.s_no=sco.s_no
inner join course on sco.c_no=course.c_no
go
--13、假设，所有同学都参加所有课程的考试，那么总共需要多少份试卷；
select COUNT(*) from course cross join stu

go
--左外连接
select * from stu
left join sco on stu.s_no=sco.s_no


# 3，面试题范例三

第一题：

S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄
C(C#,CN)C#,CN分别代表课程编号，课程名称
SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩
(1)使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
(4) 查询选修了课程的学员人数?
(5) 查询选修课程超过5门的学员学号和所属单位?

drop table S;
drop table C;
drop table SC;

create table S
(
S# varchar(10),
SN varchar (25),
SD varchar (25),
SA int
)

create table C
(
C# varchar(10),
CN varchar (25)
)

create table SC
(
S# varchar(10),
C# varchar(10),
G   int
Primary Key(S#, C#)
)
insert into S values ('10001','Students1','department1',23)
insert into S values ('10002','Students2','department1',24)
insert into S values ('10003','Students3','department2',25)
insert into S values ('10004','Students4','department2',26)
insert into S values ('10005','Students5','department3',23)
insert into S values ('10006','Students6','department3',24)
insert into S values ('10007','Students7','department3',25)
insert into S values ('10008','Students8','department4',25)

insert into C values ('C1','数学')
insert into C values ('C2','物理')
insert into C values ('C3','化学')
insert into C values ('C4','英语')
insert into C values ('C5','中文')
insert into C values ('C6','税收基础')
insert into C values ('C7','传媒')
insert into C values ('C8','日语')

insert into SC values ('10001','C1',67)
insert into SC values ('10001','C2',77)
insert into SC values ('10001','C3',87)
insert into SC values ('10001','C4',97)
insert into SC values ('10001','C5',57)
insert into SC values ('10001','C6',47)

insert into SC values ('10002','C1',62)
insert into SC values ('10002','C2',72)
insert into SC values ('10002','C3',82)
insert into SC values ('10002','C4',92)
insert into SC values ('10002','C5',52)
insert into SC values ('10002','C6',42)
insert into SC values ('10004','C2',74)
insert into SC values ('10004','C5',54)
insert into SC values ('10004','C6',44)

--（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?

--解法一:
select S#,SN from S where S# in (select S# from C, SC where C.C#=SC.C# and C.CN='税收基础')
--解法二:
select S.S#,S.SN from S inner join (select S# from C left join SC on C.C#=SC.C# where C.CN='税收基础') T on T.S#=S.S#

--(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?

--解答：
select S.SN,S.SD from S,SC where S.S#=SC.S# and SC.C#='C2'

--(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?

--解答：
select distinct S.SN,S.SD from S where S.S# not in (select S.S# from S,SC where S.S#=SC.S# and SC.C#='C5')

--(4) 查询选修了课程的学员人数?

--解法一:
select 学员人数=count(distinct s#) from sc
--解法二:
select count(*) as 学员人数 from (select distinct SC.S# from SC) t

--(5) 查询选修课程超过5门的学员学号和所属单位?

--解法一:
select S#,SD from S where S.S# in (select SC.S# from SC group by SC.S# having count(*)>5)
--解法二:
select S#,SD from S where S# in(select S# from SC group by S# having count(distinct C#)>5)

create table testtable1
(
id int IDENTITY,
department varchar(12)
)

insert into testtable1 values('设计')
insert into testtable1 values('市场')
insert into testtable1 values('售后')

id department
1   设计
2   市场
3   售后

create table testtable2
(
id int IDENTITY,
dptID int,
name varchar(12)
)
insert into testtable2 values(1,'张三')
insert into testtable2 values(1,'李四')
insert into testtable2 values(2,'王五')
insert into testtable2 values(3,'彭六')
insert into testtable2 values(4,'陈七')
insert into testtable2 values(5,'陈七')

select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2 left join testtable1 t1 on t1.id=t2.dptID

select * from testtable2

id dptID department name
1   1      设计        张三
2   1      设计        李四
3   2      市场        王五
4   3      售后        彭六
5   4      黑人        陈七

--解答：
--解法一:
select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2 left join testtable1 t1 on t1.id=t2.dptID
--解法二:
SELECT t2.id , t2.dptID, ISNULL(t1.department,'黑人') dptName,t2.name FROM testtable1 t1 right join testtable2 t2 on t2.dptID = t1.ID

--注意下面两个语句查询结果与上面答案的区别
select t2.id,t2.dptID,t1.department,t2.name from testtable1 t1,testtable2 t2 where t1.id=t2.dptID
select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2 inner join testtable1 t1 on t1.id=t2.dptID

A: p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03

p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8

create table A
(
p_ID int,
p_Num int,
s_id int
)

insert into A values(1,10,01)
insert into A values(1,12,02)
insert into A values(2,8,01)
insert into A values(3,11,01)
insert into A values(3,8,03)

--解答：
select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id,
sum(case when s_id=2 then p_num else 0 end) as s2_id,
sum(case when s_id=3 then p_num else 0 end) as s3_id
from A group by p_id

--1.查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列？

create table A
(
id int IDENTITY,
Name varchar (25)
)

--1.查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列？
--解答：
select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by id ) T) order by id

--查询A(ID,Name)表中存在ID重复三次以上的记
drop table A
create table A
(
id int,
Name varchar (25)
)

insert into A values(1,'a')
insert into A values(2,'a')
insert into A values(3,'a')
insert into A values(1,'a')
insert into A values(2,'a')
insert into A values(3,'a')
insert into A values(4,'a')
insert into A values(1,'a')
--解答：
select id,name from A where id in (select id from A group by id having count(id)>3)order by id

courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------

courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------

create table Course(
courseid int IDENTITY,
coursename varchar (25),
score int
)

insert into Course values ( 'java',70)
insert into Course values ( 'oracle',90)
insert into Course values ( 'xml',40)
insert into Course values ( 'jsp',30)
insert into Course values ( 'servlet',80)

--解答：
--oracle：
select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course

--SQL Server：
select *, (case when score<60 then 'failed' else 'pass' end) as mark from Course

有表：emp(id, name, age)
要求：列出所有名字重复的人的记录?

create table emp(
id int IDENTITY,
name varchar (25),
age int
)

insert into emp values('Zhang1',26)
insert into emp values('Zhang2',27)
insert into emp values('Zhang3',28)
insert into emp values('Zhang1',26)
insert into emp values('Zhang2',27)
insert into emp values('Zhang3',29)
insert into emp values('Zhang1',26)
insert into emp values('Zhang2',27)
insert into emp values('Zhang3',28)
insert into emp values('Zhang1',26)
insert into emp values('Zhang4',22)
insert into emp values('Wang1',27)
insert into emp values('wang2',28)
insert into emp values('Wang2',26)
insert into emp values('Wang1',22)

--列出所有名字重复的人的记录?
--解法一：要知道所有名字有重复人资料，首先必须知道哪个名字重复了：
select id,name,age from emp where name in (select name from emp group by name having count(*)>1)

--解法二：如果对每个名字都和原表进行比较，大于2个人名字与这条记录相同的就是合格的 ，就有：
select * from emp where (select count(*) from emp e where e.name=emp.name)>1

--解法三：如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求：
select * from emp where exists (select * from emp e where e.name=emp.name and e.id<>emp.id)
--或：
select distinct emp.* from emp inner join emp e on emp.name=e.name and emp.id<>e.id

Tom   16
Sun   14
Tom   16
Tom   16

create table emp(
name varchar(20),
age int
)

insert into emp values('Tom',16)
insert into emp values('Sun',14)
insert into emp values('Tom',16)
insert into emp values('Tom',16)

--解法一：通过distinct、group by过滤重复：
select distinct * from emp

select name,age from emp group by name,age

--获得需要的数据，如果可以使用临时表就有解法：
select distinct * into #tmp from emp
delete from emp
insert into emp select * from #tmp

--但是如果不可以使用临时表，那该怎么办？
alter table emp add chk int identity(1,1)
--重复记录可以表示为：
select * from emp where (select count(*) from emp e where e.name=emp.name)>1
--要删除的是：
delete from emp where (select count(*) from emp e where e.name=emp.name and e.chk>=emp.chk)>1
--再把添加的列删掉，出现结果。
alter table emp drop column chk

--)另一个思路：视图
select min(chk) from emp group by name having   count(*)   >1
--获得有重复的记录chk最小的值,于是可以
delete from emp where chk not in (select min(chk) from emp group by name)

001 Tom 17
002 Sun 14
003 Tom 15
004 Tom 16

create table emp(
emp_no int,
name varchar(20),
age int
)

insert into emp values(001,'Tom',17)
insert into emp values(002,'Sun',14)
insert into emp values(003,'Tom',15)
insert into emp values(004,'Tom',16)

--(1)最简单的方法：

alter table emp add chk int identity(1,1)
--或
select *,identity(int,1,1) chk into #tmp from emp
select * from emp
alter table emp drop column chk

--如果需要控制顺序怎么办？
select *,identity(int,1,1) chk into #tmp from emp order by age
delete from emp
alter table emp add chk int
insert into emp select * from #tmp
select * from #tmp
drop table #tmp

--(2)假如不可以更改表结构，怎么办？

select emp.*,(select count(*) from emp e where e.emp_no<=emp.emp_no) from emp order by (select count(*) from emp e where e.emp_no<=emp.emp_no)

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

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

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

create table course(
Name varchar(25),
CName varchar(25)
)

insert into course values ('张三','数学')
insert into course values ('张三','物理')
insert into course values ('张三','语文')
insert into course values ('张三','化学')

insert into course values ('李四','数学')
insert into course values ('李四','语文')
insert into course values ('李四','化学')

insert into course values ('王五','数学')
insert into course values ('王五','物理')
insert into course values ('王五','语文')

insert into course values ('赵四','数学')
insert into course values ('赵四','物理')
insert into course values ('赵四','语文')

insert into course values ('周七','数学')
insert into course values ('周七','物理')

select * from course

--问题一：只选数学，物理，语文的学生, 查询结果如下,写出相应SQL语句------

--解法一：
select A.Name,B.CName from
(select T.Name from (select Name,CName from Course where CName in('数学','物理','语文'))T group by Name having count(*)=3 )A,
(select Name,CName from Course where CName in('数学','物理','语文'))B
where A.Name=B.Name
and A.Name not in (select Name from Course group by Name having count(*)>3 )
--解法二：
select * from course
where name in (select name from course where CName in('数学','物理','语文') group by name having count(*)=3)
and name not in(select name from course group by name having count(*)>3)

--问题二：同时选了数学，物理，语文的学生, 查询结果如下,写出相应SQL语句---
--解法一：
select A.Name,B.CName from
(select T.Name from (select Name,CName from Course where CName in('数学','物理','语文'))T group by Name having count(*)=3 )A,
(select Name,CName from Course where CName in('数学','物理','语文'))B
where A.Name=B.Name

--解法二：
select * from course
where name in (select name from course where CName in('数学','物理','语文') group by name having count(*)=3)

第十一题：

create table students (
name varchar(25),
class varchar(25),
)

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)

create table students (
name varchar(25),
class varchar(25),
)

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)

--解答：
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,
u_id int
)
create table checkinfo
(
id int,
checktype varchar(32) --出勤的类型（正常，病假，事假）
u_id int
)

create table userinfo
(
id int,
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：按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量

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列的

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

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

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表字段(
id --部门编号
departmentName-部门名称
)
b表字段(
id--部门编号
employee- 员工名称
)

*/
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

1 a
2 b
3 c
4 d

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
bc
bd
cd
--参考答案：
select a.name+b.name from B a,B b
where a.id<b.id group by a.name+b.name

第二十题：

id   name
--------
1    test1
2    test2
3    test3
4    test4
5    test5
6    test6
2    test2
3    test3
2    test2
6    test6

create table D(
id varchar (20),
name varchar (20)
)

insert into D values('1','test1')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('6','test6')
insert into D values('5','test5')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('7','test7')
insert into D values('4','test4')
insert into D values('6','test6')
insert into D values('5','test5')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('8','test8')
insert into D values('10','test4')

select * from D where

--解法一:
--查询出重复的记录
select id,name from D group by id,name having count(*)>1
--查询出重复的记录及重复次数
select a.id,a.name from D a,(select id,name from D group by id,name having count(*)>1) b where a.id=b.id and a.name=b.name

--解法二:
select t1.* from D t1,
(select sum(1) as Sig,id,name from D group by id,name) as t2
where t1.name=t2.name and t1.id=t2.id and t2.Sig>1

year salary
2000 1000
2001 2000
2002 3000
2003 4000

year salary
2000 1000
2001 3000

create table t_salary(
year int,
salary int
)

select * from t_salary
insert into t_salary values(2000,1000)
insert into t_salary values(2001,2000)
insert into t_salary values(2002,3000)
insert into t_salary values(2003,4000)

--解答：
select year, (select sum(salary) from t_salary b where b.year <= a.year) salary from t_salary a group by year

year month total
1996 1 3000
1996 3 4000
1996 7 5000
1997 3 4000
1997 6 3000
.

year m1,m2,m3,m4
year 为年，m1等为季度，要求按上行输出

create table Outputs (
year char(4),
month int,
total int
)

insert into Outputs values ('1996',1,3000)
insert into Outputs values ('1996',3,4000)
insert into Outputs values ('1996',7,5000)

insert into Outputs values ('1997',3,4000)
insert into Outputs values ('1997',6,3000)
insert into Outputs values ('1997',2,3000)

insert into Outputs values ('1998',1,3000)
insert into Outputs values ('1998',4,3500)

select * from Outputs

----------------解答-------------------
--解法一：
select year,
sum(case when month<=3 then total else 0 end) as M1,
sum(case when month>3 and month<=6 then total else 0 end) as M2,
sum(case when month>6 and month<=9 then total  else 0 end) as M3,
sum(case when month>9 and month<=12 then total else 0 end) as M2
from Outputs group by year

--解法二：
select year,
sum(case when month in(1,2,3) then total else 0 end) as M1,
sum(case when month in(4,5,6) then total else 0 end) as M2,
sum(case when month in(7,8,9) then total else 0 end) as M3,
sum(case when month in(10,11,12) then total else 0 end) as M2
from Outputs group by year

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

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

create table tb(

insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
insert into tb values('李四' , '历史' , 94)
go

-----解法一：SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)---------
select 姓名,
max(case when 课程='语文' then 分数 end) as 语文,
max(case when 课程='数学' then 分数 end) as 数学,
max(case when 课程='物理' then 分数 end) as 物理
from tb
group by 姓名

-----解法二：--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同) ---------
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)

-----解法三：SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

-----解法四：SQL SERVER 2005 静态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

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

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

--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名

--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')

drop table tb

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

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

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

create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go

--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end

--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')

--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t

--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。

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

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

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

select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end

ID NAME           ID NAME
----------        ------------------
1 A5              1 A5,A8,AF....
2 A8              2 B5,B3,BD....
3 AF              3 C3,CK,CI....
4 B5
5 B3
6 BD
7 C3
8 CK
9 CI

create table leftTable(
id int indentity,
name varchar(20)
)

create table rightTable(
id int indentity,
name varchar(20)
)

insert into leftTable values('A5')
insert into leftTable values('A8')
insert into leftTable values('AF')
insert into leftTable values('B5')
insert into leftTable values('B3')
insert into leftTable values('BD')
insert into leftTable values('C3')
insert into leftTable values('CK')
insert into leftTable values('CI')

--本题答案征集中................

--如何删除SQL表中重复的记录,除ID值不一样外其它字段都一样,每两行记录重复

create table duplicateTable(
id int IDENTITY,
name varchar(10),
class varchar(10),
nationality varchar(30)
)

--解答：
delete t from duplicateTable t where exists(select 1 from duplicateTable where name=t.name and class=t.class and address=t.address and nationality=t.nationality and id>t.id)

# 4，面试题范例四

1.一道SQL语句面试题，关于group by

2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

胜负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))

insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')
1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001
2.请教一个面试中遇到的SQL语句的查询问题

------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name

3.面试题：一个日期判断的sql语句？

------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文          数学           英语
及格          优秀           不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table

7.请用一个sql语句得出结果

table1

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

table2

--------------------------------
01      国内业务一部
02      国内业务二部
03      国内业务三部
04      国际业务部

table3 （result）

--------------------------------------
01      10        null      null
02      10         8        null
03      null       5        8
04      null      null      9
------------------------------------------
1)
select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部门dep = b.部门dep and b.月份mon = '一月份' and
a.部门dep = c.部门dep and c.月份mon = '二月份' and
a.部门dep = d.部门dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep

8.华为一道面试题

------------------------------------------
select id, Count（*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1

表名：成绩表

……
（其他用户实验的记录大家可自行插入）

select * from score
where s_name not in
(select s_name from score
where score<60)

select * from score where s_name in
(select s_name from score
group by s_name
having min(score)>=60)

（3）表名：商品表

……
（其他用户实验的记录大家可自行插入）

select 名称 from 商品表 group by 名称 having avg(进价) < 2
（4）表名：高考信息表

2006001     语文       119
2006001     数学       108
2006002     物理       142
2006001     化学       136
2006001     物理       127
2006002     数学       149
2006002     英语       110
2006002      语文       105
2006001      英语        98
2006002     化学       129
……
（其他用户实验的记录大家可自行插入）

select 准考证号 from 高考信息表 group by 准考证号 having sum(成绩) > 600
（5）表名：高考信息表

2006001                108         119         98        127         136
2006002                149         105        110        142         129
……
（其他用户实验的记录大家可自行插入）

select 准考证号 from 高考信息表 where (数学+语文+英语+物理+化学) > 600
(四部分)
（一）表名：club
id gender age
67 M      19
68 F      30
69 F      27
70 F      16
71 M      32
……（其余测试数据请自行插入）

select gender,count(id) from club group by gender
（二）表名：team
ID(number型) Name(varchar2型)
1                  a
2                  b
3                  b
4                  a
5                  c
6                  c

ID(number型) Name(varchar2型)
1                  a
2                  b
5                  c

delete from team where id not in
(
select min(a1.id) from team a1
where a1.name=team.name )
delete from team where id not in
(
select min(id) from team group by name)
（三）表名：student
name course score

select * from student where name in
(select name from student
where name like '张%'
group by name having avg(score) > 75)

11-01 2182
06-26 394
04-09 209
07-14 2万+
05-08 7223
07-31 2057
06-12 309
05-08 9719
02-21 359
04-19 812
08-16 4978
11-09 4233
12-30 3万+
09-11 1826
07-30 5万+
07-18 2万+
12-02 2342

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。