SQL Puzzle

What is the best way to resolve this task:

Each duck belongs to a particular species and lives in one of the several ponds. Each pond is described by its temperature and location city. Each species is described by two values: its thermal preferences and its temperature limit. Depending on their thermal preferences, duck from a given species may prefer temperatures not higher or not lower than the temperature limit. A duck of a certain species will only feel comfortable if the temperature in the pond fulfills its thermal preferences(i.e. it is above/below or equal to the species' limit temperature).

The column temp_preferences in table species determines whether temp_limit is minimum ("+") or maximum ("-") acceptable temperature for the given species. Rows in all tables are ordered by the column id.

For each pond we would like to count the ducks which live in it and fell comfortable.

You are giving 3 tables: species, ponds and ducks with the following structures:

create table species(

id integer not null,

temp_preferences varchar(1) check(temp_preferences in('+', '-')),

temp_limit integer not null,

  unique(id)

);

create table ponds (

id integer not null,

temperature integer not null,

city varchar(10),

unique(id)

);

create table ducks (

id integer not null,

name varchar(10),

species_id integer not null,

pond_id integer not null,

unique(id)

);

Write an SQL query that returns a table consisting of 2 columns: pond_id, happy_ducks, ordered by pond_id.

Every pond should appear in this table.

For example, for the following data:

species:

id    temp_prefernces    temp_limit

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

30    +                  15

40    -                  20

50    -                  31

ponds:

id   temperature    city

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

1    13             Oregon

2    31             Oregano

ducks:

id    name     species_id    pond_id

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

1     Martin   50            2

3     Bruno    30            1

9     Ignacio  40            2

27    Hedwig   40            1

81    Marina   30            2

Query should return:

pond_id    happy_ducks

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

1          1

2          2

2. For the following O/P we can use following data

For the following data:

species:

id    temp_preferences    temp_limit

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

1     +                   10

ponds:

id    temperature    city

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

10    5              Bialystok

ducks:

id    name    species_id   pond_id

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

10    Lotto   1            10

Query should return:

pond_id   happy_ducks

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

1         0

Try/ Catch Usage in Stored Procedures

Create Procedure XXX

 AS

 Begin

 Begin Try

  

Set nocount on

--Declaration Section

Begin Transaction Trans1

---Your Code 

Commit Transaction Trans1

  

End Try

Begin Catch

Declare

    @ErrorMessage    NVARCHAR(4000),

    @ErrorNumber     INT,

    @ErrorSeverity   INT,

    @ErrorState      INT,

    @ErrorLine       INT,

    @ErrorProcedure  NVARCHAR(200);

-- Assign variables to error-handling functions that it capture information for RAISERROR.

Select

    @ErrorNumber = ERROR_NUMBER(),

    @ErrorSeverity = ERROR_SEVERITY(),

    @ErrorState = ERROR_STATE(),

    @ErrorLine = ERROR_LINE(),

    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Building the message string that will contain original error information.

Select

@ErrorMessage = 

    N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '+ ERROR_MESSAGE();

If XACT_STATE() <> 0 Rollback Transaction Trans1

-- Raise an error: msg_str parameter of RAISERROR will contain the original error information.

Raiserror

     (

    @ErrorMessage,

     @ErrorSeverity,

     1,              

     @ErrorNumber,    -- parameter: original error number.

     @ErrorSeverity,  -- parameter: original error severity.

     @ErrorState,     -- parameter: original error state.

     @ErrorProcedure, -- parameter: original error procedure name.

     @ErrorLine       -- parameter: original error line number.

     );

 End Catch;

END

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)