Infytq - SQL

page 1 out of 1

page 1 out of 1

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

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;

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?

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.

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;

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;

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


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?

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});



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

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



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?

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?


page 1 out of 1

page 1 out of 1

Sign Up Page


To view the solution need to Login

Score : 0 / 0