Q1. Consider the tables project and allocation given below:

Table: project

Table: allocation

SELECT p.projectid,p.projectname,a.empid

FROM project p FULL JOIN allocation a

ON p.projectid=a.projectid AND a.projectid IS NOT NULL ;

How many rows will be fetched when the above query is executed?

A. 7

B. 5

C. 3

D. 6

Answer: Option C

Explanation:

The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not. In this question the resultant data will be completely dependent on the query "p.projectid=a.projectid AND a.projectid IS NOT NULL". The column a.projectid have only 3 rows without NULL. E303 and E304 have NULL hence only 3 rows will be fetched.

Q2. Identify the statements that will be true after executing the following statements on an empty MongoDB collection(Multiple Answer Question).

db.fruit.insert([{_id:501,Fruitname:”Pine apple”,Season:”Summer”,Price:45},

{_id:502,Fruitname:”Water melon”,Season:”Winter”,Price:40},

{_id:503,Fruitname:”Custard apple”,Season:”Summer”,Price:40},

{_id:504,Fruitname:”Banana”,Season:”Winter”,Price:25},

{_id:505,Fruitname:”Mosambi”,Season:”Winter”,Price:25},

{_id:506,Fruitname:”Musk melon”,Season:”Summer”,Price:35},

db.fruit.update({$or: [{Fruitname:”Water melon’}, {Price:40}]},{$set:{Price:35}});

db.fruit.update({_id: 503}, {$set:{Price:25}});

db.fruit.update({_id: 506}, {Season:”Winter”});

db.fruit.remove({Price:{$gt:40}});

A. Three fruits will have price as 25

B. The fruit collection will have 4 “Winter” seasons

C. Two fruits will have price as 35

D. Two fruits will have a name ending with “melon”

Answer: Option A & B

Explanation:

Collection fruit after insertion:

"db.fruit.update({$or: [{Fruitname:”Water melon’}, {Price:40}]},{$set:{Price:35}})" will update the price as 35 for the rows that have fruit name as "Water melon"or price as "40"

"db.fruit.update({_id: 503}, {$set:{Price:25}});" will update the price as 25 for the fruit with ID 503

"db.fruit.update({_id: 506}, {Season:”Winter”});" will update the entire document as ID:506 and Season as Winter. Fruit name and Price will be null. This is because $set:{Season:"Winter"} was not used.

"db.fruit.remove({Price:{$gt:40}});" will remove the rows with price greater than 40.

Collection fruit after modification:

From the above table we can conclude the answers are Option A and Option B

Q3. Consider the table instructor given below:

Table: instructor

How many number of rows will be in output after executing the below query?

Query:

SELECT instructorid, name FROM instructor WHERE salary > 40000

GROUP BY instructorid, name HAVING COUNT (DISTINCT university) > 1;

A. 1

B. 2

C. 3

D. 4

Answer: Option A

Explanation:

All the rows which have salary greater than 40000 will counts to 5 and due to group by command this 5 rows will be reduced to 3.

Count of Distinct university is 3 and it is greater than 1 hence it will return 1(true) and finally 1 row will be returned.

Q4. Consider the tables contractor and construction given below:

Table: contractor

Table: construction

Query:

SELECT contractorid FROM construction WHERE status=’In-Progress’ AND contractor IN

(SELECT contractorid FROM contractor WHERE noofconstructions>=100);

How many rows will be fetched when the above query gets executed?

A. 3

B. 2

C. 1

D. 4

Answer: Option B

Explanation:

The table construction with status='In-Progress' have 3 rows with contractorid C1001 and C1004. Among those contractorid the noofconstructions>=100 is present for only C1001(noofconstructions =115). Hence 2 rows will be fetched as the final result.

Q5. Consider a Patient table with attributes patientid (primary key), patientname, city, dateofbirth and phone. Except patientid no columns are unique. The table has three indexes as follows:

IDX1 - patientid 

IDX2 - patientname, dateofbirth 

IDX3 - dateofbirth, phone

Which of the following queries will result in INDEX UNIQUE SCAN?

A. WHERE city > Mumbai' AND dateofbirth > '30-Mar-1995'

B. WHERE patientid = 'P1007' AND dateofbirth = '30-Mar-1995'

C. WHERE patientname = 'Sam' AND dateofbirth = '30-Mar-1995’

D. WHERE patientname LIKE 'R%

Answer: Option C

Explanation: Index helps us identifying the required data very easily. It is similar to Index page available in a book. Here we can find a patient with either of three index. Among the options WHERE patientname = 'Sam' AND dateofbirth = '30-Mar-1995’ alone is equivalent to IDX2. Hence our answer is Option C.

Q6. Consider the tables salesinformation given below:

Table: salesinformation

Katie and Lisa have written queries to get the below desired output:

Katie’s Query:

SELECT*FROM salesinformation WHERE (LOWER(region) LIKE ‘%t’ AND (UPPER(salesman) LIKE ‘%N’ OR UPPER(salesman) LIKE ‘N%’)) ;

Lisa’s Query:

SELECT*FROM salesinformation WHERE (LOWER(region) LIKE ‘%th’ OR (UPPER(salesman) LIKE ‘%N’ AND UPPER(salesman) LIKE ‘N%’)) ;

Whose query will generate the desired output?

A. Both Lisa’s and Katie’s

B. Neither Lisa’s nor Katie’s

C. Only Katie’s

D. Only Lisa’s

Answer: Option C

Explanation: We need to West, East in region and salesman name starting with N or ending with N. Among Kaite and Lisa Kaite's query is correct. Because Lisa is searching for region ending with "th" and also Lisa's query is searching for salesman name starting and ending with N which will fetch the wrong output.

Q7. Consider the tables store and sales given below:

Table: store

Table: sales

Which is the best primary key for the sales table from the following?

A. desc

B. {productid,store_id}

C. productid

D. store_id

Answer: Option C

Explanation:

Primary key will always consists of unique value. But all the three columns in the table sales have two duplicate values. Having desc as primary key is unusual so we should either have store_id or productid as primary key but not both as we can not have more than one primary key for a table. The table sales gives details about the product mapped to a particular store. Hence we can come to a conclusion that the best possible primary key shall be productid.