SQL Subquery interview questions and answers

Here are some beginner to intermediate level SQL interview questions and answers. This post compliments my other posts.

•  SQL Interview Questions and Answers
•  SQL interview questions and answers - scenarios based
•  SQL Interview Questions and Answers on deleting records
•  SQL Interview Questions and Answers: storing a tree structure in a database table
•  Excel spreadsheet to generate SQL
•  SQL Tutorial with HSQLDB

Q. What is a subquery?
A. Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE clause of the sql statement. A subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.  Subqueries are an alternate way of returning data from multiple tables.


Q. Can you create a subquery in a From clause?
A. Yes. Subqueries can be used in From, Where and Having clauses. For example, in Sybase

select * 
from 
(
    select 'A' as colVal
    union 
    select 'B' as colVal
) data

Returns:

colVal
------
A
B

Joining virtual tables is one of the most powerful feature of subqueries. Virtual in this context means the result set you are joining is built on the fly. Here is a more advanced example:

  declare @clientId varchar(30),
          @reportDate date, 
          
  set nocount on

  select reportId   from   
          Report_Status s,   
          ReportKey k,  
          ReportGroupKey gk,  
    
           --subquery in from clause
          (select  max(s.createddttm) as maxdate, k1.clientId from   
              Report_Status  s,   
              ReportKey k1,  
              ReportGroupKey gk      
           where   k1.InactiveFlag ='N'  
             and  gk.InactiveFlag ='N'  
             and  gk.KeyId = k1.Id  
             and  gk.Id = s.GroupKeyId  
           group by k1.clientId  
          )  maxdates    
            
      where k.InactiveFlag ='N'  
      and  gk.InactiveFlag ='N'  
      and  gk.KeyId = k.Id  
      and  gk.Id = s.GroupKeyId  
      and s.CreatedDtTm = maxdates.maxdate  
      and k.ClientId = @clientId 
      and maxdates.ClientId = k.ClientId
      and k.reportDate = @reportDate 
   

Q.What is a correlated subquery?
A. A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

 
SELECT outer.product_name FROM product outer 
  
 WHERE outer.product_id = (SELECT inner.product_id FROM order_items inner 
                            
                            WHERE outer.product_id = inner.product_id);

If a subquery is not dependent on the outer query it is called a non-correlated subquery.




Q. What are the advantages and disadvantages of using a subquery?
A.

Advantages:

• Subqueries allow you to use the results of another query in the outer query.
• Subqueries in some complex SQL queries can simplify coding and improve maintainability by breaking down the complex query into a series of logical steps.
• In some cases, subqueries are easier to understand than complex joins and unions.

Disadvantages:

•  When a subquery is used, the query optimizer of the database server may have to perform additional steps like sorting the results, etc. Hence, in some cases subqueries can be less efficient than using joins. So, favor joins to subqueries.

You may also like:

• SQL Interview Questions and Answers
•  SQL interview questions and answers - scenarios based
•  SQL Interview Questions and Answers on deleting records
•  SQL Interview Questions and Answers: storing a tree structure in a database table
•  Excel spreadsheet to generate SQL
•  SQL Tutorial with HSQLDB

Sybase Interview Questions and Answers

In my experience, many investment banks use Sybase as their database management system.


Q. How would you go about creating a new stored procedure in Sybase?
A. The basic steps involved are

1. Check if the procedure already exists?
2. If it already exists drop the procedure
3. create a new procedure with input arguments
4. declare any additional variables to be used in the procedure
5. assign values to these variables
6. create temporary tables and load some data
7. join other tables with temp tables to process data
8. handle error  scenarios
9. Keep track of the row counts
10. add logging statements where required.


Here is a very simplified example, covering a number of key aspects of a stored procedure.

-- select the schema to use
use my_db_schema 
go

IF OBJECT_ID('dbo.my_stored_proc') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.my_stored_proc
    IF OBJECT_ID('dbo.my_stored_proc') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.my_stored_proc >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.my_stored_proc >>>'
END
go

CREATE PROC dbo.my_stored_proc  
(   
    @ParentName      varchar(30) = null,
    @ParentBatchJobId   int = null
)   
AS   

--declare variables
DECLARE @error  int,   
    @rowcount   int,   
    @error_message  varchar(240),  
 @error_number   int,
 @NumberOfDays   int,
 @DeleteDate datetime,
    @TotalRows  int,
 @proc_name  varchar(30),   
 @now        datetime
 
 
--assign values to your variables
  SELECT  @proc_name  = object_name(@@procid),
          @now     = GETDATE(),
    @rowcount     = 0,
          @TotalRows = 0

  
   select  @NumberOfDays = NumberValue,
           @DeleteDate = DateValue
        from    ConfigTable
        where   type = 'basic' and name='purge'
       
 -- error handling from last select
  SELECT @Error = @@Error   
        IF @Error <> 0
        BEGIN   
            SELECT @error_number = 50010
            SELECT @error_message = "Failed: could not get number of days parameter.(" + CONVERT(varchar(10), @Error) + ") "
            GOTO Error_Exit   
        END
 
 
 --Create a temporary table select the parant ids that need to be deleted
 CREATE TABLE #tmp_parent_ids
    (   
       ParentId    numeric(12,0)  not null
    )
    
    insert  into #tmp_parent_ids 
    select distinct(parent_id) from dbo.parent_table where createddttm < @DeleteDate
    
 
 
 if @NumberOfDays > 0
 BEGIN
 
   -- execute another stored proc and get an output to be used in this stored proc
    EXEC another_stored_proc "BatchJob", @NewNum = @ChildBatchJobId Output  

    SELECT @Error = @@Error   
       IF @Error <> 0 OR @ChildBatchJobId IS NULL   
       BEGIN   
         SELECT @error_number = 50050
         SELECT @error_message = "Failed: could not get the next BatchJobId from the SequenceNumber table.(" + CONVERT(varchar(10), @Error) + ") "
         GOTO Error_Exit   
        END    
  
  
  //do som logic like purging rows
    BEGIN   
           
            SET ROWCOUNT @PurgeRows   
       
            BEGIN TRANSACTION   

            DELETE child_table
            FROM child_table ct, #tmp_parent_ids tmp
            WHERE ct.parent_id = tmp.ParentId
       
            SELECT  @Error = @@Error, @Rows = @@Rowcount
            IF @Error != 0   
            BEGIN   
                SELECT @error_number = 50100
                SELECT @error_message = "Failed: (" + CONVERT(varchar(10), @Error) + ") could delete rows"
                ROLLBACK TRANSACTION       
                GOTO Error_Exit   
            END   
 
 END
 

success_exit:
      RETURN (0)
 
Error_Exit:  

     SELECT @error_message = "Error processing the proc" + " " + + @error_message;
  RAISERROR @error_number @error_message   
     RETURN (1)
  
go

IF OBJECT_ID('dbo.my_stored_proc') IS NOT NULL
    EXEC sp_procxmode 'dbo.my_stored_proc','unchained'
go
IF OBJECT_ID('dbo.my_stored_proc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.my_stored_proc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.my_stored_proc >>>'
go
IF OBJECT_ID('dbo.my_stored_proc') IS NOT NULL
    GRANT EXECUTE ON dbo.my_stored_proc TO user_admin
go

@@variable_name like @@Error, @@procid are Sybase variables. @variable_name like @Error are user defined stored proc variables.