朋友的文思数据仓库笔试题

朋友的文思数据仓库笔试题

下面三道题目是我一个去文思创新面试数据仓库职位的朋友带回来的笔试题。号称税后12k的职位，大家也可以尝试一下，当然不是写出来就有12k，但是要写不出来就肯定没有。。。下面写下自己的大概思路和解法。

Requirement:

1. Use T-SQL answer these questions.

2. Your answer can be ran by SSMS

3. Using your best way answer the question

4. Code must be concise and readable

SQL Questions:

/*

1. Write a query to get the EmployeeID, EmployeeName and Salary for the 4th, 5th and 6th for salaries ranked

in the descending order from the @employee table below.

Note: If there are multiple solutions to the problem, choose the best and justify why it is the best.

Sample Resultset:

EmployeeID 

EmployeeName      

Salary          

Rank

1

Albert Einstein

150000.00

2

2

Isaac Newton

130000.00

3

5

Galileo Galilei

120000.00

4

declare @Employee table

      (EmployeeID int,

       EmployeeName varchar(30),

       Salary money)

 

insert into @Employee(EmployeeID, EmployeeName, Salary)

select 1, 'Albert Einstein', 150000 union all

select 2, 'Isaac Newton', 130000 union all

select 3, 'Michael Faraday', 100000 union all

select 4, 'Graham Bell', 160000 union all

select 5, 'Galileo Galilei', 120000 union all

select 6, 'Stephen Hawking', 80000 union all

select 7, 'Ernest Rutherford', 90000 union all

select 8, 'Blaise Pascal', 100000 union all

select 9, 'Niels Bohr', 70000 union all

select 10, 'Conrad Roentgen', 110000

/*

 

2. Using the @mediatype table, write a query to return the mediatypes and its parent level mediatypes

Note: If there are multiple solutions to the problem, choose the best and justify why it is the best.

Sample Resultset:

ID

Name

ParentID

ParentName

Level

-1

Unknown / Not Used

NULL

 

0

1

Offline

NULL

 

0

3

Print

1

Offline

1

4

Broadcast

1

Offline

1

2

Online

NULL

 

0

5

Website

2

Online

1

6

Email

2

Online

1

7

Magazine

3

Print

2

 

*/

declare @mediatype table

 

(

      MediaTypeID int not null,

      ParentMediaTypeID int null,

      MediaTypeName nVarchar(120) not null

)

 

insert into @mediatype(MediaTypeID, ParentMediaTypeID, MediaTypeName)

select -1, null, 'Unknown / Not Used' union all

select 1, null, 'Offline' union all

select 2, null, 'Online' union all

select 3, 1, 'Print' union all

select 4, 1, 'Broadcast' union all

select 5, 2, 'Website' union all

select 6, 2, 'Email' union all

select 7, 3, 'Magazine' union all

select 8, 3, 'Newspaper' union all

select 9, 3, 'Mailer' union all

select 10, 4, 'TV'

 

 

SSIS Question:

 

/*

 

Create an SSIS package that extracts the data from source to destination.

Use the ProductType table created below as the source.

The data should be split between 2 destination tables, one to store the active records (ActiveBool=1)

and the other for inactive records (ActiveBool=0) during extract.

The source and the destination tables should reside in separate databases.

The package should be able to be deployed in any environment and run successfully.

The data should be extracted based on the UpdateDate in ProductType table, and should have the ability to

take the criteria start and end dates as parameters.

The package should also be able to log the execution activity.

Note: If there are multiple solutions to the problem, choose the best and justify why it is the best.

 

*/

 

if object_id('ProductType') is not null

begin

      drop table ProductType

end

 

create table ProductType(

      ProductTypeID int not null primary key,

      ProductTypeName varchar(120) not null,

      ProductTypeLabel char(3) not null,

      ActiveBool bit not null,

      UpdateDate datetime not null

)

 

insert into ProductType(ProductTypeID, ProductTypeName, ProductTypeLabel, ActiveBool, UpdateDate)

select -1, 'Unknown/Not Used', 'XXX', 0, '2010-05-04 17:23:12.253' union all

select 0, 'General', 'GEN ', 1, '2010-02-11 23:13:00.000' union all

select 1, 'Air', 'AIR', 1, '2009-11-24 22:24:54.777' union all

select 2, 'Hotel', 'HTL', 1, '2010-08-25 18:23:49.460' union all

select 3, 'Package', 'BOP', 0, '2009-11-24 22:24:54.777' union all

select 4, 'Car', 'CAR', 1, '2009-11-24 22:25:07.823' union all

select 5, 'Train', 'TRN', 1, '2009-11-24 22:25:19.013' union all

select 6, 'Insurance', 'INS', 0, '2010-02-11 23:13:00.000' union all

select 7, 'Destination Services', 'DST', 1, '2010-02-11 23:13:00.000' union all

select 8, 'Pre-Package Vacations', 'PPV', 0, '2010-02-11 23:13:00.000' union all

select 9, 'Cruise', 'CRZ', 1, '2010-03-09 21:14:03.750' union all

select 10, 'Other', 'OTH', 0, '2010-08-25 18:23:49.470'

 

下面看下我个人的解题思路，跟大家分享一下

第一题：很简单，考察rank 函数。Sql server提供的排序函数 有 rank() 、dense_rank()、row_number()等。题目没有明确排名相等时如何处理，所以可以随意选择一个使用。如下：

select EmployeeID, EmployeeName, Salary , rank() over(order by salary desc) from @Employee

 

 

第二题：想了一下，没有想出用一条语句写出来的方法。如果哪位有好办法可以给我留言，这第二题主要就是获得level。解法如下：

create function getlevel(@m int)

returns int

begin

declare @mc int

set @mc = -1

while @m is not null

    begin

       select @m = parentMediaTypeId from  mediatype where mediaTypeid = @m

       set @mc = @mc + 1

    end

return @mc

end

 

select c.MediaTypeID , c.MediaTypeName , c.ParentMediaTypeID , p.MediaTypeName , dbo.getlevel(c.MediaTypeID) from

mediatype c left join mediatype p on c.ParentMediaTypeID = p.MediaTypeID

先写一个自定义函数，然后下面通过一条语句进行查询，如果哪位能直接select出来，欢迎给我留言，共同学习。。

 

 

第三题：看上去难度不大，也没法贴代码，大家自己写写看啦。