Infosys Springboard DBMS MCQs

Infosys Springboard DBMS MCQs

Infosys Springboard DBMS MCQs

Q01.  Consider the tables employee and allocation given below:

Constraint: allocation table allocempid column references the employee table empid column.

Tom and Dick executed the below SQL statements in their respective machines sequentially.

Tom’s UPDATE statements:

UPDATE allocation SET allocempid=1067 WHERE allocationid=116;

UPDATE employee SET empid=1077 WHERE empid=1076;

Dick’s UPDATE statements:

UPDATE allocation SET allocempid=NULL WHERE allocationid=107;

UPDATE employee SET empid=1077 WHERE empid=1076;

Infosys HackWithInfy

Choose the correct statement from below that reflects the state of the two tables after the UPDATE statement execution.

Options :-

A. Tom's update fails due to integrity constraint violation whereas Dick's also fails due to integrity constraint violation

B. Tom's update fails due to integrity constraint violation whereas Dick's update succeeds

C. Tom's update succeeds whereas Dick's update also succeeds

D. Tom's update succeeds whereas Dick's update fails due to integrity constraint violation

Q02. Consider the tables product and orders with the data as follows:

Choose the query which will generate the output given below:

Options :-

A. SELECT p.prdid, p.prdname, MIN(o.quantityordered) quantity FROM product p INNER JOIN orders o ON p.prdid=o.prdid GROUP BY p.prdid, p.prdname having MIN(o.quantityordered)>10;

B. SELECT p.prdid, p.prdname, MIN(o.quantityordered) quantity FROM product p INNER JOIN orders o ON p.prdid=o.prdid GROUP BY p.prdid, p.prdname;

C. SELECT p.prdid, p.prdname, SUM(o.quantityordered) quantity FROM product p INNER JOIN orders o ON p.prdid=o.prdid GROUP BY p.prdid, p.prdname having SUM(o.quantityordered)<10;

D. SELECT p.prdid, p.prdname, MIN(o.quantityordered) quantity FROM product p INNER JOIN orders o ON p.prdid=o.prdid GROUP BY p.prdid, p.prdname having MIN(o.quantityordered)<10;

Q03. Consider the tables customer and dietplan given below:

Table: customer

Query:

SELECT planid, c.customerid FROM dietplan d FULL OUTER JOIN customer c ON c.customerid=d.customerid

AND LENGTH(emailid)>14;

Options :-

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

A. 7

B. 9

C. 5

D. 6

Q04. Consider the table demo given below:

Rex wants to extract only the numeric month from DOB column. Which of the following queries are CORRECT to achieve his requirement? [Choose any TWO]

Options :-

A. SELECT SUBSTR (TO_CHAR (dob, 'MM/DD/CCYY'),1,2) DOB FROM demo;

B. SELECT SUBSTR (TO_CHAR (dob, 'DD/MM/CCYY'),4,5) DOB FROM demo;

C. SELECT TO_CHAR (dob, 'MON') DOB FROM demo;

D. SELECT TO_CHAR (dob, 'MM') DOB FROM demo;

Q05. Consider the following relational schema along with functional dependencies:

OrderDetails(OrderId, DateOrdered, QuantityOrdered, ItemNumber, ItemName, ItemPrice, CustomerId, CustomerName)

{OrderId, ItemNumber} -> QuantityOrdered

OrderId ->     CustomerId,  DateOrdered

ItemNumber-> ItemName, ItemPrice

CustomerId -> CustomerName

What would be the resulting relational schema after converting to 3NF?

Options :-

A.  TableA (OrderId, CustomerId)

    TableB (OrderId, ItemNumber, QuantityOrdered, ItemPrice)

    TableC (ItemNumber, ItemName)

    TableD (CustomerId, CustomerName)

    TableE (OrderId, DateOrdered)

B.  TableA (OrderId, CustomerId, DateOrdered, CustomerName) 

    TableB (OrderId, ItemNumber, QuantityOrdered) 

    TableC (ItemNumber, ItemName) 

    TableD (CustomerId, CustomerName) 

    TableE (ItemNumber, ItemPrice)

