Infosys Springboard DBMS Part 2 MCQs
 |
| Infosys Springboard DBMS Part 2 MCQs |
📚 Infosys Springboard DBMS Part 2 – MCQs Practice & Explanation
Welcome to Part 2 of our DBMS (Database Management System) MCQs series based on the Infosys Springboard curriculum! This Web Page contains a set of carefully selected multiple-choice questions that will help reinforce your understanding of key DBMS
This Web Page is the second part of the DBMS MCQ series tailored for learners using Infosys Springboard. It includes:
15–20 MCQs with detailed solutions
Real exam pattern-based questions
Easy-to-understand explanations
Quick revision for interview or test prep
Whether you're brushing up on basics or prepping for assessments.
01. Consider the following statements with respect to a candidate key:
a. Candidate key identifies rows in a relation uniquely.
b. There can be only one candidate key in a relation.
c. A candidate key can be a combination of more than one attribute in a relation.
Identify the statements which are TRUE.
Only a and c ✔️
Only a and b
Only a
Only b and c
02. Consider the tables emp and dept given below:
 |
INSERT INTO emp VALUES(1006, 'Fedrick', 10, 2000); ✔️
INSERT INTO emp VALUES (1008, 'Fedrick', NULL, 3000): ✔️
INSERT INTO dept (deptno) VALUES (40);
INSERT INTO dept VALUES (NULL, 'HR'):
03. Consider the tables SUPPLIER and ORDERS given below:
TABLE: ORDERS
Identify the query to fetch the details of all the suppliers along with order details. Include the suppliers who haven't ordered any items also.
SELECT s.supplier_id, o.supplier id, o.order date FROM supplier s RIGHT OUTER JOIN orders o on s.supplier_id=o.supplier id;
SELECT s.supplier_id, o.supplier_id, o.order_date FROM orders o LEFT OUTER JOIN supplier s on s.supplier_id=o.supplier_id;
SELECT s.supplier_id, o.supplier_id, o.order_date FROM orders o FULL OUTER JOIN supplier s on s.supplier _id=o.supplier _id;
SELECT .supplier_id, o.supplier_id, o.order_date FROM suppliers LEFT OUTER JOIN orders o on s.supplier s.supplier_id=o.supplier _id; ✔️
04. Consider following tables:
There is a requirement to display donor id, donor name of those donors who donated the blood.
Also display the patient id who have received blood from these donors.
The following query was written to solve the above requirement.
SELECT DISTINCT d.donorid, donorname, patientid FROM donor d INNER JOIN bloodtransaction b ON didonorid = b.donorid;
What is the output of the above query?
Ans: B
05. Rajesh created a table EMP in order to record employee details.
The table creation script for the same is given below:
CREATE TABLE EMP(
empid NUMBER(10) PRIMARY KEY,
empname VARCHAR2(50),
cabinnumber NUMBER(20) UNIQUE
):
Currently table has some data as given below:
INSERT INTO EMP VALUES (1004,'Lali',789);
INSERT INTO EMP VALUES (1004, 'Lali',NULL); ✔️
INSERT INTO EMP VALUES (NULL, 'Lali',456);
INSERT INTO EMP VALUES (1003,'Lali',578);
Infosys Springboard DBMS Part 01 MCQs
06. Consider the tables customer and subscription given below:
What is the output of the below query?
SELECT customerName
FROM customer c JOIN subscription s ON s.customerId=c.customerId
WHERE s. customerId NOT IN (SELECT customerId
FROM subscription GROUP BY customerId HAVING COUNT (customerId)
(SELECT MAX(COUNT(customerId)) FROM subscription GROUP BY customerId)):
Jack
Harry ✔️
Tom
Peter
07. Consider the tables vehtype and vehicle given below:
vehtype(vid ,vtype) with vid being the primary key.
vehicle(id, vid, brand, model, price) with id being the primary key and vid foreign key to the vehtype tabie
Consider the below join query:
select brand from vehicle v join vehtype vt
on v.vid =vt.vid
group by brand
having count(vtype) >1
Choose an equivalent subquery that would achieve the functionality performed by the above join query.
Note: The only difference between the options is in the 'WHERE' clause.
SELECT brand FROM vehicle WHERE vid in (SELECT vid FROM vehtype HAVING COUNT(vtype)> 1);
SELECT brand FROM vehicle WHERE vid in (SELECT vid FROM vehtype GROUP BY vid HAVING COUNT(vtype)>1)
SELECT brand FROM vehicle WHERE vid IN (SELECT vid FROM vehtype) HAVING COUNT(vid)> 1;
SELECT brand FROM vehicle WHERE vid in (SELECT vid FROM vehtype) GROUP BY brand HAVING COUNT (yid)> 1 ✔️
08. Consider the following tables:
What will be the output of the following query?
SELECT B.BankName,AD.AccType,SUM(Balance)
FROM CustAccountDetails CA INNER JOIN Bank B ON CA.BankCode = B.BankCode
INNER JOIN AccountDetails AD ON CA.Accid = AD.AccId
GROUP BY B.BankName,AD.AccType HAVING SUM(Balance) =
(SELECT MIN(SUM(Balance)) from custAccountDetails GROUP BY BankCode,Accid);
09. Consider the table broker given below:
Which of the following will be one of the rows in the output of the below SQL query?
SELECT BrokerNo, COUNT(NVL(Comission,0)) Commission
FROM Broker GROUP BY BrokerNo; |
 |
