SQL interview Questions II

1>

data one;
infile datalines;
input name $ salary;
bonus=sum(salary);
datalines;
ram 220
sita 221
radha 222
ravan 223
arjun 224
;
run;
proc sql;
select sum(salary) as avgnonus
from one;
quit; What will be the value of bonus and avgbonus variable.

a>

bonus  Avgbonus
220     1110
221
222
223
224

b>

bonus    Avgbonus
 1110       220   
                  221
                  222
                  223
                   224

c>

bonus   Avgbonus
 .                   .

d>

It will not create the bonus and avgbonus   variable

2>

Which order by clause orders a report by descending state and descending city?

a>order by state,city

b>order by desc state,  city

c>order by state desc, city desc

d>order by desc  state, desc  city

3>

Given the SAS data set ONE and TWO:
               One                                                        Two
   Year   QTR    Budget                        Year       QTR      Sales
   2001     3       500                             2001        4           300
   2001     4       400                             2001        2           200 
   2002     1       700                             2002        1           600
The following SAS program is submitted:
Proc sql ; 
    Select one.*, sales from one, two
           Where one.year=two.year;
 Quit;                                                                                                            

 Which report is generated?

a>

Year   Qtr  Budget   Sales
     2001  4      400         300
     2002  1      700         600

b>Year  Qtr   Budget   Sales
     2001  3       500        300
     2001  4       400        200
     2002  1       700        600

c>Year   Qtr    Budget  Sales
     2001   3        500       300
     2001   3        500       200
      2001   4        400       300
     2001   4        400       200
     2002   1        700       600

d>Year   Qtr     Budget  Sales
     2001   3        500        300
     2001   4        400        300
      2002  1    700  300
      2001  3     500  200
      2001  4     400  200
      2002  1    700   200
      2001  3    500   600
      2001  4    400   600
      2002  1   700    600

4>

Which syntax will select employee IDs having bonuses greater than $500?       

a>

select   employee_ID, salary*0.10 as bonus                                         

From test

Where  calculated bonus>500;
b>select employee_id, salary*0.10 as bonus                                                  

         from test having bonus>500;

c>both of the above

d>Neither of the above

5>

Proc SQL;

 Select   department , job_title , (find(job_title , "manager", "I")>0)  as manager

from test;                                                                                                                

 quit;                                                                                                                             

 what will be the observation of manager variable if it is able to find manager word in the joib_title variable?
a>10
b>1
c>0
d>5

6>Subqueries returns values to the outer queries:

a>Where and Having Clause

b>Order by Clause

c>From Clause

d>all

7>  Which statement is true?

   

a>     In noncorrelated subquery inner query is dependent on outer query

b>    in noncorrelated subquery inner query is not dependent on the outer query

c>     in correlated subquery inner query is not dependent on the outer query

d>     correlated and noncorrelated subquery returns values in the from clause.

     
   
   

8> Which statement is false?

a>A subquery is a query that resides within an outer query.

b>return values to be used in the outer query's WHERE and HAVING Clause

c>can return single or multiple values.

d>must return values from more than one column.

9>

Given the SAS data sets ONE and TWO:
              ONE                                                  TWO
    Num   Country                              Num    City
      1        Canada                                3         Paris
      2         France                                 5         Tokyo
      3         Germany
      4        Belgium
      5         Japan
   The following SAS program is submitted:
    Proc sql;
      Select country from one
          Where not exists
           (select * from two where one.num=two.num);
    Quit;

a>

Country
   France
    Japan

b>

   Country
     Germany
     Japan

c>

Country
     Canada
     France
     Belgium

d>

Country
     France
     Germany
     Belgium

10>

Which is false regarding the joins?

a>There are two types are   joins i.e inner joins and outer join .

b>Outer join is of three types: left , right and full join.

c>joins are used to combine two or more SAS data sets vertically.

d>We can combine only 2 tables in outer joins.

11>

Given the SAS dataset ONE
ONE
SALARY
----------
200
205
.
523

The following SAS program is submitted:
proc sql;
select *  from one
<insert WHERE expression here>;
quit;
The following output is desired:
SALARY
----------
200
205
523

a>where salary ne null

b>   where salary is not .

c> where salary ne missing

d>where salary is not missing

12>

Complete the SELECT clause below to create a new column named Profit by
subtracting the values of the column Cost from those of the column Price.
 select   fruit, cost, price,

a> Profit=price-cost

b> price-cost as Profit

c> profit=price-cost

d>Profit as price-cost

13>

What happens if you use a GROUP BY clause in a PROC SQL step without a
summary function?

   

a>     The step does not execute

b>    The first numeric column is summed by default

c>     The GROUP BY clause is changed to an ORDER BY clause.

d>    The step executes but does not group or sort data.

   
   
       

14>

If you specify a CREATE TABLE statement in your PROC SQL step,

a>the results of the query are displayed, and a new table is created.

b>a new table is created, but it does not contain any summarization that was
specified in the PROC SQL step.

c> a new table is created, but no report is displayed.

d> results are grouped by the value of the summarized column.

15>

          A Cartesian product is returned when

a>     join conditions are not specified in a PROC SQL join.

b>    join conditions are not specified in a PROC SQL set operation.

c>     more than two tables are specified in a PROC SQL join.

d>    the keyword ALL is used with the OUTER UNION operator.

Now prepare your Base SAS and Advance SAS Certification Exams with Online Mock tests:  http://exam.sankhyana.com

For training related info kindly mail us at info@sankhyana.com
www.sankhyana.com