C.  TableA (OrderId, DateOrdered) 

    TableB (OrderId, ItemNumber, QuantityOrdered) 

    TableC (ItemNumber, ItemName) 

    TableD (CustomerId, CustomerName) 

    TableE (ItemNumber, ItemPrice)

D.  TableA (OrderId, CustomerId, DateOrdered) 

    TableB (OrderId, ItemNumber, QuantityOrdered) 

    TableC (ItemNumber, ItemName) 

    TableD (CustomerId, CustomerName) 

    TableE (ItemNumber, ItemPrice)

Q06. Fazer Suppliers is a supply chain management solution. They have a table Employee in their database as follows:

3 Developers Tom, Dick and Harry are given the task of finding the number of those employees whose EmployeeNo starts with ‘3’ in the company. They have been told that a NULL in the salary column means that those employees are not to be considered

They write the following queries:

Tom: SELECT COUNT(SALARY) FROM Employee WHERE SUBSTR(EmployeeNo,1,2) = '3';

Dick: SELECT COUNT(SALARY) FROM Employee WHERE SUBSTR(EmployeeNo,1,1) = '3' AND SALARY IS NOT NULL;

Harry: SELECT COUNT(*) FROM Employee WHERE SUBSTR(EmployeeNo,1,1) = '3';

Which of them will get the correct output?

Options :-

A. Tom and Harry

B. All of them

C. Only Dick

D. Tom and Dick

Q07. Consider the table toys given below:

Query:

select * from Toys where toyname between 'A' and 'F';

Options :-

A. 0

B. 1

C. 2

D. 3

Q08. Consider the table FlightDetails given below. Determine the number of records displayed for the given query:

SELECT DISTINCT flightid, capacity, basefare FROM FlightDetails;

Options :-

A. 5

B. 4

C. 3

D. 6

Q09. Consider the table orders given below:

Options :-

A. 3

B. 5

C. 4

D. 0

Q10. David wants to create a table with CHECK constraint on cage column. He has come up with two ways of implementing the check constraint as follows:

A). CREATE TABLE customer(

cid NUMBER(5) PRIMARY KEY,

cage NUMBER(5),

cname VARCHAR2(20),

CONSTRAINT cust_check CHECK(cage>=20 AND cage<=50));

B). CREATE TABLE customer(

cid NUMBER(5) PRIMARY KEY,

cage NUMBER(5) CONSTRAINT cust_check CHECK(cage>=20 AND cage<=50),

cname VARCHAR2(20));

Identify which of the above ways of creating table with CHECK constraint is/are CORRECT?

Options :-

A. Only A

B. Only B

C. Neither A nor B

D. Both A and B

Q11. Consider the table toys given below:

A query is written to classify the toys based on toy type and price as follows:

SELECT toyid, toytype, CASE

WHEN toytype IN ('SoftToy', 'SportGame', 'BoardGame') and Price BETWEEN 1000 AND 2000 THEN 'A'

WHEN toytype IN ('ActionFigure', 'BoardGame') and Price > 2000 THEN 'B'

ELSE 'C'

END AS "TOYCATEGORY"

FROM toys;

Which among the following will be the output for the given query?

Options :-

A. A

B. B

C. C

D. D

Q12. Consider the following Employee table.

SELECT SUM(salary)

FROM Employee

GROUP BY deptno

HAVING deptno = 20;

Predict the output of the above query:

Options :-

A. A

B. B

C. C

D. D

Q13. Consider the table engineer given below:

Query:

SELECT e1.engid FROM engineer e1 INNER JOIN engineer e2 ON e1.managerid=e2.managerid

WHERE e1.engineername LIKE '%a%' AND e1.engid<>e2.engid;

Which of the employee’s details will be fetched when the above query is executed?

Options :-

A. Kristina

B. Ross

C. Paul

D. MariaQuery:

Q14. Consider the tables given below:

Determine the output of the below query.

Infosys HackWithInfy 2025

SELECT customer.customerid, NVL(booking.bookingid,'Not Yet') FROM customer LEFT OUTER JOIN booking ON customer.customerid = booking.customerid;

