
page 1 out of 1
page 1 out of 1
Question 1
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
SELECT SUBSTR(STR_TO_DATE('01-02-2018','MM-DD-YYYY'),4,3)||'-'||2018 month from dual;
Question 3
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
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
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
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?
Question 8
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?
Question 9
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?
Question 10
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
Oops!!
To view the solution need to Login