Company-Specific Courses
Free Resources
Webinars
About Us

Edit
Reply




Edit

InfyTQ Questions | InfyTQ Final Round DBMS Questions

Published on 14 Apr 2021

This article covers the InfyTQ Final Round DBMS Questions. Post the initial Qualifying Round, the Final Round in InfyTQ is what determines whether candidates get the certificate of Infosys Certified Software Programmer and also potentially land a job offer in Infosys. What's covered in this article is solutions to InfyTQ Questions which test on DBMS concepts, which are part of the official sample paper.


To know the pattern of InfyTQ, please click here



InfyTQ Questions | InfyTQ Final Round DBMS Qs


Q1. Consider the tables project and allocation given below:


Table: project

projectidprojectname
Infy101NAM
Infy102All State Bank
Infy103Suntrust
Infy104Swiss Insure


Table: allocation

empid
empname
projectid
E300OliviaInfy102
E301HussyInfy102
E302MikeInfy103
E303Jack
E304Smith


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
501Pine appleSummer45
502Water melonWinter40
503Custard appleSummer40
504BananaWinter25
505MosambiWinter25
506Musk melonSummer35


"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
502Water melonWinter35
503Custard appleSummer25
504BananaWinter25
505MosambiWinter25
506Winter


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
I201AlexJavaHarvard70000
I202SamRubyOxford75000
I201AlexRDBMSHarvard60000
I203MitchelNetworkingCambridge50000
I202SamRDBMSHarvard40000
I203Mitchel.NETOxford50000


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
C1001ABC constructions115
C1002SSN constructions225
C1003XYZ constructions100
C1004PSN constructions75
C1005SSK constructions65


Table: construction


buildingid
contractorid
status
completiondate
B1001C1001In-ProgressNULL
B1002C1002completed20-Aug-17
B1003C1001In-ProgressNULL
B1004C1002completed20-Aug-17
B1005C1004In-ProgressNULL


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
NorthJames800000
WestAlan760000
WestDavid350000
EastJohn124000
NorthNolan590000
SouthNick235000
EastNicholas145000


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


region
salesman
sales
WestAlan760000
EastJohn124000
EastNicholas145000


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
S001New YorkEast
S002ChicagoCentral
S003AtlantaEast
S004Los AngelesWest
S005SanFranciscoWest
S006PhiladelphiaEast


Table: sales

productid
desc
store_id
P204biscuitsS001
P205shampooS004
P204biscuitsS002
P203soapS003
P206riceS005
P201wheatS001


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.


 If you want to check out the video explanation of these questions, please watch the video below.




Suggested Articles:


InfyTQ Questions and Answers | Python Qualifying Round

InfyTQ 2022 batch | Infosys InfyTQ Registration & Process

HackWithInfy 2021 | Infosys Coding Contest for 2022 batch

InfyTQ Questions and Answers | InfyTQ Aptitude for Qualifying Round

InfyTQ Questions and Answers | Java Qualifying Round

InfyTQ Final Round Python Coding Questions

InfyTQ Questions | Python MCQs | Final Round 

InfyTQ Questions | InfyTQ Final Round Java MCQs



If you have any feedback about this article and want to improve this, please write to enquiry@faceprep.in
Explore 'infosys'
Articles