Infytq | SQL | The Inquisitive

Question 1

Consider the following table emplooyeedetail.

empid	empname	empsalary	bonus	projectdept
1	John	27000	400	Finance
2	Michael	20000	300	Telecom
3	John	40000	600	ENG
4	Mike	50000	700	ENG
5	Robert	25000	NULL	Telecom
6	Robert	20000	300	Telecom
7	Peter	30000	NULL	ENG
8	Carl	27000	NULL	Finance

When the below query is executed, what will appear as part of the output?

SELECT projectdept, round(avg(empsalary)) AVGSAL, sum(bonus) TOTALBONUS from emplooyeedetail GROUP by projectdept HAVING avg(empsalary)>20000 and sum(bonus)>500

A

projectdept	avgsal	totalbonus
Finance	40000	600

B

projectdept	avgsal	totalbonus
ENG	40000	1300
Finance	40000	600

C

projectdept	avgsal	totalbonus
ENG	40000	1300

D

projectdept	avgsal	totalbonus
Telecom	21667	600
Finance	40000	600
ENG	50000	700

Question 2

What is the output of the following query?

SELECT SUBSTR(STR_TO_DATE('01-02-2018','MM-DD-YYYY'),4,3)||'-'||2018 month from dual;

A

MONTH
JAN-2018

B

MONTH
01-2018

C

MONTH
02-2018

D

MONTH
FEB-2018

Question 3

Consider the following relational schema:

mobile (mobileid, model, cost, batterylife)

mobileid is the candidate key and following are the functional dependencies:

mobileid -> model, cost

model -> batterylife

Which type of dependency exists in the above relation?

A

partial dependency

B

Transitive dependency

C

Full functional dependency

D

Relative dependency

Question 4

Consider the table account given below:

accountnum	customerid	balance	accttype	status
100001	1001	9999	Savings	Closed
100002	1002	2500	Current	Active
100003	1003	3600	Savings	Active
100004	1004	5800	Savings	Active

Choose the appropriate SQL query to fetch the account numbers of the customers who have an active with a balance less than the average balance.

A

SELECT accountnum FROm account WHERE status = 'Active' AND balance IN (SELECT AVG(balance) FROM account);

B

SELECT accountnum FROm account WHERE status = 'Active' AND balance < (SELECT AVG(balance) FROM account GROUP BY accountnum, balance);

C

SELECT accountnum FROm account WHERE status = 'Active' GROUP BY accountnum HAVING AVG(balance) < (SELECT AVG(balance) FROm account GROUP BY accountnum);

D

SELECT accountnum FROm account WHERE status = 'Active' AND balance < (SELECT AVG(balance) FROM account)

Question 5

Consider the tables customer, vehicle and booking given below having the information of the vehicles booked by the customers by paying the initial booking amount.

Table: customer

customerid	name
C101	Richard
C102	Jason
C103	Xavier
C104	Albert

Table: vehicle

vehicleid	vehiclemodel
V101	Vespa
V102	Activa
V103	Gusto
V104	Maestro

SELECT name,vehiclemodel, bookingid, bookingamount from customer c INNER JOIN booking b on c.customerid=b.customerid and name like '%a%' left OUTER join vehicle v on b.vehicleid=v.vehicleid WHERE bookingamount>1300;

A

B

C

D

Question 6

id	name	gender	doj	dob	phone
1001	Kristen	F	20-Jan-19	5-Dec-92	8789631235
1002	Michael	M	22-Feb-18	19-Mar-92	NULL
1003	Evan	F	12-Sep-19	21-Jul-93	NULL
1004	Robert	M	8-Apr-19	16-Aug-92	9876584912

Update consultant set phone=8967859400 where id=1003;

Update consultant set name="Rahim" where id=1002

Delete from consultant where gender="F" and dob like "%92"

Select name, gender, phone from consultant;

A

name	gender	phone
Rahim	M	NULL
Evan	F	8967859400
Robert	M	9876584912

B

name	gender	phone
Kristen	F	8789631235
Evan	F	8967859400

C

name	gender	phone
Kristen	F	8789631235
Rahim	M	NULL
Robert	M	9876584912

D

name	gender	phone
Robert	M	9876584912
Rahim	M	NULL