Options :-

A. A

B. B

C. C

D. D

Q15. Consider the table softwares given below:

Maria and Sophia have written the following queries to retrieve the details of the softwares whose license expires within 30 months.

Note: Assume sysdate as ‘24-Aug-17’

        Maria’s Query:

        SELECT * FROM softwares WHERE ADD_MONTHS (sysdate, 30) > licenseexpirydate;            

        Sophia’s Query:

        SELECT * FROM softwares WHERE MONTHS_BETWEEN (licenseexpirydate, sysdate) < 30;  

        Whose query will retrieve the required information?

Options :-

A. Maria

B. Sophia

C. Both Maria and Sophia

D. Neither Maria nor Sophia

Q16. What will be the output of the query given below?

Note: Consider the sysdate as ‘26-Jul-2017’

SELECT ADD_MONTHS(SYSDATE,5) "DATE1", ADD_MONTHS (SYSDATE, -4) "DATE2" FROM dual;

Options :-

A. A

B. B

C. C

D. D

Q17. Consider the following table named employee:

What will be the output of the following query?

SELECT * FROM employee WHERE empname LIKE 't%';

Options :-

A. A

B. B

C. C

D. D

Q18. Robert works on oracle and he has created the following table with data:

Robert needs to write a query to display the details in the following format:

Which of the following query will result in the above output? 

Options :-

A. SELECT ENAME, DESIGNATION, CASE WHEN DESIGNATION='SE' OR DESIGNATION='SSE' THEN 3 WHEN DESIGNATION='TA' THEN 4 WHEN DESIGNATION='PM' THEN 6 END as "MyRole" FROM EMPLOYEE;

B. SELECT ENAME, DESIGNATION, CASE DESIGNATION WHEN 'SE' OR 'SSE' THEN MyRole=3 WHEN 'TA' THEN MyRole=4 WHEN 'PM' THEN MyRole=6 END as "MyRole" FROM EMPLOYEE;

C. SELECT ENAME, DESIGNATION, CASE DESIGNATION WHEN 'SE' OR 'SSE' THEN 3 WHEN 'TA' THEN 4 WHEN 'PM' THEN 6 END as "MyRole" FROM EMPLOYEE;

D. SELECT ENAME, DESIGNATION, CASE WHEN DESIGNATION='SE' OR DESIGNATION='SSE' 

THEN MyRole=3 WHEN DESIGNATION='TA' THEN MyRole=4 WHEN DESIGNATION='PM' THEN MyRole=6 END as "MyRole" FROM EMPLOYEE;

Q19. There is a pet show being organized. The organizers have the following rules for pets and their owners. 

If a pet is registered for the show then the pet must be associated to atleast one owner

Multiple owners can be associated with a pet.

An owner cannot be associated with more than one pet

Choose the correct crow-feet notation that represents this relationship.

Options :-

A. A

B. B

C. C

D. D

Q20. Consider the company table given below:

Options :-

A. A

B. B

C. C

D. D

Q21. Consider the tables store and sales given below:

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

Options :-

A. productid

B. desc

C. store_id

D. {productid ,store_id}

Q22. Consider the CREATE TABLE command given below:

CREATE TABLE record (

recordid VARCHAR2(4) CONSTRAINT REC_ID_PK UNIQUE,

filename VARCHAR2(20) CHECK(filename<>subfilename),

subfilename VARCHAR2(20));

Which of the following statement will be TRUE when the above CREATE statement gets executed?

Options :-

A. Will result in ERROR as a TABLE should have one PRIMARY KEY

B. Will result in ERROR as check constraints cannot be specified in table level

C. Will result in ERROR as check constraint is specified at column level

D. Will get successfully executed

Q23. Consider the table Destination given below:

DestId is the PRIMARY KEY.

Another table Travel holds the list of tour packages as follows

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]

A. DELETE FROM Destination where destId = 102

B. UPDATE Destination SET destId = 105 where destId = 103

C. DELETE FROM Destination where destId = 104

D. UPDATE Destination SET destId = 105 where destId = 102

Infosys HackWithInfy 2025