GATE-CS-2014-(Set-1)

Question 61

Given the following schema:

     employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
     departments(dept-id, dept-name, manager-id, location-id) 

You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SQL> SELECT last-name, hire-date
     FROM employees
     WHERE (dept-id, hire-date) IN ( SELECT dept-id, MAX(hire-date)
                                     FROM employees JOIN departments USING(dept-id)
                                     WHERE location-id = 1700
                                     GROUP BY dept-id); 

What is the outcome?

• It executes but does not give the correct result.
• It executes and gives the correct result.
• It generates an error because of pairwise comparison.
• It generates an error because the GROUP BY clause cannot be used with table joins in a subquery

Question 62

Consider two processors P1 and P2 executing the same instruction set. Assume that under identical conditions, for the same input, a program running on P2 takes 25% less time but incurs 20% more CPI (clock cycles per instruction) as compared to the program running on P1. If the clock frequency of P1 is 1GHz, then the clock frequency of P2 (in GHz) is _________.

• 1.6
• 3.2
• 1.2
• 0.8

Question 63

An operating system uses the Banker’s algorithm for deadlock avoidance when managing the allocation of three resource types X, Y, and Z to three processes P0, P1, and P2. The table given below presents the current system state. Here, the Allocation matrix shows the current number of resources of each type allocated to each process and the Max matrix shows the maximum number of resources of each type required by each process during its execution. There are 3 units of type X, 2 units of type Y and 2 units of type Z still available. The system is currently in a safe state. Consider the following independent requests for additional resources in the current state:

REQ1: P0 requests 0 units of X,  
      0 units of Y and 2 units of Z
REQ2: P1 requests 2 units of X, 
      0 units of Y and 0 units of Z

Which one of the following is TRUE?

• Only REQ1 can be permitted.
• Only REQ2 can be permitted.
• Both REQ1 and REQ2 can be permitted.
• Neither REQ1 nor REQ2 can be permitted

Question 64

Given the following statements:

    S1: A foreign key declaration can always 
        be replaced by an equivalent check
        assertion in SQL.
    S2: Given the table R(a,b,c) where a and
        b together form the primary key, the 
        following is a valid table definition.
        CREATE TABLE S (
            a INTEGER,
            d INTEGER,
            e INTEGER,
            PRIMARY KEY (d),
            FOREIGN KEY (a) references R) 

Which one of the following statements is CORRECT?

• S1 is TRUE and S2 is FALSE.
• Both S1 and S2 are TRUE.
• S1 is FALSE and S2 is TRUE.
• Both S1 and S2 are FALSE.

Question 65

Choose the most appropriate word from the options given below to complete the following sentence. He could not understand the judges awarding her the first prize, because he thought that her performance was quite __________.

• superb
• medium
• mediocre
• exhilarating