InfyTQ DBMS Questions

28 January 2023


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.



Related Articles

Ask Us Anything !