) -- score: 8 select distinct stores.stor_id, stor_name from stores inner join sales on stores.stor_id = sales.stor_id inner join titles on sales.title_id = titles.title_id and titles.type = 'business' intersect select distinct stores.stor_id, stor_name from stores inner join sales on stores.stor_id = sales.stor_id inner join titles on sales.title_id = titles.title_id and titles.type = 'mod_cook' -- score: 10 select distinct st.stor_id,st.stor_name from stores st inner join sales ss1 on ss1.stor_id = st.stor_id inner join titles tit1 on ss1.title_id = tit1.title_id where tit1.[type] ='business' and exists ( select * from sales ss2 inner join titles tit2 on ss2.title_id = tit2.title_id where st.stor_id = ss2.stor_id and tit2.[type] ='mod_cook') -- 参考答案 select s.stor_id, st.stor_name from sales s join titles t on s.title_id = t.title_id and t.type in ('mod_cook', 'business') join stores st on s.stor_id = st.stor_id group by s.stor_id, st.stor_name having count(distinct t.type) = 2 select stor_id, stor_name from stores where exists ( select * from sales join titles on sales.title_id = titles.title_id where titles.[type] = 'business' and stores.stor_id = sales.stor_id) and exists ( select * from sales join titles on sales.title_id = titles.title_id where titles.[type] = 'mod_cook' and stores.stor_id = sales.stor_id) /**//* Question 8 How can I list non-contignous data?
In database pubs, I create a table test using statement as below, and I insert several row as below
create table test
( id int primary key ) go
insert into test values (1 ) insert into test values (2 ) insert into test values (3 ) insert into test values (4 ) insert into test values (5 ) insert into test values (6 ) insert into test values (8 ) insert into test values (9 ) insert into test values (11) insert into test values (12) insert into test values (13) insert into test values (14) insert into test values (18) insert into test values (19) go
Now I want to list the result of the non-contignous row as below,how can I do it?
Missing after Missing before ------------- -------------- 6 8 9 11... */
---- 参考答案
select max(a.id) as \min(b.id) as \from test a ,test b where a.id having min(b.id)-max(a.id)>1 How can I list all book with prices greather than the average price of books of the same type? In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books. Now I want to get the result as below: type title price ------------ --------------------------------------------------------------------------------- business The Busy Executive's Database Guide 19.9900............/--(1) Select type,price,title from titles Where Price > (select avg(price) from titles) 这样取得的是所有书的平均价格，而不是某一类书的平均价格。 --(2) with avgprice(type,price) as ( select type,avg(price) as price from titles t2 group by type) select distinct titles.type,titles.title,titles.price from titles inner join avgprice on (titles.type = avgprice.type and titles.price> avgprice.price) 使用CTE也可以实现，但如果取出的数据量很大会影响性能。为什么要用distinct呢？ --(3) select a.type, a.title, a.price from titles a where a.price > ( select avg (isnull (price, 0)) -- 有价格为null的情况，算平均值时应该算上价格为null的书本 from titles b where a.type = b.type) 因为没有具体提及统计的需求，但这样严谨的作风非常值得大家学习。 参考答案： select t.type, t.title, t.price from titles t where t.price > (select avg(price) from titles tt where tt.type = t.type) 题目内容如下 * 一表TB Id 字段为编号,递增不一定连续. M 字段为区段路费,比如从家到哈尔滨是60元,从哈尔滨到长春是70元,数值类型. S 字段站点名称. 现有500元钱,从家先经哈尔滨出发,能走多远? (例:60+70+80+50+90+75=425 60+70+80+50+90+75+80=505 所以答案应该是6武汉) Id M S 1 60 哈尔滨 2 70 长春 3 80 沈阳 4 50 北京 5 90 郑州 6 75 武汉 7 80 长沙 8 90 广东 要求,请用一句SQL语句实现 */ --创建表 Create Table TB ( Id Int Identity(1, 1) Not Null, M Int, S varchar(50) ) --测试数据 Insert Into TB Values(60, '哈尔滨') Insert Into TB Values(70,'长春') Insert Into TB Values(80,'沈阳') Insert Into TB Values(50,'北京') Insert Into TB Values(90,'郑州') Insert Into TB Values(75,'武汉') Insert Into TB Values(80,'长沙') Insert Into TB Values(90,'广东') ------------------------------------------------------------------------------------------------------- 实现方法两种 1. SELECT TOP 1 B.id, b.s, sum(A.m) s_sum FROM TB A, TB B WHERE A.ID <= B.ID GROUP BY b.id, b.s HAVING sum(a.m) <= 500 ORDER BY b.id DESC 2. SELECT TOP 1 ID, S, M_SUM FROM (SELECT *, (SELECT SUM(M) FROM TB WHERE ID >= 1 AND ID <= A.ID) AS M_SUM FROM TB A) B WHERE M_SUM <= 500 ORDER BY ID DESC SQL面试题 （二） 有一张工资表，包含三列：员工编号(ID),部门编号(GROUPS)，工资(SALARY) 1. .找到每个部门工资最高的人(包括并列第一) 2. 找到每个部门工资最高的人(只选一个) SQL语句如下： DECLARE @G TABLE(ID INT,GROUPS NVARCHAR(20),SALARY MONEY)