| Infosys Springboard DBMS MCQs |
10. Consider the table products given below:
Note: modelno is the PRIMARY KEY of products table.
What will be the output of the following query?
SELECT prodtype FROM products
GROUP BY prodtype
HAVING COUNT(modelno) = (SELECT MIN(COUNT(modelno)) FROM products GROUP BY prodtype);
PC
PC Printer ✔️
Laptop
Printer
11. Consider the following code written for creating the table
CREATE TABLE ACCOUNT (ACCNO INT, ACCNAME VARCHAR(30) NOT NULL, BALANCE ).
The table is NOT getting created, identify the reason.
BALANCE must be NOT NULL
ACCNO must be NOT NULL
Primary key is missing for ACCOUNT
BALANCE must have a datatype ✔️
12. Consider the table Teacher given below:
How many rows will get updated when the below query is executed?
UPDATE TEACHER SET SALARY = SALARY + 5000 WHERE TEACHERID IN
(SELECT TEACHERID FROM TEACHER WHERE AVAILABILITY = 'N' OR SALARY > 5000);
1
4
3 ✔️
2
13. Cray Retail is a retail chain. They have a table in their database that has the following data:
Three developers Tom, Dick and Harry are given the task of finding the average salary of the employees in the company. They have been told that a NULL in the salary column means that those employees should not be considered.
They write the following queries:
Tom: SELECT SUM(Salary)/COUNT*) from EMPLOYEE WHERE Salary IS NOT NULL;
Dick: SELECT SUM(Salary)/COUNT(*) from EMPLOYEE;
Harry: SELECT AVG(Salary) from EMPLOYEE;
Which of them have got the query correct?
Tom and Harry have got it right ✔️
Only Harry has got it right
Tom and Dick have got it right
Only Dick has got it right
14. Consider the table toys given below:
What will be the output of the below query?
SELECT * FROM toys t1 WHERE price = (SELECT MAX(price) FROM toys t2 WHERE t1.categoryid=t2.categoryid);
Ans: A
15. A table Employee has the following data:
The following queries were executed on the table successfully :
UPDATE EMPLOYEE SET DEPARTMENT = 'HR' WHERE DEPARTMENT = 'Marketing' ;
DELETE FROM EMPLOYEE WHERE DEPARTMENT = 'HR' AND SALARY = 1000;
What will be the output of the following query?
SELECT COUNT(*) FROM EMPLOYEE;
5
6
4 ✔️
2
16. Consider the below table named Destination:
DestId is the PRIMARY KEY.
Another table Travel holds the list of tour packages as given below:
DestId in the Travel table references the Destid of the Destination table using a FOREIGN KEY.
Given the above details which of the following queries will execute successfully.
[Choose any TWO]
DELETE FROM Destination where destid = 102 ✔️
UPDATE Destination SET destid = 105 where destid = 103
DELETE FROM Destination where destid = 104
UPDATE Destination SET destid = 105 where destld = 102 ✔️
17. Tables course and student have 1-N relationship respectively. Cid is the primary key of course table and Sid is the primary key of student table. To which table the foreign key should be added?
Either Student or Course
In 1-N relationship foreign key cannot be established
Only Student ✔️
Only Course
18. The below table holds the result of an assessment of 3 students.
If a student has scored less than 50, his status must be shown as 'FAIL. Otherwise, his mark must be displayed.
The 3 developers wrote the following queries:
Tom:
SELECT STUDENTID, CASE WHEN MARKS < 50 THEN 'FAIL' ELSE TO_CHAR(Marks) END AS STATUS FROM ASSESSMENT
Dick:
SELECT STUDENTID, CASE WHEN MARKS < 50 THEN 'FAIL' ELSE Marks END AS STATUS FROM ASSESSMENT
Harry:
SELECT STUDENTID, CASE WHEN MARKS > 50 THEN 'FAIL' ELSE Marks END FROM ASSESSMENT
Which one of them will give the desired output?
Dick
Harry
Tom ✔️
None of them
19. Consider the tables Bank, CustAccountDetails and AccountDetails given below:
SELECT CustAccId, Balance, BankName, ad.AccId FROM CustAccountDetails cd
INNER JOIN Bank ba ON ba. BankCode = cd.BankCode INNER JOIN AccountDetails ad
ON cd.AccId = ad.AccId
WHERE Balance >
(SELECT AVG(Balance) FROM CustAccountDetails
INNER JOIN Bank
ON Bank. BankCode = CustAccountDetails.BankCode
WHERE BankName = ba.BankName) AND ad.AccType = 'Saving';
20. Consider the below table SalesPerson:
SELECT DISTINCT Id, Amount FROM SalesPerson ORDER BY Amount ASC;
Based on the output of the above query, identify the correct statement.
1005 will be 3rd record ✔️
1009 will be 4th record
1002 will be 1st record
1001 will be 4th record
 |
| Infosys Springboard DBMS MCQs |