Question 7

Consider the table consultation given below:

Table: Consultation

consultationid	department	doctorid	patientid	consultationdate	fees
1001	PED	D901	P901	12-Feb-18	210
1002	ENT	D902	P902	20-Jan-18	110
1003	GYN	D903	P903	20-Feb-18	470
1004	ENT	D904	P901	11-Feb-18	250
1005	OPD	D905	P904	12-Jan-18	300
1006	GYN	D906	P905	12-Mar-18	640
1007	PED	D901	P902	19-Feb-18	270

Query:

SELECT consultationid, patientid FROM consultation WHERE TO_CHAR(consultationdate, 'MON')="FEB" AND fees>250 UNION ALL SELECT consultationid, patientid FROM consultation WHERE department ="GYN" AND fees <= 640 ORDER BY 1 DESC;

What will be the output when the above query is executed?

A

CONSULTATIONID	PATIENTID
1003	P903
1007	P902
1003	P903
1006	P905

B

CONSULTATIONID	PATIENTID
1007	P902
1006	P905
1003	P903

C

CONSULTATIONID	PATIENTID
1007	P902
1006	P905
1004	P901
1003	P903

D

CONSULTATIONID	PATIENTID
1007	P902
1006	P905
1003	P903
1003	P903

Question 8

Consider a MongoDB collection named item given below:

db.item.insert([{_id:1, description:"Sugar", price: 60, discount:10},

{_id:2, description: "Vinegar", price: 110, discount: 15},

{_id: 3, description: "Tea", price: 200, discount: 20},

{_id: 4, description: "Biscuits". price: 50, discount: 20},

{_id:5, description: "Coffee", price: 75, discount: 5}]);

Tom executed the following mongodb statements:

db.item.update({$or:[{description:"Sugar"},{discount:20}]},{$set: {discount: 8 }});

db.item.remove({discount: 15});

db.item.find();

Which of the following statements will be TRUE when the above statements are executed sequentially?

A

Two items will have discount of 15

B

The item collection will have four documents

C

Two items will have price more than 100

D

Two items will have discount of 20

Question 9

Consider the tables book and libraryregister given below

Table: book

bookid	bookname
1001	A Brief History Of Time
1005	Hamlet
1006	The Grand Design
1011	Gulliver's travel
1020	Cosmos: A Personal Voyage
1008	War and Peace

Table: libraryregister

issueid	bookid	issuedate	returndate
10001	1005	15-Jan-18	20-Jan-18
10002	1011	12-Feb-18	20-Feb-18
10003	1006	5-May-19	17-May-19
10004	1020	12-Jan-19	20-Jan-19
10005	1001	25-Dec-18	10-Jan-19
10006	1001	12-Feb-18	20-May-18
10007	1001	12-Jan-19	20-Jan-19

Query:

SELECT bookname FROM libraryregister l1, book b WHERE l1.bookid = b.bookid AND EXISTS (SELECT 1 FROM libraryregister l2 WHERE l1.issuedate = l2.issuedate AND l1.issueid<> l1.issueid AND l1.returndate = l2.returndate);

What will be the output when the above query is executed?

A

BOOKNAME
A Brief History Of Time
Gulliver's travel
Cosmos: A Personal Voyage

B

BOOKNAME
A Brief History Of Time
Cosmos: A Personal Voyage

C

BOOKNAME
A Brief History Of Time
Gulliver's travel

D

BOOKNAME
A Brief History Of Time
A Brief History Of Time
Gulliver's travel
Cosmos: A Personal Voyage

Question 10

Consider a table order with attributes orderid(primary key), customerid, orderdate, quantity, price and category. Columns customerid, orderdate, quantity and price are non unique. The table has two explicit index as follows:

IDX1 - quantity

IDX2 - customerid, orderdate

Which of the following queries will not result in Table Scan?

A

WHERE LOWER(category) = 'stationary' AND price> 400

B

WHERE cutomerid<>'C1001' AND orderdate='30-MAR-2018'

C

WHERE price<650 AND orderid = 1001

D

WHRE customerid='C1005' OR quantity>10

E

Both 2 and 4

THE INQUISITIVE

THE INQUISITIVE

Forgot Password