Q1. Consider the tables project and allocation given below:

Table: project

projectid	projectname
Infy101	NAM
Infy102	All State Bank
Infy103	Suntrust
Infy104	Swiss Insure

 

Table: allocation

empid	empname	projectid
E300	Olivia	Infy102
E301	Hussy	Infy102
E302	Mike	Infy103
E303	Jack
E304	Smith

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:

id	Fruitname	Season	Price
501	Pine apple	Summer	45
502	Water melon	Winter	40
503	Custard apple	Summer	40
504	Banana	Winter	25
505	Mosambi	Winter	25
506	Musk melon	Summer	35

"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:

id	Fruitname	Season	Price
502	Water melon	Winter	35
503	Custard apple	Summer	25
504	Banana	Winter	25
505	Mosambi	Winter	25
506		Winter

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

---

Q3. Consider the table instructor given below:

Table: instructor

Instructorid	name	subject	university	salary
I201	Alex	Java	Harvard	70000
I202	Sam	Ruby	Oxford	75000
I201	Alex	RDBMS	Harvard	60000
I203	Mitchel	Networking	Cambridge	50000
I202	Sam	RDBMS	Harvard	40000
I203	Mitchel	.NET	Oxford	50000

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

contractorid	contractorname	Noofconstructions
C1001	ABC constructions	115
C1002	SSN constructions	225
C1003	XYZ constructions	100
C1004	PSN constructions	75
C1005	SSK constructions	65

Table: construction

buildingid	Contractorid	status	completiondate
B1001	C1001	In-Progress	NULL
B1002	C1002	completed	20-Aug-17
B1003	C1001	In-Progress	NULL
B1004	C1002	completed	20-Aug-17
B1005	C1004	In-Progress	NULL

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

region	Salesman	Sales
North	James	800000
West	Alan	760000
West	David	350000
East	John	124000
North	Nolan	590000
South	Nick	235000
East	Nicholas	145000

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

region	Salesman	sales
West	Alan	760000
East	John	124000
East	Nicholas	145000

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

store_id	city	region
S001	New York	East
S002	Chicago	Central
S003	Atlanta	East
S004	Los Angeles	West
S005	SanFrancisco	West
S006	Philadelphia	East

Table: sales

Productid	desc	store_id
P204	biscuits	S001
P205	shampoo	S004
P204	biscuits	S002
P203	soap	S003
P206	rice	S005
P201	wheat	S001

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.

---

---