from one of the two SQL skills evaluations sent by potential employers as the first stage. First evaluation had 6 questions, the other had 10. If you complete them to their satisfaction, THEN you can progress to an actual telephone interview stage.
DDL to create the tables:
CREATE TABLE [dbo].[Asset] (
[asset_id] [int] NOT NULL ,
[issue_id] [int] NULL ,
[deal_id] [int] NULL ,
[purchase_date] [smalldatetime] NULL ,
[par_amount] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[issue] (
[issue_id] [int] NOT NULL ,
[issuer_id] [int] NULL ,
[issue_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[security_type] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[defaulted] [tinyint] NULL ,
[default_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[asset] ADD
CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED
(
[asset_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[issue] ADD
CONSTRAINT [PK_issue] PRIMARY KEY CLUSTERED
(
[issue_id]
) ON [PRIMARY]
GO
Populate the two tables:
/* insert issue data */
insert issue (issue_id, issuer_id, issue_name, security_type, currency_code, defaulted, default_date)
values (1,1,'Bond', 'B', 'USD', null, '2006-03-01 00:00:00')
insert issue (issue_id, issuer_id, issue_name, security_type, currency_code, defaulted, default_date)
values (2,1,'Term Loan A', 'B', 'USD', null, '2007-01-01 00:00:00')
insert issue (issue_id, issuer_id, issue_name, security_type, currency_code, defaulted, default_date)
values (3,2,'Revolver', 'B', 'USD', null, null)
insert issue (issue_id, issuer_id, issue_name, security_type, currency_code, defaulted, default_date)
values (4,3,'Term Loan B', 'B', 'USD', null, null)
/*Insert asset data*/
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (1, 1, 1, '2006-01-31 00:00:00', 200000.0000)
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (2, 2, 1, '2006-01-01 00:00:00', 300000.0000)
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (3, 1, 1, '2005-12-15 00:00:00', 400000.0000)
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (4, 1, 2, '2001-12-01 00:00:00', 3760000.0000)
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (5, 3, 2, '2004-12-10 00:00:00', 50000.0000)
Now, here's the question:
"Write a stored procedure, Deal_compare, that compares issue par_amount for two different deals. The procedure takes two parameters, @deal_id_1, @deal_id_2 and returns the following fields: issue_id, issue_name, deal_1_par_amount, deal_2_par_amount, and par_difference. Each deal may invest in different set of issues. The result should represent all issues which are in either deal, and those that are in both should appear on one line."
I didn't see how to produce an answer without using a temp table.