SQL Interview Questions

Scenario 1:
------------
I have audit table in my database and i need to find who has updated the table recently..

I mean to say..

If there is a table A and it has updated/accessed by many users during the day. But i need to know who has lastly update the table?

Eg:
TableId Tablemodified , UserName
-------------- ---------------------------
A,'2015-04-04 13:33:45', 'venkat'
A,'2015-04-04 14:23:35','priya'
A,'2015-04-04 18:03:15','vijay'

B, '2015-04-04 11:13:05' , 'vijay'
B,'2015-04-04 16:13:14' , 'suresh'

Now the O/P should be

TableName LastAccessedTime
-------------- ---------------------------

A,,'2015-04-04 18:03:15' , 'vijay'

B,'2015-04-04 16:13:14', 'suresh'

 Query:
----------------
select * from Labs
select * from (
select row_number() over ( partition by tableid order by Tablemodified desc )rank, tableid,userName, Tablemodified from Labs)
a
where a.rank = 1

Scenario 2:
------------
I have audit table in my database and i need to find who has updated the table recently..

I mean to say..

If there is a table A and it has updated/accessed by many users during the day. But i need to know who has lastly update the table?

Eg:
TableId Tablemodified , UserName
-------------- ---------------------------
A,'2015-04-04 13:33:45', 'venkat'
A,'2015-04-04 14:23:35','priya'
A,'2015-04-04 18:03:15','vijay'

B, '2015-04-04 11:13:05' , 'vijay'
B,'2015-04-04 16:13:14' , 'suresh'

Now the O/P should be

TableName LastAccessedTime
-------------- ---------------------------

A,,'2015-04-04 18:03:15' , 'vijay'

B,'2015-04-04 16:13:14', 'suresh'

 Query:
----------------
select * from Labs
select * from (
select row_number() over ( partition by tableid order by Tablemodified desc )rank, tableid,userName, Tablemodified from Labs)
a where a.rank = 1


Scenario 3 :  Remove duplicate rows from a table
---------------------------------------------------------------

Assuming we have a duplicate records in a table employee as follows
------------------------------------------------------------------------------------

create table employee(
empid int,
ename varchar(50),
phone varchar(20)
)

Go

insert into employee values(1, 'Jack', '555-55-5555');
insert into employee values (2, 'Joe', '555-56-5555');
insert into employee values (3, 'Fred', '555-57-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');
insert into employee values (1, 'Jack', '555-55-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6 ,'Lisa', '555-70-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');

SQL Server
-------------
DELETE SUB FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY EmpId, EmpName, EmpSSN ORDER BY EmpId) cnt
 FROM Employee) SUB
WHERE SUB.Cnt > 1;

Oracle

-----------

Delete from employee where rowid not in (select min(rowid) from employee group by EmpId, EmpName, EmpSSN order by EmpId)