Microsoft Interview Question
Software Engineer / DevelopersCountry: United States
Interview Type: Phone Interview
;with cte as (
select oam.*, o.obj_name, a.att_name from objattmapping oam
inner join obj o
on oam.obj_id = o.obj_id
inner join att a
on oam.att_id = a.att_id
)
select obj_id, obj_name, max([color]),max([height]), max([length]) ,max([width]) from cte
pivot (max(att_value) for att_name in ([color],[height], [length] ,[width]) ) p
group by obj_id, obj_name
CREATE TABLE Object (obj_id int key, obj_name VARCHAR(10));
CREATE TABLE Attribute (att_id int key, att_name VARCHAR(10));
CREATE TABLE ObjectAttributeMapping (objAtt_id int key, obj_id int, att_id int, att_value VARCHAR(10));
INSERT INTO Object Values (1, 'cube'), (2, 'square'), (3, 'matrix');
INSERT INTO Attribute VALUES (1, 'color'), (2, 'height'), (3, 'length'), (4, 'width');
INSERT INTO ObjectAttributeMapping Values (1, 1, 1, 'red'), (2, 1, 2, '10'), (3, 1, 3, '12'), (4, 1, 4, '5'), (5, 2, 1, 'green'), (6, 2, 2, '6'), (7, 3, 3, '5'), (8, 3, 4, '9');
SELECT O.obj_id, O.obj_name, (SELECT OAc.att_value FROM Object Oc NATURAL JOIN ObjectAttributeMapping OAc NATURAL JOIN Attribute Ac WHERE Ac.att_name='color' AND Oc.obj_id=O.obj_id) color, (SELECT OAc.att_value FROM Object Oc NATURAL JOIN ObjectAttributeMapping OAc NATURAL JOIN Attribute Ac WHERE Ac.att_name='height' AND Oc.obj_id=O.obj_id) height, (SELECT OAc.att_value FROM Object Oc NATURAL JOIN ObjectAttributeMapping OAc NATURAL JOIN Attribute Ac WHERE Ac.att_name='length' AND Oc.obj_id=O.obj_id) length, (SELECT OAc.att_value FROM Object Oc NATURAL JOIN ObjectAttributeMapping OAc NATURAL JOIN Attribute Ac WHERE Ac.att_name='width' AND Oc.obj_id=O.obj_id) width FROM Object O;
select * from
(
select o.Obj_id,Obj_name,Att_name,Att_value from Object1 o cross join Attribute a
left outer join ObjectAttributeMapping oa
on o.Obj_id=oa.Obj_id and a.Att_id=oa.Att_id
) A
PIVOT
(
Min(Att_value)
FOR
Att_name
IN( [color],[height],[length],[width])
)PivotTable
order by Obj_id
select
distinct
ob.id as object_id
,ob.name as object_name
,A.color
,A.height
,A.length
,A.width
from ob
left outer join
(select
objatt.obid
,string_agg(case when att.name='color' then attvalue else null end,'') as color
,string_agg(case when att.name='hight' then attvalue else null end,'') as height
,string_agg(case when att.name='lenght' then attvalue else null end,'') as length
,string_agg(case when att.name='width' then attvalue else null end,'') as width
from objatt
left outer join att on
objatt.attid=att.id
group by objatt.obid) A on
ob.id=A.obid
CREATE TABLE ob
- Anonymous July 14, 2015( ID integer PRIMARY KEY,
name varchar(50) NOT NULL,
);
insert into ob values(1,'Cube')
insert into ob values(2,'Sqr')
insert into ob values(3,'Matrix')
Create table att
( ID integer Primary key,
name varchar(50) not null
)
insert into att values(1,'color')
insert into att values(2,'hight')
insert into att values(3,'lenght')
insert into att values(4,'width')
Create table objatt
(ID integer Primary key,
obid integer Foreign key REFERENCES ob(id) ,
attid integer Foreign key REFERENCES att(id) ,
attvalue varchar(50) not null
)
insert into objatt values(1,1,1,'red')
insert into objatt values(2,1,2,10)
insert into objatt values(3,1,3,12)
insert into objatt values(4,1,4,5)
insert into objatt values(5,2,1,'green')
insert into objatt values(6,2,2,6)
insert into objatt values(7,3,3,5)
insert into objatt values(8,3,4,9)
Query:
select id,name,
max(case when attid = 1 then attvalue end) color,
max(case when attid = 2 then attvalue end)hight,
max(case when attid = 3 then attvalue end)lenght,
max(case when attid = 4 then attvalue end)width
from(select ob.id, ob.name, objatt.attvalue, objatt.attid
from ob left outer join objatt on ob.id = objatt.obid
)d
group by id,name